SQL Exercise: Display country, city, and the departments

SQL JOINS on HR Database: Exercise-17 with Solution

17. From the following table, write a SQL query to find the name of the country, city, and departments, which are running there.

Sample table: countries

Sample table: locations

Sample table: departments

Sample Solution:

-- Selecting specific columns (country_name, city, department_name) from the 'countries' table
SELECT country_name, city, department_name 

-- Performing an INNER JOIN between the 'countries' table and the 'locations' table using the common column 'country_id'
FROM countries 

JOIN locations USING (country_id) 

-- Performing another INNER JOIN between the result set and the 'departments' table using the common column 'location_id'
JOIN departments USING (location_id);

Sample Output:

country_name			city		department_name
Canada				Toronto		Marketing
Germany				Munich		Public Relations
United Kingdom			London		Human Resources
United States of America	Seattle		Payroll
United States of America	Seattle		Recruiting
United States of America	Seattle		Retail Sales
United States of America	Seattle		Government Sales
United States of America	Seattle		IT Helpdesk
United States of America	Seattle		NOC
United States of America	Seattle		IT Support
United States of America	Seattle		Operations
United States of America	Seattle		Contracting
United States of America	Seattle		Construction
United States of America	Seattle		Manufacturing
United States of America	Seattle		Benefits
United States of America	Seattle		Shareholder Services
United States of America	Seattle		Control And Credit
United States of America	Seattle		Corporate Tax
United States of America	Seattle		Treasury
United States of America	Seattle		Accounting
United States of America	Seattle		Finance
United States of America	Seattle		Executive
United States of America	Southlake	IT
United States of America	South San Francisco Shipping
United States of America	Seattle		Purchasing
United States of America	Seattle		Administration

Code Explanation:

The said query in SQL that retrieves data from the 'countries', 'locations', and 'departments' tables using the "JOIN" keyword to combine the data based on matching columns.
The query selects three columns: "country_name", "city", and "department_name".
The 'countries' and 'locations' tables are joined using the "country_id" column and the 'locations' and 'departments' tables are joined using the "location_id" column.

Relational Algebra Expression:

Relational Algebra Tree:

Visual Presentation:

Alternative Solutions:

Using INNER JOIN with Aliases:

SELECT c.country_name, l.city, d.department_name
FROM countries c
JOIN locations l ON c.country_id = l.country_id
JOIN departments d ON l.location_id = d.location_id;

Explanation:

This query uses INNER JOINs with aliases (c for countries, l for locations, d for departments). It combines the three tables based on matching keys ("country_id" and "location_id") and selects the desired columns.

Using INNER JOIN with Explicit Column Names:

SELECT countries.country_name, locations.city, departments.department_name
FROM countries
JOIN locations ON countries.country_id = locations.country_id
JOIN departments ON locations.location_id = departments.location_id;

Explanation:

This query uses INNER JOINs but explicitly specifies column names. It performs INNER JOINs based on matching keys and selects the desired columns.

Using NATURAL JOIN:

SELECT country_name, city, department_name
FROM countries
NATURAL JOIN locations
NATURAL JOIN departments;

Explanation:

This query uses NATURAL JOINs, which automatically join tables based on columns with the same name. It combines the three tables and selects the desired columns.

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

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

Previous SQL Exercise: Salary differences of employees in the department 80.
Next SQL Exercise: Department name and the full name of the manager.