Keys — Know the Whole Family
Keys are the most common opening question because they underpin everything else.
- Primary key — uniquely identifies each row; cannot be null; one per table.
- Candidate key — any column(s) that could serve as a primary key.
- Foreign key — a column referencing a primary key in another table; enforces referential integrity.
- Composite key — a primary key made of two or more columns.
- Unique key — enforces uniqueness but, unlike a primary key, allows one null.
Normalization — Up to 3NF Is Enough
You will be asked to define the normal forms and why we normalize. Focus on 1NF–3NF; BCNF is a bonus.
- Why normalize: remove redundancy and avoid insert/update/delete anomalies.
- 1NF — atomic values, no repeating groups.
- 2NF — 1NF plus no partial dependency on part of a composite key.
- 3NF — 2NF plus no transitive dependency (non-key depending on another non-key).
- Trade-off: over-normalizing means more joins; sometimes we denormalize for read speed.
ACID — The Transaction Guarantees
ACID is the most quoted DBMS acronym. Define each with a one-line example — ideally a bank transfer.
- Atomicity — all steps of a transaction succeed or none do (debit + credit both, or roll back).
- Consistency — a transaction moves the DB from one valid state to another (constraints hold).
- Isolation — concurrent transactions do not interfere; results match some serial order.
- Durability — once committed, data survives crashes (written to non-volatile storage).
Indexing — Why Queries Get Fast
Indexing separates candidates who have only written SELECTs from those who understand performance.
- An index is a data structure (usually a B-tree) that speeds lookups at the cost of extra storage and slower writes.
- Clustered index — determines the physical row order; one per table (often the primary key).
- Non-clustered index — a separate structure with pointers to rows; many allowed.
- Indexes help WHERE, JOIN and ORDER BY columns; avoid over-indexing write-heavy tables.
Joins — Draw the Venn Diagram
Be ready to explain each join and predict its output on two small tables.
- INNER JOIN — only matching rows in both tables.
- LEFT JOIN — all rows from the left, matched or null from the right.
- RIGHT JOIN — all rows from the right, matched or null from the left.
- FULL OUTER JOIN — all rows from both, nulls where no match.
- CROSS JOIN — Cartesian product of both tables.
Common Interview Questions & Answers
Q1. What is the difference between a primary key and a unique key?
Both enforce uniqueness, but a primary key cannot contain nulls and there is exactly one per table, since it is the row's canonical identifier. A unique key can contain a single null value and you can have several unique keys on a table. A primary key also typically creates a clustered index by default in many systems.
Mention the null difference first — it is the detail interviewers want.
Q2. Explain normalization and why we do it.
Normalization organises columns and tables to minimise redundancy and eliminate update, insert and delete anomalies. In 1NF every value is atomic; 2NF removes partial dependencies on part of a composite key; 3NF removes transitive dependencies where a non-key column depends on another non-key column. We normalize for data integrity, but sometimes denormalize deliberately to reduce joins on read-heavy workloads.
End with the denormalization trade-off — it shows you think about real systems, not just theory.
Q3. What are ACID properties?
ACID describes the guarantees of a reliable transaction. Atomicity means all-or-nothing execution. Consistency means the database moves between valid states, preserving constraints. Isolation means concurrent transactions behave as if run in some serial order. Durability means once committed, changes survive power loss or crashes. The classic example is a bank transfer: the debit and credit must both happen (atomicity) and remain after a crash (durability).
Use the bank-transfer example — it makes all four properties concrete in one story.
Q4. How does an index speed up a query, and what is the cost?
An index is usually a B-tree that lets the engine find rows without scanning the whole table, turning an O(n) scan into an O(log n) lookup on the indexed column. The cost is extra storage and slower INSERT/UPDATE/DELETE, because every write must also update the index. So you index columns used in WHERE, JOIN and ORDER BY, but avoid over-indexing write-heavy tables.
Naming the B-tree and the write-penalty trade-off is what elevates this answer.
Q5. What is the difference between DELETE, TRUNCATE and DROP?
DELETE removes rows one at a time and can use a WHERE clause; it is logged and can be rolled back. TRUNCATE removes all rows at once by deallocating pages; it is faster, minimally logged, and resets identity counters, but cannot target specific rows. DROP removes the entire table structure along with its data. So DELETE is row-level and reversible, TRUNCATE wipes all rows fast, DROP deletes the table itself.
The 'DDL vs DML' framing — TRUNCATE/DROP are DDL, DELETE is DML — is a nice extra.
Common Mistakes to Avoid
Defining normal forms without being able to give a small example table
Saying a primary key can be null, or that unique keys never allow nulls
Reciting ACID with no concrete example
Claiming indexes are always good — ignoring the write penalty
Confusing DELETE (DML, row-level) with TRUNCATE/DROP (DDL)
Expert Tips
Keep two tiny 3-row tables in your head to demo joins and normalization instantly
Use the bank-transfer story for ACID — it covers all four at once
For any performance question, always state the trade-off, not just the benefit
If you know one SQL query well (e.g. second-highest salary), volunteer it — it lands strongly
Pre-Interview Checklist
6 itemsFrequently Asked Questions
Is DBMS or SQL more important for freshers?
Both — DBMS theory (normalization, ACID, indexing) is asked in the technical round, while SQL query-writing is tested separately. Prepare the concepts here and pair them with the SQL Interview Questions guide for hands-on queries.
How deep should I go on normalization?
Up to 3NF with examples is expected. BCNF and higher are bonus territory unless you are interviewing for a data-heavy role.
Do I need to memorise SQL syntax for a DBMS round?
You should be comfortable writing common queries (joins, group by, subqueries), but pure theory questions focus on concepts. Practice a handful of classic queries so you can write one confidently on the whiteboard.
Ready to ace your next interview?
Practice with SpeakWell AI. Upload your resume → get resume-based questions → practice with AI interviewers → improve communication → track progress → get instant AI feedback.