SQL Exercises: Employees in departments 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

Code Explanation:

This is a SQL query used to retrieve the first names of employees from the 'employees' table based on the departments, locations, and countries they belong to.
The query starts by selecting the "first_name" column from the 'employees' table. The subquery in the WHERE clause retrieves the department IDs from the "departments" table where the location IDs belong to locations in the country "United Kingdom". The location IDs are retrieved using another subquery that selects the location IDs from the 'locations' table based on the country ID for "United Kingdom". The country ID is retrieved from the 'countries' table using yet another subquery.

Visual Presentation:

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

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

Previous SQL Exercise: Departments that have one or more employees.
Next SQL Exercise: IT employees who earn more than the average.