w3resource

SQL Subquery Exercises: Identify all employees who work in departments located in the United Kingdom

SQL SUBQUERY: Exercise-27 with Solution

From the following tables, write a SQL query to find employees who work in departments located in the United Kingdom. Return first name.

Sample table: employees


Sample table: departments


Sample table: locations


Sample table: countries


Sample Solution:

SELECT first_name 
FROM employees 
WHERE department_id IN 
(SELECT department_id 
FROM departments 
WHERE location_id IN 
(SELECT location_id 
FROM locations 
WHERE country_id = 
(SELECT country_id 
FROM countries 
WHERE country_name='United Kingdom')));

Sample Output:

first_name
Susan

Pictorial Presentation:

SQL Subqueries: Identify all employees who work in departments located in the United Kingdom.

Practice Online


Query Visualization:

Duration:

Query visualization of Identify all employees who work in departments located in the United Kingdom - Duration

Rows:

Query visualization of Identify all employees who work in departments located in the United Kingdom - Rows

Cost:

Query visualization of Identify all employees who work in departments located in the United Kingdom - 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 departments where at least one or more employees work.Return department name.
Next: From the following table, write a SQL query to find those employees who earn more than average salary and who work in any of the ‘IT’ departments. Return last name.

Test your Programming skills with w3resource's quiz.

What is the difficulty level of this exercise?



Share this Tutorial / Exercise on : Facebook and Twitter

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