Skip to content
Tutorials Center
My learning

Topics

Lesson 78 of 79 ยท Practice and Review

DBMS Interview Questions

Prepare for database interviews: concise model answers on ACID, keys, normalization, joins, isolation, deadlocks, indexes, B-trees, CAP and sharding.

Database questions come up in almost every software interview, from internships to senior roles. The good news is that they are predictable: interviewers want to know whether you understand the why behind a feature, not whether you can recite a definition. A strong answer is short, correct, names the trade-off and, when possible, gives a concrete example.

This lesson collects the questions that appear most often, grouped by topic, with model answers you can say out loud in under a minute. Each group links back to the lesson where the topic is explained in full. Three live demos let you show, not just tell: a join and NULL trap, atomicity plus an index plan, and deadlock and quorum logic.

How to answer well

Define it One clear sentence.
Say why What problem does it solve?
Name the cost Every feature has a trade-off.
Give an example A bank, a shop, a library.

Short and structured beats long and vague.

1 / 2

Basics and design

What is the difference between a database and a DBMS?

A database is an organized collection of data; a DBMS is the software that stores, protects and queries it. The DBMS gives you a data model and query language, enforces integrity constraints, controls concurrent access, recovers from crashes and manages security. Without it every application would reimplement these things, badly. See What Is a Database? and File Systems vs a DBMS.

What are the ACID properties?

  • Atomicity: a transaction happens completely or not at all (enforced by logging and rollback).
  • Consistency: a transaction moves the database from one valid state to another, respecting every constraint (enforced by constraints plus correct application logic).
  • Isolation: concurrent transactions behave as if they ran one after another (enforced by locking or multiversioning).
  • Durability: once committed, a change survives crashes (enforced by write-ahead logging and flushing the log).

A good follow-up point: "consistency" is the only one that is partly the application's job. See ACID Properties in Depth.

What is the difference between a primary key, a candidate key, a unique key and a foreign key?

A candidate key is a minimal set of attributes that identifies a row; the primary key is the one candidate you choose (not null, one per table). A unique constraint enforces uniqueness on another candidate (and in most systems allows nulls). A foreign key is a column that must match a key in another table (or be null), which enforces referential integrity. See Keys and Integrity Constraints.

What is normalization, and when would you denormalize?

Normalization organizes tables, guided by functional dependencies, so each fact is stored once, which prevents update, insertion and deletion anomalies. In one line each: 1NF means atomic values; 2NF removes dependence on part of a key; 3NF removes dependence on non-key attributes (transitive dependencies); BCNF requires every determinant to be a key. You denormalize deliberately when read speed matters more than write simplicity, for example storing a precomputed total or duplicating a name in a reporting table, and you accept responsibility for keeping the copies in sync. See 1NF, 2NF and 3NF and Multivalued Dependencies, 4NF and Denormalization.

What is the difference between 3NF and BCNF?

3NF allows a dependency X -> A if X is a superkey or A is a prime attribute. BCNF allows only the first. BCNF is stricter and removes more redundancy, but sometimes cannot preserve every dependency, while a lossless, dependency-preserving 3NF design always exists. See BCNF and Lossless Decomposition.

SQL questions

What are the types of joins, and what does each return?

An inner join returns only the matching pairs. A left (right) outer join returns every row from the left (right) table, with nulls for missing matches. A full outer join keeps both sides. A cross join returns every combination. A self join joins a table to itself, such as employees to their bosses. The next demo shows how an inner join silently drops rows that an outer join keeps.

What is the difference between WHERE and HAVING?

WHERE filters rows before grouping and cannot use aggregates; HAVING filters groups after aggregation. Always filter early with WHERE where you can: it reduces the work the grouping has to do.

Why can NOT IN give a wrong answer?

If the subquery returns even one NULL, x NOT IN (...) evaluates to unknown for every row, so nothing is returned. NOT EXISTS does not have this problem. It is the best-known example of SQL's three-valued logic (Nulls and Three-Valued Logic).

SQL Joins, WHERE vs HAVING and the NOT IN trap

Press Run (or Ctrl + Enter) to see the result here.

Read the five results in order. The inner join counts only two departments (and loses Di, who has no department); the left join adds "Empty dept" with 0. The NOT IN query returns nothing because boss_id contains a NULL, while NOT EXISTS correctly finds Bo and Di, the employees who manage nobody. The last query keeps only departments with more than one employee.

What is the difference between DELETE, TRUNCATE and DROP?

