Pure SQL Machine Learning Lab · kNN, Naive Bayes and more

SIX LIVE FAMILIES

Different algorithms, the same relational vocabulary.

Press Run SQL to execute each complete model pipeline on the server. Results and the exact SQL are returned; source rows stay inside the engine.

k-Nearest Neighbors

DISTANCE · TOP-K · VOTE

Encode rows, compute Euclidean distance, rank five neighbors and vote with GROUP BY.

10,000-row bounded runtime

EXECUTED SQL

Select Run pure SQL.

—

Naive Bayes

PRIOR · LIKELIHOOD · LOG SCORE

Estimate priors and likelihoods with aggregates, apply Laplace smoothing and sum log probabilities.

10,000-row bounded runtime

EXECUTED SQL

Select Run pure SQL.

—

Linear Regression

COVARIANCE · SLOPE · INTERCEPT

Fit slope, intercept and R² from SQL aggregate statistics and predict in the same statement.

10,000-row bounded runtime

EXECUTED SQL

Select Run pure SQL.

—

k-means

ASSIGN · CENTROID · REPEAT

Use joins for point-centroid distance, a window rank for assignment and GROUP BY for new centroids.

10,000-row bounded runtime

EXECUTED SQL

Select Run pure SQL.

—

Association Rules

SUPPORT · CONFIDENCE · LIFT

Self-join items within baskets and derive support, confidence and lift from grouped counts.

10,000-row bounded runtime

EXECUTED SQL

Select Run pure SQL.

—

Statistical Anomaly

MEAN · STANDARD DEVIATION · Z-SCORE

Learn mean and standard deviation, score every observation and rank unusual rows.

10,000-row bounded runtime

EXECUTED SQL

Select Run pure SQL.

—

EMBEDDED EXCEL WORKSHEET

The original worksheet, translated into a web-readable model.

The 2022 workbook teaches the same dataset through probability and distance. Tabs below preserve that learning order; download the Excel file to inspect and modify the formulas.

Play Golf · 14 training examples

No.	Outlook	Temperature	Humidity	Windy	Play
1	sunny	hot	high	false	no
2	sunny	hot	high	true	no
3	overcast	hot	high	false	yes
4	rainy	mild	high	false	yes
5	rainy	cool	normal	false	yes
6	rainy	cool	normal	true	no
7	overcast	cool	normal	true	yes
8	sunny	mild	high	false	no
9	sunny	mild	normal	false	yes
10	rainy	mild	normal	false	yes
11	sunny	mild	normal	true	yes
12	overcast	mild	high	true	yes
13	overcast	hot	normal	false	yes
14	rainy	mild	high	true	no

Naive Bayes · rainy, cool, high, true

Class	Prior	P(rainy\|class)	P(cool\|class)	P(high\|class)	P(windy=true\|class)	Original product
yes	9/14	3/9	2/9	3/9	3/9	0.00823
no	5/14	2/5	1/5	4/5	3/5	0.03840

Workbook view

Visible multiplication makes conditional evidence easy to follow.

Web SQL upgrade

Adds class prior, Laplace smoothing and log scores so unseen values and underflow are handled explicitly.

kNN · sunny, 0.82, 0.83, not windy · k=5

Neighbor	Distance	Class
1	0.0361	no
8	0.1562	no
9	0.1838	yes
2	1.0026	no
11	1.0108	yes

Vote: no = 3, yes = 2 → prediction = no

Download original Excel workbook ↓Download original SQL lesson ↓Open full worksheet preview ↗

HOW SQL BECOMES ML

The model is stored logic, learned state—or both.

Pure SQL does not mean every algorithm is equally elegant or scalable. It means the complete transformation from training rows to decision is expressed and executed inside the data engine.

TABLEtraining rows

CTE / VIEWfeature contract

AGGREGATElearned statistics

WINDOW / JOINcompare and rank

RELATIONprediction + evidence

Where pure SQL is strong—and where honesty matters

Excellent for transparent baselines, small-to-medium tabular problems, governed data and teaching algorithm mechanics.
kNN is expensive at scoring time: a naive query compares each query row with every training row.
Iterative methods need bounded recursion, procedures or explicitly unrolled steps; convergence must be measured.
Deep neural networks, GPU training and complex automatic differentiation are not sensible targets for hand-written SQL.

RELATED LESSONS

Use pure SQL to understand; use platforms to operate.

Continue to Declarative ML for the wider ecosystem, MADlib for mature in-database algorithms, and DuckDB × ClickHouse for execution-boundary comparison.

NEXT PURE SQL LAB

Turn relationships into ranked recommendations.

Continue with seven SQL-only recommender methods: popularity, graph co-occurrence, collaborative filtering, content, time decay and hybrid ranking.