SQL cheat sheet

Getting data

SELECT * FROM films

Every column and every row of a table.

SELECT title, year FROM films

Only the columns you name, in that order.

SELECT title AS film FROM films

AS gives a column another name in the result.

SELECT title, price_per_day * 2 AS weekend FROM films

Arithmetic with +, -, * and /, row by row.

SELECT title FROM films LIMIT 5

At most 5 rows. Without ORDER BY the database picks which.

SELECT title FROM films LIMIT 5 OFFSET 10

OFFSET skips rows first: handy for showing results page by page.

Filtering with WHERE

SELECT title FROM films WHERE genre = 'horror'

Text between single quotes. = cares about upper and lower case.

SELECT title FROM films WHERE minutes >= 120

=, <> (or !=), <, <=, >, >=. Numbers without quotes.

SELECT * FROM rentals WHERE rented_on > '2024-10-15'

Dates as text 'year-month-day', always between quotes.

SELECT title FROM films WHERE (genre = 'drama' OR genre = 'comedy') AND year > 2000

AND comes before OR: put brackets around what belongs together.

SELECT title FROM films WHERE NOT genre = 'animation'

NOT turns a condition around.

Lists, ranges and patterns

SELECT name FROM members WHERE town IN ('Leuven', 'Gent')

IN is a short OR. NOT IN is the opposite.

SELECT title FROM films WHERE year BETWEEN 1990 AND 1999

BETWEEN includes both borders. Smallest value first.

SELECT title FROM films WHERE title LIKE 'The %'

% is zero or more characters, _ exactly one. In SQLite LIKE ignores upper and lower case.

SELECT name FROM members WHERE email IS NULL

Find missing values with IS NULL or IS NOT NULL, never with = NULL.

Sorting and duplicates

SELECT title, score FROM films ORDER BY score DESC

ORDER BY sorts, ASC ascending (the default), DESC descending.

SELECT title, year FROM films ORDER BY year DESC LIMIT 3

Sorting and then limiting gives "the newest three".

SELECT genre, title, score FROM films ORDER BY genre, score DESC

The second column decides when the first one ties. Each column has its own direction. NULL comes first when sorting ascending.

SELECT DISTINCT genre FROM films

DISTINCT removes duplicate rows. With several columns, the combination counts.

Counting and calculating

SELECT COUNT(*) FROM films

COUNT(*) counts rows. You get one row with one number.

SELECT COUNT(score), COUNT(DISTINCT genre) FROM films

COUNT(column) skips NULL, COUNT(DISTINCT column) counts different values.

SELECT SUM(fine), AVG(fine) FROM rentals

SUM adds up, AVG averages. Both skip NULL. Without rows, SUM gives NULL, not 0.

SELECT MIN(year), MAX(year) FROM films

Smallest and largest value. Works on text and dates too.

SELECT ROUND(AVG(score), 1) FROM films

ROUND(number, decimals) rounds. Watch out: 7 / 2 gives 3 in SQLite; write 7 * 1.0 / 2 for 3.5.

Grouping

SELECT genre, COUNT(*) AS films FROM films GROUP BY genre

One row per group. Every column in SELECT is in GROUP BY or inside a function such as COUNT.

SELECT genre, COUNT(*) FROM films GROUP BY genre HAVING COUNT(*) >= 4

WHERE filters rows before grouping, HAVING filters groups afterwards.

SELECT genre, AVG(score) AS average FROM films WHERE year > 1990 GROUP BY genre ORDER BY average DESC

You may sort on the result of a function, or on its alias.

SELECT town, plan, COUNT(*) FROM members GROUP BY town, plan

With several columns, every combination is a group.

Combining tables

The table before JOIN is on the left, the table after it on the right. The coloured part is what stays.

SELECT f.title, r.rented_on FROM rentals r JOIN films f ON r.film_id = f.id

JOIN (or INNER JOIN): only rows with a partner on both sides. r and f are aliases.

SELECT f.title, r.id FROM films f LEFT JOIN rentals r ON r.film_id = f.id

LEFT JOIN: every row on the left, also without a partner. The right side is then NULL.

SELECT f.title, r.id FROM rentals r RIGHT JOIN films f ON r.film_id = f.id

RIGHT JOIN: every row on the right. The same as a LEFT JOIN with the tables swapped.

SELECT m.name, r.id FROM members m FULL JOIN rentals r ON r.member_id = m.id

FULL JOIN: every row of both sides, with NULL where a partner is missing.

SELECT f.title FROM films f LEFT JOIN rentals r ON r.film_id = f.id WHERE r.id IS NULL

What is missing: LEFT JOIN and then IS NULL on the right side. Here: films that were never rented.

SELECT m.name, f.title FROM rentals r JOIN members m ON r.member_id = m.id JOIN films f ON r.film_id = f.id

