MySQL Joins Exercises: Find the addresses of all the departments

MySQL Joins: Exercise-1 with Solution

Write a MySQL query to find the addresses (location_id, street_address, city, state_province, country_name) of all the departments.

country_id  country_name  region_id
----------  ------------  ----------
country_id  country_name  region_id
AR          Argentina     2
AU          Australia     3
BE          Belgium       1
BR          Brazil        2
CA          Canada        2
CH          Switzerland   1
CN          China         3
DE          Germany       1
DK          Denmark       1
EG          Egypt         4
FR          France        1
HK          HongKong      3
IL          Israel        4
IN          India         3
IT          Italy         1
JP          Japan         3
KW          Kuwait        4
MX          Mexico        2
NG          Nigeria       4
NL          Netherlands   1
SG          Singapore     3
UK          United Kingd  1
US          United State  2
ZM          Zambia        4
ZW          Zimbabwe      4

Code:

-- This SQL query selects specific columns from the 'locations' table after performing a natural join with the 'countries' table.

SELECT 
    location_id, -- Selecting the 'location_id' column from the result set.
    street_address, -- Selecting the 'street_address' column from the result set.
    city, -- Selecting the 'city' column from the result set.
    state_province, -- Selecting the 'state_province' column from the result set.
    country_name -- Selecting the 'country_name' column from the result set.
FROM 
    locations -- Specifying the 'locations' table.
NATURAL JOIN 
    countries; -- Performing a natural join with the 'countries' table based on any common columns.

Explanation:

• This SQL query is used to retrieve specific columns from the 'locations' table after joining it with the 'countries' table.
• The 'NATURAL JOIN' keyword is used to join the two tables based on columns with the same name and data type in both tables.
• The columns selected for the output are 'location_id', 'street_address', 'city', 'state_province', and 'country_name'.

Relational Algebra Expression:

Relational Algebra Tree:

Sample Output:

location_id		street_address			city				state_province			country_name
1000			1297 Via Cola di Rie		Roma								Italy
1100			93091 Calle della Testa		Venice								Italy
1200			2017 Shinjuku-ku		Tokyo				Tokyo Prefecture		Japan
1300			9450 Kamiya-cho			Hiroshima							Japan
1400			2014 Jabberwocky Rd		Southlake			Texas				United States of America
1500			2011 Interiors Blvd		South San Francisco		California			United States of America
1600			2007 Zagora St			South Brunswick			New Jersey			United States of America
1700			2004 Charade Rd			Seattle				Washington			United States of America
1800			147 Spadina Ave			Toronto				Ontario				Canada
1900			6092 Boxwood St			Whitehorse			Yukon				Canada
2000			40-5-12 Laogianggen		Beijing								China
2100			1298 Vileparle (E)		Bombay				Maharashtra			India
2200			12-98 Victoria Street		Sydney				New South Wales			Australia
2300			198 Clementi North		Singapore							Singapore
2400			8204 Arthur St			London								United Kingdom
2600			9702 Chester Road		Stretford			Manchester			United Kingdom
2700			Schwanthalerstr. 7031		Munich				Bavaria				Germany
2800			Rua Frei Caneca 1360		Sao Paulo			Sao Paulo			Brazil
2900			20 Rue des Corps-Saints		Geneva				Geneve				Switzerland
3000			Murtenstrasse 921		Bern				BE				Switzerland
3100			Pieter Breughelstraat 837	Utrecht				Utrecht				Netherlands

MySQL Code Editor:

Structure of 'hr' database :

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

Previous:MySQL Joins
Next:Write a MySQL query to find the name (first_name, last name), department ID and name of all the employees.