SQL Cheat Sheet

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, deposit INTEGER);
INSERT INTO tools (name, deposit) VALUES ('hand saw', 350), ('tile cutter', 1500);
SELECT count(*) AS tools FROM tools;
SELECT name FROM tools ORDER BY name;

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO tools (name) VALUES ('hand saw');

CREATE TABLE members (
  id       INTEGER PRIMARY KEY,                       -- auto-fills with the next integer
  name     TEXT    NOT NULL,                          -- must always have a value
  email    TEXT    UNIQUE,                            -- no two rows alike; NULLs allowed, any number of them
  joined   TEXT    DEFAULT '2026-01-01',              -- used when INSERT omits the column
  credits  INTEGER NOT NULL DEFAULT 0 CHECK (credits >= 0),
  active   INTEGER NOT NULL DEFAULT 1                 -- SQLite has no BOOLEAN: 0 or 1
);

INSERT INTO members (name, email) VALUES ('Asha', 'asha@example.invalid');
INSERT INTO members (name, email, credits) VALUES ('Bruno', 'bruno@example.invalid', 3);

SELECT * FROM members;
SELECT name, typeof(credits) AS credits_type, typeof(joined) AS joined_type FROM members LIMIT 1;

CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT NOT NULL, credits INTEGER CHECK (credits >= 0));
INSERT INTO members (name, credits) VALUES ('Asha', 2);
SELECT name, credits FROM members;
INSERT INTO members (name, credits) VALUES ('Bruno', -1);

PRAGMA foreign_keys = ON;

CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE loans (
  id        INTEGER PRIMARY KEY,
  member_id INTEGER NOT NULL REFERENCES members(id) ON DELETE CASCADE,
  taken_on  TEXT NOT NULL
);

INSERT INTO members (id, name) VALUES (1, 'Asha');
INSERT INTO loans (member_id, taken_on) VALUES (1, '2026-03-04');
SELECT * FROM loans;

INSERT INTO loans (member_id, taken_on) VALUES (99, '2026-03-05');

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, shelf TEXT, deposit INTEGER);
INSERT INTO tools (name, shelf, deposit) VALUES
  ('hedge trimmer', 'garden', 950), ('cordless drill', 'power', 1200), ('hand saw', 'hand', 350);

SELECT * FROM tools;                                    -- every column, exploring only
SELECT shelf, name FROM tools;                          -- named columns, in the order you asked
SELECT
  name,
  deposit / 100.0        AS pounds,                     -- 100.0 not 100: see Numbers below
  upper(shelf)           AS shelf_label,
  name || ' (' || shelf || ')' AS label                 -- || joins text
FROM tools;
SELECT 2 + 2 AS no_table_needed;                        -- FROM is optional in SQLite

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, shelf TEXT, deposit INTEGER);
INSERT INTO tools (name, shelf, deposit) VALUES
  ('hedge trimmer', 'garden', 950), ('cordless drill', 'power', 1200),
  ('wheelbarrow', 'garden', 500), ('Hammer', 'hand', 350), ('donated ladder', 'garden', NULL);

SELECT name FROM tools WHERE deposit > 500 AND shelf <> 'power';
SELECT name FROM tools WHERE shelf IN ('garden', 'hand') AND deposit BETWEEN 350 AND 950;
SELECT name FROM tools WHERE name LIKE '%er';           -- SQLite LIKE ignores ASCII case
SELECT name FROM tools WHERE deposit IS NULL;
SELECT name FROM tools WHERE COALESCE(deposit, 0) < 400; -- substitute rather than lose the row

