Skip to content
SQL
-
SQL
SQL Window Functions and CTEs for Real Reports
ROW_NUMBER, RANK, LAG, running totals, top-N per group, recursive CTEs and JSON functions in SQLite, with frame and NULL traps and measured speed.
-
SQL
SQL Transactions, WAL and 'database is locked'
Real BEGIN/COMMIT/ROLLBACK errors, 10,000 inserts timed in autocommit vs one transaction, rollback journal vs WAL, and three causes of database is locked.
-
SQL
SQL JOINs, GROUP BY and Subqueries: Traps, Measured
SQL lecture 3 on SQLite 3.51: INNER vs LEFT JOIN, the row explosion that triples totals, HAVING vs WHERE, COUNT(*) vs COUNT(col), subquery vs JOIN timed.
-
SQL
SQL INSERT, UPDATE, DELETE and Constraints, Tested
SQL lecture 2 on SQLite 3.51: INSERT, UPDATE, DELETE, real NOT NULL, UNIQUE, CHECK and FOREIGN KEY errors, and an UPDATE without WHERE undone by ROLLBACK.
-
SQL
SQL Indexes and EXPLAIN QUERY PLAN on 1M Rows
Read EXPLAIN QUERY PLAN, measure a query with and without an index on 1,000,000 rows, pick composite column order, and see when SQLite ignores your index.
-
SQL
SQL Basics: SELECT, WHERE, ORDER BY and NULL Traps
SQL lecture 1, run on SQLite 3.51: first table in 5 minutes, WHERE, ORDER BY, LIMIT, why = NULL finds nothing, type affinity traps and 1M-row timings.
↑