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

Sample table: countries

SQLite Code:

SELECT location_id, street_address, city, state_province, country_name
FROM locations
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
1500         2011 Interiors Blvd   South San   California      United State
1600         2007 Zagora St        South Brun  New Jersey      United State
1700         2004 Charade Rd       Seattle     Washington      United State
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 Stree  Sydney      New South Wale  Australia
2300         198 Clementi North    Singapore                   Singapore
2400         8204 Arthur St        London                      United Kingd
2500         Magdalen Centre, The  Oxford      Oxford          United Kingd
2600         9702 Chester Road     Stretford   Manchester      United Kingd
2700         Schwanthalerstr. 703  Munich      Bavaria         Germany
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

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).