SQL for Testers

The basics: databases, tables and keys

Think of a database as a set of very strict spreadsheets. Each sheet is a table with a fixed list of columns, and every column has a type: whole numbers (INTEGER), decimals (REAL, DECIMAL), text (TEXT, VARCHAR), dates and so on.

The practice database in this lesson is a simplified FoodDash, the same food delivery app you test in the Capstone. It has five tables:

• The primary key (🔑) uniquely identifies a row. There is exactly one order with id = 1007.
• A foreign key (→) stores another table’s primary key. orders.user_id = 2 means “this order belongs to user 2”. One user can have many orders, and one order can have many items.
• A column may allow NULL, meaning “no value”. In FoodDash, users.city and orders.coupon_id can be NULL.

SELECT: reading data

Every read starts with SELECT (which columns) and FROM (which table):

SELECT * FROM restaurants;                 -- every column
SELECT name, city FROM restaurants;        -- only these two
SELECT name AS restaurant, rating FROM restaurants;  -- rename a column in the result
SELECT DISTINCT city FROM restaurants;     -- each city once

• * is handy for a first look, but on real tables with 40 columns, pick the ones you need.
• AS gives a column a friendlier name in the result. It doesn’t change the table.
• DISTINCT removes duplicate result rows. SELECT DISTINCT city FROM restaurants lists Belgrade, Novi Sad and Berlin once each.
• End each query with a semicolon ;. Keywords aren’t case-sensitive (select works), but writing them in capitals makes queries easier to read.

WHERE: picking the rows you need

WHERE keeps only the rows where a condition is true.

Combine conditions with AND, OR and NOT. When you mix AND and OR, use brackets, because AND is evaluated first:

-- Delivered or on the way, AND over 20 euros
SELECT id, status, total
FROM orders
WHERE (status = 'delivered' OR status = 'on_the_way')
  AND total > 20;

Without the brackets you would get every on-the-way order over 20 plus every delivered order of any size. That’s a classic wrong-result bug in testers’ own queries.

LIKE patterns

% matches any number of characters (even none) and _ matches exactly one.

Dates

In most databases, you write dates as text in the ISO format 'YYYY-MM-DD', and comparisons work as expected:

SELECT id, created_at
FROM orders
WHERE created_at >= '2025-09-10';

Watch out with BETWEEN on date-time columns: created_at BETWEEN '2025-09-01' AND '2025-09-07' stops at midnight at the start of September 7, so orders placed later that day are missed. Use created_at < '2025-09-08' for the upper limit instead.

ORDER BY and LIMIT

Without ORDER BY, the database may return rows in any order, and that order can change between runs. If the order matters, say so:

SELECT id, total
FROM orders
ORDER BY total DESC, id ASC   -- highest total first; ties sorted by id
LIMIT 5;                      -- the top five

ASC (smallest first) is the default. LIMIT works in PostgreSQL, MySQL and SQLite. SQL Server uses SELECT TOP 5 ..., and both SQL Server and Oracle support OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY (Oracle also allows the shorter FETCH FIRST 5 ROWS ONLY).

Counting and totals: aggregates, GROUP BY and HAVING

Aggregate functions turn many rows into one value:

GROUP BY

GROUP BY splits the rows into groups and runs the aggregate once per group:

SELECT status, COUNT(*) AS orders, SUM(total) AS revenue
FROM orders
GROUP BY status;

Rule of thumb: every column in SELECT must either be inside an aggregate or listed in GROUP BY. (SQLite and older MySQL setups let you break this rule and quietly return a random value from the group. Don’t rely on it.)

This is exactly how you test a dashboard: if the admin panel says “11 delivered orders” and your query says 10, someone is counting wrong.

WHERE vs HAVING

WHERE filters rows before they are grouped. HAVING filters groups after the aggregates are calculated.

SELECT user_id, COUNT(*) AS orders
FROM orders
WHERE status <> 'canceled'   -- rows
GROUP BY user_id
HAVING COUNT(*) >= 2;         -- groups

SELECT user_id, COUNT(*)
FROM orders
WHERE COUNT(*) >= 2
GROUP BY user_id;

JOINs: combining tables

Real questions span several tables: “Which customer placed this order at which restaurant?” A JOIN matches rows using the keys:

SELECT o.id, u.name AS customer, r.name AS restaurant, o.total
FROM orders o
JOIN users u       ON u.id = o.user_id
JOIN restaurants r ON r.id = o.restaurant_id
WHERE o.id = 1007;

