DBMS Interview Questions Every Student Must Know

Database management is a favorite topic in campus placements and entry-level technical interviews because it tests whether you understand how real applications store and retrieve data. You don't need to memorize the textbook — you need to explain the core ideas clearly. Here are the questions that come up most, with concise answers.

Fundamentals

1. What is a DBMS, and how is it different from a file system?

A DBMS (Database Management System) is software that stores, manages, and retrieves data efficiently while handling concurrency, security, and integrity. Unlike a plain file system, it prevents data redundancy, enforces relationships and constraints, and supports multi-user access safely.

2. What is the difference between DBMS and RDBMS?

An RDBMS (Relational DBMS) stores data in tables with defined relationships and enforces rules like primary and foreign keys. A plain DBMS may store data without a relational structure. MySQL, PostgreSQL, and Oracle are RDBMS examples.

3. What are the different types of keys?

• Primary key: uniquely identifies each row; cannot be null.
• Candidate key: any column(s) that could serve as a primary key.
• Foreign key: references the primary key of another table to link them.
• Composite key: a primary key made of two or more columns.
• Unique key: ensures values are unique but allows one null.

Normalization

4. What is normalization, and why do we use it?

Normalization is the process of organizing tables to reduce redundancy and avoid anomalies during insert, update, and delete operations. It splits large tables into smaller related ones.

5. Explain 1NF, 2NF, and 3NF briefly.

• 1NF: each column holds atomic (indivisible) values, no repeating groups.
• 2NF: 1NF plus every non-key attribute depends on the whole primary key (removes partial dependency).
• 3NF: 2NF plus no non-key attribute depends on another non-key attribute (removes transitive dependency).

6. What is denormalization?

Deliberately adding redundancy back into a database to improve read performance, at the cost of some write complexity. It's a trade-off used in read-heavy systems.

Transactions & ACID

7. What are the ACID properties?

• Atomicity: a transaction is all-or-nothing.
• Consistency: a transaction moves the database from one valid state to another.
• Isolation: concurrent transactions don't interfere with each other.
• Durability: once committed, changes survive crashes.

8. What is a transaction?

A single logical unit of work made of one or more operations that must either all succeed (commit) or all fail (rollback). The classic example is transferring money between two bank accounts.

SQL & queries

9. What is the difference between DELETE, TRUNCATE, and DROP?

DELETE removes specific rows and can be rolled back. TRUNCATE removes all rows quickly and usually can't be rolled back. DROP removes the entire table structure.

10. What is the difference between WHERE and HAVING?

WHERE filters rows before grouping; HAVING filters groups after a GROUP BY aggregation.

11. What are the different types of JOINs?

• INNER JOIN: rows matching in both tables.
• LEFT JOIN: all rows from the left table plus matches from the right.
• RIGHT JOIN: all rows from the right table plus matches from the left.
• FULL OUTER JOIN: all rows from both tables, matched where possible.

12. What is the difference between primary key and unique key?

A table can have only one primary key (no nulls), but multiple unique keys (each allowing one null). Both enforce uniqueness.

Performance & design

13. What is an index, and why use it?

An index is a data structure that speeds up data retrieval, like the index of a book. It makes reads faster but slightly slows writes and uses extra storage, so you index the columns you search on most.

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

SQL databases are relational, use fixed schemas and tables, and are great for structured data and complex queries. NoSQL databases (document, key-value, graph) are flexible-schema and scale horizontally, suited to large, unstructured, or rapidly changing data.

15. What is a stored procedure?

A precompiled set of SQL statements stored in the database that can be reused and executed by name — useful for repeated logic and reducing network traffic.

How to prepare

DBMS questions reward clear definitions plus a one-line example. For each concept above, practice giving the definition and a real example (a bank transfer for ACID, a book index for indexing). Examples are what convince an interviewer you truly understand it.

← Back to all articles