Interview Questions — DBMS

Fresher Level Questions

Q1. What is the difference between SQL and NoSQL databases?

Q2. What is an Index? When should you use it?

An index is a data structure that improves the speed of data retrieval on a database table. It works like a book's index — instead of scanning every page, you jump directly to the relevant section.

Use an index when:

• A column is frequently used in WHERE clauses
• A column is used in JOIN conditions
• A column is used in ORDER BY or GROUP BY
• The table has a large number of rows

Avoid when:

• Table is small
• Column has low cardinality (e.g., boolean or gender)
• Table undergoes frequent INSERT/UPDATE/DELETE operations

Q3. What is a stored procedure vs. a function?

Q4. Explain different types of database relationships.

Q5. What are database transactions? Explain with an example.

A transaction is a logical unit of work that consists of one or more SQL operations that must all succeed or all fail together.

Example — Online Banking:

Intermediate Level Questions

Q6. What is query optimization? How does a query optimizer work?

Query optimization is the process of selecting the most efficient execution plan for a given SQL query. The optimizer evaluates multiple possible plans and chooses the one with the lowest estimated cost (I/O, CPU, memory).

Steps:

1. Parsing: SQL is parsed into an internal representation (parse tree)
2. Logical Optimization: Apply algebraic transformations (push selections down, eliminate redundancy)
3. Physical Plan Generation: Generate multiple candidate physical plans
4. Cost Estimation: Estimate cost using statistics (row counts, index availability, data distribution)
5. Plan Selection: Choose the plan with minimum estimated cost
6. Execution: Execute the chosen plan

Q7. What is deadlock detection using a Wait-For Graph?

A Wait-For Graph (WFG) is used to detect deadlocks. Nodes represent transactions; a directed edge T1 → T2 means T1 is waiting for a resource held by T2.

Q8. Explain MVCC (Multi-Version Concurrency Control).

MVCC maintains multiple versions of each data item so that readers never block writers and writers never block readers.

• Every write creates a new version of the row with a timestamp.
• Readers see the most recent committed version as of their transaction start time.
• Used by PostgreSQL, Oracle, MySQL InnoDB.

Advantages:

• High read concurrency
• Non-blocking reads
• Consistent snapshots

Q9. What is the CAP Theorem?

The CAP Theorem states that a distributed database can guarantee at most two of the following three properties simultaneously:

• Consistency: Every read receives the most recent write
• Availability: Every request receives a response (not necessarily the latest)
• Partition Tolerance: System continues operating despite network partitions

Q10. How would you design a database for an e-commerce platform?

Core Entities:

Key design decisions:

• Use foreign keys to enforce referential integrity
• Index on UserID (Orders), ProductID (OrderItems), Email (Users)
• Separate OrderItems to handle multiple products per order (normalization)
• Store UnitPrice in OrderItems (not just from Products) to preserve historical pricing

Advanced Level Questions

Q11. What is database sharding?

Sharding is a horizontal partitioning technique where data is distributed across multiple database servers (shards) based on a shard key.

Advantages: Linear horizontal scalability, improved performance. Challenges: Cross-shard queries are expensive, re-sharding is complex, no cross-shard transactions.

Q12. Explain the differences between OLTP and OLAP systems.

Q13. What is eventual consistency in distributed systems?

Eventual consistency is a consistency model used in distributed databases where it is guaranteed that, if no new updates are made, all replicas will eventually converge to the same value.

It is a weaker guarantee than strong consistency but allows higher availability and partition tolerance (AP systems in CAP theorem).

Example: DNS, Amazon DynamoDB, Apache Cassandra.