Several JOINs in a row, each with its own ON.

SELECT a.name, b.name FROM members a JOIN members b ON a.town = b.town AND a.id < b.id

Self join: the same table twice, with two aliases. Here: members from the same town.

Smarter queries

SELECT title, CASE WHEN score >= 8 THEN 'top' WHEN score >= 7 THEN 'good' ELSE 'so-so' END AS verdict FROM films

CASE picks a value per row, like an if-then. ELSE catches the rest, NULL included.

SELECT title FROM films WHERE score > (SELECT AVG(score) FROM films)

A subquery between brackets gives one value to compare with.

SELECT name FROM members WHERE id IN (SELECT member_id FROM rentals WHERE fine > 0)

A subquery with IN gives a list. Here the members who ever got a fine.

WITH popular AS (SELECT film_id, COUNT(*) AS times FROM rentals GROUP BY film_id) SELECT f.title, p.times FROM popular p JOIN films f ON p.film_id = f.id

WITH (a CTE) gives an intermediate result a name, so your query stays readable.

SELECT name FROM members WHERE town = 'Gent' UNION SELECT name FROM members WHERE plan = 'premium'

UNION stacks results and removes duplicates. UNION ALL keeps them.

Text, dates and NULL

SELECT UPPER(title), LOWER(genre), LENGTH(title) FROM films

Upper case, lower case and the number of characters.

SELECT name || ' (' || town || ')' AS member FROM members

|| glues text together.

SELECT SUBSTR(title, 1, 3), REPLACE(title, ' ', '_'), TRIM('  yes  ') FROM films

Take a piece of text, replace something and trim spaces at the edges.

SELECT title FROM films WHERE LOWER(genre) = 'horror'

Compare without minding upper and lower case.

SELECT rented_on, strftime('%Y', rented_on) AS year, date(rented_on, '+7 days') AS due FROM rentals

strftime takes part of a date, date(..., '+7 days') calculates with it. date('now') is today.

SELECT julianday(returned_on) - julianday(rented_on) AS days FROM rentals

The difference between two dates, in days.

SELECT name, COALESCE(email, 'no e-mail') FROM members

COALESCE gives the first value that isn't NULL: handy as a fallback.

Window functions

Calculating over several rows, without merging them into one row as GROUP BY does.

SELECT title, score, ROW_NUMBER() OVER (ORDER BY score DESC) AS place FROM films

ROW_NUMBER() numbers the rows in the order of OVER (ORDER BY ...).

SELECT title, genre, score, RANK() OVER (PARTITION BY genre ORDER BY score DESC) AS place FROM films

PARTITION BY starts again for every genre. RANK() gives equal scores the same place.

SELECT rented_on, fine, SUM(fine) OVER (ORDER BY rented_on) AS total FROM rentals

A running total, added up row by row.

Changing data

In Lurndo you practise without risk. After every query the database resets itself.

INSERT INTO members (name, town, birth_year, member_since, plan) VALUES ('Ann Peeters', 'Lier', 1985, '2025-10-01', 'basic')

A new row. Columns you don't name become NULL (or their default value).

UPDATE films SET price_per_day = price_per_day + 0.5 WHERE year >= 2020

Change existing rows. Without `WHERE` you change every row.

DELETE FROM rentals WHERE returned_on IS NOT NULL AND fine = 0

Delete rows. Without `WHERE` the whole table is empty.

BEGIN; UPDATE members SET plan = 'premium' WHERE id = 1; COMMIT;

A transaction: all or nothing. COMMIT saves, ROLLBACK undoes everything.

Creating tables

CREATE TABLE reservations (id INTEGER PRIMARY KEY, film_id INTEGER NOT NULL REFERENCES films (id), name TEXT NOT NULL, reserved_on TEXT DEFAULT (date('now')))

Types in SQLite: INTEGER, REAL, TEXT, BLOB. PRIMARY KEY, NOT NULL, DEFAULT and REFERENCES (foreign key) guard your data.

ALTER TABLE members ADD COLUMN phone TEXT

Add a column to an existing table.

DROP TABLE rentals

Delete a table with all its rows. That is final.

CREATE INDEX idx_films_genre ON films (genre)

An index makes searching on that column faster, at the cost of some storage.

CREATE VIEW recent_films AS SELECT title, year FROM films WHERE year >= 2020; SELECT * FROM recent_films

A view is a saved query you use like a table.

The order of a query

SELECT genre, COUNT(*) AS films FROM films WHERE year >= 2000 GROUP BY genre HAVING COUNT(*) > 1 ORDER BY films DESC LIMIT 3

This is how you write it: SELECT, FROM, JOIN, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT. The database works in a different order: first FROM and JOIN, then WHERE, GROUP BY, HAVING, then SELECT, and only at the end ORDER BY and LIMIT. That is why an alias works in ORDER BY, but not in WHERE.