How to write powerful SQL that's easy to maintain

on YouTube ↗18m

" summary

Starts from a real report example counting employees per department and shows refactors: use WITH clause for CTEs, collapse common joins into views, then replace views with SQL macros to parameterize thresholds and even table and column identifiers using table_t and columns_t. Explains table versus scalar macros, that macros return SQL text as a CLOB, and that macros can call macros. Demonstrates debugging with dbms_utility.expand_sql_text to reveal a copy paste bug (>= vs >) and verifies equivalence with set difference queries.

AI-written summary · about

" chapters (5)

1. 0:00Intro
2. 1:14Writing the query
3. 4:10SQL Macros
4. 8:06Demo
5. 16:13Recap

" related talks

• 31mFuturistic SQL: Making the impossible possibleConnor McDonald · Oracle DatabaseWorld 2021
• 54mBecoming a SQL GuruStella Nisenbaum · PGConf Silicon Valley 2016
• 46mAwesome SQL – tips and tricks for application developersVlad Mihalcea · Voxxed Days Cluj-Napoca 2019
• 57mProgramming the SQL Way with Common Table ExpressionsBruce Momjian · Postgres Open 2012
• 01h 00mComplex Queries with Modern SQLHolger Jakobs · FrOSCon 2016
• 41mModern SQL: Evolution of a dinosaurMarkus Winand · WebCamp Zagreb 2018
• 40mLevel-up Your ActiveRecord Skills: Learn SQL!Ryan Dlugosz · RailsConf 2016
• 18mApparently, you can "debug" your SQL queriesEyal Trabelsi · Conf42 Observability 2023