PLAINFINCH learn by building

SQL cheat sheet

The 128 keywords and functions you will use most, on one page. The everyday ones come first. Print it, or keep it open beside your editor.

Full reference

Everyday 70

% (wildcard)
Inside LIKE, stand for any run of characters, including none at all.
+ - * / %
Do arithmetic: add, subtract, multiply, divide and find a remainder.
;
End one SQL statement so the next one can begin.
< > <= >=
Compare values to check which is smaller or larger.
= <> !=
Compare two values to check if they are equal or different.
? placeholder
Mark a spot in a query where a value is safely filled in later.
AND
Require that both conditions are true for a row to be kept.
AS
Give a column, calculation or table a new, clearer name.
ASC / DESC
Sort in ascending order (the default) or descending order.
AVG()
Work out the average of the values in a column.
BEGIN
Start a transaction so several changes succeed or fail together.
BETWEEN
Check whether a value falls inside a range, including both ends.
CASE
Choose a value based on conditions, like an if-then inside a query.
COALESCE()
Return the first value in a list that is not missing.
COMMIT
Save all the changes made since the transaction began.
COUNT(*)
Count how many rows there are, including rows with missing values.
COUNT(column)
Count the rows where a column has a value, skipping NULLs.
COUNT(DISTINCT)
Count how many different values appear in a column.
CREATE INDEX
Build a lookup structure that makes searching a column faster.
CREATE TABLE
Make a new table with named columns and their types.
date()
Get a date as YYYY-MM-DD text, optionally shifted by modifiers.
DEFAULT
Fill in a value automatically when an insert leaves a column out.
DELETE FROM
Remove rows from a table, usually limited with WHERE.
DISTINCT
Remove duplicate rows so each result appears only once.
DROP TABLE
Delete a whole table along with all of its rows.
ELSE
Give the value CASE uses when none of the conditions are true.
END
Close a CASE expression so the query can carry on.
FOREIGN KEY
Mark a column that points to the primary key of another table.
FROM
Name the table a query reads its rows from.
GROUP BY
Collect rows that share a value so totals are worked out per group.
HAVING
Keep only the groups that pass a condition, applied after grouping.
IN
Check whether a value matches any item in a list.
IN (subquery)
Check whether a value appears in the results of another query.
INSERT INTO
Add one or more new rows to a table you name.
INTEGER PRIMARY KEY
Make a column that SQLite fills with a new number for each row.
IS NOT NULL
Check that a value is present rather than missing.
IS NULL
Check whether a value is missing, since = NULL never matches.
JOIN
Combine rows from two tables where a matching condition holds.
LEFT JOIN
Keep every row from the first table, even when nothing matches.
LENGTH()
Count how many characters are in a piece of text.
LIKE
Match text against a pattern that uses % and _ as wildcards.
LIMIT
Return at most a given number of rows from a query.
LOWER()
Turn every letter in a piece of text into a lowercase letter.
MAX()
Find the largest value in a column, or in each group.
MIN()
Find the smallest value in a column, or in each group.
NOT
Flip a condition so true becomes false and false becomes true.
NOT NULL
Refuse any row that leaves this column empty.
NULL
A special marker meaning a value is missing or unknown.
ON
Give the condition that decides which rows of a join match up.
OR
Keep a row when at least one of the conditions is true.
ORDER BY
Sort the result rows by one or more columns.
PRIMARY KEY
Mark the column whose value uniquely identifies each row.
REFERENCES
Name the table and column that a foreign key points to.
ROLLBACK
Undo all the changes made since the transaction began.
ROUND()
Round a number to a chosen number of decimal places.
SELECT
Choose which columns or values a query returns.
SELECT *
Return every column of a table, handy for a quick look at the data.
SET
Say which columns to change and what their new values are.
subquery
A query placed inside another query, written in brackets.
SUM()
Add up all the values in a column, skipping missing ones.
table aliases
Give a table a short name so its columns are quicker to write.
TRIM()
Remove spaces from the start and end of some text.
UNIQUE
Refuse any row that repeats a value already in this column.
UPDATE
Change values in rows that already exist in a table.
UPPER()
Turn every letter in a piece of text into a capital letter.
VALUES
List the values for each new row in an INSERT statement.
WHEN ... THEN
Give one condition inside CASE and the value to use when it is true.
WHERE
Keep only the rows that pass a condition you write.
WITH
Name a query step up front so later parts can use it like a table.
||
Join two pieces of text together into one longer string.

