Project: Task Briefing

Project Overview

This project brings together everything you have learned about INSERT statements, subqueries, CTEs, and idempotent scripting. You will receive a provided PostgreSQL schema and must write a single .sql file that populates the database with realistic data — and can be run again and again without error.

This is not a theory exercise. You are writing production-quality data scripts of the kind used in real development workflows: migrations, seed scripts, test fixtures, and demo data loaders.

By the end of the project you will have a script that a teammate could checkout from version control, run on a fresh database, and get exactly the right data — every time.

Provided Schema

The project uses a simplified e-commerce schema with the following tables:

CREATE TABLE IF NOT EXISTS categories (
    category_id SERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS customers (
    customer_id SERIAL PRIMARY KEY,
    full_name   VARCHAR(150) NOT NULL,
    email       VARCHAR(150) NOT NULL UNIQUE,
    joined_date DATE NOT NULL DEFAULT CURRENT_DATE
);

CREATE TABLE IF NOT EXISTS products (
    product_id  SERIAL PRIMARY KEY,
    name        VARCHAR(150) NOT NULL UNIQUE,
    category_id INTEGER NOT NULL REFERENCES categories(category_id),
    price       NUMERIC(10, 2) NOT NULL CHECK (price > 0),
    stock       INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS orders (
    order_id    SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
    order_date  DATE NOT NULL DEFAULT CURRENT_DATE,
    status      VARCHAR(50) NOT NULL DEFAULT 'pending'
);

CREATE TABLE IF NOT EXISTS order_items (
    item_id    SERIAL PRIMARY KEY,
    order_id   INTEGER NOT NULL REFERENCES orders(order_id),
    product_id INTEGER NOT NULL REFERENCES products(product_id),
    quantity   INTEGER NOT NULL CHECK (quantity > 0),
    unit_price NUMERIC(10, 2) NOT NULL
);

CREATE TABLE IF NOT EXISTS order_summary (
    summary_id    SERIAL PRIMARY KEY,
    customer_id   INTEGER NOT NULL REFERENCES customers(customer_id) UNIQUE,
    order_count   INTEGER NOT NULL DEFAULT 0,
    total_value   NUMERIC(12, 2) NOT NULL DEFAULT 0
);

Study this schema carefully before you write a single INSERT statement.

Project Requirements

Deliverable: A single .sql file that meets every requirement below.

Functional Requirements

1. Create all tables using CREATE TABLE IF NOT EXISTS. The script must be self-contained — a fresh database must work with no manual setup.
2. Reset data using TRUNCATE ... CASCADE before every INSERT block. The TRUNCATE order must be correct (most dependent tables first).
3. Populate all tables — at least 5 rows per table.
4. No hardcoded FK IDs — every foreign key value must be resolved using a subquery or CTE. Do not write category_id = 3.
5. At least one INSERT INTO ... SELECT statement — insert data derived from existing rows.
6. At least one CTE + INSERT pattern — use a WITH expression to prepare data before inserting it.
7. ON CONFLICT DO NOTHING on every INSERT that could fail on re-run (any table with a UNIQUE constraint).
8. Runs cleanly twice — the second run must produce no errors and no extra rows.

Code Quality Requirements

• Script is commented: each section has a short header comment explaining what it does
• Data is realistic and domain-appropriate (real-looking names, prices, dates)
• The file ends with a verification block: SELECT COUNT(*) from each table

Grading Criteria

Session Structure

Getting Started

Step 1 — Draw the FK dependency tree

Before writing any SQL, identify which tables depend on which others:

categories       (no FKs — root table, insert first)
customers        (no FKs — root table, insert first)
    |
products  --------> categories
orders    --------> customers
    |
order_items ------> orders, products
order_summary ----> customers

Insert order: categories and customers first, then products, then orders, then order_items and order_summary.

TRUNCATE order (reverse — most dependent first): order_summary, order_items, orders, products, customers, categories.

Step 2 — Write the TRUNCATE block first

Write your TRUNCATE block before any INSERTs. This forces you to think through FK order before you start writing data:

-- ============================================================
-- RESET: truncate in reverse FK dependency order
-- ============================================================
TRUNCATE TABLE order_summary  CASCADE;
TRUNCATE TABLE order_items    CASCADE;
TRUNCATE TABLE orders         CASCADE;
TRUNCATE TABLE products       CASCADE;
TRUNCATE TABLE customers      CASCADE;
TRUNCATE TABLE categories     CASCADE;

Run this block alone and confirm it executes without errors.

Step 3 — Insert root tables first

Write inserts for tables with no FK columns (categories, customers). Add ON CONFLICT DO NOTHING so the script is idempotent:

-- ============================================================
-- categories
-- ============================================================
INSERT INTO categories (name) VALUES
    ('Electronics'),
    ('Books'),
    ('Clothing'),
    ('Home & Garden'),
    ('Sports')
ON CONFLICT DO NOTHING;

Step 4 — Use subqueries for FK values

Never write a numeric ID directly. Look up parent rows by their natural key:

-- ============================================================
-- products
-- ============================================================
INSERT INTO products (name, category_id, price, stock) VALUES
    ('Laptop Pro 15',
     (SELECT category_id FROM categories WHERE name = 'Electronics'),
     1299.99, 50),
    ('Wireless Mouse',
     (SELECT category_id FROM categories WHERE name = 'Electronics'),
     29.99, 200)
ON CONFLICT DO NOTHING;

Step 5 — Test after each table

After inserting each table, run a count to confirm the rows are there:

SELECT COUNT(*) FROM categories;   -- expect 5
SELECT COUNT(*) FROM products;     -- expect at least 5

Common Planning Mistakes

Forgetting TRUNCATE on a child table before its parent. If order_items references orders, you must truncate order_items before orders. Otherwise the TRUNCATE fails with a FK violation.

Hardcoding IDs "just for now." Every student who hardcodes IDs "temporarily" forgets to fix them. Write the subquery the first time.

Not testing re-runnability until the last session. Test after session 2 — before you add complexity.

Writing all INSERTs before testing any. Insert one table, run a SELECT, confirm it worked. Then move on. Debugging 200 lines of SQL at once is painful.

Forgetting ON CONFLICT DO NOTHING. If any table has a UNIQUE constraint and you do not add this clause, the second run will fail with a duplicate key error.