SQL Exercises: Manager who works for a department based in the US
From the following tables, write a SQL query find the employees who report to a manager based in the United States. Return first name, last name.
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 | 2003-06-17 | AD_PRES | 24000.00 | 0.00 | 0 | 90 | | 101 | Neena | Kochhar | NKOCHHAR | 515.123.4568 | 2005-09-21 | AD_VP | 17000.00 | 0.00 | 100 | 90 | | 102 | Lex | De Haan | LDEHAAN | 515.123.4569 | 2001-01-13 | AD_VP | 17000.00 | 0.00 | 100 | 90 | | 103 | Alexander | Hunold | AHUNOLD | 590.423.4567 | 2006-01-03 | IT_PROG | 9000.00 | 0.00 | 102 | 60 | | 104 | Bruce | Ernst | BERNST | 590.423.4568 | 2007-05-21 | IT_PROG | 6000.00 | 0.00 | 103 | 60 | | 105 | David | Austin | DAUSTIN | 590.423.4569 | 2005-06-25 | IT_PROG | 4800.00 | 0.00 | 103 | 60 | | 106 | Valli | Pataballa | VPATABAL | 590.423.4560 | 2006-02-05 | IT_PROG | 4800.00 | 0.00 | 103 | 60 | | 107 | Diana | Lorentz | DLORENTZ | 590.423.5567 | 2007-02-07 | IT_PROG | 4200.00 | 0.00 | 103 | 60 | | 108 | Nancy | Greenberg | NGREENBE | 515.124.4569 | 2002-08-17 | FI_MGR | 12008.00 | 0.00 | 101 | 100 | | 109 | Daniel | Faviet | DFAVIET | 515.124.4169 | 2002-08-16 | FI_ACCOUNT | 9000.00 | 0.00 | 108 | 100 | | 110 | John | Chen | JCHEN | 515.124.4269 | 2005-09-28 | FI_ACCOUNT | 8200.00 | 0.00 | 108 | 100 | .................... +-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
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 | | 100 | Finance | 108 | 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 Testa | 10934 | Venice | | IT | | 1200 | 2017 Shinjuku-ku | 1689 | Tokyo | Tokyo Prefecture | JP | | 1300 | 9450 Kamiya-cho | 6823 | Hiroshima | | JP | | 1400 | 2014 Jabberwocky Rd | 26192 | Southlake | Texas | US | | 1500 | 2011 Interiors Blvd | 99236 | South San Francisco | California | US | | 1600 | 2007 Zagora St | 50090 | South Brunswick | New Jersey | US | | 1700 | 2004 Charade Rd | 98199 | Seattle | Washington | US | | 1800 | 147 Spadina Ave | M5V 2L7 | Toronto | Ontario | CA | | 1900 | 6092 Boxwood St | YSW 9T2 | Whitehorse | Yukon | CA | ........ +-------------+------------------------------------------+-------------+---------------------+-------------------+------------+
Sample Solution:
-- Selecting specific columns (first_name, last_name) from the 'employees' table
SELECT first_name, last_name
-- Filtering rows based on the condition that the 'manager_id' is in the result set of a nested subquery
FROM employees
-- Subquery to find 'employee_id' values where the 'department_id' is in the result set of another subquery
WHERE manager_id IN
(SELECT employee_id
FROM employees
-- Subquery to find 'department_id' values where the 'location_id' is in the result set of another subquery
WHERE department_id IN
-- Subquery to find 'department_id' values where the 'country_id' is 'US' in the 'locations' table
(SELECT department_id
FROM departments
-- Subquery to find 'location_id' values where the 'country_id' is 'US' in the 'countries' table
WHERE location_id IN
(SELECT location_id
FROM locations
WHERE country_id='US')
)
);
Sample Output:
first_name last_name Neena Kochhar Lex De Haan Alexander Hunold Bruce Ernst David Austin Valli Pataballa Diana Lorentz Nancy Greenberg Daniel Faviet John Chen Ismael Sciarra Jose Manuel Urman Luis Popp Den Raphaely Alexander Khoo Shelli Baida Sigal Tobias Guy Himuro Karen Colmenares Matthew Weiss Adam Fripp Payam Kaufling Shanta Vollman Kevin Mourgos Julia Nayer Irene Mikkilineni James Landry Steven Markle Laura Bissot Mozhe Atkinson James Marlow TJ Olson Jason Mallin Michael Rogers Ki Gee Hazel Philtanker Renske Ladwig Stephen Stiles John Seo Joshua Patel Trenna Rajs Curtis Davies Randall Matos Peter Vargas John Russell Karen Partners Alberto Errazuriz Gerald Cambrault Eleni Zlotkey Winston Taylor Jean Fleaur Martha Sullivan Girard Geoni Nandita Sarchand Alexis Bull Julia Dellinger Anthony Cabrio Kelly Chung Jennifer Dilly Timothy Gates Randall Perkins Sarah Bell Britney Everett Samuel McCain Vance Jones Alana Walsh Kevin Feeney Donald OConnell Douglas Grant Jennifer Whalen Michael Hartstein Susan Mavris Hermann Baer Shelley Higgins William Gietz
Code Explanation:
The said query in SQL that retrieves the first name and last name of employees whose manager works in a department located in a location in the United States. The query is executed in several steps, as follows:
One: A subquery is used to retrieve the location_id for the country_id 'US' from the locations table.
Two: Another subquery is used to retrieve the department_id for the locations returned from step one from the departments table.
Three: A third subquery is used to retrieve the employee_id for the departments returned from step two from the employees table.
The final query uses the IN operator to retrieve the first_name and last_name of employees whose manager_id is one of the employee_ids returned from step three.
Visual Presentation:
Alternative Statements:
Using JOINs:
SELECT e1.first_name, e1.last_name
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.employee_id
JOIN departments d ON e2.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id
WHERE l.country_id = 'US';
Using EXISTS Clause:
SELECT first_name, last_name
FROM employees e1
WHERE EXISTS
(SELECT 1
FROM employees e2
JOIN departments d ON e2.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id
WHERE e1.manager_id = e2.employee_id
AND l.country_id = 'US');
Practice Online
Query Visualization:
Duration:
Rows:
Cost:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Determine who earns more than Mr. Ozer.
Next SQL Exercise: Salary exceeds 50% of their departments total salary.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.