Top 50 DBMS Interview Questions (2026)

A DBMS is software that stores, retrieves, and manages data while handling the hard parts for you: structure, concurrent access, transactions, constraints, and permissions. The database is the data itself; the DBMS is the engine that governs how it's stored and used.

Teams use it instead of flat files because it gives shared multi-user access without corruption, enforces data integrity through constraints, controls who can read or write, and recovers cleanly after a crash. Doing all that by hand in files is error-prone and doesn't scale.

A DBMS is any system that manages data; an RDBMS is a DBMS that organizes data into tables of rows and columns with defined relationships, and enforces rules through keys and constraints. Every RDBMS is a DBMS, but not every DBMS is relational.

In practice, RDBMS means SQL databases like PostgreSQL, MySQL, and Oracle: structured tables, foreign keys, and ACID transactions. A plain DBMS could be a simpler file-based or hierarchical store without the relational model. Most interview questions assume an RDBMS unless they say otherwise.

SQL (Structured Query Language) is the standard language for defining, querying, and modifying data in a relational database. You use it to create and change tables, insert and update rows, control who has access, manage transactions, and ask questions of the data with SELECT. It's declarative: you say what you want, and the engine works out how.

It splits into sub-languages by purpose: DDL defines structure (CREATE, ALTER, DROP), DML changes data (INSERT, UPDATE, DELETE), DQL queries it (SELECT), DCL controls access (GRANT, REVOKE), and TCL manages transactions (COMMIT, ROLLBACK). Knowing which command belongs to which group is a common quick check.

A primary key is the column or set of columns that uniquely identifies each row in a table. No two rows can share the same primary key value, and it can never be NULL, so every row is guaranteed addressable.

A table has at most one primary key, though it can span multiple columns (a composite key). Most databases automatically create a unique index on it, which is why lookups by primary key are fast. If several columns could serve as the key, those are candidate keys, and you pick one as primary.

Both enforce uniqueness, so no duplicate values are allowed. The differences: a table can have only one primary key but many unique keys, and a primary key can't be NULL while a unique key usually allows one NULL (behavior varies slightly by database).

Use the primary key as the row's main identifier, and unique keys for other columns that must stay unique, like an email or a username. A foreign key from another table typically references the primary key, not a unique key.

A foreign key is a column in one table that references the primary key of another table, linking the two. It enforces referential integrity: you can't insert a row whose foreign key value doesn't exist in the referenced table, and you can't delete a referenced row while children still point to it (unless you set a cascade rule).

This is how a relational database keeps relationships honest. An orders table with a customer_id foreign key can't have an order pointing at a customer who was never created, which is exactly the kind of orphaned-data bug foreign keys prevent.

A super key is any set of columns that uniquely identifies a row, including sets with extra unnecessary columns. A candidate key is a minimal super key: it uniquely identifies rows and has no redundant column you could remove and still stay unique.

From the candidate keys you choose one as the primary key; the rest are called alternate keys. So the hierarchy is: super keys contain candidate keys, one candidate key becomes the primary key. For example, both employee_id and email might be candidate keys; you pick one as primary.

ACID is the set of guarantees a transaction gives so data stays correct. Atomicity means all steps happen or none do; a half-done transfer never survives. Consistency means a transaction moves the database from one valid state to another, respecting all constraints.

Isolation means concurrent transactions don't see each other's uncommitted work, so they behave as if run one at a time. Durability means once a transaction commits, its result survives a crash or power loss. Together they're why you trust a database with money.

A transaction is a unit of work made of one or more SQL statements that must succeed or fail together. You start it, run the statements, and then either COMMIT to make every change permanent, or ROLLBACK to undo all of them as if nothing happened.

The classic case is a bank transfer: debit one account and credit another. If the credit fails after the debit, you ROLLBACK so money isn't lost. COMMIT is what makes changes durable and visible to other transactions; before it, the work is provisional.

Normalization is organizing columns and tables to reduce redundant data. Instead of repeating a customer's address in every order row, you store it once in a customers table and reference it. The goal is to remove duplication so the same fact lives in exactly one place.

