SQL Exercises: Display the city where the employee with ID 134 works
From the following tables, write a SQL query to find the city of the employee of ID 134. Return city.
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 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 Solution:
-- Selecting the 'city' column from the 'locations' table
SELECT city
-- Filtering rows based on the condition that the 'location_id' matches the result of a nested subquery
FROM locations
-- Subquery to find the 'location_id' where the 'department_id' matches the result of another subquery in the 'departments' table
WHERE location_id =
(SELECT location_id
FROM departments
-- Subquery to find the 'department_id' where the 'employee_id' is 134 in the 'employees' table
WHERE department_id =
(SELECT department_id
FROM employees
-- Condition to find the 'department_id' where the 'employee_id' is 134
WHERE employee_id=134)
);
Sample Output:
city South San Francisco
Code Explanation:
The said query in SQL that is selecting the city from the 'locations' table, based on the matching "location_id". The "location_id" is being selected from the 'departments' table, where the "department_id" matches the "department_id" selected from the 'employees' table, where the "employee_id" is 134.
The query is using nested subqueries to first retrieve the "department_id" based on the given "employee_id", then the "location_id" based on the retrieved "department_id", and finally the "city" based on the retrieved "location_id".
Visual Presentation:
Alternative Solutions:
Using JOINs:
SELECT l.city
FROM locations l
JOIN departments d ON l.location_id = d.location_id
JOIN employees e ON d.department_id = e.department_id
WHERE e.employee_id = 134;
Using EXISTS:
SELECT city
FROM locations l
WHERE EXISTS (
SELECT 1
FROM departments d
JOIN employees e ON d.department_id = e.department_id
WHERE e.employee_id = 134 AND l.location_id = d.location_id
);
Using JOINs and Subqueries:
SELECT l.city
FROM locations l
JOIN (
SELECT location_id
FROM departments
WHERE department_id = (
SELECT department_id
FROM employees
WHERE employee_id = 134
)
) d ON l.location_id = d.location_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 whose department is in London.
Next SQL Exercise: Employees who already done one or more jobs.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.