Before this lesson

Be able to create SQLite tables and run SELECT. Understand column types and WHERE.

What you will be able to do

Combine predicates and handle missing values and sorting ties.

Understand the mechanism

WHERE filters rows before they are returned. AND has higher precedence than OR. Use parentheses to express mixed business conditions clearly.

NULL means unknown or missing, not an empty string or zero. Comparing with = NULL does not yield true. Use IS NULL or IS NOT NULL; WHERE retains only true conditions.

LIMIT selects a slice and needs deterministic ordering. Add a unique id after minutes to break ties. OFFSET pagination can still drift between pages when data changes.

Runnable example

This course uses SQLite 3. Open a fresh in-memory database, for example with sqlite3 :memory:, and run the complete script. Each lesson supplies its own schema and sample data, independent of the website database.

CREATE TABLE lessons(id INTEGER PRIMARY KEY, title TEXT, minutes INTEGER, level TEXT);
INSERT INTO lessons VALUES
 (1,'Variables',20,'beginner'),(2,'Loops',20,'beginner'),
 (3,'Objects',40,'intermediate'),(4,'Review',NULL,'beginner');
SELECT id, title, minutes FROM lessons
WHERE level = 'beginner' AND minutes IS NOT NULL AND minutes <= 30
ORDER BY minutes, id LIMIT 2;

Trace and verify

Only rows 1 and 2 satisfy every condition. Row 4 has a missing duration and is not treated as zero.

Common mistakes and boundaries

COALESCE can provide a display fallback, but replacing unknown durations with zero changes statistical meaning. Define a missing-data policy first.

Practice challenge

Find unknown-duration lessons, then fetch page two with two rows per page ordered by id.

Reference solution and reasoning

WHERE minutes IS NULL returns Review. ORDER BY id LIMIT 2 OFFSET 2 returns Objects and Review; OFFSET is the number of skipped rows.

Lab: NULL and stable page ordering

A status not equal to done does not automatically include unknown statuses. Comparisons with NULL normally produce UNKNOWN, not TRUE. Run this in a fresh SQLite in-memory database; tied sort values expose a second issue in pagination.

CREATE TABLE lessons(id INTEGER PRIMARY KEY, score INTEGER, status TEXT);
INSERT INTO lessons VALUES (1, 90, 'todo'), (2, 90, NULL), (3, 80, 'done');
SELECT id FROM lessons WHERE status <> 'done' ORDER BY id;
SELECT id FROM lessons WHERE status <> 'done' OR status IS NULL ORDER BY id;
SELECT id, score FROM lessons ORDER BY score DESC, id ASC LIMIT 2;
SELECT id, score FROM lessons
WHERE score < 90 OR (score = 90 AND id > 2)
ORDER BY score DESC, id ASC LIMIT 2;

Expected observations

The first query returns only 1; the second returns 1 and 2. The first page is (1,90), (2,90); the page after the cursor is (3,80).

Why this happens and where it stops applying

WHERE retains only TRUE. Decide how unknown status should be presented before adding an IS NULL branch. A unique id breaks score ties deterministically. Cursor comparisons follow the sort directions: lower score for descending score, higher id for ascending id. This fixture has non-null scores; a real cursor API must either forbid null scores or explicitly define their ordering and cursor representation.

Extension challenge

Does keyset pagination guarantee exactly-once traversal if scores change between requests?

Reasoned solution

No. A record can move across the cursor boundary. Keyset pagination reduces offset-shift problems, but snapshot consistency, immutable sort keys or accepted eventual consistency remain separate design choices.

Further reading