Top 30 Databases & Architecture Interview Questions and Answers
30 multiple-choice questions on Databases & Architecture, of the kind that come up in a technical interview, drawn from 30 bites in the Databases & Architecture library. Answer them here or read straight down. Every question carries the correct option, why it is correct, and a link to the bite it came from.
SQL, NoSQL, system design, microservices, APIs
30 questions. Pick an answer, or open “Show the answer” to read it.
Answers are graded in your browser. Nothing is saved, and no XP or streak is earned here. The app keeps score.
Question 1 of 30
What is the main reason to place a unique constraint on a non-primary column such as email?
Show the answer
Answer: c · To enforce business-level uniqueness while keeping a stable primary key for joins
The card gives email as an alternate key that stops duplicate signups while the auto-incrementing primary key stays the stable join target. The distractor that it serves as the main row identifier conflates a unique constraint with a primary key, a red flag the card explicitly warns against.
Read the full bite: What is the difference between primary, foreign, and unique keys?
Question 2 of 30
During a bank transfer, if the debit succeeds but the credit fails, which ACID property ensures the money does not vanish?
Show the answer
Answer: b · Atomicity, because the entire transaction must complete or roll back entirely
Atomicity guarantees that a transaction either fully completes or fully rolls back, preventing partial debits without matching credits. Consistency is a tempting distractor because it concerns valid state invariants, not the undo of incomplete operations.
Read the full bite: Explain ACID properties and why they matter for banking or e-commerce
Question 3 of 30
When adding a new column to a table and then filling it with values, how do the two types of SQL commands involved differ?
Show the answer
Answer: d · The first command is DDL that modifies the schema and may lock the table, while the second is DML that changes row data inside a transaction
Adding a column is DDL because it changes the schema and often locks the table, while updating rows is DML that operates row by row within a transaction. Option B is tempting because it labels the command types correctly, but it is wrong because DDL often auto-commits and cannot be rolled back in many engines, and DML—not DDL—runs inside explicit transactions.
Read the full bite: What is the difference between DDL and DML in SQL?
Question 4 of 30
When is it appropriate to intentionally violate 3NF by duplicating a customer name in an orders table?
Show the answer
Answer: c · When read latency is critical and avoiding joins outweighs update anomaly risks
Intentionally violating 3NF trades update anomaly risk for faster reads by eliminating joins, which is appropriate in read-heavy workloads. The most tempting distractor confuses the goal of normalization—preventing transitive dependencies—with a reason to denormalize.
Read the full bite: Describe 1NF, 2NF, 3NF, normalization's purpose, and its performance trade-off.
Question 5 of 30
Which statement best captures the mechanical cost of maintaining multiple indexes on a write-heavy table?
Show the answer
Answer: c · Every table write typically triggers random I/O to update each index's B-Tree, plus node splits and log overhead
The card explains that every write likely updates every index, causing extra random I/O, node splits, and WAL overhead. Option B reflects the common misconception that binary search trees are the classic disk structure, while D confuses hash indexes with the standard B-Tree approach.
Read the full bite: Explain database indexes, the classic data structure, and write-heavy trade-offs
Question 6 of 30
When the Orders table may contain NULL user_ids, which statement correctly explains the safest way to find users who never placed an order?
Show the answer
Answer: b · NOT EXISTS is preferred because it is immune to NULLs in the subquery and avoids duplicate rows.
NOT EXISTS handles NULL values safely and stops at the first match per user, avoiding duplicate rows. Option D is tempting because LEFT JOIN is a common pattern, but it can inflate results when a user has multiple orders unless you add DISTINCT or GROUP BY.
Read the full bite: Find users who never placed an order and explain JOIN choice
Question 7 of 30
When writing a query that groups by department and filters on COUNT(*) > 10, why must the predicate be in HAVING rather than WHERE?
Show the answer
Answer: b · WHERE is evaluated before GROUP BY, so the aggregate count does not yet exist
WHERE filters individual rows before grouping and aggregation occur, so aggregate values like COUNT(*) have not been computed yet and do not exist at that stage. Option D is wrong because repeating the aggregate expression in WHERE does not help; the aggregate still does not exist when WHERE is evaluated.
Read the full bite: What is the difference between WHERE and HAVING in SQL?
Question 8 of 30
A transaction reads a row twice and gets different committed values each time. Which isolation level permits this while still forbidding dirty reads?
Show the answer
Answer: d · Read Committed
Read Committed forbids dirty reads but allows non-repeatable reads, so committed changes can appear between two reads. Read Uncommitted would also allow dirty reads, which is stricter than the scenario describes.
Read the full bite: Read Committed versus Serializable isolation levels
Question 9 of 30
An endpoint fires 1 query for a list and then one extra query per item to load a relation. Which fix most directly reduces the number of round trips?
Show the answer
Answer: c · Eager-load the relation with a JOIN or single batched IN query
Eager loading collapses the per-item queries into one or two statements, attacking the round-trip count itself. Caching only masks the volume and adds invalidation work without removing the structural N+1 pattern.
Read the full bite: Diagnosing and fixing the N+1 query problem
Question 10 of 30
A social app adds a like_count column directly on the posts table instead of counting rows in a likes table on every read. According to the card, what new problem does this denormalization introduce?
Show the answer
Answer: a · The count can drift out of sync if updates aren't handled atomically, requiring periodic reconciliation
The card's example warns that a missed update or race condition can leave the count wrong, requiring periodic reconciliation, that is the core cost of denormalizing. The tempting wrong answer has it backwards: the point of adding the column is that reads become a fast single column fetch, not a join.
Read the full bite: When to intentionally denormalize a schema
Question 11 of 30
In the normalized design, CustomerName and CustomerAddress are moved out of the Orders table primarily to eliminate which kind of dependency?
Show the answer
Answer: b · A transitive dependency through CustomerID
Customer attributes depend on CustomerID, a non-key column in Orders, so the dependency is transitive and removing it achieves 3NF. A partial dependency would involve only part of a composite primary key, which is a 2NF concern.
Question 12 of 30
Why can't a many-to-many relationship between Students and Courses be modeled with just a single foreign key column on one of those tables?
Show the answer
Answer: c · A single foreign-key column can hold only one related value per row
One foreign-key column stores a single reference per row, so it can only express one side relating to many, not both sides being many. A junction table with two foreign keys is required to represent the full set of pairings.
Read the full bite: Modeling one-to-many versus many-to-many relationships
Question 13 of 30
A category tree is read constantly to render menus but almost never restructured. Which model best fits, and why?
Show the answer
Answer: a · Nested set, because subtree reads are a single range query
Nested set encodes descendants as a left/right range, so a whole subtree is one indexed query, ideal for read-heavy trees. Its weakness is expensive writes, which barely matters here since the tree is almost never restructured.
Read the full bite: Adjacency List versus Nested Set for hierarchies
Question 14 of 30
A table is in 3NF but not BCNF because of a dependency Teacher to Subject where Teacher is not a superkey. What makes this still acceptable for 3NF?
Show the answer
Answer: d · Subject is a prime attribute, part of a candidate key
3NF permits a non-superkey determinant when the dependent attribute is prime, so Subject being part of a candidate key keeps it in 3NF. BCNF has no such exception, which is exactly why the table violates BCNF.
Read the full bite: 3NF versus BCNF and the overlapping-key gap
Question 15 of 30
Why can't a standard foreign key enforce integrity on the commentable_id column in a polymorphic comments table?
Show the answer
Answer: b · A foreign key can reference only one specific table, not a target chosen at runtime
A foreign key is bound to one referenced table at definition time, so it cannot validate an ID whose parent table varies per row. Foreign keys can reference any unique column and text can be indexed, so those options are wrong.
Read the full bite: Polymorphic associations and referential integrity
Question 16 of 30
A Cassandra feed table partitioned by user_id makes feed reads fast. What is the main cost this design imposes compared to a relational read-time join?
Show the answer
Answer: b · Each new post must be written into every follower's partition
Fan-out on write copies each post into all followers' partitions, creating heavy write amplification, which is the trade-off for cheap single-partition reads. Cassandra avoids cross-partition joins and favors availability over strong consistency, so those options are wrong.
Read the full bite: Relational versus wide-column for a news feed
Question 17 of 30
A users table is sharded by user_id. What is the most efficient way to support frequent logins that look users up by email?
Show the answer
Answer: a · Maintain a secondary email-to-user_id index to resolve the shard in one hop
A secondary mapping from email to user_id lets a login resolve the correct shard directly, avoiding a broadcast. A UNIQUE constraint only enforces uniqueness within a single shard, and scatter-gather wastes resources on every login.
Read the full bite: Shard key impact on uniqueness and cross-shard lookups
Question 18 of 30
Which ACID property guarantees that a committed transaction's effects will survive a server crash that happens immediately after commit?
Show the answer
Answer: c · Durability
Durability ensures committed changes persist to non-volatile storage and survive crashes. Atomicity governs all-or-nothing application before commit, not survival of already-committed data after a crash.
Question 19 of 30
Two requests each read a counter at 10, add one in application code, and write back. The final value is 11. What prevents this lost update most directly?
Show the answer
Answer: b · Performing the increment as a single atomic UPDATE that reads and writes under one lock
An atomic UPDATE counter = counter + 1 reads and writes the row under one lock, so concurrent increments serialize correctly. Merely raising the isolation level does not fix a read-modify-write performed in application code unless it also locks the row.
Question 20 of 30
Two transactions each hold a lock the other needs, forming a cycle. What does a typical database do to recover?
Show the answer
Answer: c · Detect the cycle, abort a chosen victim, and let the application retry
Engines detect the wait-for cycle and roll back one victim to release its locks, then the application retries that transaction. Waiting indefinitely or merging transactions is not how deadlock resolution works.
Read the full bite: Database deadlocks and how engines resolve them
Question 21 of 30
Why does MVCC let a long-running read query avoid blocking concurrent writers?
Show the answer
Answer: b · Writers create new row versions while the reader keeps seeing its consistent snapshot
Writers produce new versions rather than overwriting, so the reader continues consulting the versions visible at its snapshot without taking a lock. There is no table lock or write downgrade involved in MVCC reads.
Question 22 of 30
Under Two-Phase Locking, what action is forbidden once a transaction has released its first lock?
Show the answer
Answer: a · Acquiring any new lock
After the first release the transaction is in the shrinking phase and may only release, never acquire, which is what guarantees a conflict-serializable schedule. Continuing to release locks and committing are both allowed in the shrinking phase.
Question 23 of 30
Why is simple row-level locking insufficient to prevent write skew under snapshot isolation?
Show the answer
Answer: a · The two transactions write to different rows, so no lock conflict arises
Write skew involves transactions that update disjoint rows based on an overlapping read, so locking individual written rows produces no conflict. The shared dependency is a predicate over a set, which needs serializable isolation or predicate locks, not single-row locks.
Question 24 of 30
Under write-ahead logging, what must be guaranteed durable on disk before a transaction's commit is acknowledged?
Show the answer
Answer: c · The transaction's log records describing its changes
Only the log records must be flushed at commit, which is why commits are cheap yet durable; the data pages can be written later. Forcing all dirty data pages on every commit is exactly what the WAL avoids.
Read the full bite: How the write-ahead log ensures atomicity and durability
Question 25 of 30
Adding an index speeds up reads but is not free. What is the primary cost of maintaining an index?
Show the answer
Answer: d · It adds overhead to every insert, update, and delete plus extra storage
Every write must also update the index and the index occupies additional storage, which is the cost of faster reads. Indexes speed up selective reads rather than slowing them, so that option is wrong.
Read the full bite: What a database index is and when it helps
Question 26 of 30
You create a composite index on (last_name, first_name). Which query can it efficiently serve via the leftmost prefix?
Show the answer
Answer: a · A query filtering on last_name alone
The leftmost-prefix rule lets the index serve last_name alone or last_name plus first_name, since last_name is the leading column. A first_name-only filter cannot use the index efficiently because first_name is not a prefix of the index order.
Read the full bite: Composite index column order for multi-column filters
Question 27 of 30
Why can a table have only one clustered index but many non-clustered indexes?
Show the answer
Answer: a · Rows can be physically ordered only one way, and the clustered index defines that order
A clustered index sets the single physical ordering of the table's rows, so only one can exist; non-clustered indexes are separate structures with pointers, so a table can have many. Clustered indexes in fact excel at range queries, making that option wrong.
Question 28 of 30
A developer sees a high cost number in EXPLAIN output and concludes the query will take that many milliseconds. What is wrong with this reasoning?
Show the answer
Answer: c · Cost is an abstract optimizer unit, not a time measurement; only EXPLAIN ANALYZE gives real timings
Cost is a unitless estimate the optimizer uses to compare plans, not wall-clock time. Actual durations come from EXPLAIN ANALYZE; the other options invent fixed conversions or scopes that do not exist.
Question 29 of 30
On a high-throughput insert table, which operation is most directly penalized by adding several indexes, and why?
Show the answer
Answer: c · Writes, because every INSERT must also add entries to each index
Each index is a structure the engine must keep in sync, so every insert pays to update all of them, throttling write throughput. Reads generally benefit from indexes, and the lazy-maintenance claim is false for transactional engines.
Question 30 of 30
A query is SELECT name FROM t WHERE status = 'active'. Why does an index on (status) alone fail to be a covering index for it?
Show the answer
Answer: d · Because the index lacks the name column, forcing heap lookups to fetch it
Covering requires every referenced column, including projected ones, to live in the index; without name the engine must visit the heap. The other options misstate cardinality rules and falsely claim single-column or filter-column limits.
Could you explain these out loud?
That is what an interview actually tests. Tezvyn gives you questions like these with what the interviewer is really checking, the answer that lands, and the mistake that ends the conversation, in the four minutes before your next meeting.
The iPhone app is on the way
We are building it. Until it lands, nothing here is held back from you: every interview card, your saved cards, streaks and the job board all work in Safari, plus hundreds of free practice quizzes of thirty questions each. Sign in and it all carries over to the app the day it arrives.
Want it as an icon? Tap Share at the bottom of Safari, then Add to Home Screen. It opens full screen and the cards you have read stay available offline.