Interview Q&A / SQL & DBMS

1. What are the ACID properties of a transaction?

   ACID stands for Atomicity, Consistency, Isolation and Durability. A transaction either fully happens or not at all, leaves the data valid according to its rules, behaves as if it ran without interference from other transactions, and stays saved once committed, even if the server crashes.

   BeginnerIn-depth3 min read
2. What is the difference between a clustered and a non-clustered index?

   A clustered index decides the physical order in which table rows are stored, so the table itself is the index and there can be only one. A non-clustered index is a separate structure holding the indexed values plus a pointer back to the row, and a table can have many.

   AdvancedIn-depth4 min read
3. What are insertion, update and deletion anomalies?

   They are problems in a badly designed table where redundant data forces you to add dummy values, edit many rows for one change, or lose unrelated facts when deleting a row. Normalization removes them.

   BeginnerQuick answer1 min read
4. What does a table need to be in First Normal Form, and what breaks it?

   A table is in 1NF when every column holds a single atomic value, there are no repeating groups, and each row is unique. Storing a comma-separated list of values in one cell breaks 1NF.

   BeginnerQuick answer1 min read
5. What is a composite key and when do you need one?

   A composite key is a key made of two or more columns that together identify a row. It is needed when no single column is unique, such as a student and subject pair in a marks table.

   BeginnerQuick answer1 min read
6. What is a functional dependency in DBMS?

   A functional dependency X -> Y means that if two rows have the same X value they must have the same Y value, so X determines Y. Normalization is built on finding and organizing these dependencies.

   BeginnerQuick answer1 min read
7. What is the difference between a super key, a candidate key and a primary key?

   A super key is any set of columns that uniquely identifies a row. A candidate key is a minimal super key with no removable column. The primary key is the one candidate key you choose to identify rows.

   BeginnerQuick answer1 min read
8. What are dirty reads, non-repeatable reads and phantom reads?

   A dirty read sees another transaction's uncommitted data. A non-repeatable read gets a different value for the same row on a second read. A phantom read gets a different set of rows for the same query.

   IntermediateQuick answer1 min read
9. What are the four transaction isolation levels in SQL?

   From weakest to strongest they are READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE. Higher levels prevent more anomalies such as dirty reads, non-repeatable reads and phantoms, at the cost of concurrency.

   IntermediateQuick answer1 min read
10. What is a deadlock in a database and how is it handled?

    A deadlock happens when two transactions each hold a lock the other needs, so neither can proceed. The database detects the cycle and aborts one transaction as the victim, and the application should retry it.

    IntermediateQuick answer1 min read
11. What is a partial dependency and how does 2NF remove it?

    A partial dependency is when a non-key column depends on only part of a composite primary key. 2NF requires 1NF plus no partial dependencies, which you achieve by moving that column to a table keyed by that part.

    IntermediateQuick answer1 min read
12. What is denormalization and when would you use it?

    Denormalization deliberately adds redundant data, such as copied columns or precomputed totals, to avoid joins and speed up reads. It trades storage and write complexity for read performance, and suits read-heavy reporting.

    IntermediateQuick answer1 min read
13. What is the difference between a surrogate key and a natural key?

    A natural key is a real-world attribute that is unique, like email or Aadhaar number. A surrogate key is a meaningless generated value, like an auto-increment id. Surrogate keys stay stable when business data changes.

    IntermediateQuick answer1 min read
14. What is the difference between optimistic and pessimistic locking?

    Pessimistic locking locks the row up front so others must wait, assuming conflicts are likely. Optimistic locking takes no lock and checks at update time, usually with a version number, whether someone else changed the row.

    IntermediateQuick answer1 min read
15. What is BCNF and how is it stricter than 3NF?

    A table is in BCNF if, for every non-trivial dependency X -> Y, X is a superkey. 3NF also lets Y be a prime attribute, which BCNF does not allow, so BCNF removes a few anomalies that 3NF misses.

    AdvancedQuick answer1 min read
16. What is MVCC in databases?

    MVCC keeps multiple versions of a row so readers see a consistent snapshot while writers create new versions. This lets reads and writes proceed without blocking each other. PostgreSQL, MySQL InnoDB and Oracle use it.

    AdvancedQuick answer1 min read