Hone

Lessons · SQL · Quick reference

SQL quick reference

120 topics, one line each, in the order Hone teaches them.

Hone is a place to practise programming. This sheet is the whole SQL track at a glance: every idea it covers, in the order they are taught, one line each. It is a map rather than a lesson. Read opens the full explanation of an idea; Practise gives you a question on it. Both are free, and reading needs no account at all.

Queries that hold up · It ran, and it lied

a WHERE that undoes a LEFT JOINA condition on the right table in WHERE turns a LEFT JOIN into an inner join, because unmatched rows have NULL there and fail the test. Put that condition in the ON clause to keep the left rows. Read: Where the filter goes in a LEFT JOIN · Practise a WHERE that undoes a LEFT JOIN
adding up only some rowsSUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) adds only the paid rows. Put several such sums in one SELECT and a status becomes columns. Read: Two totals from one pass · Practise adding up only some rows
date rangesDates stored as ISO text sort correctly, but subtracting them needs a number: julianday(date) gives days as a number, so the difference of two is a number of days. Read: Arithmetic on dates · Practise date ranges
BETWEEN is inclusiveBETWEEN a AND b means a <= x AND x <= b, both ends included. Read: A range, inclusive · Practise BETWEEN is inclusive
AND before ORa AND b OR c means (a AND b) OR c. Use parentheses to say what you mean. Read: AND binds tighter than OR · Practise AND before OR

Queries that hold up · Filter at the right moment

HAVING vs WHEREWHERE runs before grouping on individual rows; HAVING runs after on the aggregated groups. Read: Filter rows with WHERE, groups with HAVING · Practise HAVING vs WHERE
grouped SELECTsAfter GROUP BY, each output row is one group, so SELECT may hold only the grouped columns and aggregates over the group. Read: What SELECT may contain after GROUP BY · Practise grouped SELECTs
ORDER BY two columnsORDER BY a DESC, b ASC sorts by a descending and breaks ties by b ascending. Read: Sorting by more than one thing · Practise ORDER BY two columns
LIMITLIMIT n returns at most n rows; ORDER BY decides which n. Read: Just some of the rows · Practise LIMIT

Queries that hold up · A question inside a question

subqueriesA SELECT in parentheses can stand in for a value, a list, or a table. Read: A query inside a query · Practise subqueries
comparing against one computed valueA subquery that returns exactly one row and one column can be used anywhere a value can: in a comparison, a SELECT list, a WHERE. Read: A query that returns one value · Practise comparing against one computed value
is there at least oneEXISTS (subquery) is true when the subquery returns at least one row. NOT EXISTS is the robust way to ask 'has no matching row', with any condition you like inside. Read: Is there such a row · Practise is there at least one

Queries that hold up · One row per thing, and what it costs

the top row per groupMAX gives the value, not its row. ROW_NUMBER() OVER (PARTITION BY group ORDER BY value DESC) numbers each group's rows from the biggest; keep the rows numbered 1. Read: The whole row that holds the maximum · Practise the top row per group
a subquery that runs per rowA correlated subquery references the outer row (o.customer_id), so it is evaluated for each outer row. It answers per-group questions, and it costs a query per row. Read: A subquery that runs per row · Practise a subquery that runs per row
why DISTINCT is slowSELECT DISTINCT col returns each value once. It has to sort or hash the whole result to do it. Read: DISTINCT collapses duplicates · Practise why DISTINCT is slow

Queries that hold up · Changing data without regret

UPDATE needs WHEREUPDATE table SET col = value WHERE condition changes matching rows. Without WHERE, every row. Read: UPDATE needs a WHERE · Practise UPDATE needs WHERE
DELETE needs WHEREDELETE FROM table removes rows matching WHERE. Without WHERE it removes every row. Read: DELETE needs a WHERE · Practise DELETE needs WHERE
transactionsRANK() OVER (ORDER BY x DESC) numbers rows by x without collapsing them; ties share a rank. Read: Ranking with window functions · Practise transactions
INSERTINSERT INTO table (columns) VALUES (values), in the same order. Read: Adding a row · Practise INSERT

