SQLite reference
A practical reference with runnable examples and expected output. Each example opens a fresh temporary dataset and stages SQL for your review. Press Run when ready.
12 examples
SELECT and WHERE
Choose columns and filter rows before sorting.
SELECT name, price FROM products WHERE price >= 30 ORDER BY price DESC, name;Expected output
Returns products costing at least 30, highest price first.
WHERE filters rows before grouping. Add a stable tie breaker to ORDER BY; without ORDER BY, row order is unspecified.
LEFT JOIN
Keep rows even when the other table has no match.
SELECT i.name, COALESCE(SUM(s.quantity),0) AS units FROM items i LEFT JOIN stock s ON s.item_id=i.id GROUP BY i.id ORDER BY i.id;Expected output
Keyboard 20; Mouse 33; Monitor 10; Cable 60; Dock 3; Stand 0.
A filter on the right table in WHERE can remove unmatched rows. Put matching conditions in ON when unmatched rows must survive.
COUNT and GROUP BY
Count rows per group and filter groups with HAVING.
WITH values_to_count(category, value) AS (VALUES ('A',1),('A',NULL),('B',2)) SELECT category,COUNT(*) AS rows,COUNT(value) AS non_null FROM values_to_count GROUP BY category HAVING COUNT(*)>1 ORDER BY category;Expected output
One row: A, 2, 1.
COUNT(*) counts rows. COUNT(column) ignores NULL. WHERE filters input rows; HAVING filters aggregate groups.
SUM, AVG and ROUND
Calculate totals and averages while handling empty inputs.
WITH numbers(x) AS (VALUES (10),(20),(NULL)) SELECT SUM(x) AS total,ROUND(AVG(x),2) AS average FROM numbers;Expected output
total = 30; average = 15.0.
SUM and AVG ignore NULL. SUM over no values is NULL; use COALESCE(SUM(x),0) if zero is the desired result. For exact money, consider integer minor units.
COALESCE and NULLIF
Provide a fallback or turn a special value into NULL.
SELECT COALESCE(NULL,'Unknown') AS label, 10.0 / NULLIF(0,0) AS ratio;Expected output
label = Unknown; ratio = NULL.
Use IS NULL to test missing values. Equality comparisons with NULL do not evaluate to true. NULLIF can make a denominator policy explicit.
CASE expressions
Assign labels with conditions evaluated in order.
WITH prices(x) AS (VALUES (15),(80),(NULL)) SELECT x,CASE WHEN x IS NULL THEN 'Missing' WHEN x>=50 THEN 'Premium' ELSE 'Standard' END AS tier FROM prices ORDER BY x;Expected output
NULL → Missing; 15 → Standard; 80 → Premium.
The first matching branch wins. An omitted ELSE returns NULL when no condition matches.
substr, length and trim
Clean text and extract a section using a one-based position.
SELECT trim(' SQLite ') AS cleaned,substr('SQLite',1,3) AS prefix,length('SQLite') AS characters;Expected output
cleaned = SQLite; prefix = SQL; characters = 6.
substr positions start at 1. Default trim removes spaces, not every kind of whitespace. Built-in lower/upper case conversion is ASCII-focused without extensions.
date and strftime
Group ISO timestamps or move a fixed date by a calendar interval.
SELECT date('2026-01-31','start of month','+1 month') AS next_month,strftime('%Y-%m','2026-01-20 10:30:00') AS month;Expected output
next_month = 2026-02-01; month = 2026-01.
SQLite has no dedicated date storage class. Store consistent ISO text or explicitly documented Unix timestamps. Modifier order matters; use unixepoch for seconds.
json_extract and json_each
Extract typed values from valid JSON and expand arrays.
SELECT json_extract('{"device":"mobile","count":3}','$.device') AS device;
SELECT value FROM json_each('[10,20,30]') ORDER BY key;Expected output
First result: mobile. Second result: 10, 20, 30.
With one path, json_extract returns SQL scalars for scalar JSON values. Malformed JSON raises an error; use json_valid when inspecting unknown input.
Common table expressions
Name an intermediate result to make a query easier to read.
WITH sales(region,amount) AS (VALUES ('East',10),('East',20),('West',15)), totals AS (SELECT region,SUM(amount) AS total FROM sales GROUP BY region) SELECT region,total FROM totals WHERE total>20 ORDER BY region;Expected output
East, 30.
A CTE exists for one statement. It is not automatically a persisted table or a guaranteed performance improvement.
Window functions
Calculate a running total without collapsing input rows.
WITH sales(day,amount) AS (VALUES ('2026-01-01',10),('2026-01-02',20),('2026-01-03',5)) SELECT day,amount,SUM(amount) OVER (ORDER BY day ROWS UNBOUNDED PRECEDING) AS running_total FROM sales ORDER BY day;Expected output
Running totals: 10, 30, 35.
Use PARTITION BY for separate groups. ROWS defines a row-based frame; include a unique ordering key when dates can repeat.
Indexes and query plans
Compare a filter before and after adding an index.
CREATE TABLE plan_demo(id INTEGER PRIMARY KEY, category TEXT, amount INTEGER);
WITH RECURSIVE n(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM n WHERE x<1000) INSERT INTO plan_demo SELECT x,CASE WHEN x%20=0 THEN 'rare' ELSE 'common' END,x FROM n;
SELECT id,amount FROM plan_demo WHERE category='rare';
-- After running this setup, select ONLY the SELECT above and click Explain.
-- Keep it as a baseline, close the plan, then select and run the CREATE below.
-- CREATE INDEX plan_demo_category ON plan_demo(category);
-- Uncomment the CREATE before running it. Explain the same SELECT again.Expected output
The SELECT returns 50 rows. Before the index, expect a SCAN; after creating the index, expect a SEARCH using plan_demo_category.
Plans describe a strategy, not measured runtime. Indexes use space and add write work. Run this setup once in its fresh example database.
No matching examples. Try a shorter search or another category.
Put it into practice
Follow a learning path · Explore sample databases · Understand query plans