DEA-C01 Question 334
Single answerYou are designing a data pipeline that ingests customer orders into an Amazon RDS PostgreSQL database. You need to generate an analytical report that combines customer details from the 'customers' table and their corresponding order details from the 'orders' table. The report should include customer names and total order amounts for each customer. Which SQL query would correctly retrieve this data?
- A
SELECT c.customer_name, SUM(o.order_amount) FROM customers c JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_name;
- B
SELECT c.customer_name, o.order_amount FROM customers c FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;
- C
SELECT c.customer_name, SUM(o.order_amount) FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id GROUP BY o.order_amount;
- D
SELECT customer_name, order_amount FROM customers, orders WHERE customers.customer_id = orders.customer_id;
Show answer and explanation
Correct answer: A
Explanation
The correct query must combine both tables on the 'customer_id', calculate the total order amount for each customer, and group the results by customer name. An INNER JOIN is appropriate here because the focus is on customers who have placed orders. The use of an aggregate function (SUM) and a GROUP BY clause ensures the results are summarized correctly.
- A. Correct.
This query correctly uses an INNER JOIN to combine the 'customers' and 'orders' tables on the 'customer_id' field, calculates the total order amount using SUM, and groups the results by customer name. This fulfills the requirements of the scenario.
- B. Incorrect.
This query uses a FULL OUTER JOIN, which would include customers with no orders and orders with no matching customers. However, it does not group the results or calculate the total order amount, so it does not meet the requirements.
- C. Incorrect.
This query uses a LEFT JOIN and attempts to calculate the total order amount, but grouping by 'o.order_amount' is incorrect because it breaks the aggregation logic, leading to invalid results.
- D. Incorrect.
This query uses a Cartesian product (implicit join) rather than an explicit join, which can generate an incorrect and overly large result set. It also does not aggregate or group the data as required by the scenario.