Top 30 Database Interview Questions and Answers
30 multiple-choice questions on Database, drawn from 30 bites out of the 113 tagged Database on Tezvyn. 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.
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 primary challenge did the relational model address compared to earlier database systems?
Show the answer
Answer: c · The need for complex procedural code to navigate specific data storage paths
The card states that before the relational model, "Retrieving data required writing complex procedural code to navigate specific data paths," which was inflexible. The relational model aimed to separate the logical data structure from its physical implementation. Option A is a distractor because while hierarchical models were predecessors, the core problem was the procedural navigation within them, not just the representation of hierarchies.
Read the full bite: The Relational Model: Data as Simple Tables
Question 2 of 30
In FastAPI, why should an async database session dependency wrap yield in try and place await session.close() in finally?
Show the answer
Answer: a · It guarantees cleanup runs even if the path operation raises an exception.
A try/finally block guarantees that await session.close() runs even when the path operation raises an exception, preventing database connection leaks. The thread pool issue in distractor C is caused by using def instead of async def, not by omitting exception handling.
Question 3 of 30
Which statement best describes the core trade-off when selecting a lower transaction isolation level?
Show the answer
Answer: d · It aims to maximize concurrent transaction execution, potentially allowing more data anomalies.
The card states that lower isolation levels increase concurrency by using fewer locks, but this comes at the cost of allowing more types of data anomalies. Option D accurately captures this fundamental trade-off between maximizing concurrent execution and the risk of data anomalies. Option B describes the characteristics of higher isolation levels, not lower ones.
Read the full bite: Transaction Isolation Levels: The Concurrency vs. Correctness Dial
Question 4 of 30
Under what specific condition might a database designer intentionally opt for a 3NF schema instead of striving for BCNF?
Show the answer
Answer: b · If achieving BCNF would make it impossible to enforce a critical functional dependency using a key constraint.
The card explicitly states that the primary reason not to use BCNF is the trade-off with dependency preservation, where achieving BCNF might prevent enforcing an original functional dependency with a key constraint. Option D describes the goal of BCNF, which is stricter than 3NF in eliminating anomalies, making it an incorrect reason to choose 3NF.
Read the full bite: Boyce-Codd Normal Form (BCNF): Stricter Than 3NF
Question 5 of 30
What is the primary purpose of creating an Entity-Relationship Diagram (ERD)?
Show the answer
Answer: a · To provide a visual blueprint for a relational database schema before implementation.
An ERD serves as a blueprint for designing a relational database schema before any tables are created, preventing costly rework. It is explicitly stated that designing the model to match the UI is a common pitfall, and ERDs are less useful for NoSQL databases.
Read the full bite: Entity-Relationship Diagrams: A Blueprint for Your Data
Question 6 of 30
In the context of database design, what does the functional dependency "A -> B" primarily signify?
Show the answer
Answer: c · For every unique value in column A, there is exactly one corresponding value in column B.
A functional dependency A -> B means that if you know the value(s) in column A, you can uniquely determine the value(s) in column B. Option B describes a foreign key relationship, which is a different concept from functional dependency.
Read the full bite: Functional Dependency: The Rules Behind Your Data
Question 7 of 30
Which scenario represents a violation of Third Normal Form (3NF)?
Show the answer
Answer: a · A non-key attribute in a table is fully dependent on another non-key attribute.
Third Normal Form (3NF) specifically addresses transitive dependencies, which occur when a non-key attribute depends on another non-key attribute instead of directly on the primary key. Options A and C describe violations of First Normal Form (1NF) and Second Normal Form (2NF), respectively.
Read the full bite: Third Normal Form (3NF): Nothing But The Key
Question 8 of 30
In a Compose project, why does the web app use db (the service name) rather than localhost to reach the database?
Show the answer
Answer: d · From inside the web container, localhost is the web container itself; db resolves via Docker DNS to the database
Each container's localhost refers to itself, so the web container must use the database's service name, which Docker's embedded DNS resolves to the right container; the localhost-blocking and protocol claims are false.
Read the full bite: How Compose services reach each other by name
Question 9 of 30
What specific type of data redundancy does Fourth Normal Form (4NF) primarily address that BCNF does not?
Show the answer
Answer: c · Redundancy from combining multiple independent lists of facts about a single entity.
Correct answer C directly describes the core problem 4NF solves: the redundancy arising when a table combines two or more independent, multi-valued lists of facts about a single entity. Option A describes the type of redundancy addressed by BCNF, which focuses on functional dependencies where a determinant is not a superkey, not on independent multi-valued relationships.
Read the full bite: Fourth Normal Form (4NF): Isolating Independent Facts
Question 10 of 30
Which concurrency control strategy is best suited for a system with frequent data conflicts where preventing inconsistent states is prioritized?
Show the answer
Answer: b · Pessimistic concurrency, as it acquires locks before data modification, ensuring exclusive access.
Pessimistic concurrency is designed for high-contention environments with frequent conflicts, as it prevents inconsistent states by locking data before modification. Optimistic concurrency, while avoiding initial locks, would lead to frequent and costly transaction retries in such a scenario.
Read the full bite: Lock Now or Check Later: Optimistic vs. Pessimistic Concurrency
Question 11 of 30
When a database uses Write-Ahead Logging (WAL) for a data modification, what action occurs first?
Show the answer
Answer: b · A record detailing the intended modification is appended to the WAL file on disk.
The core principle of WAL is to first record the intended change in a sequential log file on disk. Only after this log entry is safely persisted does the database apply the change to its in-memory copy of the data, making option D incorrect as it happens later.
Read the full bite: Write-Ahead Logging (WAL): Survive Crashes by Journaling First
Question 12 of 30
What is the primary trade-off when implementing a snowflake schema compared to a star schema?
Show the answer
Answer: d · It reduces storage space but leads to slower query execution.
The card states that a snowflake schema "saves storage space by breaking down large dimension tables" but "comes at the cost of slower, more complex queries due to the increased number of joins required." Option B is incorrect because a snowflake schema reduces data redundancy through normalization, thereby enhancing data integrity, not increasing redundancy.
Read the full bite: Snowflake Schema: Trading Query Speed for Storage
Question 13 of 30
When a database detects a deadlock between two transactions, what is its typical immediate action to resolve the situation?
Show the answer
Answer: a · It aborts one of the transactions, rolling back its changes to free up resources.
The card explicitly states that the database 'breaks the stalemate by choosing one transaction as the 'victim,' aborting it, and rolling back all its changes.' Option B describes a prevention strategy that developers implement, not an automatic resolution action by the database during an active deadlock.
Question 14 of 30
What is the main advantage of using Strict Two-Phase Locking (S2PL) in a database system?
Show the answer
Answer: b · It prevents transactions from reading data that might later be rolled back.
S2PL's core benefit is preventing transactions from seeing uncommitted work, thereby avoiding cascading aborts and ensuring strong consistency. Option D is incorrect because S2PL actually reduces concurrency due to longer lock durations.
Read the full bite: Strict Two-Phase Locking (S2PL): Safety Over Speed
Question 15 of 30
Which statement best describes the core mechanism by which Timestamp Concurrency Control maintains database consistency?
Show the answer
Answer: d · It assigns timestamps to transactions and aborts those whose operations violate the established temporal order.
Timestamp Concurrency Control assigns a unique timestamp to each transaction and allows them to proceed optimistically. If a transaction's operations are found to violate the serializable order implied by these timestamps, it is aborted and restarted. Option C describes traditional pessimistic locking, which TCC is designed to avoid.
Read the full bite: Timestamp Concurrency Control: No Locks, Just Time
Question 16 of 30
What is the primary purpose of a database's query execution plan?
Show the answer
Answer: a · To identify the most cost-effective sequence of operations for data retrieval.
The query optimizer generates the plan to choose the most efficient method for data retrieval, estimating the 'cost' of various options and selecting the one with the lowest cost. Syntax validation is handled by the parser before plan generation.
Read the full bite: Query Execution Plan: The Database's Road Map
Question 17 of 30
Which task is a Time-Series Database (TSDB) uniquely optimized to perform efficiently?
Show the answer
Answer: c · Aggregating and analyzing millions of sensor readings collected every second over long periods.
The card states TSDBs are optimized for "constant, high-volume writes of timestamped records, and queries that aggregate data over time ranges," making them ideal for sensor data. Option D describes a strength of relational databases, which TSDBs are explicitly not suited for due to their lack of support for complex relationships.
Read the full bite: Time-Series Databases: Optimized for Data Over Time
Question 18 of 30
What is the main purpose of a database's query optimizer?
Show the answer
Answer: c · To automatically translate a declarative SQL query into the most efficient data retrieval strategy.
The query optimizer's core function is to take a declarative SQL query (what data is wanted) and determine the most efficient 'how' to retrieve it by selecting the best execution plan. While other options describe important database functions, they are not the primary role of the query optimizer, which focuses on execution strategy rather than data integrity, caching, or mere compilation.
Read the full bite: The Query Optimizer: Your Database's Internal GPS
Question 19 of 30
What is the fundamental reason querying NoSQL databases requires model-specific approaches rather than a universal language like SQL?
Show the answer
Answer: c · The query method in NoSQL is inherently dictated by the database's specific data model, such as key-value, document, or graph.
The card emphasizes that "The query method is tied to the data's shape" and "Querying in NoSQL is specific to the database's data model." This means the underlying data structure (key-value, document, graph) directly determines how data can be accessed. While NoSQL handles unstructured data, it also handles semi-structured data, and the core reason for model-specific querying is the data model itself, not solely the unstructured nature of the data.
Read the full bite: Querying NoSQL: It Depends on the Data Model
Question 20 of 30
What is the fundamental purpose of database statistics in a modern relational database system?
Show the answer
Answer: b · To enable the query optimizer to make informed, cost-based decisions on execution plans.
The card states that statistics provide the "necessary context for the optimizer to make an informed, cost-based decision" and act as an "intelligence report" for choosing the most efficient execution plan. Option A describes data integrity mechanisms like constraints, which are distinct from statistics.
Read the full bite: Database Statistics: The Query Optimizer's Internal Map
Question 21 of 30
What is the primary role of cardinality estimation in a database's query optimizer?
Show the answer
Answer: c · To estimate the data volume at each query processing step to select the most efficient execution plan.
Cardinality estimation's core purpose is to guess the number of rows processed at each step of a query, which is crucial for the optimizer to accurately estimate costs and choose the fastest execution plan. It provides an estimate, not an exact count, and focuses on intermediate steps rather than just the final result.
Read the full bite: Cardinality Estimation: How Databases Guess Query Costs
Question 22 of 30
Which statement accurately differentiates data masking from encryption?
Show the answer
Answer: d · Data masking creates a non-reversible, altered dataset, while encryption allows for original data recovery.
Data masking creates a new, permanently altered dataset for non-production use, making it irreversible. In contrast, encryption is reversible, allowing the original data to be recovered through decryption.
Read the full bite: Data Masking: Protect Data, Preserve Utility
Question 23 of 30
A business intelligence team needs to frequently run queries that calculate the average order value across all customers and product types. Which database storage organization would be most efficient for this task?
Show the answer
Answer: a · Column-oriented, as it allows the database to read only the necessary columns (e.g., order value, customer ID, product type) across many rows, reducing I/O.
Column-oriented storage is designed for Online Analytical Processing (OLAP) tasks like calculating averages over many records, as it stores column data contiguously, allowing the system to read only the specific columns required, thus minimizing disk I/O. Row-oriented storage, while efficient for retrieving entire records, would be inefficient here because it would force the system to read all columns for every row, even if only a few are needed for the calculation.
Read the full bite: Row vs. Columnar Storage: Organizing Data for Speed
Question 24 of 30
Which scenario best justifies using a heap file organization for a database table?
Show the answer
Answer: a · A staging table for ingesting large volumes of raw sensor data that will be processed later.
Heap file organization prioritizes extremely fast writes, making it ideal for bulk-loading data into staging tables before further processing. It is unsuitable for tables requiring frequent reads, updates, or complex queries, as these operations necessitate slow full table scans.
Read the full bite: Heap File Organization: Fast Writes, Slow Reads
Question 25 of 30
Under what condition is a nested loop join typically considered an efficient database operation?
Show the answer
Answer: c · When one of the tables is significantly smaller and can be designated as the outer table.
The card explicitly states that a nested loop join is efficient when one of the tables is very small, as making it the outer table results in very few scans of the larger inner table. While NLJ is a fundamental fallback, its efficiency is primarily determined by table sizes and indexing, not solely by memory constraints.
Read the full bite: Nested Loop Join: The Brute-Force Database Join
Question 26 of 30
Under which scenario would a database's query optimizer most likely choose a hash join?
Show the answer
Answer: a · Joining two large, unsorted tables on an equality condition, with one table significantly smaller.
A hash join is ideal for large, unsorted equijoins where one table is significantly smaller, allowing its hash table to fit in memory. For pre-sorted tables, a merge join is typically more efficient, and hash joins are unsuitable for non-equijoins.
Read the full bite: Hash Join: Faster Database Joins with Hash Tables
Question 27 of 30
What scenario makes a sort-merge join particularly efficient compared to other join methods?
Show the answer
Answer: c · When the data in both tables is already ordered by the join key.
A sort-merge join is most efficient when the tables are already sorted on the join key, as this allows the database to skip the expensive initial sort phase. Option D is incorrect because a hash join is typically faster when one table fits in memory.
Read the full bite: Sort-Merge Join: The 'Line Up and Walk' Join
Question 28 of 30
Why are committed Sequelize migrations preferred over each developer manually altering their local database?
Show the answer
Answer: c · They give every environment the same ordered, reproducible, reversible schema changes
Migrations are version-controlled scripts with up/down applied in order, so all environments converge on an identical schema with rollback support. They concern structure, not query speed, and are distinct from seeders.
Read the full bite: Database migrations with the Sequelize CLI
Question 29 of 30
What is the primary factor limiting the speed of External Merge Sort?
Show the answer
Answer: d · The speed of disk I/O operations.
The card explicitly states that "its speed is limited by disk I/O, not CPU." While RAM size and the efficiency of the in-memory sort are important for overall performance, the fundamental bottleneck for external merge sort is the slow speed of reading from and writing to disk.
Read the full bite: External Merge Sort: Sorting Data Bigger Than RAM
Question 30 of 30
When a database's query rewriter reorders operations like filtering and joining, what is its main objective?
Show the answer
Answer: b · To achieve the identical final result set using a more performant execution strategy.
The card states the rewriter's goal is to transform queries into "faster equivalents" that "always produce the same final result" by reordering operations. Option A is incorrect because the rewriter often changes the user's written order to optimize performance.
Read the full bite: Query Rewriting: Your Database's Unseen Optimizer
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.