SQL Exercises: Employees whose department is in London
From the following tables, write a SQL query to find all employees whose department is located in London. Return first name, last name, salary, and department ID.
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: 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: 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 Solution:
-- Selecting specific columns (first_name, last_name, salary, department_id) from the 'employees' table
SELECT first_name, last_name, salary, department_id
-- 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 where the 'location_id' matches the result of another subquery
WHERE department_id IN
(SELECT department_id
FROM departments
-- Subquery to find 'location_id' values where the 'city' is 'London' in the 'locations' table
WHERE location_id =
(SELECT location_id
FROM locations
-- Condition to find 'location_id' values where the 'city' is 'London'
WHERE city = 'London')
);
Sample Output:
first_name last_name salary department_id Susan Mavris 6500.00 40
Code Explanation:
The said query in SQL that retrieves data from the 'employees' table. The columns selected are "first_name", "last_name", "salary", and "department_id". The query only returns rows where the department_id of the employees is in a list of department_id's from departments located in London.
The subquery in the WHERE clause first selects the location_id of the city 'London' from the 'locations' table. This result is then used in another subquery to select the department_id of departments located in London from the 'departments' table.
Visual Presentation:
Alternative Solutions:
Using JOINs:
SELECT e.first_name, e.last_name, e.salary, e.department_id
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id
WHERE l.city = 'London';
Using EXISTS:
SELECT first_name, last_name, salary, department_id
FROM employees e1
WHERE EXISTS (
SELECT 1
FROM departments d
JOIN locations l ON d.location_id = l.location_id
WHERE d.department_id = e1.department_id AND l.city = 'London'
);
Using JOINs and Subqueries:
SELECT e.first_name, e.last_name, e.salary, e.department_id
FROM employees e
JOIN (
SELECT department_id
FROM departments
WHERE location_id = (
SELECT location_id
FROM locations
WHERE city = 'London'
)
) d ON e.department_id = d.department_id;
Practice Online
Query Visualization:
Duration:
Rows:
Cost:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Employees earn less than the average salary like Laura.
Next SQL Exercise: Display the city where the employee with ID 134 works.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.