SQL
Recursive CTEs
Query a hierarchy of unknown depth, generate a series of rows, or follow a chain of references - all with one self referencing CTE.
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
NOT NULL, DEFAULT, UNIQUE and CHECK
The four constraints that guard a single table. What each one enforces, how NULL interacts with UNIQUE and CHECK, and why the database is the right pl...
SQL
PRIMARY KEY and FOREIGN KEY
The two constraints that give a database its structure: one that identifies a row, and one that guarantees a reference points at something real.
SQL
Referential Integrity, Cascades and Constraint Management
What happens to child rows when a parent is deleted or updated, how to choose between RESTRICT, CASCADE and SET NULL, and how to add, drop and disable...
SQL
Candidate, Primary, Alternate and Composite Keys
The vocabulary of keys, explained on one table: which columns could identify a row, which one you choose, and what happens to the rest.
SQL
Natural vs Surrogate Keys
Should the primary key come from the data or be generated? The trade offs, the failure modes of each, and the pattern most teams settle on.
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.
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
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
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
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
BCNF and Functional Dependencies
The formal notation behind normalisation, and the stricter normal form that catches what 3NF allows through when candidate keys overlap.