﻿ SQL SUBQUERIES: Find the employees who report to a manager based in the United States - w3resource

# 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
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
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:

## Query Visualization:

Duration:

Rows:

Cost:

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

Test your Programming skills with w3resource's quiz.

What is the difficulty level of this exercise?

﻿

## SQL: Tips of the Day

How to count occurrences of a column value in SQL?

Input table:

```id | age
--------
0  | 25
1  | 25
2  | 23
```
```SELECT age, count(age)
FROM Students
GROUP by age
```

Output:

```id | age | count
----------------
0  | 25  | 2
1  | 25  | 2
2  | 23  | 1
```

Ref: https://bit.ly/3zbLPQm