w3resource
SQL exercises

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. Write a query in SQL 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

Practice Online


HR database model

Query Visualization:

Duration:

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

Rows:

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

Cost:

Query visualization of Display the department name, city, and state province for each department - Cost

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

Previous: Write a query in SQL to display the first name of all employees including the first name of their manager.
Next: Write a query in SQL to display the first name, last name, department number and name, for all employees who have or have not any department.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming