Database interview questions

What is a DBMS? How does an RDBMS differ from a DBMS and from plain files?

Easy · Database, Fundamentals, Fresher

SQL vs NoSQL: what are the differences and how do you choose between them?

Easy · Database, Fundamentals, Fresher

Explain super key, candidate key, primary key, composite key, foreign key, surrogate key, and natural key.

Easy · Database, Fundamentals, Fresher

What is the difference between a PRIMARY KEY and a UNIQUE constraint?

Easy · Database, Fundamentals, Fresher

What are DDL, DML, DQL, DCL, and TCL commands in SQL?

Easy · Database, Fundamentals, Fresher

How does NULL behave in SQL? Explain three-valued logic.

Medium · Database, Fundamentals, Fresher

What integrity constraints does SQL support and when do you use each?

Easy · Database, Fundamentals, Fresher

What is referential integrity? Explain ON DELETE CASCADE, SET NULL, and RESTRICT.

Easy · Database, Fundamentals, Fresher

Explain the ER model: entities, attributes, relationships, and cardinality.

Easy · Database, Fundamentals, Fresher

How do you implement 1:1, 1:N, and M:N relationships in a relational database?

Easy · Database, Fundamentals, Fresher

Explain the three-schema architecture and logical vs physical data independence.

Easy · Database, Fundamentals, Fresher

OLTP vs OLAP: how do the workloads, schemas, and storage differ?

Easy · Database, Fundamentals, Fresher

Explain INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN with examples.

Easy · Database, SQL, Fresher

What is a self join and when do you need one?

Easy · Database, SQL, Fresher

What is the difference between WHERE and HAVING?

Easy · Database, SQL, Fresher

How does GROUP BY work? What are the rules for columns in the SELECT list?

Easy · Database, SQL, Fresher

What is the logical execution order of a SQL SELECT statement?

Medium · Database, SQL, Fresher

UNION vs UNION ALL vs INTERSECT vs EXCEPT — differences and performance.

Easy · Database, SQL, Fresher

Explain scalar, multi-row, and correlated subqueries with examples.

Medium · Database, SQL, Fresher

EXISTS vs IN vs JOIN — which should you use and why does NOT IN break with NULLs?

Medium · Database, SQL, Fresher

What is a CTE? How do recursive CTEs work?

Medium · Database, SQL, Fresher

What are window functions? Explain OVER, PARTITION BY, ORDER BY, and frames.

Medium · Database, SQL, Fresher

ROW_NUMBER vs RANK vs DENSE_RANK vs NTILE — what is the difference?

Medium · Database, SQL, Fresher

Explain CASE, COALESCE, NULLIF, and the vendor equivalents IFNULL / NVL / ISNULL.

Easy · Database, SQL, Fresher

How do you write an UPSERT? Explain INSERT ... ON CONFLICT and MERGE.

Medium · Database, SQL, Fresher

Difference between DELETE, TRUNCATE, and DROP. Which can be rolled back?

Easy · Database, SQL, Fresher

What are database views? Differentiate between regular views and materialized views.

Medium · Database, SQL, Fresher

Difference between stored procedures and functions in SQL. When should you use each?

Medium · Database, SQL, Fresher

Explain database normalization up to 3NF.

Easy · Database, Schema Design, Fresher

Explain BCNF, 4NF, and 5NF. When do you normalize beyond 3NF?

Hard · Database, Schema Design, Staff

What is denormalization? When is it the right choice and what does it cost?

Medium · Database, Schema Design, Senior

Star schema vs snowflake schema — how do you model a data warehouse?

Medium · Database, Schema Design, Senior

Surrogate key vs natural key — which should be the primary key?

Medium · Database, Schema Design, Senior

UUID vs auto-increment primary keys — what are the trade-offs at scale?

Medium · Database, Schema Design, Senior

How do you choose column data types? CHAR vs VARCHAR vs TEXT, NUMERIC vs FLOAT, INT sizing.

Medium · Database, Schema Design, Senior

How do you store dates, timestamps, and time zones correctly?

Medium · Database, Schema Design, Senior

Soft delete vs hard delete — which do you pick and what are the pitfalls?

Medium · Database, Schema Design, Senior

How do you track data history? Audit columns, history tables, and slowly changing dimensions.

