Awesome SQL – tips and tricks for application developers
summary
Many developers still write SQL-92 queries, but modern SQL is concrete: use WITH RECURSIVE to materialize a comment tree, compute SUM(...) OVER (PARTITION BY root_id) and DENSE_RANK() to pick the top 3 threads by total score (example mirrors Reddit-style comment trees), use UPSERT / MERGE for atomic insert-or-update to avoid race conditions, use JSON columns and “shredding” to turn arrays into relational rows, prefer keyset pagination and SKIP LOCKED for queues, and watch CTE inlining quirks in PostgreSQL for performance.
AI-written summary · about
chapters (44)
- 0:00Introduction
- 0:26Trends in technology
- 0:52Stack Overflow
- 1:34SQL
- 1:50History of SQL
- 3:49History of Database
- 4:11Why use SQL
- 4:30Top database systems
- 4:45What does SQL mean
- 4:59SQL is a standard
- 5:27SQL 92
- 6:49Example
- 8:11Pagination
- 9:11Derived Tables
- 10:18SQL Gadgets
- 10:59SQL Standard
- 11:14Common Table Expressions
- 11:41Recursive Table Expressions
- 13:54Recursive Common Tables
- 16:08Calculate Total Cost
- 16:28Aggregation
- 17:36Recursive
- 19:11Noncore members
- 19:59Query structure
- 20:24Root ID
- 20:45Window Functions
- 21:49How does it work
- 22:18Streams
- 22:34Ranking
- 23:12Limit order
- 24:56March
- 26:42Dis constraint violation
- 27:23Merge
- 27:53SQL 2006
- 30:01SQL 2011
- 33:19MySQL
- 35:03transform
- 35:47virtual table
- 36:50new SQL databases
- 37:26cockroach GDB
- 37:50volDB
- 38:55BigQuery
- 39:31Google Spanner
- 41:30Questions
related talks
50mPostgreSQL gotchas for app developers
26mLess Code, Faster Apps- Rediscovering SQL for Modern ORM Developers
41mModern SQL: Evolution of a dinosaur
42mPostgreSQL Tips & Tricks For App Devs
02h 42mSQL (The Return): Demystifying Preconceptions and Using the DB Effectively
34mSimple and Awesome Database Tricks
49mSELECT 'amazing features' FROM "postgresql"
39mMore Than a Query Language: SQL in the 21st Century