The payoff is fewer anomalies. When data is duplicated, an update has to change every copy or the data goes inconsistent, a delete can wipe out information you still needed, and inserts get awkward. Normalization removes those update, delete, and insert anomalies by design.

First normal form (1NF) requires each column to hold a single atomic value, no lists or repeating groups in a cell, and each row to be unique. Second normal form (2NF) is 1NF plus every non-key column depending on the whole primary key, not just part of a composite key.

Third normal form (3NF) is 2NF plus no transitive dependencies: non-key columns must depend only on the key, not on another non-key column. Most schemas aim for 3NF as the practical sweet spot between clean structure and query simplicity.

Denormalization deliberately adds redundant data back into a normalized schema to speed up reads. Instead of joining five tables on every request, you might store a precomputed total or copy a frequently-read field so a single query hits one table and returns fast, avoiding the join cost entirely on that hot path.

You do it when read performance matters more than the cost of keeping duplicates in sync, common in reporting or read-heavy systems. The trade-off is real: now an update has to touch every copy, so you accept write complexity and the risk of inconsistency to buy faster reads. Normalize first, denormalize only where measurements justify it.

SQL (relational) databases store structured data in tables with a fixed schema and give strong ACID transactions, so they fit data with clear relationships and correctness needs. NoSQL is a family of stores (document, key-value, wide-column, graph) that relax schema or transactional guarantees to gain flexibility or horizontal scale.

Pick relational when you need joins, complex queries, and transactional integrity, like orders and payments. Reach for NoSQL when the schema changes often, the data is naturally nested, or you need to scale writes across many machines beyond what one relational node handles. It's about fit, not one being better.

A SELECT names the columns you want, FROM names the table, WHERE filters which rows come back, ORDER BY sorts them, and LIMIT caps how many. The database reads the table (or uses an index), keeps rows matching the WHERE condition, then sorts and trims the result.

The order you write it isn't the order it runs: logically FROM and WHERE happen first to pick rows, then grouping and selection, then ORDER BY, then LIMIT. Knowing that logical order explains why you can't reference a SELECT alias in a WHERE clause in most databases.

WHERE filters individual rows before any grouping happens, so it decides which rows even enter the aggregation. HAVING filters groups after GROUP BY has aggregated them, so HAVING is where you put conditions on aggregate results like COUNT or SUM. Put simply, WHERE runs first on raw rows and HAVING runs last on grouped totals.

So to find departments with more than ten employees, you group by department and use HAVING COUNT(*) > 10, because the count only exists after grouping. You can't put that in WHERE. WHERE is per-row and runs first; HAVING is per-group and runs after aggregation.

DISTINCT removes duplicate rows from a result so each combination of the selected columns appears exactly once. It's purely for deduplicating output when you just want the unique values, with no calculation involved. Selecting DISTINCT department from an employees table gives you one row per department, no matter how many people work in each.

GROUP BY also collapses duplicate values, but its purpose is to compute aggregates per group (counts, sums, averages). If you only want unique values with no aggregation, DISTINCT is clearer; if you need a per-group calculation, GROUP BY is the right tool. For a plain list of unique departments, DISTINCT; for a count of employees per department, GROUP BY.

NULL means 'unknown' or 'no value', not zero and not an empty string. Because it's unknown, any comparison with NULL using = or != returns NULL (treated as not true), so you test for it with IS NULL or IS NOT NULL, never = NULL.

NULL also affects aggregates and logic: most aggregate functions like SUM and AVG skip NULLs, COUNT(column) ignores them while COUNT(*) counts every row, and three-valued logic means a WHERE clause only keeps rows where the condition is truly TRUE. Forgetting this is a classic bug where rows silently drop out.

Constraints are rules the database enforces on column values so bad data can't get in. The main ones: NOT NULL (a value is required), UNIQUE (no duplicates), PRIMARY KEY (unique and not null), FOREIGN KEY (must reference an existing row), CHECK (a custom condition must hold), and DEFAULT (a value used when none is given).

The point is that the database, not just application code, guarantees integrity. A CHECK (salary > 0) stops a negative salary no matter which app inserts it, and a UNIQUE on email stops duplicates even if two requests race. Putting rules in the schema is more reliable than trusting every caller to validate.

