PostgreSQL - Subqueries in PostgreSQL
You want to list each product's name and its price difference from the average price of all products. Which query correctly uses a scalar subquery to achieve this?
You want to list each product's name and its price difference from the average price of all products. Which query correctly uses a scalar subquery to achieve this?
hard📝 Application Q15 of 15
Step-by-Step Solution
Solution:
Step 1: Understand the goal
We want each product's price minus the average price of all products.Step 2: Analyze each option
SELECT name, price - (SELECT AVG(price) FROM products) AS price_diff FROM products; correctly uses a scalar subquery to get AVG(price) once and subtracts it from each product's price. SELECT name, price - AVG(price) FROM products; lacks GROUP BY and will error. SELECT name, price - (SELECT price FROM products) AS price_diff FROM products; returns multiple prices, causing error. SELECT name, price - (SELECT SUM(price) FROM products) AS price_diff FROM products; subtracts sum, not average, which is incorrect.Final Answer:
SELECT name, price - (SELECT AVG(price) FROM products) AS price_diff FROM products; -> Option BQuick Check:
Scalar subquery for AVG(price) = SELECT name, price - (SELECT AVG(price) FROM products) AS price_diff FROM products; [OK]
Quick Trick: Use scalar subquery for single aggregate value [OK]
Common Mistakes:
- Using aggregate without GROUP BY causing error
- Subquery returning multiple rows instead of one
- Confusing SUM with AVG in calculation
Master "Subqueries in PostgreSQL" in PostgreSQL
Want More Practice?
15+ quiz questions · All difficulty levels · Free
Free Signup - Practice All QuestionsMore PostgreSQL Quizzes