You want to list each product with its price and the average price of all products in the same category. Which query correctly uses a scalar subquery in the SELECT clause to achieve this?
hard📝 Application Q8 of 15
SQL - Subqueries
You want to list each product with its price and the average price of all products in the same category. Which query correctly uses a scalar subquery in the SELECT clause to achieve this?
ASELECT product_name, price, AVG(price) FROM products GROUP BY category_id;
BSELECT product_name, price, (SELECT AVG(price) FROM products p2 WHERE p2.category_id = p1.category_id) AS avg_category_price FROM products p1;
CSELECT product_name, price, (SELECT price FROM products WHERE category_id = category_id) AS avg_category_price FROM products;
DSELECT product_name, price, (SELECT AVG(price) FROM products) AS avg_category_price FROM products;
Step-by-Step Solution
Solution:
Step 1: Understand requirement
We need average price per category for each product row.
Step 2: Analyze options
SELECT product_name, price, (SELECT AVG(price) FROM products p2 WHERE p2.category_id = p1.category_id) AS avg_category_price FROM products p1; uses correlated scalar subquery correctly to compute average per category.
Final Answer:
SELECT product_name, price, (SELECT AVG(price) FROM products p2 WHERE p2.category_id = p1.category_id) AS avg_category_price FROM products p1; -> Option B
Quick Check:
Correlated scalar subquery computes per-row category average [OK]
Quick Trick:Use correlated scalar subquery for per-row aggregates [OK]
Common Mistakes:
MISTAKES
Using uncorrelated subquery returning overall average
Using GROUP BY without joining to outer query
Incorrect WHERE clause causing no correlation
Master "Subqueries" in SQL
9 interactive learning modes - each teaches the same concept differently
