Level 2 of 13 · about 2 weeks
Advanced SQL
Views and materialized views, window functions, common table expressions and recursive queries, stored procedures and triggers, integrity constraints, a first look at indexes from the SQL side, and authorization.
Modules
Core: everyone needs it. Common: most jobs use it. Specialist: optional depth, safe to skip on a first read.
1
Views
virtual tables backed by a stored query, when they can be updated, and materialized views as a preview
Common 5 parts · about 3 hours
2
Window functions
OVER and PARTITION BY, computing a value per row without collapsing the result like GROUP BY does
Core 5 parts · about 3 hours
3
Common table expressions and recursive queries
naming a subquery with WITH, and using a recursive CTE to walk hierarchical data
Common 5 parts · about 3 hours
4
Stored procedures, functions and triggers
server-side logic, what each of the three is for, and when to avoid reaching for them
Common 5 parts · about 3 hours
5
Integrity constraints
PRIMARY KEY, FOREIGN KEY, CHECK, UNIQUE and NOT NULL, as rules the database enforces on every write
Core 5 parts · about 3 hours
6
Indexes from the SQL side
CREATE INDEX, and a first, practical look at which column is worth indexing
Core 5 parts · about 3 hours
7
Authorization in SQL
GRANT, REVOKE, roles, least-privilege access, and SQL injection as the classic failure to avoid
Common 5 parts · about 3 hours