w3resource

PostgreSQL JOINS: Find the addresses of all the departments


1. Write a query to find the addresses, including location_id, street_address, city, state_province and country_name of all the departments.

Sample Solution:

Code:

SELECT location_id, street_address, city, state_province, country_name
FROM locations
NATURAL JOIN countries;

Sample table: locations


Sample table: countries


Output:

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

Practice Online


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

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming