Answer-first summary for fast verification
Answer: `CREATE TABLE all_transactions AS SELECT * FROM march_transactions UNION SELECT * FROM april_transactions;`
The UNION operator is the correct choice because: 1. **UNION combines result sets** from multiple SELECT statements into a single result set 2. **UNION automatically removes duplicates** - which is exactly what's needed since the requirement is to have all records without duplicates 3. **The problem states there are no duplicate records between tables**, but UNION ensures no duplicates even if there were **Why other options are incorrect:** - **A (INNER JOIN)**: This performs a join operation based on matching columns, not a union of all records - **C (OUTER JOIN)**: This also performs a join operation, not a union of all records - **D (INTERSECT)**: This returns only records that exist in BOTH tables, not all records from both tables - **E (MERGE)**: MERGE is used for upsert operations (UPDATE/INSERT), not for combining all records from two tables **Note**: In SQL, UNION ALL would keep duplicates, but UNION removes duplicates. Since the requirement is "without duplicate records," UNION is the correct choice.
Author: Keng Suppaseth
Ultimate access to all questions.
No comments yet.
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?
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;