APICS Certified Supply Chain Professional (CSCP) — Question 187

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?

Answer options

• 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;

Correct answer: B

Explanation

The correct answer is B because the UNION operation combines the results of two queries and eliminates duplicate records, which is needed here since the two tables have no overlapping data. Option A uses INNER JOIN, which would not include all records from both tables. Option C employs OUTER JOIN incorrectly, and option D uses INTERSECT which would only return common records, not all. Option E is not a valid SQL operation.