Databricks Certified Data Engineer - Associate
Get started today
Ultimate access to all questions.
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?
Real Exam
Community
KKeng
A
CREATE TABLE all_transactions AS
SELECT * FROM march_transactions
INNER JOIN SELECT * FROM april_transactions;
B
CREATE TABLE all_transactions AS
SELECT * FROM march_transactions
UNION SELECT * FROM april_transactions;
C
CREATE TABLE all_transactions AS
SELECT * FROM march_transactions
OUTER JOIN SELECT * FROM april_transactions;
D
CREATE TABLE all_transactions AS
SELECT * FROM march_transactions
INTERSECT SELECT * from april_transactions;
E
CREATE TABLE all_transactions AS
SELECT * FROM march_transactions
MERGE SELECT * FROM april_transactions;
Explanation:
Explanation
The correct answer is B because:
-
UNION combines the results of two SELECT statements and removes duplicate rows by default (UNION vs UNION ALL). Since the problem states there are no duplicate records between the tables, UNION is appropriate.
-
INNER JOIN (Option A) would only return records that have matching values in both tables, which is not what we want since we need ALL records from both tables.
-
OUTER JOIN (Option C) syntax is incorrect - there's no such thing as just "OUTER JOIN" without specifying LEFT, RIGHT, or FULL. Even if corrected, joins combine tables based on matching columns, not simply concatenating all rows.
-
INTERSECT (Option D) returns only the records that exist in BOTH tables, which would likely return no records since the tables contain different months' transactions.
-
MERGE (Option E) is used for upsert operations (UPDATE/INSERT) based on matching conditions, not for combining two tables into one.
Key Points:
- UNION combines result sets vertically (adds rows)
- JOIN combines tables horizontally (adds columns)
- The problem asks for "all records from march_transactions and april_transactions" which is exactly what UNION does
- Since there are no duplicates between tables, UNION will include all records from both tables
Comments
Loading comments...