Skip to content
Educora
EN
AzərbaycancaEnglishРусскийTürkçe
My learningEducora StudioSettings

Formulas & shortcuts

SQL · 20

Every formula in this course and the easiest ways to remember them, on one page.

1. Advanced SQL2. Databases at a professional level

1Advanced SQLAdvanced

Window functions

Open lesson

CTEs: WITH and recursive queries

Open lesson

CASE and conditional logic

Open lesson

String, date and math functions

Open lesson

Set operations and advanced joins

Open lesson

2Databases at a professional levelUniversity

Database design and normalisation

Open lesson
X → Y ⇔ ∀ t₁, t₂ ∈ r : t₁[X] = t₂[X] ⇒ t₁[Y] = t₂[Y]
where:
  • Xthe determinant — a set of attributes
  • Ythe set of dependent attributes
  • rany 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.

(R₁ ∩ R₂) → R₁ ∨ (R₁ ∩ R₂) → R₂
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 JOIN is done on

If the condition holds, the decomposition is lossless: R = R₁ ⋈ R₂.

Transactions and concurrency

Open lesson
s = s₀ − q₂ ≠ s₀ − q₁ − q₂
where:
  • s₀the initial stock that both transactions read
  • q₁, q₂the quantities sold by T1 and T2
  • sthe 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 lesson
h = ⌈ log N ÷ log f ⌉
where:
  • hthe height of the tree — the number of pages read to find one key
  • Nthe number of keys (rows) in the index
  • fthe 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.

s = n ÷ N
where:
  • sthe selectivity of the condition (between 0 and 1)
  • nthe number of rows matching the condition
  • Nall 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 lesson

Data analysis with SQL

Open lesson
AOV = R ÷ Nₒ
where:
  • AOVaverage order value, manat
  • Rrevenue for the period: Σ quantity · price, manat
  • Nₒthe number of orders in that period
r = A ÷ C₀ · 100%
where:
  • rthe cohort's retention rate
  • C₀the cohort size — customers who arrived in the first period
  • Acustomers who ordered again in a later period
cᵢ = nᵢ ÷ n₁ · 100%
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?”.

k = ⌈ p ÷ 100 · n ⌉
where:
  • kthe position of the percentile in the sorted list (starting from 1)
  • pthe percentile, e.g. 90
  • nthe number of values

The nearest-rank method: the p-th percentile is the smallest value that covers at least p% of the values.