SQL cheat sheet: a runnable reference for everyday queries

This file is a cheat sheet you can run. It creates two small tables in memory, fills them with sample rows, and then runs one example for each common SQL pattern. Nothing is written to disk.

Run it

sqlite3 -header -column :memory: < sql-cheatsheet.sql

SQLite is preinstalled on macOS and available as a package on every major Linux distribution. On Windows, download the command-line tools from sqlite.org. You can also paste the statements into a PostgreSQL or MySQL client; the differences are listed below.

The cheat sheet

-- Run with: sqlite3 -header -column :memory: < sql-cheatsheet.sql

CREATE TABLE customers (
  id    INTEGER PRIMARY KEY,
  name  TEXT NOT NULL,
  city  TEXT,
  email TEXT UNIQUE
);

CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers (id),
  amount      NUMERIC(10, 2) NOT NULL,
  status      TEXT NOT NULL DEFAULT 'pending',
  created_at  DATE NOT NULL
);

INSERT INTO customers (id, name, city, email) VALUES
  (1, 'Ada', 'London', '[email protected]'),
  (2, 'Brian', 'Berlin', NULL),
  (3, 'Chen', 'London', '[email protected]'),
  (4, 'Dana', NULL, '[email protected]');

INSERT INTO orders (id, customer_id, amount, status, created_at) VALUES
  (10, 1, 25.00, 'paid', '2026-01-05'),
  (11, 1, 140.00, 'paid', '2026-02-11'),
  (12, 2, 60.00, 'refunded', '2026-02-20'),
  (13, 3, 15.50, 'paid', '2026-03-02'),
  (14, 3, 99.90, 'pending', '2026-03-15');

-- Filter, sort, limit
SELECT name, city FROM customers
WHERE city IN ('London', 'Berlin') AND name LIKE 'A%'
ORDER BY name ASC
LIMIT 10 OFFSET 0;

-- Ranges and NULL checks
SELECT id, amount FROM orders WHERE amount BETWEEN 20 AND 100;
SELECT name FROM customers WHERE email IS NULL;

-- Unique values
SELECT DISTINCT status FROM orders ORDER BY status;

-- Aggregates per group, then filter the groups
SELECT status, COUNT(*) AS n, SUM(amount) AS total
FROM orders
GROUP BY status
HAVING SUM(amount) > 50
ORDER BY total DESC;

-- INNER JOIN: only matching rows
SELECT c.name, o.id AS order_id, o.amount
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id;

-- LEFT JOIN: keep every customer, count orders (0 when none)
SELECT c.name, COUNT(o.id) AS orders
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.name;

-- Rows with no match (anti-join)
SELECT c.name
FROM customers AS c
WHERE NOT EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.id);

-- Conditional values and defaults
SELECT o.id,
       CASE WHEN o.amount >= 100 THEN 'large'
            WHEN o.amount >= 50 THEN 'medium'
            ELSE 'small' END AS size,
       COALESCE(c.city, 'unknown') AS city
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id;

-- Common table expression (a named subquery)
WITH paid AS (
  SELECT customer_id, SUM(amount) AS total
  FROM orders
  WHERE status = 'paid'
  GROUP BY customer_id
)
SELECT c.name, p.total
FROM paid AS p
JOIN customers AS c ON c.id = p.customer_id
ORDER BY p.total DESC;

-- Window function: rank orders within each customer
SELECT customer_id, id, amount,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rank_in_customer
FROM orders;

-- Change data inside a transaction; test the WHERE clause with a SELECT first
BEGIN;
UPDATE orders SET status = 'paid' WHERE id = 14;
DELETE FROM orders WHERE status = 'refunded';
SELECT id, status FROM orders;
ROLLBACK;

-- Index a frequent lookup, then check the plan
CREATE INDEX idx_orders_customer_id ON orders (customer_id);
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 3;

Quick syntax table

• Filter: =, <>, <, >=, IN (...), BETWEEN a AND b (inclusive), LIKE 'a%' (% any text, _ one character), IS NULL.
• Combine: AND binds before OR, so add parentheses when you mix them.
• Aggregate: COUNT(*), COUNT(col) (skips NULL), SUM, AVG, MIN, MAX; filter groups with HAVING.
• Join: JOIN keeps matches only; LEFT JOIN keeps all left rows; anti-join with NOT EXISTS.
• Set operations: UNION removes duplicates, UNION ALL keeps them and is faster.
• Clause order: SELECT, FROM, JOIN, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT.

Where SQLite, PostgreSQL and MySQL differ

• Limit rows: LIMIT n OFFSET m works in all three. Standard SQL also defines FETCH FIRST n ROWS ONLY, which PostgreSQL supports.
• Auto-increment ids: SQLite uses INTEGER PRIMARY KEY, PostgreSQL uses GENERATED ALWAYS AS IDENTITY (or the older SERIAL), and MySQL uses AUTO_INCREMENT.
• String concatenation: a || b in SQLite and PostgreSQL; CONCAT(a, b) in MySQL, where || means OR by default.
• Quoting: strings always use single quotes. For identifiers, use double quotes in PostgreSQL and SQLite, and backticks in MySQL unless ANSI_QUOTES mode is on.
• Types: SQLite is flexibly typed and accepts declarations such as NUMERIC(10, 2) or DATE without enforcing them; it stores dates as text or numbers. Use STRICT tables (SQLite 3.37+) for type checking.
• Transactions: BEGIN / COMMIT / ROLLBACK work everywhere. MySQL commits automatically before most schema changes such as CREATE TABLE, so those cannot be rolled back.
• Query plans: EXPLAIN QUERY PLAN in SQLite, EXPLAIN or EXPLAIN ANALYZE in PostgreSQL and MySQL.
• Upsert: INSERT ... ON CONFLICT (col) DO UPDATE in PostgreSQL and SQLite; INSERT ... ON DUPLICATE KEY UPDATE in MySQL.

Window functions such as ROW_NUMBER() need SQLite 3.25+, MySQL 8.0+ or any supported PostgreSQL version.