Fifteen common DBMS interview questions
- What are primary, candidate, super, foreign and composite keys?
- Explain 1NF, 2NF, 3NF and BCNF.
- What are the types of joins?
- What does ACID stand for?
- What are the isolation levels, and which anomalies does each prevent?
- What is a dirty read, a non-repeatable read and a phantom read?
- How does an index work? Clustered versus non-clustered?
- When can an index hurt performance?
- What is a deadlock in a database, and how is it resolved?
- Optimistic versus pessimistic locking?
- DELETE versus TRUNCATE versus DROP?
- What are views and stored procedures?
- What is denormalisation, and when is it justified?
- SQL versus NoSQL?
- What does the CAP theorem say?
Keys and normalisation
- Super key: any set of columns that uniquely identifies a row. Candidate key: a minimal super key. Primary key: the chosen candidate key. Foreign key: references another table's key. Composite key: a key made of several columns.
- 1NF: atomic values, no repeating groups. 2NF: 1NF plus no partial dependency on part of a composite key. 3NF: 2NF plus no transitive dependencies. BCNF: every determinant is a candidate key.
- Why normalise: remove redundancy and update anomalies. Why denormalise: fewer joins for read-heavy workloads, at the cost of keeping copies consistent.
Transactions and isolation
| Isolation level | Prevents | Still allows |
|---|---|---|
| Read uncommitted | — | Dirty, non-repeatable and phantom reads |
| Read committed | Dirty reads | Non-repeatable and phantom reads |
| Repeatable read | Dirty and non-repeatable reads | Phantoms (in the standard definition) |
| Serializable | All three | — (at a concurrency cost) |
ACID: atomicity (all or nothing), consistency (constraints hold), isolation (concurrent transactions don't interfere improperly) and durability (committed data survives crashes, via write-ahead logging). Real databases implement isolation differently — many use multi-version concurrency control so readers don't block writers — so mention that behaviour varies by engine. See PostgreSQL interview questions.
Indexes
Most indexes are B-trees that keep keys sorted, making lookups, range scans and ordering fast. A clustered index determines the physical order of rows (one per table); non-clustered indexes are separate structures pointing to rows. Indexes speed reads but slow inserts and updates and use storage, and they may be ignored for low-selectivity columns or when queries wrap the column in a function. Composite indexes follow the leftmost-prefix rule: an index on (a, b) helps queries on a, or a and b, but not on b alone.
How deep it goes by level
Freshers are usually asked definitions with a small example: keys, normal forms, joins and ACID. Mid-level backend candidates get scenario questions — a report shows different totals on refresh, a query became slow after a data migration, two users booked the same seat — and are expected to reason with isolation levels, indexes and locks. Senior candidates are asked about replication lag, partitioning, choosing between relational and NoSQL stores for a workload, and how to change a schema on a live system without downtime. Prepare one real story at your level, because “tell me about a database problem you solved” follows most theory questions.
Concurrency and NoSQL
- Database deadlocks: two transactions each hold a lock the other needs; the database detects the cycle and aborts one, which the application should retry.
- Pessimistic locking locks data before changing it — safe under heavy contention. Optimistic locking checks a version number at commit — efficient when conflicts are rare.
- SQL vs NoSQL: relational databases offer schemas, joins and strong transactions; NoSQL systems (document, key-value, wide-column, graph) trade some of that for flexible schemas and horizontal scale.
- CAP: during a network partition, a distributed system must choose between consistency and availability.
Practising
Reading answers is not the same as saying them. Pick ten of the questions above and answer them aloud against a timer; a free AI interview assistant like CoPilot Interview can ask database questions in a mock interview and flag answers that ran long or lacked an example. Write the SQL for each join type once and explain the result aloud; then see SQL interview questions and data modeling questions.
Disclosure: CoPilot Interview is our product, so weigh what we say about it accordingly. Everything else on this page applies whichever tool you use, or none.
Frequently asked questions
What are the ACID properties?
Atomicity, consistency, isolation and durability: transactions complete fully or not at all, keep data valid, don't interfere improperly with each other, and persist once committed.
What is the difference between 2NF and 3NF?
2NF removes partial dependencies on part of a composite key. 3NF additionally removes transitive dependencies, where a non-key column depends on another non-key column.
Clustered or non-clustered index?
A clustered index defines the physical order of the table's rows, so there is one per table. Non-clustered indexes are separate structures pointing to rows, and a table can have many.
What is a phantom read?
When a transaction re-runs a query and finds new rows that another committed transaction inserted in the meantime.
Practise on real interview questions
Run 145 real interview questions across 10 tracks, answer out loud and see a structured answer to compare with yours. Free, no signup.
Try a practice question