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
+-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+ | EMPLOYEE_ID | FIRST_NAME | LAST_NAME | EMAIL | PHONE_NUMBER | HIRE_DATE | JOB_ID | SALARY | COMMISSION_PCT | MANAGER_ID | DEPARTMENT_ID | +-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+ | 100 | Steven | King | SKING | 515.123.4567 | 1987-06-17 | AD_PRES | 24000.00 | 0.00 | 0 | 90 | | 101 | Neena | Kochhar | NKOCHHAR | 515.123.4568 | 1987-06-18 | AD_VP | 17000.00 | 0.00 | 100 | 90 | | 102 | Lex | De Haan | LDEHAAN | 515.123.4569 | 1987-06-19 | AD_VP | 17000.00 | 0.00 | 100 | 90 | | 103 | Alexander | Hunold | AHUNOLD | 590.423.4567 | 1987-06-20 | IT_PROG | 9000.00 | 0.00 | 102 | 60 | | 104 | Bruce | Ernst | BERNST | 590.423.4568 | 1987-06-21 | IT_PROG | 6000.00 | 0.00 | 103 | 60 | | 105 | David | Austin | DAUSTIN | 590.423.4569 | 1987-06-22 | IT_PROG | 4800.00 | 0.00 | 103 | 60 | | 106 | Valli | Pataballa | VPATABAL | 590.423.4560 | 1987-06-23 | IT_PROG | 4800.00 | 0.00 | 103 | 60 | | 107 | Diana | Lorentz | DLORENTZ | 590.423.5567 | 1987-06-24 | IT_PROG | 4200.00 | 0.00 | 103 | 60 | | 108 | Nancy | Greenberg | NGREENBE | 515.124.4569 | 1987-06-25 | FI_MGR | 12000.00 | 0.00 | 101 | 100 | .......... | 206 | William | Gietz | WGIETZ | 515.123.8181 | 1987-10-01 | AC_ACCOUNT | 8300.00 | 0.00 | 205 | 110 | +-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
Sample table: departments
+---------------+----------------------+------------+-------------+ | DEPARTMENT_ID | DEPARTMENT_NAME | MANAGER_ID | LOCATION_ID | +---------------+----------------------+------------+-------------+ | 10 | Administration | 200 | 1700 | | 20 | Marketing | 201 | 1800 | | 30 | Purchasing | 114 | 1700 | | 40 | Human Resources | 203 | 2400 | | 50 | Shipping | 121 | 1500 | | 60 | IT | 103 | 1400 | | 70 | Public Relations | 204 | 2700 | | 80 | Sales | 145 | 2500 | | 90 | Executive | 100 | 1700 | ........ | 270 | Payroll | 0 | 1700 | +---------------+----------------------+------------+-------------+
Sample table: locations
location_id street_address postal_code city state_province country_id ----------- -------------------- ----------- ---------- -------------- ---------- 1000 1297 Via Cola di Rie 989 Roma IT 1100 93091 Calle della Te 10934 Venice IT 1200 2017 Shinjuku-ku 1689 Tokyo Tokyo Prefectu JP 1300 9450 Kamiya-cho 6823 Hiroshima JP 1400 2014 Jabberwocky Rd 26192 Southlake Texas US 1500 2011 Interiors Blvd 99236 South San California US 1600 2007 Zagora St 50090 South Brun New Jersey US 1700 2004 Charade Rd 98199 Seattle Washington US 1800 147 Spadina Ave M5V 2L7 Toronto Ontario CA ......... 3200 Mariano Escobedo 999 11932 Mexico Cit Distrito Feder MX
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)
Go to:
PREV : 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.
Practice Online
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?