A visual reference · SQLite

SQLite,
one change at a time

From a first SELECT to relational queries, transactions, indexes, JSON and full-text search. Complete source. A small change. Its actual result.

The Org file is the canonical source. Read the HTML guide beside your work, or download its PDF and source for offline reading.

Last changes: 2026-10-31. Introduced runnable SQLite examples of queries, joins, window functions, transactions, indexes, JSON and full-text search, with verified results and downloadable databases.

Download PDFRendered tour · read offline

96worked examples

16topic families

1canonical Org source

Browse by query and result

SQL and result gallery

96 independent examples. Choose a preview to read the complete query, its result and explanations. An ellipsis marks shortened code or output.

01 · A first SELECT

SQL-01-001

Select stored columns

SQL preview

SELECT id,site,value FROM sample ORDER BY id;

SQLite · exit 0

{"id": 1, "site": "A", "value": 12.0}
{"id": 2, "site": "B", "value": 20.0}
…

View complete example ↗SQL-01-002

Filter individual observations

SQL preview

SELECT id,site,value
FROM sample WHERE value>=16 ORDER BY id;

SQLite · exit 0

{"id": 2, "site": "B", "value": 20.0}
{"id": 3, "site": "A", "value": 18.0}
…

View complete example ↗SQL-01-003

Find the top three

SQL preview

SELECT id,value FROM sample
ORDER BY value DESC,id LIMIT 3;

SQLite · exit 0

{"id": 2, "value": 20.0}
{"id": 3, "value": 18.0}
…

View complete example ↗SQL-01-004

Return distinct values

SQL preview

SELECT DISTINCT site FROM sample ORDER BY site;

SQLite · exit 0

{"site": "A"}
{"site": "B"}
…

View complete example ↗SQL-01-005

Calculate a derived column

SQL preview

SELECT id,value*2 AS doubled
FROM sample ORDER BY id;

SQLite · exit 0

{"id": 1, "doubled": 24.0}
{"id": 2, "doubled": 40.0}
…

View complete example ↗SQL-01-006

Classify with CASE

SQL preview

SELECT id,CASE WHEN value>=16 THEN 'high'
               ELSE 'low' END AS class
FROM sample ORDER BY id;

SQLite · exit 0

{"id": 1, "class": "low"}
{"id": 2, "class": "high"}
…

View complete example ↗

02 · Filtering, NULL and ordering

SQL-02-001

Display a value for NULL

SQL preview

SELECT id,coalesce(note,'(missing)') AS note
FROM sample ORDER BY id;

SQLite · exit 0

{"id": 1, "note": "(missing)"}
{"id": 2, "note": "checked"}
…

View complete example ↗SQL-02-002

Test NULL explicitly

SQL preview

SELECT id,note FROM sample
WHERE note IS NULL ORDER BY id;

SQLite · exit 0

{"id": 1, "note": null}
{"id": 3, "note": null}
…

View complete example ↗SQL-02-003

Combine range and membership filters

SQL preview

SELECT id,site,value FROM sample
WHERE value BETWEEN 10 AND 18
  AND site IN ('A','C') ORDER BY id;

SQLite · exit 0

{"id": 1, "site": "A", "value": 12.0}
{"id": 3, "site": "A", "value": 18.0}
…

View complete example ↗SQL-02-004

Match and normalize text

SQL preview

SELECT id,lower(note) AS note FROM sample
WHERE note LIKE 'check%' ORDER BY id;

SQLite · exit 0

{"id": 2, "note": "checked"}
{"id": 6, "note": "checked"}

View complete example ↗SQL-02-005

Place NULL values last

SQL preview

SELECT id,note FROM sample
ORDER BY note NULLS LAST,id;

SQLite · exit 0

{"id": 7, "note": "archived"}
{"id": 2, "note": "checked"}
…

View complete example ↗SQL-02-006

Continue with keyset pagination

SQL preview

SELECT id,site FROM sample
WHERE id>4 ORDER BY id LIMIT 3;

SQLite · exit 0

{"id": 5, "site": "C"}
{"id": 6, "site": "A"}
…

View complete example ↗

03 · Expressions, text and dates

SQL-03-001

Distinguish integer and real arithmetic

SQL preview

SELECT 5/2 AS integer_division,
       5.0/2 AS real_division,
       CAST(5 AS REAL)/2 AS cast_division;

SQLite · exit 0

