SQL Subquery Exercises: Find out which employees have a manager who works for a department based in the US

SQL SUBQUERY: Exercise-30 with Solution

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

Sample table: departments

Sample table: locations

Sample Solution:

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

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

Pictorial Presentation:

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: From the following table, write a SQL query to find all those employees who earn more than an employee whose last name is ‘Ozer’. Sort the result in ascending order by last name. Return first name, last name and salary.
Next: From the following tables, write a SQL query to find those employees whose salary is greater than 50% of their department's total salary bill. Return first name, last name.