12. SQL fixtures, nulls and independent row expectations

qatestingtest-designindependent-practice

Saved on this device

Full lesson · 4 learning steps Open any exercise for focused practice

Step 1 of 4 · intro card

12. SQL fixtures, nulls and independent row expectations

Concept and contract

Use a small disposable database to verify stored behavior without touching production.

A SQL fixture defines a known schema and a small dataset for one investigation. Use a disposable local database, not the production catalogue. The expected rows should be written independently so a wrong query cannot redefine the oracle that is supposed to challenge it.

Null represents missing or unknown information under SQL’s three-valued logic. Comparing a value with equals NULL does not produce the ordinary true condition needed by WHERE. Use IS NULL or IS NOT NULL when testing nullness explicitly.

Mechanism and worked example

The worked script creates three roles and selects those whose salary is unknown.

The worked script creates three roles and selects those whose salary is unknown. Its expected id is r2. The fixture includes a zero salary so that missing data cannot be confused with a falsy or low numeric value.

Use explicit ORDER BY whenever row order is part of the expectation. A stable result observed once without ordering is not a contract. Keep schema setup and assertions in the same disposable fixture or a clearly named companion file.

Complete worked example

sql

CREATE TABLE roles(id TEXT PRIMARY KEY, salary INTEGER);
INSERT INTO roles VALUES ('r1', 100), ('r2', NULL), ('r3', 0);
SELECT id FROM roles WHERE salary IS NULL ORDER BY id;

Reasoning: The fixture distinguishes an unknown salary from a known zero salary.

Counterexamples and common mistakes

The weak query uses salary = NULL and therefore does not identify the missing-salary row.

The weak query uses salary = NULL and therefore does not identify the missing-salary row. A test checking only that the query executed would miss the logical error. Inspect exact returned ids rather than treating successful SQL execution as correct business behavior.

Do not clean fixtures by deleting rows from a live database. Temporary in-memory databases or an isolated test file provide a safer boundary. Foreign-key enforcement and transaction semantics also vary by engine, so state which engine the lesson actually runs.

Counterexample: what this check misses

sql

SELECT id FROM roles WHERE salary = NULL;

Reasoning: Equals NULL does not establish a true nullness predicate in WHERE.

Independent practice: small task

Write a complete script selecting roles with a known salary, including zero.

Write a complete script selecting roles with a known salary, including zero. Keep the same three-row fixture and order by id. Begin by predicting the expected ids manually, then write the null predicate.

Run it in a new SQLite database and compare exact rows. Optional extension: add a NOT NULL constraint to another field and test a rejected insert. This lesson does not claim SQLite fixtures prove PostgreSQL-specific locking or query-planner behavior.

Step 1: Write expected ids before the query. Step 2: Select known salaries and order them explicitly.

Practice starter: solve before reading the solution

sql

CREATE TABLE roles(id TEXT PRIMARY KEY, salary INTEGER);
INSERT INTO roles VALUES ('r1', 100), ('r2', NULL), ('r3', 0);
-- TODO: select known salaries, preserving zero, ordered by id.

Reasoning: Predict r1 and r3 before writing the query.

Hint 1: Zero is not NULL.
Hint 2: Use an in-memory fixture, never a live database.

Solution, tests and trade-offs

The solution uses IS NOT NULL and returns r1 and r3.

The solution uses IS NOT NULL and returns r1 and r3. It retains zero as known data. The verifier executes both scripts in fresh in-memory SQLite databases and checks exact rows; it also demonstrates that the equals-NULL variant misses its target.

When adapting the test to another engine, verify its types, constraints and configuration explicitly. Keep the query’s expected business result separate from incidental formatting in a command-line client.

Practice solution and acceptance checks

sql

CREATE TABLE roles(id TEXT PRIMARY KEY, salary INTEGER);
INSERT INTO roles VALUES ('r1', 100), ('r2', NULL), ('r3', 0);
SELECT id FROM roles WHERE salary IS NOT NULL ORDER BY id;

Reasoning: IS NOT NULL preserves zero and gives a deterministic expected row order.

Interview reasoning and next step

In interviews, explain NULL versus zero with the fixture and show why equals NULL is the wrong predicate.

In interviews, explain NULL versus zero with the fixture and show why equals NULL is the wrong predicate. Describe the isolation boundary before running SQL. Mention ordering when comparing row arrays.

Next, inspect joins and aggregate cardinality. A syntactically valid join can multiply rows and make counts misleading even when every individual field looks reasonable.

Optional stretch — after the core task works: Add a constraint failure in a separate disposable case.

Computer practice: download the companion lab at https://applydjinn.com/typorithm/qa-lab-20261105.zip. Read its README, install the pinned tools and run the accepted examples before attempting a starter. The mobile reader preserves your reading and self-check progress; the local lab runs browser tests separately.

Use a small disposable database to verify stored behavior without touching production.