SQL for verification

You are not learning SQL to build things

A developer learns SQL to write features. You are learning it for two much narrower jobs, and they need perhaps a tenth of the language:

1. Verification — the screen says the order is Paid. Is it stored as Paid?
2. Test data — you need an account with an expired discount code and three past orders, and clicking your way there takes twenty minutes.

That is it. SELECT, a WHERE, a couple of JOINs and GROUP BY will cover almost every question you have. You can skip the rest of the language for now without guilt.

Why the screen is not enough

A confirmation message means the application believes it succeeded. Between that belief and the database sit caches, queues, retries and transactions, and all of them are places where a UI can tell the truth about what it did while the data says something else.

The bugs that hide in that gap are the expensive ones, because they pass every UI test:

• the order shows as Paid, and orders.status is still PENDING — a webhook updated the screen and the write failed
• the address updated on screen, and one of two tables did not get it — a partial write, and now two screens disagree about the same customer
• a deleted item disappears from the list but the row is still there with deleted_at set, and it comes back in the monthly export
• the total on screen is right because the front end recalculated it, and the stored total is wrong

Every one of those is invisible to a tester who only reads screens.

The five clauses, in the order the database runs them

Worth knowing because it explains the error you will hit most often:

FROM      which table
WHERE     which rows            <- runs BEFORE grouping
GROUP BY  fold rows together
HAVING    which groups          <- filters AFTER grouping
SELECT    which columns
ORDER BY  sort

WHERE filters rows; HAVING filters groups. You cannot put COUNT(*) > 1 in a WHERE — at that point the counting has not happened yet. That single fact is behind most of the errors a beginner gets.

Enough syntax to be useful

Assume ShopMini's schema: orders, order_items, customers, discount_codes.

-- The order I just placed
SELECT id, status, total, created_at
FROM orders
WHERE customer_email = 'buyer@shopmini.test'
ORDER BY created_at DESC
LIMIT 5;

ORDER BY created_at DESC LIMIT 5 is the shape you will type most often — "show me what just happened".

-- Does the stored total match the sum of its line items?
SELECT o.id,
       o.total                              AS stored_total,
       SUM(oi.unit_price * oi.quantity)     AS calculated_total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.id = 'ord_8831'
GROUP BY o.id, o.total;

That query is the internal consistency oracle from the oracles lesson, written down. Nobody has to approve it as a requirement: if those two columns disagree, the software is wrong, and you can say so without asking anyone.

JOIN, in the only detail you need

A JOIN follows a relationship: this order's items, this item's product, this customer's orders. INNER JOIN (the default) keeps only rows that matched on both sides.

That default is a trap for testers, and here is the shape of it:

-- WRONG: silently hides orders that have no items
SELECT o.id, COUNT(oi.id) AS items
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id;

-- RIGHT: keeps every order; an empty one shows 0
SELECT o.id, COUNT(oi.id) AS items
FROM orders o
LEFT JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id;

An order with no line items is exactly the bug you are hunting, and an INNER JOIN deletes it from your results. When you are looking for missing or orphaned data, reach for LEFT JOIN — and then WHERE oi.id IS NULL to see only the broken ones:

-- Orders with no line items at all
SELECT o.id, o.status, o.created_at
FROM orders o
LEFT JOIN order_items oi ON oi.order_id = o.id
WHERE oi.id IS NULL;

A query that finds nothing is not proof. It means "no rows matched this query" — which is also what a typo in a column value produces. Before trusting an empty result, run the same query without the WHERE and check it returns anything at all.

GROUP BY, for the questions worth asking

Counting is where a tester finds problems nobody reported:

-- Duplicate orders: the same customer, same total, within a minute
SELECT customer_email, total, COUNT(*) AS n
FROM orders
WHERE created_at > NOW() - INTERVAL '1 day'
GROUP BY customer_email, total, DATE_TRUNC('minute', created_at)
HAVING COUNT(*) > 1;

Double-submit, found in one query rather than by clicking fast. And the shape generalises — "group by the thing that should be unique, keep the groups with more than one" is how you find duplicates of anything.

-- Distribution check: are all the states reachable?
SELECT status, COUNT(*) FROM orders GROUP BY status ORDER BY 2 DESC;

Run that after a release. A state with zero rows that used to have thousands is a bug that no test case was written for.

Rules for touching a real database

1. Read-only, always. Ask for a read-only account and use it. The one time you run an UPDATE without a WHERE is the day you learn why.
2. Never on production without permission, and never with anything that locks. A heavy query on a live database is an outage you caused.
3. Wrap anything you must write in a transaction, and check before you commit:

BEGIN;
UPDATE orders SET status = 'PAID' WHERE id = 'ord_8831';
-- look at it first
SELECT id, status FROM orders WHERE id = 'ord_8831';
COMMIT;   -- or ROLLBACK if it is not what you meant

4. Watch what you copy into a ticket. Query results are real customer data. Emails, addresses and payment details do not belong in a bug report — quote the ids and the offending column, not the row.

The habit worth building

Whenever you verify something important through the UI, ask the database the same question. It takes a minute, and it is the check that catches the class of defect where the application and its data disagree — the class no amount of clicking will ever reach.

Where TestForge fits

Put the query in the case. A step that reads "verify the order is paid" is a different test depending on who runs it; a step that reads SELECT status FROM orders WHERE id = :orderId with an expected result of PAID is the same test every time, and it is the difference between a case that checks the screen and one that checks the system.

Anything you find this way becomes a defect with the query attached — the developer can re-run it, which means they cannot fail to reproduce it.

Next: taking all of this to more than one device — building a browser and device matrix from analytics instead of from superstition.