PostgreSQL JOINS: Make a join with three tables to find the name, jobs, department name and ID of all the employees working in London
3. Write a SQL query to make a join with three tables employees, departments and locations to find the name, including first_name and last_name, jobs, department name and ID, of the employees working in London.
Sample Solution:
Code:
-- This SQL query retrieves employee details along with their department information for employees working in London.
SELECT e.first_name, -- Selects the first_name column from the employees table
e.last_name, -- Selects the last_name column from the employees table
e.job_id, -- Selects the job_id column from the employees table
e.department_id, -- Selects the department_id column from the employees table
d.department_name -- Selects the department_name column from the departments table
FROM employees e -- Specifies the first table from which to retrieve data, in this case, the employees table
JOIN departments d -- Joins the employees table with the departments table
ON (e.department_id = d.department_id) -- Specifies the join condition based on the department_id column
JOIN locations l ON -- Joins the departments table with the locations table
(d.location_id = l.location_id) -- Specifies the join condition based on the location_id column
WHERE l.city = 'London'; -- Filters the rows to include only those where the city is 'London'
Explanation:
- This SQL query retrieves employee details along with their department information for employees working in London.
- The SELECT statement selects the first name, last name, job ID, department ID, and department name columns.
- The FROM clause specifies the first table from which to retrieve data, which is the employees table, aliased as e.
- The JOIN keyword is used to join the employees table (e) with the departments table (d) based on the department_id column.
- The ON clause specifies the join condition based on the matching department_id.
- Another JOIN operation is performed to join the departments table (d) with the locations table (l) based on the location_id column.
- The WHERE clause filters the rows to include only those where the city is 'London'.
- The result set will contain employee details along with their department information for employees working 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: 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
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 | | 90 | Executive | 100 | 1700 | ........ | 270 | Payroll | 0 | 1700 | +---------------+----------------------+------------+-------------+
Output:
pg_exercises-# FROM employees e pg_exercises-# JOIN departments d pg_exercises-# ON (e.department_id = d.department_id) pg_exercises-# JOIN locations l ON pg_exercises-# (d.location_id = l.location_id) pg_exercises-# WHERE l.city = 'London'; first_name | last_name | job_id | department_id | department_name ------------+-----------+--------+---------------+----------------- Susan | Mavris | HR_REP | 40 | Human Resources (1 row)
Relational Algebra Expression:
Relational Algebra Tree:
Go to:
PREV : Write a query to make a join with employees and departments table to find the name of the employee, including first_name and last name, department ID and name of departments.
NEXT : Write a query to make a join with two tables employees and itself to find the employee id, last_name as Employee along with their manager_id and last name as Manager.
Practice Online
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?