SQL Exercises: Departments which located in the city London

Last update on March 12 2025 12:11:01 (UTC/GMT +8 hours)

From the following tables, write a SQL query to find those departments that are located in the city of London. Return department ID, department name.

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 |
......
+---------------+----------------------+------------+-------------+

View the table

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         |
........
+-------------+------------------------------------------+-------------+---------------------+-------------------+------------+

View the table

Sample Solution:

-- Selecting specific columns (department_id, department_name) from the 'departments' table
SELECT department_id, department_name 

-- Filtering rows based on the condition that the 'location_id' matches the result of a subquery
FROM departments 

-- Subquery to find the 'location_id' where the city is 'London' in the 'locations' table
WHERE location_id = 

  (SELECT location_id 
   FROM locations 

   -- Condition to find the 'location_id' where the city is 'London'
   WHERE city = 'London'
  );

Sample Output:

department_id	department_name
40		Human Resources

Code Explanation:

The said query in SQL that retrieves the department ID and department name from the 'departments' table where the location is in London. The location ID is determined by a subquery that retrieves the location ID from the 'locations' table where the city is equal to "London".
Using the WHERE clause, the main query filters the data from the 'departments' table based on the subquery result.

Visual Presentation:

Alternative Statements:

Using EXISTS:

SELECT department_id, department_name 
FROM departments d
WHERE EXISTS (
    SELECT 1 
    FROM locations l 
    WHERE l.location_id = d.location_id AND l.city = 'London'
);

Using a Join:

SELECT d.department_id, d.department_name 
FROM departments d
JOIN locations l ON d.location_id = l.location_id
WHERE l.city = 'London';

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

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

Previous SQL Exercise: Salary earn by the employee which is maximum.
Next SQL Exercise: Employees earn more than average salary in ASC order.