SQL for the workplace · Ask a question

SELECT basicsSELECT names the columns you want, FROM names the table. That is a complete question. Read: Asking a table for columns · Practise SELECT basics
WHEREWHERE filters rows before anything else happens. Only rows where the condition is true survive. Read: Keeping only the rows you mean · Practise WHERE
AND narrows the rowsWHERE a AND b keeps only rows where both are true. A row that fails either one, or where either is NULL, is out. Read: Both conditions at once · Practise AND narrows the rows
the top one, by orderORDER BY sorts the result; LIMIT keeps the first N rows of that order. Together they answer 'the biggest', 'the latest', 'the top five'. Read: The top of a sorted list · Practise the top one, by order
one row per valueSELECT DISTINCT col returns each different value once, however many rows carry it. Read: Each value once · Practise one row per value
LIKELIKE 'A%' matches text starting with A; % is any run of characters, _ is exactly one. Read: Pattern matching on text · Practise LIKE

SQL for the workplace · Count and sum

COUNT, SUM, AVGCOUNT, SUM, AVG, MIN, MAX collapse many rows into one value. Read: One number from many rows · Practise COUNT, SUM, AVG
counting rowsCOUNT(*) counts rows. Without GROUP BY it always returns exactly one row, even when the answer is 0. Read: How many rows · Practise counting rows
adding a column upSUM(amount) adds the values in a column across the rows the query keeps. NULLs are skipped; if no rows remain, the result is NULL, not 0. Read: Adding a column up · Practise adding a column up
the largest valueMAX(col) returns the largest value in the column; MIN the smallest. Both ignore NULLs and return one row. Read: The largest value · Practise the largest value
GROUP BYGROUP BY splits rows into groups by a column's value; aggregates then run once per group. Read: One number per group · Practise GROUP BY
GROUP BY an expressionYou can GROUP BY an expression, not only a column: the month of a date, the first letter of a name. Read: Bucketing by a computed value · Practise GROUP BY an expression
filtering the groups, not the rowsWHERE filters rows before grouping; HAVING filters groups after, by their aggregate. HAVING COUNT(*) >= 2 keeps the groups with at least two rows. Read: Filtering groups · Practise filtering the groups, not the rows
AVG skips the NULLsAVG divides the sum by the count of non-NULL values, not by the number of rows. A missing rating is left out entirely, not counted as zero. Read: Averages skip the blanks · Practise AVG skips the NULLs

SQL for the workplace · Join the tables

JOINJOIN combines rows from two tables where a condition holds, usually one table's foreign key equals the other's id. Read: Rows from two tables, matched up · Practise JOIN
INNER JOINAn inner JOIN returns rows where the ON condition matches on both sides; unmatched rows vanish. Read: Only the matches · Practise INNER JOIN
keeping the rows with no matchLEFT JOIN keeps every row of the left table; where the right table has no match, its columns come back NULL. Read: Keep every row on the left · Practise keeping the rows with no match
LEFT JOIN and NULLLEFT JOIN keeps every row from the left table; where the right has no match, its columns are NULL. WHERE right.id IS NULL then finds the unmatched. Read: Keep everyone, spot the gaps · Practise LEFT JOIN and NULL
when a join multiplies rowsJoining one order to its three items gives three rows, each carrying the order's amount. Summing that column afterwards counts the order three times. Read: When a join multiplies rows · Practise when a join multiplies rows
an inner join loses rowsINNER JOIN keeps only rows with a partner on both sides. An order whose customer_id matches no customer is not in the result, and nothing says so. Read: An inner join drops the unmatched · Practise an inner join loses rows

SQL for the workplace · Nothing is something

