CSE Roadmap

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. 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. 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. 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. 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. 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. 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. 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