Medium · Database, Schema Design, Senior

When should you use JSON / JSONB columns in a relational database?

Medium · Database, Schema Design, Senior

How do you design a multi-tenant database? Shared table vs schema-per-tenant vs database-per-tenant.

Hard · Database, Schema Design, Staff

Explain ACID properties with real-world examples.

Easy · Database, Transactions, Fresher

How do you decide transaction boundaries? What belongs inside a transaction and what does not?

Medium · Database, Transactions, Senior

What are dirty read, non-repeatable read, phantom read, and lost update?

Medium · Database, Transactions, Senior

Explain the four SQL isolation levels and which anomalies each one prevents.

Medium · Database, Transactions, Senior

How does MVCC work? How is it different from lock-based concurrency control?

Hard · Database, Transactions, Staff

Explain shared, exclusive, row-level, table-level, and intent locks.

Medium · Database, Transactions, Senior

What is a deadlock? How do databases detect it and how do you prevent it?

Medium · Database, Transactions, Senior

What does SELECT ... FOR UPDATE do? Explain SKIP LOCKED and NOWAIT.

Medium · Database, Transactions, Senior

Differentiate between optimistic locking and pessimistic locking. When should you use each?

Medium · Database, Transactions, Senior

What is write skew? Why does snapshot isolation allow it and how does SERIALIZABLE stop it?

Hard · Database, Transactions, Staff

Explain two-phase commit (2PC) and XA transactions. Why are they avoided in microservices?

Hard · Database, Transactions, Staff

What is the Saga pattern? Compare choreography and orchestration.

Hard · Database, Transactions, Staff

What is the transactional outbox pattern and which problem does it solve?

Hard · Database, Transactions, Staff

How do you make database writes idempotent when clients and queues retry?

Hard · Database, Transactions, Staff

Explain database indexing in detail.

Medium · Database, Indexing, Fresher

Clustered vs non-clustered index — what is the difference?

Medium · Database, Indexing, Senior

B-tree vs hash index — how do they work and when do you use each?

Medium · Database, Indexing, Senior

How do you order columns in a composite index? Explain the leftmost prefix rule.

Medium · Database, Indexing, Senior

What is a covering index / index-only scan?

Medium · Database, Indexing, Senior

What is a partial (filtered) index and when is it a big win?

Medium · Database, Indexing, Senior

What is a function-based / expression index?

Medium · Database, Indexing, Senior

What are selectivity and cardinality? How do they decide whether an index is used?

Medium · Database, Indexing, Senior

Why is my index not being used? Explain sargability.

Hard · Database, Indexing, Staff

What does an index cost? Write amplification, storage, and over-indexing.

Medium · Database, Indexing, Senior

How does full-text search work in a relational database, and when do you move to Elasticsearch?

Hard · Database, Indexing, Staff

How do you optimize a slow SQL query? What does EXPLAIN / EXPLAIN ANALYZE tell you?

Hard · Database, Performance, Staff

Why is SELECT COUNT(*) slow on huge tables and what are the alternatives?

Medium · Database, Performance, Senior

What is table partitioning? Explain range, list, and hash partitioning with partition pruning.

Hard · Database, Performance, Staff

How do you diagnose and fix lock contention and hot rows?

Hard · Database, Performance, Staff

How do you read an execution plan? Seq scan, index scan, nested loop, hash join, merge join.

Hard · Database, Performance, Staff

How does a cost-based optimizer choose a plan? Why do stale statistics cause bad plans?

Hard · Database, Performance, Staff

What is connection pooling? Why is it critical for performance? How does HikariCP work?

Medium · Database, Performance, Senior

How do you insert or update millions of rows efficiently?

Medium · Database, Performance, Senior

What caching strategies sit in front of a database? Cache-aside, read-through, write-through, write-behind.

Medium · Database, Performance, Senior

How do read replicas scale reads, and what breaks when you use them?

Medium · Database, Performance, Senior

A query that was fast yesterday is slow in production today. How do you debug it?

Hard · Database, Performance, Staff

What are the most common ORM performance pitfalls and how do you avoid them?

Medium · Database, Performance, Senior

Does MongoDB support ACID transactions? What are the limits?

Medium · Database, NoSQL, Senior

What are the four families of NoSQL databases and what is each good at?

