PostgreSQL - Subqueries in PostgreSQL
You want to find employees who earn more than the average salary in their own department. Which query correctly uses a subquery to achieve this?
You want to find employees who earn more than the average salary in their own department. Which query correctly uses a subquery to achieve this?
hard📝 Application Q15 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, salary, department_id FROM employees WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = employees.department_id); uses a correlated subquery that calculates average salary per employee's department. SELECT name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); compares to overall average, ignoring departments. SELECT name FROM employees WHERE salary > ALL (SELECT salary FROM employees); compares salary to all salaries, not averages. SELECT name FROM employees WHERE salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id); compares to a list of averages but uses ANY incorrectly.Final Answer:
SELECT name, salary, department_id FROM employees WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = employees.department_id); -> Option AQuick Check:
Correlated subquery compares salary to department average [OK]
Quick Trick: Use correlated subqueries to compare within groups [OK]
Common Mistakes:
- Comparing to overall average instead of department average
- Using ALL or ANY incorrectly with averages
- Not correlating subquery with outer query
Master "Subqueries in PostgreSQL" in PostgreSQL
Want More Practice?
15+ quiz questions · All difficulty levels · Free
Free Signup - Practice All QuestionsMore PostgreSQL Quizzes