• orders o gives the table a short alias, so you can write o.id instead of orders.id.
• The ON condition says how rows belong together: foreign key = primary key.
• When two tables have a column with the same name (name, id), you must prefix it, or you’ll get an “ambiguous column name” error.

INNER JOIN vs LEFT JOIN

JOIN on its own means INNER JOIN: only rows that have a partner on both sides. A LEFT JOIN keeps every row from the left (first) table, and when there’s no partner, the right side’s columns are NULL. Try the three versions:

SELECT o.id, o.total, oi.dish
FROM orders o
JOIN order_items oi ON oi.order_id = o.id;

That difference is the heart of many tester queries:

• Find what’s missing: LEFT JOIN + WHERE right_side.id IS NULL finds orders without items, users who never ordered, restaurants with no orders.
• Beware of hidden rows: an INNER JOIN silently drops the broken order. If your “orders per restaurant” report uses an inner join, a restaurant with zero orders won’t appear at all instead of showing 0.

-- Users who have never placed an order
SELECT u.id, u.name
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

NULL: the trap behind many wrong results

NULL means “no value” or “unknown”. It isn’t 0, it isn’t an empty string '', and it doesn’t behave like a normal value. These are the traps that catch testers (and developers) all the time:

Typical tester queries

These recipes cover most of what testers do with SQL day to day. In the practice database below, each one finds a real bug.

Verify that the UI saved the right data

You place an order in the app, then check exactly what was stored:

Find duplicates

SELECT LOWER(email) AS email, COUNT(*) AS accounts
FROM users
GROUP BY LOWER(email)
HAVING COUNT(*) > 1;

GROUP BY ... HAVING COUNT(*) > 1 is the universal duplicate finder. Group by whatever should be unique: an email, a coupon code, an order number, a payment id. Use LOWER() or TRIM() when duplicates may hide behind capitals or spaces.

Find orphan rows

SELECT oi.*
FROM order_items oi
LEFT JOIN orders o ON o.id = oi.order_id
WHERE o.id IS NULL;

Orphans are rows whose foreign key points to nothing, often left behind when something was deleted. Check them after delete features, migrations and imports.

Find totals that don’t add up

SELECT o.id, o.total, ROUND(SUM(oi.quantity * oi.unit_price), 2) AS items_total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id, o.total
HAVING ROUND(SUM(oi.quantity * oi.unit_price), 2) <> o.total;

Find rule violations

Business rules make great queries. “WELCOME10 may be used once per user”:

SELECT o.user_id, c.code, COUNT(*) AS times_used
FROM orders o
JOIN coupons c ON c.id = o.coupon_id
WHERE c.once_per_user = 1
GROUP BY o.user_id, c.code
HAVING COUNT(*) > 1;

Other rules worth a query: no delivered order without a delivery time, no coupon used after valid_until, no negative quantities, no order total of 0.

Database checks in your test cases

A database check fits into a normal test case as part of the expected result:

Preparing test data: INSERT, UPDATE and DELETE

Sometimes the fastest way to reach a precondition (“a user with an expired coupon”) is to create it directly. That’s fine only on a database that is yours: a local copy, a personal test environment, or a shared test database where your team explicitly allows it.

-- Create a test coupon that has already expired
INSERT INTO coupons (id, code, percent, once_per_user, valid_until)
VALUES (90, 'QA_EXPIRED', 10, 1, '2025-01-31');

-- Move one test order to another status
UPDATE orders SET status = 'canceled' WHERE id = 1013;

-- Remove the test coupon again
DELETE FROM coupons WHERE id = 90;

You can try all three statements in the playground below. It’s your own copy, and Reset database undoes everything.

Tools you’ll meet at work

• DBeaver (free, works with almost every database), pgAdmin (PostgreSQL), MySQL Workbench (MySQL), SQL Server Management Studio (SQL Server) and DataGrip (paid, by JetBrains).
• Ask for a read-only database user for each environmentEnvironmentA setup of servers, data and configuration where the software runs, such as dev, staging or production.Open in glossary →. DBeaver also has a “Read-only connection” option in the connection settings: switch it on for anything shared.
• Many teams also have an admin panel that shows the same data. It’s convenient, but it may hide fields or apply its own logic. SQL shows you the raw truth.
• Save useful queries in a shared file or wiki page. A team “query cookbook” pays off quickly.

Common mistakes