You want to list all products and the number of orders for each, including products with zero orders. Given tables Product, Order, and junction table OrderProduct, which query correctly does this?
hard📝 Application Q9 of 15
SQL - Table Relationships
You want to list all products and the number of orders for each, including products with zero orders. Given tables Product, Order, and junction table OrderProduct, which query correctly does this?
ASELECT p.Name, COUNT(op.OrderID) FROM Product p FULL JOIN OrderProduct op ON p.ID = op.ProductID GROUP BY p.ID;
BSELECT p.Name, COUNT(op.OrderID) FROM Product p JOIN OrderProduct op ON p.ID = op.ProductID GROUP BY p.ID;
CSELECT p.Name, COUNT(op.OrderID) FROM Product p RIGHT JOIN OrderProduct op ON p.ID = op.ProductID GROUP BY p.ID;
DSELECT p.Name, COUNT(op.OrderID) FROM Product p LEFT JOIN OrderProduct op ON p.ID = op.ProductID GROUP BY p.ID;
Step-by-Step Solution
Solution:
Step 1: Use LEFT JOIN to include all products
LEFT JOIN keeps all products even if no matching orders exist.
Step 2: Use COUNT on order IDs to count orders per product
Counting order IDs from junction table shows number of orders per product.
Final Answer:
SELECT p.Name, COUNT(op.OrderID) FROM Product p LEFT JOIN OrderProduct op ON p.ID = op.ProductID GROUP BY p.ID; -> Option D
Quick Check:
LEFT JOIN + COUNT includes zero counts [OK]
Quick Trick:Use LEFT JOIN to include items with zero matches [OK]
Common Mistakes:
MISTAKES
Using INNER JOIN excludes products with zero orders
Using RIGHT JOIN incorrectly
Using FULL JOIN where not supported
Master "Table Relationships" in SQL
9 interactive learning modes - each teaches the same concept differently
