w3resource
SQL exercises

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

Write a query to find out which employees have a manager who works for a department based in the US.

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

Practice Online


Query Visualization:

Duration:

Query visualization of Find out which employees have a manager who works for a department based in the US - Duration

Rows:

Query visualization of Find out which employees have a manager who works for a department based in the US - Rows

Cost:

Query visualization of Find out which employees have a manager who works for a department based in the US - Cost

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

Previous: Write a query to determine who earns more than Mr. Ozer.
Next: Write a query which is looking for the names of all employees whose salary is greater than 50% of their department’s total salary bill.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming