If you are preparing for campus placements or a data role in India, DBMS interview questions are almost guaranteed to show up. Whether you are targeting a software engineer role at a service company, a backend position at a product startup, or an analyst seat at a fintech, interviewers use database fundamentals to check whether you can model data cleanly, write correct SQL, and reason about consistency under load. This guide collects 50+ of the most commonly asked DBMS interview questions with concise, correct answers, small SQL snippets, and comparison tables, organised from basics to advanced topics so you can revise fast the night before a round.
The questions below are grouped by subtopic. Freshers should focus on the Basics, Keys, Normalization, and Joins sections. Experienced candidates should go deep on Transactions, Concurrency Control, Indexing, and the scenario questions near the end.
DBMS Basics
1. What is a DBMS?
A Database Management System is software that lets you create, store, retrieve, update, and manage data in a structured way. It sits between the raw data files and the application, and provides data independence, security, concurrency control, backup, and recovery. Examples include MySQL, PostgreSQL, Oracle, and SQL Server.
2. What is the difference between DBMS and a file system?
A file system stores data as plain files with no built-in structure. It suffers from data redundancy, inconsistency, poor concurrent access, weak security, and no easy querying. A DBMS solves these with controlled redundancy, integrity constraints, multi-user concurrency, access control, and a query language. In short, a DBMS gives you data independence and integrity that a raw file system cannot.
3. What are the different levels of data abstraction?
There are three levels:
- Physical level: how data is actually stored on disk (files, indexes, blocks). Hidden from users.
- Logical level: what data is stored and the relationships between it (tables and columns).
- View level: the customised slice of data an individual user or application sees.
This separation is called the three-schema architecture and is what enables data independence.
4. What is data independence?
Data independence is the ability to change the schema at one level without affecting the level above it. Logical data independence means you can change the logical schema (add a column) without rewriting applications. Physical data independence means you can change storage (add an index, change file organisation) without changing the logical schema.
5. What are the types of database languages?
| Language | Full form | Common commands |
|---|---|---|
| DDL | Data Definition Language | CREATE, ALTER, DROP, TRUNCATE |
| DML | Data Manipulation Language | SELECT, INSERT, UPDATE, DELETE |
| DCL | Data Control Language | GRANT, REVOKE |
| TCL | Transaction Control Language | COMMIT, ROLLBACK, SAVEPOINT |
6. What is a NULL value?
NULL represents missing, unknown, or inapplicable data. It is not the same as zero or an empty string. Comparisons with NULL use IS NULL or IS NOT NULL rather than =, because any arithmetic or comparison with NULL yields NULL (unknown).
7. Who is a DBA and what do they do?
A Database Administrator manages the database environment: schema design decisions, user access and roles, performance tuning, backup and recovery planning, capacity planning, and security. In many Indian teams the DBA role overlaps with backend and DevOps responsibilities.
DBMS vs RDBMS
8. What is the difference between DBMS and RDBMS?
An RDBMS is a DBMS that stores data in related tables and enforces relationships between them, following E. F. Codd's relational rules. Every RDBMS is a DBMS, but not every DBMS is relational.
| Aspect | DBMS | RDBMS |
|---|---|---|
| Data storage | Files or navigational structures | Tables (rows and columns) |
| Relationships | Not enforced | Enforced via foreign keys |
| Normalization | Not supported | Supported |
| Constraints | Minimal | Rich (PK, FK, UNIQUE, CHECK) |
| Multi-user | Limited | Strong concurrency support |
| Examples | XML stores, file-based systems | MySQL, PostgreSQL, Oracle |
9. What is a relation, tuple, and attribute?
A relation is a table. A tuple is a single row (a record). An attribute is a column (a field). The number of tuples is the cardinality and the number of attributes is the degree of the relation.
10. What is the difference between intension and extension?
Intension (the schema) is the fixed description of the database: table definitions, constraints, and structure. It changes rarely. Extension is the actual data stored at a given moment, that is, the current set of tuples. It changes constantly as rows are inserted and deleted.
Database Keys
11. What is a super key?
A super key is any set of one or more attributes that can uniquely identify a row in a table. It may contain extra attributes that are not strictly needed for uniqueness.
12. What is a candidate key?
A candidate key is a minimal super key, meaning no attribute can be removed without losing uniqueness. A table can have multiple candidate keys.
13. What is a primary key?
A primary key is the candidate key chosen to uniquely identify each row. It cannot be NULL and must be unique. A table has exactly one primary key.
CREATE TABLE student (
roll_no INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(120) UNIQUE
);
14. What is a foreign key?
A foreign key is an attribute in one table that references the primary key of another table, enforcing referential integrity. It ensures you cannot insert a child row that points to a non-existent parent.
CREATE TABLE enrollment (
id INT PRIMARY KEY,
roll_no INT,
course_id INT,
FOREIGN KEY (roll_no) REFERENCES student(roll_no)
);
15. What is the difference between a primary key and a unique key?
Both enforce uniqueness. A primary key does not allow NULLs and there is only one per table. A unique key allows one NULL (or more, depending on the database) and a table can have several unique keys. A candidate key that is not chosen as primary is called an alternate key.
16. What is a composite key?
A composite key is a primary or candidate key made up of two or more columns, used when no single column is unique on its own. For example, in enrollment the pair (roll_no, course_id) may together be unique.
17. What is a surrogate key?
A surrogate key is an artificial, system-generated identifier (like an auto-increment id or UUID) with no business meaning, used as the primary key instead of a natural attribute. It is stable and simple, which is why most application tables use one.
Normalization
18. What is normalization and why do we need it?
Normalization is the process of organising columns and tables to reduce data redundancy and avoid update, insert, and delete anomalies. It splits large tables into smaller related ones connected by keys. The trade-off is that heavily normalized schemas need more joins at read time.
19. What are the normal forms?
| Normal form | Rule it enforces |
|---|---|
| 1NF | Atomic (indivisible) values, no repeating groups, each row unique |
| 2NF | 1NF and no partial dependency (non-key attributes depend on the whole composite key) |
| 3NF | 2NF and no transitive dependency (non-key attributes do not depend on other non-key attributes) |
| BCNF | 3NF and for every functional dependency, the left side is a super key |
20. Explain 1NF with an example.
1NF requires atomic values. This table violates 1NF because courses holds multiple values:
| roll_no | name | courses |
|---|---|---|
| 1 | Anita | DBMS, OS |
Converted to 1NF, each course becomes its own row:
| roll_no | name | course |
|---|---|---|
| 1 | Anita | DBMS |
| 1 | Anita | OS |
21. Explain 2NF with an example.
2NF applies when the primary key is composite. Consider (roll_no, course_id) as the key with course_name as a column. Since course_name depends only on course_id (part of the key), it is a partial dependency. To reach 2NF, move course_id and course_name into a separate course table.
22. Explain 3NF with an example.
3NF removes transitive dependencies. If a student table stores (roll_no, city, city_pincode), then city_pincode depends on city, which depends on roll_no. That is transitive. To reach 3NF, move city and city_pincode into a separate city table.
23. What is BCNF and how is it stricter than 3NF?
BCNF (Boyce-Codd Normal Form) requires that for every non-trivial functional dependency X to Y, X must be a super key. A table can be in 3NF but not in BCNF when a non-prime attribute determines part of a candidate key. BCNF eliminates that last class of anomalies, sometimes at the cost of an extra table.
24. What is denormalization?
Denormalization deliberately introduces redundancy (for example, storing a precomputed total or duplicating a name) to reduce expensive joins and speed up reads. It is common in reporting tables and read-heavy analytics, where read performance matters more than storage or write simplicity.
ER Model
25. What is an ER model?
The Entity-Relationship model is a high-level, visual way to design a database. It represents real-world objects as entities, their properties as attributes, and the associations between entities as relationships. It is later mapped to relational tables.
26. What are the types of attributes in an ER model?
- Simple vs composite (a name split into first and last).
- Single-valued vs multi-valued (one date of birth vs multiple phone numbers).
- Stored vs derived (date of birth stored, age derived from it).
- Key attribute, which uniquely identifies an entity.
27. What is cardinality in an ER model?
Cardinality describes how many instances of one entity relate to another: one-to-one, one-to-many, and many-to-many. A student and their unique roll number is one-to-one; a department and its employees is one-to-many; students and courses is many-to-many.
28. What is a weak entity?
A weak entity cannot be uniquely identified by its own attributes alone and depends on a related strong entity. It is identified using a partial key combined with the strong entity's key. For example, a dependent of an employee is a weak entity.
Joins
29. What is a JOIN?
A join combines rows from two or more tables based on a related column. It is how normalized data is stitched back together at query time.
30. What are the types of joins?
| Join | Returns |
|---|---|
| INNER JOIN | Only rows with matches in both tables |
| LEFT JOIN | All rows from the left table, NULLs where no right match |
| RIGHT JOIN | All rows from the right table, NULLs where no left match |
| FULL OUTER JOIN | All rows from both tables, NULLs where no match |
| CROSS JOIN | Cartesian product of both tables |
| SELF JOIN | A table joined with itself |
SELECT s.name, c.course_name
FROM student s
INNER JOIN enrollment e ON s.roll_no = e.roll_no
INNER JOIN course c ON e.course_id = c.course_id;
31. What is the difference between INNER JOIN and LEFT JOIN?
INNER JOIN returns only matching rows. LEFT JOIN returns every row from the left table and fills NULL for unmatched right-side columns. A common trap: adding a WHERE filter on a right-table column silently turns a LEFT JOIN into an INNER JOIN, because NULLs fail the condition. Put such conditions in the ON clause instead.
32. What is a self join and when is it used?
A self join joins a table to itself using aliases. It is used for hierarchical data, such as finding each employee's manager when both are rows in the same employee table.
SELECT e.name AS employee, m.name AS manager
FROM employee e
LEFT JOIN employee m ON e.manager_id = m.id;
33. What is the difference between UNION and UNION ALL?
UNION combines two result sets and removes duplicates, which requires a sort and is slower. UNION ALL keeps all rows including duplicates and is faster. Use UNION ALL when you know the sets do not overlap or duplicates are acceptable.
34. What is the difference between WHERE and HAVING?
WHERE filters individual rows before grouping and cannot use aggregate functions. HAVING filters groups after GROUP BY and can use aggregates like COUNT and SUM.
SELECT course_id, COUNT(*) AS total
FROM enrollment
GROUP BY course_id
HAVING COUNT(*) > 50;
Interview rounds love mixing conceptual and hands-on SQL, so rehearse explaining your reasoning out loud. A structured way to build that fluency is a timed session with the Goodspace AI Mock Interview, which asks follow-up questions the way a real panel does.
Transactions and ACID
35. What is a transaction?
A transaction is a logical unit of work made up of one or more SQL operations that must all succeed or all fail together. The classic example is transferring money between two bank accounts: the debit and credit must both happen or neither should.
36. What are the ACID properties?
- Atomicity: all operations complete or none do.
- Consistency: the database moves from one valid state to another, respecting all constraints.
- Isolation: concurrent transactions do not interfere with each other's intermediate state.
- Durability: once committed, changes survive crashes and power loss.
37. What are COMMIT, ROLLBACK, and SAVEPOINT?
COMMIT makes all changes in a transaction permanent. ROLLBACK undoes changes since the transaction began (or since a savepoint). SAVEPOINT marks a point you can partially roll back to without discarding the whole transaction.
BEGIN;
UPDATE account SET balance = balance - 500 WHERE id = 1;
SAVEPOINT after_debit;
UPDATE account SET balance = balance + 500 WHERE id = 2;
COMMIT;
38. What are the states of a transaction?
Active, Partially Committed (last statement executed but not yet committed), Committed, Failed, and Aborted (rolled back). Understanding this lifecycle helps explain how recovery works after a crash.
39. What is a transaction log?
A transaction log records every change before it is applied, so the database can redo committed transactions and undo incomplete ones after a failure. This write-ahead logging is the mechanism behind the Durability guarantee.
Concurrency Control and Locks
40. What is concurrency control and why is it needed?
Concurrency control manages simultaneous transactions so they do not corrupt data or read inconsistent values. Without it you get problems like dirty reads, lost updates, and non-repeatable reads. Common techniques are locking, timestamp ordering, and optimistic concurrency control.
41. What are shared and exclusive locks?
A shared (read) lock lets multiple transactions read a data item at the same time but blocks writes. An exclusive (write) lock allows a single transaction to read and write and blocks all others. This is how a database serialises conflicting access.
42. What is two-phase locking (2PL)?
2PL is a protocol that guarantees serializability. Each transaction has a growing phase, where it acquires locks and releases none, and a shrinking phase, where it releases locks and acquires none. Strict 2PL holds all exclusive locks until commit to also prevent cascading rollbacks.
43. What is a deadlock and how is it handled?
A deadlock occurs when two or more transactions each hold a lock the other needs, so none can proceed. Databases handle it by detection (building a wait-for graph, finding a cycle, and rolling back a victim) or prevention (acquiring locks in a consistent order, or using timeout and wait-die/wound-wait schemes).
44. What are transaction isolation levels?
| Isolation level | Dirty read | Non-repeatable read | Phantom read |
|---|---|---|---|
| Read Uncommitted | Possible | Possible | Possible |
| Read Committed | Prevented | Possible | Possible |
| Repeatable Read | Prevented | Prevented | Possible |
| Serializable | Prevented | Prevented | Prevented |
Higher isolation means fewer anomalies but lower concurrency. Read Committed is the default in many databases; Serializable is the safest and slowest.
Indexing
45. What is an index and how does it help?
An index is a data structure (commonly a B+ tree or hash) that speeds up row lookups by avoiding a full table scan, similar to the index at the back of a book. It speeds up reads but adds overhead to writes and consumes storage, so you index selectively.
46. What is the difference between clustered and non-clustered indexes?
A clustered index determines the physical order of rows in the table, so there can be only one per table (often the primary key). A non-clustered index is a separate structure that points to the rows and you can have many of them.
47. What is the difference between a B-tree and a B+ tree index?
In a B-tree, keys and data pointers can appear in internal nodes. In a B+ tree, all actual data pointers live in the leaf nodes and the leaves are linked in a list, which makes range scans and ordered traversals efficient. Most relational databases use B+ trees.
48. When should you avoid adding an index?
Avoid over-indexing on tables with heavy writes, on very small tables, and on low-cardinality columns (like a boolean flag) where the index gives little selectivity. Each index must be maintained on every insert, update, and delete.
Views, Triggers, and Stored Procedures
49. What is a view?
A view is a virtual table defined by a stored query. It does not store data itself; it runs the underlying query when accessed. Views simplify complex queries, restrict access to specific columns, and present data consistently.
CREATE VIEW active_students AS
SELECT roll_no, name FROM student WHERE status = 'ACTIVE';
50. What is a materialized view?
A materialized view physically stores the query result on disk, so reads are fast, but the data can become stale and must be refreshed on a schedule or on demand. Use it for expensive aggregations that are read far more often than the underlying data changes.
51. What is a trigger?
A trigger is a block of code that runs automatically in response to an event such as INSERT, UPDATE, or DELETE on a table. BEFORE triggers are used for validation; AFTER triggers are used for auditing and logging. Triggers can be hard to debug when they chain, so use them carefully.
52. What is a stored procedure and how does it differ from a function?
A stored procedure is a precompiled set of SQL statements you call by name, optionally with input and output parameters, to encapsulate business logic. A function must return a value and can be used inside a query; a procedure performs actions and is invoked with CALL or EXEC, and generally cannot be embedded in a SELECT.
53. What is a cursor?
A cursor lets you process a result set one row at a time. Cursors are implicit (managed automatically) or explicit (opened, fetched, and closed by you). They are slower than set-based operations, so prefer set operations unless row-by-row logic is unavoidable.
SQL vs NoSQL
54. What is the difference between SQL and NoSQL databases?
| Aspect | SQL (relational) | NoSQL |
|---|---|---|
| Schema | Fixed, predefined | Flexible or schema-less |
| Scaling | Vertical (bigger server) | Horizontal (more servers) |
| Data model | Tables | Document, key-value, column, graph |
| Consistency | Strong (ACID) | Often eventual (CAP / BASE) |
| Best for | Structured data, complex queries | Large-scale, evolving, semi-structured data |
Choose SQL when you need strong consistency and complex joins, and NoSQL when you need flexible schemas and massive horizontal scale.
55. What is the CAP theorem?
The CAP theorem states a distributed data store can guarantee only two of three properties at once: Consistency, Availability, and Partition tolerance. Since network partitions are unavoidable, real systems choose between consistency and availability during a partition. This is why many NoSQL systems favour availability with eventual consistency.
Common Scenario Questions
56. What is the difference between DELETE, TRUNCATE, and DROP?
| Command | What it removes | Rollback | Speed | Type |
|---|---|---|---|---|
| DELETE | Selected rows (with WHERE) | Yes | Slower (row by row, logged) | DML |
| TRUNCATE | All rows, keeps structure | Usually no | Fast (deallocates pages) | DDL |
| DROP | Entire table and structure | Usually no | Fast | DDL |
57. How do you find the second highest salary?
A common approach uses a subquery:
SELECT MAX(salary) AS second_highest
FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee);
A more general approach uses a window function to get the Nth highest:
SELECT DISTINCT salary FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employee
) t
WHERE rnk = 2;
58. How do you find and remove duplicate rows?
Find duplicates by grouping on the columns that define a duplicate:
SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
To remove them, keep the row with the smallest id and delete the rest using a correlated condition or a window-function ranking, depending on your database.
59. Why can a query be slow and how would you speed it up?
Typical causes are missing indexes, functions applied to indexed columns (which prevent index use), unnecessary SELECT *, poor join order, and outdated statistics. Start by reading the execution plan with EXPLAIN, add or fix indexes on the filtered and joined columns, rewrite the query to be sargable, and only select the columns you need.
60. How would you design a schema for a simple e-commerce order system?
At minimum you need customer, product, orders, and order_item tables. orders holds a foreign key to customer; order_item is a junction table resolving the many-to-many relationship between orders and product, with quantity and price captured per line. This normalized design avoids duplicating product data across orders and keeps totals consistent.
How to Prepare for a DBMS Interview
- Master the core four: keys, normalization up to BCNF, joins, and ACID. These appear in almost every round.
- Practise SQL by writing, not reading: solve query problems on paper or a whiteboard, since Indian interviews often ask you to write SQL without an editor.
- Explain your reasoning aloud: interviewers care as much about how you justify a design (why 3NF, why an index, why a given isolation level) as the final answer.
- Prepare one project deeply: be ready to describe the schema of a project on your resume, including the keys and relationships you chose and why.
- Do timed mock rounds: simulate the pressure of follow-up questions. A structured AI mock interview can rehearse the exact rhythm of a technical DBMS round and give feedback on where your explanations are vague.
- Revise trade-offs, not just definitions: normalization vs denormalization, SQL vs NoSQL, higher isolation vs concurrency. Interviewers push on trade-offs to test depth.
Frequently Asked Questions
Are DBMS questions important for non-database roles?
Yes. Backend, full-stack, data analyst, and data engineering roles all rely on databases, so DBMS fundamentals and SQL are tested even when the job title is not "database developer".
How many DBMS questions should I expect in a placement interview?
It varies, but a technical round in most Indian product and service companies includes at least a handful of DBMS and SQL questions, often mixing one conceptual question (normalization or ACID) with one or two hands-on SQL problems.
Should freshers focus on theory or SQL practice?
Both, but start with theory for keys, normalization, and joins, then spend most of your remaining time writing SQL queries. Being able to write a correct join or aggregation quickly is a strong differentiator.
What is the single most commonly asked DBMS topic?
Normalization and joins are the two most frequently asked topics, closely followed by keys and ACID properties. Make sure you can give examples, not just definitions.
Do I need to learn NoSQL for interviews?
For most fresher roles a conceptual understanding of SQL vs NoSQL and the CAP theorem is enough. Experienced candidates applying to scale-heavy product teams should be able to discuss specific NoSQL models and consistency trade-offs.
How do I explain a concept I understand but cannot phrase well?
Use a concrete example. If you are asked about foreign keys, describe a student and enrollment relationship rather than reciting a definition. Practising with mock interviews helps you convert understanding into clear, confident answers.
Final Thoughts
DBMS interview questions reward candidates who understand the why behind each concept, not just the definitions. Focus your revision on keys, normalization, joins, transactions, and indexing, back every answer with a small example or SQL snippet, and practise explaining trade-offs out loud. Work through these 50+ questions, rehearse your SQL by writing it, and you will walk into your next placement or data-role interview ready to handle whatever the panel throws at you.
