PostgreSQL Subquery: Find the names of the employees under a manager who works for a department based in the United States

3. Write a SQL subquery to find the first_name and last_name of the employees under a manager who works for a department based in the United States.

Sample Solution:

Code:

SELECT first_name, last_name 
FROM employees 
WHERE manager_id in (select employee_id 
FROM employees WHERE department_id 
IN (SELECT department_id 
FROM departments 
WHERE location_id 
IN (select location_id 
FROM locations 
WHERE country_id='US')));

Hint : Write single-row and multiple-row subqueries

Sample table: employees

Sample table: departments

Sample table: locations

Output:

pg_exercises=# SELECT first_name, last_name
pg_exercises-# FROM employees
pg_exercises-# WHERE manager_id in (select employee_id
pg_exercises(# FROM employees WHERE department_id
pg_exercises(# IN (SELECT department_id
pg_exercises(# FROM departments
pg_exercises(# WHERE location_id
pg_exercises(# IN (select location_id
pg_exercises(# FROM locations
pg_exercises(# WHERE country_id='US')));
 first_name  |  last_name
-------------+-------------
 Alexander   | Hunold
 Bruce       | Ernst
 David       | Austin
 Valli       | Pataballa
 Diana       | Lorentz
 Den         | Raphaely
 Alexander   | Khoo
 Shelli      | Baida
 Sigal       | Tobias
 Neena       | Kochhar
 Guy         | Himuro
 Karen       | Colmenares
 Jason       | Mallin
 Michael     | Rogers
 Ki          | Gee
 Hazel       | Philtanker
 Stephen     | Stiles
 John        | Seo
 Joshua      | Patel
 Trenna      | Rajs
 Curtis      | Davies
...          | ...
 Hermann     | Baer
 Shelley     | Higgins
 William     | Gietz
(75 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 and last_name of all employees who works in the IT department.
Next: Write a SQL subquery to find the first_name and last_name of the employees who are working as a manager.