Database Interview Questions for 2026

INNER JOIN returns rows with matching values in both tables. LEFT JOIN returns all rows from the left table and matching rows from the right (NULLs where no match). RIGHT JOIN is the reverse. FULL OUTER JOIN returns all rows from both tables with NULLs where no match exists. CROSS JOIN returns the Cartesian product of both tables. Self-joins join a table with itself.

Indexes are data structures (typically B-trees or hash tables) that allow the database to find rows without scanning the entire table. They speed up SELECT queries and WHERE clauses but slow down INSERT, UPDATE, and DELETE operations because the index must also be updated. Common types include single-column, composite, unique, partial, and covering indexes.

Atomicity: all operations in a transaction succeed or all fail. Consistency: a transaction moves the database from one valid state to another. Isolation: concurrent transactions do not interfere with each other. Durability: committed transactions persist even after system failures. ACID guarantees are implemented through write-ahead logging, locking mechanisms, and multi-version concurrency control (MVCC).

Read Uncommitted: allows dirty reads. Read Committed: prevents dirty reads but allows non-repeatable reads. Repeatable Read: prevents dirty and non-repeatable reads but allows phantom reads. Serializable: prevents all anomalies but has the lowest concurrency. Higher isolation levels provide stronger guarantees but reduce throughput. Most databases default to Read Committed or Repeatable Read.

A clustered index determines the physical order of data in the table. There can be only one per table (usually the primary key). A non-clustered index is a separate structure with pointers to the actual data rows. A table can have multiple non-clustered indexes. Clustered indexes are faster for range queries; non-clustered indexes are better for point lookups on non-primary columns.

Document stores (MongoDB, CouchDB) store semi-structured JSON/BSON documents. Key-value stores (Redis, DynamoDB) store data as key-value pairs with fast lookups. Wide-column stores (Cassandra, HBase) store data in column families, ideal for time-series data. Graph databases (Neo4j, Amazon Neptune) store nodes and relationships, optimized for traversal queries.

Choose document databases when: your data has a variable or evolving schema, you need to store nested/hierarchical data naturally, you want to avoid complex joins, you need horizontal scalability, or your read patterns align with document boundaries. Avoid document databases when you need complex transactions across documents, strong referential integrity, or heavy ad-hoc querying with joins.

Redis stores all data in memory (RAM), providing sub-millisecond latency. It uses a single-threaded event loop (avoiding lock overhead), efficient data structures (hash tables, skip lists, compressed lists), and optional persistence via RDB snapshots or AOF logging. Redis Cluster provides horizontal scaling through hash-slot-based sharding across multiple nodes.

Graph databases store data as nodes (entities) and edges (relationships) with properties on both. They excel at traversing relationships: social networks, recommendation engines, fraud detection, and knowledge graphs. Unlike relational databases where joins become expensive at depth, graph databases maintain constant-time traversal regardless of dataset size.

Normalization reduces data redundancy and improves integrity. 1NF: atomic values, no repeating groups. 2NF: 1NF plus no partial dependencies on composite keys. 3NF: 2NF plus no transitive dependencies. BCNF: every determinant is a candidate key. Higher forms (4NF, 5NF) address multi-valued and join dependencies. In practice, most systems normalize to 3NF or BCNF.

Denormalization introduces controlled redundancy to improve read performance. Use it when: read-heavy workloads need faster queries, complex joins are too expensive, you are building data warehouses or analytics systems, or caching layers are insufficient. Common techniques include adding computed columns, materialized views, and duplicating data across tables. The trade-off is increased storage and more complex write logic.

Sharding horizontally partitions data across multiple database servers. Strategies include: range-based (partition by date or ID range), hash-based (hash the shard key to distribute evenly), directory-based (lookup table maps keys to shards), and geographic (partition by region). Challenges include cross-shard queries, rebalancing, and maintaining referential integrity. Choose shard keys carefully to avoid hotspots.

In relational databases, use a junction (bridge) table with foreign keys to both related tables. The junction table can also hold relationship attributes (e.g., enrollment date in a student-course relationship). Add composite or individual indexes based on query patterns. In document databases, you can embed references or use denormalized arrays, depending on cardinality and access patterns.

Start with EXPLAIN/EXPLAIN ANALYZE to understand the query plan. Common optimizations: add missing indexes, avoid SELECT *, rewrite subqueries as JOINs, use covering indexes, partition large tables, avoid functions on indexed columns in WHERE clauses, use query hints when needed, and consider materialized views for complex aggregations. Always measure before and after.

A query execution plan shows how the database engine processes a query: which indexes it uses, join algorithms (nested loop, hash join, merge join), table scan vs. index scan, estimated vs. actual row counts, and cost estimates. Look for sequential scans on large tables (missing indexes), high row estimates vs. actual (stale statistics), and nested loops with large tables.

B-tree indexes: general-purpose, good for range and equality queries (default in most RDBMS). Hash indexes: fast equality lookups but no range support. GIN indexes: full-text search and array/JSON columns. GiST indexes: geometric and spatial data. Partial indexes: index a subset of rows. Composite indexes: multiple columns (column order matters for query matching). Covering indexes: include all query columns to avoid table lookups.

Connection pooling maintains a cache of database connections that are reused across requests instead of creating new connections for each query. Creating a connection is expensive (TCP handshake, authentication, memory allocation). Pools like PgBouncer (PostgreSQL) or HikariCP (Java) manage connection lifecycle, limiting max connections and queuing excess requests. This reduces latency and prevents overwhelming the database.

Use SQL when: you need ACID transactions, complex queries with joins, strong data consistency, or a well-defined schema. Use NoSQL when: you need horizontal scalability, flexible schemas, high write throughput, or your data model fits naturally (documents, key-value, graph). Many modern systems use both: SQL for transactional data and NoSQL for caching, session storage, or analytics.

The CAP theorem states that a distributed system can guarantee at most two of three properties: Consistency (every read returns the latest write), Availability (every request gets a response), and Partition tolerance (the system works despite network failures). Since network partitions are inevitable, the real choice is between CP (strong consistency, e.g., PostgreSQL, MongoDB) and AP (high availability, e.g., Cassandra, DynamoDB).

Eventual consistency means all replicas will converge to the same value given enough time without new writes. It is acceptable for: social media feeds, product view counts, recommendation systems, DNS, and caching layers — anywhere stale reads are tolerable for seconds. It is not acceptable for: financial transactions, inventory counts during checkout, or any scenario where stale data causes incorrect decisions.