Bird
0
0

Identify the error in the following correlated subquery:

medium📝 Debug Q14 of 15
SQL - Subqueries
Identify the error in the following correlated subquery:
SELECT c.customer_id FROM customers c WHERE c.orders_count > (SELECT AVG(o.orders_count) FROM orders o WHERE o.customer_id = c.customer_id);
AThe subquery will return multiple rows causing an error
BThe subquery uses a wrong table alias 'o' which is not defined
CThe subquery compares orders_count incorrectly; should use SUM instead of AVG
DThe subquery references the outer query correctly; no error
Step-by-Step Solution
Solution:

  1. Step 1: Analyze correlation

    The subquery correctly uses 'c.customer_id' from the outer query in its WHERE clause.

  2. Step 2: Check aggregation and output

    AVG(o.orders_count) is an aggregate that returns a single scalar value, even for customers with multiple orders.

  3. Final Answer:

    The subquery references the outer query correctly; no error -> Option D

  4. Quick Check:

    Subquery must return single value for comparison [OK]
Quick Trick: Ensure subquery returns one value for comparison [OK]
Common Mistakes:
MISTAKES
  • Thinking AVG returns multiple rows without GROUP BY
  • Assuming alias 'o' is undefined
  • Believing SUM is needed instead of AVG

Want More Practice?

15+ quiz questions · All difficulty levels · Free

Free Signup - Practice All Questions
More SQL Quizzes
Aggregate Functions - COUNT function behavior - Quiz 12easy GROUP BY and HAVING - GROUP BY single column - Quiz 4medium INNER JOIN - INNER JOIN syntax - Quiz 9hard INNER JOIN - INNER JOIN with ON condition - Quiz 4medium LEFT and RIGHT JOIN - LEFT JOIN vs RIGHT JOIN decision - Quiz 10hard Set Operations - EXCEPT (MINUS) for differences - Quiz 11easy Subqueries - Subquery with EXISTS operator - Quiz 10hard Table Constraints - Composite primary keys - Quiz 10hard Views - Updatable views and limitations - Quiz 14medium Views - Updatable views and limitations - Quiz 3easy