Given tables: Orders(id, customer_id, product_id), Customers(id, name), Products(id, name, price) Write a query to find customers who ordered products costing more than $100. Which query is correct?
ASELECT DISTINCT Customers.name FROM Customers JOIN Orders ON Customers.id = Orders.customer_id JOIN Products ON Orders.product_id = Products.id WHERE Products.price > 100;
BSELECT Customers.name FROM Customers LEFT JOIN Orders ON Customers.id = Orders.customer_id LEFT JOIN Products ON Orders.product_id = Products.id WHERE Products.price > 100;
CSELECT Customers.name FROM Customers JOIN Orders ON Customers.id = Orders.customer_id JOIN Products ON Orders.product_id = Products.id HAVING Products.price > 100;
DSELECT Customers.name FROM Customers JOIN Orders ON Customers.id = Orders.customer_id JOIN Products ON Orders.product_id = Products.id WHERE Orders.price > 100;
Step-by-Step Solution
Solution:
Step 1: Join Customers, Orders, and Products
Use INNER JOINs to link customers to their orders and products.
Step 2: Filter products with price > 100
WHERE clause filters products costing more than $100.
Step 3: Use DISTINCT to avoid duplicates
Customers may have multiple qualifying orders, so DISTINCT ensures unique names.
Final Answer:
SELECT DISTINCT Customers.name FROM Customers JOIN Orders ON Customers.id = Orders.customer_id JOIN Products ON Orders.product_id = Products.id WHERE Products.price > 100; -> Option A
Quick Check:
Correct filtering and unique customers = SELECT DISTINCT Customers.name FROM Customers JOIN Orders ON Customers.id = Orders.customer_id JOIN Products ON Orders.product_id = Products.id WHERE Products.price > 100; [OK]
Quick Trick:Use DISTINCT to avoid duplicate customers in joins [OK]
Common Mistakes:
MISTAKES
Using HAVING instead of WHERE for filtering non-aggregates
Filtering on wrong table or column
Missing DISTINCT causing duplicates
Master "Advanced Joins" in SQL
9 interactive learning modes - each teaches the same concept differently
