SQL JOINS on HR Database: Display the department name, city, and state province 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:

SELECT D.department_name , L.city , L.state_province
  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

Relational Algebra Expression:

Relational Algebra Tree:

Pictorial Presentation:

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: From the following table, write a SQL query to find the employees and their managers. Return the first name of the employee and manager.
Next: From the following tables, write a SQL query to find those employees who have or not any department. Return first name, last name, department ID, department name.