Databases
🪟 Advanced SQL: windows, views and CASE
Conditional logic with CASE, window functions (OVER, PARTITION BY, ROW_NUMBER, RANK, running totals), views and an introduction to recursive CTEs.
What you'll learn
- Apply conditional logic in queries with the
CASE WHEN ... THEN ... ENDexpression - Compute window functions with
OVERandPARTITION BY(ROW_NUMBER,RANK, running totals) without collapsing rows - Use
LAG,LEAD,NTILEand window frames for moving averages - Create views with
CREATE VIEWand automate reactions withCREATE TRIGGER
Lessons
Conditional logic with CASEReturn different values depending on a condition, inside the SELECT itself. Window functionsCompute rankings and running totals without collapsing rows, with OVER and PARTITION BY. Advanced windows: LAG, LEAD, NTILE and framesLook at the previous/next row, split into groups and define moving averages and sums with window frames. Views and recursive CTEsSave queries with CREATE VIEW and generate series with WITH RECURSIVE. Triggers: automating reactionsRun SQL automatically when a row is inserted, updated or deleted, with CREATE TRIGGER.Practice this module in the app
DevPath is a hands-on course: you read the theory here; in the app you put it into practice with exercises that really run, offline.
Start free in the app →