Project Work Session 2

Goals

By the end of this session you should have:

Data inserted into all child/dependent tables using subqueries for FK resolution
At least one INSERT INTO ... SELECT statement written and tested
The full script run successfully from scratch (first execution)
The full script run a second time with identical row counts (idempotency verified)

Session Structure

Time	Activity
0–10 min	Common issues from session 1
10–60 min	Independent work
60–75 min	Checkpoint: run script twice, compare row counts
75–80 min	Wrap-up

Work Guidance

Priority 1 — Child table inserts with subquery FK resolution

After inserting parent/reference data in session 1, insert child records. Use subqueries for every FK value.

orders depends on customers:

SQL

-- ============================================================
-- orders  (FK: customer_id -> customers)
-- ============================================================
INSERT INTO orders (customer_id, order_date, status) VALUES
    ((SELECT customer_id FROM customers WHERE email = 'alice@shop.com'),
     '2026-01-10', 'delivered'),
    ((SELECT customer_id FROM customers WHERE email = 'boris@shop.com'),
     '2026-01-22', 'confirmed'),
    ((SELECT customer_id FROM customers WHERE email = 'clara@shop.com'),
     '2026-02-03', 'pending'),
    ((SELECT customer_id FROM customers WHERE email = 'alice@shop.com'),
     '2026-02-18', 'delivered'),
    ((SELECT customer_id FROM customers WHERE email = 'david@shop.com'),
     '2026-03-05', 'confirmed')
ON CONFLICT DO NOTHING;

-- verify
SELECT o.order_id, c.full_name, o.order_date, o.status
FROM orders o
JOIN customers c USING (customer_id);

order_items depends on both orders and products:

For order_items there is no natural UNIQUE key, so the INSERT can be run multiple times and will create duplicates. Handle this by relying on the TRUNCATE at the top of the script to clear the table before re-inserting.

SQL

-- ============================================================
-- order_items  (FK: order_id -> orders, product_id -> products)
-- Note: no UNIQUE constraint — idempotency is handled by TRUNCATE
-- ============================================================
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT
    (SELECT order_id FROM orders
     WHERE customer_id = (SELECT customer_id FROM customers WHERE email = 'alice@shop.com')
       AND order_date = '2026-01-10'),
    (SELECT product_id FROM products WHERE name = 'Laptop Pro 15'),
    1,
    1299.99;

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT
    (SELECT order_id FROM orders
     WHERE customer_id = (SELECT customer_id FROM customers WHERE email = 'alice@shop.com')
       AND order_date = '2026-01-10'),
    (SELECT product_id FROM products WHERE name = 'Wireless Mouse'),
    2,
    29.99;

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT
    (SELECT order_id FROM orders
     WHERE customer_id = (SELECT customer_id FROM customers WHERE email = 'boris@shop.com')
       AND order_date = '2026-01-22'),
    (SELECT product_id FROM products WHERE name = 'PostgreSQL for Beginners'),
    1,
    34.95;

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT
    (SELECT order_id FROM orders
     WHERE customer_id = (SELECT customer_id FROM customers WHERE email = 'clara@shop.com')
       AND order_date = '2026-02-03'),
    (SELECT product_id FROM products WHERE name = 'Running Shoes X200'),
    1,
    89.90;

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT
    (SELECT order_id FROM orders
     WHERE customer_id = (SELECT customer_id FROM customers WHERE email = 'david@shop.com')
       AND order_date = '2026-03-05'),
    (SELECT product_id FROM products WHERE name = 'Garden Hose 20m'),
    3,
    24.50;

Priority 2 — INSERT INTO ... SELECT

The order_summary table is a perfect candidate for INSERT INTO ... SELECT. Instead of writing each row manually, derive it from existing orders and order_items data:

SQL

-- ============================================================
-- order_summary  (INSERT INTO ... SELECT)
-- Aggregate from orders and order_items
-- ============================================================
INSERT INTO order_summary (customer_id, order_count, total_value)
SELECT
    o.customer_id,
    COUNT(DISTINCT o.order_id)                        AS order_count,
    COALESCE(SUM(oi.quantity * oi.unit_price), 0)     AS total_value
FROM orders o
LEFT JOIN order_items oi USING (order_id)
GROUP BY o.customer_id
ON CONFLICT (customer_id) DO NOTHING;

-- verify
SELECT c.full_name, os.order_count, os.total_value
FROM order_summary os
JOIN customers c USING (customer_id)
ORDER BY os.total_value DESC;

This is your required INSERT INTO ... SELECT statement. It does real work: it calculates aggregated data from two tables and inserts the results into a summary table.

Priority 3 — Idempotency test

Once your full script runs without errors, run the idempotency test:

SQL

-- Step 1: record current row counts
SELECT 'categories'    AS tbl, COUNT(*) FROM categories
UNION ALL SELECT 'customers',    COUNT(*) FROM customers
UNION ALL SELECT 'products',     COUNT(*) FROM products
UNION ALL SELECT 'orders',       COUNT(*) FROM orders
UNION ALL SELECT 'order_items',  COUNT(*) FROM order_items
UNION ALL SELECT 'order_summary', COUNT(*) FROM order_summary;

Now run your full script again from the top. Then run the same count query. The numbers must be identical.

If any count increased, you have a missing ON CONFLICT DO NOTHING or a table that is not in the TRUNCATE block.

Checkpoint Questions

The instructor will verify the following during the checkpoint:

Run your full script on a clean database — go ahead.
Run it again. Show me the row counts — are they the same?
Show me the INSERT INTO ... SELECT statement. Explain what it inserts and where the data comes from.
Open order_items. Is there a UNIQUE constraint? How does your script handle idempotency for a table without a natural unique key?
Is there any hardcoded integer ID in your script? Show me a subquery that replaced one.

Troubleshooting

Row counts increase on the second run

A table is missing ON CONFLICT DO NOTHING and is not covered by the TRUNCATE block. Check every INSERT statement — every table with a UNIQUE constraint needs the ON CONFLICT clause.

INSERT INTO ... SELECT inserts zero rows

The source query (the SELECT part) returns no rows. Run the SELECT alone first and confirm it returns data:

SQL

SELECT
    o.customer_id,
    COUNT(DISTINCT o.order_id),
    COALESCE(SUM(oi.quantity * oi.unit_price), 0)
FROM orders o
LEFT JOIN order_items oi USING (order_id)
GROUP BY o.customer_id;

Subquery in VALUES returns more than one row

You have multiple rows in a parent table that match your WHERE clause. Make the WHERE clause more specific — use a column with a UNIQUE constraint:

SQL

-- Wrong: could match multiple rows
(SELECT order_id FROM orders WHERE customer_id = ...)

-- Better: use a combination that is truly unique
(SELECT order_id FROM orders
 WHERE customer_id = (SELECT customer_id FROM customers WHERE email = 'alice@shop.com')
   AND order_date = '2026-01-10')