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.
Placement interviews move through the same areas in much the same order: basics, keys, the normalization chain, SQL, transactions and ACID, then indexing and design judgement. Rehearse each chain aloud, not the first line of each answer.
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?
A DBMS is 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, so applications must build any needed constraints, concurrency control and recovery themselves. A concrete problem is redundancy: storing one address in three files lets the copies drift 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. Follow-up: is every DBMS relational? No; the relational model of key-linked tables with engine-enforced constraints is what narrows a DBMS to an RDBMS.
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). Follow-up: which level shields applications from storage changes? The logical level: it stays fixed while the physical layout is rearranged beneath it, and holding that boundary is exactly what physical data independence means.
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. Follow-up: which one changes as data changes? The instance changes with every write, while the schema stays the design until a redesign.
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?
A super key is any attribute set that identifies rows uniquely. A candidate key is a minimal super key, one with no removable attribute. A primary key is the candidate key you choose to enforce, and the unchosen candidates become alternate keys. Follow-up: why must a candidate key be minimal? Remove any attribute and it stops identifying rows uniquely.
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, and a unique column can hold NULL where a primary key cannot. How many NULLs is the follow-up: the standard treats two NULLs as distinct, so PostgreSQL, MySQL and Oracle allow several, while SQL Server permits only one.
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 decomposition, see DBMS Normalization Explained Simply.
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. Follow-up: how do you find candidate keys from a set of FDs? Take attribute closures: a set whose closure is every attribute is a super key, and a super key with no removable attribute is a candidate key.
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 track inside CS Fundamentals for Placements by Sanchit Sir runs to over forty lessons for a reason.
24. DELETE vs TRUNCATE vs DROP?
DELETE removes selected rows and takes a WHERE clause; TRUNCATE empties the whole table by deallocating its pages, so it is much faster but cannot be filtered; DROP removes the table object itself. Follow-up: which can you roll back? DELETE always. TRUNCATE depends on the engine: PostgreSQL and SQL Server roll it back inside a transaction, while MySQL and Oracle commit it implicitly.
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. With orders holding customers A and B but customers holding only A, an inner join returns A alone, while a left join from orders returns A and B, filling B's customer columns with NULL. Each family is worked query by query in SQL Queries and Joins in DBMS.
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.
Use a subquery to find the largest salary below the maximum.
SELECT MAX(salary)
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);Follow-up: ties or the Nth highest? Rank the rows with DENSE_RANK, filter on rank 2, and change the rank to N for the Nth highest:
SELECT salary
FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees
) ranked
WHERE salary_rank = 2;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?
A function returns a value and can be called inside a query, so SELECT tax(price) works; a procedure is invoked as its own statement with CALL or EXEC and may return several result sets. Follow-up: can a function manage transactions? No, and that is why PostgreSQL added CALL-able procedures: a procedure can COMMIT, a function cannot.
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. MySQL returned an arbitrary row's value for years, until ONLY_FULL_GROUP_BY became its default, so group the column or aggregate it and the answer travels everywhere.
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. Follow-up: why the log before the data pages? The log is a small sequential write that can be forced to disk cheaply, and redoing from it rebuilds whatever pages the crash lost.
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.
Four, defined by which anomalies each one still allows. Read uncommitted allows dirty, non-repeatable and phantom reads; read committed stops dirty reads; repeatable read also stops non-repeatable reads but still allows phantoms; serializable allows none of the three. Engines then deviate from the table, so say so: PostgreSQL's repeatable read runs on snapshots and blocks phantoms too, and Oracle offers no read uncommitted at all. Follow-up: why not always serializable? Stronger isolation costs concurrency or adds retry cost, so name the workload before you name the level.
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. Follow-up: which columns get indexed first? The ones your frequent queries filter, join and sort on, most selective first.
45. Clustered vs non-clustered index?
A clustered index sets the table's row-storage order, so the table itself is the index; a non-clustered index is a separate structure of keys plus pointers back to the rows. SQL Server uses exactly these names, and in InnoDB the primary key is a clustered index by construction. Follow-up: why only one per table? Rows can sit in only one physical order, so only one index can be that order.
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.
DBMS design judgement: questions 49 and 50
These two are not recall questions; they test whether you can defend a choice.
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. A first cut of that method looks like this:
book(book_id PK, title, author)
member(member_id PK, name)
loan(loan_id PK, book_id FK, member_id FK)The loan table's two foreign keys record who borrowed which book, and the book-to-member many-to-many relationship lives there. Follow-up: what is missing before that goes live? Dates on the loan (issued_on, due_on, returned_on), without which no overdue query is possible at all, plus an index on (member_id, returned_on) for the lookup the counter runs all day: what is this member holding right now.
How to rehearse DBMS interview answers that survive follow-ups
Definitions come from reading; chains come from explaining aloud. Practise the five anchor chains (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.
