INSERT Practice (Session 1)

Goals

Write INSERT INTO ... SELECT ... statements to populate tables from existing data
Use subqueries in VALUES to resolve foreign key IDs dynamically
Use ON CONFLICT DO NOTHING to prevent duplicate inserts
Verify inserts with SELECT statements after each block

Warm-up

Task (5 min): Without running any queries, write the INSERT dependency order for these tables: rental, payment, customer, staff, inventory. Which table must be inserted first?

Hint

`customer` and `staff` have no FK dependencies on the others. `inventory` depends on `film` and `store`. `rental` depends on `customer`, `staff`, and `inventory`. `payment` depends on `rental` and `customer`.

Steps

Step 1 — Create a test table and insert with SELECT

Create a summary table and populate it from the DVD Rental database:

SQL

CREATE TABLE IF NOT EXISTS film_summary (
    film_id     INT PRIMARY KEY,
    title       VARCHAR(255),
    rating      VARCHAR(10),
    rental_rate NUMERIC(4,2)
);

-- Populate from existing data
INSERT INTO film_summary (film_id, title, rating, rental_rate)
SELECT film_id, title, rating::VARCHAR, rental_rate
FROM film
WHERE rental_rate > 2.99
ON CONFLICT DO NOTHING;

-- Verify
SELECT COUNT(*) FROM film_summary;

Expected: The table is populated with films having rental rate above 2.99. Running again produces no new rows.

Step 2 — Insert with a subquery to resolve a FK

Create a table that links actors to film categories (denormalized for reporting):

SQL

CREATE TABLE IF NOT EXISTS actor_category_link (
    actor_id    INT,
    category_id INT,
    film_count  INT,
    PRIMARY KEY (actor_id, category_id)
);

INSERT INTO actor_category_link (actor_id, category_id, film_count)
SELECT
    fa.actor_id,
    fc.category_id,
    COUNT(*) AS film_count
FROM film_actor fa
JOIN film_category fc ON fa.film_id = fc.film_id
GROUP BY fa.actor_id, fc.category_id
ON CONFLICT DO NOTHING;

SELECT * FROM actor_category_link LIMIT 5;

Step 3 — INSERT with subquery in VALUES

Insert a single row where the FK value is looked up by name:

SQL

CREATE TABLE IF NOT EXISTS featured_film (
    id          SERIAL PRIMARY KEY,
    film_id     INT NOT NULL,
    featured_on DATE DEFAULT CURRENT_DATE
);

-- Insert by title lookup — no hardcoded ID
INSERT INTO featured_film (film_id, featured_on)
VALUES (
    (SELECT film_id FROM film WHERE title = 'ACADEMY DINOSAUR'),
    '2026-03-01'
)
ON CONFLICT DO NOTHING;

SELECT f.title, ff.featured_on
FROM featured_film ff
JOIN film f ON ff.film_id = f.film_id;

Step 4 — Re-runability test

Run the INSERT statements from Steps 1, 2, and 3 a second time. Verify:

Row counts do not change
No errors appear
ON CONFLICT DO NOTHING is suppressing duplicates silently

SQL

-- Before second run
SELECT COUNT(*) FROM film_summary;
SELECT COUNT(*) FROM actor_category_link;
SELECT COUNT(*) FROM featured_film;

-- Run inserts again

-- After second run — should be identical counts
SELECT COUNT(*) FROM film_summary;
SELECT COUNT(*) FROM actor_category_link;
SELECT COUNT(*) FROM featured_film;

Bonus Tasks

RETURNING clause: Modify the featured_film insert to use RETURNING id, film_id — capture the generated ID.
Bulk insert via CTE: Write a CTE that selects the top 5 highest-rated films, then INSERT the results into featured_film.
Conditional insert: Write an INSERT that only inserts a film if it does not already exist in featured_film, using NOT EXISTS instead of ON CONFLICT.