SQL Exercise: Employees who work in any department located in London

Last update on December 20 2024 11:42:13 (UTC/GMT +8 hours)

24. From the following tables, write a SQL query to find full name (first and last name), and salary of all employees working in any department in the city of London.

Sample table: departments

Sample table: locations

Sample table: employees

Sample Solution:

-- Selecting specific columns (first_name || ' ' || last_name AS Employee_name, salary) from the 'employees' table
SELECT first_name || ' ' || last_name AS Employee_name, salary 

-- Performing an INNER JOIN between the 'employees' table and the 'departments' table using the common column 'department_id'
FROM employees 

JOIN departments USING (department_id) 

-- Performing another INNER JOIN between the result set and the 'locations' table using the common column 'location_id'
JOIN locations USING (location_id) 

-- Filtering rows based on the condition that 'city' is equal to 'London'
WHERE city = 'London';

Sample Output:

employee_name	salary
Susan Mavris	6500.00

Code Explanation:

The said query in SQL which will return a list of employee names, along with their salaries, for employees who work in a department located in the city of London, based on data from the employees, departments, and locations tables.
JOIN clause joins employees with departments and then joins locations with location_id.
The WHERE clause filters the results to only include employees who work in a department located in the city of London.

Alternative Solution:

Using INNER JOIN with Explicit Column Names and WHERE Clause:


SELECT employees.first_name || ' ' || employees.last_name AS Employee_name, employees.salary
FROM employees
JOIN departments ON employees.department_id = departments.department_id
JOIN locations ON departments.location_id = locations.location_id
WHERE locations.city = 'London';

Explanation:

This query performs INNER JOINs between 'employees', 'departments', and 'locations' tables based on matching keys (department_id and location_id). It retrieves the concatenated full name of employees and their salaries, filtered by the condition that the city is 'London'.

Practice Online