QA StepsJoin the waitlist

SQL for testers: check what the app saved

Testers use SQL mostly to read, not to change: after an action in the app, you query the database to confirm that the right rows were saved with the right values. The core case is an order. After checkout, check the order row (status and money), its item rows (one per cart line, adding up to the subtotal) and its payment rows (one per attempt, with exactly one successful charge equal to the total). A small part of SQL covers this work: SELECT with WHERE to find rows, COUNT and GROUP BY to count them, and JOIN to read several tables at once, with LEFT JOIN to find a row that should exist and does not.

6 checks
for every paid order
7 tables
of a practice shop, used in every query
Read only
SELECT, never UPDATE, for checks

How to check a saved order, step by step

  1. Step 1

    Write down what the screen shows

    Before any query, note the order number, the status and every money line on the order page: Subtotal, the coupon, Shipping and Total, plus each payment attempt.

  2. Step 2

    Work out what the rows should hold

    From the shop's rules, not from the screen: which tables get a row, how many, and with which values. A check without an expected value cannot fail.

  3. Step 3

    Find the order by its key

    Turn the number on screen into the id the database uses, and read the order row by that id. Read only the columns you need.

  4. Step 4

    Check the items against the subtotal

    List the item rows of the order, then add up price times quantity and compare the sum with the order's saved subtotal.

  5. Step 5

    Check the payments against the total

    List every payment attempt. Expect each declined one to have a reason and exactly one successful one, charged for the order's total.

  6. Step 6

    Compare the screen, the API and the rows

    The same order is shown three ways. Any disagreement between them is a finding, even when each one looks fine on its own.

  7. Step 7

    Report with the query as evidence

    Put the query, its result and the expected values into the bug report, so a developer can rerun it in one step.

    Open

Why testers need SQL

A green message on the screen says the page thinks it worked. It does not say what the app wrote down. An order can read Paid on the page while the payments table tells a different story. The rows are what finance, support and every other system will read later, so a tester checks them.

This is called data reconciliation: comparing two views of the same data and checking that they agree. For most testers SQL means reading. Changing data with INSERT, UPDATE or DELETE happens when preparing test data, on a test database, and only after a SELECT with the same WHERE has shown exactly which rows would change.

The example database

Shelf is the practice shop the courses use. Its database has seven tables. A primary key identifies one row; a foreign key points from one table to another, such as order_items.order_id to orders.id. The examples use SQLite, and the queries below run the same way in PostgreSQL and MySQL.

Three habits of this schema trip beginners up. Money is stored in whole cents: USD 49.00 is 4900, so "over fifty dollars" is price_cents > 5000. Statuses are lowercase: paid, awaiting_payment and the one for a canceled order, while the screen shows "Paid" and "Awaiting payment". And the order number on screen is not the id: SH-100049 is 100000 + 49, so the row is orders.id 49.

Shelf tables and the columns used here
products     id, sku, name, category, price_cents, stock, free_shipping
orders       id, user_id, status, subtotal_cents, discount_cents,
             coupon_code, shipping_cents, total_cents, paid_at,
             delivery_by, card_last4, created_at
order_items  id, order_id, product_id, name, unit_price_cents, quantity
payments     id, order_id, user_id, result, decline_code,
             card_last4, amount_cents, created_at
users        id, username, created_at, last_seen_at
coupons      code, amount_cents, expires, max_items, label
categories   slug, name, position

Know what the app should save

You can only check rows against an expectation, and the expectation comes from the rules. In Shelf, placing an order writes one orders row with status awaiting_payment and the money columns, and one order_items row per cart line, keeping the product's name and price at that moment. Every payment attempt writes one payments row: declined with a decline_code, or succeeded. A successful payment sets the order's status to paid and fills paid_at, delivery_by and card_last4.

Know what the app does not write as well. Shelf's checkout does not lower products.stock, so a check that expects the stock to drop would report a defect that is not there. Read the rules first, then write the checks.

Here is a worked order. In the shop you add one Hearthline Classic Kettle 1.7 l (USD 49.00, does not ship free), apply the coupon SAVE25 (USD 25.00 off), place the order, try the test card 4000000000000002, which is declined, and then pay with 4242424242424242. The page reads Order SH-100049. By the rules, shipping is USD 4.99 because the subtotal is under USD 50.00, and the total is 49.00 - 25.00 + 4.99 = USD 28.99.

Expected rows for order SH-100049
orders (1 row, id 49)
  status paid, subtotal_cents 4900, discount_cents 2500,
  coupon_code SAVE25, shipping_cents 499, total_cents 2899,
  card_last4 4242, paid_at and delivery_by filled in

order_items (1 row)
  name "Hearthline Classic Kettle 1.7 l", unit_price_cents 4900, quantity 1

payments (2 rows)
  declined, decline_code card_declined, card_last4 0002, amount_cents 2899
  succeeded, card_last4 4242, amount_cents 2899

The queries that check an order

Each query below answers one question about order 49. Run them in order and compare every value with the expected rows above. Name the columns you need instead of SELECT *: the result is shorter, and a missing column fails loudly.

