SQLite Exercise: Find the addresses of all the departments

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

Sample table : locations

location_id  street_address        postal_code  city        state_province  country_id
-----------  --------------------  -----------  ----------  --------------  ----------
1000         1297 Via Cola di Rie  989          Roma                        IT
1100         93091 Calle della Te  10934        Venice                      IT
1200         2017 Shinjuku-ku      1689         Tokyo       Tokyo Prefectu  JP
1300         9450 Kamiya-cho       6823         Hiroshima                   JP
1400         2014 Jabberwocky Rd   26192        Southlake   Texas           US
1500         2011 Interiors Blvd   99236        South San   California      US
1600         2007 Zagora St        50090        South Brun  New Jersey      US
1700         2004 Charade Rd       98199        Seattle     Washington      US
1800         147 Spadina Ave       M5V 2L7      Toronto     Ontario         CA
1900         6092 Boxwood St       YSW 9T2      Whitehorse  Yukon           CA
2000         40-5-12 Laogianggen   190518       Beijing                     CN
2100         1298 Vileparle (E)    490231       Bombay      Maharashtra     IN
2200         12-98 Victoria Stree  2901         Sydney      New South Wale  AU
2300         198 Clementi North    540198       Singapore                   SG
2400         8204 Arthur St                     London                      UK
2500         Magdalen Centre, The  OX9 9ZB      Oxford      Oxford          UK
2600         9702 Chester Road     9629850293   Stretford   Manchester      UK
2700         Schwanthalerstr. 703  80925        Munich      Bavaria         DE
2800         Rua Frei Caneca 1360  01307-002    Sao Paulo   Sao Paulo       BR
2900         20 Rue des Corps-Sai  1730         Geneva      Geneve          CH
3000         Murtenstrasse 921     3095         Bern        BE              CH
3100         Pieter Breughelstraa  3029SK       Utrecht     Utrecht         NL
3200         Mariano Escobedo 999  11932        Mexico Cit  Distrito Feder  MX

Sample table :countries

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

SQLite Code:

 -- Selecting specific columns from the "locations" table and columns related to countries using a natural join
SELECT location_id, street_address, city, state_province, country_name
-- Specifying the table from which to retrieve the data, in this case, "locations"
FROM locations
-- Performing a natural join with the "countries" table based on common columns
NATURAL JOIN countries; 

Relational Algebra Expression:

Relational Algebra Tree:

Output:

location_id  street_address        city        state_province  country_name
-----------  --------------------  ----------  --------------  ------------
1000         1297 Via Cola di Rie  Roma                        Italy
1100         93091 Calle della Te  Venice                      Italy
1200         2017 Shinjuku-ku      Tokyo       Tokyo Prefectu  Japan
1300         9450 Kamiya-cho       Hiroshima                   Japan
1400         2014 Jabberwocky Rd   Southlake   Texas           United State
...				...					...			...				...
2800         Rua Frei Caneca 1360  Sao Paulo   Sao Paulo       Brazil
2900         20 Rue des Corps-Sai  Geneva      Geneve          Switzerland
3000         Murtenstrasse 921     Bern        BE              Switzerland
3100         Pieter Breughelstraa  Utrecht     Utrecht         Netherlands
3200         Mariano Escobedo 999  Mexico Cit  Distrito Feder  Mexico

Explanation:

The above SQLite query retrieves specific columns from the "locations" table and combines them with related columns from the "countries" table using a natural join. The natural join is based on common column names, and the resulting dataset includes information from both tables where matches exist.

Here's a brief explanation of each part of SQLite code:

• SELECT clause:
• It selects specific columns, including location_id, street_address, city, state_province, and country_name.
• FROM clause:
• Specifies the table from which to retrieve the data, in this case, the "locations" table.
• NATURAL JOIN clause:
• Perform a natural join to the "countries" table. A natural join automatically matches columns with the same names in both tables. The common columns used for the join are not explicitly specified but are inferred based on the column names common to both "locations" and "countries."

Practice SQLite Online

Model Database

Structure of 'hr' database :

Improve this sample solution and post your code through Disqus.

Previous: SQLite Joins
Next: Write a query to find the employee id, name (last_name) along with their manager_id, manager name (last_name).