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
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
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.
SQL
Anomalies and Denormalisation
The three anomalies normalisation prevents, and the disciplined way to reintroduce redundancy when reads genuinely demand it.
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.