SQL - Common Table Expressions (CTEs)
You want to calculate the average salary of employees in each department using a
WITH clause. Which query correctly uses a CTE to achieve this?You want to calculate the average salary of employees in each department using a WITH clause. Which query correctly uses a CTE to achieve this?
hard📝 Application Q8 of 15
Step-by-Step Solution
Solution:
Step 1: Understand the goal
We want average salary per department using a CTE.Step 2: Check each option's correctness
WITH dept_avg AS (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id) SELECT * FROM dept_avg; correctly defines a CTE with department_id and avg_salary grouped by department_id, then selects from it.
WITH dept_avg (SELECT department_id, AVG(salary) FROM employees) SELECT * FROM dept_avg; misses AS and parentheses.
WITH dept_avg AS SELECT department_id, AVG(salary) FROM employees GROUP BY department_id SELECT * FROM dept_avg; misses parentheses.
WITH dept_avg AS (SELECT department_id, salary FROM employees) SELECT AVG(salary) FROM dept_avg GROUP BY department_id; calculates average outside CTE but does not select department_id, producing unlabeled averages.Final Answer:
WITH dept_avg AS (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id) SELECT * FROM dept_avg; -> Option BQuick Check:
CTE with aggregation and GROUP BY inside parentheses [OK]
Quick Trick: Put aggregation and GROUP BY inside CTE parentheses [OK]
Common Mistakes:
- Omitting AS or parentheses
- Doing aggregation outside CTE incorrectly
Master "Common Table Expressions (CTEs)" in SQL
Want More Practice?
15+ quiz questions · All difficulty levels · Free
Free Signup - Practice All QuestionsMore SQL Quizzes