SELECT
  NULL = NULL          AS equals_is_unknown,   -- NULL, printed as an empty-ish NULL
  NULL IS NULL         AS is_null_works,
  1 + NULL             AS arithmetic_spreads,
  'a' || NULL          AS concat_spreads,
  COALESCE(NULL, NULL, 'fallback') AS coalesce_first_non_null,
  IFNULL(NULL, 0)      AS sqlite_ifnull,
  NULLIF(5, 5)         AS nullif_when_equal;

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, shelf TEXT, deposit INTEGER);
INSERT INTO tools (name, shelf, deposit) VALUES
  ('hedge trimmer', 'garden', 950), ('cordless drill', 'power', 1200),
  ('wheelbarrow', 'garden', 500), ('Hammer', 'hand', 350), ('donated ladder', 'garden', NULL);

SELECT name, deposit FROM tools ORDER BY deposit DESC NULLS LAST LIMIT 2;
SELECT name, deposit FROM tools ORDER BY deposit;                    -- NULL sorts first ascending
SELECT name FROM tools ORDER BY name COLLATE NOCASE;                 -- case-insensitive sort
SELECT shelf, count(*) AS n FROM tools GROUP BY shelf ORDER BY n DESC, shelf LIMIT 2 OFFSET 1;
SELECT DISTINCT shelf FROM tools ORDER BY shelf;

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, shelf TEXT, deposit INTEGER);
INSERT INTO tools (name, shelf, deposit) VALUES
  ('hedge trimmer', 'garden', 950), ('cordless drill', 'power', 1200),
  ('wheelbarrow', 'garden', 500), ('tile cutter', 'power', 1500),
  ('hand saw', 'hand', 350), ('donated ladder', 'garden', NULL);

SELECT
  count(*)              AS rows_in_table,   -- counts rows
  count(deposit)        AS values_present,  -- counts non-NULLs
  count(DISTINCT shelf) AS shelves,
  sum(deposit)          AS total,
  round(avg(deposit))   AS mean,            -- NULLs are skipped, not zeroed
  min(deposit)          AS cheapest,
  max(deposit)          AS dearest
FROM tools;

SELECT shelf, count(*) AS tools, sum(deposit) AS held, group_concat(name, ' + ') AS contents
FROM tools
GROUP BY shelf
HAVING count(*) >= 2
ORDER BY held DESC;

SELECT count(*) AS matched, sum(deposit) AS sum_is_null, COALESCE(sum(deposit), 0) AS safe
FROM tools WHERE deposit > 99999;

CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, shelf TEXT);
CREATE TABLE loans (id INTEGER PRIMARY KEY, member_id INTEGER, tool_id INTEGER, taken_on TEXT);
INSERT INTO members (id, name) VALUES (1, 'Asha'), (2, 'Bruno'), (3, 'Cleo');
INSERT INTO tools (id, name, shelf) VALUES (10, 'hedge trimmer', 'garden'), (11, 'tile cutter', 'power');
INSERT INTO loans (member_id, tool_id, taken_on) VALUES (1, 10, '2026-03-04'), (1, 11, '2026-03-21'), (2, 10, '2026-04-02');

-- INNER JOIN: only the pairs that match.
SELECT m.name AS member, t.name AS tool, l.taken_on
FROM loans AS l
JOIN members AS m ON m.id = l.member_id
JOIN tools   AS t ON t.id = l.tool_id
ORDER BY l.taken_on;

-- LEFT JOIN: every left row survives; unmatched right columns come back NULL.
SELECT m.name, count(l.id) AS loans          -- count(l.id), never count(*)
FROM members AS m
LEFT JOIN loans AS l ON l.member_id = m.id
GROUP BY m.id, m.name
ORDER BY loans DESC, m.name;

-- Anti-join: what is in A but not in B.
SELECT t.name AS never_borrowed
FROM tools AS t
LEFT JOIN loans AS l ON l.tool_id = t.id
WHERE l.id IS NULL;

CREATE TABLE loans (id INTEGER PRIMARY KEY, member_id INTEGER, deposit INTEGER);
INSERT INTO loans (member_id, deposit) VALUES (1, 950), (1, 1200), (1, 500), (2, 350), (3, 1500);