Also useful 58

-- comment
Start a note that SQL ignores until the end of the line.
/* */
Wrap a note across several lines so SQL skips over it.
:name placeholder
Mark a named spot in a query where a value is safely filled in later.
_ (wildcard)
Inside LIKE, stand for exactly one character of any kind.
ALTER TABLE ADD COLUMN
Add a new column to a table that already exists.
ALTER TABLE RENAME
Give an existing table or column a new name.
CHECK
Refuse any row whose values fail a condition you write.
correlated subquery
A subquery that refers to the current row of the outer query.
CREATE UNIQUE INDEX
Build an index that also refuses duplicate values.
CREATE VIEW
Save a query under a name so you can select from it like a table.
date modifiers
Add words like '+7 days' or 'start of month' to shift a date.
DENSE_RANK()
Rank rows so ties share a number with no gaps after them.
DROP INDEX
Delete an index you no longer need, leaving the table as it was.
DROP VIEW
Delete a saved view without touching the tables it reads from.
ESCAPE
Pick a character that makes % or _ match literally inside LIKE.
EXISTS
Check whether a subquery returns at least one row.
EXPLAIN QUERY PLAN
Show how SQLite will run a query, such as whether it uses an index.
FULL JOIN
Keep every row from both tables, matched where possible.
GLOB
Match text with case-sensitive * and ? wildcards in SQLite.
IF NOT EXISTS
Skip creating something when it is already there, avoiding an error.
IFNULL()
Swap a missing value for a fallback value you choose.
IIF()
Pick one of two values depending on whether a condition is true.
INNER JOIN
The full name for JOIN, which keeps only rows that match in both tables.
INSERT ... SELECT
Add rows to a table using the results of a query.
INSTR()
Find the position where some text first appears inside another text.
julianday()
Turn a date into a day number so you can subtract two dates.
LAG()
Read a value from the row before the current one.
LEAD()
Read a value from the row after the current one.
NOT BETWEEN
Check that a value falls outside a range of values.
NOT EXISTS
Check that a subquery returns no rows at all.
NOT IN
Check that a value matches none of the items in a list.
NOT LIKE
Keep rows whose text does not match a LIKE pattern.
NULLS FIRST / NULLS LAST
Choose whether missing values appear at the start or end of a sort.
OFFSET
Skip a number of rows before returning results, often used for paging.
ON DELETE CASCADE
Delete linked rows automatically when the row they point to is deleted.
ON DELETE RESTRICT
Block deleting a row while other rows still point to it.
ON DELETE SET NULL
Empty the linking column when the row it points to is deleted.
OVER
Turn a function into a window function that looks across related rows.
PARTITION BY
Split rows into separate groups for a window function to work within.
PRAGMA
Run a SQLite command that reports or changes database settings.
RANK()
Rank rows so ties share a number and the next rank skips ahead.
RELEASE
Keep the changes since a savepoint and forget that savepoint.
REPLACE()
Swap every copy of one piece of text for another inside a value.
RETURNING
Show the rows that an INSERT, UPDATE or DELETE just changed.
RIGHT JOIN
Keep every row from the second table, even when nothing matches.
ROLLBACK TO
Undo changes back to a savepoint without ending the transaction.
ROW_NUMBER()
Number the rows 1, 2, 3 in order, with no shared numbers.
rowid
The hidden number SQLite gives every row in an ordinary table.
running SUM
Use SUM with OVER and ORDER BY to keep a running total down the rows.
SAVEPOINT
Mark a point inside a transaction that you can undo back to later.
scalar subquery
A subquery that returns a single value you can use like a number.
self-join
Join a table to itself to compare rows within the same table.
sqlite_master
A built-in table that lists every table, index and view in the database.
strftime()
Format a date or time as text using codes like %Y and %m.
STRICT
Make SQLite refuse values that do not match a column's type.
SUBSTR()
Take part of a text value, starting at a chosen position.
UNION ALL
Stack the rows of two queries on top of each other, keeping duplicates.
WITH RECURSIVE
Build rows step by step, useful for walking trees and hierarchies.

PlainFinch SQL cheat sheet · plainfinch.com/sql/cheat-sheet/ · version 3.25.2