Databricks Certified Data Engineer - Associate
Get started today
Ultimate access to all questions.
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.1223 -
a4 8.99 1
customer_id spend units
a1 28.94 7
a3 874.1223 -
a4 8.99 1
favorite_stores
customer_id store_id
a1 s1
a2 s1
a4 s2
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;
Real Exam
Community
KKeng
A
customer_id spend store_id
a1 28.94 s1
a2 NULL s1
a4 8.99 s2
customer_id spend store_id
a1 28.94 s1
a2 NULL s1
a4 8.99 s2
B
customer_id spend store_id
a1 28.94 s1
a4 8.99 s2
customer_id spend store_id
a1 28.94 s1
a4 8.99 s2
C
customer_id spend store_id
a1 28.94 s1
a3 874.12 NULL
a4 8.99 s2
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
customer_id spend store_id
a1 28.94 s1
a2 NULL s1
a3 874.12 NULL
a4 8.99 s2
Explanation:
Explanation
This question tests understanding of LEFT JOIN behavior in SQL:
Key Concepts:
- LEFT JOIN returns ALL rows from the left table (sales), and the matched rows from the right table (favorite_stores).
- If there is no match in the right table, NULL values are returned for columns from the right table.
- The left table's rows are always included regardless of whether they have matches in the right table.
Analysis:
- sales table has: a1, a3, a4
- favorite_stores table has: a1, a2, a4
Row-by-row analysis:
- a1: Exists in both tables → matched row with store_id = s1
- a3: Exists only in sales table → no match in favorite_stores → store_id = NULL
- a4: Exists in both tables → matched row with store_id = s2
- a2: Exists only in favorite_stores table → NOT included because LEFT JOIN starts from sales table
Why Option D is correct:
- Includes all 3 rows from sales table: a1, a3, a4
- Shows NULL for store_id where no match exists (a3)
- Correctly shows a1 with s1 and a4 with s2
Why other options are incorrect:
- Option A: Incorrectly includes a2 (from right table only)
- Option B: Missing a3 row from sales table
- Option C: Missing a2 row (but a2 shouldn't be included anyway)
Note: The spend value for a3 is shown as 874.12 (rounded from 874.1223) in the output options, which is reasonable for display purposes.
Comments
Loading comments...