Exam Certified Data Engineer Associate topic 1 question 21 discussion

Actual exam question from Databricks's Certified Data Engineer Associate

Question #: 21
Topic #: 1

[All Certified Data Engineer Associate 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?

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;

Show Suggested Answer

Suggested Answer: B 🗳️

by XiltroX at April 1, 2023, 5:54 p.m.

Comments

Submit Cancel

SoumyaHK

2 months, 2 weeks ago

Selected Answer: B

The response is B

upvoted 1 times

...

80370eb

11 months ago

Selected Answer: B

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

upvoted 1 times

...

SerGrey

1 year, 6 months ago

Selected Answer: B

B is correct

upvoted 2 times

...

awofalus

1 year, 8 months ago

Selected Answer: B

Correct: B

upvoted 1 times

...

ezeik

1 year, 9 months ago

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

upvoted 4 times

...

vctrhugo

1 year, 10 months ago

Selected Answer: 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.

upvoted 1 times

...