SQL Exercises: Employee who works in that department which ID is 40

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

From the following table, write a SQL query to find those employees whose salary matches that of the employee who works in department 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 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 IN (
                   SELECT salary 
                     FROM employees 
                       WHERE department_id = 40);

Sample Output:

first_name	last_name	salary	department_id
Shanta		Vollman		6500.00		50
Susan		Mavris		6500.00		40

Code Explanation:

The said query in SQL that retrieves the first name, last name, salary, and department id of all employees whose salary is equal to the salary of at least one employee who belongs to department id 40.
The WHERE clause specified is checking if the salary of each employee is found in the list of salaries of employees who belong to department id 40. Using the IN operator and a subquery in the WHERE clause, the condition retrieves the list of salaries of employees in department id 40 and checks each employee's salary.

Visual Presentation:

Alternative Solutions:

Using Self-Join:

SELECT e1.first_name, e1.last_name, e1.salary, e1.department_id 
FROM employees e1
JOIN employees e2 ON e1.salary = e2.salary
WHERE e2.department_id = 40;

Using EXISTS:

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

Query Visualization:

Duration:

Rows:

Cost:

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

Previous SQL Exercise: Department where the employee works with ID 201.
Next SQL Exercise: Employees who work in the department Marketing.