How can you use CTE referencing another CTE to find employees who earn more than the average salary?
hard📝 Application Q9 of 15
SQL - Common Table Expressions (CTEs)
How can you use CTE referencing another CTE to find employees who earn more than the average salary? Assuming table employees(id, salary). Which query is correct?
AWITH avg_salary AS (SELECT AVG(salary) AS avg_sal FROM employees), high_earners AS (SELECT id FROM employees WHERE salary > avg_sal) SELECT * FROM high_earners;
BWITH avg_salary AS (SELECT AVG(salary) AS avg_sal FROM employees), high_earners AS (SELECT id FROM employees, avg_salary WHERE salary > avg_sal) SELECT * FROM high_earners;
CWITH avg_salary AS (SELECT AVG(salary) AS avg_sal FROM employees) SELECT id FROM employees WHERE salary > avg_sal;
DWITH high_earners AS (SELECT id FROM employees WHERE salary > (SELECT AVG(salary) FROM employees)) SELECT * FROM high_earners;
Step-by-Step Solution
Solution:
Step 1: Calculate average salary in first CTE
avg_salary CTE computes average salary correctly.
Step 2: Reference avg_salary in second CTE
high_earners CTE joins employees with avg_salary to filter salaries above average.
Step 3: Final SELECT returns high earners
Query returns employee ids earning more than average.
Final Answer:
WITH avg_salary AS (SELECT AVG(salary) AS avg_sal FROM employees), high_earners AS (SELECT id FROM employees, avg_salary WHERE salary > avg_sal) SELECT * FROM high_earners; -> Option B
Quick Check:
CTE join for avg comparison = WITH avg_salary AS (SELECT AVG(salary) AS avg_sal FROM employees), high_earners AS (SELECT id FROM employees, avg_salary WHERE salary > avg_sal) SELECT * FROM high_earners; [OK]
Quick Trick:Join avg CTE with employees to compare salaries [OK]
Common Mistakes:
Using avg_sal without joining or cross referencing
Trying to use avg_sal directly in WHERE without join
Not using CTE for average calculation
Master "Common Table Expressions (CTEs)" in SQL
9 interactive learning modes - each teaches the same concept differently