-- Scalar subquery: one row, one column, usable as a value.
SELECT count(*) AS above_average
FROM loans
WHERE deposit > (SELECT avg(deposit) FROM loans);

-- IN / NOT EXISTS with a subquery.
SELECT DISTINCT member_id FROM loans WHERE member_id IN (SELECT member_id FROM loans WHERE deposit >= 1200);

-- Derived table: a subquery in FROM, which must be named.
SELECT round(avg(n), 2) AS mean_loans
FROM (SELECT member_id, count(*) AS n FROM loans GROUP BY member_id) AS per_member;

-- The same thing as a CTE, plus a second step using the first.
WITH per_member AS (
  SELECT member_id, count(*) AS loans, sum(deposit) AS value
  FROM loans
  GROUP BY member_id
),
busy AS (
  SELECT * FROM per_member WHERE loans > 1
)
SELECT member_id, loans, value FROM busy;

CREATE TABLE takings (id INTEGER PRIMARY KEY, held_on TEXT, session TEXT, amount INTEGER);
INSERT INTO takings (held_on, session, amount) VALUES
  ('2026-03-07', 'repair', 4200), ('2026-03-14', 'sharpen', 1800),
  ('2026-03-21', 'repair', 3600), ('2026-03-28', 'sharpen', 1800);

SELECT
  held_on,
  session,
  amount,
  sum(amount)  OVER ()                                    AS grand_total,
  sum(amount)  OVER (PARTITION BY session)                AS session_total,
  sum(amount)  OVER (ORDER BY held_on
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
  row_number() OVER (ORDER BY amount DESC, held_on)       AS row_no,
  rank()       OVER (ORDER BY amount DESC)                AS placing,
  dense_rank() OVER (ORDER BY amount DESC)                AS dense_placing,
  lag(amount)  OVER (ORDER BY held_on)                    AS previous,
  lead(amount) OVER (ORDER BY held_on)                    AS next_one
FROM takings
ORDER BY held_on;

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, shelf TEXT, deposit INTEGER);
INSERT INTO tools (name, shelf, deposit) VALUES
  ('hedge trimmer', 'garden', 950), ('wheelbarrow', 'garden', 500), ('post driver', 'garden', 1100),
  ('tile cutter', 'power', 1500), ('cordless drill', 'power', 1200);

WITH ranked AS (
  SELECT shelf, name, deposit,
         row_number() OVER (PARTITION BY shelf ORDER BY deposit DESC, name) AS place
  FROM tools
)
SELECT shelf, place, name, deposit FROM ranked WHERE place <= 2 ORDER BY shelf, place;

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, deposit INTEGER);
INSERT INTO tools (name, deposit) VALUES
  ('hedge trimmer', 950), ('tile cutter', 1500), ('hand saw', 350), ('donated ladder', NULL);

SELECT
  name,
  deposit,
  CASE
    WHEN deposit IS NULL  THEN 'not set'
    WHEN deposit >= 1000  THEN 'high'
    WHEN deposit >= 500   THEN 'medium'
    ELSE 'low'
  END AS band,
  sum(CASE WHEN deposit >= 500 THEN 1 ELSE 0 END) OVER () AS count_of_500_plus
FROM tools
ORDER BY name;

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT NOT NULL, shelf TEXT, deposit INTEGER);

INSERT INTO tools (name, shelf, deposit) VALUES ('hedge trimmer', 'garden', 950);          -- one row
INSERT INTO tools (name, shelf, deposit) VALUES
  ('cordless drill', 'power', 1200), ('wheelbarrow', 'garden', 500);                       -- many rows

CREATE TABLE garden (id INTEGER PRIMARY KEY, name TEXT, deposit INTEGER);
INSERT INTO garden (name, deposit) SELECT name, deposit FROM tools WHERE shelf = 'garden'; -- from a query

UPDATE tools SET deposit = deposit + 100 WHERE shelf = 'power' RETURNING name, deposit;    -- SQLite 3.35+
SELECT changes() AS rows_changed;

