PostgreSQL Subquery: Find the name and salary of the employees who draw a more salary than Bell

8. Write a SQL subquery to find the first_name, last_name and salary of the employees who draw a more salary than the employee, which the last name is Bell.

Sample Solution:

Code:

-- This SQL query retrieves the first name, last name, and salary of employees whose salary is greater than that of an employee with the last name 'Bell', ordering the results by first name.

SELECT first_name, -- Selects the first_name column from the employees table
       last_name, -- Selects the last_name column from the employees table
salary -- Selects the salary column from the employees table
FROM employees -- Specifies the table from which to retrieve data, in this case, the employees table
WHERE salary >( -- Filters the rows to include only those where the salary is greater than the salary of an employee with the last name 'Bell'
    SELECT salary -- Subquery: Selects the salary of an employee with the last name 'Bell'
    FROM employees 
    WHERE last_name = 'Bell' -- Filters employees with the last name 'Bell'
) 
ORDER BY first_name; -- Orders the results by first name

Explanation:

• This SQL query retrieves the first name, last name, and salary of employees whose salary is greater than that of an employee with the last name 'Bell', ordering the results by first name.
• The outermost SELECT statement retrieves the first name, last name, and salary from the employees table.
• The WHERE clause filters the rows to include only those where the salary is greater than the salary obtained from the subquery.
• The subquery selects the salary of an employee with the last name 'Bell'.
• The ORDER BY clause orders the results by first name.

Sample table: employees

Sample table: departments

Output:

pg_exercises=# SELECT first_name, last_name, salary
pg_exercises-# FROM employees
pg_exercises-# WHERE salary >
pg_exercises-# (SELECT salary
pg_exercises(# FROM employees
pg_exercises(# WHERE last_name = 'Bell')
pg_exercises-# ORDER BY first_name;
 first_name  | last_name  |  salary
-------------+------------+----------
 Adam        | Fripp      |  8200.00
 Alberto     | Errazuriz  | 12000.00
 Alexander   | Hunold     |  9000.00
 Alexis      | Bull       |  4100.00
 Allan       | McEwen     |  9000.00
 Alyssa      | Hutton     |  8800.00
 Amit        | Banda      |  6200.00
 Bruce       | Ernst      |  6000.00
 Charles     | Johnson    |  6200.00
 Christopher | Olsen      |  8000.00
 Clara       | Vishney    | 10500.00
 Daniel      | Faviet     |  9000.00
 David       | Lee        |  6800.00
 David       | Bernstein  |  9500.00
 David       | Austin     |  4800.00
 Den         | Raphaely   | 11000.00
 Diana       | Lorentz    |  4200.00
 Eleni       | Zlotkey    | 10500.00
 Elizabeth   | Bates      |  7300.00
 Ellen       | Abel       | 11000.00
 Gerald      | Cambrault  | 11000.00
...          |  ...       |  ...
 Valli       | Pataballa  |  4800.00
 William     | Smith      |  7400.00
 William     | Gietz      |  8300.00
(63 rows)

Practice Online

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

Previous: Write a SQL Subquery to find the first_name, last_name and salary of the employees who earn more than the average salary and works in any of the IT departments.
Next: Write a SQL subquery to find all the information of the employees who draws the same salary as the minimum salary for all departments.