Top 30 Intermediate Databases & Architecture Interview Questions and Answers

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.

1. Question 1 of 30

   When is it appropriate to intentionally violate 3NF by duplicating a customer name in an orders table?

   • aWhen preventing transitive dependencies is the top priority
   • bWhen the orders table currently contains non-atomic repeating groups
   • cWhen read latency is critical and avoiding joins outweighs update anomaly risks
   • dWhen ensuring every attribute depends on the full primary key
   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.

2. Question 2 of 30

   Which statement best captures the mechanical cost of maintaining multiple indexes on a write-heavy table?

   • aEach write requires updating only the indexes whose key columns changed, minimizing I/O for unrelated indexes
   • bThe database uses binary search trees for indexes, causing frequent full-tree rebalancing on every insert
   • cEvery table write typically triggers random I/O to update each index's B-Tree, plus node splits and log overhead
   • dIndexes are stored as hash maps, so range queries require full table scans but writes remain cheap
   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

3. Question 3 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?

   • aNOT IN is safe because SQL treats NULL as false in subquery comparisons.
   • bNOT EXISTS is preferred because it is immune to NULLs in the subquery and avoids duplicate rows.
   • cINNER JOIN with a not-equal condition can return users who have no matching orders.
   • dLEFT JOIN with IS NULL is safest because it automatically removes duplicate users from the result.
   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

4. Question 4 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?

   • aHAVING and WHERE are logically identical, but HAVING is the conventional clause for grouped results
   • bWHERE is evaluated before GROUP BY, so the aggregate count does not yet exist
   • cHAVING is more efficient than WHERE for aggregate filters because it processes fewer rows
   • dWHERE can accept aggregate functions only if the identical expression also appears in the SELECT list
   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?

5. Question 5 of 30

   A category tree is read constantly to render menus but almost never restructured. Which model best fits, and why?

   • aNested set, because subtree reads are a single range query
   • bAdjacency list, because moving nodes touches many rows
   • cAdjacency list, because subtree reads need no recursion
   • dNested set, because inserts only change one row
   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

6. Question 6 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?

   • aThe table has no candidate keys at all
   • bTeacher is a foreign key to another table
   • cTeacher to Subject is a trivial dependency
   • dSubject is a prime attribute, part of a candidate key
   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

7. Question 7 of 30

   Why does MVCC let a long-running read query avoid blocking concurrent writers?

   • aIt downgrades all writes to read-only during the query
   • bWriters create new row versions while the reader keeps seeing its consistent snapshot
   • cIt caches the entire table in memory before reading
   • dIt escalates to a table lock that writers respect
   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.

   Read the full bite: How MVCC enables non-blocking reads

8. Question 8 of 30

   Under Two-Phase Locking, what action is forbidden once a transaction has released its first lock?

   • aAcquiring any new lock
   • bReading already-locked rows
   • cCommitting the transaction
   • dReleasing any further locks
   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.

   Read the full bite: Two-Phase Locking and serializability

9. Question 9 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?

   • aCost only reflects network latency, not CPU or disk work
   • bCost is accurate but only for the first execution before caching
   • cCost is an abstract optimizer unit, not a time measurement; only EXPLAIN ANALYZE gives real timings
   • dCost is always measured in microseconds, so they are off by a factor of one thousand
   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.

   Read the full bite: What is a query execution plan?

10. Question 10 of 30

    On a high-throughput insert table, which operation is most directly penalized by adding several indexes, and why?

    • aSchema changes only, because indexes lock the table definition
    • bReads, because more indexes confuse the optimizer into full scans
    • cWrites, because every INSERT must also add entries to each index
    • dNothing, because indexes are maintained lazily in the background
    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.

    Read the full bite: Trade-offs of adding indexes to a table

11. Question 11 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?

    • aBecause status is too low cardinality to index at all
    • bBecause covering indexes cannot include filter columns
    • cBecause single-column indexes can never cover any query
    • dBecause the index lacks the name column, forcing heap lookups to fetch 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.

    Read the full bite: What is a covering index?

