Answer-first summary for fast verification
Answer: `CREATE TABLE all_transactions AS SELECT * FROM march_transactions UNION SELECT * FROM april_transactions;`
## Explanation **Correct Answer: B** **Why B is correct:** - The `UNION` operator 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, `UNION` will 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 JOIN` returns 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):** - `INTERSECT` returns 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 (use `UNION ALL` to keep duplicates) - `JOIN`: Combines columns from different tables based on a related column - `INTERSECT`: Returns common records between two result sets - `MERGE`: Performs insert/update/delete operations based on matching conditions
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;