The last query uses a LEFT JOIN. An ordinary JOIN keeps only rows that match on both sides, so an order without a successful payment would simply vanish from the result. A LEFT JOIN keeps the order and fills the payment columns with NULL, which is exactly the evidence you need. Notice that the condition on result sits in ON, not in WHERE: in WHERE it would remove the NULL row again.

Checks for one order
-- 1. The order row, and the total recalculated
SELECT status, subtotal_cents, discount_cents, coupon_code,
       shipping_cents, total_cents, paid_at, card_last4,
       subtotal_cents - discount_cents + shipping_cents AS expected_total
FROM orders
WHERE id = 49;

-- 2. The items add up to the saved subtotal
SELECT o.subtotal_cents,
       SUM(i.unit_price_cents * i.quantity) AS items_cents
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.id = 49
GROUP BY o.id, o.subtotal_cents;

-- 3. Every payment attempt, oldest first
SELECT result, decline_code, card_last4, amount_cents, created_at
FROM payments
WHERE order_id = 49
ORDER BY created_at;

-- 4. Attempts per result: exactly one succeeded
SELECT result, COUNT(*) AS attempts
FROM payments
WHERE order_id = 49
GROUP BY result;

-- 5. The successful charge equals the total, same card
SELECT o.total_cents, o.card_last4,
       p.amount_cents, p.card_last4 AS paid_with
FROM orders o
LEFT JOIN payments p
  ON p.order_id = o.id AND p.result = 'succeeded'
WHERE o.id = 49;

Six checks for a paid order

The queries above add up to one check set. Use it for every paid order you test, and keep the queries in a file: the next order needs only a new id.

  • Status and money

    The order is paid, and total_cents equals subtotal minus discount plus shipping.

  • Items match the subtotal

    The sum of unit_price_cents times quantity equals subtotal_cents.

  • Exactly one successful payment

    Not zero (paid for nothing) and not two (charged twice).

  • The charge equals the total

    amount_cents of the successful payment equals total_cents, after the discount and with shipping.

  • Every decline has a reason

    Each declined row has a decline_code; the order page shows it with spaces, such as "card declined".

  • The same card everywhere

    orders.card_last4 equals card_last4 of the successful payment.

The SQL you need, and the traps in it

SELECT names the columns, FROM names the table, WHERE picks the rows. ORDER BY sorts, and LIMIT keeps the first few, which means nothing without ORDER BY, because rows have no guaranteed order. COUNT, SUM, AVG, MIN and MAX turn many rows into one value, GROUP BY gives one result row per group, and HAVING filters the groups after they are counted, which WHERE cannot do.

NULL means no value. It is not 0 and not an empty string, and it is matched with IS NULL, never with = NULL. In Shelf, paid_at and card_last4 are NULL until an order is paid. COUNT(*) counts rows, while COUNT(card_last4) skips the NULLs, so the two answer different questions.

Dialects differ in small ways. SQLite, PostgreSQL and MySQL write LIMIT; SQL Server writes TOP. In SQLite, LIKE ignores the case of English letters; in PostgreSQL it does not, and ILIKE is the case-insensitive form.

  • WHERE price_cents > 50

    Fifty cents. Fifty dollars is 5000.

  • WHERE status = 'Paid'

    The data says paid, in lowercase. The capital letter is the screen's.

  • WHERE paid_at = NULL

    Matches nothing, ever. Write paid_at IS NULL.

  • "The charge should equal the subtotal"

    It equals the total: after the discount, with shipping.

  • "One payment row per order"

    One row per attempt. Declined attempts are rows too.

  • "No rows means the query failed"

    For a query that looks for broken rows, an empty result is the pass.

Reading safely

Work on a test database unless you have explicit permission and a reason to read production. Many database clients commit each statement the moment it runs, which is called auto-commit, so an UPDATE without a WHERE changes every row at once and there is nothing to undo. Before any change, run a SELECT with the same WHERE and read what it returns.

A free client such as DBeaver Community opens most databases, including SQLite files. Keep your check queries in a saved script, such as shelf-checks.sql: it becomes a small portfolio that shows how you test data.

Where to learn this

Questions and answers

How much SQL does a manual tester need?

Enough to read: SELECT with WHERE, ORDER BY and LIMIT, COUNT and GROUP BY with HAVING, and INNER and LEFT JOIN. With those you can check what the app saved. Writing changes is a smaller, later part.

Do testers write INSERT, UPDATE and DELETE?

Sometimes, to prepare test data on a test database. Run a SELECT with the same WHERE first, so you see exactly which rows will change, and never change production data to make a test pass.

What is the difference between WHERE and HAVING?

WHERE filters rows before they are grouped and cannot use COUNT or SUM. HAVING filters the groups after grouping and can, as in HAVING COUNT(*) > 1.

When should I use a LEFT JOIN instead of a JOIN?

When you are looking for something missing. A JOIN drops rows without a match, so an order without a payment disappears. A LEFT JOIN keeps it, with NULL in the payment columns.

Which database should I learn SQL on?

Any of them: the basics are the same. SQLite needs no server and runs from one file, which makes it easy to start. The differences, such as LIMIT and TOP, are small and quick to learn later.

More guides

See the first course

The full outline of every course is available: you can see each lesson before you start.

All courses