Review and Stretch Goals

You have a working test pack. This lesson is your self-review checklist, a small set of reflection questions, and the stretch goals that turn a competent capstone into the kind of artefact a team genuinely adopts. There is no "Practice Task" at the end — the practice is the project itself, and the review below tells you whether you've hit the bar.

Self-assessment checklist

For each item, run the relevant query (or read your file) and confirm. If a checkbox doesn't pass, go back to the lesson named in parentheses.

SELECT, FROM, WHERE — the basics

• Every verification query lists the columns it needs explicitly — no SELECT *. (Ch 2, L1)
• Every UPDATE and DELETE has a WHERE clause. (Ch 4, L2 and L3)
• Text values are single-quoted; numbers are unquoted; NULL checks use IS NULL not = NULL. (Ch 2, L1)

Filtering, joining, grouping

• At least one query uses IN (or NOT IN) instead of a chain of ORs. (Ch 3, L1)
• AND/OR mixed conditions are parenthesised. (Ch 3, L1)
• INNER JOIN and LEFT JOIN are both used in the pack — INNER for "must match," LEFT for "include unmatched." (Ch 3, L2 and L3)
• The duplicate-emails detector uses GROUP BY ... HAVING COUNT(*) > 1. (Ch 3, L4)
• The orphan-finder uses LEFT JOIN ... WHERE o.id IS NULL. (Ch 5, L3)

Modifying data

• The setup script wraps INSERTs in BEGIN ... COMMIT. (Ch 4, L4)
• The cleanup script deletes children before parents. (Ch 4, L3)
• Test-data rows have a recognisable suffix or pattern (+ci@qa.local, Test%, etc.) so cleanup is safe. (Ch 4, L3)

Verification & reports

• Every API/UI verification query returns one row in the happy path — easy to assert on. (Ch 5, L1)
• The order-total integrity query uses HAVING o.total <> SUM(oi.price * oi.quantity). (Ch 5, L3)
• At least one report uses CASE or COALESCE. (Ch 5, L4)

Subqueries (optional but encouraged)

• At least one query uses a subquery in WHERE or FROM. (Ch 6, L1)

Portability

• Your pack runs on SQLite and PostgreSQL. Any dialect-specific lines are clearly commented. (Ch 1, L4 plus dialect notes throughout)

If everything above checks out, you have a deliverable a real QA team would adopt.

Reflection questions

Spend ten minutes on these. They sound abstract; they're how you internalise what you just built.

1. Which query was hardest to write, and why? Most of the time the hard one is either the order-total integrity check (because it spans two tables and aggregates differently per row) or the top-customers report (because it joins three tables and orders on a derived value). Recognising what makes a query hard sharpens your instinct for the next one.
2. Which bug would your pack catch that the application's API/UI tests would not? If you had to pick one row from your integrity sweep that justifies the whole pack to a manager, which is it? Articulating that justification is half of getting the pack adopted.
3. What would change if the database had ten million rows instead of a thousand? Which queries get slow? Which need indexes? Which would you rewrite as derived tables? You don't need to optimise now — you need to know where you'd start.
4. Where does your verification rely on assumptions about the application that aren't in the SQL? "I assume the API uses transactions." "I assume soft delete sets deleted_at, not a separate flag." "I assume the foreign key is enforced." Each of those assumptions is something to test or confirm with engineering.
5. What's the one recurring query you'd promote into a view tomorrow? That's your first stretch goal.

Stretch goals

Five extensions you can take in any order. Each is genuinely useful, none take more than an hour.

1. Promote a verification into a VIEW

Pick the query you reach for most often — usually "active users" or "pending orders with their customer." Wrap it:

CREATE VIEW v_pending_orders_with_customer AS
SELECT o.id, o.total, o.created_at,
       u.id AS user_id, u.name, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'pending';

Now your test code does SELECT * FROM v_pending_orders_with_customer WHERE total > 50 instead of repeating the JOIN. Less typing, fewer typos, and if the schema changes, you update the view in one place.

