SQL 101: the formulas

The sentence

SELECT column_a, column_b        -- which columns
FROM table_name                  -- which table
WHERE condition                  -- which rows
GROUP BY column_a                -- one row per value
HAVING COUNT(*) > 1              -- filter groups
ORDER BY column_b DESC           -- sort
LIMIT 10;                        -- keep n rows

Clauses are optional but their order is fixed. Text goes in single quotes; numbers don't. ; ends the statement.

Filters

Want	Write
both	a = 1 AND b = 2
either	a = 1 OR b = 2 (use parentheses with AND)
any of a list	stage IN ('closed_won', 'closed_lost')
a range	amount BETWEEN 1000 AND 5000
not equal	stage <> 'closed_lost'
missing	closed_date IS NULL (never = NULL)
pattern	email LIKE '%@northwind%'

Aggregates

COUNT(*), COUNT(col) (non-NULL only), SUM, AVG, MIN, MAX. Name the result: SUM(amount) AS total. In SQLite, SUM(stage = 'closed_won') counts rows where the comparison is true, and 1.0 * a / b forces decimal division.

GROUP BY / HAVING

Every SELECT column must be grouped or aggregated. WHERE filters rows before grouping; HAVING filters groups after. The duplicate idiom: GROUP BY key HAVING COUNT(*) > 1.

Joins

FROM deals d
JOIN accounts a ON a.account_id = d.account_id        -- only matches
LEFT JOIN transactions t ON t.deal_id = d.deal_id     -- all deals, NULL if none

Qualify columns after a join (d.amount). Conditions on the right-hand table of a LEFT JOIN belong in ON, not WHERE.

Dates and text (SQLite)

substr(created_date, 1, 7) → month; strftime('%Y-%W', ts) → week; julianday(b) - julianday(a) → days between.