PostgreSQL Subquery: Find some information of the employees who draws a salary that is higher than the salary of all shipping clerks

11. Write a subquery to find the first_name, last_name, job_id and salary of the employees who draws a salary that is higher than the salary of all the Shipping Clerk (JOB_ID = 'SH_CLERK'). Sort the results on salary from the lowest to highest.

Sample Solution:

Code:

-- This SQL query retrieves the first name, last name, job ID, and salary of employees whose salary is greater than the salary of ALL sales clerks, ordered by salary.

SELECT first_name, -- Selects the first_name column from the employees table
       last_name, -- Selects the last_name column from the employees table
       job_id, -- Selects the job_id 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 > ALL ( -- Filters the rows to include only those where the salary is greater than ALL the salaries of sales clerks
    SELECT salary -- Subquery: Selects the salary of sales clerks
    FROM employees 
    WHERE job_id = 'SH_CLERK' -- Filters employees with the job ID 'SH_CLERK'
) 
ORDER BY salary; -- Orders the results by salary

Explanation:

• This SQL query retrieves the first name, last name, job ID, and salary of employees whose salary is greater than the salary of ALL sales clerks, ordered by salary.
• The outermost SELECT statement selects the specified columns from the employees table.
• The WHERE clause filters the rows to include only those where the salary is greater than ALL the salaries obtained from the subquery.
• The subquery selects the salary of sales clerks with the job ID 'SH_CLERK'.
• The ALL keyword is used to compare the salary with all the salaries of sales clerks.
• The ORDER BY clause orders the results by salary in ascending order.

Sample table: employees

Output:

pg_exercises=# SELECT first_name,last_name, job_id, salary
pg_exercises-# FROM employees
pg_exercises-# WHERE salary >
pg_exercises-# ALL (SELECT salary
pg_exercises(# FROM employees
pg_exercises(# WHERE job_id = 'SH_CLERK')
pg_exercises-# ORDER BY salary ;
 first_name  | last_name  |   job_id   |  salary
-------------+------------+------------+----------
 Jennifer    | Whalen     | AD_ASST    |  4400.00
 David       | Austin     | IT_PROG    |  4800.00
 Valli       | Pataballa  | IT_PROG    |  4800.00
 Kevin       | Mourgos    | ST_MAN     |  5800.00
 Bruce       | Ernst      | IT_PROG    |  6000.00
 Pat         | Fay        | MK_REP     |  6000.00
 Sundita     | Kumar      | SA_REP     |  6100.00
 Amit        | Banda      | SA_REP     |  6200.00
 Charles     | Johnson    | SA_REP     |  6200.00
 Sundar      | Ande       | SA_REP     |  6400.00
 Susan       | Mavris     | HR_REP     |  6500.00
 Shanta      | Vollman    | ST_MAN     |  6500.00
 David       | Lee        | SA_REP     |  6800.00
 Luis        | Popp       | FI_ACCOUNT |  6900.00
 Kimberely   | Grant      | SA_REP     |  7000.00
 Oliver      | Tuvault    | SA_REP     |  7000.00
 Sarath      | Sewall     | SA_REP     |  7000.00
 Mattea      | Marvins    | SA_REP     |  7200.00
 Elizabeth   | Bates      | SA_REP     |  7300.00
 William     | Smith      | SA_REP     |  7400.00
 Louise      | Doran      | SA_REP     |  7500.00
...          | ...        | ...        |  ...
 Lex         | De Haan    | AD_VP      | 17000.00
 Neena       | Kochhar    | AD_VP      | 17000.00
 Steven      | King       | AD_PRES    | 24000.00
(60 rows)

Practice Online

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

Previous: Write a SQL subquery to find all the information of the employees whose salary greater than the average salary of all departments.
Next: Write a SQL subquery to find the first_name and last_name of the employees who are not supervisors.