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
+-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+ | EMPLOYEE_ID | FIRST_NAME | LAST_NAME | EMAIL | PHONE_NUMBER | HIRE_DATE | JOB_ID | SALARY | COMMISSION_PCT | MANAGER_ID | DEPARTMENT_ID | +-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+ | 100 | Steven | King | SKING | 515.123.4567 | 1987-06-17 | AD_PRES | 24000.00 | 0.00 | 0 | 90 | | 101 | Neena | Kochhar | NKOCHHAR | 515.123.4568 | 1987-06-18 | AD_VP | 17000.00 | 0.00 | 100 | 90 | | 102 | Lex | De Haan | LDEHAAN | 515.123.4569 | 1987-06-19 | AD_VP | 17000.00 | 0.00 | 100 | 90 | | 103 | Alexander | Hunold | AHUNOLD | 590.423.4567 | 1987-06-20 | IT_PROG | 9000.00 | 0.00 | 102 | 60 | | 104 | Bruce | Ernst | BERNST | 590.423.4568 | 1987-06-21 | IT_PROG | 6000.00 | 0.00 | 103 | 60 | | 105 | David | Austin | DAUSTIN | 590.423.4569 | 1987-06-22 | IT_PROG | 4800.00 | 0.00 | 103 | 60 | | 106 | Valli | Pataballa | VPATABAL | 590.423.4560 | 1987-06-23 | IT_PROG | 4800.00 | 0.00 | 103 | 60 | | 107 | Diana | Lorentz | DLORENTZ | 590.423.5567 | 1987-06-24 | IT_PROG | 4200.00 | 0.00 | 103 | 60 | | 108 | Nancy | Greenberg | NGREENBE | 515.124.4569 | 1987-06-25 | FI_MGR | 12000.00 | 0.00 | 101 | 100 | ............ | 206 | William | Gietz | WGIETZ | 515.123.8181 | 1987-10-01 | AC_ACCOUNT | 8300.00 | 0.00 | 205 | 110 | +-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
Sample table: departments
+---------------+----------------------+------------+-------------+ | DEPARTMENT_ID | DEPARTMENT_NAME | MANAGER_ID | LOCATION_ID | +---------------+----------------------+------------+-------------+ | 10 | Administration | 200 | 1700 | | 20 | Marketing | 201 | 1800 | | 30 | Purchasing | 114 | 1700 | | 40 | Human Resources | 203 | 2400 | | 50 | Shipping | 121 | 1500 | | 60 | IT | 103 | 1400 | | 70 | Public Relations | 204 | 2700 | | 80 | Sales | 145 | 2500 | ......... | 270 | Payroll | 0 | 1700 | +---------------+----------------------+------------+-------------+
Sample table: locations
location_id street_address postal_code city state_province country_id ----------- -------------------- ----------- ---------- -------------- ---------- 1000 1297 Via Cola di Rie 989 Roma IT 1100 93091 Calle della Te 10934 Venice IT 1200 2017 Shinjuku-ku 1689 Tokyo Tokyo Prefectu JP 1300 9450 Kamiya-cho 6823 Hiroshima JP 1400 2014 Jabberwocky Rd 26192 Southlake Texas US 1500 2011 Interiors Blvd 99236 South San California US 1600 2007 Zagora St 50090 South Brun New Jersey US 1700 2004 Charade Rd 98199 Seattle Washington US 1800 147 Spadina Ave M5V 2L7 Toronto Ontario CA ........... 3200 Mariano Escobedo 999 11932 Mexico Cit Distrito Feder MX
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
Go to:
PREV :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).
MySQL Code Editor:
Structure of 'hr' database :
Have another way to solve this solution? Contribute your code (and comments) through Disqus.