Databricks Certified Data Engineer - Associate
Get started today
Ultimate access to all questions.
Deep dive into the quiz with AI chat providers.
We prepare a focused prompt with your quiz and certificate details so each AI can offer a more tailored, in-depth explanation.
A data engineer is working with two tables. Each of these tables is displayed below in its entirety.
sales
| customer_id | spend | units |
|---|---|---|
| a1 | 28.94 | 7 |
| a3 | 874.12 | 23 |
| a4 | 8.99 | 1 |
favorite_stores
| customer_id | store_id |
|---|---|
| a1 | s1 |
| a2 | s1 |
| a4 | s2 |
The data engineer runs the following query to join these tables together:
SELECT
sales.customer_id,
sales.spend,
favorite_stores.store_id
FROM sales
LEFT JOIN favorite_stores
ON sales.customer_id = favorite_stores.customer_id;
SELECT
sales.customer_id,
sales.spend,
favorite_stores.store_id
FROM sales
LEFT JOIN favorite_stores
ON sales.customer_id = favorite_stores.customer_id;
Which of the following will be returned by the above query ?
Real Exam
Community
KKeng
0
A
| customer_id | spend | store_id |
|---|---|---|
| a1 | 28.94 | s1 |
| a4 | 8.99 | s2 |
B
| customer_id | spend | units | store_id |
|---|---|---|---|
| a1 | 28.94 | 7 | s1 |
| a4 | 8.99 | 1 | s2 |
C
| customer_id | spend | store_id |
|---|---|---|
| a1 | 28.94 | s1 |
| a3 | 874.12 | NULL |
| a4 | 8.99 | s2 |
D
| customer_id | spend | store_id |
|---|---|---|
| a1 | 28.94 | s1 |
| a2 | NULL | s1 |
| a3 | 874.12 | NULL |
| a4 | 8.99 | s2 |
E
| customer_id | spend | store_id |
|---|---|---|
| a1 | 28.94 | s1 |
| a2 | NULL | s1 |
| a4 | 8.99 | s2 |
Explanation:
The correct answer is C because a LEFT JOIN preserves all records from the left table and includes matching records from the right table. When there is no match in the right table, NULL values are returned for the right table's columns. In this case:
- a1 has matching data in both tables (spend 28.94 and store_id s1)
- a3 exists only in the left table with spend 874.12, so store_id is NULL
- a4 has matching data in both tables (spend 8.99 and store_id s2)
- a2 is not included because it doesn't appear to exist in the left table based on the query results
Option A is incorrect because it's missing a3. Option B is incorrect because it includes a 'units' column that shouldn't be in the result. Option D is incorrect because it includes a2 which doesn't appear to be in the left table. Option E is incorrect because it's missing a3.
Comments
Loading comments...