Interview study guide
Databases, from SQL to MongoDB.
SQL and MySQL from the ground up, then MongoDB from my interview doc, Redis, and how to choose between them. Written for MySQL 9.7 LTS and MongoDB 9.0, with older versions explained where they still matter, and every query run against a real database.
- Chapters
- 27
- Topics
- 245
- Code samples
- 1976
- Added beyond the doc
- 192
01Database FundamentalsWhat a database is, the relational model, the parts of SQL, how MySQL is put together, which version you are on, how to connect, and the sample data every other chapter uses.02Querying DataReading data with SELECT: choosing columns, filtering rows, the rules of NULL, sorting, paging, removing duplicates, computing values, CASE, the everyday functions, and the order in which a query is really evaluated.03JoinsCombining rows from several tables: inner and outer joins, the FULL OUTER JOIN MySQL doesn't have, cross and self joins, long join chains, anti- and semi-joins, where conditions belong, and how MySQL actually runs a join.04Aggregation and GroupingCollapsing many rows into summaries: aggregate functions,
GROUP BY, HAVING, MySQL's ONLY_FULL_GROUP_BY rule, subtotals with WITH ROLLUP, string and JSON aggregation, and pivots with conditional aggregation.05Subqueries, CTEs and Window FunctionsQueries inside queries: scalar, correlated and derived-table subqueries, IN vs EXISTS (and the NOT IN NULL trap), CTEs and recursive CTEs, window functions for ranking, running totals and comparisons with neighbouring rows, top-N-per-group, and UNION/INTERSECT/EXCEPT.06Changing DataWriting to tables safely: INSERT, INSERT … SELECT, UPDATE and DELETE (including multi-table forms), DELETE vs TRUNCATE vs DROP, upserts with ON DUPLICATE KEY UPDATE, INSERT IGNORE and REPLACE, fast bulk loading, and the habits that stop a missing WHERE from ruining your day.07Tables, Data Types and ConstraintsHow to define tables that store the right values in the right shape and refuse the wrong ones, in MySQL 9.7 — with the older 5.7 and 8.0 behaviour you still meet.08Data Modelling and NormalisationHow to decide which tables exist, how they connect and where each fact lives — keys, relationships, ER diagrams, the normal forms with real before-and-after tables, and when to break the rules on purpose.09Indexes and Query PerformanceHow InnoDB indexes are built, how the optimizer chooses them, and how to read EXPLAIN, fix slow queries and paginate fast — measured on a 500,000-row table.10Transactions and ConcurrencyWhat ACID really guarantees in InnoDB, how isolation levels, MVCC snapshots and row, gap and next-key locks work, and how to handle deadlocks, job queues and lost updates — every example run in two live sessions.11Views, Stored Programs and SecurityCode and rules that live inside the server: views, stored procedures and functions, triggers and scheduled events, then who may run what (users, roles, GRANT), SQL injection and server-side prepared statements, and encryption and auditing.12Scaling, Replication and OperationsRunning MySQL beyond one box: scaling up and out, binlog replication and its flavours, replica lag, partitioning and sharding, connection limits, backups with point-in-time recovery, failover, and caching in front of the database.13MySQL from Node.jsTalking to MySQL from Node: mysql2 connections and pools, placeholders and prepared statements, transactions, streaming, then Knex, Sequelize, Prisma and Drizzle side by side, migrations, the N+1 problem measured, and connection limits in serverless.14MySQL vs PostgreSQLThe two most popular open-source relational databases side by side — MySQL 9.7 and PostgreSQL 18, each run for real on the same data — so you can explain where they differ and choose between them.15Classic SQL Interview ProblemsThe problems interviewers actually set — Nth highest, duplicates, top N per group, running totals, gaps and islands, hierarchies, pivots and NULL traps — each solved more than one way and run on MySQL 9.7.16MongoDB FundamentalsWhat MongoDB is, how documents and BSON work, what _id really is, the shell and the GUI, how SQL words map to MongoDB words, and which version you are running.17Querying DocumentsInserting documents, then reading them back: filters, comparison and logical operators, projections, arrays, embedded documents, regex, $exists, $expr, sorting, pagination, counting and deleting — with the traps my notes flagged.18Updating DocumentsChanging documents in place: updateOne/updateMany with $set, removing and renaming fields, arithmetic operators, upserts, replacing and find-and-modify, array operators, the positional $, $[] with arrayFilters, and pipeline updates.19Aggregation PipelineMongoDB's answer to GROUP BY, joins and reporting queries: documents flow through a list of stages, each one reshaping the stream for the next.20MongoDB IndexesHow MongoDB finds documents without reading all of them: index types, reading explain output, the ESR rule, and keeping indexes healthy.21Schema Design in MongoDBDesigning documents around how the application reads and writes them: when to embed, when to reference, the 16 MB limit, the classic patterns, and enforcing a schema anyway.22Transactions, Replication and ShardingHow MongoDB keeps a write safe: atomic single documents, multi-document transactions, replica sets with the oplog, write and read concern, and sharding for scale, all run on real three-member and sharded clusters.23MongoDB Operations and SecurityRunning MongoDB safely: backups with mongodump and mongorestore, users and SCRAM, role-based access control, TLS, client-side and Queryable Encryption, and the profiler, all shown on real servers.24MongoDB from Node.jsUsing MongoDB from a Node app: the official driver and its connection pool, Mongoose schemas, validation, middleware, populate and lean, and transactions with automatic retries, all run against MongoDB 9.0 with mongodb 7.7 and Mongoose 9.11.25Redis and CachingRedis itself — the data structures, expiry and eviction, the caching patterns, rate limits, locks, messaging and persistence — run for real on Redis 8.10, plus where Redis stands after its licence changes.26Choosing a DatabaseHow to pick between MySQL, PostgreSQL, MongoDB, Redis and the rest — the real trade-offs, CAP and PACELC, consistency models and workload shapes, demonstrated with real replicas, partitions and measurements rather than slogans.27Review QuestionsTest yourself across the whole site — SQL, design and indexes, transactions and scaling, MongoDB and Redis — with every predicted result checked against a real server.Added marks topics that weren’t in my original doc. Tick Learned on a topic to track what you’ve revised.