SQLite Exercise: Find the names of the employees who have a manager who works for a department based in the United States

Write a query to find the names (first_name, last_name) of the employees who have a manager who works for a department based in the United States.

Sample table : employees

Sample table : departments

Sample table : locations

SQLite 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')));

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 Manue  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       Mikkilinen
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

Practice SQLite Online

Model Database

Structure of 'hr' database :

Improve this sample solution and post your code through Disqus.

Previous: Write a query to find the names (first_name, last_name) of all employees who works in the IT department.
Next: Write a query to find the names (first_name, last_name) of the employees who are managers.