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 (not shown)
a4 8.99 1
customer_id spend units
a1 28.94 7
a3 874.1223 (not shown)
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 matching rows from the right table (favorite_stores). - If there's no match in the right table,
NULLvalues are returned for the right table's columns. - Rows from the right table that don't match the left table are not included.
Analysis:
- sales table has: a1, a3, a4
- favorite_stores table has: a1, a2, a4
Row-by-row analysis:
- a1 (from sales) → matches a1 in favorite_stores → store_id = s1
- a3 (from sales) → no match in favorite_stores → store_id = NULL
- a4 (from sales) → matches a4 in favorite_stores → store_id = s2
Why other options are incorrect:
- Option A: Includes a2 (which is not in sales table)
- Option B: Missing a3 (should be included in LEFT JOIN)
- Option D: Includes a2 (which is not in sales table)
Important Note:
The value 874.1223 from the sales table is displayed as 874.12 in Option C, which represents rounding for display purposes. This is acceptable in the context of the question.
Correct Result:
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
Comments
Loading comments...