Module Review: Advanced SQL

[!NOTE] This module explores the core principles of Module Review: Advanced SQL, deriving solutions from first principles and hardware constraints to build world-class, production-ready expertise.

1. Key Takeaways

  • Logical Order: SQL runs FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. Aliases in SELECT are not visible in WHERE.
  • MVCC: Postgres handles concurrency by creating new versions of rows for updates. Readers see a snapshot (old version), writers create a new version. VACUUM cleans up the dead rows.
  • Window Functions: Use OVER (PARTITION BY ... ORDER BY ...) to calculate aggregates without collapsing rows.
  • Ranking: ROW_NUMBER (1,2,3), RANK (1,1,3), DENSE_RANK (1,1,2).
  • Recursive CTEs: Use WITH RECURSIVE to traverse trees and graphs. Needs an Anchor Member and a Recursive Member.
  • JSONB: Always prefer JSONB over JSON for indexing support. Use GIN indexes for fast containment (@>) queries.

2. Module Review: Advanced SQL

3. Flashcards

Test your knowledge. Click to flip.

Which clause runs first: SELECT or WHERE?

Click to reveal

WHERE runs first. It filters rows before columns are selected.

Difference between RANK() and DENSE_RANK()?

Click to reveal

RANK skips numbers after ties (1, 1, 3).
DENSE_RANK does not skip (1, 1, 2).

What index type speeds up JSONB @> queries?

Click to reveal

GIN
(Generalized Inverted Index).

How does Postgres prevent readers from blocking writers?

Click to reveal

MVCC.
It keeps multiple versions of rows so readers see a snapshot while writers create new versions.

4. Module Review: Advanced SQL

5. Cheat Sheet

Category Concept Syntax / Example
Internals Order of Exec FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
  MVCC Multiversion Concurrency Control (Readers don’t block Writers)
Window Syntax FUNC() OVER (PARTITION BY col ORDER BY col)
  Rank RANK() (gaps), DENSE_RANK() (no gaps)
  Offset LEAD(col, 1), LAG(col, 1)
Recursive Syntax WITH RECURSIVE cte AS (Anchor UNION ALL Recursive) SELECT * FROM cte
JSONB Get JSON col -> 'key' (returns jsonb)
  Get Text col ->> 'key' (returns text)
  Contains col @> '{"key": "val"}' (uses GIN Index)
  Exists col ? 'key'

6. Module Review: Advanced SQL

7. Quick Revision

  1. Don’t try to use aliases in WHERE.
  2. Do use JSONB for flexibility, but Relational for structure.
  3. Do use EXPLAIN ANALYZE to check if your GIN index is being used.
  4. Don’t forget the termination condition in Recursive CTEs.

8. Module Review: Advanced SQL

[!NOTE] This module explores the core principles of Module Review: Advanced SQL, deriving solutions from first principles and hardware constraints to build world-class, production-ready expertise.

9. Next Steps

Now that you’ve mastered the advanced SQL constructs, it’s time to learn how to optimize them.