Formulas & shortcuts
SQL12 formulas17 shortcuts
Every formula in this course and the easiest ways to remember them, on one page.
Changing data and designing databases
AdvancedUpsert and RETURNING
Open lessonChanging a schema: ALTER TABLE and migrations
Open lessonAdvanced SQL
AdvancedWindow functions
Open lessonCTEs: WITH and recursive queries
Open lessonCASE and conditional logic
Open lessonString, date and math functions
Open lessonSet operations and advanced joins
Open lessonProgramming inside the database
AdvancedTriggers
Open lessonJSON in SQL: SQLite and PostgreSQL
Open lessonSequences: gaps and islands
Open lessonWhere
- value
- the value in the sequence (an id, a day number)
- ROW_NUMBER()
- the number of the sorted row: 1, 2, 3, …
- grp
- the island key: the same within one island
Then GROUP BY grp collapses each island into one row: MIN is the start, MAX the end, COUNT the length.
Databases at a professional level
UniversityDatabase design and normalisation
Open lessonWhere
- X
- the determinant — a set of attributes
- Y
- the set of dependent attributes
- r
- any valid state of the table (relation)
- t₁, t₂
- any two rows of the table; t[X] — the row's values in the columns X
Two rows that agree on X must also agree on Y.
Where
- R₁, R₂
- the attribute sets of the two tables produced by the decomposition (R₁ ∪ R₂ = R)
- R₁ ∩ R₂
- the common attributes — the columns the
JOINis done on
If the condition holds, the decomposition is lossless: R = R₁ ⋈ R₂.
Transactions and concurrency
Open lessonWhere
- s₀
- the initial stock that both transactions read
- q₁, q₂
- the quantities sold by T1 and T2
- s
- the result saved by the last writer (T2)
A lost update: in the “read → compute in the application → write” pattern, T1's sale drops out of the total.
Query optimisation and indexes
Open lessonWhere
- h
- the height of the tree — the number of pages read to find one key
- N
- the number of keys (rows) in the index
- f
- the fan-out — how many keys fit in one page (usually hundreds)
The height grows logarithmically with the number of rows: when the table grows 500 times, the tree gains just one level.
Where
- s
- the selectivity of the condition (between 0 and 1)
- n
- the number of rows matching the condition
- N
- all rows in the table
A small s (a condition that returns few rows) is good for an index; when s is large, a full scan may be cheaper.
PostgreSQL and MySQL in practice
Open lessonData analysis with SQL
Open lessonWhere
- AOV
- average order value, manat
- R
- revenue for the period: Σ quantity · price, manat
- Nₒ
- the number of orders in that period
Where
- r
- the cohort's retention rate
- C₀
- the cohort size — customers who arrived in the first period
- A
- customers who ordered again in a later period
Where
- cᵢ
- conversion up to stage i
- nᵢ
- customers who reached stage i
- n₁
- the first stage of the funnel
Step conversion is nᵢ ÷ nᵢ₋₁ · 100%; it answers the question “where do we lose the most?”.
Where
- k
- the position of the percentile in the sorted list (starting from 1)
- p
- the percentile, e.g. 90
- n
- the number of values
The nearest-rank method: the p-th percentile is the smallest value that covers at least p% of the values.
Beyond one database: warehouses and scaling
UniversityData warehouses: star schema, facts and dimensions
Open lessonWhere
- Σ revenue
- the sum of revenue over the selected rows
- Σ quantity
- the sum of units sold in those rows
The average price is weighted; AVG(unit_price) gives every row the same weight and is wrong.
Where
- year
- the year, e.g. 2025
- month
- the month, 1–12
- day
- the day of the month, 1–31
Such a key sorts like a date and can be read by eye.