Becoming a SQL Guru
summary
Covers concrete SQL features with examples and gotchas: use of the FILTER clause (SQL-1994) to simplify conditional aggregates, GROUPING SETS / ROLLUP / CUBE to produce totals and subtotals in one query and how to label grouped rows, window frames RANGE vs ROWS and when rank differs from row_number, CTEs as optimization fences and recursive CTEs for iteration (example: expanding parts yields a total of 34 screws), and the lateral join for correlated subqueries inside JOINs.
AI-written summary · about
chapters (23)
- 0:00YELLO'S MISSION STATEMENT
- 0:38AGENDA
- 2:04QUERIES - SYNTAX OVERVIEW
- 2:58JOIN TYPES
- 3:26CROSS JOINS: EXAMPLE
- 4:49SET OPERATIONS: UNION VS UNION ALL
- 6:09FILTERED AGGREGATES 19.4
- 7:10GROUPING SETS, CUBE, ROLLUP.
- 20:44SUBQUERIES: CORRELATED
- 27:03WINDOW FUNCTIONS - BASICS
- 28:27WINDOW FUNCTIONS - FRAME CLAUSE
- 29:46WINDOW FUNCTIONS - BASIC EXAMPLE
- 30:34WINDOW FUNCTIONS - RANGE VS ROWS
- 32:19WINDOW FUNCTIONS - WINDOW CLAUSE
- 33:20WINDOW FUNCTIONS - ROW NUMBER
- 36:41CTE'S - INTRODUCTION
- 38:24CTE'S - NON RECURSIVE EXAMPLE
- 38:59CTE'S - RECURSIVE EXAMPLE
- 39:47CTE'S - RECURSIVE QUERY EVALUATION
- 41:33CTE'S - ANOTHER RECURSIVE EXAMPLE
- 51:03LATERAL - SET RETURNING FUNCTION EXAMPLE
- 51:52LATERAL - SUBQUERY EXAMPLE
- 53:26REFERENCES
related talks
01h 00mComplex Queries with Modern SQL
31mFuturistic SQL: Making the impossible possible
39mMore Than a Query Language: SQL in the 21st Century
45mMore Than a Query Language: SQL in the 21st Century
46mAwesome SQL – tips and tricks for application developers
18mHow to write powerful SQL that's easy to maintain
44mBeyond WHERE and GROUP BY
50mJOIN "My Session" ON Modern = 'SQL'