SQL Exercise: Display the department name, city for each department

SQL JOINS on HR Database: Exercise-9 with Solution

9. From the following tables, write a SQL query to display the department name, city, and state province for each department.

Sample table: departments

Sample table: locations

Sample Solution:

-- Selecting specific columns (D.department_name, L.city, L.state_province) from the 'departments' table, aliased as 'D', and the 'locations' table, aliased as 'L'
SELECT D.department_name, L.city, L.state_province 

-- Performing an INNER JOIN between the 'departments' table (aliased as 'D') and the 'locations' table (aliased as 'L') based on the 'location_id' column
FROM departments D 

JOIN locations L 
  ON D.location_id = L.location_id;

Sample Output:

department_name		city	state_province
Administration		Seattle	Washington
Marketing		Toronto	Ontario
Purchasing		Seattle	Washington
Human 	Resources	London	
Shipping	South San Francisco	California
IT			Southlake  Texas
Public 	Relations	Munich	Bavaria
Sales			OX9 9ZB	Oxford
Executive		Seattle	Washington
Finance			Seattle	Washington
Accounting		Seattle	Washington
Treasury		Seattle	Washington
Corporate Tax 		Seattle	Washington
Control And Credit	Seattle	Washington
Shareholder Services	Seattle	Washington
Benefits		Seattle	Washington
Manufacturing		Seattle	Washington
Construction		Seattle	Washington
Contracting		Seattle	Washington
Operations		Seattle	Washington
IT Support		Seattle	Washington
NOC			Seattle	Washington
IT Helpdesk		Seattle	Washington
Government Sales	Seattle	Washington
Retail Sales		Seattle	Washington
Recruiting		Seattle	Washington
Payroll			Seattle	Washington

Code Explanation:

The said query in SQL that retrieves the department name along with the city and state/province of the location associated with each department. It uses a join to match each department with its corresponding location based on the location ID in the departments table.
The resulting output will have three columns that is "department_name", "city", and "state_province". The "department_name" column will contain the name of each department, while the "city" and "state_province" columns will contain the city and state/province of the location associated with each department.

Relational Algebra Expression:

Relational Algebra Tree:

Visual Presentation:

Alternative Solutions:

Using WHERE Clause with Equality Check:

SELECT D.department_name, L.city, L.state_province
FROM departments D, locations L
WHERE D.location_id = L.location_id;

Using ANSI-92 JOIN Syntax:

SELECT D.department_name, L.city, L.state_province
FROM departments D
JOIN locations L ON D.location_id = L.location_id;

Using CROSS JOIN with WHERE Clause:

SELECT D.department_name, L.city, L.state_province
FROM departments D
CROSS JOIN locations L
WHERE 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: Employees including the first name of their manager.
Next SQL Exercise: Display employees who have or have not any department.