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
Notes related to SQL.
131 notes tagged
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
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
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
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
Advanced Date Based Analysis
Period over period comparison, cohort retention, complete date ranges with no missing buckets, and rolling windows over time.
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
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
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
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
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
Frames, Running Totals and Moving Averages
The frame clause decides which rows each calculation sees. Learn ROWS versus RANGE, running totals, moving averages, and when a window beats GROUP BY.