Most freshers can define normalization. The interview is lost one question later, when the panel asks why BCNF exists or when you would denormalize. Follow-ups separate memorised definitions from understanding.
Here are 50 DBMS questions grouped the way placement interviews run, with the follow-up handled where one reliably exists. Rehearse the chains, not the first lines.
DBMS basics: interview questions 1 to 7
The warm-up round decides how hard the rest of the interview probes.
1. What is a DBMS, and how is it different from a file system?
Software that stores and manages data while enforcing integrity, concurrency and crash recovery. A plain file-based design does not provide these as integrated database services; applications must build any needed constraints, concurrency control and recovery themselves. Follow-up: a concrete problem? Redundancy: one address in three files drifts apart.
2. DBMS vs RDBMS?
An RDBMS stores data as relations (tables) linked by keys, with engine-enforced constraints. MySQL, PostgreSQL and Oracle are examples.
3. The three levels of data abstraction?
Physical (how data is stored), logical (what tables and relationships exist) and view (the slice each user sees).
4. What is data independence?
Changing a lower level without rewriting what sits above. Physical independence is routine; logical is harder, since applications depend on the schema's shape.
5. Schema vs instance?
The schema is the design; an instance is the data at one moment.
6. What are DDL, DML, DCL and TCL?
Definition (CREATE, ALTER, DROP), manipulation (SELECT, INSERT, UPDATE, DELETE), control (GRANT, REVOKE) and transaction control (COMMIT, ROLLBACK).
7. What is a data dictionary?
The system catalog of metadata: table definitions, constraints, users, privileges.
Keys and the ER model: questions 8 to 15
Key terminology nests, so sloppy answers show here first.
8. Super key vs candidate key vs primary key?
Any attribute set identifying rows uniquely; a minimal super key; the candidate key you choose, the rest becoming alternate keys.
9. What is a foreign key?
An attribute referencing another table's primary key, enforcing referential integrity. Follow-up: what if the referenced row is deleted? Whatever the constraint chose: RESTRICT blocks, CASCADE deletes children, SET NULL orphans explicitly.
10. Why can a primary key never be NULL?
Entity integrity: the key must identify every row; NULL means unknown and identifies nothing.
11. Unique constraint vs primary key?
A table has one primary key but may have many unique constraints. NULL behaviour under a unique constraint varies by database engine, so state the engine's rule instead of assuming one universal answer.
12. What is a composite key?
A key of two or more attributes when none is unique alone: (student_id, course_id) in enrolments.
13. What is a weak entity?
One not identifiable by its own attributes; it borrows the owner's key via an identifying relationship plus a partial key: room 204 means nothing without its hotel.
14. How do you implement a many-to-many relationship?
A junction table carrying both foreign keys, the pair forming its composite primary key.
15. What is a surrogate key?
A system-generated identifier with no business meaning (an auto-increment id), preferred when natural keys are composite or liable to change.
Normalization to BCNF to denormalization: the chain (questions 16 to 23)
The most common follow-up chain in DBMS interviews. For the worked version, read DBMS Normalization Explained Simply, then come back.
16. What is normalization?
Decomposing tables along functional dependencies to remove redundancy, eliminating insertion, update and deletion anomalies.
17. What is a functional dependency?
X determines Y when rows agreeing on X agree on Y; every normal form is defined over FDs.
Normal form | The rule it adds | The violation to name |
|---|---|---|
1NF | Atomic values, no repeating groups | One column holding two phone numbers |
2NF | No partial dependency on a composite key | student_name depending on student_id alone |
3NF | No transitive dependency via non-key attributes | department deciding department_location |
BCNF | Every determinant is a candidate key | teacher deciding subject without being a key |
18. What does 2NF remove, exactly?
Partial dependency: in (student_id, course_id, student_name), the name depends on student_id alone and moves to the student table.
19. What does 3NF remove?
Transitive dependency: if employee determines department and department determines location, location moves to a department table.
20. Why does BCNF exist if we already have 3NF?
3NF tolerates a dependency whose right side is a prime attribute; BCNF closes that loophole: every determinant must be a candidate key. Classic case: a student-subject-teacher table where teacher determines subject but is not a candidate key, so that fact repeats with every enrolment.
21. Is BCNF always the right target?
A lossless BCNF decomposition exists but may not preserve every dependency. Standard 3NF synthesis can guarantee a lossless, dependency-preserving decomposition, which is why 3NF is often the practical stop.
22. When would you deliberately denormalize?
In read-heavy paths where join cost dominates: reporting tables, dashboards, precomputed aggregates. You buy back the anomalies normalization removed, so each copy needs a controlled refresh path.
23. So is normalization good or bad?
That framing is the trap. The chain is one argument about where redundancy may live: normalize where writes must stay correct, denormalize where reads must stay fast, knowing which anomaly you re-admitted.
SQL interview questions for freshers (24 to 35)
Service companies spend the longest here; the SQL module inside CS Fundamentals for Placements by Sanchit Sir runs 46 lessons for a reason.
24. DELETE vs TRUNCATE vs DROP?
DELETE removes selected rows; TRUNCATE empties the table; DROP removes the table object. Transaction rollback and logging behaviour for TRUNCATE and other DDL operations varies by database engine, so qualify the answer for the system being discussed.
25. WHERE vs HAVING?
WHERE filters rows before grouping; HAVING filters groups after aggregation. Follow-up: why no aggregates in WHERE? They do not exist until grouping happens.
26. Inner vs left vs right vs full join?
Inner keeps matches only. Left keeps every left row, padding right columns with NULL; right mirrors it; full keeps both.
27. What is a self join?
A table joined to itself under two aliases: how you pair employees with managers via manager_id.
28. What is a correlated subquery?
One referencing the outer query's current row. Its semantics are per outer row, although an optimizer may decorrelate it; an uncorrelated subquery is independent of the outer row.
29. UNION vs UNION ALL?
UNION removes duplicates, costing a sort or hash; UNION ALL appends and is faster when duplicates cannot occur.
30. How does NULL behave in SQL?
Any comparison with NULL yields unknown, so use IS NULL, never = NULL. COUNT(*) counts rows; COUNT(column) skips NULLs.
31. Write a query for the second-highest salary.
SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); Follow-up: ties or Nth highest? DENSE_RANK() over salary descending, filtered on rank.
32. What is a view, and can you update through it?
A stored query presented as a virtual table. Simple single-table views are updatable; join or aggregate views generally are not. A materialized view stores its result and needs refreshing.
33. Stored procedure vs function?
Commonly, a function returns a value and may be used inside a query, while a procedure is invoked as a statement and may return multiple results. Exact capabilities, including transaction control, vary by database engine.
34. CHAR vs VARCHAR?
CHAR pads to fixed length, suiting fixed-format codes; VARCHAR stores actual length and fits everything else.
35. Can you SELECT a column not grouped or aggregated?
Standard SQL rejects it: the group has no single value for that column. Engines that tolerate it return an arbitrary value.
Transactions and ACID: questions 36 to 43
Product companies push hardest here; it tests reasoning about failure.
36. Define a transaction and ACID.
A unit of work that fully happens or not at all: atomicity (all or nothing), consistency (constraints hold), isolation (no mutual corruption), durability (a commit survives a crash).
37. How is durability actually achieved?
Write-ahead logging: the change reaches the on-disk log before commit is acknowledged, so recovery can redo it.
38. What is a savepoint?
A marker inside a transaction allowing partial rollback without abandoning the whole.
39. Name the classic concurrency anomalies.
Dirty read (reading uncommitted data), non-repeatable read (a row changes between two reads), phantom read (new rows appear for the same predicate).
40. Explain the isolation levels.
The SQL names are read uncommitted, read committed, repeatable read and serializable, but the anomalies each level permits and the implementation strategy vary by engine. Follow-up: why not always serializable? Stronger isolation can reduce concurrency or add retry cost, so know the workload and the engine.
41. What makes a schedule serializable?
Equivalence to some serial order of the transactions. Conflict serializability is the testable version: a cycle in the precedence graph means not serializable.
42. What is two-phase locking?
Locks are acquired only in a growing phase and released in a shrinking phase, guaranteeing conflict-serializable schedules. Follow-up: does 2PL prevent deadlock? No; transactions can still block each other in a cycle.
43. How do databases handle deadlock?
Detect with a waits-for graph and abort a victim, or prevent with timestamp schemes like wound-wait. Applications help by locking in a consistent order.
Indexing questions: 44 to 48
Indexing is where interviewers test cost thinking rather than syntax.
44. What is an index, and why not index everything?
A structure trading space and write cost for read speed; every insert and update maintains every index, so over-indexing slows writes.
45. Clustered vs non-clustered index?
In engines that use the term, a clustered index determines or closely follows the table's row-storage order, while non-clustered indexes are separate structures that locate rows. Follow-up: why only one per table? A table can have only one clustered organization, but exact behaviour is engine-specific.
46. Why do databases prefer B+ trees over B-trees?
All records live in linked leaf nodes, so internal nodes carry only keys: higher fan-out, shorter trees, and a range scan becomes a walk along the leaf chain.
47. Dense vs sparse index?
Dense keeps an entry per record; sparse one per block, requiring the file sorted on the key. Sparse is smaller; dense serves lookups faster.
48. When does the optimizer ignore your index?
When it cannot use it: a function around the column, a leading-wildcard LIKE, or selectivity so low a full scan is cheaper.
The maturity questions: 49 and 50
49. When would you choose NoSQL over an RDBMS?
When data is schema-flexible, scale horizontal, access key-based: sessions, catalogs, event streams. Keep an RDBMS for joins, constraints, multi-row transactions. "NoSQL is faster" with no workload attached is the red flag.
50. Design a schema for a library system.
Do not jump to tables. Name entities (book, member, loan), choose keys, mark cardinalities, normalize to 3NF, then index for expected queries. Interviewers score the method; the tables are secondary.
How to prepare so the follow-ups stop scaring you
Definitions come from reading; chains come from explaining aloud. Practise the five anchor chains here (keys, normalization to BCNF, joins, isolation levels, indexing cost) in under a minute each until the follow-up feels like part of the answer.
If DBMS still needs building, the module sequence on the Computer Science learn page runs from ER diagrams through normalization to concurrency control, and the Placement Preparation category holds the aptitude and interview material alongside. Fifty rehearsed explanations beat five hundred read ones.
