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,department_name
FROM locations
NATURAL JOIN countries
NATURAL JOIN departments;

Sample table: locations

Sample table: countries

Sample table: departments

Output:

pg_exercises=# SELECT location_id, street_address, city, state_province, country_name,department_name
pg_exercises=# FROM locations
pg_exercises=# NATURAL JOIN countries
pg_exercises=# NATURAL JOIN departments;
 location_id |    street_address     |        city         | state_province |       country_name       |   department_name
-------------+-----------------------+---------------------+----------------+--------------------------+----------------------
        1800 | 147 Spadina Ave       | Toronto             | Ontario        | Canada                   | Marketing
        2700 | Schwanthalerstr. 7031 | Munich              | Bavaria        | Germany                  | Public Relations
        2400 | 8204 Arthur St        | London              |                | United Kingdom           | Human Resources
        1700 | 2004 Charade Rd       | Seattle             | Washington     | United States of America | Payroll
        1700 | 2004 Charade Rd       | Seattle             | Washington     | United States of America | Recruiting
        1700 | 2004 Charade Rd       | Seattle             | Washington     | United States of America | Retail Sales
        1700 | 2004 Charade Rd       | Seattle             | Washington     | United States of America | Government Sales
        1700 | 2004 Charade Rd       | Seattle             | Washington     | United States of America | IT Helpdesk
        1700 | 2004 Charade Rd       | Seattle             | Washington     | United States of America | NOC
        1700 | 2004 Charade Rd       | Seattle             | Washington     | United States of America | IT Support
        1700 | 2004 Charade Rd       | Seattle             | Washington     | United States of America | Operations
        1700 | 2004 Charade Rd       | Seattle             | Washington     | United States of America | Contracting
        1700 | 2004 Charade Rd       | Seattle             | Washington     | United States of America | Construction
        1700 | 2004 Charade Rd       | Seattle             | Washington     | United States of America | Manufacturing
        1700 | 2004 Charade Rd       | Seattle             | Washington     | United States of America | Benefits
        1700 | 2004 Charade Rd       | Seattle             | Washington     | United States of America | Shareholder Services
        1700 | 2004 Charade Rd       | Seattle             | Washington     | United States of America | Control And Credit
        1700 | 2004 Charade Rd       | Seattle             | Washington     | United States of America | Corporate Tax
        1700 | 2004 Charade Rd       | Seattle             | Washington     | United States of America | Treasury
        1700 | 2004 Charade Rd       | Seattle             | Washington     | United States of America | Accounting
        1700 | 2004 Charade Rd       | Seattle             | Washington     | United States of America | Finance
        1700 | 2004 Charade Rd       | Seattle             | Washington     | United States of America | Executive
        1400 | 2014 Jabberwocky Rd   | Southlake           | Texas          | United States of America | IT
        1500 | 2011 Interiors Blvd   | South San Francisco | California     | United States of America | Shipping
        1700 | 2004 Charade Rd       | Seattle             | Washington     | United States of America | Purchasing
        1700 | 2004 Charade Rd       | Seattle             | Washington     | United States of America | Administration
(26 rows)

Relational Algebra Expression:

Relational Algebra Tree:

Practice Online

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

Previous: PostgreSQL JOINS - Exercises, Practice, Solution
Next: Write a query to make a join with employees and departments table to find the name of the employee, including first_name and last name, department ID and name of departments.