Bird
0
0

You want to calculate the average salary per department and then find departments with average salary above 5000. Which query correctly uses multiple CTEs to achieve this?

hard📝 Application Q8 of 15
SQL - Common Table Expressions (CTEs)
You want to calculate the average salary per department and then find departments with average salary above 5000. Which query correctly uses multiple CTEs to achieve this?
AWITH dept_avg AS (SELECT dept_id, salary FROM employees GROUP BY dept_id), high_avg AS (SELECT dept_id FROM dept_avg WHERE avg_sal > 5000) SELECT * FROM high_avg;
BWITH dept_avg AS (SELECT dept_id, AVG(salary) AS avg_sal FROM employees), high_avg AS (SELECT dept_id FROM dept_avg WHERE avg_sal > 5000) SELECT * FROM high_avg;
CWITH dept_avg AS (SELECT dept_id, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id), high_avg AS (SELECT dept_id FROM dept_avg WHERE avg_sal > 5000) SELECT * FROM high_avg;
DWITH dept_avg AS (SELECT dept_id, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id) SELECT * FROM dept_avg WHERE avg_sal > 5000;
Step-by-Step Solution
Solution:

  1. Step 1: Calculate average salary per department

    dept_avg CTE correctly groups by dept_id and calculates AVG(salary).

  2. Step 2: Filter departments with avg_sal > 5000

    high_avg CTE selects dept_id from dept_avg where avg_sal > 5000.

  3. Step 3: Select from high_avg

    The final SELECT returns departments meeting the condition.

  4. Final Answer:

    Query with two CTEs: dept_avg and high_avg, filtering avg_sal > 5000 -> Option C

  5. Quick Check:

    Multiple CTEs can chain calculations and filters [OK]
Quick Trick: Use one CTE for aggregation, next for filtering [OK]
Common Mistakes:
  • Omitting GROUP BY in aggregation
  • Not chaining CTEs properly
  • Using wrong columns in GROUP BY

Want More Practice?

15+ quiz questions · All difficulty levels · Free

Free Signup - Practice All Questions
More SQL Quizzes
Common Table Expressions (CTEs) - Recursive CTE for series generation - Quiz 10hard Common Table Expressions (CTEs) - Recursive CTE for series generation - Quiz 9hard Indexes and Query Performance - Why indexes matter - Quiz 4medium Indexes and Query Performance - Composite index and column order - Quiz 2easy Indexes and Query Performance - When indexes help and when they hurt - Quiz 12easy Transactions and Data Integrity - Savepoints within transactions - Quiz 10hard Transactions and Data Integrity - ACID properties mental model - Quiz 2easy Triggers - DELETE trigger - Quiz 3easy Window Functions Fundamentals - OVER clause with ORDER BY - Quiz 3easy Window Functions Fundamentals - Why window functions are needed - Quiz 10hard