[Solved] You want to find departments where the highest employee salary is greater than the average salary of all employees in that department. Which query correctly uses a correlated subquery to achieve this? | PostgreSQL

PostgreSQL - Subqueries in PostgreSQL

You want to find departments where the highest employee salary is greater than the average salary of all employees in that department. Which query correctly uses a correlated subquery to achieve this?

ASELECT d.name FROM departments d WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.id AND e.salary > (SELECT AVG(salary) FROM employees));

BSELECT d.name FROM departments d WHERE MAX(e.salary) > AVG(e.salary) FROM employees e WHERE e.department_id = d.id;

CSELECT d.name FROM departments d WHERE (SELECT MAX(e.salary) FROM employees e WHERE e.department_id = d.id) > (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department_id = d.id);

DSELECT d.name FROM departments d JOIN employees e ON e.department_id = d.id WHERE e.salary > (SELECT AVG(salary) FROM employees);

Step-by-Step Solution

Solution:

Step 1: Understand the requirement
We need to compare the max salary per department with the average salary per department.
Step 2: Analyze each option's logic
SELECT d.name FROM departments d WHERE (SELECT MAX(e.salary) FROM employees e WHERE e.department_id = d.id) > (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department_id = d.id); uses two correlated subqueries referencing d.id to get max and average salary per department, correctly implementing the condition.
Step 3: Identify errors in other options
SELECT d.name FROM departments d WHERE MAX(e.salary) > AVG(e.salary) FROM employees e WHERE e.department_id = d.id; has invalid syntax mixing aggregation and WHERE clause. SELECT d.name FROM departments d JOIN employees e ON e.department_id = d.id WHERE e.salary > (SELECT AVG(salary) FROM employees); compares employee salary to overall average, not per department. SELECT d.name FROM departments d WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.id AND e.salary > (SELECT AVG(salary) FROM employees)); compares employee salary to overall average, not per department.
Final Answer:
SELECT d.name FROM departments d WHERE (SELECT MAX(e.salary) FROM employees e WHERE e.department_id = d.id) > (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department_id = d.id); -> Option C
Quick Check:
Use correlated subqueries for per-department aggregates [OK]

Quick Trick: Use correlated subqueries for per-group comparisons [OK]

Common Mistakes:

Mixing aggregation without GROUP BY
Comparing to overall average instead of per department
Incorrect syntax for aggregation in WHERE clause

Master "Subqueries in PostgreSQL" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

You want to find departments where the highest employee salary is greater than the average salary of all employees in that department. Which query correctly uses a correlated subquery to achieve this?

Step 1: Understand the requirement

Step 2: Analyze each option's logic

Step 3: Identify errors in other options

Final Answer:

Quick Check:

Want More Practice?