DBMS and SQL Interview Questions Freshers Should Expect

17 Jul 2026 7 min read

DBMS is the most reliably examined of the CS fundamentals, for a simple reason: whatever the role, you will touch a database. So interviewers use it as a floor — a fresher who can’t explain a join or a primary key is a fresher who hasn’t built anything real.

The good news is that the examinable surface is small and it barely changes. Here’s what’s on it.

Normalization (know it as a reason, not a ladder)

The question is usually “explain normalization” or “what is 3NF”, and the weak answer is a recitation of the forms in order.

Normalization is organising tables so that a fact is stored in exactly one place. Everything else follows from that goal. If a fact lives in two rows, the two rows can disagree — and that’s what the anomalies are:

Update anomaly — you change a fact in one row and miss the other copies.
Insert anomaly — you can’t record a fact because you don’t yet have some unrelated fact to go with it.
Delete anomaly — deleting a row destroys a fact you still needed.

The forms are just checkpoints on the way there:

1NF — every column holds a single atomic value; no repeating groups, no comma-separated lists in a cell.
2NF — 1NF, plus no partial dependency: no non-key column depends on only part of a composite primary key. (Only relevant if your key is composite.)
3NF — 2NF, plus no transitive dependency: non-key columns depend on the key, not on another non-key column.

The line people quote for 3NF — every non-key attribute depends on the key, the whole key, and nothing but the key — is genuinely a good mnemonic, because it maps exactly onto 1NF/2NF/3NF in order.

BCNF is the stricter version of 3NF: every determinant must be a candidate key. It matters in the edge case where a table has overlapping candidate keys.

Be ready for the counter-question: “when would you denormalize?” When read performance matters more than write simplicity — deliberately duplicating data to avoid expensive joins, accepting that you now own the job of keeping the copies in sync. Saying that trade-off out loud shows you understand normalization is a default, not a religion.

ACID

Four properties of a transaction. Expect to define them and give an example.

Atomicity — all of it happens or none of it does. The bank transfer: debit and credit both commit, or neither does. A crash between them must not leave money destroyed.
Consistency — the transaction moves the database from one valid state to another; constraints hold before and after.
Isolation — concurrent transactions don’t see each other’s half-finished work.
Durability — once committed, it survives a crash. This is what the write-ahead log is for.

The bank transfer example covers atomicity and durability well, and it’s the one interviewers expect — using it is not unoriginal, it’s efficient.

Isolation levels and the anomalies they allow

This is the follow-up to “I” in ACID, and it’s where the good answers separate out, because the levels are only meaningful as which problem does this one still permit:

Level	Still allows
Read Uncommitted	Dirty reads, non-repeatable reads, phantoms
Read Committed	Non-repeatable reads, phantoms
Repeatable Read	Phantoms
Serializable	Nothing (and costs the most)

The three anomalies:

Dirty read — you read data another transaction wrote but hasn’t committed. It might roll back, and you acted on a fact that never existed.
Non-repeatable read — you read the same row twice in one transaction and get different values, because someone committed an update in between.
Phantom read — you run the same query twice and get a different set of rows, because someone inserted or deleted matching rows in between.

The distinction between the last two is a favourite: non-repeatable is about a row changing, phantom is about the result set changing.

Every step down that table buys correctness with concurrency. That trade-off is the actual answer to “which isolation level should I use?”

Indexing

Expect: what is an index, why not index everything, clustered vs non-clustered.

An index is a separate structure (usually a B+ tree) that lets the database find rows without scanning the whole table — the same reason a book has an index. It turns a linear scan into something logarithmic.

Why not index every column? Because an index is not free:

It costs disk space.
It slows down every write — INSERT, UPDATE and DELETE now have to maintain the index as well as the table.

So indexes are a read/write trade-off, and the answer to “should I add an index?” is always “what’s the read/write ratio on this column?”

Clustered vs non-clustered: a clustered index determines the physical order of rows in the table, so there can be only one. A non-clustered index is a separate structure pointing at rows, so you can have many. In InnoDB (MySQL’s default engine) the primary key is the clustered index, which is worth knowing because it explains why primary-key lookups are the fastest thing the table can do.

Joins

You’ll be asked to explain them, and quite possibly to write one.

INNER JOIN — rows matching in both tables.
LEFT JOIN — all rows from the left, with NULLs where the right has no match.
RIGHT JOIN — the mirror.
FULL OUTER JOIN — everything from both sides, NULLs where a side is missing. (Note MySQL has no FULL OUTER JOIN — you emulate it with a UNION of left and right joins. A nice detail to know.)
CROSS JOIN — the Cartesian product, every row against every row.

The practical follow-up is often “find rows in A with no match in B” — a LEFT JOIN ... WHERE B.id IS NULL. That pattern is worth having ready, because it’s the one that shows you understand what the NULLs in an outer join mean.

The small ones that come up constantly

Primary key vs unique key — a primary key is one per table and can’t be NULL. A unique key allows NULLs, and you can have several per table. Worth knowing that NULL handling under UNIQUE varies by database: MySQL and PostgreSQL permit multiple NULLs in a unique column, SQL Server historically permits only one. “It depends on the DBMS” is the correct and impressive answer.
DELETE vs TRUNCATE vs DROP — DELETE removes rows and can be filtered with WHERE and rolled back; TRUNCATE empties the whole table quickly by deallocating pages rather than logging each row; DROP removes the table itself, structure included.
WHERE vs HAVING — WHERE filters rows before grouping, HAVING filters groups after. That’s why you can’t use an aggregate in WHERE: the aggregate doesn’t exist yet.
UNION vs UNION ALL — UNION removes duplicates (and pays for a sort to do it); UNION ALL doesn’t, and is faster. If you know there are no duplicates, UNION ALL is the right call.

Practise it as retrieval

You’ll notice a pattern in every answer above: the good version explains why the thing exists, then derives the details. That isn’t a presentation trick — it’s what actually understanding the concept looks like from the outside, and interviewers can tell the difference instantly.

You can’t get there by re-reading notes. You get there by being asked and finding out whether you were right: our DBMS interview bank has questions with an explanation on every one, free and untimed if you want it that way.

Then round out the fundamentals round with operating systems and computer networks — the three together are most of what “CS core” means on an interview panel’s scorecard.