DELETE removes chosen rows, can have a WHERE, fires row triggers and is logged row by row. TRUNCATE removes all rows quickly, usually without row-level logging, and often resets counters. DROP removes the table itself, structure and all. In many systems TRUNCATE and DROP are DDL, so they commit implicitly; SQLite has no TRUNCATE (use DELETE without a WHERE).

How do you find the second-highest salary?

Three standard answers exist. Take the maximum of the values below the maximum, skip the first row of a descending list with OFFSET, or rank with a window function. Use DISTINCT or DENSE_RANK when ties at the top should count once.

SQL Second-highest salary, three ways

Press Run (or Ctrl + Enter) to see the result here.

All three print 7000. Without DISTINCT, the OFFSET version would return 9000, because the top salary appears twice.

What is the difference between CHAR and VARCHAR?

CHAR(n) is fixed length: a shorter value is padded with spaces up to n characters, so every value takes the same space. VARCHAR(n) is variable length: it stores only the characters present plus a small length prefix, up to a maximum of n. Use CHAR for values that really have one length (country codes, fixed-size hashes) and VARCHAR for names, emails and text. Padding and comparison rules differ by engine. SQLite is the odd one out: it uses type affinity, so CHAR(5) and VARCHAR(5) behave identically and the length is not enforced.

SQL SQLite does not enforce CHAR(n) or VARCHAR(n)

Press Run (or Ctrl + Enter) to see the result here.

A server DBMS would pad the first CHAR value to 5 characters and reject the second row with a "value too long" error. In SQLite both rows are stored exactly as given.

What is a multiple-row subquery?

A subquery that returns more than one row. It cannot be compared with a plain =; use IN, NOT IN, EXISTS, or (where supported) ANY and ALL. For example, WHERE salary IN (SELECT salary FROM emp WHERE dept = 'A') keeps the employees who earn any salary that department A pays. SQLite has no ANY/ALL; salary > ANY (subquery) is the same as salary > (SELECT MIN(...)), and > ALL is the same as > (SELECT MAX(...)). A subquery that returns exactly one row and one column is a single-row (scalar) subquery, and a subquery that refers to the outer query is correlated.

What are embedded SQL and dynamic SQL?

Embedded (static) SQL puts SQL statements inside a host-language program. A precompiler or the driver checks them and prepares the plan before the program runs, which is fast and safe, but the statement text is fixed. Dynamic SQL builds the statement as a string at run time, for example when a search form lets the user pick the columns and filters. It is flexible, but the statement must be parsed and planned when it runs, and string concatenation of user input opens the door to SQL injection. Always combine dynamic SQL with bind parameters (? or :name) for values; identifiers such as column names cannot be parameters, so check them against a fixed allow-list. Libraries expose this as prepared statements, and stored procedures offer EXECUTE or EXECUTE IMMEDIATE. See Database Languages and Triggers and Stored Logic.

Transactions and concurrency

What are the isolation levels and the anomalies they prevent?

Level Dirty read Non-repeatable read Phantom
Read uncommitted possible possible possible
Read committed prevented possible possible
Repeatable read prevented prevented possible (by the standard)
Serializable prevented prevented prevented

A dirty read sees uncommitted data. A non-repeatable read reads the same row twice and gets different values. A phantom re-runs a range query and gets new rows. The standard table describes the minimum; real systems differ (snapshot isolation also prevents phantoms but allows write skew). See Isolation Levels and Concurrency Anomalies.

What is a deadlock, and how is it handled?

A deadlock is a cycle of transactions each waiting for a lock another holds. The database can prevent them (wait-die and wound-wait use timestamps to decide who waits), detect them (find a cycle in the wait-for graph and abort a victim) or fall back to timeouts. Applications reduce them by locking in a consistent order and keeping transactions short. See Deadlocks: Detection, Prevention and Recovery.

What is two-phase locking, and how does it differ from MVCC?

In 2PL a transaction acquires locks in a growing phase and releases them only in a shrinking phase, which guarantees serializability; strict 2PL holds write locks until commit, which also avoids cascading aborts. MVCC keeps several versions of each row, so readers see a consistent snapshot without blocking writers and writers do not block readers. The cost is storage for old versions and cleanup work. See Two-Phase Locking and MVCC and Optimistic Concurrency Control.

How does a database recover from a crash?

It uses write-ahead logging: a change is described in the log, and the log is flushed before the changed data page (and before the commit is acknowledged). After a crash, recovery redoes the changes of committed work that did not reach the disk and undoes the changes of unfinished transactions that did. Because the log is sequential, this is also fast. See Logging and Write-Ahead Logging.

SQL Atomicity and an index, live

Press Run (or Ctrl + Enter) to see the result here.