{"integer_division": 2, "real_division": 2.5, "cast_division": 2.…

View complete example ↗SQL-03-002

Format a consistent identifier

SQL preview

SELECT printf('S%02d',id) AS label,lower(site) AS site
FROM sample ORDER BY id;

SQLite · exit 0

{"label": "S01", "site": "a"}
{"label": "S02", "site": "b"}
…

View complete example ↗SQL-03-003

Clean and join text

SQL preview

SELECT trim('  River  ') AS cleaned,
       'site-'||lower('A') AS slug,
       replace('north_bank','_',' ') AS title;

SQLite · exit 0

{"cleaned": "River", "slug": "site-a", "title": "north bank"}

View complete example ↗SQL-03-004

Guard a denominator with NULLIF

SQL preview

SELECT 12.0/nullif(0,0) AS unknown_ratio,
       coalesce(12.0/nullif(0,0),0) AS display_ratio,
       round(12.0/5,2) AS rounded;

SQLite · exit 0

{"unknown_ratio": null, "display_ratio": 0, "rounded": 2.4}

View complete example ↗SQL-03-005

Measure elapsed days

SQL preview

SELECT id,CAST(julianday(day)-julianday('2026-01-01')
               AS INTEGER) AS days
FROM sample ORDER BY id;

SQLite · exit 0

{"id": 1, "days": 0}
{"id": 2, "days": 1}
…

View complete example ↗SQL-03-006

Calculate a calendar boundary

SQL preview

SELECT date('2026-02-12','start of month','+1 month','-1 day')
       AS month_end,
       strftime('%Y-%m','2026-02-12') AS month;

SQLite · exit 0

{"month_end": "2026-02-28", "month": "2026-02"}

View complete example ↗

04 · Aggregates and grouped questions

SQL-04-001

Summarize the entire table

SQL preview

SELECT count(*) AS n,round(avg(value),2) AS mean,
       min(value) AS minimum,max(value) AS maximum
FROM sample;

SQLite · exit 0

{"n": 8, "mean": 12.5, "minimum": 6.0, "maximum": 20.0}

View complete example ↗SQL-04-002

Group observations by site

SQL preview

SELECT site,count(*) AS n,sum(value) AS total
FROM sample GROUP BY site ORDER BY site;

SQLite · exit 0

{"site": "A", "n": 3, "total": 36.0}
{"site": "B", "n": 2, "total": 28.0}
…

View complete example ↗SQL-04-003

Filter groups with HAVING

SQL preview

SELECT site,sum(value) AS total FROM sample
GROUP BY site HAVING sum(value)>25 ORDER BY site;

SQLite · exit 0

{"site": "A", "total": 36.0}
{"site": "B", "total": 28.0}

View complete example ↗SQL-04-004

Count missing and present values

SQL preview

SELECT site,count(*) AS n,count(note) AS noted,
       count(*) FILTER (WHERE note IS NULL) AS missing
FROM sample GROUP BY site ORDER BY site;

SQLite · exit 0

{"site": "A", "n": 3, "noted": 1, "missing": 2}
{"site": "B", "n": 2, "noted": 2, "missing": 0}
…

View complete example ↗SQL-04-005

Calculate a weighted mean

SQL preview

WITH weights(id,w) AS (VALUES(1,1),(2,2),(3,1))
SELECT sum(s.value*w.w)/sum(w.w) AS weighted_mean
FROM sample AS s JOIN weights AS w ON w.id=s.id;

SQLite · exit 0

{"weighted_mean": 17.5}

View complete example ↗SQL-04-006

Make an ordered list per group

SQL preview

SELECT site,group_concat(id,',' ORDER BY id) AS ids
FROM sample GROUP BY site ORDER BY site;

SQLite · exit 0

{"site": "A", "ids": "1,3,6"}
{"site": "B", "ids": "2,4"}
…

View complete example ↗

05 · Joins and missing relationships

SQL-05-001

Match a lookup with an inner join

SQL preview

SELECT s.id,t.label FROM sample AS s
JOIN site AS t ON t.id=s.site ORDER BY s.id;

SQLite · exit 0

{"id": 1, "label": "River"}
{"id": 2, "label": "Hill"}
…

View complete example ↗SQL-05-002

Keep observations with missing metadata

SQL preview

SELECT s.id,t.label FROM sample AS s
LEFT JOIN site AS t ON t.id=s.site ORDER BY s.id;

SQLite · exit 0

{"id": 1, "label": "River"}
{"id": 2, "label": "Hill"}
…

View complete example ↗SQL-05-003

Find missing relationships

SQL preview

SELECT s.id,s.site FROM sample AS s
WHERE NOT EXISTS(SELECT 1 FROM site AS t WHERE t.id=s.site)
ORDER BY s.id;

SQLite · exit 0

{"id": 8, "site": "D"}

View complete example ↗SQL-05-004

Compare an ON filter with a WHERE filter

SQL preview

SELECT 'ON' AS placement,count(*) AS n
FROM sample AS s LEFT JOIN site AS t
 ON t.id=s.site AND t.label='River'
UNION ALL
…

SQLite · exit 0

{"placement": "ON", "n": 8}
{"placement": "WHERE", "n": 3}

View complete example ↗SQL-05-005

See join multiplicity

SQL preview

CREATE TABLE tag(sample_id INTEGER,tag TEXT);
INSERT INTO tag VALUES(1,'red'),(1,'checked'),(2,'blue');
SELECT s.id,t.tag FROM sample AS s
JOIN tag AS t ON t.sample_id=s.id ORDER BY s.id,t.tag;

SQLite · exit 0

{"id": 1, "tag": "checked"}
{"id": 1, "tag": "red"}
…

View complete example ↗SQL-05-006

Join a table to itself

SQL preview

SELECT a.id AS first_id,b.id AS next_id
FROM sample AS a JOIN sample AS b
 ON b.day=date(a.day,'+1 day') ORDER BY a.id,b.id;

SQLite · exit 0

{"first_id": 1, "next_id": 2}
{"first_id": 2, "next_id": 3}
…

View complete example ↗

06 · Subqueries, CTEs and sets

SQL-06-001

Compare with a correlated group mean

SQL preview

SELECT s.id,s.site,s.value FROM sample AS s
WHERE s.value>(SELECT avg(t.value) FROM sample AS t
               WHERE t.site=s.site) ORDER BY s.id;

SQLite · exit 0

{"id": 2, "site": "B", "value": 20.0}
{"id": 3, "site": "A", "value": 18.0}

View complete example ↗SQL-06-002

Name an intermediate result

SQL preview

WITH totals AS (
 SELECT site,sum(value) AS total FROM sample GROUP BY site
)
SELECT site,total FROM totals ORDER BY total DESC,site;

SQLite · exit 0

{"site": "A", "total": 36.0}
{"site": "B", "total": 28.0}
…

View complete example ↗SQL-06-003

Use EXISTS as a membership question

SQL preview

SELECT t.id,t.label FROM site AS t
WHERE EXISTS(SELECT 1 FROM sample AS s
             WHERE s.site=t.id AND s.value>15) ORDER BY t.id;

SQLite · exit 0

{"id": "A", "label": "River"}
{"id": "B", "label": "Hill"}

View complete example ↗SQL-06-004

Expose the NOT IN and NULL trap

SQL preview

SELECT 'NOT IN' AS method,count(*) AS n FROM sample
WHERE site NOT IN(SELECT id FROM site UNION ALL SELECT NULL)
UNION ALL
SELECT 'NOT EXISTS',count(*) FROM sample AS s
…

SQLite · exit 0

{"method": "NOT EXISTS", "n": 1}
{"method": "NOT IN", "n": 0}

View complete example ↗SQL-06-005

Compare UNION and UNION ALL

SQL preview

SELECT site FROM sample UNION SELECT id FROM site ORDER BY site;
SELECT site FROM sample UNION ALL SELECT id FROM site ORDER BY si…

SQLite · exit 0

{"site": "A"}
{"site": "B"}
…

View complete example ↗SQL-06-006

Intersect and subtract sets

SQL preview

SELECT site FROM sample INTERSECT SELECT id FROM site ORDER BY si…
SELECT site FROM sample EXCEPT SELECT id FROM site ORDER BY site;

SQLite · exit 0

{"site": "A"}
{"site": "B"}
…

View complete example ↗

07 · Window functions

SQL-07-001

Number rows within each site

SQL preview

SELECT id,site,row_number() OVER(
 PARTITION BY site ORDER BY value DESC,id) AS position
FROM sample ORDER BY site,position;

SQLite · exit 0

{"id": 3, "site": "A", "position": 1}
{"id": 1, "site": "A", "position": 2}
…

View complete example ↗SQL-07-002

Calculate a running total

SQL preview

SELECT id,sum(value) OVER(ORDER BY id
 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative
FROM sample ORDER BY id;

SQLite · exit 0

{"id": 1, "cumulative": 12.0}
{"id": 2, "cumulative": 32.0}
…

View complete example ↗SQL-07-003

Measure change from the preceding row

SQL preview

SELECT id,value-lag(value) OVER(ORDER BY id) AS change
FROM sample ORDER BY id;

SQLite · exit 0

{"id": 1, "change": null}
{"id": 2, "change": 8.0}
…

View complete example ↗SQL-07-004

Calculate a three-row moving mean

SQL preview

SELECT id,round(avg(value) OVER(ORDER BY id
 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),2) AS mean3
FROM sample ORDER BY id;

SQLite · exit 0

{"id": 1, "mean3": 12.0}
{"id": 2, "mean3": 16.0}
…

View complete example ↗SQL-07-005

Compare rank and dense rank

SQL preview

SELECT id,value,rank() OVER(ORDER BY value DESC) AS r,
 dense_rank() OVER(ORDER BY value DESC) AS dense_r
FROM sample ORDER BY value DESC,id;

SQLite · exit 0

{"id": 2, "value": 20.0, "r": 1, "dense_r": 1}
{"id": 3, "value": 18.0, "r": 2, "dense_r": 2}
…

View complete example ↗SQL-07-006

Give LAST_VALUE a complete frame

SQL preview

SELECT id,first_value(value) OVER w AS first,
 last_value(value) OVER w AS last FROM sample
WINDOW w AS (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING
                         AND UNBOUNDED FOLLOWING) ORDER BY id;

SQLite · exit 0

{"id": 1, "first": 12.0, "last": 16.0}
{"id": 2, "first": 12.0, "last": 16.0}
…

View complete example ↗

08 · Recursive queries

SQL-08-001

Generate a finite sequence

SQL preview

WITH RECURSIVE seq(n) AS (
 VALUES(1) UNION ALL SELECT n+1 FROM seq WHERE n<5
) SELECT n,n*n AS square FROM seq ORDER BY n;

SQLite · exit 0

{"n": 1, "square": 1}
{"n": 2, "square": 4}
…

View complete example ↗SQL-08-002

Generate a calendar with missing days

SQL preview

WITH RECURSIVE days(day) AS (
 VALUES('2026-01-01') UNION ALL
 SELECT date(day,'+1 day') FROM days WHERE day<'2026-01-09'
) SELECT d.day,count(s.id) AS n FROM days AS d
…

SQLite · exit 0

{"day": "2026-01-01", "n": 1}
{"day": "2026-01-02", "n": 1}
…

View complete example ↗SQL-08-003

Walk a parent-child hierarchy

SQL preview

CREATE TABLE node(id INTEGER PRIMARY KEY,parent INTEGER,name TEXT…
INSERT INTO node VALUES(1,NULL,'Archive'),(2,1,'Field'),
 (3,1,'Lab'),(4,2,'North'),(5,2,'South');
WITH RECURSIVE tree(id,path,depth) AS (
…

SQLite · exit 0

{"path": "Archive", "depth": 0}
{"path": "Archive/Field", "depth": 1}
…

View complete example ↗SQL-08-004

Prevent cycles in a graph walk

SQL preview

CREATE TABLE edge(a INTEGER,b INTEGER);
INSERT INTO edge VALUES(1,2),(2,3),(3,1),(2,4);
WITH RECURSIVE walk(id,path,depth) AS (
 VALUES(1,',1,',0) UNION ALL
…

SQLite · exit 0

{"id": 1, "depth": 0}
{"id": 2, "depth": 1}
…

View complete example ↗SQL-08-005

Carry an accumulated calculation

SQL preview

WITH RECURSIVE fact(n,value) AS (
 VALUES(0,1) UNION ALL
 SELECT n+1,value*(n+1) FROM fact WHERE n<6
) SELECT n,value FROM fact ORDER BY n;

SQLite · exit 0

{"n": 0, "value": 1}
{"n": 1, "value": 1}
…

View complete example ↗SQL-08-006

Expand a bill of materials

SQL preview

CREATE TABLE component(parent TEXT,child TEXT,qty INTEGER);
INSERT INTO component VALUES('kit','case',1),('kit','pack',2),('p…
WITH RECURSIVE bom(part,qty) AS (
 VALUES('kit',1) UNION ALL
…

SQLite · exit 0

{"part": "bolt", "needed": 8}
{"part": "case", "needed": 1}

View complete example ↗

09 · Schema, types and constraints

SQL-09-001

Reject a violated CHECK constraint

SQL preview

CREATE TABLE quantity(id INTEGER PRIMARY KEY,n INTEGER NOT NULL C…
INSERT INTO quantity VALUES(1,-1);

SQLite · expected exit 1

Error near line 21 of SQL-09-001.sql: CHECK constraint failed: n>…

View complete example ↗SQL-09-002

Enforce a foreign key

SQL preview

CREATE TABLE parent(id INTEGER PRIMARY KEY);
CREATE TABLE child(id INTEGER PRIMARY KEY,parent_id INTEGER REFER…
INSERT INTO child VALUES(1,99);

SQLite · expected exit 1

Error near line 22 of SQL-09-002.sql: FOREIGN KEY constraint fail…

View complete example ↗SQL-09-003

Compare STRICT storage with ANY

SQL preview

CREATE TABLE typed(n INTEGER,tag ANY) STRICT;
INSERT INTO typed VALUES('000123','000123');
SELECT n,typeof(n) AS n_type,tag,typeof(tag) AS tag_type FROM typ…

SQLite · exit 0

{"n": 123, "n_type": "integer", "tag": "000123", "tag_type": "tex…

View complete example ↗SQL-09-004

Store a generated column

SQL preview

CREATE TABLE line(qty INTEGER,unit_cents INTEGER,
 total_cents INTEGER GENERATED ALWAYS AS (qty*unit_cents) STORED);
INSERT INTO line(qty,unit_cents) VALUES(3,120),(2,125);
SELECT qty,unit_cents,total_cents FROM line ORDER BY total_cents;

SQLite · exit 0

{"qty": 2, "unit_cents": 125, "total_cents": 250}
{"qty": 3, "unit_cents": 120, "total_cents": 360}

View complete example ↗SQL-09-005

Use a composite key without rowid

SQL preview

CREATE TABLE coordinate(a TEXT,b INTEGER,value TEXT,
 PRIMARY KEY(a,b)) WITHOUT ROWID;
INSERT INTO coordinate VALUES('A',1,'north'),('A',2,'south'),('B'…
SELECT a,b,value FROM coordinate ORDER BY a,b;

SQLite · exit 0

{"a": "A", "b": 1, "value": "north"}
{"a": "A", "b": 2, "value": "south"}
…

View complete example ↗SQL-09-006

See how UNIQUE treats NULL

SQL preview

CREATE TABLE code(id INTEGER PRIMARY KEY,label TEXT UNIQUE);
INSERT INTO code VALUES(1,NULL),(2,NULL),(3,'X');
SELECT count(*) AS n,count(label) AS known,
 count(DISTINCT label) AS distinct_known FROM code;

SQLite · exit 0

{"n": 3, "known": 1, "distinct_known": 1}

View complete example ↗

10 · Writing data, views and triggers

SQL-10-001

Insert a row with RETURNING

SQL preview

INSERT INTO sample VALUES(9,'A',14,'2026-01-10','new')
RETURNING id,site,value;

SQLite · exit 0

{"id": 9, "site": "A", "value": 14.0}

View complete example ↗SQL-10-002

Update one row with RETURNING

SQL preview

UPDATE sample SET value=value+2 WHERE id=2
RETURNING id,value;

SQLite · exit 0

{"id": 2, "value": 22.0}

View complete example ↗SQL-10-003

Delete one row with RETURNING

SQL preview

DELETE FROM sample WHERE id=8 RETURNING id,site;

SQLite · exit 0

{"id": 8, "site": "D"}

View complete example ↗SQL-10-004

Handle a conflict with UPSERT

SQL preview

INSERT INTO site(id,label) VALUES('A','River East'),('D','Desert')
ON CONFLICT(id) DO UPDATE SET label=excluded.label;
SELECT id,label FROM site ORDER BY id;

SQLite · exit 0

{"id": "A", "label": "River East"}
{"id": "B", "label": "Hill"}
…

View complete example ↗SQL-10-005

Reuse a view definition

SQL preview

CREATE VIEW site_total AS
 SELECT site,sum(value) AS total FROM sample GROUP BY site;
SELECT site,total FROM site_total ORDER BY site;

SQLite · exit 0

{"site": "A", "total": 36.0}
{"site": "B", "total": 28.0}
…

View complete example ↗SQL-10-006

Record changes with a trigger

SQL preview

CREATE TABLE audit(id INTEGER PRIMARY KEY,sample_id INTEGER,
 old_value REAL,new_value REAL);
CREATE TRIGGER sample_audit AFTER UPDATE OF value ON sample
BEGIN
…

SQLite · exit 0

{"sample_id": 1, "old_value": 12.0, "new_value": 13.0}

View complete example ↗

11 · Transactions and savepoints

SQL-11-001

Inspect and roll back a change

SQL preview

BEGIN;
UPDATE sample SET value=99 WHERE id=1;
SELECT 'staged' AS phase,value FROM sample WHERE id=1;
ROLLBACK;
…

SQLite · exit 0

{"phase": "staged", "value": 99.0}

View complete example ↗SQL-11-002

Commit a group of writes

SQL preview

BEGIN;
INSERT INTO sample VALUES(9,'A',14,'2026-01-10','new');
COMMIT;
SELECT count(*) AS n,sum(value) AS total FROM sample;

SQLite · exit 0

{"n": 9, "total": 114.0}

View complete example ↗SQL-11-003

Roll back to a savepoint

SQL preview

BEGIN;
UPDATE sample SET value=14 WHERE id=1;
SAVEPOINT later;
UPDATE sample SET value=0 WHERE id=2;
…

SQLite · exit 0

{"id": 1, "value": 14.0}
{"id": 2, "value": 20.0}

View complete example ↗SQL-11-004

Defer a foreign-key check until commit

SQL preview

CREATE TABLE parent(id INTEGER PRIMARY KEY);
CREATE TABLE child(name TEXT,parent_id INTEGER REFERENCES parent(…
 DEFERRABLE INITIALLY DEFERRED);
BEGIN;
…

SQLite · exit 0

{"name": "pending", "parent_id": 42}

View complete example ↗SQL-11-005

Begin a write transaction immediately

SQL preview

BEGIN IMMEDIATE;
UPDATE sample SET value=21 WHERE id=1;
COMMIT;
SELECT id,value FROM sample WHERE id=1;

SQLite · exit 0

{"id": 1, "value": 21.0}

View complete example ↗SQL-11-006

Select write-ahead logging

SQL preview

PRAGMA journal_mode=WAL;
BEGIN; UPDATE sample SET value=value+1 WHERE id=1; COMMIT;
SELECT id,value FROM sample WHERE id=1;

SQLite · exit 0

{"journal_mode": "wal"}

View complete example ↗

12 · Indexes and query plans

SQL-12-001

Read a table-scan plan

SQL preview

EXPLAIN QUERY PLAN
SELECT id,site,value FROM sample WHERE site='A';

SQLite · exit 0

{"id": 2, "parent": 0, "notused": 216, "detail": "SCAN sample"}

View complete example ↗SQL-12-002

Add a selective index

SQL preview

CREATE INDEX ix_site ON sample(site);
EXPLAIN QUERY PLAN
SELECT id,site,value FROM sample WHERE site='A';

SQLite · exit 0

{"id": 3, "parent": 0, "notused": 62, "detail": "SEARCH sample US…

View complete example ↗SQL-12-003

Use a covering index

SQL preview

CREATE INDEX ix_site_value ON sample(site,value);
EXPLAIN QUERY PLAN
SELECT site,value FROM sample WHERE site='A' ORDER BY value;

SQLite · exit 0

{"id": 3, "parent": 0, "notused": 55, "detail": "SEARCH sample US…

View complete example ↗SQL-12-004

Use a composite index prefix and range

SQL preview

CREATE INDEX ix_site_day ON sample(site,day);
EXPLAIN QUERY PLAN
SELECT id,day FROM sample
WHERE site='A' AND day>='2026-01-03' ORDER BY day;

SQLite · exit 0

{"id": 3, "parent": 0, "notused": 47, "detail": "SEARCH sample US…

View complete example ↗SQL-12-005

Index a subset of rows

SQL preview

CREATE INDEX ix_missing ON sample(day) WHERE note IS NULL;
EXPLAIN QUERY PLAN
SELECT id,day FROM sample WHERE note IS NULL
 AND day>='2026-01-03' ORDER BY day;

SQLite · exit 0

{"id": 4, "parent": 0, "notused": 194, "detail": "SEARCH sample U…

View complete example ↗SQL-12-006

Index an expression

SQL preview

CREATE INDEX ix_lower_label ON site(lower(label));
EXPLAIN QUERY PLAN
SELECT id,label FROM site WHERE lower(label)='river';

SQLite · exit 0

{"id": 3, "parent": 0, "notused": 61, "detail": "SEARCH site USIN…

View complete example ↗

13 · JSON documents

SQL-13-001

Extract typed JSON values

SQL preview

WITH doc(body) AS (VALUES('{"site":"A","count":3,"checked":true,"…
SELECT json_extract(body,'$.site') AS site,
 json_extract(body,'$.count') AS n,
 json_type(body,'$.checked') AS checked_type,
…

SQLite · exit 0

{"site": "A", "n": 3, "checked_type": "true", "missing_type": "nu…

View complete example ↗SQL-13-002

Compare JSON text and SQL scalars

SQL preview

WITH doc(body) AS (VALUES('{"n":4,"empty":null}'))
SELECT body->'$.n' AS json_value,body->>'$.n' AS sql_value,
 body->>'$.empty' AS empty,typeof(body->'$.n') AS json_type,
 typeof(body->>'$.n') AS sql_type
…

SQLite · exit 0

{"json_value": "4", "sql_value": 4, "empty": null, "json_type": "…

View complete example ↗SQL-13-003

Expand a JSON array into rows

SQL preview

SELECT CAST(key AS INTEGER) AS position,value,type
FROM json_each('["A","B",null]') ORDER BY position;

SQLite · exit 0

{"position": 0, "value": "A", "type": "text"}
{"position": 1, "value": "B", "type": "text"}
…

View complete example ↗SQL-13-004

Modify and merge JSON objects

SQL preview

SELECT json_set('{"site":"A","count":3}',
 '$.count',4,'$.checked',json('true')) AS updated,
 json_patch('{"site":"A","keep":1}',
            '{"site":"B","keep":null}') AS patched;

SQLite · exit 0

{"updated": "{\"site\":\"A\",\"count\":4,\"checked\":true}", "pat…

View complete example ↗SQL-13-005

Aggregate ordered values into JSON

SQL preview

SELECT json_group_array(site ORDER BY site) AS sites
FROM (SELECT DISTINCT site FROM sample);

SQLite · exit 0

{"sites": "[\"A\",\"B\",\"C\",\"D\"]"}

View complete example ↗SQL-13-006

Store SQLite JSONB

SQL preview

SELECT typeof(jsonb('{"site":"A","n":3}')) AS storage,
 json(jsonb('{"site":"A","n":3}')) AS text_json,
 json_extract(jsonb('{"site":"A","n":3}'),'$.n') AS n;

SQLite · exit 0

{"storage": "blob", "text_json": "{\"site\":\"A\",\"n\":3}", "n":…

View complete example ↗

14 · Full-text search

SQL-14-001

Search terms with FTS5

SQL preview

CREATE VIRTUAL TABLE docs USING fts5(title,body);
INSERT INTO docs(rowid,title,body) VALUES
 (1,'River survey','pottery fragments near the river bank'),
 (2,'Hill archive','bronze tools from the hill settlement'),
…

SQLite · exit 0

{"rowid": 1, "title": "River survey"}
{"rowid": 3, "title": "Coastal notes"}
…

View complete example ↗SQL-14-002

Search an exact phrase

SQL preview

SELECT rowid,title FROM docs
WHERE docs MATCH '"river bank"' ORDER BY rowid;

SQLite · exit 0

{"rowid": 1, "title": "River survey"}

View complete example ↗SQL-14-003

Combine a prefix and an exclusion

SQL preview

SELECT rowid,title FROM docs
WHERE docs MATCH 'pot* NOT laboratory' ORDER BY rowid;

SQLite · exit 0

{"rowid": 1, "title": "River survey"}
{"rowid": 3, "title": "Coastal notes"}

View complete example ↗SQL-14-004

Order matches by BM25

SQL preview

SELECT rowid,title FROM docs WHERE docs MATCH 'pottery'
ORDER BY bm25(docs),rowid;

SQLite · exit 0

{"rowid": 4, "title": "River laboratory"}
{"rowid": 1, "title": "River survey"}
…

View complete example ↗SQL-14-005

Highlight matching tokens

SQL preview

SELECT rowid,highlight(docs,1,'[',']') AS excerpt
FROM docs WHERE docs MATCH 'pottery' ORDER BY rowid;

SQLite · exit 0

{"rowid": 1, "excerpt": "[pottery] fragments near the river bank"}
{"rowid": 3, "excerpt": "[pottery] and shells from the coastal su…
…

View complete example ↗SQL-14-006

Restrict a full-text query by column

SQL preview

SELECT rowid,title FROM docs
WHERE docs MATCH 'title:river AND body:analysis' ORDER BY rowid;

SQLite · exit 0

{"rowid": 4, "title": "River laboratory"}

View complete example ↗

15 · Inspection and database copies

SQL-15-001

Inspect the schema catalog

SQL preview

SELECT name,type FROM sqlite_schema
WHERE type='table' ORDER BY name;

SQLite · exit 0

{"name": "sample", "type": "table"}
{"name": "site", "type": "table"}

View complete example ↗SQL-15-002

Query column metadata as a table

SQL preview

SELECT name,type,"notnull" AS required
FROM pragma_table_info('sample') ORDER BY cid;

SQLite · exit 0

{"name": "id", "type": "INTEGER", "required": 0}
{"name": "site", "type": "TEXT", "required": 1}
…

View complete example ↗SQL-15-003

Check declared foreign-key relationships

SQL preview

SELECT count(*) AS violations FROM pragma_foreign_key_check;

SQLite · exit 0

{"violations": 0}

View complete example ↗SQL-15-004

Run a database consistency check

SQL preview

PRAGMA quick_check;

SQLite · exit 0

{"quick_check": "ok"}

View complete example ↗SQL-15-005

Inspect affected-row counts

SQL preview

UPDATE sample SET note='none' WHERE id=-1;
SELECT changes() AS affected;
INSERT INTO sample VALUES(9,'A',14,'2026-01-10','new');
SELECT changes() AS affected;

SQLite · exit 0

{"affected": 0}

View complete example ↗SQL-15-006

Create and verify a database copy

SQL preview

VACUUM INTO 'snapshot.sqlite3';
ATTACH DATABASE 'snapshot.sqlite3' AS snapshot;
SELECT (SELECT count(*) FROM main.sample) AS original_rows,
       (SELECT count(*) FROM snapshot.sample) AS copied_rows;
…

SQLite · exit 0

{"original_rows": 8, "copied_rows": 8}

View complete example ↗

16 · Practical query patterns

SQL-16-001

Pivot a small categorical summary

SQL preview

SELECT sum(CASE WHEN site='A' THEN value ELSE 0 END) AS a,
 sum(CASE WHEN site='B' THEN value ELSE 0 END) AS b,
 sum(CASE WHEN site='C' THEN value ELSE 0 END) AS c,
 sum(CASE WHEN site='D' THEN value ELSE 0 END) AS d FROM sample;

SQLite · exit 0

{"a": 36.0, "b": 28.0, "c": 20.0, "d": 16.0}

View complete example ↗SQL-16-002

Find islands of consecutive dates

SQL preview

WITH distinct_days AS (SELECT DISTINCT day FROM sample),
 keyed AS (SELECT day,julianday(day)-row_number() OVER(ORDER BY d…
           FROM distinct_days)
SELECT min(day) AS start,max(day) AS end,count(*) AS days
…

SQLite · exit 0

{"start": "2026-01-01", "end": "2026-01-06", "days": 6}
{"start": "2026-01-08", "end": "2026-01-09", "days": 2}

View complete example ↗SQL-16-003

Return the top two within each group

SQL preview

WITH ranked AS (SELECT id,site,value,row_number() OVER(
 PARTITION BY site ORDER BY value DESC,id) AS position FROM sampl…
SELECT id,site,value FROM ranked WHERE position<=2
ORDER BY site,position;

SQLite · exit 0

{"id": 3, "site": "A", "value": 18.0}
{"id": 1, "site": "A", "value": 12.0}
…

View complete example ↗SQL-16-004

Deduplicate an explicit ingestion key

SQL preview

CREATE TABLE incoming(id INTEGER PRIMARY KEY,batch TEXT,code TEXT…
INSERT INTO incoming VALUES(1,'b1','A',12),(2,'b1','A',12),
 (3,'b1','B',20),(4,'b2','A',18);
DELETE FROM incoming WHERE id IN(
…

SQLite · exit 0

{"id": 1, "batch": "b1", "code": "A", "value": 12}
{"id": 3, "batch": "b1", "code": "B", "value": 20}
…

View complete example ↗SQL-16-005

Calculate a calendar-based rolling total

SQL preview

WITH RECURSIVE days(day) AS (
 VALUES('2026-01-01') UNION ALL
 SELECT date(day,'+1 day') FROM days WHERE day<'2026-01-09'
), filled AS (SELECT d.day,coalesce(s.value,0) AS value
…

SQLite · exit 0

{"day": "2026-01-01", "value": 12.0, "total3": 12.0}
{"day": "2026-01-02", "value": 20.0, "total3": 32.0}
…

View complete example ↗SQL-16-006

Compose a joined summary report

SQL preview

WITH totals AS (SELECT site,count(*) AS n,sum(value) AS total
 FROM sample GROUP BY site)
SELECT t.site,coalesce(s.label,'(unmapped)') AS label,t.n,t.total,
 round(t.total/(SELECT sum(value) FROM sample),3) AS share
…

SQLite · exit 0

{"site": "A", "label": "River", "n": 3, "total": 36.0, "share": 0…
{"site": "B", "label": "Hill", "n": 2, "total": 28.0, "share": 0.…
…

View complete example ↗

Reading the tour

Three ways to see a change

SQL program

Read the query beside its real result; expand the complete schema and fixture.

Actual rows

Inspect typed SQLite output, NULL values, query plans and expected constraint failures.

One change

Follow a comparison, run the standalone SQL or explore its downloadable database.

Read from the beginning or jump to a stable example ID. Each example includes its complete input and captured result. Copy link preserves the address of the guide you are reading, including its current domain and path.

The starting atlas supplies twenty-four relational questions. This guide retains all twenty-four topics, develops them into complete independent programs, and adds schema design, data changes, transactions, indexes, JSON, full-text search and practical report patterns.

Every example starts with a new database. The concise view shows the statements to study; expand the complete program for its schema and fixture. No example depends on having run an earlier one. Run a downloaded SQL file in a fresh working directory with sqlite3 -header -box :memory: < SQL-01-001.sql. The WAL example needs a fresh file database instead of :memory:. The VACUUM INTO example writes snapshot.sqlite3 in its working directory.

Results are actual output from the native sqlite3 CLI. The guide renders its JSON rows as tables. NULL means an absent SQL value; italic empty text is an empty string. A failed constraint is labelled as an expected diagnostic. Only nonempty result sets produce JSON arrays in this CLI format. Downloads include the standalone SQL, captured results and a data snapshot after the example. Downloads use rollback-journal mode to remain self-contained; the WAL example still executes in WAL mode and reports that native result. If a failed program leaves a transaction open, connection close rolls it back before the database is saved.

Starting database

The shared fixture has eight synthetic observations. Values total 100. Site D has no lookup label, the seventh calendar day has no observation, and notes contain NULL. The site column is deliberately not a declared foreign key, so lookup gaps remain available for join examples. Dedicated constraint examples create actual foreign-key relationships with enforcement enabled.

id	site	value	day	note
1	A	12	2026-01-01	NULL
2	B	20	2026-01-02	checked
3	A	18	2026-01-03	NULL
4	B	8	2026-01-04	recheck
5	C	10	2026-01-05	NULL
6	A	6	2026-01-06	checked
7	C	10	2026-01-08	archived
8	D	16	2026-01-09	NULL

Lookup labels: A = River; B = Hill; C = Coast. Full-text examples use a separate four-document synthetic corpus in their complete source. All dates and input values are fixed. Query-plan wording depends on the SQLite version; these examples show actual chosen plans rather than timing claims. Full preparation requires SQLite 3.45 or newer with JSON and FTS5.

References and provenance

The atlas supplies the starting questions. This guide uses a new, explicitly listed fixture and independent explanations, comparisons and added examples. All twenty-four original topics are mapped in ATLAS_PANEL properties and sources/atlas-provenance.json. No poster builder or external dataset is needed. SQLite and its installed extensions remain external dependencies.

01 · A first SELECT

SQL-01-001

Select stored columns

A projection chooses which fields answer the question. An explicit ORDER BY defines the returned row order.

Clause or feature	Meaning
`SELECT`	Choose the output columns.
`FROM sample`	Read the shared observation table.
`ORDER BY id`	Return a deterministic order.

Clause or feature	Meaning
`WHERE`	Retain only rows whose condition is true.
`>=16`	Include the threshold itself.

Clause or feature	Meaning
`DESC`	Sort values from largest to smallest.
`id`	Break any ties consistently.
`LIMIT 3`	Return at most three rows.

Clause or feature	Meaning
`DISTINCT`	Return each selected value once.
`ORDER BY site`	Sort the unique results.

Clause or feature	Meaning
`value*2`	Calculate from each stored value.
`AS doubled`	Name the result column.

Clause or feature	Meaning
`CASE WHEN`	Evaluate a classification condition.
`ELSE`	Provide the alternative class.

id	note
1	(missing)
2	checked
3	(missing)
4	recheck
5	(missing)
6	checked
7	archived
8	(missing)

Clause or feature	Meaning
`coalesce`	Return the first non-NULL argument.
`NULL`	Represent an absent value, not an empty string.

Clause or feature	Meaning
`IS NULL`	Test whether a value is absent.
`WHERE`	Unknown conditions do not pass the filter.

Clause or feature	Meaning
`BETWEEN`	Use an inclusive numeric interval.
`IN`	Accept either named site.
`AND`	Require both conditions.

id	site	value
1	A	12.0
2	B	20.0
3	A	18.0
4	B	8.0
5	C	10.0
6	A	6.0
7	C	10.0
8	D	16.0

id	site	value
2	B	20.0
3	A	18.0
8	D	16.0

id	value
2	20.0
3	18.0
8	16.0

id	doubled
1	24.0
2	40.0
3	36.0
4	16.0
5	20.0
6	12.0
7	20.0
8	32.0

Clause or feature	Meaning
`LIKE check%`	Match the chosen prefix.
`lower`	Normalize the displayed text; it does not update storage.

Clause or feature	Meaning
`NULLS LAST`	Place absent notes after actual strings.
`id`	Stabilize ties within each note value.

Clause or feature	Meaning
`id>4`	Continue after the last seen key.
`ORDER BY id`	Use the same key for filtering and ordering.
`LIMIT`	Bound the page size.

Clause or feature	Meaning
`Integer division`	Two integer operands produce an integer quotient.
`CAST AS REAL`	Choose real arithmetic explicitly.

Clause or feature	Meaning
`printf S%02d`	Pad integer identifiers with a leading zero.
`lower`	Convert ASCII site letters to lowercase.

Clause or feature	Meaning
`trim`	Remove surrounding spaces.
`\|\|`	Concatenate text values.
`replace`	Substitute an exact substring.

Clause or feature	Meaning
`nullif(a,b)`	Return NULL when the arguments are equal.
`Display fallback`	Zero here is a presentation choice, not the unknown mathematical result.

Clause or feature	Meaning
`julianday`	Map a date to a numeric day count.
`Fixed origin`	Keep this calculation reproducible.

Clause or feature	Meaning
`start of month`	Move to day one.
`+1 month then -1 day`	Move to the last day of the original month.
`strftime`	Choose a date display format.

Clause or feature	Meaning
`count(*)`	Count observation rows.
`avg / min / max`	Compute summary statistics over values.

SQLite,one change at a time

SQL and result gallery

01 · A first SELECT

Select stored columns

Filter individual observations

Find the top three

Return distinct values

Calculate a derived column

Classify with CASE

02 · Filtering, NULL and ordering

Display a value for NULL

Test NULL explicitly

Combine range and membership filters

Match and normalize text

Place NULL values last

Continue with keyset pagination

03 · Expressions, text and dates

Distinguish integer and real arithmetic

Format a consistent identifier

Clean and join text

Guard a denominator with NULLIF

Measure elapsed days

Calculate a calendar boundary

04 · Aggregates and grouped questions

Summarize the entire table

Group observations by site

Filter groups with HAVING

Count missing and present values

Calculate a weighted mean

Make an ordered list per group

05 · Joins and missing relationships

Match a lookup with an inner join

Keep observations with missing metadata

Find missing relationships

Compare an ON filter with a WHERE filter

See join multiplicity

Join a table to itself

06 · Subqueries, CTEs and sets

Compare with a correlated group mean

Name an intermediate result

Use EXISTS as a membership question

Expose the NOT IN and NULL trap

Compare UNION and UNION ALL

Intersect and subtract sets

07 · Window functions

Number rows within each site

Calculate a running total

Measure change from the preceding row

Calculate a three-row moving mean

Compare rank and dense rank

Give LAST_VALUE a complete frame

08 · Recursive queries

Generate a finite sequence

Generate a calendar with missing days

Walk a parent-child hierarchy

Prevent cycles in a graph walk

Carry an accumulated calculation

Expand a bill of materials

09 · Schema, types and constraints

Reject a violated CHECK constraint

Enforce a foreign key

Compare STRICT storage with ANY

Store a generated column

Use a composite key without rowid

See how UNIQUE treats NULL

10 · Writing data, views and triggers

Insert a row with RETURNING

Update one row with RETURNING

Delete one row with RETURNING

Handle a conflict with UPSERT

Reuse a view definition

Record changes with a trigger

11 · Transactions and savepoints

Inspect and roll back a change

Commit a group of writes

Roll back to a savepoint

Defer a foreign-key check until commit

Begin a write transaction immediately

Select write-ahead logging

12 · Indexes and query plans

SQLite,
one change at a time

Clause or feature	Meaning
`GROUP BY site`	Form one group per site.
`ORDER BY`	Define the order of the grouped results.

Clause or feature	Meaning
`HAVING`	Test an aggregate after grouping.
`sum(value)>25`	Keep only groups that meet the total threshold.

Clause or feature	Meaning
`count(note)`	Count only non-NULL notes.
`FILTER`	Apply a condition to one aggregate.

Clause or feature	Meaning
`Weighted numerator`	Multiply every value by its weight.
`Weight total`	Divide by the sum of weights, not the number of rows.

Clause or feature	Meaning
`group_concat`	Combine values with a chosen separator.
`Aggregate ORDER BY`	Specify list order explicitly; supported since SQLite 3.44.

Clause or feature	Meaning
`JOIN ON`	Require a matching site key.
`Aliases`	Distinguish observation columns from lookup columns.

Clause or feature	Meaning
`LEFT JOIN`	Preserve the rows from the left side.
`NULL label`	Expose the missing lookup for D.

Clause or feature	Meaning
`NOT EXISTS`	Keep a row only when the correlated lookup is empty.
`Correlation`	Match the lookup to the current observation.

Clause or feature	Meaning
`ON condition`	Preserve unmatched left rows.
`WHERE condition`	Require a matching River label after the join.

Clause or feature	Meaning
`One-to-many`	One observation can have several tags.
`Result unit`	The output now represents an observation-tag pair.