How can you use a scalar subquery to find employees whose salary is above the average salary in their department?

hard📝 Application Q9 of 15

PostgreSQL - Subqueries in PostgreSQL

ASELECT name FROM employees WHERE salary > (SELECT salary FROM employees);

BSELECT name FROM employees WHERE salary > AVG(salary);

CSELECT name FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id);

DSELECT name FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

Step-by-Step Solution

Solution:

Step 1: Understand the problem
We want employees with salary greater than their department's average salary.
Step 2: Analyze the scalar subquery usage
SELECT name FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id); uses a correlated scalar subquery to compute AVG(salary) per employee's department.
Final Answer:
SELECT name FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id); -> Option D
Quick Check:
Correlated scalar subquery compares salary to department average [OK]

Quick Trick: Correlate subquery to outer query for per-group calculations [OK]

Common Mistakes:

Master "Subqueries in PostgreSQL" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Want More Practice?

15+ quiz questions · All difficulty levels · Free