SQL - Subqueries
You want to find employees who earn more than the average salary in their department. Which query correctly uses a subquery to achieve this?
You want to find employees who earn more than the average salary in their department. Which query correctly uses a subquery to achieve this?
hard📝 Application Q8 of 15
Step-by-Step Solution
Solution:
Step 1: Understand the requirement
We need to compare each employee's salary to the average salary of their own department.Step 2: Analyze each option
SELECT name FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id) uses a correlated subquery referencing the employee's department_id to calculate average salary per department correctly.Final Answer:
SELECT name FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id) -> Option DQuick Check:
Correlated subquery filters by department average [OK]
Quick Trick: Use correlated subqueries to compare within groups [OK]
Common Mistakes:
MISTAKES- Using aggregate functions without GROUP BY
- Not correlating subquery to outer query
- Using ALL incorrectly for this case
Master "Subqueries" in SQL
Want More Practice?
15+ quiz questions · All difficulty levels · Free
Free Signup - Practice All QuestionsMore SQL Quizzes