Databricks Data Engineer Associate Question 181
Select 4You are working with two tables in Databricks: customers and orders. The customers table contains customer information with columns customer_id and customer_name. The orders table contains order details with columns order_id, customer_id, and order_amount. You execute the following query:
SELECT c.customer_id, c.customer_name, o.order_id, o.order_amount FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id;
Given the following data in the tables:
customers:
| customer_id | customer_name |
|---|---|
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie |
orders:
| order_id | customer_id | order_amount |
|---|---|---|
| 101 | 1 | 50 |
| 102 | 1 | 30 |
| 103 | 2 | 70 |
What will be the result of this query?
- A
customer_id customer_name order_id order_amount 1 Alice 101 50 - B
customer_id customer_name order_id order_amount 1 Alice 102 30 - C
customer_id customer_name order_id order_amount 2 Bob 103 70 - D
customer_id customer_name order_id order_amount 3 Charlie NULL NULL - E
customer_id customer_name order_id order_amount 2 Bob NULL NULL
Show answer and explanation
Correct answers: A, B, C, D
Explanation
The query performs a LEFT JOIN between customers and orders on the customer_id column. For customers with matching orders, all matching rows from the orders table are included. For customers without matching orders, a row is included with NULL values for the columns from the orders table. In this case, Alice has two matching orders, Bob has one matching order, and Charlie has no matching orders, resulting in four rows in the output.
- A. Correct.
This row is included because Alice (customer_id = 1) has a matching order (order_id = 101) in the
orderstable. - B. Correct.
This row is included because Alice (customer_id = 1) has a second matching order (order_id = 102) in the
orderstable. - C. Correct.
This row is included because Bob (customer_id = 2) has a matching order (order_id = 103) in the
orderstable. - D. Correct.
This row is included because Charlie (customer_id = 3) does not have any matching orders in the
orderstable. Since this is a LEFT JOIN, NULL values are returned for theorder_idandorder_amountcolumns for unmatched rows. - E. Incorrect.
This row is incorrect because Bob (customer_id = 2) has a matching order, so there will not be a row with NULL values for
order_idandorder_amount.