SQL
Conditional Aggregation, Pivot and Unpivot
Turning rows into columns and back again with portable SQL. Conditional aggregation, the PIVOT operator where it exists, and unpivoting with UNION ALL...
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
Top N Per Group
Three ways to get the best row in each group - window function, correlated subquery and lateral join - and how to pick between them.
SQL
Finding and Removing Duplicates
Detect duplicate rows on any definition of "duplicate", inspect them safely, delete all but one, and add the constraint that stops them coming back.
SQL
Gaps and Islands, and Comparing Adjacent Rows
Finding missing values in a sequence and grouping consecutive runs together - the pattern behind streak counting, session detection and attendance rep...
SQL
Hierarchical Data and Recursive Queries
Storing and querying trees: the adjacency list, walking it with a recursive CTE, and the materialised path and closure table alternatives.
SQL
Advanced Date Based Analysis
Period over period comparison, cohort retention, complete date ranges with no missing buckets, and rolling windows over time.
SQL
Stored Procedures and Parameters
Named procedural code stored in the database. Learn CREATE PROCEDURE, IN, OUT and INOUT parameters, control flow, and when a procedure is the right to...
SQL
User Defined Functions
Functions return a value and can be used inside a query. Learn CREATE FUNCTION, determinism, the difference from procedures, and the performance trap.
SQL
Trigger Basics: BEFORE, AFTER, INSERT, UPDATE and DELETE
Code the database runs automatically when data changes. Learn the six trigger points, the NEW and OLD row references, and how to write an audit trigge...
SQL
Trigger Use Cases and Limitations
What triggers are genuinely good at, the costs they impose, and the honest list of reasons teams end up removing them.
SQL
Temporary Tables and Session Scope
A real table that exists only for your session. Learn CREATE TEMPORARY TABLE, its scope and lifetime, indexing it, and the dialect differences.
SQL
Temporary Tables vs CTEs vs Views vs Derived Tables
Four ways to name an intermediate result, and a decision rule for choosing between them based on scope, reuse and whether you need an index.