Top 30 Databases 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

   What is the main reason to place a unique constraint on a non-primary column such as email?

   • aTo prevent NULL values from being inserted into that column
   • bTo create a performance index for faster lookups on that column
   • cTo enforce business-level uniqueness while keeping a stable primary key for joins
   • dTo serve as the main row identifier that other tables use for relationships
   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?

2. Question 2 of 30

   What is the primary function of a Database Management System (DBMS)?

   • aTo develop the user interface and business logic for applications.
   • bTo be the physical collection of all stored data and related files.
   • cTo manage and control how applications interact with and access stored data.
   • dTo provide a structured query language (SQL) for data manipulation.
   Show the answer

   Answer: c · To manage and control how applications interact with and access stored data.

   The card states the DBMS acts as a "single, controlled gateway" and a "librarian" to manage access and operations on data for applications. Option B describes the database itself, which the card identifies as a common misconception, incorrect confusion point.

   Read the full bite: DBMS: The Software That Runs Your Database

3. Question 3 of 30

   A team needs to speed up queries on a large table and purge old records. Which task requires DDL?

   • aUpdating the status of expired records to Inactive
   • bAdding an index on the date column to speed up filtering
   • cQuerying all active records for the monthly report
   • dRemoving rows older than two years to free up space
   Show the answer

   Answer: b · Adding an index on the date column to speed up filtering

   Adding an index changes the database schema, which is the purpose of DDL. Purging old rows may seem structural, but it only deletes data, making it a DML operation.

   Read the full bite: DDL: The Blueprint for Database Objects

4. Question 4 of 30

   During a bank transfer, if the debit succeeds but the credit fails, which ACID property ensures the money does not vanish?

   • aDurability, because committed changes must survive any system crash
   • bAtomicity, because the entire transaction must complete or roll back entirely
   • cIsolation, because concurrent transactions must not interfere with each other
   • dConsistency, because the database must enforce valid account balances after every update
   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

5. Question 5 of 30

   Which action is a clear use of DML because it changes stored data rather than table structure or read-only querying?

   • aAdding a new column to an existing table
   • bRunning a SELECT statement to retrieve rows
   • cCreating a new table in the database
   • dInserting a new row into an existing table
   Show the answer

   Answer: d · Inserting a new row into an existing table

   Inserting a new row is explicitly called a textbook DML operation that alters stored data. Running a SELECT statement is a tempting distractor because the card notes that read-only querying is sometimes classified separately as DQL rather than being grouped with DML.

   Read the full bite: DML: Insert, Update, and Delete

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

   • aBoth commands are DML because they alter the contents of the database
   • bThe first command is DDL and can always be rolled back inside the same transaction, while the second is DML that acquires aggressive schema locks
   • cThe first command is DML because it changes the table, and the second is DDL because it defines the new values
   • dThe 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
   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?

7. Question 7 of 30

   What is the primary reason to avoid using "natural keys" like email addresses as primary keys?

   • aTheir values can change over time, complicating data management and relationships.
   • bThey are typically longer strings, which can slow down database queries.
   • cThey often contain sensitive personal information, posing security risks.
   • dThey cannot be automatically generated by the database system.
   Show the answer

   Answer: a · Their values can change over time, complicating data management and relationships.

   The card states that natural keys "can change, which creates a maintenance nightmare" and requires updating the value in every referencing table. This directly points to complications in data management and maintaining relationships. While other options might have some truth in different contexts, the card emphasizes changeability as the core issue.

   Read the full bite: Primary Keys: The Unique ID for Every Row

8. Question 8 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.

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

10. Question 10 of 30

    In a bank transfer, which ACID property guarantees that if the transaction fails midway, both accounts revert to their original state?

    • aIsolation
    • bConsistency
    • cDurability
    • dAtomicity
    Show the answer

    Answer: d · Atomicity

    Atomicity ensures that a transaction is treated as an 'all or nothing' operation; if any part fails, the entire transaction is rolled back. Consistency, while ensuring the database moves from one valid state to another, does not specifically handle the rollback of an incomplete operation in the same way Atomicity does.

    Read the full bite: ACID: The Four Guarantees of Database Transactions

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

