Module 7 · SQL for QA

SELECT, filtering and NULL

Query a table, filter rows and handle missing values correctly.

About 10 min reading and 15 min practice (estimates)

Loading progress…

Read

Learning objectives

Write SELECT queries with WHERE, ORDER BY and LIMIT.
Explain why = NULL never matches, and use IS NULL.
Count rows correctly.

Explanation

Testers use SQL to check what the application really saved — not just what the screen shows. A query names the columns, the table, an optional filter, an order and a limit.

-- Fictional Dar Al Souq data, for practice only
CREATE TABLE customers (
  id      integer PRIMARY KEY,
  name    text NOT NULL,
  wilayat text            -- may be NULL
);
CREATE TABLE orders (
  id           integer PRIMARY KEY,
  customer_id  integer NOT NULL REFERENCES customers(id),
  status       text NOT NULL,          -- 'paid', 'cancelled', ...
  total        numeric(10,3) NOT NULL, -- OMR
  delivered_at timestamptz             -- NULL until delivered
);
CREATE TABLE order_items (
  order_id   integer NOT NULL REFERENCES orders(id),
  product_id text NOT NULL,
  qty        integer NOT NULL,
  unit_price numeric(10,3) NOT NULL
);

INSERT INTO customers VALUES
  (1, 'Test Customer A', 'Muscat'),
  (2, 'Test Customer B', NULL),
  (3, 'Test Customer C', 'Sohar');
INSERT INTO orders VALUES
  (101, 1, 'paid',      16.500, '2026-10-01 10:00+04'),
  (102, 1, 'paid',      23.000, NULL),
  (103, 2, 'cancelled',  6.500, NULL),
  (104, 2, 'paid',      12.000, '2026-10-03 15:30+04');
INSERT INTO order_items VALUES
  (101, 'coffee-cups',     2,  8.250),
  (102, 'serving-bowl',    1, 12.000),
  (102, 'woven-tote',      1,  9.500),
  (103, 'incense-burner',  1,  6.500),
  (104, 'serving-bowl',    1, 12.000);

Run this in a local PostgreSQL database you own to try the queries.

SELECT id, status, total
FROM orders
WHERE status = 'paid' AND total >= 15
ORDER BY total DESC
LIMIT 10;
-- 102 | paid | 23.000
-- 101 | paid | 16.500

NULL means "unknown" or "missing". Any comparison with NULL — even NULL = NULL — is not true, so WHERE delivered_at = NULL returns no rows. Use IS NULL or IS NOT NULL. COUNT(*) counts rows; COUNT(column) counts only rows where that column is not NULL.

SELECT count(*)            AS orders,
       count(delivered_at) AS delivered
FROM orders;
-- orders = 4, delivered = 2

Example: verifying a cancellation

After cancelling order 103 in the app, confirm it was stored correctly and never marked delivered:

SELECT id, status, delivered_at
FROM orders
WHERE id = 103;
-- 103 | cancelled | NULL

Common mistakes

Writing = NULL instead of IS NULL.
Running a query without WHERE or LIMIT on a large table.
Trusting the user interface instead of checking stored values.

Try

Practise what you just read. Answers are checked here; nothing is graded publicly or counts toward Bug Arena.

Exercise

Which query lists paid orders that have not been delivered yet?

Choose one answer.

SELECT id FROM orders WHERE status = 'paid' AND delivered_at = NULL;
SELECT id FROM orders WHERE status = 'paid' AND delivered_at IS NULL;
SELECT id FROM orders WHERE status = 'paid' OR delivered_at IS NULL;
SELECT id FROM orders WHERE delivered_at IS NOT NULL;

Review

Check yourself — you should now be able to:

Write SELECT queries with WHERE, ORDER BY and LIMIT.
Explain why = NULL never matches, and use IS NULL.
Count rows correctly.

References

Continue

When you’re ready, mark the lesson complete in the Try step and move on.

Marked as read when you reach the end of the lesson.