SQL Exercises: Salary exceeds the average salary of any department

Last update on March 12 2025 12:11:07 (UTC/GMT +8 hours)

From the following table, write a SQL query to find those employees whose salaries are higher than the average for all departments. Return employee ID, first name, last name, job ID.

Sample table: employees

+-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
| EMPLOYEE_ID | FIRST_NAME  | LAST_NAME   | EMAIL    | PHONE_NUMBER       | HIRE_DATE  | JOB_ID     | SALARY   | COMMISSION_PCT | MANAGER_ID | DEPARTMENT_ID |
+-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
|         100 | Steven      | King        | SKING    | 515.123.4567       | 2003-06-17 | AD_PRES    | 24000.00 |           0.00 |          0 |            90 |
|         101 | Neena       | Kochhar     | NKOCHHAR | 515.123.4568       | 2005-09-21 | AD_VP      | 17000.00 |           0.00 |        100 |            90 |
|         102 | Lex         | De Haan     | LDEHAAN  | 515.123.4569       | 2001-01-13 | AD_VP      | 17000.00 |           0.00 |        100 |            90 |
|         103 | Alexander   | Hunold      | AHUNOLD  | 590.423.4567       | 2006-01-03 | IT_PROG    |  9000.00 |           0.00 |        102 |            60 |
|         104 | Bruce       | Ernst       | BERNST   | 590.423.4568       | 2007-05-21 | IT_PROG    |  6000.00 |           0.00 |        103 |            60 |
|         105 | David       | Austin      | DAUSTIN  | 590.423.4569       | 2005-06-25 | IT_PROG    |  4800.00 |           0.00 |        103 |            60 |
|         106 | Valli       | Pataballa   | VPATABAL | 590.423.4560       | 2006-02-05 | IT_PROG    |  4800.00 |           0.00 |        103 |            60 |
|         107 | Diana       | Lorentz     | DLORENTZ | 590.423.5567       | 2007-02-07 | IT_PROG    |  4200.00 |           0.00 |        103 |            60 |
|         108 | Nancy       | Greenberg   | NGREENBE | 515.124.4569       | 2002-08-17 | FI_MGR     | 12008.00 |           0.00 |        101 |           100 |
|         109 | Daniel      | Faviet      | DFAVIET  | 515.124.4169       | 2002-08-16 | FI_ACCOUNT |  9000.00 |           0.00 |        108 |           100 |
|         110 | John        | Chen        | JCHEN    | 515.124.4269       | 2005-09-28 | FI_ACCOUNT |  8200.00 |           0.00 |        108 |           100 |
....................
+-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+

View the table

Sample Solution:

-- Selecting specific columns (employee_id, first_name, last_name, job_id) from the 'employees' table
SELECT employee_id, first_name, last_name, job_id  

-- Filtering rows based on the condition that the 'salary' is greater than all average salaries in the result set of a subquery
FROM employees  

-- Subquery to calculate average salaries grouped by 'department_id' in the 'employees' table
WHERE salary > ALL  

  (SELECT AVG(salary)  
   FROM employees  

   -- Grouping salaries by 'department_id'
   GROUP BY department_id 
  );

Sample Output:

employee_id	first_name	last_name	job_id
100		Steven		King		AD_PRES

Code Explanation:

The said query in SQL that retrieves the employee ID, first name, last name, and job ID of all employees who have a salary higher than the average salary of all employees in each department. In the "WHERE" clause, the "GROUP BY" clause and the "AVG" function are used to calculate the average salary of all employees in each department. Using the keyword "ALL" in the outer query, the salary of each employee is then compared with the result of the subquery.

Visual Presentation:

Alternative Statements:

Using Subquery in the WHERE Clause:

SELECT employee_id, first_name, last_name, job_id 
FROM employees
WHERE salary > (
    SELECT MAX(avg_salary)
    FROM (
        SELECT AVG(salary) as avg_salary
        FROM employees
        GROUP BY department_id
    ) dept_avg
);

Using JOIN:

SELECT DISTINCT e1.employee_id, e1.first_name, e1.last_name, e1.job_id 
FROM employees e1
JOIN employees e2 ON e1.department_id = e2.department_id
WHERE e1.salary > ALL (
    SELECT AVG(salary)
    FROM employees
    WHERE e1.department_id = e2.department_id
    GROUP BY department_id
);

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.

Previous SQL Exercise: Employees whose salary is higher than title is PU_MAN.
Next SQL Exercise: Employees whose salary is more than 3700.