SQL & Databases Roadmap

What SQL is, the relational model, and the users/orders/products schema used throughout this section.

SELECT, WHERE, comparison and logical operators, LIKE, IN, BETWEEN, ORDER BY, and LIMIT.

INNER, LEFT, RIGHT, and FULL joins explained with examples, plus self-joins.

COUNT, SUM, AVG, MIN, MAX, GROUP BY, and the difference between WHERE and HAVING.

Subqueries in WHERE and FROM, correlated subqueries, common table expressions, and recursive CTEs.

ROW_NUMBER, RANK, DENSE_RANK, LAG/LEAD with PARTITION BY, running totals, and top-N-per-group.

Reading a query plan conceptually, plus classic puzzles: gaps in a sequence, second-highest value, and hierarchical self-joins.

B-tree vs GIN vs GiST indexes, reading EXPLAIN ANALYZE, and partial indexes.

Why schema design matters and the process: identifying entities, relationships, and constraints.

1NF, 2NF, and 3NF explained with a before/after example, and when to deliberately denormalize.

What to index, composite index column order, and the real costs of over-indexing.

Atomicity, consistency, isolation, and durability, each explained with a concrete failure example.

PostgreSQL's MVCC model, isolation levels, and why VACUUM exists.

A complete worked example: entity identification, normalization decisions, and the final schema for an e-commerce order system.

Embedding vs referencing, the core MongoDB modeling decision, and schema validation rules.

Strings, hashes, lists, sets, and sorted sets with real commands, including a leaderboard example.

Primary-replica setup, binlog-based replication, GTIDs, replication lag, and read/write splitting.

Indexing, read replicas, sharding strategies, and the trade-offs of each.

Real SQL interview questions and answers covering joins, subqueries, NULL handling, and more.