Views Exercise Solutions

view solutions, CTEs, joins, tree data

PostgreSQL Views Exercise: Managing Paris Tree Data

Scenario

You are a data analyst working for the Paris Parks and Gardens Department. Your team is responsible for maintaining and analyzing the extensive database of trees in Paris. To streamline common queries and improve data accessibility for various stakeholders, you’ve been tasked with creating and managing views based on the existing trees database.

Exercise Tasks

1. Create a Basic View

Create a view named v_tree_info that combines basic information about trees, including their ID, height, circumference, and location details.

CREATE VIEW v_tree_info AS
SELECT
    t.id,
    t.height,
    t.circumference,
    l.address,
    l.arrondissement
FROM public.trees t
JOIN public.locations l ON t.location_id = l.id;

Verify the view

SELECT * FROM v_tree_info LIMIT 5;

2. List All Views

List all views in the current database to confirm the creation of v_tree_info.

SELECT table_name
FROM information_schema.views
WHERE table_schema = 'public';

You can also simply use \dv

3. Create a Filtered View

Create a view named v_remarkable_trees that shows only remarkable trees with their taxonomy (name, genrre, species, variety) information. You can use a CTE to first get the taxonomy columns and then join on trees and locations.

CREATE VIEW v_remarkable_trees AS
WITH tree_taxonomy AS (
    SELECT
        t.id,
        tn.name AS tree_name,
        tg.genre,
        ts.species,
        tv.variety
    FROM trees t
    JOIN taxonomy tx ON t.taxonomy_id = tx.id
    LEFT JOIN tree_names tn ON tx.name_id = tn.id
    LEFT JOIN tree_genres tg ON tx.genre_id = tg.id
    LEFT JOIN tree_species ts ON tx.species_id = ts.id
    LEFT JOIN tree_varieties tv ON tx.variety_id = tv.id
)
SELECT
    tt.*,
    t.height,
    t.circumference,
    l.address
FROM tree_taxonomy tt
JOIN trees t ON tt.id = t.id
JOIN locations l ON t.location_id = l.id
WHERE t.remarkable = true;

Query the view

SELECT * FROM v_remarkable_trees LIMIT 5;

4. Use a View in a SELECT Statement

Write a query that uses the v_tree_info view to find the average height of trees in each arrondissement.

SELECT
    arrondissement,
    AVG(height) AS avg_height
FROM
    v_remarkable_trees
GROUP BY
    arrondissement
ORDER BY
    avg_height DESC;

5. Drop a View

Drop the v_tree_info view and verify that it has been removed.

DROP VIEW IF EXISTS v_tree_info;

-- Verify that the view has been dropped
SELECT table_name
FROM information_schema.views
WHERE table_schema = 'public';