SQL cheat sheet

Clause order

You write the clauses in this order, but SQL runs them in another one: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. That is why groups are filtered with HAVING: at WHERE time, the groups do not exist yet.

Syntax by topic

SELECT col1, col2 AS alias
FROM my_table;

WHERE prix > 10 AND ville IN ('Lyon', 'Paris')
WHERE nom LIKE 'B%'     -- starts with B
WHERE age IS NULL

ORDER BY prix DESC, nom
LIMIT 10 OFFSET 20

SELECT DISTINCT ville FROM clients;
SELECT COUNT(DISTINCT ville) FROM clients;

SELECT COUNT(*), SUM(x), AVG(x), MIN(x), MAX(x)
FROM my_table;

SELECT ville, COUNT(*) AS nb
FROM clients
GROUP BY ville
HAVING COUNT(*) > 5;

SELECT o.id, c.nom
FROM commandes o
JOIN clients c ON c.id = o.client_id;

SELECT c.nom
FROM clients c
LEFT JOIN commandes o ON o.client_id = c.id
WHERE o.id IS NULL;   -- customers without orders

WHERE prix > (SELECT AVG(prix) FROM produits)
WHERE id IN (SELECT client_id FROM commandes)
WHERE NOT EXISTS (SELECT 1 FROM … WHERE …)

WITH step AS (
  SELECT client_id, COUNT(*) AS nb
  FROM commandes GROUP BY client_id
)
SELECT * FROM step WHERE nb > 5;

RANK() OVER (PARTITION BY categorie ORDER BY prix DESC)
ROW_NUMBER() OVER (ORDER BY date_commande)
SUM(revenue) OVER (ORDER BY month)   -- running total

CASE
  WHEN prix >= 100 THEN 'premium'
  ELSE 'standard'
END AS price_range

INSERT INTO t (a, b) VALUES (1, 'x');
UPDATE t SET a = a + 1 WHERE id = 3;
DELETE FROM t WHERE id = 3;

CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT);
ALTER TABLE t ADD COLUMN age INTEGER;
CREATE VIEW v AS SELECT * FROM t;

ROUND(x, 2)    LENGTH(s)    UPPER(s)    LOWER(s)
COALESCE(x, 0) a || b       substr(s, 1, 3)
strftime('%Y', date)        date('now')

Missing values (NULL)

Never compare with = NULL: write IS NULL or IS NOT NULL. The SUM, AVG, MIN, MAX and COUNT(column) functions ignore NULLs, whereas COUNT(*) counts every row. See the lesson on NULL.