You have tables: Orders(order_id, customer_id), Customers(customer_id, name), Payments(payment_id, order_id, amount). Write a query to find each customer's name and the total amount they paid across all orders. Which query is correct?
ASELECT Customers.name, SUM(Payments.amount) FROM Customers JOIN Orders ON Customers.customer_id = Orders.customer_id JOIN Payments ON Orders.order_id = Payments.order_id GROUP BY Customers.name;
BSELECT Customers.name, Payments.amount FROM Customers JOIN Orders ON Customers.customer_id = Orders.customer_id JOIN Payments ON Orders.order_id = Payments.order_id;
CSELECT Customers.name, SUM(Payments.amount) FROM Customers, Orders, Payments WHERE Customers.customer_id = Orders.customer_id AND Orders.order_id = Payments.order_id;
DSELECT Customers.name, SUM(Payments.amount) FROM Customers JOIN Payments ON Customers.customer_id = Payments.order_id GROUP BY Customers.name;
Step-by-Step Solution
Solution:
Step 1: Understand the relationships
Customers link to Orders by customer_id; Orders link to Payments by order_id.
Step 2: Check aggregation and grouping
We need total payment per customer, so SUM and GROUP BY Customers.name are required.
Step 3: Validate options
SELECT Customers.name, SUM(Payments.amount) FROM Customers JOIN Orders ON Customers.customer_id = Orders.customer_id JOIN Payments ON Orders.order_id = Payments.order_id GROUP BY Customers.name; correctly joins tables and groups by customer name with SUM of payments. SELECT Customers.name, Payments.amount FROM Customers JOIN Orders ON Customers.customer_id = Orders.customer_id JOIN Payments ON Orders.order_id = Payments.order_id; lacks aggregation. SELECT Customers.name, SUM(Payments.amount) FROM Customers, Orders, Payments WHERE Customers.customer_id = Orders.customer_id AND Orders.order_id = Payments.order_id; misses GROUP BY. SELECT Customers.name, SUM(Payments.amount) FROM Customers JOIN Payments ON Customers.customer_id = Payments.order_id GROUP BY Customers.name; joins wrong columns.
Final Answer:
SELECT Customers.name, SUM(Payments.amount) FROM Customers JOIN Orders ON Customers.customer_id = Orders.customer_id JOIN Payments ON Orders.order_id = Payments.order_id GROUP BY Customers.name; -> Option A
Quick Check:
JOIN + SUM + GROUP BY = correct total per customer [OK]
Quick Trick:Use JOINs with GROUP BY and SUM for totals [OK]
Common Mistakes:
MISTAKES
Missing GROUP BY when using SUM
Joining tables on wrong columns
Selecting aggregated and non-aggregated columns without GROUP BY
Master "Advanced Joins" in SQL
9 interactive learning modes - each teaches the same concept differently
