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:

-- This SQL query retrieves the first name and last name of employees who are managed by managers located in the US.

SELECT first_name, -- Selects the first_name column from the employees table
       last_name -- Selects the last_name column from the employees table
FROM employees -- Specifies the table from which to retrieve data, in this case, the employees table
WHERE manager_id IN ( -- Filters the rows to include only those where the manager_id matches an employee_id
        SELECT employee_id -- Subquery: Selects the employee_id of managers located in the US
        FROM employees 
        WHERE department_id IN ( -- Filters managers based on the department_id
                SELECT department_id -- Subquery: Selects the department_id of departments located in the US
                FROM departments 
                WHERE location_id IN ( -- Filters departments based on the location_id
                        SELECT location_id -- Subquery: Selects the location_id of locations in the US
                        FROM locations 
                        WHERE country_id = 'US' -- Filters locations based on the country_id, 'US'
                    )
            )
    );

Explanation:

• This SQL query retrieves the first name and last name of employees who are managed by managers located in the US.
• The outermost SELECT statement retrieves the first name and last name from the employees table.
• The WHERE clause filters the rows to include only those where the manager_id matches an employee_id obtained from the subquery.
• The subquery selects employee_ids of managers located in the US. It first filters managers based on their department_id and then further filters based on the location_id of their departments, which are located in the US.
• The subqueries are enclosed in parentheses and executed before the outer query.

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.