12. Question 12 of 30

    Why is a hash index a poor choice for a query using WHERE price BETWEEN 10 AND 50 ORDER BY price?

    • aHash indexes are slower than B-Trees even for the equality part
    • bHash indexes do not support numeric columns
    • cHashing discards key ordering, so it cannot scan ranges or return sorted rows
    • dHash indexes can only index unique columns
    Show the answer

    Answer: c · Hashing discards key ordering, so it cannot scan ranges or return sorted rows

    A hash index maps keys to buckets with no ordering, making range scans and ORDER BY impossible to serve. The other claims are false: hash can be fast for equality, supports numeric and non-unique keys.

    Read the full bite: B-Tree versus Hash indexes

13. Question 13 of 30

    What is the main trade-off when increasing checkpoint frequency?

    • aSlower recovery but lower disk usage
    • bFaster recovery but more frequent dirty-page flush I/O during normal operation
    • cIt truncates the WAL more aggressively, risking data loss
    • dIt disables durability to gain speed
    Show the answer

    Answer: b · Faster recovery but more frequent dirty-page flush I/O during normal operation

    More frequent checkpoints bound replay work so recovery is faster, but they flush dirty pages more often, adding write I/O and latency risk at runtime. Checkpoints do not truncate the WAL or weaken durability.

    Read the full bite: Database checkpoints with WAL

14. Question 14 of 30

    Why can inserting rows with random primary-key values degrade a clustered (index-organized) table more than a heap?

    • aClustered tables must place each row in key order, causing mid-tree page splits and fragmentation
    • bHeaps store data in key order while clustered tables do not
    • cRandom keys disable the buffer pool for clustered tables
    • dHeaps reject random keys, forcing sequential inserts
    Show the answer

    Answer: a · Clustered tables must place each row in key order, causing mid-tree page splits and fragmentation

    A clustered table physically orders rows by key, so random keys force insertions into the middle and trigger page splits, while a heap simply appends. The last option reverses the definitions; heaps are unordered.

    Read the full bite: Heap file versus clustered index

15. Question 15 of 30

    At COMMIT for a row update, what must be durably written before the database acknowledges success?

    • aBoth the WAL and the data page, atomically together
    • bThe WAL records for the transaction, fsynced to disk; the dirty data page is flushed later
    • cNothing; the commit is acknowledged from memory and synced overnight
    • dThe modified data page itself, flushed to the data file
    Show the answer

    Answer: b · The WAL records for the transaction, fsynced to disk; the dirty data page is flushed later

    Write-ahead logging requires only the log records to be durably flushed at commit; the dirty data page is written back asynchronously by a checkpoint. Forcing the data page or syncing nothing both violate the WAL durability model.

    Read the full bite: Lifecycle of a single row update

16. Question 16 of 30

    After locating the leaf page holding the lower bound, how does a B+ Tree efficiently return the rest of a range?

    • aIt follows the sorted linked list between leaf pages, reading consecutive leaves until the upper bound
    • bIt loads the entire tree into memory and filters
    • cIt re-traverses from the root once per matching row
    • dIt switches to a hash lookup for each remaining key
    Show the answer

    Answer: a · It follows the sorted linked list between leaf pages, reading consecutive leaves until the upper bound

    Leaf pages are linked in sorted order, so the engine simply walks that chain from the start key onward, avoiding repeated root descents. Re-traversing per row or loading the whole tree would be far more expensive.

    Read the full bite: B+ Tree range queries across pages

17. Question 17 of 30

    When would a sort-merge join be clearly preferable to a hash join?

    • aWhen both inputs already arrive sorted on the join key or the output must be ordered
    • bWhen the join key is a hashed surrogate column
    • cWhen the join uses a strict equality predicate and memory is plentiful
    • dWhen one input is tiny and fits entirely in memory
    Show the answer

    Answer: a · When both inputs already arrive sorted on the join key or the output must be ordered

    Sort-merge avoids sort cost when inputs are pre-sorted and produces ordered output for free, making it ideal there. Plentiful memory with equality and a tiny build side are exactly the conditions that favor a hash join.

    Read the full bite: Hash join versus sort-merge join

18. Question 18 of 30

    Why must a database use external merge sort rather than a normal in-memory sort for a 100GB ORDER BY with 1GB of RAM?

    • aORDER BY is not allowed on large tables without an index
    • bThe optimizer always sorts on disk regardless of size
    • cThe data far exceeds RAM, so it is sorted as memory-sized runs on disk and then merged
    • dIn-memory sorts cannot handle more than a million rows
    Show the answer

    Answer: c · The data far exceeds RAM, so it is sorted as memory-sized runs on disk and then merged

    Because the dataset dwarfs available memory, the engine sorts memory-sized chunks into runs on disk and merges them in passes. In-memory sorts have no fixed row cap, and the engine uses memory when data does fit.

    Read the full bite: Sorting data larger than memory

