SQLPrintable

SQL Cheat Sheet

SELECT, WHERE, NULL, functions, CASE, GROUP BY, dates, JOIN, UNION, CTEs, views, indexes, transactions, window functions, triggers and security on one printable page.

Back to the SQL tutorial

Reading data

Syntax	What it does	Lesson
`SELECT * FROM t;`	Every column and row	Introduction
`SELECT a, b FROM t;`	Only some columns	SELECT
`SELECT a AS label`	Rename a column in the result	SELECT
`SELECT DISTINCT city`	Remove duplicates	SELECT

Filtering

Syntax	What it does	Lesson
`WHERE price > 10`	Compare: `= <> > < >= <=`	WHERE
`WHERE a = 1 AND (b = 2 OR c = 3)`	Combine conditions	WHERE
`WHERE city IN ('A', 'B')`	Match a list	WHERE
`WHERE n BETWEEN 1 AND 9`	Inclusive range	WHERE
`WHERE name LIKE 'A%'`	Pattern: `%` any, `_` one char	WHERE
`WHERE city IS NULL`	Missing value	WHERE

Functions & CASE

Syntax	What it does	Lesson
`UPPER(x)` `LOWER(x)` `LENGTH(x)`	Text functions	Functions
`SUBSTR(x, 1, 3)` `TRIM(x)`	Part of text, trim spaces	Functions
`a \|\| ' ' \|\| b`	Join text (`CONCAT` in MySQL)	Functions
`ROUND(x, 2)` `ABS(x)`	Number functions	Functions
`COALESCE(x, 'n/a')`	Fallback for NULL	Functions
`CASE WHEN c THEN a ELSE b END`	If-then-else in a query	CASE

Sorting & summarising

Syntax	What it does	Lesson
`ORDER BY price DESC`	Sort (ASC is default)	ORDER BY
`LIMIT 5`	First 5 rows	ORDER BY
`COUNT(*)` `SUM(x)` `AVG(x)`	Count, total, average	ORDER BY
`GROUP BY category`	One row per group	ORDER BY
`HAVING COUNT(*) > 1`	Filter groups	ORDER BY
`SUM(CASE WHEN c THEN 1 ELSE 0 END)`	Conditional count	CASE

Changing data

Syntax	What it does	Lesson
`INSERT INTO t (a, b) VALUES (1, 'x');`	Add a row	INSERT
`UPDATE t SET a = 2 WHERE id = 1;`	Change rows	UPDATE
`DELETE FROM t WHERE id = 1;`	Remove rows	DELETE
`BEGIN;` `COMMIT;` `ROLLBACK;`	All-or-nothing changes	Transactions

Tables & constraints

Syntax	What it does	Lesson
`CREATE TABLE t (id INTEGER PRIMARY KEY, ...)`	Create a table	CREATE TABLE
`ALTER TABLE t ADD COLUMN c TEXT;`	Add a column	CREATE TABLE
`DROP TABLE IF EXISTS t;`	Delete a table	CREATE TABLE
`NOT NULL` `UNIQUE` `DEFAULT 0`	Column rules	Constraints
`CHECK (qty > 0)`	Reject bad values	Constraints
`REFERENCES customers(id)`	Foreign key	Constraints

Joins & subqueries

Syntax	What it does	Lesson
`JOIN c ON o.customer_id = c.id`	Matching rows only	JOIN
`LEFT JOIN o ON o.customer_id = c.id`	Keep every left row	JOIN
`WHERE x > (SELECT AVG(x) FROM t)`	Scalar subquery	Subqueries
`WHERE EXISTS (SELECT 1 FROM ...)`	Related rows exist?	Subqueries
`WITH name AS (SELECT ...)`	Common table expression	Subqueries

Views, indexes & windows

Syntax	What it does	Lesson
`CREATE VIEW v AS SELECT ...;`	Save a query	Views
`CREATE INDEX i ON t (col);`	Faster lookups	Indexes
`EXPLAIN QUERY PLAN SELECT ...`	See how a query runs	Indexes
`ROW_NUMBER() OVER (ORDER BY x)`	Number rows	Window functions
`RANK() OVER (PARTITION BY g ORDER BY x)`	Rank within groups	Window functions
`SUM(x) OVER (ORDER BY d)`	Running total	Window functions

NULL & dates