2. A reset_test_data stored procedure

Wrap your 05_cleanup.sql in a procedure (syntax varies — pick your dialect's flavour):

-- PostgreSQL
CREATE PROCEDURE reset_test_data()
LANGUAGE SQL AS $$
    DELETE FROM order_items WHERE order_id IN (SELECT id FROM orders WHERE user_id IN (SELECT id FROM users WHERE email LIKE '%+ci@qa.local'));
    DELETE FROM orders     WHERE user_id IN (SELECT id FROM users WHERE email LIKE '%+ci@qa.local');
    DELETE FROM users      WHERE email LIKE '%+ci@qa.local';
$$;
 
-- Call it from CI:
CALL reset_test_data();

One line in your CI pipeline replaces five DELETE statements scattered across files.

3. Window-function trend reports

Use LAG() to compute week-over-week revenue change:

WITH weekly AS (
    SELECT DATE(created_at, 'weekday 0', '-7 days') AS week_start,
           SUM(total) AS revenue
    FROM orders
    GROUP BY week_start
)
SELECT
    week_start,
    revenue,
    LAG(revenue) OVER (ORDER BY week_start)         AS previous,
    revenue - LAG(revenue) OVER (ORDER BY week_start) AS week_over_week_change
FROM weekly
ORDER BY week_start DESC;

The PM will love it. Trend dashboards are made of queries exactly like this.

4. A data-quality dashboard

A single SELECT that returns a one-row health snapshot — same idea as Chapter 5, Lesson 4, but scaled to your full integrity sweep:

SELECT
    (SELECT COUNT(*) FROM users)                                 AS total_users,
    (SELECT COUNT(*) FROM users WHERE email LIKE '%+ci@qa.local') AS test_users,
    (SELECT COUNT(*) FROM orders)                                AS total_orders,
    (SELECT COUNT(*) FROM (SELECT email FROM users GROUP BY email HAVING COUNT(*) > 1)) AS duplicate_emails,
    (SELECT COUNT(*) FROM order_items oi LEFT JOIN orders o ON oi.order_id = o.id WHERE o.id IS NULL) AS orphan_items;

One row, every health metric you care about. Pin it on a dashboard.

5. Before/after diff — compare two test runs

After a release, you want to know what changed between two snapshots:

-- "Orders that changed status since the last snapshot"
SELECT o.id, o.status AS new_status, s.status AS previous_status
FROM orders o
JOIN orders_snapshot_yesterday s ON s.id = o.id
WHERE o.status <> s.status;

The setup is more involved (you need a snapshot table or table copy), but the value compounds — diff queries are how you spot side-effects of a release.

Where SQL fits in your wider QA practice

Where to go next

A handful of natural follow-ups depending on where you're heading:

• API verification. API Testing Masterclass — and specifically its Data Integrity and Database Verification lesson — covers when database verification earns its keep, the patterns that make it cheap to maintain, and the cleanup strategies that scale to large suites.
• Postman + SQL. API Testing with Postman covers running Postman collections in CI; pair them with your SQL pack and you have a complete API/data verification flow.
• UI + SQL. Cypress with TypeScript — Cypress lets you call into Node code from a test, which means you can run SQL assertions alongside UI assertions. The combination is powerful.
• Manual QA workflows. Manual Software Testing — the test-design and risk-based-testing chapters tell you which parts of your application most deserve a SQL verification layer.

Your handover

If you've built the pack, run the queries against your data, and worked through the self-review, you have shipped a real QA artefact: a SQL test pack that seeds, verifies, integrity-checks, reports on, and cleans up an e-commerce database. The same pattern — five files, around 25 queries, the whole test lifecycle in one folder — applies to any data-backed application you'll ever test.

Take the pack with you. Adapt it to your real schema. The first time it catches a bug nobody else saw, you'll know exactly why testers learn SQL.