SQLSQL cheatsheet

SELECT title, year FROM songs;

Pick columns from a table, in the order you list them.

SELECT * FROM songs;

Every column. Good for a first look, messy in a finished query.

SELECT title, seconds / 60.0 AS minutes FROM songs;

Work out a new column and name it with AS.

SELECT DISTINCT genre FROM artists;

Each different value once.

-- a comment

SQL ignores everything after two dashes on a line.

WHERE country = 'UK' AND age >= 16

Text in single quotes. AND needs both tests true, OR needs one.

WHERE (genre = 'pop' OR genre = 'indie') AND year > 2022

AND runs before OR, so add brackets when you mix them.

WHERE title LIKE '%rain%'

% is any run of characters, _ is exactly one.

WHERE country IN ('UK', 'IE', 'US')

Matches any value in the list.

WHERE year BETWEEN 2020 AND 2022

Includes both ends.

ORDER BY score DESC, gamertag

Biggest first, then alphabetical to break ties.

LIMIT 5

Only the first 5 rows. Use it after ORDER BY for a top 5.

LIMIT 5 OFFSET 10

Skip 10 rows, then take 5: page 3 of a list.

WHERE critic_score IS NULL

The only way to find missing values. = NULL never matches.

WHERE note IS NULL OR note != 'gift'

!= skips NULL rows too, so ask for them on purpose.

COALESCE(score, 0)

The first value that is not NULL: a fallback for display.

COUNT(*), COUNT(score)

All rows, and only rows where score is not NULL.

SELECT COUNT(*), AVG(price), MAX(price) FROM products;

Aggregates squash all rows into one.

SELECT category, COUNT(*) FROM products GROUP BY category;

One row per category. Every other column must be grouped or aggregated.

GROUP BY customer_id HAVING COUNT(*) >= 3

HAVING filters groups, after GROUP BY. WHERE filters rows, before it.

COUNT(DISTINCT listener_id)

Count different people, not plays.

SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END)

Count only the rows that match a condition.

FROM songs AS s
JOIN artists AS a ON a.id = s.artist_id

Keep the pairs where the keys match. Short aliases save typing.

FROM players AS p
LEFT JOIN sessions AS s ON s.player_id = p.id

Every player, with NULLs where they have no sessions.

LEFT JOIN sessions AS s ON s.player_id = p.id
WHERE s.id IS NULL

Rows with no partner at all: players who never played.

SELECT c.name, p.name

Prefix a column with its table when both tables have one with that name.

first_name || ' ' || last_name

Glue text together.

UPPER(name), LOWER(name), LENGTH(name)

Change case, count characters.

substr(title, 1, 10)

Part of a string: 10 characters from position 1.

strftime('%Y-%m', ordered_on), strftime('%H', sold_at)

Pull the month or the hour out of a date or time. Dates sort right as 'YYYY-MM-DD' text.

date('now'), date(joined_on, '+30 days')

Today's date, and date maths.