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
Correct Answer: B
Why B is correct:
- The
UNIONoperator in SQL combines the result sets of two or more SELECT statements and removes duplicate rows. - Since the problem states there are no duplicate records between the tables,
UNIONwill combine all records from both tables into a single result set. - The syntax
CREATE TABLE all_transactions AS SELECT * FROM march_transactions UNION SELECT * FROM april_transactions;correctly creates a new table with all records from both source tables.
Why other options are incorrect:
A (INNER JOIN):
INNER JOINreturns only records that have matching values in both tables.- Since these are separate monthly transaction tables with no overlapping records (as stated), an INNER JOIN would return no records.
C (OUTER JOIN):
OUTER JOIN(typically FULL OUTER JOIN) returns all records when there is a match in either left or right table.- However, this would create a result with combined columns from both tables (not a simple union of rows), and would require a join condition.
- The syntax is also incorrect for combining rows from two tables.
D (INTERSECT):
INTERSECTreturns only the records that exist in both tables.- Since there are no duplicate records between the tables, this would return an empty result set.
E (MERGE):
MERGE(or UPSERT) is used to insert, update, or delete records based on matching conditions.- It's not used for simply combining two tables into a new table.
- The syntax shown is not valid SQL for combining two tables.
Key SQL Concepts:
UNION: Combines result sets, removes duplicates (useUNION ALLto keep duplicates)JOIN: Combines columns from different tables based on a related columnINTERSECT: Returns common records between two result setsMERGE: Performs insert/update/delete operations based on matching conditions
Comments
Loading comments...