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

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:

``````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
Marketing		Toronto	Ontario
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:

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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿

SQL: Tips of the Day

Count the number of occurrences of a string in a VARCHAR field?

```SELECT
title,
description,
ROUND (
(
LENGTH(description)
- LENGTH( REPLACE ( description, "value", "") )
) / LENGTH("value")
) AS count
FROM <table>
```

Ref: https://bit.ly/3PvNdT4