SQL Exercise: Display name, department, city and state each employee
SQL JOINS on HR Database: Exercise-2 with Solution
2. From the following tables, write a SQL query to find the first name, last name, department, city, and state province for each employee.
Sample table: departments
Sample table: employees
Sample table: locations
Sample Solution:
-- Selecting specific columns (E.first_name, E.last_name, D.department_name, L.city, L.state_province) from the 'employees' table, aliased as 'E', the 'departments' table, aliased as 'D', and the 'locations' table, aliased as 'L'
SELECT E.first_name, E.last_name, D.department_name, L.city, L.state_province
-- Performing an INNER JOIN between the 'employees' table (aliased as 'E') and the 'departments' table (aliased as 'D') based on the 'department_id' column
FROM employees E
JOIN departments D
ON E.department_id = D.department_id
-- Performing another INNER JOIN between the 'departments' table (aliased as 'D') and the 'locations' table (aliased as 'L') based on the 'location_id' column
JOIN locations L
ON D.location_id = L.location_id;
Sample Output:
first_name last_name department_name city state_province Steven King Executive Seattle Washington Neena Kochhar Executive Seattle Washington Lex De Haan Executive Seattle Washington Alexander Hunold IT Southlake Texas Bruce Ernst IT Southlake Texas David Austin IT Southlake Texas Valli Pataballa IT Southlake Texas Diana Lorentz IT Southlake Texas Nancy Greenberg Finance Seattle Washington Daniel Faviet Finance Seattle Washington John Chen Finance Seattle Washington Ismael Sciarra Finance Seattle Washington Jose Manuel Urman Finance Seattle Washington Luis Popp Finance Seattle Washington Den Raphaely Purchasing Seattle Washington Alexander Khoo Purchasing Seattle Washington Shelli Baida Purchasing Seattle Washington Sigal Tobias Purchasing Seattle Washington Guy Himuro Purchasing Seattle Washington Karen Colmenares Purchasing Seattle Washington Matthew Weiss Shipping South San Francisco California Adam Fripp Shipping South San Francisco California Payam Kaufling Shipping South San Francisco California Shanta Vollman Shipping South San Francisco California Kevin Mourgos Shipping South San Francisco California Julia Nayer Shipping South San Francisco California Irene Mikkilineni Shipping South San Francisco California James Landry Shipping South San Francisco California Steven Markle Shipping South San Francisco California Laura Bissot Shipping South San Francisco California Mozhe Atkinson Shipping South San Francisco California James Marlow Shipping South San Francisco California TJ Olson Shipping South San Francisco California Jason Mallin Shipping South San Francisco California Michael Rogers Shipping South San Francisco California Ki Gee Shipping South San Francisco California Hazel Philtanker Shipping South San Francisco California Renske Ladwig Shipping South San Francisco California Stephen Stiles Shipping South San Francisco California John Seo Shipping South San Francisco California Joshua Patel Shipping South San Francisco California Trenna Rajs Shipping South San Francisco California Curtis Davies Shipping South San Francisco California Randall Matos Shipping South San Francisco California Peter Vargas Shipping South San Francisco California John Russell Sales OX9 9ZB Oxford Karen Partners Sales OX9 9ZB Oxford Alberto Errazuriz Sales OX9 9ZB Oxford Gerald Cambrault Sales OX9 9ZB Oxford Eleni Zlotkey Sales OX9 9ZB Oxford Peter Tucker Sales OX9 9ZB Oxford David Bernstein Sales OX9 9ZB Oxford Peter Hall Sales OX9 9ZB Oxford Christopher Olsen Sales OX9 9ZB Oxford Nanette Cambrault Sales OX9 9ZB Oxford Oliver Tuvault Sales OX9 9ZB Oxford Janette King Sales OX9 9ZB Oxford Patrick Sully Sales OX9 9ZB Oxford Allan McEwen Sales OX9 9ZB Oxford Lindsey Smith Sales OX9 9ZB Oxford Louise Doran Sales OX9 9ZB Oxford Sarath Sewall Sales OX9 9ZB Oxford Clara Vishney Sales OX9 9ZB Oxford Danielle Greene Sales OX9 9ZB Oxford Mattea Marvins Sales OX9 9ZB Oxford David Lee Sales OX9 9ZB Oxford Sundar Ande Sales OX9 9ZB Oxford Amit Banda Sales OX9 9ZB Oxford Lisa Ozer Sales OX9 9ZB Oxford Harrison Bloom Sales OX9 9ZB Oxford Tayler Fox Sales OX9 9ZB Oxford William Smith Sales OX9 9ZB Oxford Elizabeth Bates Sales OX9 9ZB Oxford Sundita Kumar Sales OX9 9ZB Oxford Ellen Abel Sales OX9 9ZB Oxford Alyssa Hutton Sales OX9 9ZB Oxford Jonathon Taylor Sales OX9 9ZB Oxford Jack Livingston Sales OX9 9ZB Oxford Charles Johnson Sales OX9 9ZB Oxford Winston Taylor Shipping South San Francisco California Jean Fleaur Shipping South San Francisco California Martha Sullivan Shipping South San Francisco California Girard Geoni Shipping South San Francisco California Nandita Sarchand Shipping South San Francisco California Alexis Bull Shipping South San Francisco California Julia Dellinger Shipping South San Francisco California Anthony Cabrio Shipping South San Francisco California Kelly Chung Shipping South San Francisco California Jennifer Dilly Shipping South San Francisco California Timothy Gates Shipping South San Francisco California Randall Perkins Shipping South San Francisco California Sarah Bell Shipping South San Francisco California Britney Everett Shipping South San Francisco California Samuel McCain Shipping South San Francisco California Vance Jones Shipping South San Francisco California Alana Walsh Shipping South San Francisco California Kevin Feeney Shipping South San Francisco California Donald OConnell Shipping South San Francisco California Douglas Grant Shipping South San Francisco California Jennifer Whalen Administration Seattle Washington Michael Hartstein Marketing Toronto Ontario Pat Fay Marketing Toronto Ontario Susan Mavris Human Resources London Hermann Baer Public Relations Munich Bavaria Shelley Higgins Accounting Seattle Washington William Gietz Accounting Seattle Washington
Code Explanation:
.The said query in SQL that joins the 'employees' table with the 'departments' table using the "department_id" column, and then joins the 'departments' table with the 'locations' table using the "location_id" column.
It selects the "first_name", "last_name", "department_name", "city", and "state_province" columns from the joined tables. The result set includes one row for each combination of employee, department, and location where the "department_id" and "location_id" values match between the respective tables.
The "JOIN" keyword is used to combine the tables based on the common columns.
Relational Algebra Expression:
Relational Algebra Tree:
Visual Presentation:
Alternative Solutions:
Using WHERE Clause with Equality Check:
SELECT E.first_name, E.last_name, D.department_name, L.city, L.state_province
FROM employees E, departments D, locations L
WHERE E.department_id = D.department_id
AND D.location_id = L.location_id;
Using JOIN with ANSI-92 Syntax:
SELECT E.first_name, E.last_name, D.department_name, L.city, L.state_province
FROM employees E
JOIN departments D ON E.department_id = D.department_id
JOIN locations L ON D.location_id = L.location_id;
Using CROSS JOIN with WHERE Clause:
SELECT E.first_name, E.last_name, D.department_name, L.city, L.state_province
FROM employees E, departments D
CROSS JOIN locations L
WHERE E.department_id = D.department_id
AND D.location_id = L.location_id;
Practice Online
Query Visualization:
Duration:
Rows:
Cost:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Display the name, department number, for each employee.
Next SQL Exercise: List all employees names, salaries, and job grades.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://w3resource.com/sql-exercises/joins-hr/sql-joins-hr-exercise-2.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics