SQL - Subqueries
You want to find all customers who placed orders with a total amount greater than the average order amount. Which query correctly uses a subquery to achieve this?
You want to find all customers who placed orders with a total amount greater than the average order amount. Which query correctly uses a subquery to achieve this?
hard📝 Application Q15 of 15
Step-by-Step Solution
Solution:
Step 1: Understand the goal
We want customers with orders greater than the average order amount.Step 2: Check subquery usage
SELECT customer_id FROM orders WHERE total_amount > (SELECT AVG(total_amount) FROM orders); correctly uses a subquery to calculate average and compares each order's total_amount to it.Step 3: Identify errors in other options
SELECT customer_id FROM orders WHERE total_amount > AVG(total_amount); misuses AVG without subquery; C uses IN incorrectly; D has wrong comparison logic.Final Answer:
SELECT customer_id FROM orders WHERE total_amount > (SELECT AVG(total_amount) FROM orders); -> Option BQuick Check:
Subquery calculates average, main query compares amounts [OK]
Quick Trick: Use subquery to get average, compare in main query [OK]
Common Mistakes:
MISTAKES- Using aggregate functions without subqueries
- Misusing IN for single value comparisons
- Comparing with wrong operators or missing parentheses
Master "Subqueries" in SQL
Want More Practice?
15+ quiz questions · All difficulty levels · Free
Free Signup - Practice All QuestionsMore SQL Quizzes