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:
-- This SQL query retrieves location details along with associated country and department information using natural joins.
SELECT location_id, -- Selects the location_id column
street_address, -- Selects the street_address column
city, -- Selects the city column
state_province, -- Selects the state_province column
country_name, -- Selects the country_name column
department_name -- Selects the department_name column
FROM locations -- Specifies the first table from which to retrieve data, in this case, the locations table
NATURAL JOIN countries -- Performs a natural join with the countries table to include associated country information
NATURAL JOIN departments; -- Performs a natural join with the departments table to include associated department information
Explanation:
- This SQL query retrieves location details along with associated country and department information.
- The SELECT statement selects various columns including location_id, street_address, city, state_province, country_name, and department_name.
- The FROM clause specifies the first table from which to retrieve data, which is the locations table.
- The NATURAL JOIN keywords are used to perform natural joins with the countries and departments tables.
- Natural join matches the columns with the same name in both tables and includes only the matching rows from both tables.
- As a result, the query combines data from the locations, countries, and departments tables based on the matching columns (country_id and department_id) without the need to specify join conditions explicitly.
- The result set will contain location details along with associated country and department information for each location.
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 ......... 3200 Mariano Escobedo 999 11932 Mexico Cit Distrito Feder MX
Sample table: countries
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 ...... ZW Zimbabwe 4
Sample table: departments
+---------------+----------------------+------------+-------------+ | DEPARTMENT_ID | DEPARTMENT_NAME | MANAGER_ID | LOCATION_ID | +---------------+----------------------+------------+-------------+ | 10 | Administration | 200 | 1700 | | 20 | Marketing | 201 | 1800 | | 30 | Purchasing | 114 | 1700 | | 40 | Human Resources | 203 | 2400 | | 50 | Shipping | 121 | 1500 | | 60 | IT | 103 | 1400 | | 70 | Public Relations | 204 | 2700 | | 80 | Sales | 145 | 2500 | | 90 | Executive | 100 | 1700 | ........ | 270 | Payroll | 0 | 1700 | +---------------+----------------------+------------+-------------+
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:
Go to:
PREV : 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.
Practice Online
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?