w3resource

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 Expression: Display the first and last name, department, city, and state province for each employee.

Relational Algebra Tree:

Relational Algebra Tree: Display the first and last name, department, city, and state province for each employee.

Visual Presentation:

SQL Exercises: Display the first and last name, department, city, and state province for each employee

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


HR database model

Query Visualization:

Duration:

Query visualization of Display the first and last name, department, city, and state province for each employee - Duration

Rows:

Query visualization of Display the first and last name, department, city, and state province for each employee - Rows

Cost:

Query visualization of Display the first and last name, department, city, and state province for each employee - 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.



Follow us on Facebook and Twitter for latest update.