🗄️ Database Interview Questions

Master database interviews with questions on SQL, NoSQL, optimization, indexing, and database design

Indexing, ACID properties, SQL vs NoSQL, normalization, and query optimization

⚡

15-Minute Database Cheatsheet

Quick reference for last-minute interview preparation

🗄️ SQL vs NoSQL

SQL: Structured, ACID, relationships, schema
NoSQL: Flexible schema, BASE, horizontal scale
Document: MongoDB, nested JSON objects
Key-Value: Redis, caching, sessions
Graph: Neo4j, relationships-first

⚡ ACID Properties

Atomicity: All or nothing transaction
Consistency: Valid state before and after
Isolation: Concurrent transactions independent
Durability: Committed data persists
BASE: Basically Available, Soft state, Eventually consistent

📇 Indexing

B-Tree: Default, range queries, O(log n)
Hash: Equality only, O(1) lookup
Composite: Multiple columns, order matters
Covering: Index includes all query columns
Trade-off: Faster reads, slower writes

📐 Normalization

1NF: Atomic values, no repeating groups
2NF: 1NF + no partial dependencies
3NF: 2NF + no transitive dependencies
Denormalization: Trade consistency for speed
When: Reporting, read-heavy workloads

🚀 Query Optimization

EXPLAIN: Analyze query execution plan
Avoid: SELECT *, functions on indexed cols
N+1 Problem: Use JOINs or batch queries
Pagination: LIMIT/OFFSET vs cursor-based
Connection Pool: Reuse connections

🔒 Transactions & Locking

Isolation Levels: Read Uncommitted → Serializable
Row Lock: UPDATE/DELETE specific rows
Table Lock: DDL operations, maintenance
Deadlock: Circular wait, timeout/detection
MVCC: Read doesn't block write

📝 Essential SQL Patterns

JOIN: INNER, LEFT, RIGHT, FULL OUTER
GROUP BY + HAVING for aggregates
Window: ROW_NUMBER(), RANK(), LAG(), LEAD()
CTE: WITH temp AS (...) SELECT * FROM temp

⚠️ Common Interview Topics

• Difference between DELETE, TRUNCATE, DROP
• Clustered vs Non-clustered indexes
• Sharding vs Replication
• CAP theorem trade-offs
• Slow query diagnosis steps
• When to use Redis vs PostgreSQL

Indexes improve query performance by creating data structures that allow faster lookups. However, they slow down writes and consume additional storage.

Common Index Types:

  • B-Tree: Default index, good for equality and range queries
  • Hash: Fast for equality comparisons, not for ranges
  • GiST/GIN: Full-text search and complex data types
  • Partial: Index on subset of rows
  • Composite: Index on multiple columns
SQL

ACID guarantees reliability of database transactions:

  • Atomicity: All operations succeed or all fail (no partial updates)
  • Consistency: Data remains valid according to rules/constraints
  • Isolation: Concurrent transactions don't interfere with each other
  • Durability: Committed data persists even after system failure
SQL

SQL (Relational) and NoSQL (Non-relational) databases have different strengths:

SQL (PostgreSQL, MySQL)

  • Structured data, fixed schema
  • ACID transactions
  • Complex queries, JOINs
  • Vertical scaling
  • Strong consistency

NoSQL (MongoDB, Redis)

  • Flexible/dynamic schema
  • BASE (Basically Available, Soft state, Eventual consistency)
  • Denormalized data
  • Horizontal scaling
  • High performance for specific use cases
SQL
JavaScript
JavaScript

Normalization: Organizing data to reduce redundancy and improve integrity.Denormalization: Intentionally introducing redundancy for performance.

Normal Forms:

  • 1NF: Atomic values, no repeating groups
  • 2NF: 1NF + no partial dependencies
  • 3NF: 2NF + no transitive dependencies
  • BCNF: 3NF + stricter rules
SQL

Query optimization improves performance by reducing execution time and resource usage.

SQL

Window functions perform calculations across rows related to the current row without grouping them into a single output row. They enable analytical queries like running totals, rankings, and moving averages.

SQL

Both stored procedures and functions are stored SQL code, but they have different purposes and capabilities:

Stored Procedures

  • Can modify database state
  • Can have multiple return values (OUT parameters)
  • Can return result sets
  • Can use transactions
  • Cannot be used in SELECT

Functions

  • Should not modify database state
  • Must return a single value
  • Can be used in SELECT, WHERE, HAVING
  • Cannot use transactions
  • More restricted operations
SQL

Triggers are special stored procedures that automatically execute when specific events occur in the database. Common trigger types include BEFORE, AFTER, and INSTEAD OF triggers.

SQL

Sharding is a horizontal partitioning strategy that splits data across multiple database servers. Each shard contains a subset of the total data, enabling scalability beyond a single server's capacity.

Common Sharding Strategies:

  • Range-based: Partition by value ranges (e.g., user IDs 1-1000, 1001-2000)
  • Hash-based: Use hash function on shard key (consistent hashing)
  • Geographic: Partition by location (region-based sharding)
  • Directory-based: Lookup table maps entities to shards
SQL

Connection pooling reuses database connections instead of creating new ones for each request. This significantly improves performance by reducing connection overhead.

JavaScript

Common Table Expressions (CTEs) are temporary named result sets that exist within a single query. Recursive CTEs can reference themselves, enabling hierarchical and graph traversal queries.

SQL

Modern databases (PostgreSQL, MySQL, SQL Server) support native JSON data types with rich querying capabilities. This enables flexible schema design while maintaining ACID properties.

SQL

Database backup and recovery ensures data protection and business continuity. Different strategies balance between recovery point objective (RPO) and recovery time objective (RTO).

SQL

Locking prevents conflicts when multiple transactions access the same data concurrently. Different lock types and isolation levels balance between concurrency and consistency.

SQL

Database replication creates copies of data across multiple servers for high availability, load distribution, and disaster recovery. Common strategies include master-slave, master-master, and multi-master replication.

SQL

Interview Tips for Database