SQL Exercises: Employees in departments in the United Kingdom
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
+-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+ | EMPLOYEE_ID | FIRST_NAME | LAST_NAME | EMAIL | PHONE_NUMBER | HIRE_DATE | JOB_ID | SALARY | COMMISSION_PCT | MANAGER_ID | DEPARTMENT_ID | +-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+ | 100 | Steven | King | SKING | 515.123.4567 | 2003-06-17 | AD_PRES | 24000.00 | 0.00 | 0 | 90 | | 101 | Neena | Kochhar | NKOCHHAR | 515.123.4568 | 2005-09-21 | AD_VP | 17000.00 | 0.00 | 100 | 90 | | 102 | Lex | De Haan | LDEHAAN | 515.123.4569 | 2001-01-13 | AD_VP | 17000.00 | 0.00 | 100 | 90 | | 103 | Alexander | Hunold | AHUNOLD | 590.423.4567 | 2006-01-03 | IT_PROG | 9000.00 | 0.00 | 102 | 60 | | 104 | Bruce | Ernst | BERNST | 590.423.4568 | 2007-05-21 | IT_PROG | 6000.00 | 0.00 | 103 | 60 | | 105 | David | Austin | DAUSTIN | 590.423.4569 | 2005-06-25 | IT_PROG | 4800.00 | 0.00 | 103 | 60 | | 106 | Valli | Pataballa | VPATABAL | 590.423.4560 | 2006-02-05 | IT_PROG | 4800.00 | 0.00 | 103 | 60 | | 107 | Diana | Lorentz | DLORENTZ | 590.423.5567 | 2007-02-07 | IT_PROG | 4200.00 | 0.00 | 103 | 60 | | 108 | Nancy | Greenberg | NGREENBE | 515.124.4569 | 2002-08-17 | FI_MGR | 12008.00 | 0.00 | 101 | 100 | | 109 | Daniel | Faviet | DFAVIET | 515.124.4169 | 2002-08-16 | FI_ACCOUNT | 9000.00 | 0.00 | 108 | 100 | | 110 | John | Chen | JCHEN | 515.124.4269 | 2005-09-28 | FI_ACCOUNT | 8200.00 | 0.00 | 108 | 100 | .................... +-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
Sample table: departments
+---------------+----------------------+------------+-------------+ | DEPARTMENT_ID | DEPARTMENT_NAME | MANAGER_ID | LOCATION_ID | +---------------+----------------------+------------+-------------+ | 10 | Administration | 200 | 1700 | | 20 | Marketing | 201 | 1800 | | 30 | Purchasing | 114 | 1700 | | 40 | Human Resources | 203 | 2400 | | 50 | Shipping | 121 | 1500 | | 60 | IT | 103 | 1400 | | 70 | Public Relations | 204 | 2700 | | 80 | Sales | 145 | 2500 | | 90 | Executive | 100 | 1700 | | 100 | Finance | 108 | 1700 | ...... +---------------+----------------------+------------+-------------+
Sample table: locations
+-------------+------------------------------------------+-------------+---------------------+-------------------+------------+ | LOCATION_ID | STREET_ADDRESS | POSTAL_CODE | CITY | STATE_PROVINCE | COUNTRY_ID | +-------------+------------------------------------------+-------------+---------------------+-------------------+------------+ | 1000 | 1297 Via Cola di Rie | 989 | Roma | | IT | | 1100 | 93091 Calle della Testa | 10934 | Venice | | IT | | 1200 | 2017 Shinjuku-ku | 1689 | Tokyo | Tokyo Prefecture | JP | | 1300 | 9450 Kamiya-cho | 6823 | Hiroshima | | JP | | 1400 | 2014 Jabberwocky Rd | 26192 | Southlake | Texas | US | | 1500 | 2011 Interiors Blvd | 99236 | South San Francisco | California | US | | 1600 | 2007 Zagora St | 50090 | South Brunswick | New Jersey | US | | 1700 | 2004 Charade Rd | 98199 | Seattle | Washington | US | | 1800 | 147 Spadina Ave | M5V 2L7 | Toronto | Ontario | CA | | 1900 | 6092 Boxwood St | YSW 9T2 | Whitehorse | Yukon | CA | ........ +-------------+------------------------------------------+-------------+---------------------+-------------------+------------+
Sample table: countries
+------------+--------------------------+-----------+ | COUNTRY_ID | COUNTRY_NAME | REGION_ID | +------------+--------------------------+-----------+ | AR | Argentina | 2 | | AU | Australia | 3 | | BE | Belgium | 1 | | BR | Brazil | 2 | | CA | Canada | 2 | | CH | Switzerland | 1 | | CN | China | 3 | | DE | Germany | 1 | | DK | Denmark | 1 | | EG | Egypt | 4 | ......... +------------+--------------------------+-----------+
Sample Solution:
-- Selecting the 'first_name' column from the 'employees' table
SELECT first_name
-- Filtering rows based on the condition that the 'department_id' is in the result set of a nested subquery
FROM employees
-- Subquery to find 'department_id' values from the 'departments' table
WHERE department_id IN
-- Subquery to find 'department_id' values where the 'location_id' is in the result set of another subquery
(SELECT department_id
FROM departments
-- Subquery to find 'location_id' values from the 'locations' table
WHERE location_id IN
-- Subquery to find 'location_id' values where the 'country_id' is in the result set of another subquery
(SELECT location_id
FROM locations
-- Subquery to find 'country_id' values from the 'countries' table where the 'country_name' is 'United Kingdom'
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:
Alternative Statements:
Alternative 1:
SELECT e.first_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id
JOIN countries c ON l.country_id = c.country_id
WHERE c.country_name = 'United Kingdom';
Alternative 2:
SELECT first_name
FROM employees e, departments d, locations l, countries c
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND l.country_id = c.country_id
AND c.country_name = 'United Kingdom';
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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.