SQL Exercises: Employees whose department is in London

SQL SUBQUERY: Exercise-46 with Solution

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

Sample table: departments

Sample table: locations

Sample Solution:

SELECT first_name, last_name, salary, department_id 
  FROM employees
    WHERE department_id IN 
          (SELECT department_id 
            FROM departments 
             WHERE location_id = 
               (SELECT location_id 
                 FROM locations 
                   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:

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.