SQL cheat sheet

A scannable SQL reference: 23 short snippets across 12 topics, each linking back to the lesson it came from.

At a glance

Quick snippets

SELECT: reading data

The shape of a query

SELECT   name, price            -- 5. choose columns
FROM     products               -- 1. source table
WHERE    price > 20             -- 2. filter rows
GROUP BY category               -- 3. collapse groups
HAVING   COUNT(*) > 1           -- 4. filter groups
ORDER BY price DESC             -- 6. sort
LIMIT    10;                    -- 7. restrict rows

Columns and aliases

SELECT DISTINCT category FROM products;

SELECT name, price * 1.2 AS price_with_tax
FROM products;

-- CASE for conditional labels
SELECT name,
  CASE WHEN price >= 100 THEN 'premium'
       WHEN price >= 20  THEN 'mid'
       ELSE 'budget' END AS tier
FROM products;

Filtering rows

SELECT * FROM users
WHERE country = 'US'
  AND (plan = 'pro' OR trial_ends > CURRENT_DATE)
  AND email IS NOT NULL;

Full lesson: SELECT: reading data →

Joins

The four joins

SELECT o.id, c.name, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id;

-- customers with no orders: anti-join idiom
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Row multiplication

-- wrong: p.price counted once per item row
SELECT SUM(p.price)
FROM products p JOIN items i ON i.product_id = p.id;

-- right: aggregate each level separately
SELECT SUM(t.total)
FROM (
  SELECT order_id, SUM(quantity * unit_price) AS total
  FROM items GROUP BY order_id
) t;

Self joins and CTEs

-- employees and their managers, same table
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

-- clearer for multi-step logic
WITH monthly AS (
  SELECT DATE_TRUNC('month', created_at) AS m, SUM(total) AS revenue
  FROM orders GROUP BY 1
)
SELECT m, revenue FROM monthly ORDER BY m;

Full lesson: Joins →

Aggregation and GROUP BY

Grouping

SELECT category, COUNT(*) AS n, AVG(price) AS avg_price
FROM products
WHERE active = TRUE
GROUP BY category
HAVING COUNT(*) > 5
ORDER BY n DESC;

Window functions (a glimpse)

SELECT name, category, price,
       AVG(price) OVER (PARTITION BY category) AS cat_avg,
       RANK() OVER (ORDER BY price DESC) AS price_rank
FROM products;

Full lesson: Aggregation and GROUP BY →

INSERT, UPDATE, DELETE

Updating and deleting

UPDATE products
SET price = price * 0.9, updated_at = NOW()
WHERE category = 'home'
RETURNING id, price;

DELETE FROM products
WHERE discontinued = TRUE AND stock = 0;

Upserts

-- PostgreSQL: insert, or update on conflict
INSERT INTO settings (user_id, theme)
VALUES (1, 'dark')
ON CONFLICT (user_id)
DO UPDATE SET theme = EXCLUDED.theme, updated_at = NOW();

-- MySQL equivalent
INSERT INTO settings (user_id, theme) VALUES (1, 'dark')
ON DUPLICATE KEY UPDATE theme = VALUES(theme);

Transactions

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;   -- or ROLLBACK to undo everything

Full lesson: INSERT, UPDATE, DELETE →

Indexes and query speed

What an index gives you

CREATE INDEX idx_products_category ON products (category);
CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at DESC);
CREATE UNIQUE INDEX idx_users_email ON users (email);

DROP INDEX idx_products_category;

Reading a plan

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

Full lesson: Indexes and query speed →

Data types, NULL and three-valued logic

Types you will actually use

CREATE TABLE readings (
  id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sensor    TEXT         NOT NULL,
  value     NUMERIC(8,2),                    -- exact: 0.1 + 0.2 stays 0.3
  taken_at  TIMESTAMPTZ  NOT NULL DEFAULT now(),
  active    BOOLEAN      NOT NULL DEFAULT TRUE,
  note      TEXT                             -- nullable on purpose
);

Coalesce, nullif and casts

SELECT
  COALESCE(nickname, first_name, 'anonymous') AS display_name,
  COALESCE(discount, 0)                       AS discount,
  NULLIF(trim(email), '')                     AS email,      -- empty string becomes NULL
  CAST(price AS NUMERIC(10,2))                AS price,
  price::TEXT                                 AS price_text  -- PostgreSQL shorthand
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