NoneNULL is not a value; it is the absence of one. Any comparison with NULL is unknown, not true, so WHERE col = NULL matches nothing. Test it with IS NULL. Read: NULL means unknown · Practise None
testing for NULLIS NULL finds rows where a column has no value; IS NOT NULL finds the rest. They are the only operators that can see NULL. Read: Finding the blanks · Practise testing for NULL
COALESCECOALESCE(a, b) returns a unless it is NULL, then b. Read: A value instead of NULL · Practise COALESCE
comparing with NULLNULL means unknown, and unknown compared to anything is unknown, including to itself. A WHERE keeps only rows whose test is true, so an unknown result drops the row. Read: Why = NULL finds nothing · Practise comparing with NULL
NOT IN meets a NULLx NOT IN (list) is false if x is in the list and unknown if the list contains a NULL, so one NULL in the subquery makes every row disappear. Read: NOT IN and a single NULL · Practise NOT IN meets a NULL
COUNT and NULLCOUNT(*) counts rows; COUNT(col) counts rows where col is not NULL. Read: COUNT(*) versus COUNT(column) · Practise COUNT and NULL

SQL for the workplace · The real reports

naming a query with WITHWITH name AS (query) gives a query a name for the rest of the statement. Nothing is stored; the name exists only inside that statement. Read: Naming a step · Practise naming a query with WITH
sorting rows into bucketsCASE WHEN amount >= 200 THEN 'large' ELSE 'small' END turns a number into a label inside the query, so you can group and count by the label. Read: Turning values into labels · Practise sorting rows into buckets
grouping by month across yearsThe month number alone is not a period: strftime('%m') puts last March and this March in one bucket. Use '%Y-%m' so each calendar month is its own group. Read: Grouping by calendar month · Practise grouping by month across years
the most recent one eachROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY placed DESC) numbers each customer's orders from newest; keep the rows numbered 1. Read: The latest row per group · Practise the most recent one each
a window keeps every rowA window function computes an aggregate over a set of rows but keeps every row: each order can show its customer's total beside its own amount. Read: A total beside every row · Practise a window keeps every row
a total that grows down the rowsSUM(amount) OVER (ORDER BY placed) adds up every row up to and including the current one. The ORDER BY inside the window is what makes it accumulate. Read: A running total · Practise a total that grows down the rows

More

