sqlbeginner10 minutes

Find Employees with Salaries Above Department Average

Write an SQL query to find employees whose salaries are higher than the average salary of their respective departments.

Challenge prompt

Given two tables, Employees(employee_id, employee_name, department_id, salary) and Departments(department_id, department_name), write an SQL query to return the employee_id, employee_name, and salary of employees whose salary is greater than the average salary in their department.

Guidance

• Calculate the average salary per department using GROUP BY.
• Use a subquery or JOIN to compare each employee's salary with their department's average.
• Select only those employees whose salary is greater than the department average.

Hints

• Use the GROUP BY clause to find average salary for each department.
• A JOIN between Employees and the subquery with averages can help filter.
• Remember to compare salary values with the calculated average per department.

Starter code

SELECT employee_id, employee_name, salary FROM Employees WHERE salary > (SELECT AVG(salary) FROM Employees WHERE department_id = Employees.department_id);

Expected output

A table listing employee_id, employee_name, and salary of employees earning more than their department's average salary.

Core concepts

aggregate functionssubqueriesJOINsGROUP BY

Challenge a Friend

Send this duel to someone else and see if they can solve it.