DBMS Interview Questions and Database Notes

Database fundamentals: transactions, normalization, SQL, and the ACID guarantees that keep real data trustworthy.

43 topics across 6 categories, 174 interview questions

Top 10 DBMS interview questions with answers

  1. 1.What problems does a DBMS solve that a plain file system doesn't?

    Expect a clear list: concurrency control, structure enforcement, efficient search via indexing, and crash recovery.

    What Is a DBMS?
  2. 2.What's the difference between a candidate key and a primary key?

    A table can have multiple candidate keys (each independently sufficient to uniquely identify a row); the primary key is the one officially chosen from among them.

    Keys in the Relational Model
  3. 3.What is normalization, and why does it matter?

    The process of restructuring a schema through defined stages (normal forms) to eliminate redundancy and the anomalies it causes.

    Understanding Normalization
  4. 4.What's the difference between 3NF and BCNF?

    BCNF requires the left side of every functional dependency to be a superkey, with no exception; 3NF allows a narrow exception when the right side is part of some candidate key.

    Boyce-Codd Normal Form (BCNF)
  5. 5.Name and briefly explain all four ACID properties.

    Atomicity (all-or-nothing), Consistency (valid state to valid state), Isolation (concurrent transactions don't interfere), Durability (committed changes survive crashes).

    ACID Properties: Complete Overview
  6. 6.Name the four standard isolation levels, from weakest to strongest.

    Read Uncommitted, Read Committed, Repeatable Read, Serializable.

    Isolation in ACID Transactions
  7. 7.What's the difference between INNER JOIN and LEFT JOIN?

    INNER JOIN only returns rows with a match in both tables; LEFT JOIN returns every row from the left table regardless, with NULLs where there's no match on the right.

    SQL Joins
  8. 8.What is an index, and why does it help?

    A separate sorted data structure that lets the database find matching rows without scanning the entire table.

    SQL Indexes
  9. 9.What's the difference between DELETE, TRUNCATE, and DROP?

    DELETE removes matching rows one at a time and keeps the table; TRUNCATE removes all rows at once (faster) and keeps the table; DROP removes the table entirely, structure and all.

    Other SQL Commands
  10. 10.What is a dirty read?

    Reading another transaction's uncommitted changes, which might later be rolled back and never actually take effect.

    Problems in Concurrency Control

All 174 DBMS interview questions

174 questions, grouped by topic. Each one is answered on its topic page, next to the explanation it comes from.

ACID Properties

What Is a Transaction in a Database?

  1. What is a transaction, in one sentence?
  2. What do BEGIN, COMMIT, and ROLLBACK each do?
  3. Why wrap multiple statements in one transaction instead of running them separately?
  4. What happens if the application crashes mid-transaction, before COMMIT?
Answers and notes

Atomicity in ACID Transactions

  1. What does atomicity actually guarantee, in one sentence?
  2. How does a database implement atomicity under the hood?
  3. What happens if the database crashes in the middle of a transaction?
  4. Give a real example of why atomicity matters.
  5. How is atomicity different from consistency?
Answers and notes

Consistency in ACID Transactions

  1. What does consistency actually guarantee?
  2. How is consistency different from atomicity?
  3. Who enforces consistency, the application or the database?
  4. Give an example of a consistency violation the database would block.
Answers and notes

Isolation in ACID Transactions

  1. Name the four standard isolation levels, from weakest to strongest.
  2. What's a dirty read, and which isolation level allows it?
  3. What's the difference between a non-repeatable read and a phantom read?
  4. What's the tradeoff of using Serializable everywhere?
Answers and notes

Durability in ACID Transactions

  1. What does durability guarantee?
  2. How is durability implemented?
  3. How is durability different from atomicity?
  4. Why can't a database just keep committed data in memory for speed?
Answers and notes

ACID Properties: Complete Overview

  1. Name and briefly explain all four ACID properties.
  2. Which ACID property would fail if two people booked the last item in stock at the same time and both succeeded?
  3. Give an example of one transaction that would test all four properties at once.
  4. Why do some databases intentionally relax ACID guarantees?
Answers and notes

Introduction

What Is a DBMS?

  1. What problems does a DBMS solve that a plain file system doesn't?
  2. What's the difference between a database and a DBMS?
  3. Name some real DBMS software you've used.
  4. What's a schema?
Answers and notes

DBA (Database Administrator) and Functions

  1. What are the main responsibilities of a DBA?
  2. How would you investigate a slow query?
  3. Why is testing backups important, not just taking them?
  4. What's the principle of least privilege, and why does it matter for database security?
Answers and notes

DBMS Architecture (1-Tier, 2-Tier, 3-Tier)

  1. What's the difference between 2-tier and 3-tier architecture?
  2. Why is 3-tier architecture considered more secure?
  3. Give a real example of each architecture.
  4. What are the advantages of separating the application tier from the database tier?
Answers and notes

Database Languages (DDL, DML, DCL, TCL)

  1. What's the difference between DDL and DML?
  2. Is `TRUNCATE` DDL or DML, and why does that matter?
  3. What do `GRANT` and `REVOKE` do?
  4. Why would you wrap multiple DML statements in a transaction?
Answers and notes

Data Abstraction (Physical, Logical, View Levels)

  1. What are the three levels of data abstraction in a DBMS?
  2. What's the difference between physical and logical data independence?
  3. Why does this separation matter in practice?
  4. How does a SQL `VIEW` relate to this concept?
Answers and notes

Important DBMS Terms

  1. What's the difference between a schema and an instance?
  2. What is metadata, and where is it stored?
  3. What's a tuple, in relational database terms?
  4. Name some data models other than the relational model.
Answers and notes

RDBMS

What Is an RDBMS?

  1. What makes a database 'relational'?
  2. Is every DBMS an RDBMS?
  3. What's the role of a foreign key in an RDBMS?
  4. Why use multiple related tables instead of one big table?
Answers and notes

Introduction to the ER Model

  1. What are the three core components of an ER model?
  2. What's the difference between one-to-many and many-to-many relationships?
  3. Why can't a many-to-many relationship be represented with a single foreign key?
  4. Walk me through modeling a real-world scenario as an ER diagram.
Answers and notes

How to Draw an ER Diagram

  1. Walk me through how you'd design a schema for [some scenario].
  2. How do you represent a many-to-many relationship in an actual database?
  3. What notation do you use for ER diagrams?
  4. What do you do after identifying a many-to-many relationship?
Answers and notes

Introduction to the Relational Model

  1. Who proposed the relational model, and roughly when?
  2. What does it mean for a value to be atomic in the relational model?
  3. What's the difference between degree and cardinality of a relation?
  4. Why does every row in a relation need to be unique?
Answers and notes

Keys in the Relational Model

  1. What's the difference between a candidate key and a primary key?
  2. What's the difference between a super key and a candidate key?
  3. What is a foreign key, and what can it reference?
  4. When would you use a composite key?
  5. Why might a surrogate key be preferred over a natural key as a primary key?
Answers and notes

RDBMS Design

Features of a Good Relational Database Design

  1. What are the three classic anomalies a poor schema design can cause?
  2. Give an example of an update anomaly.
  3. What does 'lossless decomposition' mean?
  4. How does normalization relate to avoiding these anomalies?
Answers and notes

Functional Dependencies and Their Types

  1. What is a functional dependency, in your own words?
  2. What's the difference between a partial and a full functional dependency?
  3. What's a transitive dependency, and why does it matter?
  4. Identify the functional dependencies in this table.
Answers and notes

Introduction to Decomposition

  1. What does it mean for a decomposition to be lossless?
  2. What does dependency preservation mean, and why does it matter?
  3. How do you know if a decomposition is lossless?
  4. Can a decomposition be lossless but not dependency-preserving?
Answers and notes

Understanding Normalization

  1. What is normalization, and why does it matter?
  2. Walk me through normalizing this table.
  3. What's the tradeoff of normalizing a schema?
  4. What normal form do most production schemas target?
Answers and notes

First Normal Form (1NF)

  1. What does First Normal Form require?
  2. Is a table with a `phone1`, `phone2`, `phone3` column set in 1NF?
  3. How do you fix a 1NF violation?
  4. Why does 1NF matter for querying?
Answers and notes

Second Normal Form (2NF)

  1. What does Second Normal Form require, beyond 1NF?
  2. When is 2NF automatically satisfied?
  3. Identify the 2NF violation in this table.
  4. How do you fix a partial dependency?
Answers and notes

Third Normal Form (3NF)

  1. What does Third Normal Form require, beyond 2NF?
  2. Identify the transitive dependency in this table.
  3. What's the difference between 2NF and 3NF?
  4. Why is 3NF often considered the practical target for real schemas?
Answers and notes

Boyce-Codd Normal Form (BCNF)

  1. What's the difference between 3NF and BCNF?
  2. Give an example of a table in 3NF but not BCNF.
  3. Is BCNF always achievable without losing dependency preservation?
  4. Why do many real schemas stop at 3NF instead of going to BCNF?
Answers and notes

SQL

Introduction to SQL

  1. What does SQL stand for, and what is it used for?
  2. What are DDL, DML, DCL, and TCL?
  3. Is SQL the same across every database?
  4. What's the difference between DELETE and DROP?
Answers and notes

SQL Data Types

  1. Why shouldn't you store money in a FLOAT column?
  2. What's the difference between CHAR and VARCHAR?
  3. What's DECIMAL(10, 2) mean?
  4. When would you use TEXT instead of VARCHAR?
Answers and notes

Basic SQL Commands

  1. What are the five most commonly used SQL commands?
  2. What happens if you run UPDATE or DELETE without a WHERE clause?
  3. How would you safely test a DELETE before running it?
  4. What's the difference between DELETE and UPDATE?
Answers and notes

Other SQL Commands

  1. What's the difference between DELETE, TRUNCATE, and DROP?
  2. Why is TRUNCATE usually faster than DELETE with no condition?
  3. Can TRUNCATE be rolled back?
  4. When would you use ALTER TABLE?
Answers and notes

SQL Aggregate Functions

  1. Name the five common aggregate functions.
  2. What's the difference between WHERE and HAVING?
  3. Why can't you use an aggregate function in a WHERE clause?
  4. How would you find the customer with the highest total spend?
Answers and notes

SQL Joins

  1. What's the difference between INNER JOIN and LEFT JOIN?
  2. How would you find customers who have never placed an order?
  3. What does a FULL JOIN return?
  4. Why use joins instead of storing all the data in one big table?
Answers and notes

SQL Views

  1. What is a view?
  2. Does a view store data?
  3. Why would you use a view instead of just running the full query every time?
  4. What's the tradeoff of a materialized view versus a regular view?
Answers and notes

SQL Indexes

  1. What is an index, and why does it help?
  2. What's the tradeoff of adding an index?
  3. When would you NOT want to add an index?
  4. How would you check if a slow query is missing an index?
Answers and notes

SQL Subqueries

  1. What is a subquery?
  2. What's a correlated subquery?
  3. Can every subquery be rewritten as a JOIN?
  4. Give an example of when you'd need a correlated subquery instead of a simple one.
Answers and notes

SQL Stored Procedures

  1. What is a stored procedure?
  2. What's a real benefit of using a stored procedure?
  3. What's a real downside?
  4. When might a team deliberately avoid stored procedures?
Answers and notes

Transaction Processing

What Is Transaction Processing?

  1. What is transaction processing responsible for?
  2. What's a lost update, and how do you prevent it?
  3. What is the difference between OLTP and OLAP?
  4. What are the states of a transaction?
Answers and notes

Schedules and Their Types

  1. What is a schedule?
  2. What's the difference between a serial and a serializable schedule?
  3. Why not just always use serial schedules to guarantee correctness?
  4. How would you recognize a non-serializable schedule?
Answers and notes

Serializability

  1. What does it mean for a schedule to be serializable?
  2. What's the difference between conflict serializability and view serializability?
  3. Which one do real databases actually use?
  4. How do you check if two operations conflict?
Answers and notes

What Is Concurrency Control?

  1. What is concurrency control?
  2. What's the difference between pessimistic and optimistic concurrency control?
  3. When would you choose optimistic over pessimistic concurrency control?
  4. What happens when an optimistic concurrency check fails at commit time?
Answers and notes

Problems in Concurrency Control

  1. What is a dirty read?
  2. What is a lost update, concretely?
  3. What is an unrepeatable read?
  4. Which isolation level would you need to prevent lost updates entirely?
Answers and notes

Locking Protocols

  1. What are the two phases in Two-Phase Locking?
  2. What does 2PL guarantee, and why?
  3. What's the difference between basic 2PL and strict 2PL?
  4. How can 2PL lead to a deadlock?
Answers and notes

Deadlocks in Transaction Processing

  1. What is a deadlock?
  2. How does a database detect a deadlock?
  3. How does a database resolve a detected deadlock?
  4. What's a practical way application code can reduce deadlock frequency?
Answers and notes

Recoverability of Schedules

  1. What does it mean for a schedule to be recoverable?
  2. What goes wrong in an unrecoverable schedule?
  3. What's a cascading rollback?
  4. How is recoverability different from serializability?
Answers and notes

Frequently asked questions

Where do these dbms interview questions come from?

Each of the 174 questions comes from the "What interviewers actually ask" section of one of 43 DBMS topic notes. Every question links to its topic page, where it is answered next to the explanation it tests.

What does the DBMS section cover?

43 topics across 6 categories: Database fundamentals: transactions, normalization, SQL, and the ACID guarantees that keep real data trustworthy.

Is it free to read?

Yes. Every article is free to read with no account required. Creating a free DevsUnite account lets you mark topics as read and track your progress across every Core Subjects article.