Discuss Databricks Databricks Certified Data Engineer Associate Exam Topic 2 Question 25

A data engineering team has two tables. The first table march_transactions is a collection of all retail transactions in the month of March. The second table april_transactions is a collection of all retail transactions in the month of April. There are no duplicate records between the tables.

Which of the following commands should be run to create a new table all_transactions that contains all records from march_transactions and april_transactions without duplicate records?

ACREATE TABLE all_transactions AS
SELECT * FROM march_transactions
INNER JOIN SELECT * FROM april_transactions;

BCREATE TABLE all_transactions AS
SELECT * FROM march_transactions
UNION SELECT * FROM april_transactions;

CCREATE TABLE all_transactions AS
SELECT * FROM march_transactions
OUTER JOIN SELECT * FROM april_transactions;

DCREATE TABLE all_transactions AS
SELECT * FROM march_transactions
INTERSECT SELECT * from april_transactions;

ECREATE TABLE all_transactions AS
SELECT * FROM march_transactions
MERGE SELECT * FROM april_transactions;

Suggested Answer: D

In Databricks, you can use different languages within the same notebook by using magic commands. Magic commands are special commands that start with a percentage sign (%) and allow you to change the behavior of the cell. To use SQL within a cell of a Python notebook, you can add %sql to the first line of the cell. This will tell Databricks to interpret the rest of the cell as SQL code and execute it against the default database. You can also specify a different database by using the USE statement. The result of the SQL query will be displayed as a table or a chart, depending on the output mode. You can also assign the result to a Python variable by using the -o option. For example, %sql -o df SELECT * FROM my_table will run the SQL query and store the result as a pandas DataFrame in the Python variable df. Option A is incorrect, as it is possible to use SQL in a Python notebook using magic commands. Option B is incorrect, as attaching the cell to a SQL endpoint is not necessary and will not change the language of the cell. Option C is incorrect, as simply writing SQL syntax in the cell will result in a syntax error, as the cell will still be interpreted as Python code. Option E is incorrect, as changing the default language of the notebook to SQL will affect all the cells, not just one.Reference:Use SQL in Notebooks - Knowledge Base - Noteable, [SQL magic commands - Databricks], [Databricks SQL Guide - Databricks]

Get Premium Databricks Certified Data Engineer Associate Questions as Interactive Web-Based Practice Test or PDF Real Exam Format Detailed Explanations Instant Access Get Your Practice Test

38 comments · newest first

Leota 10 months ago

I doubt option E would work, MERGE isn’t a standard SQL command for this.

Judy 11 months ago

I’m surprised there’s no mention of duplicates in the tables!

Sherita 11 months ago

Wait, why would you use INNER JOIN? That doesn't fit the requirement.

Helga 11 months ago

I think B is correct too, makes sense!

Fletcher 11 months ago

Option B is the right choice! UNION combines them without duplicates.

Yan 12 months ago

I thought OUTER JOIN was for including all records, but that might not help with duplicates either. I'm confused about the right command.

Krystina 12 months ago

I practiced a similar question, and I feel like UNION is definitely the way to go for combining distinct records.

Bo 12 months ago

I'm not entirely sure, but I remember something about INNER JOIN not being suitable for this since it requires matching records.

Ernest 1 year ago

I think we need to combine the two tables without duplicates, so maybe UNION is the right choice?

Melita 1 year ago

This is a good one! I think the key is understanding the difference between UNION and INTERSECT. UNION will combine the two tables and remove duplicates, while INTERSECT will only keep the records that are present in both tables. Since the question says there are no duplicates, UNION is the way to go.

Donette 1 year ago

I'm a little confused by the OUTER JOIN option. Isn't that for combining tables with matching keys, not just concatenating two tables? I'm leaning towards UNION, but I want to double-check that before submitting my answer.

Vi 1 year ago

Okay, I've got this. Since the question specifies there are no duplicates between the tables, UNION is the way to go. That will combine the two tables while automatically removing any duplicates. Easy peasy!

Elise 1 year ago

Hmm, I'm a bit unsure about this one. The wording is a bit tricky - it says there are no duplicate records between the tables, so I'm not sure if UNION or INTERSECT is the right approach. I'll have to think this through carefully.

Hannah 1 year ago

This looks like a straightforward SQL question. I think the key is to combine the two tables in a way that avoids duplicates, so I'll focus on the UNION and INTERSECT options.

Earleen 1 year ago

Okay, let's see here. I remember from the course materials that MCOs usually have a formal program for overseeing delegated activities. I think option D sounds like it's describing the purpose of that program, so I'll go with that.

Lizbeth 1 year ago

Ah, I remember learning about this in class. The key is that PPPoE requires the server and client to be in the same broadcast domain. With a router in between, that wouldn't work. B is the correct answer.

Lezlie 1 year ago

Okay, I've got this. An e-commerce system with electronic data interchange and electronic funds transfer should lead to faster cash flow and lower inventory and receivables, so the answer is A.

Sharika 1 year ago

I'm leaning towards C since it has something to do with collaboration, but I really can't recall if it applies to mobile access specifically.

France 1 year ago

MERGE? Is this a data engineering exam or a relationship counseling session? Let's keep it professional, folks!

Lai 1 year ago

↳ replying to France

Agreed, using UNION is the way to go for this data engineering task.

Lonna 1 year ago

↳ replying to France

That's the correct command to combine the records from both tables without duplicates.

Erick 1 year ago

↳ replying to France

Agreed, let's stick to professional terms in data engineering.

Benedict 1 year ago

↳ replying to France

B) CREATE TABLE all_transactions AS SELECT * FROM march_transactions UNION SELECT * FROM april_transactions;

Bettina 1 year ago

↳ replying to France

That's the correct command to combine the records without duplicates.

Cherelle 1 year ago

↳ replying to France

B) CREATE TABLE all_transactions AS SELECT * FROM march_transactions UNION SELECT * FROM april_transactions;

Melda 1 year ago

INTERSECT? Really? That would only return the common records between the two tables, which is not what we need here.

Ammie 1 year ago

Hmm, OUTER JOIN would include all records from both tables, even if there are duplicates. Not the best choice for this scenario.

Stephanie 1 year ago

↳ replying to Ammie

B) CREATE TABLE all_transactions AS SELECT * FROM march_transactions UNION SELECT * FROM april_transactions;

Leontine 1 year ago

↳ replying to Ammie

A) CREATE TABLE all_transactions AS SELECT * FROM march_transactions UNION SELECT * FROM april_transactions;

Alecia 2 years ago

I agree with Rickie. UNION is the way to go here, as we want to combine the two tables without any duplicates.

Margart 1 year ago

↳ replying to Alecia

No, I think you guys are right. UNION is the best choice to combine the tables without duplicates.

Michael 1 year ago

↳ replying to Alecia

I'm not sure about that. Maybe we should consider other options as well.

Johnathon 1 year ago

↳ replying to Alecia

Yes, I agree. We need to use UNION to merge the tables without duplicate records.

Latanya 1 year ago

↳ replying to Alecia

I think option B) is the correct one. UNION combines the two tables without duplicates.

Arlean 2 years ago

I'm not sure, but I think B makes sense because it combines the records from both tables.

Rickie 2 years ago

Option B is the correct answer. The UNION operator combines the results of two queries while removing any duplicate records.

Beatriz 2 years ago

I agree with Iola, UNION is used to combine records without duplicates.

Iola 2 years ago

I think the answer is B.

Databricks Certified Data Engineer Associate Exam - Topic 2 Question 25 Discussion