Easy · Database, NoSQL, Fresher

MongoDB fundamentals: documents, collections, BSON, and the _id field.

Easy · Database, NoSQL, Fresher

Embed vs reference in MongoDB — how do you decide?

Medium · Database, NoSQL, Senior

How does indexing work in MongoDB? Single field, compound, multikey, TTL, and text indexes.

Medium · Database, NoSQL, Senior

How does a MongoDB replica set provide high availability?

Medium · Database, NoSQL, Senior

How do you model data in Cassandra? Partition key vs clustering key.

Hard · Database, NoSQL, Staff

What data structures does Redis provide and what is each used for?

Easy · Database, NoSQL, Fresher

Beyond caching, what do teams use Redis for? Locks, rate limiting, leaderboards, queues.

Medium · Database, NoSQL, Senior

How does Redis persist data? RDB snapshots vs AOF.

Medium · Database, NoSQL, Senior

How does Elasticsearch work? Inverted index, shards, and near-real-time search.

Hard · Database, NoSQL, Staff

When do you use a graph database like Neo4j? Explain nodes, relationships, and Cypher.

Medium · Database, NoSQL, Senior

How do you detect and deal with replication lag?

Medium · Database, Distributed Systems, Staff

Explain the CAP theorem. What does 'choosing CP or AP' really mean?

Medium · Database, Distributed Systems, Senior

Explain strong, eventual, causal, read-your-writes, and monotonic-read consistency.

Hard · Database, Distributed Systems, Staff

Compare synchronous vs asynchronous and leader-follower vs multi-leader vs leaderless replication.

Medium · Database, Distributed Systems, Senior

How do quorum reads and writes work? Why must R + W > N?

Hard · Database, Distributed Systems, Staff

What is database sharding? What are the challenges of horizontal partitioning?

Hard · Database, Distributed Systems, Staff

How do you choose a shard key? What happens when you choose badly?

Hard · Database, Distributed Systems, Staff

How do you reshard or rebalance a live sharded cluster without downtime?

Hard · Database, Distributed Systems, Staff

How do distributed databases elect a leader and fail over? Explain Raft in one minute.

Hard · Database, Distributed Systems, Staff

What is split brain in a database cluster and how do you prevent it?

Hard · Database, Distributed Systems, Staff

What is Change Data Capture? How does Debezium stream database changes?

Hard · Database, Distributed Systems, Staff

How do you control cloud database costs without hurting performance?

Medium · Database, Cloud Databases, Staff

How do you model data in DynamoDB? Partition key, sort key, GSI, LSI, and single-table design.

Hard · Database, Cloud Databases, Staff

DynamoDB capacity: RCU/WCU, on-demand vs provisioned, hot partitions, and throttling.

Hard · Database, Cloud Databases, Staff

Managed cloud database vs self-hosted on VMs — what do you gain and give up?

Medium · Database, Cloud Databases, Architect

AWS RDS: Multi-AZ vs read replicas, backups, and maintenance windows.

Medium · Database, Cloud Databases, Staff

How is Amazon Aurora different from RDS MySQL/PostgreSQL?

Hard · Database, Cloud Databases, Architect

Redshift vs BigQuery vs Snowflake — how do you choose a cloud data warehouse?

Hard · Database, Cloud Databases, Architect

How do you secure a cloud database? IAM authentication, encryption, network isolation, secrets.

Medium · Database, Cloud Databases, Staff

How do you design HA and disaster recovery for a cloud database? RTO vs RPO.

Hard · Database, Cloud Databases, Staff

What is SQL injection and how do you prevent it?

Easy · Database, Administration, Fresher

How do you run schema migrations with zero downtime? Explain expand-contract.

Hard · Database, Administration, Staff

Which database metrics do you monitor and alert on in production?

Medium · Database, Administration, Staff

What backup types exist and how does point-in-time recovery work?

Medium · Database, Administration, Staff

How do you manage database users, roles, and privileges with least privilege?

Easy · Database, Administration, Fresher

How do you handle PII, data masking, and the GDPR right to be forgotten?

Medium · Database, Administration, Staff

Why does each microservice own its database? How do you query across services?

Hard · Database, Architecture, Architect

How do you design an active-active multi-region database deployment?

