Level 3 - Question 2: Correlated Subquery

Scenario

Your HR department wants to identify employees who earn more than the average salary in their department.

Task

Write a query using a correlated subquery to find employee names, departments, and salaries where the salary is greater than the average salary of their department.

Sample Schema: `employees`

emp_id	name	department	salary
1	John Doe	IT	75000
2	Jane Smith	IT	65000
3	Mike Johnson	HR	55000
4	Sarah Lee	HR	60000
5	Tom Brown	IT	80000

Solution:

SELECT name, department, salary
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department = e1.department
)
ORDER BY department, salary DESC;

This correlated subquery calculates the average salary for each department and compares each employee's salary against their department's average. The outer query references the department from the inner query.

Previous Question Next Question

Level 3 - Question 2: Correlated Subquery

Scenario

Task

Sample Schema: employees

Solution:

Sample Schema: `employees`