DISTINCTQuestions on Hone; no lesson yet. Practise DISTINCT
an index is for finding rowsQuestions on Hone; no lesson yet. Practise an index is for finding rows
ORDER BYQuestions on Hone; no lesson yet. Practise ORDER BY
aliases (AS)AS gives a column or table a name for this query; the data is unchanged, the label is yours. Read: Naming a column or table · Practise aliases (AS)
CASE WHENQuestions on Hone; no lesson yet. Practise CASE WHEN
|| joins textQuestions on Hone; no lesson yet. Practise || joins text
COUNT(column)Questions on Hone; no lesson yet. Practise COUNT(column)
COUNT of a column with NULLsQuestions on Hone; no lesson yet. Practise COUNT of a column with NULLs
COUNT(DISTINCT ...)Questions on Hone; no lesson yet. Practise COUNT(DISTINCT ...)
counting the rows that matchQuestions on Hone; no lesson yet. Practise counting the rows that match
COUNT(*)Questions on Hone; no lesson yet. Practise COUNT(*)
accidental cross joinsQuestions on Hone; no lesson yet. Practise accidental cross joins
one query as three stepsQuestions on Hone; no lesson yet. Practise one query as three steps
a month with no rows at allQuestions on Hone; no lesson yet. Practise a month with no rows at all
dates as text that still sortQuestions on Hone; no lesson yet. Practise dates as text that still sort
deleting duplicates, keeping oneQuestions on Hone; no lesson yet. Practise deleting duplicates, keeping one
which duplicate should surviveQuestions on Hone; no lesson yet. Practise which duplicate should survive
DELETEQuestions on Hone; no lesson yet. Practise DELETE
what a DELETE leaves behindQuestions on Hone; no lesson yet. Practise what a DELETE leaves behind
RANK leaves a gapQuestions on Hone; no lesson yet. Practise RANK leaves a gap
counting the distinct valuesQuestions on Hone; no lesson yet. Practise counting the distinct values
why WHERE cannot see COUNTQuestions on Hone; no lesson yet. Practise why WHERE cannot see COUNT
EXPLAINQuestions on Hone; no lesson yet. Practise EXPLAIN
finding the duplicatesQuestions on Hone; no lesson yet. Practise finding the duplicates
foreign keysQuestions on Hone; no lesson yet. Practise foreign keys
one row per whatQuestions on Hone; no lesson yet. Practise one row per what
group_concatQuestions on Hone; no lesson yet. Practise group_concat
grouping by two columnsQuestions on Hone; no lesson yet. Practise grouping by two columns
HAVING filters groupsQuestions on Hone; no lesson yet. Practise HAVING filters groups
IN a list of valuesQuestions on Hone; no lesson yet. Practise IN a list of values
counting with INQuestions on Hone; no lesson yet. Practise counting with IN
IN as shorthand for ORQuestions on Hone; no lesson yet. Practise IN as shorthand for OR
indexes cost writesQuestions on Hone; no lesson yet. Practise indexes cost writes
a function on a column loses the indexQuestions on Hone; no lesson yet. Practise a function on a column loses the index
SQL injectionQuestions on Hone; no lesson yet. Practise SQL injection
the last thirty daysQuestions on Hone; no lesson yet. Practise the last thirty days
COUNT(*) counts the empty matchQuestions on Hone; no lesson yet. Practise COUNT(*) counts the empty match
LIKE with a wildcardQuestions on Hone; no lesson yet. Practise LIKE with a wildcard
LIMIT is a ceilingQuestions on Hone; no lesson yet. Practise LIMIT is a ceiling
LIMIT with OFFSETQuestions on Hone; no lesson yet. Practise LIMIT with OFFSET
two writers, one lostQuestions on Hone; no lesson yet. Practise two writers, one lost
MINQuestions on Hone; no lesson yet. Practise MIN
last month on the same rowQuestions on Hone; no lesson yet. Practise last month on the same row
normalisationQuestions on Hone; no lesson yet. Practise normalisation
<> not equalQuestions on Hone; no lesson yet. Practise <> not equal
= NULL matches nothingQuestions on Hone; no lesson yet. Practise = NULL matches nothing
ORDER BY DESCQuestions on Hone; no lesson yet. Practise ORDER BY DESC
when two bookings overlapQuestions on Hone; no lesson yet. Practise when two bookings overlap
rows into columnsQuestions on Hone; no lesson yet. Practise rows into columns
primary keysQuestions on Hone; no lesson yet. Practise primary keys
RANK, DENSE_RANK and ROW_NUMBERQuestions on Hone; no lesson yet. Practise RANK, DENSE_RANK and ROW_NUMBER
walking up a treeQuestions on Hone; no lesson yet. Practise walking up a tree
a recursive CTE that countsQuestions on Hone; no lesson yet. Practise a recursive CTE that counts
ROUNDQuestions on Hone; no lesson yet. Practise ROUND
SELECT *Questions on Hone; no lesson yet. Practise SELECT *
LENGTH, UPPER and SUBSTRQuestions on Hone; no lesson yet. Practise LENGTH, UPPER and SUBSTR
the row with the largest valueQuestions on Hone; no lesson yet. Practise the row with the largest value
SUM of no rows is NULLQuestions on Hone; no lesson yet. Practise SUM of no rows is NULL
SUM skips the NULLsQuestions on Hone; no lesson yet. Practise SUM skips the NULLs
UNION removes duplicatesQuestions on Hone; no lesson yet. Practise UNION removes duplicates
UNION ALL keeps everythingQuestions on Hone; no lesson yet. Practise UNION ALL keeps everything
UPDATEQuestions on Hone; no lesson yet. Practise UPDATE
what an UPDATE changedQuestions on Hone; no lesson yet. Practise what an UPDATE changed
upsertQuestions on Hone; no lesson yet. Practise upsert
counting with a WHEREQuestions on Hone; no lesson yet. Practise counting with a WHERE
WHERE runs before the groupingQuestions on Hone; no lesson yet. Practise WHERE runs before the grouping
which rows a window coversQuestions on Hone; no lesson yet. Practise which rows a window covers
why a window cannot go in WHEREQuestions on Hone; no lesson yet. Practise why a window cannot go in WHERE
all of it, or none of itQuestions on Hone; no lesson yet. Practise all of it, or none of it