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.
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
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)
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?