Here's the breakdown: CREATE is DDL because it defines structure. INSERT is DML because it changes data. GRANT is DCL because it controls access. COMMIT is TCL because it manages transactions. TRUNCATE is DDL, which surprises people who assume it's DML like DELETE, since both seem to remove rows.

TRUNCATE is DDL because it deallocates the table's data pages rather than deleting rows one at a time, so it's a structural operation that usually can't be filtered with WHERE and often can't be rolled back in the same way as DELETE. That difference is exactly why interviewers use it as the tricky one.

INNER JOIN returns only rows that match in both tables. LEFT JOIN returns all rows from the left table plus matches from the right, filling NULLs where there's no match. RIGHT JOIN is the mirror, keeping all right-table rows. FULL OUTER JOIN keeps unmatched rows from both sides.

CROSS JOIN pairs every row of one table with every row of the other (a Cartesian product), and a self join joins a table to itself, useful for hierarchies like employees and their managers. The one interviewers probe most is LEFT JOIN, because it's how you find rows that have no match, like customers with zero orders.

An inner join keeps only rows that have a match on both sides, so any row without a partner simply disappears from the result. An outer join (LEFT, RIGHT, or FULL) keeps the unmatched rows from one or both tables and fills the missing side with NULL, so nothing gets silently dropped from the side you're protecting.

Say you join customers to orders. An inner join lists only customers who placed at least one order. A LEFT JOIN lists every customer, with NULLs for those who never ordered, which is exactly how you'd find customers who ordered nothing by adding WHERE orders.id IS NULL.

An index is a separate data structure, usually a B-tree, that stores the indexed column values in sorted order with pointers back to the rows. Instead of scanning every row to find matches, the database walks the sorted index to jump straight to the rows it needs, turning a linear scan into a logarithmic lookup.

That helps queries that filter, join, or sort on the indexed columns. The cost is on writes: every INSERT, UPDATE, or DELETE has to update the index too, and each index takes disk space. So indexes trade slower writes and more storage for faster reads, which is why you index the columns you query, not every column.

A clustered index determines the physical order of rows in the table, so the table data itself is stored sorted by that index. There can be only one per table because rows can only be sorted one way, and it's usually the primary key. Looking up by it is fast because the row data is right there.

A non-clustered index is a separate structure holding the sorted key values with pointers to the actual rows. You can have many of them. A lookup finds the key in the index, then follows the pointer to fetch the row, one extra hop compared to a clustered index.

Indexes slow down writes: every INSERT, UPDATE, or DELETE must maintain each index on the table, so a table with many indexes pays a write penalty on every change. They also consume disk and memory. On a write-heavy table, too many indexes is a real drag.

An index also doesn't help, or even gets skipped, when a query returns most of the table (a full scan is cheaper), when you filter on a column that isn't indexed, or when a function wraps the column (WHERE UPPER(name) = ...) so the plain index can't be used. Low-cardinality columns like a boolean rarely benefit either.

A composite index covers multiple columns in a defined order, say (last_name, first_name). It's stored sorted by the first column, then the second within that, like a phone book. It can serve queries that filter on the leading column, or the leading plus following columns.

Order matters because of that leftmost-prefix rule: an index on (a, b) helps queries filtering on a, or a and b, but not a query filtering only on b, since the index isn't sorted by b alone. So you put the column you filter on most, or the most selective one, first.

