Bird
0
0

How can you optimize a correlated subquery that calculates the average salary per department for a large employee table?

hard📝 Application Q9 of 15
SQL - Subqueries
How can you optimize a correlated subquery that calculates the average salary per department for a large employee table?
ARewrite the subquery as a scalar subquery without correlation
BPrecompute average salaries per department in a temporary table and join it
CUse a correlated subquery with an index on employee name
DRemove the subquery and use a WHERE clause only
Step-by-Step Solution
Solution:

  1. Step 1: Understand correlated subquery cost

    Correlated subqueries run repeatedly, which is expensive on large tables.

  2. Step 2: Use precomputed aggregation

    Precomputing averages per department in a temp table and joining reduces repeated calculations.

  3. Final Answer:

    Precompute average salaries per department in a temporary table and join it -> Option B

  4. Quick Check:

    Precompute aggregates to optimize correlated subqueries [OK]
Quick Trick: Replace repeated subqueries with joins on precomputed results [OK]
Common Mistakes:
MISTAKES
  • Ignoring performance impact of correlated subqueries
  • Using indexes on irrelevant columns
  • Removing subquery without replacement

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