﻿ SQL - Employees whose department is in London

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:

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.

﻿

SQL: Tips of the Day

How to request a random row in SQL?

Select a random row with MySQL:

```SELECT column FROM table
ORDER BY RAND()
LIMIT 1
```

Select a random row with PostgreSQL:

```SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1
```

Select a random row with Microsoft SQL Server:

```SELECT TOP 1 column FROM table
ORDER BY NEWID()
```

Select a random row with IBM DB2:

```SELECT column, RAND() as IDX
FROM table
ORDER BY IDX FETCH FIRST 1 ROWS ONLY
```

Select a random record with Oracle:

```SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1
```

Database: SQL Server, PostgreSQL Server, MySQL

Ref: https://bit.ly/39n35HP

We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook