SQL Exercises: Salary exceeds 50% of their departments total salary

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

From the following tables, write a SQL query to find those employees whose salaries exceed 50% of their department's total salary bill. Return first name, last name.

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 (e1.first_name, e1.last_name) from the 'employees' table, aliased as e1
SELECT e1.first_name, e1.last_name 

-- Filtering rows based on the condition that the 'salary' is greater than half the sum of salaries within the same department
FROM employees e1 

-- Subquery to calculate half the sum of salaries for each department in the 'employees' table, aliased as e2
WHERE salary > 

  (SELECT (SUM(salary)) * 0.5 

   -- From the 'employees' table, aliased as e2
   FROM employees e2 

   -- Matching rows based on the equality of 'department_id' between e1 and e2
   WHERE e1.department_id = e2.department_id
  );

Sample Output:

first_name	last_name
Kimberely	Grant
Jennifer	Whalen
Michael		Hartstein
Susan		Mavris
Hermann		Baer
Shelley		Higgins

Code Explanation:

The said query in SQL that retrieves the first name and last name of employees whose salary is greater than 50% of the total sum of salaries in their department.
The WHERE clause specifies the condition that filters the rows returned by the query. In this case, the salary of the employee must be greater than 50% of the sum of all salaries in the same department.

Visual Presentation:

Alternative Statements:

Using Subquery with IN:

SELECT e1.first_name, e1.last_name 
FROM employees e1
WHERE salary > 0.5 * (
    SELECT SUM(salary)
    FROM employees e2
    WHERE e1.department_id = e2.department_id
    GROUP BY department_id
);

Using Subquery with JOIN:

SELECT e1.first_name, e1.last_name 
FROM employees e1
JOIN (
    SELECT department_id, SUM(salary) as total_salary
    FROM employees
    GROUP BY department_id
) e2
ON e1.department_id = e2.department_id
WHERE e1.salary > e2.total_salary * 0.5;

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Manager who works for a department based in the US.
Next SQL Exercise: Find out the details of employees who are managers.