SQL
Anomalies and Denormalisation
The three anomalies normalisation prevents, and the disciplined way to reintroduce redundancy when reads genuinely demand it.
All notes, in reading order
963 published notes across every category.
#Interview
#Data Structures
#Intermediate
#SQL
#Computer Networks
#HTML
#Beginner
#CSS
#DSA
#Java
#Frontend
#Advanced
SQL
Creating and Querying Views
A view is a stored query you can select from like a table. Learn CREATE VIEW, how views are executed, and the four jobs they do well.
SQL
Updatable Views, Dropping Views and Views vs Tables
When you can INSERT or UPDATE through a view, what WITH CHECK OPTION protects against, and an honest comparison of views, tables and materialised view...
SQL
OVER, PARTITION BY and Window ORDER BY
Window functions compute across related rows without collapsing them. Learn the OVER clause, PARTITION BY, window ORDER BY, and how they differ from G...
SQL
ROW_NUMBER, RANK, DENSE_RANK and NTILE
The four ranking functions, how each handles ties, and the top N per group pattern that makes ROW_NUMBER the most used window function in SQL.
SQL
LAG, LEAD, FIRST_VALUE and LAST_VALUE
Reading other rows from the current row: the previous value, the next value, and the first or last in the window - with the LAST_VALUE frame trap.
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.
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
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.