There are four standard levels, trading consistency for concurrency. Read uncommitted allows dirty reads (seeing another transaction's uncommitted changes). Read committed only sees committed data but allows non-repeatable reads. Repeatable read prevents non-repeatable reads but can still allow phantoms. Serializable is the strictest, behaving as if transactions ran one at a time.

Each level prevents more anomalies but reduces concurrency and can increase locking or aborts. Most databases default to read committed. You raise the level when correctness demands it, like preventing double-booking, and accept the performance cost.

A dirty read is reading another transaction's uncommitted change that might later roll back, so you acted on data that never really existed. A non-repeatable read is reading the same row twice in one transaction and getting different values because another transaction committed an update in between.

A phantom read is running the same query twice and getting a different set of rows because another transaction inserted or deleted rows matching your condition. Non-repeatable is about a changed row; phantom is about new or removed rows appearing. Each stricter isolation level knocks out one more of these.

A deadlock happens when two transactions each hold a lock the other needs, so neither can proceed. Transaction A holds row 1 and wants row 2; transaction B holds row 2 and wants row 1. They wait on each other forever.

Databases detect deadlocks (usually with a wait-for graph) and break the cycle by aborting one transaction, the victim, which then rolls back and can retry. To reduce them, acquire locks in a consistent order across your code, keep transactions short, and use appropriate isolation. Your application should be ready to catch the deadlock error and retry.

Pessimistic locking assumes conflicts are likely, so it locks a row the moment you read it and holds that lock until you finish, blocking anyone else who wants it. It's safe under heavy contention because nobody can slip in a conflicting change, but it reduces concurrency and raises the risk of deadlocks when transactions wait on each other.

Optimistic locking assumes conflicts are rare, so it doesn't lock. Instead it checks at write time whether the row changed since you read it (often via a version number or timestamp) and rejects the write if it did, asking you to retry. It scales better when conflicts are uncommon, which is why web apps favor it.

A stored procedure is a named block of SQL (and procedural logic) saved in the database that you call by name. It can take parameters, run multiple statements, use control flow, and return results, all executing inside the database engine.

You'd use one to keep multi-step logic close to the data (fewer round trips), to enforce a consistent operation across different apps, or to grant callers permission to run the procedure without direct table access. The trade-off is that business logic in the database is harder to version, test, and move than logic in application code, so teams weigh that.

A view is a saved query that behaves like a virtual table. It stores no data itself; every time you query the view, the underlying query runs against the base tables. Views simplify complex queries, present a consistent interface, and can restrict which columns a user sees.

A materialized view actually stores the query's result on disk, so reads are fast because there's no recomputation, but the data is a snapshot that goes stale and must be refreshed. Use a plain view for always-current data and simpler queries; use a materialized view when an expensive query is read often and slightly stale results are acceptable.

Aggregate functions (COUNT, SUM, AVG, MIN, MAX) collapse many rows into one summary value. With GROUP BY, they compute one result per group instead of one for the whole table, so GROUP BY department with COUNT(*) gives a headcount per department.

A rule that trips people up: every column in the SELECT that isn't inside an aggregate must appear in GROUP BY, because the database needs to know how to collapse it. And to filter on an aggregate result you use HAVING, not WHERE, since the aggregate only exists after grouping.

A subquery is a query nested inside another, in the WHERE, FROM, or SELECT clause. A correlated subquery references the outer query and runs per outer row; an uncorrelated one runs once. Subqueries read naturally for existence checks (WHERE EXISTS) or filtering against a computed set (WHERE id IN (SELECT ...)).

Joins are usually the better choice when you need columns from both tables in the output, and the planner often optimizes a join better than an equivalent correlated subquery. Reach for a subquery for readability on existence or set-membership tests; reach for a join when you're combining columns from multiple tables. Many are interchangeable, and a good optimizer treats them similarly.

ACID (Atomicity, Consistency, Isolation, Durability) is the strong-consistency model relational databases follow: a transaction fully commits or fully rolls back, and once it commits, the data is immediately consistent everywhere you read it. It prioritizes correctness over raw availability, which is exactly what you want for money, orders, and anything where a wrong answer is unacceptable.

BASE (Basically Available, Soft state, Eventual consistency) is the model many distributed NoSQL systems follow to stay available and scalable: the system is basically available, state can be in flux, and replicas become consistent eventually rather than instantly. It trades immediate consistency for availability and partition tolerance, which connects to the CAP theorem.

The CAP theorem says a distributed data store can guarantee at most two of three properties at once: Consistency (every read sees the latest write), Availability (every request gets a response), and Partition tolerance (the system keeps working despite network splits between nodes).

Since network partitions are a fact of distributed systems, partition tolerance isn't really optional, so the real choice under a partition is between consistency and availability. A CP system refuses some requests to stay consistent; an AP system answers but may serve stale data. The theorem explains why distributed databases pick different trade-offs.

SQL is declarative: you say what data you want, not how to get it. The query optimizer decides how, choosing the access method (index scan vs full scan), the join order, and the join algorithm, using table statistics to estimate which plan is cheapest.

The execution plan is the chosen strategy, and you inspect it with EXPLAIN (or EXPLAIN ANALYZE to also run it and show real timings). Reading a plan tells you whether an index is used, where the row estimates are wrong, and which step is the bottleneck, which is the starting point for tuning a slow query.

A B-tree is a balanced tree kept shallow so lookups touch only a few nodes. The search starts at the root, which holds key ranges and pointers to child nodes, follows the child whose range contains the target key, and repeats down each level until it reaches a leaf node holding the sorted keys.

The leaf stores the key and a pointer to the actual row (or, in a covering index, the needed columns directly, so no table fetch is needed). Because the tree is balanced and wide, even a table with millions of rows is only a few levels deep, so a lookup is a handful of page reads instead of a full scan. That's the whole reason indexes work.

Partitioning splits one large table into smaller pieces within a single database, by range, list, or hash of a column. Queries and management get faster because the engine can prune to the relevant partitions, but it's still one database on one server.

Sharding spreads data across multiple independent databases on different servers, each holding a subset of the rows keyed by a shard key. It's how you scale writes and storage beyond one machine. The cost is real: cross-shard queries and transactions get hard, rebalancing is painful, and choosing a bad shard key creates hot spots. Partition first; shard only when one server genuinely can't hold the load.

Replication keeps copies of the data on multiple servers. The common model is one primary handling writes that streams its changes to read replicas, which serve read traffic to scale reads and provide failover if the primary dies. Some systems support multi-primary, where more than one node accepts writes.

The core trade-off is synchronous versus asynchronous. Synchronous replication waits for a replica to confirm before committing, so no data is lost on failover but writes are slower. Asynchronous commits immediately and ships changes after, so it's fast but a replica can lag and a crash can lose the last changes. Reading from a lagging replica also means stale reads, which your app has to tolerate or route around.

Multi-version concurrency control keeps multiple versions of a row so readers and writers don't block each other. When a transaction updates a row, the database writes a new version rather than overwriting the old one; each transaction sees the version consistent with when it started.

The payoff is that reads never block writes and writes never block reads, which gives high concurrency without heavy locking. It's how PostgreSQL and others implement isolation. The cost is that old row versions accumulate and must be cleaned up (vacuuming in PostgreSQL), and neglecting that cleanup causes table bloat and degraded performance.

The N+1 problem is running one query to fetch a list of N parent rows, then a separate query per parent to fetch its children, so you fire N+1 queries total. It usually comes from an ORM lazily loading a relationship inside a loop, and it quietly destroys performance as N grows.

You fix it by fetching the related data in one or two queries instead: a JOIN to pull parents and children together, or an eager-load/batch that fetches all children with a single WHERE child.parent_id IN (...). The instinct to build is spotting a query count that scales with row count, which is the tell that an N+1 is hiding.

Opening a database connection is expensive: a TCP handshake, authentication, and backend process or thread setup. Doing that per request adds latency and, worse, can exhaust the database's connection limit under load, because each open connection costs the server memory and resources.

A connection pool keeps a set of open connections and hands them out to requests, returning them to the pool when done instead of closing them. This caps concurrency at a safe number, reuses the expensive setup, and protects the database from being overwhelmed by thousands of short-lived connections. Sizing the pool right matters: too small starves the app, too large overloads the database.

Choose NoSQL when the relational model fights your workload. If the schema changes constantly or data is naturally nested, a document store fits. If you need to scale writes across many machines beyond one relational node, a wide-column store handles that. For simple key-based lookups at huge volume, a key-value store is right. For heavily connected data with deep relationship queries, a graph database wins.

Stay relational when you need multi-row ACID transactions, complex ad-hoc queries with joins, and strong consistency, which is most business data. The honest answer resists 'NoSQL is more scalable' as a blanket claim; it names the specific store and the specific workload it fits, and admits you often use both (polyglot persistence) in one system.

Start normalized, then denormalize only where measurements show a join or aggregation is a real bottleneck on a hot read path. The candidates are precomputed aggregates (a stored order total), duplicated fields to avoid a frequent join, or read-model tables built for a specific query.

The decision is a trade you own: you buy read speed by accepting write complexity, since every denormalized copy must be kept in sync, through the application, a trigger, or an async pipeline, and any of those can drift. So you denormalize when reads dominate, the query is proven slow, and you have a reliable way to keep the duplicate consistent. Guessing without profiling is how denormalization creates bugs instead of speed.

First confirm which query and how slow, from the slow-query log or your monitoring, because you fix the query that actually hurts, not a guess. Then run EXPLAIN ANALYZE to see the real plan: look for a sequential scan where an index was expected, a bad join order, or row estimates far off from actual, which points at stale statistics.

From there the usual fixes are adding or correcting an index (including a composite or covering one), rewriting the query to be index-friendly (avoiding a function on the filtered column), updating statistics, or reducing the data touched. If the query is fine but the table is huge, partitioning or a read replica may be the answer. Verify with EXPLAIN ANALYZE again and confirm the improvement under real load.

A single-database ACID transaction doesn't stretch across independent services, so you can't just wrap them in one BEGIN/COMMIT. The common pattern is the saga: break the operation into a sequence of local transactions, each with a compensating action that undoes it, so if a later step fails you run the compensations to roll the whole thing back logically.

You also lean on idempotency (so retries are safe), the outbox pattern (write the event in the same local transaction as the data, then publish reliably) to avoid the dual-write problem, and accept eventual consistency where strong consistency isn't required. Two-phase commit exists but is avoided at scale because it blocks and couples services. The honest answer is that distributed consistency is about designing for eventual convergence and safe retries, not pretending you still have one big transaction.

Databases use shared locks for reads (many readers can hold one at once) and exclusive locks for writes (only one holder, and it blocks readers and other writers). Locks also have granularity: row-level locks allow high concurrency but cost more to track, while table-level locks are cheap but block everyone.

Coarser or longer-held locks reduce concurrency and raise the chance of deadlocks, so you keep transactions short and touch as few rows as possible. Many modern databases use MVCC so that plain reads don't take blocking locks at all, and explicit locking (SELECT FOR UPDATE) is reserved for when you truly need to serialize access to specific rows.

A write-ahead log (WAL) records every change to a durable log before applying it to the actual data files. The rule is log-first: the change is written and flushed to the WAL before the transaction is considered committed, so the record of what happened is safe on disk even if the data pages haven't been written yet.

This is what makes durability and crash recovery work. After a crash, the database replays the WAL to redo committed changes that hadn't reached the data files and to roll back uncommitted ones, restoring a consistent state. The WAL also underpins replication (replicas consume it) and point-in-time recovery. Without it, a crash mid-write could leave the database corrupt.

Use expand-and-contract so the schema stays compatible with both old and new code at every step. Expand: add the new column or table without touching the old, deploy code that writes to both and reads the old, backfill existing rows in batches to avoid a long lock, then switch reads to the new. Contract: once nothing uses the old shape, drop it in a later deploy.

The rules that keep it safe: never make a breaking change in one step while old code runs, avoid operations that hold a long exclusive lock on a big table (some ALTERs rewrite the whole table), backfill in small batches, and keep each step independently deployable and reversible. The point is decoupling the schema change from the code change so neither requires downtime.

Clarify first: do we track inventory, support multiple items per order, and need status history. Then lay out the core entities: customers, products, orders, and an order_items join table, because an order has many products and a product appears in many orders, a many-to-many that needs its own table. order_items holds order_id, product_id, quantity, and the unit price at purchase.

Add keys and constraints: primary keys on each table, foreign keys from orders to customers and from order_items to both orders and products, NOT NULL on required fields, and a CHECK that quantity is positive. Index the foreign keys and any column you filter on often (customer_id on orders, order status). The structure that scores is clarify, identify entities and relationships, resolve the many-to-many, then say which columns you'd key and index and why, each choice tied to a query the system will run.