DBMS Interview Questions for Freshers (2026): Theory Beyond SQL
Updated August 2026
Writing a query and explaining why the database ran it that way are two different interview skills. Once you clear the SQL questions, the follow-ups move into DBMS theory — transactions, isolation, indexing internals and normal forms — and that is where prepared candidates separate themselves.
This set is the theory layer that sits on top of our SQL question set. Answer each one in 30–60 seconds, and always finish with the "why it exists" line — that is the part interviewers are listening for.
Frequently asked questions
DBMS vs RDBMS — what is the actual difference?
A DBMS is any software that stores and retrieves data. An RDBMS stores it as related tables with rows and columns, enforces relationships through keys, and follows the relational model (Codd's rules) with SQL as the query language. MySQL, PostgreSQL and Oracle are RDBMS; a plain file store or an older hierarchical system is not. Interviewers use this as a warm-up — answer it in one line and move on.
Explain ACID with a concrete example.
Atomicity — all or nothing (a bank transfer either debits and credits, or neither happens). Consistency — the database moves from one valid state to another, constraints intact. Isolation — concurrent transactions do not see each other's half-finished work. Durability — once COMMIT returns, the data survives a crash, because the change is already in the write-ahead log on disk. The transfer example carries all four; use it.
What are the isolation levels, and what problem does each one fix?
Read Uncommitted allows dirty reads (seeing uncommitted data). Read Committed removes dirty reads but allows non-repeatable reads (the same row changes between two reads). Repeatable Read fixes that but can still allow phantom rows (new rows matching your filter). Serializable removes all three by behaving as if transactions ran one after another. The tradeoff is always the same: more isolation, less concurrency.
What is a deadlock, and how do databases handle it?
Two transactions each hold a lock the other needs, so neither can proceed — T1 holds row A and wants row B while T2 holds B and wants A. Databases detect it (wait-for graph) or time out, then abort one transaction as the victim so the other completes; your application retries the aborted one. Prevention in application code: acquire locks on tables in a consistent order and keep transactions short.
How does an index actually speed up a query?
Most indexes are B+ trees: the tree keeps keys sorted, so the engine descends a few levels instead of scanning every row — roughly O(log n) instead of O(n). The leaf nodes hold pointers to the actual rows. That structure is also why an index helps ORDER BY and range scans on the indexed column, not just equality lookups.
Clustered vs non-clustered index?
A clustered index defines the physical order of rows in the table, so there can be only one — the primary key usually gets it. A non-clustered index is a separate structure holding the key plus a pointer back to the row, and a table can have several. The follow-up: a non-clustered lookup may need an extra hop to fetch the row, which is why a covering index (one that already contains every column the query needs) is faster.
When does adding an index make things worse?
Every INSERT, UPDATE and DELETE must also maintain the index, so write-heavy tables slow down and storage grows. Indexes on low-cardinality columns (a gender or status flag) rarely help, since the planner may still choose a full scan. Also, an index on column B alone is not used for a filter on A when your composite index is (A, B) — leftmost-prefix rule.
Explain normalization up to BCNF, and when you would denormalize.
1NF: atomic values, no repeating groups. 2NF: no partial dependency on part of a composite key. 3NF: no transitive dependency (non-key depending on non-key). BCNF: for every functional dependency X → Y, X must be a superkey — it fixes the edge cases 3NF allows with overlapping candidate keys. You denormalize deliberately in read-heavy reporting systems, accepting duplicated data to avoid expensive joins.
What is a functional dependency, and what is a candidate key?
A functional dependency X → Y means X determines Y: given a roll number, the student name is fixed. A candidate key is a minimal set of attributes that functionally determines every other attribute; one candidate key is chosen as the primary key and the rest become alternate keys. Normalization is entirely built on this vocabulary, so define it precisely before attempting a normal-form question.
Primary key vs super key vs composite key?
A super key is any attribute set that uniquely identifies a row (possibly with extra attributes). A candidate key is a minimal super key. The primary key is the candidate key you pick — unique, not null, one per table. A composite key is simply a key made of two or more columns, common in junction tables like student_course(student_id, course_id).
What is a view, and can you update through one?
A view is a stored query that behaves like a virtual table — useful for hiding complexity and restricting column access. Simple views over a single table with no aggregation are generally updatable; views with joins, GROUP BY, DISTINCT or aggregate functions usually are not. A materialized view goes further and stores the result physically, trading freshness for speed.
What is a stored procedure, and how is a trigger different?
A stored procedure is precompiled SQL you call explicitly by name, with parameters — it cuts network round-trips and centralises logic. A trigger is procedural code the database fires automatically on INSERT, UPDATE or DELETE, typically for auditing or enforcing a rule. The distinction to state clearly: you call a procedure; a trigger calls itself.
SQL vs NoSQL — when would you choose each?
Choose a relational database when the schema is stable, relationships matter and you need multi-row transactional guarantees — most business and placement-project systems. Choose a document or key-value store when the shape of records varies, the access pattern is a simple lookup by key, or you are scaling reads horizontally. Say "it depends on the access pattern", then give one example of each — a flat "NoSQL is faster" answer loses marks.
Is DBMS still asked in 2027-batch placement interviews?
Yes — DBMS remains one of the most consistently asked core subjects for freshers alongside OOP and OS, because almost every project role touches a database. Panels vary in depth, but transactions, indexing, keys and normalization are the recurring themes. Prepare those four and you cover most of what a fresher panel has time to ask.
My project used MongoDB, not SQL. Will I be asked DBMS theory anyway?
Very likely, and it is a fair question — theory is tested independently of your project stack. Prepare the relational fundamentals as above, then be ready for the natural follow-up: "why did you pick MongoDB for your project?" Answer with the access pattern and schema flexibility you actually needed, not with a claim about performance you cannot defend.
Don't just read DBMS questions — get asked them
Phiny's AI interviews you on exactly these topics, follows up on weak answers, and tells you what a stronger answer looks like. Text interviews are free and unlimited.
Start a free AI mock interviewHow to prepare
- Pair every theory answer with the one-line reason it exists — "isolation levels exist because full serialisability costs concurrency". Panels reward the reason more than the definition.
- Draw as you speak: a two-transaction deadlock diagram or a B+ tree sketch on the rough sheet turns a shaky answer into a confident one.
- Do this set right after our SQL questions — interviewers move from "write the query" to "why is that query slow", and the second question is this page.
Where these questions get asked
- TCS NQT guide and Infosys hiring guide — the two biggest exams these questions appear in.
- All company placement guides — pattern, syllabus and rounds for every mass recruiter.