Hard · Database, Architecture, Architect

What is polyglot persistence? How do you stop it from becoming unmanageable?

Medium · Database, Architecture, Architect

Explain CQRS and event sourcing. When are they worth the complexity?

Hard · Database, Architecture, Architect

Data lake vs data warehouse vs lakehouse, and ETL vs ELT.

Hard · Database, Architecture, Architect

How do you choose the right database for a new service? Walk through your decision framework.

Hard · Database, Architecture, Architect

Reference schema: the tables and sample data used by every query below.

Easy · Database, SQL Queries, Fresher

Write a query to find the second highest salary.

Easy · Database, SQL Queries, Fresher

Write a query to find the Nth highest salary (for example the 3rd).

Medium · Database, SQL Queries, Senior

Find the top 3 highest paid employees in each department.

Medium · Database, SQL Queries, Senior

Find duplicate values in a column (for example duplicate customer cities with counts).

Easy · Database, SQL Queries, Fresher

Delete duplicate rows keeping only the one with the lowest id.

Medium · Database, SQL Queries, Senior

Find employees who earn more than their manager.

Medium · Database, SQL Queries, Fresher

Find customers who have never placed an order.

Easy · Database, SQL Queries, Fresher

Count employees per department, including departments with zero employees.

Easy · Database, SQL Queries, Fresher

Find employees earning more than the average salary of their own department.

Medium · Database, SQL Queries, Senior

Find the highest paid employee in each department, including ties.

Medium · Database, SQL Queries, Senior

Print the full reporting hierarchy under a manager with the depth level.

Hard · Database, SQL Queries, Staff

Compute a running total of order amounts per customer ordered by date.

Medium · Database, SQL Queries, Senior

Compute a 3-order moving average of order amounts per customer.

Medium · Database, SQL Queries, Senior

Compute month-over-month revenue growth percentage.

Hard · Database, SQL Queries, Staff

Show each department's salary cost as a percentage of the company total.

Medium · Database, SQL Queries, Senior

Find the median salary of all employees.

Medium · Database, SQL Queries, Senior

Pivot monthly revenue into one column per month.

Medium · Database, SQL Queries, Senior

Find the best-selling product in each category by revenue.

Medium · Database, SQL Queries, Senior

Find customers who bought one product but never bought another.

Medium · Database, SQL Queries, Senior

Get the first and the most recent order for each customer in a single row.

Medium · Database, SQL Queries, Senior

Find the longest streak of consecutive daily logins per user.

Hard · Database, SQL Queries, Staff

Split monthly orders into new vs returning customers.

Hard · Database, SQL Queries, Staff

Find overlapping bookings for the same room.

Hard · Database, SQL Queries, Staff

List each order with a comma-separated list of its product names.

Easy · Database, SQL Queries, Fresher

Query, filter, and aggregate a JSONB column.

Medium · Database, SQL Queries, Senior

Update employee salaries from a staging table using a join.

Medium · Database, SQL Queries, Senior

Insert a product or update it if it already exists (upsert).

Medium · Database, SQL Queries, Senior

Write keyset (seek) pagination for an ordered list of orders.

Medium · Database, SQL Queries, Senior

What is a database cursor? When should you avoid row-by-row processing?

Medium · Database, Fundamentals, Fresher

What are database triggers? When should you use them vs application logic?

Medium · Database, Fundamentals, Fresher

What is a CROSS JOIN? When is a Cartesian product intentional vs a bug?

Easy · Database, SQL, Fresher

OFFSET vs keyset pagination — tradeoffs, performance, and when to use each.

Medium · Database, SQL, Fresher

How do you pivot rows to columns using conditional aggregation in SQL?

Medium · Database, SQL, Fresher

Explain GROUPING SETS, ROLLUP, and CUBE in SQL.

Hard · Database, SQL, Senior

What is the Entity-Attribute-Value (EAV) model and when is it a bad idea?

Medium · Database, Schema Design, Senior

How do you model hierarchies in SQL? Adjacency list, nested sets, closure table.

Hard · Database, Schema Design, Staff

What are savepoints in SQL transactions and when do you use them?

Medium · Database, Transactions, Staff

Why are long-running transactions dangerous and how do you avoid them?

Hard · Database, Transactions, Staff

