w3resource

MySQL Joins Exercises: Find the name, job, department ID and name of the employees who works in London

MySQL Joins: Exercise-3 with Solution

Write a MySQL query to find the name (first_name, last_name), job, department ID and name of the employees who works in London.

Sample table: employees


Sample table: departments


Sample table: locations


Code:

-- This SQL query selects specific columns from the 'employees' and 'departments' tables, as well as the 'locations' table, to retrieve information about employees in the London city.

SELECT 
    e.first_name, -- Selecting the 'first_name' column from the 'employees' table and aliasing it as 'e'.
    e.last_name, -- Selecting the 'last_name' column from the 'employees' table and aliasing it as 'e'.
    e.job_id, -- Selecting the 'job_id' column from the 'employees' table and aliasing it as 'e'.
    e.department_id, -- Selecting the 'department_id' column from the 'employees' table and aliasing it as 'e'.
    d.department_name -- Selecting the 'department_name' column from the 'departments' table and aliasing it as 'd'.
FROM 
    employees e -- Specifying the 'employees' table and aliasing it as 'e'.
JOIN 
    departments d -- Specifying the 'departments' table and aliasing it as 'd'.
ON 
    (e.department_id = d.department_id) -- Performing a join between 'employees' and 'departments' based on the 'department_id' column.
JOIN 
    locations l ON -- Joining the 'locations' table and aliasing it as 'l'.
    (d.location_id = l.location_id) -- Performing a join between 'departments' and 'locations' based on the 'location_id' column.
WHERE 
    LOWER(l.city) = 'London'; -- Filtering the result to only include rows where the city in lowercase is 'London'.

Explanation:

  • This SQL query retrieves specific columns from the 'employees' table (aliased as 'e') and 'departments' table (aliased as 'd') to get information about employees in the London city.
  • It joins the 'employees' and 'departments' tables based on the 'department_id' column to link employees to their respective departments.
  • It then joins the 'departments' table with the 'locations' table (aliased as 'l') based on the 'location_id' column to associate departments with their corresponding locations.
  • The WHERE clause filters the result to only include rows where the city in lowercase is 'London'.

Sample Output:

first_name	last_name	job_id	department_id	department_name
Susan		Mavris		HR_REP	40		Human Resources

 

MySQL Code Editor:

Structure of 'hr' database :

hr database

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

Previous:Write a MySQL query to find the name (first_name, last name), department ID and name of all the employees.
Next:Write a MySQL query to find the employee id, name (last_name) along with their manager_id and name (last_name).



Follow us on Facebook and Twitter for latest update.