DBMS Interview Questions
Common theory questions asked in interviews, with short, clear answers. Good to review after finishing the DBMS Theory Concepts page.
1. What is a DBMS?
A DBMS (Database Management System) is software that stores, organizes, and lets you interact with data reliably — handling storage, retrieval, security, and consistency so applications don't have to manage raw files themselves.
2. What's the difference between a DBMS and RDBMS?
A DBMS just manages data in general. An RDBMS (Relational DBMS) specifically stores data in structured tables with rows and columns, and enforces relationships between them using keys. MySQL and PostgreSQL are RDBMS; MongoDB is a DBMS but not relational.
3. What is SQL?
SQL (Structured Query Language) is the standard language used to create, query, update, and manage data in a relational database.
4. What's the difference between a Primary Key and a Foreign Key?
A Primary Key uniquely identifies each row within its own table and can't be empty or duplicated.
A Foreign Key is a column in one table that references a Primary Key in another table, creating a relationship between them.
Example: id in customers is a primary key. customer_id in orders is a foreign key pointing back to it.
5. What is normalization? Why is it used?
Normalization is organizing tables so each piece of data is stored in exactly one place, avoiding duplication. It's used to prevent inconsistent data, reduce storage waste, and make updates simpler — change a fact once instead of in many rows.
6. What is denormalization?
Denormalization is a database optimization technique that intentionally introduces redundant data into a previously normalized schema to improve read performance and simplify queries.
Unlike normalization, which splits data into separate tables to eliminate duplication and ensure consistency, denormalization merges tables or duplicates attributes to reduce the computational cost of JOIN operations during data retrieval.
7. What are ACID properties?
| Property | Meaning |
|---|---|
| Atomicity | A set of changes either all succeed or none do |
| Consistency | Data always follows defined rules and constraints |
| Isolation | Concurrent operations don't interfere with each other |
| Durability | Saved changes survive crashes or power loss |
8. What is a JOIN? Name the common types.
A JOIN combines rows from two or more tables based on a related column, usually a foreign key.
| Join Type | Returns |
|---|---|
| INNER JOIN | Only rows that match in both tables |
| LEFT JOIN | All rows from the left table, matched rows from the right (NULL if no match) |
| RIGHT JOIN | All rows from the right table, matched rows from the left (NULL if no match) |
| FULL JOIN | All rows from both tables, matched where possible |
9. What's the difference between DELETE, TRUNCATE, and DROP?
| Command | What it does | Can be rolled back? |
|---|---|---|
DELETE | Removes specific rows matching a condition | Yes, if inside a transaction |
TRUNCATE | Removes all rows, resets auto-increment, keeps table structure | Usually no |
DROP | Deletes the entire table, including its structure | No |
10. What is a transaction?
A transaction is a group of one or more SQL operations executed as a single unit — either all of them succeed, or none do. Managed with START TRANSACTION, COMMIT, and ROLLBACK. Used whenever multiple related changes need to happen together, like transferring money between two accounts.
11. What is an index? What's the trade-off?
An index is a data structure that speeds up how fast the database can find rows matching a condition, similar to a book's index. The trade-off: indexes speed up reads (SELECT) but slow down writes (INSERT, UPDATE, DELETE) slightly, since the index has to be updated too.
12. What is a constraint? Name a few common ones.
A constraint is a rule attached to a column that blocks invalid data from being saved. Common ones: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT.
13. What's the difference between WHERE and HAVING?
WHERE filters rows before grouping happens. HAVING filters groups after aggregation (like after a GROUP BY with COUNT or SUM). You can't use aggregate functions in WHERE, but you can in HAVING.
14. What's the difference between a Primary Key and a Unique Key?
Both prevent duplicate values, but a table can have only one Primary Key (and it can't be NULL), while it can have multiple Unique Keys, and those can allow one NULL value depending on the database.
15. What is the difference between a clustered and non-clustered index?
Clustered Index
- Stores the actual table rows in sorted order.
- A table can have only one clustered index.
- Best for range queries, sorting, and retrieving ordered data.
Non-Clustered Index
- Stores a separate index that points to the actual table rows.
- A table can have multiple non-clustered indexes.
- Best for quickly searching specific columns without changing the table's physical order.
A clustered index is like arranging books on a shelf alphabetically, while a non-clustered index is like using the index at the back of a book to find a topic.
16. What is a Foreign Key constraint used for, beyond just linking tables?
It also enforces referential integrity — the database will reject an insert or update that references a non-existent row in the parent table, and can be configured to cascade updates/deletes automatically (ON DELETE CASCADE).
Quick Recap Table
| Topic | Key takeaway |
|---|---|
| DBMS vs RDBMS | RDBMS is relational, structured, uses keys |
| Primary vs Foreign Key | One identifies a row, the other links tables |
| Normalization | Store each fact once |
| ACID | Guarantees safe, reliable transactions |
| Index | Faster reads, slightly slower writes |
| Transaction | All-or-nothing group of operations |