Syntax	What it does	Lesson
`WHERE col IS NULL`	Find missing values	NULL Values
`COALESCE(col, 'n/a')`	Replace NULL with a default	NULL Values
`COUNT(col)` vs `COUNT(*)`	Skip NULLs / count every row	NULL Values
`WHERE d >= '2024-07-01' AND d < '2024-08-01'`	A safe date range	Dates & Times
`strftime('%Y-%m', d)`	Year and month (SQLite)	Dates & Times
`date(d, '+7 days')`	Add days (SQLite)	Dates & Times
`julianday(b) - julianday(a)`	Days between (SQLite)	Dates & Times

More joins & set operations

Syntax	What it does	Lesson
`LEFT JOIN b ... WHERE b.id IS NULL`	Rows with no match	More Joins
`FROM staff e JOIN staff m ON m.id = e.manager_id`	Self join	More Joins
`FULL OUTER JOIN`	All rows from both tables	More Joins
`CROSS JOIN`	Every combination	More Joins
`UNION` / `UNION ALL`	Stack results, with or without duplicates	UNION
`INTERSECT` / `EXCEPT`	In both / in first only	UNION

Cleaning & design

Syntax	What it does	Lesson
`TRIM(col)` `LOWER(col)`	Standardise text	Data Cleaning
`GROUP BY key HAVING COUNT(*) > 1`	Find duplicates	Data Cleaning
`NULLIF(TRIM(col), '')`	Blank to NULL	Data Cleaning
`PRIMARY KEY (a_id, b_id)`	Junction table key	Relationships
`REFERENCES t(id) ON DELETE CASCADE`	Delete linked rows too	Relationships
1NF, 2NF, 3NF	Store each fact once	Normalization

CTEs, triggers & security

Syntax	What it does	Lesson
`WITH a AS (...), b AS (...) SELECT ...`	Named query steps	CTEs
`WITH RECURSIVE r AS (anchor UNION ALL ...)`	Series and hierarchies	CTEs
`CREATE TRIGGER t AFTER UPDATE ON tbl`	Run SQL on data changes	Triggers & Procedures
`OLD.col` / `NEW.col`	Values before / after	Triggers & Procedures
`CALL procedure(1)`	Run a stored procedure	Triggers & Procedures
`WHERE email = ?`	Parameterized query	Security
`GRANT SELECT ON t TO user`	Give a privilege	Security
`REVOKE INSERT ON t FROM user`	Remove a privilege	Security

How to use this SQL cheat sheet

This SQL cheat sheet gathers the statements and clauses you'll use most, in the same order as the course: reading data, filtering and NULL, functions and CASE, sorting and summarising, changing data, dates, creating tables and constraints, joins and UNION, subqueries and CTEs, data cleaning, views, indexes, transactions, window functions, triggers and security. Each row links to the lesson that explains it properly, with example results from the course's shop database and the mistakes to avoid.

Keep it open while you practise in SQLite, MySQL or PostgreSQL. Where the three databases spell something differently, such as auto-numbered ids, joining text or date functions, the linked lesson shows each version. Type a keyword such as "JOIN" or "LIKE" into the filter box to find a row instantly, or print the page for a one-sheet reference next to your keyboard. Remember the order of clauses, too: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT.

SQL gotchas worth remembering

NULL is never equal to anything. WHERE phone = NULL matches no rows. Use IS NULL and IS NOT NULL.
WHERE filters rows, HAVING filters groups. You can't use an aggregate such as COUNT(*) in WHERE; move that condition to HAVING.
Every selected column must be grouped. With GROUP BY, each column in SELECT must either be in the GROUP BY or inside an aggregate.
Forgetting WHERE in UPDATE or DELETE changes every row. Run the same WHERE with SELECT first, and use a transaction when in doubt.
LEFT JOIN conditions belong in ON. Putting a condition on the right-hand table in WHERE quietly turns it back into an inner join.

Practise each pattern on a small table of your own, then confirm with the SQL quiz.

SQL Cheat Sheet

Reading data

Filtering

Functions & CASE

Sorting & summarising

Changing data

Tables & constraints

Joins & subqueries

Views, indexes & windows

NULL & dates

More joins & set operations

Cleaning & design

CTEs, triggers & security

How to use this SQL cheat sheet

SQL gotchas worth remembering

The SQL lessons behind this cheat sheet

Beginner

Intermediate

Advanced

Syntax and commands reference

Functions and querying reference

Database and table reference

Joins and advanced queries reference

Windows and performance reference

Practical SQL

Dialects and SQL practice