-- counts rows and counts known values: two different numbers
SELECT COUNT(*) AS rows_seen, COUNT(phone) AS phones_known FROM users;

Full lesson: Data types, NULL and three-valued logic →

Creating and altering schema with DDL

Temp tables and transactional DDL

BEGIN;
  CREATE TABLE audit_tmp (id BIGINT, note TEXT);
  INSERT INTO audit_tmp VALUES (1, 'test');
ROLLBACK;   -- PostgreSQL and SQLite remove the table; MySQL and Oracle keep it

CREATE TEMP TABLE scratch (n INT);   -- visible only to this session
DROP TABLE IF EXISTS audit_tmp CASCADE;   -- also drops dependent views

Full lesson: Creating and altering schema with DDL →

Views, functions, procedures and triggers

Views

CREATE VIEW order_totals AS
SELECT o.id AS order_id,
       o.customer_id,
       SUM(i.quantity * i.unit_price) AS total
FROM orders o
JOIN order_items i ON i.order_id = o.id
GROUP BY o.id, o.customer_id;

SELECT * FROM order_totals WHERE total > 500;

CREATE OR REPLACE VIEW order_totals AS ...;   -- change the definition, keep the name
DROP VIEW order_totals;

Materialised views

CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT date_trunc('month', placed_at) AS month,
       SUM(total) AS revenue
FROM orders
GROUP BY 1;

CREATE UNIQUE INDEX ON monthly_revenue (month);   -- required for CONCURRENTLY

REFRESH MATERIALIZED VIEW monthly_revenue;              -- blocks readers while it runs
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue; -- slower, does not block readers

Full lesson: Views, functions, procedures and triggers →

Transactions and isolation levels

Transactions and ACID

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;   -- or ROLLBACK to undo every statement in the block

-- savepoints let you undo part of a transaction
BEGIN;
  INSERT INTO audit (note) VALUES ('start');
  SAVEPOINT after_audit;
  UPDATE accounts SET balance = -1 WHERE id = 1;   -- violates a CHECK
  ROLLBACK TO SAVEPOINT after_audit;               -- the audit row survives
COMMIT;

Isolation levels and read phenomena

-- per transaction, not globally
BEGIN ISOLATION LEVEL REPEATABLE READ;
  SELECT * FROM accounts WHERE id = 1;   -- the same answer even after another commit
COMMIT;

-- SERIALIZABLE turns anomalies into errors, so it needs a retry loop
BEGIN ISOLATION LEVEL SERIALIZABLE;
  UPDATE counters SET n = n + 1 WHERE id = 1;
COMMIT;   -- may fail with a serialization error: catch it and run the block again

Full lesson: Transactions and isolation levels →

Query tuning and execution plans

Statistics and join strategies

-- the planner decides with statistics, so keep them fresh and specific
ANALYZE orders;
CREATE STATISTICS orders_customer_status (dependencies)
  ON customer_id, status FROM orders;

-- see how many rows the planner believes a predicate matches
EXPLAIN SELECT * FROM orders WHERE customer_id = 42 AND status = 'paid';

-- disable a strategy only to test a hypothesis, never in production
SET enable_seqscan = off;
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
RESET enable_seqscan;

Full lesson: Query tuning and execution plans →

Portable SQL across database engines

Identifiers, quoting and case

-- standard: double quotes for identifiers, single quotes for strings
SELECT "order", 'it''s fine' FROM "sales"."orders";

-- quoting is not portable, so avoid needing it at all
SELECT "order" FROM sales.orders;   -- PostgreSQL, standard
SELECT `order` FROM sales.orders;   -- MySQL
SELECT [order] FROM sales.orders;   -- SQL Server

-- lower_snake_case names that are not reserved words need no quoting anywhere
SELECT order_no, customer_id, placed_at FROM sales_orders;

Full lesson: Portable SQL across database engines →

Next steps: analytics, data modelling and practice

How to keep improving

-- a self-check exercise list: write each of these against your own data
-- 1. monthly revenue with month-over-month change
-- 2. the top 3 products per category by revenue, in one query
-- 3. customers who bought in two consecutive months
-- 4. a funnel over an events table with no inner joins
-- 5. the same report as a materialised view refreshed nightly
-- 6. the query plan for number 2, and one change that makes it faster

Full lesson: Next steps: analytics, data modelling and practice →

FAQ

Related cheat sheets

MySQL PostgreSQL MongoDB Redis SQLite

Last refreshed 2026-09-27.