Certified Data Engineer Associate Exam — Question 21

Exam Certified Data Engineer Associate All QuestionsBrowse all questions from this exam

Question 21

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;

Correct Answer: B

To create a new table all_transactions that contains all records from march_transactions and april_transactions without duplicate records, the UNION operator should be used. The UNION operator combines the result sets of the two tables while automatically removing duplicate records. Therefore, the correct command is: CREATE TABLE all_transactions AS SELECT * FROM march_transactions UNION SELECT * FROM april_transactions.

Discussion

ezeikOption: B

UNION [ALL | DISTINCT] Returns the result of subquery1 plus the rows of subquery2`. If ALL is specified duplicate rows are preserved. If DISTINCT is specified the result does not contain any duplicate rows. This is the default. https://docs.databricks.com/en/sql/language-manual/sql-ref-syntax-qry-select-setops.html#examples

SerGreyOption: B

B is correct

XiltroXOption: B

Answer is correct

awofalusOption: B

Correct: B

vctrhugoOption: B

B. CREATE TABLE all_transactions AS SELECT * FROM march_transactions UNION SELECT * FROM april_transactions; To create a new table all_transactions that contains all records from march_transactions and april_transactions without duplicate records, you should use the UNION operator, as shown in option B. This operator combines the result sets of the two tables while automatically removing duplicate records.

AtnafuOption: B

B CREATE TABLE all_transactions AS SELECT * FROM march_transactions UNION SELECT * FROM april_transactions;

prasiosoOption: B

Answer is B.

surrabhi_4Option: B

option B