What is the Write-Ahead Log (WAL)? How does it guarantee durability?

Hard · Database, Transactions, Staff

Explain GIN, GiST, BRIN, and other specialized index types.

Hard · Database, Indexing, Staff

How do you maintain indexes and handle bloat in PostgreSQL?

Medium · Database, Indexing, Staff

How do you archive and purge old data without killing production?

Medium · Database, Performance, Staff

How do you benchmark a database before choosing or migrating?

Hard · Database, Performance, Staff

How does the MongoDB aggregation pipeline work?

Medium · Database, NoSQL, Senior

How does MongoDB sharding work? Shard key selection and chunk migration.

Hard · Database, NoSQL, Staff

Explain Cassandra architecture: nodes, rings, vnodes, and gossip.

Hard · Database, NoSQL, Staff

What is tunable consistency in Cassandra? ONE, QUORUM, LOCAL_QUORUM, ALL.

Hard · Database, NoSQL, Staff

How does Redis handle eviction policies and TTL expiry?

Medium · Database, NoSQL, Senior

When do you use a time-series database? InfluxDB and TimescaleDB use cases.

Medium · Database, NoSQL, Senior

Wide-column stores vs columnar databases — what is the difference?

Hard · Database, NoSQL, Architect

What is a vector database and when do you need one beyond PostgreSQL pgvector?

Hard · Database, NoSQL, Architect

Explain PACELC. How does it extend CAP for real-world latency tradeoffs?

Hard · Database, Distributed Systems, Staff

How does consistent hashing work for sharding and cache distribution?

Hard · Database, Distributed Systems, Staff

Why are clocks unreliable in distributed databases? Clock skew and logical clocks.

Hard · Database, Distributed Systems, Staff

What is Google Cloud Spanner? How does TrueTime enable external consistency?

Hard · Database, Cloud Databases, Architect

What is Azure Cosmos DB? Explain consistency levels and partitioning.

Hard · Database, Cloud Databases, Architect

When do serverless databases (Aurora Serverless, PlanetScale) make sense?

Medium · Database, Cloud Databases, Architect

List every employee with their manager's name (self-join with NULL handling).

Easy · Database, SQL Queries, Fresher

Find departments with zero employees (anti-join).

Easy · Database, SQL Queries, Fresher

Write a monthly revenue report grouped by product category.

Medium · Database, SQL Queries, Senior

Find customers who placed more than N orders (HAVING practice).

Medium · Database, SQL Queries, Senior

Relational division: customers who bought every product in a category.

Hard · Database, SQL Queries, Staff

Calculate employee tenure using date arithmetic.

Medium · Database, SQL Queries, Senior

Exchange seats between two passengers (swap update pattern).

Hard · Database, SQL Queries, Staff

Bulk update rows using CASE expressions.

Medium · Database, SQL Queries, Senior

Delete orphan records not referenced by any parent.

Medium · Database, SQL Queries, Senior

Delete millions of rows in batches without locking the table.

Hard · Database, SQL Queries, Staff

Calculate the P95 salary using percentile functions.

Hard · Database, SQL Queries, Staff

Compare ROW_NUMBER, RANK, and DENSE_RANK on the same dataset.

Medium · Database, SQL Queries, Senior

Design a relational schema for an e-commerce app (users, products, orders, inventory).

Hard · Database, Fundamentals, Fresher

PostgreSQL vs MySQL (InnoDB): storage, replication, and when to choose each.

Medium · Database, Administration, Senior

What is row-level security (RLS)? When and how do you use it?

Hard · Database, Administration, Staff

How do you define SLA/SLO for the data layer (availability, durability, latency)?

Medium · Database, Administration, Staff

How do you capacity-plan a database (connections, storage, IOPS, growth)?

Hard · Database, Administration, Staff

Walk through a database incident response: replication lag, connection storm, runaway query.

Hard · Database, Administration, Staff

Design order + inventory so you never oversell under concurrent requests.

Hard · Database, Transactions, Staff

What is a data mesh? How is federated data governance different from a central data team?

Hard · Database, Architecture, Architect

How do you migrate from a monolith database using the strangler fig pattern?

Hard · Database, Architecture, Architect

How do you treat the database platform as an internal product for engineering teams?

Medium · Database, Architecture, Architect