12. Question 12 of 30

    What is the main objective of applying database normalization in a relational database?

    • aTo ensure data integrity and minimize redundancy by structuring data into smaller, related tables.
    • bTo facilitate the storage and retrieval of unstructured data types, such as documents or media files.
    • cTo consolidate all related data into a single, comprehensive table for easier access.
    • dTo optimize complex analytical queries by reducing the need for JOIN operations.
    Show the answer

    Answer: a · To ensure data integrity and minimize redundancy by structuring data into smaller, related tables.

    Normalization's primary goal is to eliminate data redundancy and prevent modification anomalies (update, insertion, deletion), thereby ensuring data integrity. Option D describes a benefit of denormalization, often used in analytical systems, which is the opposite of normalization's typical effect on JOINs.

    Read the full bite: Database Normalization: Tidy Tables, Less Redundancy

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

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

    • aNo standard level allows this
    • bRead Uncommitted
    • cSerializable
    • dRead Committed
    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

15. Question 15 of 30

    By default, what action does a relational database take when a user attempts to delete a record from a "parent" table that is referenced by a foreign key in a "child" table?

    • aIt converts the foreign key in the child table into a primary key to preserve the relationship.
    • bIt automatically sets the foreign key column in the child table to NULL for all referencing records.
    • cIt blocks the deletion of the parent record to maintain data integrity.
    • dIt automatically deletes all corresponding referencing records in the child table.
    Show the answer

    Answer: c · It blocks the deletion of the parent record to maintain data integrity.

    The card explicitly states that if you try to delete a user referenced by posts, "the database will, by default, block the deletion to avoid creating orphaned posts." This is the standard behavior to enforce referential integrity. Option B describes an 'ON DELETE SET NULL' action, which is a configurable behavior, not the default.

    Read the full bite: Foreign Keys: The Glue of Relational Databases

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

    • aIncrease the database connection pool size
    • bAdd a read-through cache in front of the queries
    • cEager-load the relation with a JOIN or single batched IN query
    • dAdd a covering index on the child table
    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

17. Question 17 of 30

    What is the main performance trade-off introduced by adding a database index?

    • aIt requires manual updates to maintain data consistency.
    • bIt increases the complexity of database schema design.
    • cIt makes queries on non-indexed columns significantly slower.
    • dIt speeds up data retrieval but slows down data modification operations.
    Show the answer

    Answer: d · It speeds up data retrieval but slows down data modification operations.

    The card explicitly states that indexes speed up data retrieval but add overhead to INSERT, UPDATE, and DELETE operations, as the index structure must also be modified. This trade-off between faster reads and slower writes is the primary consideration. Indexes do not inherently complicate schema design, slow down non-indexed queries, or require manual updates for consistency.

    Read the full bite: Database Index: The Phonebook for Your Data

18. Question 18 of 30

    What is the primary distinction between a standard database view and a base table?

    • aA view executes a predefined query against base tables each time it is accessed, without storing its own data.
    • bA view can only be used to restrict access to specific rows, while a base table allows full access.
    • cA view physically stores its own copy of the data, unlike a base table.
    • dA view automatically optimizes complex joins, making it inherently faster than querying base tables directly.
    Show the answer

    Answer: a · A view executes a predefined query against base tables each time it is accessed, without storing its own data.

    A standard view stores only its query definition and executes it against the underlying base tables each time it's accessed, as explained in the 'How It Works' section. Option C describes a common misconception; views do not store their own data, unlike materialized views.

    Read the full bite: Database Views: A Saved Query That Acts Like a Table

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

    • aThe count can drift out of sync if updates aren't handled atomically, requiring periodic reconciliation
    • bReads become slower because the column must still be computed with a join
    • cIt removes the need for any consistency handling since counts are now precomputed once
    • dIt only works if the underlying database doesn't support ACID transactions
    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

20. Question 20 of 30

    In the normalized design, CustomerName and CustomerAddress are moved out of the Orders table primarily to eliminate which kind of dependency?

    • aA foreign-key cycle between tables
    • bA transitive dependency through CustomerID
    • cA multi-valued dependency requiring 4NF
    • dA partial dependency on part of a composite key
    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.

    Read the full bite: Normalizing a flat orders table to 3NF

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

    • aMany-to-many relationships are disallowed in relational databases
    • bForeign keys cannot reference primary keys in other tables
    • cA single foreign-key column can hold only one related value per row
    • dIt would violate the ACID durability property
    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

