Top 30 Easy Databases & Architecture Interview Questions and Answers for Freshers
30 easy multiple-choice Databases & Architecture interview questions, the ones an interviewer opens with: definitions, everyday syntax, and the quick checks that you have really used it. They come from 30 bites in the Databases & Architecture library, the gentlest slice of the 134 Databases & Architecture interview questions in the 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
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 5 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 6 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 7 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 8 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 9 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 10 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 11 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 12 of 30
Why do databases read and write whole pages rather than individual rows directly to disk?
Show the answer
Answer: d · Block-oriented I/O has a high fixed cost per operation, so whole pages amortize it across many rows
Each disk or SSD operation carries a large fixed cost no matter how few bytes are moved, so transferring a whole page spreads that cost over many rows. The other options misstate row sizes and conflate storage with security or ACID.
Question 13 of 30
How does a WAL let a database confirm a commit as durable without first writing the modified data pages to their locations?
Show the answer
Answer: b · It durably appends the change to a sequential log before commit; data pages are flushed later and replayed on crash
The write-ahead rule flushes a cheap sequential log record before acknowledging commit, letting dirty data pages persist later and be replayed after a crash. The other options invent an atomic dual write or abandon durability entirely.
Question 14 of 30
Which stage is responsible for choosing whether to use an index scan or a sequential scan for a SELECT?
Show the answer
Answer: c · The optimizer, when building the physical execution plan from cost estimates
Access-method selection is the optimizer's job, using statistics to estimate which plan is cheapest. The parser only checks syntax, binding resolves names, and the executor runs the already-chosen plan rather than racing alternatives.
Question 15 of 30
A developer runs EXPLAIN on a slow lookup and sees a sequential scan on a large table for an equality filter. What is the most likely takeaway?
Show the answer
Answer: c · A usable index on the filtered column is probably missing
A full scan for a selective equality filter usually signals a missing or unusable index, which adding one would fix. Scans are not always optimal, and the plan reflects a real optimizer decision rather than corruption.
Question 16 of 30
In a hash join between a huge table and a tiny one, which side is used to build the in-memory hash table, and why?
Show the answer
Answer: d · The tiny table, because it fits in memory and is probed once per row of the huge table
Building on the small table keeps the hash table in memory so the large table is streamed once with cheap probes. Building on the huge side risks spilling, and hash joins do not sort inputs the way a sort-merge join does.
Question 17 of 30
A product catalog has items with very different attributes per type, is read mostly by key, and has high write volume. Per the card's example, which storage model fits best and why?
Show the answer
Answer: b · A document model, since each product can be fetched as a self-contained record without needing joins across varying attributes
The card's example says this exact scenario fits a document model because each product fetches as a self-contained document without joins. The tempting wrong answer, that document databases are always faster at scale, is explicitly flagged in the card as a common wrong answer.
Read the full bite: How do you choose between relational and NoSQL databases?
Question 18 of 30
According to the CAP theorem, what is the actual decision a distributed system faces, and when does it apply?
Show the answer
Answer: d · During a network partition you must trade off between consistency and availability
Because partitions are inevitable, the binding choice arises only during a partition: stay consistent and reject requests, or stay available and serve stale data. The pick-two framing and dropping partition tolerance are common misreadings.
Question 19 of 30
Which pairing correctly matches the workload to its typical optimization?
Show the answer
Answer: c · OLTP optimizes for many short read-write transactions on normalized data; OLAP optimizes for large aggregating scans on denormalized data
OLTP favors short concurrent transactions on normalized current data, while OLAP favors heavy aggregating scans on denormalized historical data, often columnar. The other options invert these roles or wrongly equate the two.
Read the full bite: What is the difference between OLTP and OLAP?
Question 20 of 30
In a star schema, which statement correctly describes the fact and dimension tables?
Show the answer
Answer: c · The fact table holds numeric measures and foreign keys; dimensions hold denormalized descriptive attributes
The fact table stores measures plus foreign keys to denormalized dimension tables that carry descriptive context. The other options invert the roles or wrongly describe the schema as fully normalized.
Question 21 of 30
What is the defining architectural difference that makes ELT attractive with modern cloud data warehouses?
Show the answer
Answer: a · ELT loads raw data first and runs transformations using the warehouse's own scalable compute, keeping raw data for reprocessing
ELT loads raw data then transforms in place using the warehouse's elastic compute, preserving raw data for reprocessing. The other options either trivialize the difference, drop extraction, or describe ETL's pre-load transform.
Read the full bite: What is the difference between ETL and ELT?
Question 22 of 30
What are the two primary benefits of database replication, and how does it differ from sharding?
Show the answer
Answer: d · It keeps full copies on multiple nodes, giving high availability via failover and better read scalability, unlike sharding which partitions data
Replication maintains full copies for failover and spread-out reads, whereas sharding partitions data across nodes. It does not chiefly boost write throughput, and it complements rather than replaces backups.
Read the full bite: What is database replication and why use it?
Question 23 of 30
Why might a team shard a database rather than continue vertically scaling a single server?
Show the answer
Answer: b · Vertical scaling hits a hardware ceiling, grows disproportionately costly, and is a single point of failure, while sharding distributes data and write load across nodes
Sharding spreads data and write load across commodity nodes, sidestepping the cost ceiling and single point of failure of one big server. Copying the whole dataset describes replication, and sharding requires a deliberate shard key.
Read the full bite: What is sharding and why shard over vertical scaling?
Question 24 of 30
Restoring from a full backup plus a chain of backups where each one only captured changes since the previous backup describes which strategy?
Show the answer
Answer: b · Incremental backup
Incremental backups each capture changes since the last backup of any type, so restore needs the full plus every incremental in order. A differential captures changes since the last full, so its restore needs only the full plus one differential.
Read the full bite: Full, differential, and incremental backups
Question 25 of 30
What is the strongest reason to give an application service account only SELECT/INSERT/UPDATE on its own tables rather than admin rights?
Show the answer
Answer: d · It limits the blast radius if the account is compromised
Narrow grants mean a compromised account can only do what those grants allow, containing the damage. Privilege scope does not affect query speed or memory, and the SQL standard does not mandate least privilege; it is a security practice.
Read the full bite: Least privilege for database service accounts
Question 26 of 30
What is the core job of a JDBC or ODBC driver?
Show the answer
Answer: a · To translate a standard API into the database's specific wire protocol
A driver translates calls against a uniform API into the particular database's network protocol, keeping app code portable. Caching, security enforcement, and schema generation are handled by other layers like pools, the database, or migration tools.
Question 27 of 30
What is the primary cost a connection pool eliminates in a busy web application?
Show the answer
Answer: c · The repeated setup cost (handshake, auth) of opening a connection per request
Pools reuse already-open connections, removing the per-request handshake and authentication overhead. They do not change SQL execution, disk writes, or result caching, which are separate concerns.
Read the full bite: Connection pools and the problem they solve
Question 28 of 30
What is the strongest argument for choosing managed RDS over self-managing PostgreSQL on EC2 for a small team?
Show the answer
Answer: d · It offloads patching, backups, and automatic failover, freeing the team for product work
Managed services automate undifferentiated operational toil (patching, backups, HA failover), the key value for a small team. RDS is not always cheaper per hour, does not magically speed queries, and self-managed databases can still use encryption.
Question 29 of 30
Your app needs automatic failover during a zone outage AND must offload heavy read traffic. Which RDS configuration meets both needs?
Show the answer
Answer: b · Multi-AZ for failover combined with read replicas for read scaling
Multi-AZ handles automatic failover while read replicas offload reads; they solve different problems. A standard Multi-AZ standby serves no read traffic, so option C is wrong.
Question 30 of 30
After a user updates their settings, they reload and see the old values. What is the least disruptive fix that preserves read scaling?
Show the answer
Answer: a · Route that user's reads to the primary briefly after their write
Pinning the user to the primary for a short window gives read-your-writes consistency while keeping replicas for everyone else. Synchronous replication everywhere defeats the scaling purpose.
Read the full bite: Replica lag and read-your-writes consistency
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.