w3resource

PostgreSQL Subquery: Find the name of the employees who are not supervisors


12. Write a SQL subquery to find the first_name and last_name of the employees who are not supervisors.

Sample Solution:

Code:

SELECT b.first_name,b.last_name 
FROM employees b 
WHERE NOT EXISTS (
SELECT 'X' 
FROM employees a 
WHERE a.manager_id = b.employee_id);

Sample table: employees


Output:

pg_exercises=# SELECT b.first_name,b.last_name
pg_exercises-# FROM employees b
pg_exercises-# WHERE NOT EXISTS (
pg_exercises(# SELECT 'X'
pg_exercises(# FROM employees a
pg_exercises(# WHERE a.manager_id = b.employee_id);
 first_name  |  last_name
-------------+-------------
 Bruce       | Ernst
 David       | Austin
 Valli       | Pataballa
 Diana       | Lorentz
 Alexander   | Khoo
 Shelli      | Baida
 Sigal       | Tobias
 Guy         | Himuro
 Karen       | Colmenares
 Jason       | Mallin
 Michael     | Rogers
 Ki          | Gee
 Hazel       | Philtanker
 Stephen     | Stiles
 John        | Seo
 Joshua      | Patel
 Trenna      | Rajs
 Curtis      | Davies
 Randall     | Matos
 Peter       | Vargas
 Janette     | King
...          | ...
 Susan       | Mavris
 Hermann     | Baer
 William     | Gietz
(88 rows)

Practice Online


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

Previous: 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.
Next: Write a SQL subquery to find the employee ID, first name, last name and department names of all employees.

What is the difficulty level of this exercise?



Inviting useful, relevant, well-written and unique guest posts