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?



Follow us on Facebook and Twitter for latest update.