19. Question 19 of 30

    Why is embedding the full list of liking user IDs inside each post document a poor choice for a popular social platform?

    • aReferencing is impossible without joins, which document stores lack
    • bThe liker array is unbounded, so it can exceed the document size limit and forces rewriting the whole document on each like
    • cEmbedding always violates ACID guarantees
    • dDocument databases cannot store arrays at all
    Show the answer

    Answer: b · The liker array is unbounded, so it can exceed the document size limit and forces rewriting the whole document on each like

    Unbounded growth collides with the document size cap and causes costly full-document rewrites and contention per like. Document stores do support arrays and offer lookups, so the other options are false.

    Read the full bite: Embed or reference likes in a document database?

20. Question 20 of 30

    Which feature is the clearest case where eventual consistency would be unacceptable and strong consistency is required?

    • aDisplaying the number of views on a video
    • bRendering a social media activity feed
    • cDecrementing the remaining stock of a limited-edition item at checkout
    • dShowing a user's follower count
    Show the answer

    Answer: c · Decrementing the remaining stock of a limited-edition item at checkout

    Limited inventory must be strongly consistent or two buyers could purchase the same last unit. View counts, follower counts, and feeds tolerate brief staleness without causing real harm.

    Read the full bite: What are eventual consistency and the BASE model?

21. Question 21 of 30

    Which architectural property of Cassandra most directly removes a write bottleneck for a high-write user profile service?

    • aA masterless peer-to-peer design where any replica can accept writes, with data spread by consistent hashing
    • bIt relies on joins to distribute write load across tables
    • cIt writes synchronously to all replicas before acknowledging
    • dA single primary node coordinates all writes for strong ordering
    Show the answer

    Answer: a · A masterless peer-to-peer design where any replica can accept writes, with data spread by consistent hashing

    Cassandra's leaderless ring lets any replica accept writes and spreads them via consistent hashing, eliminating a single write bottleneck. A single primary, synchronous all-replica writes, and joins would each constrain rather than help write throughput.

    Read the full bite: Why fit Cassandra to a high-read, high-write workload?

22. Question 22 of 30

    Which trade-off most accurately captures star versus snowflake schemas?

    • aSnowflake schemas always run faster because normalization speeds up joins
    • bStar schemas use fewer joins and simpler faster queries; snowflake schemas normalize dimensions to cut redundancy at the cost of more joins
    • cSnowflake schemas eliminate the fact table entirely
    • dStar schemas save more storage than snowflake schemas
    Show the answer

    Answer: b · Star schemas use fewer joins and simpler faster queries; snowflake schemas normalize dimensions to cut redundancy at the cost of more joins

    Stars denormalize for fewer joins and faster queries, while snowflakes normalize dimensions to reduce redundancy but add joins. More joins do not speed queries, stars use more storage, and both keep a fact table.

    Read the full bite: Star schema vs snowflake schema trade-offs?

23. Question 23 of 30

    Why does columnar storage accelerate an aggregation like AVG over one column across many rows?

    • aIt avoids compression so reads are simpler
    • bIt stores rows together so each row is read in a single seek
    • cIt stores each column contiguously, so the engine reads only the needed column and skips the rest, scanning far less data
    • dIt precomputes every possible aggregate at write time
    Show the answer

    Answer: c · It stores each column contiguously, so the engine reads only the needed column and skips the rest, scanning far less data

    Columnar layout co-locates a column's values, letting the engine read only that column and skip the others, cutting I/O sharply, with better compression on top. Row co-location, precomputing all aggregates, and avoiding compression are not how it works.

    Read the full bite: How does columnar storage speed up analytics?

24. Question 24 of 30

    How does a Type 2 slowly changing dimension correctly preserve history when a customer's address changes?

    • aIt overwrites the address in the existing row so the latest value is always shown
    • bIt inserts a new row with a new surrogate key, validity dates, and a current flag, expiring the old row
    • cIt stores both old and new addresses in two columns of the same single row
    • dIt deletes the old customer record and creates a fresh natural key
    Show the answer

    Answer: b · It inserts a new row with a new surrogate key, validity dates, and a current flag, expiring the old row

    Type 2 versions the dimension by adding a new row with a new surrogate key and validity dates while expiring the prior row, so facts stay tied to the version current at their time. Overwriting is Type 1, and the two-column approach is Type 3.

    Read the full bite: What is a Type 2 slowly changing dimension?

