Databases & SQL

Database fundamentals, SQL, transactions, scaling, and interview-heavy DBMS topics.

• Database Fundamentals — DBMS, RDBMS vs NoSQL, tables, rows, columns, schemas, and constraints.
• Database Design — Relationships, ER diagrams, junction tables, schema design, and cardinality.
• Keys & Constraints — Primary keys, foreign keys, candidate keys, composite keys, super keys, alternate keys, and unique keys.
• Normalization — Data redundancy, anomalies, 1NF, 2NF, 3NF, BCNF, and denormalization.
• SQL Basics — SQL command categories (DDL, DML, DQL, DCL, TCL), SELECT, DELETE vs TRUNCATE vs DROP, and logical query order.
• SQL Filtering & Sorting — WHERE, logical operators, NULL and three-valued logic, LIKE, IN, BETWEEN, DISTINCT, ORDER BY, LIMIT, aliases, and CASE.
• SQL Joins — INNER, LEFT, RIGHT, FULL, CROSS, and SELF JOIN, ON vs WHERE, anti-joins, and how databases execute joins.
• Aggregation — COUNT, SUM, AVG, MIN, MAX, NULL handling, GROUP BY, HAVING, and conditional aggregation.
• Intermediate SQL — Subqueries, correlated subqueries, EXISTS, IN, ANY, ALL, CTEs, UNION, INTERSECT, and EXCEPT.
• Window Functions — OVER, PARTITION BY, frames, ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, running totals, and top-N per group.
• Transactions — What a transaction is, ACID properties and how databases provide them, autocommit, savepoints, and the transaction lifecycle.
• Concurrency Control — Dirty reads, non-repeatable reads, phantom reads, lost updates, write skew, and how locking, MVCC and optimistic control prevent them.
• Isolation Levels — Read Uncommitted, Read Committed, Repeatable Read, and Serializable: the SQL standard versus how PostgreSQL and MySQL InnoDB actually behave.
• Locking — Shared and exclusive locks, row and table locks, SELECT FOR UPDATE, gap locks, and optimistic versus pessimistic locking.
• Deadlocks — Causes, detection, prevention, and resolution.
• Views — Views and materialized views.
• Stored Procedures & Triggers — Stored procedures, functions, and triggers.
• Indexing — B-Tree, hash indexes, clustered indexes, composite indexes, and table scans.
• Query Optimization — EXPLAIN, execution plans, optimizers, predicate pushdown, N+1, pagination, and SELECT *.
• Database Scaling — Vertical scaling, horizontal scaling, read replicas, partitioning, and sharding.
• Advanced Concepts — MVCC, WAL, replication, CAP theorem, BASE, connection pooling, and Redis caching.
• NoSQL — Non-relational database models and when they fit better than SQL.
• Types of NoSQL Databases — Document, key-value, wide-column, and graph database models.
• MongoDB & Document Databases — How document databases store JSON-like data and when MongoDB fits.
• Key-Value Databases — Fast key-based lookup using databases such as Redis.
• Wide-Column Databases — Distributed write-heavy storage using databases such as Cassandra.
• Graph Databases — Relationship-heavy data using nodes and edges.
• When to Use SQL vs NoSQL — Clear decision rules with practical examples.