SQL
BEGIN, COMMIT, ROLLBACK and Savepoints
Grouping statements into one unit of work that either fully applies or fully undoes. Learn transaction boundaries, savepoints, autocommit and what sil...
SQL
A complete SQL path: fundamentals, DDL and DML, querying, joins, subqueries, CTEs, window functions, design, indexing, transactions, performance and security.
91 notes in 31 topics
Topics in SQL
SQL Basics 6
DDL 3
DML 3
SELECT 3
WHERE 3
Functions 4
NULL and Logic 2
Aggregate Functions 2
GROUP BY 3
JOIN 5
Subqueries 4
Set Operations 2
CTEs 2
Constraints 3
Keys 2
Relationships 2
Database Design 2
Normalisation 3
Views 2
Window Functions 4
Advanced SQL 6
Procedures and Functions 2
Triggers 2
Temporary Tables 2
Transactions 2
Isolation and Locking 3
Indexes 3
Query Performance 3
SQL Security 2
SQL Dialects 2
Practical SQL 4
Notes
SQL
ACID Properties Explained
Atomicity, consistency, isolation and durability - what each guarantee actually promises, how the database delivers it, and where each one can be weak...
SQL
Isolation Levels and Read Phenomena
The four isolation levels, the three anomalies they prevent, and what each database actually defaults to - with the session by session demonstrations.
SQL
Locks: Shared, Exclusive, Row and Table Level
How the database stops two transactions corrupting each other: lock modes, granularity, locking reads, and why an unindexed WHERE clause can lock a wh...
SQL
Deadlocks and Lock Contention
Why two transactions can wait for each other forever, how the database resolves it, and the design habits that prevent deadlocks and reduce contention...
SQL
What Indexes Are and Why They Exist
An index is a sorted lookup structure the database maintains for you. Learn how a B-tree finds a row, what an index costs, and the clustered index in...
SQL
Composite, Unique and Covering Indexes
Multi column indexes and why their order decides everything: the leftmost prefix rule, selectivity, covering indexes and index only scans.
SQL
When Indexes Help and When They Hurt
Indexes are not free. Learn the cases where the optimiser correctly ignores them, how to find unused and duplicate indexes, and how to maintain them.
SQL
How a Query Executes and Reading EXPLAIN
What happens between typing a query and getting rows, and how to read an execution plan - the single most useful performance skill in SQL.
SQL
Scans, Seeks, Join Strategies and Sorting
What the engine actually does: full scans versus index seeks, nested loop and hash joins, and why a sort or a temporary table appears in your plan.
SQL
Query Optimisation and Pagination Performance
A practical checklist for making a slow query fast, and the specific fix for deep pagination that no amount of indexing solves.
SQL
SQL Injection and Parameterised Queries
Why string concatenation lets user input rewrite your query, and why prepared statements make it structurally impossible - with the cases parameters c...