25. Question 25 of 30

    A banking ledger refuses writes on a node it cannot safely coordinate during a network partition. How does this position it on the CAP trade-off?

    • aIt is an AP system favoring availability over consistency
    • bIt proves you can have all three of C, A, and P at once
    • cIt avoids the CAP trade-off entirely by being a single server
    • dIt is a CP system sacrificing availability to preserve consistency during the partition
    Show the answer

    Answer: d · It is a CP system sacrificing availability to preserve consistency during the partition

    Refusing writes to avoid divergence trades availability for consistency, the defining behavior of a CP system under partition. Serving despite staleness would be AP, and CAP's trade-off cannot be escaped in a distributed system.

    Read the full bite: Apply the CAP theorem to a real system

26. Question 26 of 30

    What is the central trade-off between range-based and hash-based sharding?

    • aRange sharding enables efficient range scans but risks hot spots on monotonic keys; hash sharding spreads load evenly but makes range queries inefficient
    • bRange sharding spreads writes evenly while hash sharding concentrates them on one shard
    • cBoth strategies handle range queries equally well and neither risks hot spots
    • dHash sharding makes range queries efficient while range sharding scatters them
    Show the answer

    Answer: a · Range sharding enables efficient range scans but risks hot spots on monotonic keys; hash sharding spreads load evenly but makes range queries inefficient

    Range sharding keeps ordered keys together for fast range scans but concentrates monotonic writes on one shard, while hashing distributes load evenly at the cost of efficient range queries. The other options invert these properties.

    Read the full bite: Range-based vs hash-based sharding trade-offs?

27. Question 27 of 30

    A team moves from leader-follower to multi-leader replication across two regions. Which new problem must they now explicitly design for?

    • aThe leader becoming a single point of failure
    • bConcurrent conflicting writes to the same record
    • cRead replicas becoming stale
    • dLoss of all transaction support
    Show the answer

    Answer: b · Concurrent conflicting writes to the same record

    Multiple leaders accepting writes asynchronously means two regions can edit the same record concurrently, requiring conflict resolution. Stale read replicas also exist in plain leader-follower, so that is not the new problem multi-leader introduces.

    Read the full bite: Leader-follower vs multi-leader replication

28. Question 28 of 30

    Which feature is the WEAKEST candidate for eventual consistency and most likely needs strong consistency instead?

    • aUpdating a trending-topics list
    • bDecrementing limited inventory at checkout
    • cShowing a user's follower number
    • dDisplaying a post's like count
    Show the answer

    Answer: b · Decrementing limited inventory at checkout

    Selling the same last unit twice due to a stale read causes real harm, so inventory decrement at checkout needs strong consistency. Like counts, follower numbers, and trending lists tolerate brief staleness without consequence.

    Read the full bite: What is eventual consistency?

29. Question 29 of 30

    Why can setting a connection pool's maximum size too high actually hurt overall throughput?

    • aPools larger than 10 are rejected by drivers
    • bIt can exceed the database's connection limit and cause resource thrashing
    • cIt disables prepared statements
    • dLarger pools force every query to reconnect
    Show the answer

    Answer: b · It can exceed the database's connection limit and cause resource thrashing

    Too many connections can exhaust the database's connection ceiling and memory and cause excessive context switching, lowering throughput. Larger pools do not force reconnects or disable prepared statements, and drivers do not cap pools at 10.

    Read the full bite: Connection pooling and its key parameters

30. Question 30 of 30

    Besides a base backup, what is the essential component that makes Point-in-Time Recovery possible?

    • aA read replica kept in sync
    • bA second full backup taken hourly
    • cA continuous archive of the write-ahead/transaction log
    • dAn in-memory cache snapshot
    Show the answer

    Answer: c · A continuous archive of the write-ahead/transaction log

    PITR replays archived write-ahead log records over a base backup to reach a precise moment, so the log archive supplies the granularity. A replica or cache snapshot does not let you rewind to an arbitrary past instant.

    Read the full bite: Point-in-Time Recovery (PITR)