22. Question 22 of 30

    What is the fundamental purpose of relational algebra within a database system?

    • aTo define the visual structure and relationships of database tables.
    • bTo provide a formal, procedural model for query execution and optimization.
    • cTo offer a declarative language for users to specify desired data.
    • dTo enable direct, low-level data manipulation by application developers.
    Show the answer

    Answer: b · To provide a formal, procedural model for query execution and optimization.

    Relational algebra serves as the formal, procedural model that a database uses to translate declarative SQL queries into an optimizable execution plan. Option C describes SQL itself, which is the high-level, declarative language, not relational algebra.

    Read the full bite: Relational Algebra: The Math Behind SQL Queries

23. Question 23 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

24. Question 24 of 30

    What is the primary philosophical goal Codd's 12 Rules aim to achieve for database systems?

    • aEnsuring complete separation between the logical data model and its physical implementation.
    • bMaximizing database query performance and scalability for large datasets.
    • cProviding a practical framework for comparing different database vendors' products.
    • dStandardizing the SQL language across all relational database management systems.
    Show the answer

    Answer: a · Ensuring complete separation between the logical data model and its physical implementation.

    Codd's rules were created to formally define data independence, which is the separation of the logical data representation from its physical implementation. They are explicitly stated as a theoretical benchmark, not a practical tool for vendor comparison.

    Read the full bite: Codd's 12 Rules: The Relational Database Litmus Test

25. Question 25 of 30

    Which scenario best illustrates the primary benefit of a Schema-on-Read approach?

    • aMigrating an existing relational database to a new, more scalable platform.
    • bIngesting raw log files from various microservices for exploratory analysis.
    • cDeveloping an analytics dashboard with sub-second performance requirements.
    • dBuilding a financial transaction system requiring strict data integrity.
    Show the answer

    Answer: b · Ingesting raw log files from various microservices for exploratory analysis.

    Schema-on-Read excels at handling varied, evolving data like raw log files for exploration, as it defers structure definition to query time, optimizing for ingestion speed and flexibility. Options A and B describe use cases where Schema-on-Write is superior due to its emphasis on upfront data consistency and query performance.

    Read the full bite: Schema-on-Read vs. Write: Pay for Structure Now or Later?

26. Question 26 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

27. Question 27 of 30

    An analyst needs to calculate total sales revenue by region for the last five years. Which system is best suited for this type of complex, read-heavy query?

    • aA nightly backup of the transactional database, to avoid impacting production performance while using the same schema.
    • bA data warehouse, because its denormalized schema is optimized for fast analytical queries across large historical datasets.
    • cThe transactional (OLTP) database, because it is designed to handle a high volume of concurrent queries.
    • dThe transactional (OLTP) database, because it contains the most current and granular transaction data.
    Show the answer

    Answer: b · A data warehouse, because its denormalized schema is optimized for fast analytical queries across large historical datasets.

    A data warehouse is correct because its purpose is to support complex analytical queries on historical data. Running this query on a transactional (OLTP) database would be slow and could negatively impact real-time business operations.

    Read the full bite: What is a data warehouse vs. a transactional database?

28. Question 28 of 30

    When a business needs to analyze historical sales trends over multiple years, which database system is typically preferred and why?

    • aA data warehouse, because it provides real-time data updates and immediate operational insights.
    • bA data warehouse, because its architecture is optimized for complex, read-heavy analytical queries on large, historical datasets.
    • cA transactional database, because its normalized structure ensures data integrity for all historical records.
    • dA transactional database, because it efficiently handles a high volume of small, individual data retrievals for reporting.
    Show the answer

    Answer: b · A data warehouse, because its architecture is optimized for complex, read-heavy analytical queries on large, historical datasets.

    Data warehouses are designed for understanding the business through historical data analysis, handling complex queries across large datasets. Transactional databases, while ensuring data integrity, are optimized for real-time operational transactions and high volumes of small, fast operations, not complex historical trend analysis.

    Read the full bite: What is a data warehouse vs. a transactional database?

29. Question 29 of 30

    Why can't a standard foreign key enforce integrity on the commentable_id column in a polymorphic comments table?

    • aThe check constraint overrides the foreign key
    • bA foreign key can reference only one specific table, not a target chosen at runtime
    • cPolymorphic columns are always stored as text and cannot be indexed
    • dForeign keys cannot be placed on non-primary-key columns
    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

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

    • aIt cannot store posts in chronological order
    • bEach new post must be written into every follower's partition
    • cIt enforces strong consistency that slows writes
    • dFeed reads require expensive cross-partition joins
    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