SQL Exercises: Employees earning more than departments minimum wage

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

From the following table, write a SQL query to find those employees who earn more than the minimum salary of a department of ID 40. Return first name, last name, salary, and department 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 (first_name, last_name, salary, department_id) from the 'employees' table
SELECT first_name, last_name, salary, department_id 

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

-- Subquery to find salaries where the 'department_id' is 40 in the 'employees' table
WHERE salary > ANY 
    (SELECT salary 
       FROM employees 
          WHERE department_id = 40);

Sample Output:

first_name	last_name	salary	department_id
Steven		King		24000.00	90
Neena		Kochhar		17000.00	90
Lex		De Haan		17000.00	90
Alexander	Hunold		9000.00		60
Nancy		Greenberg	12000.00	100
Daniel		Faviet		9000.00		100
John		Chen		8200.00		100
Ismael		Sciarra		7700.00		100
Jose Manuel	Urman		7800.00		100
Luis		Popp		6900.00		100
Den		Raphaely	11000.00	30
Matthew		Weiss		8000.00		50
Adam		Fripp		8200.00		50
Payam		Kaufling	7900.00		50
John		Russell		14000.00	80
Karen		Partners	13500.00	80
Alberto		Errazuriz	12000.00	80
Gerald		Cambrault	11000.00	80
Eleni		Zlotkey		10500.00	80
Peter		Tucker		10000.00	80
David		Bernstein	9500.00		80
Peter		Hall		9000.00		80
Christopher	Olsen		8000.00		80
Nanette		Cambrault	7500.00		80
Oliver		Tuvault		7000.00		80
Janette		King		10000.00	80
Patrick		Sully		9500.00		80
Allan		McEwen		9000.00		80
Lindsey		Smith		8000.00		80
Louise		Doran		7500.00		80
Sarath		Sewall		7000.00		80
Clara		Vishney		10500.00	80
Danielle	Greene		9500.00		80
Mattea		Marvins		7200.00		80
David		Lee		6800.00		80
Lisa		Ozer		11500.00	80
Harrison	Bloom		10000.00	80
Tayler		Fox		9600.00		80
William		Smith		7400.00		80
Elizabeth	Bates		7300.00		80
Ellen		Abel		11000.00	80
Alyssa		Hutton		8800.00		80
Jonathon	Taylor		8600.00		80
Jack		Livingston	8400.00		80
Kimberely	Grant		7000.00		0
Michael		Hartstein	13000.00	20
Hermann		Baer		10000.00	70
Shelley		Higgins		12000.00	110
William		Gietz		8300.00		110

Code Explanation:

The said query in SQL that retrieves the first name, last name, salary, and department ID of employees from a table named 'employees' where the salary is greater than any salary of employees in department 40.
The "WHERE" clause then filters the results to only include records where the salary is greater than any salary of employees in department 40. This is achieved by using the "ANY" operator, which compares the salary of each employee in the outer query to the result of the subquery.

Visual Presentation:

SQL Subqueries: Display the first and last name, salary, and department ID for those employees who earn more than the minimum salary of a department which ID is 40.

Alternative Solutions:

Using JOIN with ANY Operator:


SELECT e1.first_name, e1.last_name, e1.salary, e1.department_id 
FROM employees e1
JOIN (
    SELECT MAX(salary) as max_salary
    FROM employees 
    WHERE department_id = 40
) e2
ON e1.salary > e2.max_salary;

Using Subquery with a Comparison Operator:


SELECT first_name, last_name, salary, department_id 
FROM employees
WHERE salary > (
    SELECT MIN(salary) 
    FROM employees 
    WHERE department_id = 40
);

Using EXISTS and a Subquery:


SELECT first_name, last_name, salary, department_id 
FROM employees e1
WHERE EXISTS (
    SELECT 1
    FROM employees e2
    WHERE e2.department_id = 40 AND e1.salary > e2.salary
);

Practice Online