DELETE FROM tools WHERE deposit < 600;
SELECT id, name, deposit FROM tools ORDER BY id;

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, deposit INTEGER);
INSERT INTO tools (name, deposit) VALUES ('hedge trimmer', 950), ('tile cutter', 1500);

BEGIN;
UPDATE tools SET deposit = 0;
SELECT name, deposit AS inside FROM tools ORDER BY id;
ROLLBACK;

SELECT name, deposit AS after_rollback FROM tools ORDER BY id;

BEGIN;
UPDATE tools SET deposit = deposit + 50 WHERE id = 1;
COMMIT;
SELECT name, deposit AS after_commit FROM tools ORDER BY id;

CREATE TABLE spring (id INTEGER PRIMARY KEY, member TEXT);
CREATE TABLE summer (id INTEGER PRIMARY KEY, member TEXT);
INSERT INTO spring (member) VALUES ('Asha'), ('Bruno'), ('Cleo'), ('Asha');
INSERT INTO summer (member) VALUES ('Bruno'), ('Devi');

SELECT member FROM spring UNION     SELECT member FROM summer ORDER BY member;  -- both, duplicates removed
SELECT member FROM spring UNION ALL SELECT member FROM summer ORDER BY member;  -- both, duplicates kept
SELECT member FROM spring INTERSECT SELECT member FROM summer;                  -- in both
SELECT member FROM spring EXCEPT    SELECT member FROM summer ORDER BY member;  -- in the first only

SELECT
  length('hedge trimmer')                AS len,
  upper('garden')                        AS up,
  lower('GARDEN')                        AS down,
  substr('hedge trimmer', 1, 5)          AS first_five,     -- 1-based, not 0-based
  substr('2026-04-02', 1, 7)             AS year_month,
  replace('hand saw', 'saw', 'plane')    AS replaced,
  trim('   padded   ')                   AS trimmed,
  instr('hedge trimmer', 'trim')         AS position_or_zero,
  'a' || '-' || 'b'                      AS joined,
  printf('%.2f', 9.5)                    AS formatted,
  cast('42' AS INTEGER) + 1              AS cast_then_add;

SELECT
  7 / 2            AS integer_division,   -- 3, not 3.5
  7 / 2.0          AS with_a_decimal,
  7 * 1.0 / 2      AS forced_to_decimal,
  CAST(7 AS REAL) / 2 AS cast_version,
  7 % 2            AS remainder,
  round(9.567, 2)  AS rounded,
  abs(-42)         AS absolute,
  max(3, 9)        AS bigger_of_two,       -- two arguments: not the aggregate
  min(3, 9)        AS smaller_of_two;

SELECT
  date('2026-04-02')                              AS normalised,
  date('2026-04-02', '+7 days')                   AS a_week_later,
  date('2026-04-02', 'start of month')            AS month_start,
  date('2026-04-02', 'start of month', '+1 month', '-1 day') AS month_end,
  strftime('%Y-%m', '2026-04-02')                 AS year_month,
  strftime('%w', '2026-04-02')                    AS day_of_week_0_sunday,
  CAST(julianday('2026-04-11') - julianday('2026-04-02') AS INTEGER) AS days_between,
  date('2026-04-02') < '2026-04-11'               AS text_compare_works;

CREATE TABLE loans (id INTEGER PRIMARY KEY, member_id INTEGER, taken_on TEXT);
INSERT INTO loans (member_id, taken_on) VALUES (1, '2026-03-04'), (2, '2026-03-19'), (1, '2026-04-08');

CREATE INDEX idx_loans_member ON loans(member_id);
CREATE UNIQUE INDEX idx_loans_member_date ON loans(member_id, taken_on);

EXPLAIN QUERY PLAN SELECT * FROM loans WHERE member_id = 1;
SELECT name FROM sqlite_master WHERE type = 'index' AND tbl_name = 'loans' ORDER BY name;