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 :
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).
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics