[Solved] You want to find departments where the average employee salary is higher than the company-wide average. Which query correctly uses a correlated subquery to achieve this? | PostgreSQL

PostgreSQL - Subqueries in PostgreSQL

You want to find departments where the average employee salary is higher than the company-wide average. Which query correctly uses a correlated subquery to achieve this?

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

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

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

DSELECT d.name FROM departments d WHERE AVG(salary) > (SELECT AVG(salary) FROM employees);

Step-by-Step Solution

Solution:

Step 1: Understand the requirement
We compare each department's average salary to the overall average salary.
Step 2: Check query correctness
SELECT d.name FROM departments d WHERE (SELECT AVG(salary) FROM employees e WHERE e.department_id = d.id) > (SELECT AVG(salary) FROM employees); correctly uses a correlated subquery for department average and independent subquery for company average.
Final Answer:
SELECT d.name FROM departments d WHERE (SELECT AVG(salary) FROM employees e WHERE e.department_id = d.id) > (SELECT AVG(salary) FROM employees); -> Option B
Quick Check:
Correct correlated comparison query = SELECT d.name FROM departments d WHERE (SELECT AVG(salary) FROM employees e WHERE e.department_id = d.id) > (SELECT AVG(salary) FROM employees); [OK]

Quick Trick: Use correlated subquery for per-department average, independent for overall. [OK]

Common Mistakes:

Using aggregate without subquery in WHERE.
Swapping comparison sides incorrectly.
Misplacing correlation in subqueries.

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 average employee salary is higher than the company-wide average. Which query correctly uses a correlated subquery to achieve this?

Step 1: Understand the requirement

Step 2: Check query correctness

Final Answer:

Quick Check:

Want More Practice?