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
Notes related to SQL.
131 notes tagged
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
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
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
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
Anomalies and Denormalisation
The three anomalies normalisation prevents, and the disciplined way to reintroduce redundancy when reads genuinely demand it.
SQL
BCNF and Functional Dependencies
The formal notation behind normalisation, and the stricter normal form that catches what 3NF allows through when candidate keys overlap.
SQL
First, Second and Third Normal Form
Normalisation worked through on one table: remove repeating groups, then partial dependencies, then transitive ones - with the SQL at each step.
SQL
Data Integrity, Redundancy and Consistency
The three kinds of integrity a relational database enforces, why redundancy causes anomalies, and where a derived value is worth its maintenance cost.
SQL
Schema Design: Entities, Attributes and Relationships
How to get from a description of a business to a set of tables: find the entities, give them attributes, connect them, and name everything consistentl...
SQL
Many to Many Relationships and Junction Tables
Two tables that both have many of each other need a third table. Learn the junction table pattern, its key design, and how to query and count across i...
SQL
One to One and One to Many Relationships
The two simplest relationships and the single rule that decides where the foreign key goes - plus when a one to one split is actually worth it.