161 Database Essentials interview questions with answers
161 Database Essentials interview questions with detailed answers, sorted from basic to advanced. Free to practice, with progress saved automatically.
All Database Essentials interview questions
What does a basic SELECT statement look like in SQL?
How do INSERT, UPDATE, and DELETE work in SQL?
What is an ER Diagram (Entity-Relationship Diagram)? What are its main components?
What is CASCADE in a foreign key constraint? How does ON DELETE CASCADE work?
UUID vs Auto-increment ID: when should you use each?
What is database migration? Why is it important in CI/CD?
MongoDB vs PostgreSQL: when should you choose each?
What is an ORM (Object-Relational Mapper)? What are its benefits and drawbacks?
What is Prisma ORM? Why is it popular in the Node.js/TypeScript ecosystem?
How does a Prisma schema file work?
What is Sequelize? How does it compare to Prisma?
What is Mongoose? How does Schema work in Mongoose?
When should you use Redis as a cache?
Database transactions in Prisma: how to use them?
What is Drizzle ORM? How does it compare to Prisma?
What is the relational model? How do tables, rows, columns and relationships fit together?
Distinguish primary key, foreign key, unique key and composite key.
What is normalization? Explain 1NF, 2NF and 3NF.
What is denormalization and when should you accept redundant data?
What is ACID? Explain each property.
How do transactions work (BEGIN/COMMIT/ROLLBACK) and what are savepoints for?
What are the isolation levels and which anomalies does each prevent?
Distinguish dirty read, non-repeatable read and phantom read.
How does a B-tree index work and why is lookup fast?
When should you add an index and when should you not?
What is a composite index and why does column order (leftmost prefix) matter?
What is a covering index / index-only scan?
What is EXPLAIN for and how do you read a query plan?
What is the N+1 query problem and how do you fix it?
How do the JOIN types (INNER, LEFT, RIGHT, FULL, CROSS) differ?
What is the difference between a view and a materialized view?
How do SQL and NoSQL differ?
What are the NoSQL types (document, key-value, wide-column, graph) with examples?
When do you choose SQL versus NoSQL?
What is connection pooling and why is it needed?
What is replication (leader/follower) and what problem does it solve?
What is a read replica and how does replication lag affect it?
What is sharding and how does it differ from replication?
What is partitioning? Distinguish horizontal and vertical partitioning.
How do optimistic and pessimistic locking differ?
What is a deadlock, what causes it and how do you avoid it?
What is MVCC and what problem does it solve?
What does VACUUM do in PostgreSQL and why is it needed?
What is JSONB in PostgreSQL and how does it differ from plain JSON?
What is WAL (Write-Ahead Logging) and what does it guarantee?
How does MongoDB store data? What are documents, collections and BSON?
How does the MongoDB aggregation pipeline work?
What types of indexes does MongoDB have?
What is a replica set in MongoDB?
How does sharding work in MongoDB?
In MongoDB, when should you embed versus reference?
How do you run schema migrations with zero downtime?
A query is slow — how do you diagnose and optimize it?
Why add a caching layer and what strategies exist?
What is the CAP theorem and how does it apply to databases?
What is eventual consistency and when is it acceptable?
Beyond B-tree, what index types does PostgreSQL have and when are they used?
How do OLTP and OLAP differ?
What is the difference between a clustered and a non-clustered index?
Why is OFFSET pagination slow, and what is keyset pagination?
Surrogate key vs natural key — which should be the primary key?
How do DELETE, TRUNCATE and DROP differ?
How do GROUP BY, aggregate functions and HAVING vs WHERE work?
What is a CTE (WITH) and how does it differ from a subquery?
Text search: how do LIKE and full-text search differ?
What command groups make up SQL (DDL, DML, DCL, TCL)?
What does NULL mean in SQL and what is three-valued logic?
What is the difference between UNION and UNION ALL? And INTERSECT/EXCEPT?
How do you choose string data types: CHAR, VARCHAR, TEXT?
How do you model relationships (1–1, 1–many, many–many)? What is a junction table?
What is a self join and when do you use it?
What is the difference between a subquery and a correlated subquery?
What is a window function and how does it differ from GROUP BY?
What is Redis? What data types does it have and what is it used for?
What is BASE and how does it differ from ACID?
What are stored procedures and triggers? Pros and cons?
Why should you avoid SELECT * in production code?
What is UPSERT (INSERT ... ON CONFLICT) and what problem does it solve?
What is the difference between soft delete and hard delete? When to use each?
Why should you not store money as a float? What type should you use?
Distributed transactions: what are two-phase commit (2PC) and the Saga pattern?
How do B-tree and LSM-tree storage engines differ? Why do write-heavy NoSQL stores use LSM?
What is the difference between a star schema and a snowflake schema (dimensional modeling)?
Backup strategies: what are full/incremental, PITR and RPO/RTO?
What is consistent hashing and what problem does it solve in sharding/caching?
How do MySQL and PostgreSQL differ, and when do you choose each?
How do you design an inventory-decrement / ticket-booking flow that avoids race conditions?
Classic SQL exercise: how do you find the second-highest salary / top-N per group?
What is SQL injection and why do parameterized queries stop it?
How do you design an e-commerce order schema (orders / order_items)?
How do EXISTS, IN and NOT EXISTS differ, and when should you use each?
How do RANK(), DENSE_RANK() and ROW_NUMBER() differ?
How do super key, candidate key, primary key and composite key differ?
How do DBMS and RDBMS differ?
How do a B-tree and a B+ tree differ, and why do database indexes usually use a B+ tree?
How do you map an ER (entity-relationship) model to a relational schema (tables)?
What is the logical execution order of a SELECT statement, and why does it differ from the written order?
Why can't you use a column alias in `WHERE` but you can in `ORDER BY`?
How do `COUNT(*)`, `COUNT(col)` and `COUNT(DISTINCT col)` differ? How do `SUM`/`AVG` treat NULL?
What are `COALESCE` and `NULLIF` used for? Give practical examples.
Why does `NOT IN` with a subquery containing NULL always return nothing? How do you fix it?
Why does my `LEFT JOIN` behave like an `INNER JOIN`? Should the condition go in `ON` or `WHERE`?
Why does joining several tables and then `SUM`ming produce doubled numbers? How do you handle join fan-out?
Why does paginating with `ORDER BY created_at LIMIT/OFFSET` repeat or skip rows?
How do you use `LAG`/`LEAD` to compute month-over-month revenue growth?
What are `GROUPING SETS`, `ROLLUP` and `CUBE`? When use them instead of several `UNION ALL` queries?
How do you compute a running total with a window function, and how do `ROWS` and `RANGE` differ in the frame clause?
Indexes speed up reads — why do they slow down writes? How many indexes are too many?
Why does `WHERE DATE(created_at) = '2026-01-01'` fail to use an index on `created_at`? How do you rewrite it?
Is it useful to index a column like `is_active` or `status` that only holds a few distinct values?
Why can `LIKE 'abc%'` use an index while `LIKE '%abc%'` cannot? How do you make substring search fast?
A query has an index on the column but is still slow because of an implicit cast. What is happening?
A query with `ORDER BY created_at DESC LIMIT 20` is still slow despite an index. How should the index be built?
Are `NULL` values stored in an index? Can `WHERE col IS NULL` use one?
For `WHERE tenant_id = ? AND created_at > ? AND status = ?`, what column order should the composite index use?
Should you use one multi-column composite index or several single-column indexes? Can the database combine indexes?
Without a transaction, where does a money-transfer flow break? What does each letter of ACID mean in practice?
Which anomaly does each of the four isolation levels prevent, and how do PostgreSQL and MySQL defaults differ?
How does a lost update happen? Name three ways to prevent it.
What does a plain `UPDATE` lock? How do row locks differ from table locks?
How do you implement optimistic locking with a `version` column, and when do you pick it over a pessimistic lock?
Why should an external API call never sit inside a transaction, and how do you restructure it?
What is a savepoint for, and what is a "nested transaction" in an ORM really?
What traps make an ORM transaction not actually cover what you think it covers?
A client times out without knowing whether the transaction committed — how do you retry safely?
What is write skew? Why does `REPEATABLE READ` fail to prevent it, and what does `SERIALIZABLE` cost?
What are InnoDB gap locks and next-key locks, and why can an `UPDATE` on a non-indexed column lock nearly the whole table?
You must update data in two services / two databases — 2PC or saga? What are the trade-offs?
Order line items already store `product_id` — why also copy the product name and price at purchase time?
How do you model products with variants (size, color)? What does the cart point to?
Selling from multiple warehouses: how do you model inventory so you know per-warehouse stock and can hold items during checkout?
How do you model promotions and discount codes? How do you stop a code being redeemed more times than allowed?
Selling in several countries: how do you store money in the database?
A many-to-many relationship that carries its own data (enrolment date, grade) — how do you model it?
A multi-level product category tree: compare adjacency list, materialized path and closure table. Which do you pick?
Should the primary key be a UUID or an auto-increment bigint? How does it affect indexes?
The users table has `unique(email)` and uses soft delete. A user deletes their account then signs up again with the same email and hits a duplicate error — how do you fix it?
Requirement: view any record as it was at any past moment, and know who changed what. How do you store change history?
A SaaS app serving many customers: database per tenant, schema per tenant, or a shared table with `tenant_id`? What are the trade-offs?
A customer's shipping address changes over time. How do you store it so old orders still show the address they were actually shipped to?
A post list page shows a comment count and `count(*)` on every request is slow. Should you add a `comment_count` column? How do you keep it in sync?
Order status (pending, paid, shipped, cancelled): store it as an integer, a string with a CHECK, a native ENUM, or a lookup table?
Customers want to define their own extra profile fields (a different set per tenant). EAV or JSONB? What are the trade-offs?
An event/log table grows by millions of rows a day, queries keep slowing down, and deleting old data stalls the database. How do you redesign it?
How long should a cache TTL be? Why add jitter to it?
How do you design cache keys? How do you retire old entries when the data format changes?
What data should not be cached? Give a case where caching caused a business bug.
Cache hit ratio sits around 40% — what do you check?
After updating the DB, should you **delete** the cache entry or **overwrite** it with the new value?
Every deploy or Redis restart makes the system slow for minutes because the cache is empty. How do you fix it?
How do you sync data from Postgres to Elasticsearch? What latency is acceptable?
Postgres has full-text search — when do you actually need Elasticsearch?
After saving, the user opens the list page and does not see the new record because reads go to a read replica. How do you handle it?
Storing time-series metrics/telemetry: Postgres or a dedicated time-series DB?
The team wants to add a new datastore for one feature. What does that decision really cost?
How does DynamoDB table design differ from relational design? When is choosing DynamoDB a mistake?
How far can Postgres go as a replacement for Redis as cache and job queue?
Key-value, document, wide-column, graph — what does each win at, and what signals a wrong choice?
Which cache layers does a request pass through? Where should you put the cache?
What decides that two requests are "the same content" for a CDN? How do you push new content out?
After a deploy or a Redis restart, the database gets overwhelmed and dies. Why, and how do you prevent it?
