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:

SELECT first_name,last_name, job_id, salary 
FROM employees 
WHERE salary > 
ALL (SELECT salary 
FROM employees 
WHERE job_id = 'SH_CLERK') 
ORDER BY salary ;

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.