Phase 1 of 8 · 8 lessons
Reading data: SELECT, WHERE and sorting
Pick the columns you want, keep only the rows you want, sort them, and handle the missing values that trip everyone up.
Most queries you will ever write are a SELECT with a WHERE and an ORDER BY. Get these, plus NULL, completely solid, and the rest of SQL is built on top of them.
Lessons, in order
-
1.1
SELECT and FROM: choosing columns
SELECT lists the columns (or calculations) you want in the result, and FROM names the table they come from.
-
1.2
WHERE: keeping only the rows you want
WHERE keeps only the rows for which a condition is true, using comparisons such as =, <>, <, > and the words AND, OR and NOT.
-
1.3
IN, BETWEEN and LIKE: shorter conditions
IN checks a value against a list, BETWEEN checks a range (both ends included), and LIKE matches text against a pattern with % (any number of characters) and _ (exactly one).
-
1.4
NULL: the missing value
NULL means "no value" or "unknown"; any comparison with it — even
NULL = NULL— is unknown rather than true, so you test for it with IS NULL and replace it with COALESCE. -
1.5
ORDER BY, LIMIT and OFFSET: sorting and top N
ORDER BY sorts the result by one or more columns (ASC for smallest first, DESC for largest first), and LIMIT keeps only the first n rows, with OFFSET to skip some first.
-
1.6
DISTINCT: removing duplicate rows
SELECT DISTINCT removes duplicate rows from the result, where two rows are duplicates only if every selected column is the same.
-
1.7
CASE WHEN: if-then logic inside a query
CASE WHEN condition THEN value … ELSE value END returns a value chosen by the first condition that is true, so a query can label, group or tidy values row by row.
-
1.8
Built-in functions for text, numbers and dates
Functions such as UPPER, LENGTH, SUBSTR, ROUND and the date functions transform a value inside a query — one value in, one value out, for each row.