The first block shows atomicity: after the ROLLBACK the balances are back at 100 and 50, as if the half-finished transfer never happened. The committed transfer then changes both rows together (40 and 110). The last three plans are SQLite's own account of what it does: without an index it must SCAN the whole table, with the index it can SEARCH directly, and a range on an unindexed column still scans.

Storage and indexing

What is an index, and what does it cost?

An index is a separate, ordered structure, usually a B+ tree, that maps column values to row locations, so a lookup takes a few page reads instead of a scan. The price is extra storage and slower writes: every insert, update of an indexed column and delete must also maintain each index. An index also helps only when the query is selective and uses the indexed columns in a compatible way, for example the leading column of a composite index. See Indexing Basics.

What is the difference between a clustered and a non-clustered index?

A clustered index determines the physical order of the rows themselves (so there can be only one per table); the leaf level holds the rows. A non-clustered (secondary) index has its own structure whose leaves hold the key plus a pointer to the row or to the primary key. A covering index contains every column the query needs, so the table need not be touched at all.

Why do databases use B+ trees rather than binary trees or hash tables?

Disk access is by page, so we want a tree with a very high fan-out and a tiny height: three or four levels can hold hundreds of millions of keys. A B+ tree keeps data only in the leaves, which are linked, so range scans and ordering are cheap. A hash index gives constant-time equality lookups but no ordering or ranges. See B-Trees and B+ Trees and Hashing: Static, Extendible and Linear.

Query processing and distributed systems

How does the database decide how to run a query?

The parser produces a tree, the optimizer rewrites it using algebra rules (push selections down), enumerates plans (join order, access paths, join algorithms) and picks the one with the lowest estimated cost, using statistics about table sizes and value distributions. Stale statistics are the usual reason for a bad plan. See Statistics, Cost Estimation and Plan Search.

When is each join algorithm best?

A nested loop join is fine for tiny inputs or when an index on the inner table makes each probe cheap. A hash join is usually the best for large unsorted equality joins. A sort-merge join wins when the inputs are already sorted, or when a sorted result is needed anyway. See Join Algorithms.

What is the CAP theorem?

When a network partition happens, a distributed system must choose between consistency (every read sees the latest write) and availability (every request gets an answer). Partitions cannot be ruled out, so the real choice is C or A during a partition; the PACELC extension adds that even without partitions there is a trade-off between latency and consistency. See CAP, BASE and Consistency Models.

What is the difference between replication and sharding?

Replication copies the same data to several nodes for availability and read scaling. Sharding splits the data across nodes so each holds only a part, for write and storage scaling. Real systems combine them: each shard is replicated. Sharding makes cross-shard joins and transactions hard. See Replication, Sharding and Distributed Transactions.

Python A deadlock cycle and the quorum rule

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The first part finds a deadlock by following "waits for" edges until a node repeats. The second shows why R + W > N matters: only then must every read quorum share at least one replica with every write quorum, so a read cannot miss the latest write. With three replicas, W=2, R=2 is the common compromise.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A subquery in x NOT IN (...) returns a NULL among its values. What does the NOT IN predicate evaluate to for rows that are not matched?

    x NOT IN (a, NULL) means x <> a AND x <> NULL. The second part is unknown, so the whole predicate is never true. NOT EXISTS avoids the problem.
  2. 2. Which pair of statements about indexes is correct?

    An index lets reads find rows in a few page reads, but each write must also update the index.
  3. 3. What does it take for a read quorum to always see the latest acknowledged write in a replicated store with N replicas?

    When R + W > N the two quorums must overlap in at least one replica, which holds the newest version.
  4. 4. Which of these is NOT a way databases deal with deadlocks?

    Constraints are unrelated to lock cycles. Databases prevent, detect or time out deadlocks.
  5. 5. Why do relational engines prefer B+ trees for ordered indexes?

    Few levels mean few page reads per lookup, and the leaf chain serves ranges and ordering.

Summary

  • Good interview answers define the idea, say why it exists, name the trade-off and give a small example.
  • ACID, keys, normal forms and the difference between 3NF and BCNF are the foundation questions.
  • Know the join types, WHERE versus HAVING, and the NOT IN plus NULL trap by heart.
  • For transactions, be able to name the anomalies, the isolation levels, how locking and MVCC differ, how deadlocks are handled and why the log makes recovery possible.
  • For storage, explain what an index costs, why B+ trees dominate, and when a hash index is the right tool.
  • For distributed systems, know CAP/PACELC, replication versus sharding and the quorum rule.

Next: DBMS Cheat Sheet.