SQL Exercises: Employees getting more salary than employee 163

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

From the following table, write a SQL query to find those employees who receive a higher salary than the employee with ID 163. 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 'first_name' and 'last_name' columns from the 'employees' table
SELECT first_name, last_name 
-- Specifying the table to retrieve data from ('employees')
FROM employees 
-- Filtering the results to include rows where 'salary' is greater than the salary of the employee with 'employee_id' equal to 163
WHERE salary > 
    -- Subquery to retrieve the salary of the employee with 'employee_id' equal to 163
    ( SELECT salary  
      -- Specifying the table to retrieve data from ('employees')
      FROM employees 
      -- Filtering the results to include only the employee with 'employee_id' equal to 163
      WHERE employee_id = 163
    );

Sample Output:

first_name	last_name
Steven		King
Neena		Kochhar
Lex		De Haan
Nancy		Greenberg
Den		Raphaely
John		Russell
Karen		Partners
Alberto		Errazuriz
Gerald		Cambrault
Eleni		Zlotkey
Peter		Tucker
Janette		King
Clara		Vishney
Lisa		Ozer
Harrison	Bloom
Tayler		Fox
Ellen		Abel
Michael		Hartstein
Hermann		Baer
Shelley		Higgins

Code Explanation:

The said query in SQL which is selecting the first name and last name columns from the 'employees' table, where the salary of the employee is greater than the salary of the employee with the ID of 163, as determined by the subquery.
The subquery find the salary from the 'employees' table for the employee with ID of 163, and the outer query uses this value to filter the results.

Visual Presentation:

SQL Subqueries: Display the name for those employees who gets more salary than the employee whose id is 163.

Alternative Statements:

Using JOIN:

SELECT e1.first_name, e1.last_name
FROM employees e1
JOIN employees e2 ON e1.salary > e2.salary
WHERE e2.employee_id = 163;

Using EXISTS:

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