Database Interview Questions, and What a Strong Answer Includes

Database interviews, whether for a developer, analyst, data engineer or DBA, keep coming back to the same nine topics: joins, keys and normalization, indexes, transactions, isolation levels, execution plans, SQL versus NoSQL, a design problem and a debugging problem. Interviewers care less about definitions than about whether you know the trade-offs and the traps.

For each topic below: the question as it's usually asked, and what a strong answer includes. Where the answer depends on the database, it says which, with a link to the official documentation for PostgreSQL 18 or MySQL 8.4. If you know which database the team uses, learn how it behaves, not just what the SQL standard says.

1. Joins

"What's the difference between an inner join and a left join? Find the customers who have never ordered."

• An inner join returns only rows that match in both tables. A left join returns every row from the left table, with NULLs in the right table's columns where nothing matches. A full outer join keeps the unmatched rows from both sides.
• Customers who never ordered: FROM customers LEFT JOIN orders ON orders.customer_id = customers.id WHERE orders.id IS NULL. Or NOT EXISTS, which states the intent directly.
• The trap: a condition on the right-hand table in the WHERE clause, such as orders.status = 'paid', throws away the NULL rows and quietly turns the left join into an inner join. It belongs in the ON clause.
• The other trap: NOT IN with a subquery that returns a NULL. The result is then null, not true, so no rows come back. NOT EXISTS doesn't have this problem.
• Joining to a one-to-many table multiplies rows, so totals can double-count. Aggregate first, then join.

2. Keys and normalization

"What's the difference between a primary key, a unique key and a foreign key? Normalize this table."

• A primary key identifies each row, so its values must be unique and not null, and a table has at most one. A unique constraint also blocks duplicates, and a table can have several. A foreign key requires each non-null value to match a row in the referenced table; you choose what happens on delete: block it, cascade it or set NULL.
• A view on surrogate keys (a generated id) versus natural ones (an email address): natural keys change, so many designs use a surrogate key plus a unique constraint on the natural one.
• An engine difference: PostgreSQL indexes primary keys and unique constraints automatically, but not the referencing columns of a foreign key, while MySQL requires that index and creates it if it's missing.
• Normalizing with reasons. First normal form: one value per column, so a comma-separated tags column becomes its own table. Second: nothing depends on only part of a composite key, so in order_items keyed on (order_id, product_id), the product name moves to products. Third: nothing depends on another non-key column, so department_name moves out of employees.
• When to denormalize: a read-heavy report or a stored order total, with a plan for keeping the copy correct.

3. Indexes, and when they hurt

"How does an index make a query faster? When wouldn't you add one?"

• A B-tree index, PostgreSQL's default, keeps keys in order, so the database can find matching rows without reading the whole table. It serves equality, ranges, sorting and LIKE patterns anchored at the start, such as 'abc%' but not '%abc' (outside the C locale, PostgreSQL needs a special operator class for those). A hash index serves equality only.
• Storage differs by engine. In MySQL's InnoDB, the primary key is the clustered index that holds the rows, and every secondary index stores the primary key. In PostgreSQL, every index is secondary, stored apart from the table.
• Column order in a composite index matters. MySQL uses an index on (a, b, c) for lookups on a, (a, b) or (a, b, c): the leftmost prefix. PostgreSQL can use any subset of the columns but is most efficient with the leading ones. Both can also use a skip scan for some queries that don't filter on the first column (PostgreSQL since version 18), which is a reason to check the plan rather than recite a rule.
• When indexes hurt: each one adds work to writes, since inserting a row or changing an indexed column means updating the index too, and each takes disk and memory. When they don't help: a filter matching a large share of the table is often faster as a sequential scan, and wrapping the column in a function, such as lower(email), stops a plain index being used unless you index the expression.

4. Transactions and ACID

"What does ACID mean? Walk me through a money transfer."

• Atomicity: the debit and the credit both happen, or neither does. Consistency: constraints, such as a balance that can't go negative, hold before and after. Isolation: concurrent transactions don't see each other's half-finished work, to a degree set by the isolation level. Durability: once committed, the change survives a crash. PostgreSQL does this with a write-ahead log that reaches permanent storage before the data files change.
• The transfer as one transaction: change both balances in the UPDATE itself (balance = balance - 100 on one, balance + 100 on the other) rather than writing back a value calculated in the application, check the rows affected, then commit. Updating the two accounts in a consistent order, such as lowest id first, avoids deadlocks.
• Engine differences that bite: in PostgreSQL, TRUNCATE is transaction-safe and rolls back with the transaction; in MySQL, it causes an implicit commit and can't be rolled back.

5. Isolation levels and anomalies

"What are the isolation levels, and what can go wrong at each?"

• The anomalies: a dirty read sees another transaction's uncommitted data; a non-repeatable read finds a row changed when it reads it again; a phantom read finds a different set of rows when it reruns a query; a serialization anomaly is an outcome no one-at-a-time ordering of the transactions could produce.
• The standard levels: Read Uncommitted allows dirty reads, Read Committed prevents them, Repeatable Read also prevents non-repeatable reads, and Serializable prevents all of them.
• How the engines differ. In PostgreSQL, Read Committed is the default, Read Uncommitted behaves like Read Committed, Repeatable Read also prevents phantoms, and Repeatable Read and Serializable transactions can fail with "could not serialize access" errors that the application must retry. MySQL's InnoDB defaults to Repeatable Read: plain reads use a snapshot taken at the first read, and locking reads, updates and deletes that scan a range use gap or next-key locks to block inserts into it.
• A lost update: two sessions read a stock level of 5 and both write back 4. Fixes: do the arithmetic in the UPDATE, lock the row with SELECT ... FOR UPDATE, check a version column, or use a stricter level and retry.

6. Execution plans and query optimization

"This query is slow. What do you do?" You'll often be shown the plan.

• Start with the plan, not a guess. EXPLAIN shows the plan the optimizer chose, with estimated costs and rows; EXPLAIN ANALYZE also runs the query and reports actual times and rows. Because it really executes the statement, wrap an UPDATE or DELETE in BEGIN and ROLLBACK. MySQL's EXPLAIN ANALYZE runs the statement too.
• Read it properly: find the step where the time goes, and compare estimated rows with actual rows. PostgreSQL's guide says whether the estimates are reasonably close to reality is usually the most important thing to look for; a large gap often means stale statistics. On a step that runs repeatedly, times and rows are per loop.
• Fix the cause: an index that matches the filter and the sort; a rewrite of conditions that can't use an index; fresh statistics, because the planner can choose badly without them, so run ANALYZE after a bulk load; keyset pagination instead of a large OFFSET, whose skipped rows still have to be computed.
• Check the fix on realistic data, and count the write cost of any new index.

7. SQL vs NoSQL

"When would you choose a NoSQL database over a relational one?" A strong answer starts from the data and how it's read:

• Relational suits structured data with relationships, ad hoc queries and joins, constraints, and transactions across many rows.
• Document stores suit data read and written as one unit, such as an order with its lines, and schemas that change often. In MongoDB, an operation on a single document is atomic; multi-document transactions exist, but MongoDB's own documentation says they cost more and that good schema design often removes the need for them.
• Key-value stores suit caches and sessions; wide-column stores, very high write volumes with known queries; graph databases, questions about connections, such as friends of friends.
• The costs: duplicated data the application has to keep consistent, limited joins, and, once data is distributed, the CAP trade-off: during a network partition, a system must choose between answering every request and guaranteeing the latest data. (CAP's "consistency" isn't ACID's.)
• The middle ground: PostgreSQL's jsonb type can be indexed, so semi-structured data doesn't always need a second database.

8. A design question

"Design the database for a meeting-room booking system."

• Ask first: who books, fixed slots or any time, recurring bookings, cancellations, history, time zones, scale.
• Sketch the tables: rooms, users and bookings, with foreign keys from bookings to both, a start and an end time, and a status.
• Put the rules in the database: a check that the end is after the start, and no overlapping bookings for the same room. In PostgreSQL that's an exclusion constraint on a time range, which the documentation demonstrates with a room-reservation example using the btree_gist extension. Elsewhere, lock the room's row in a transaction before checking for overlaps, or use fixed slots with a unique key on room and slot.
• Index for the access patterns: a room's bookings by time, and a user's bookings.
• Plan for change: cancellations as a status rather than a delete, an audit trail, and times stored with their time zone. If prices appear, store them as numeric (DECIMAL in MySQL), never floating point.

9. A debugging question

"Deleting a customer has started taking several seconds. What's going on?"

A strong answer works from evidence (reproduce it, read the plan, check for locks and triggers) and then spots the likely cause. Each delete must check referencing tables, such as orders, for rows that still point at the customer. PostgreSQL doesn't index a foreign key's referencing column automatically, so without one, every check scans the orders table. The fix is an index on orders.customer_id.

A common follow-up: "The logs say 'deadlock detected'." A strong answer knows the database has already aborted one of the transactions to break the cycle (InnoDB rolls one back too), finds the two statements involved, and fixes the cause: take locks in a consistent order, keep transactions short, and retry the one that failed.

How to prepare

• Write queries by hand, without autocomplete: joins, GROUP BY with HAVING, a window function, a NOT EXISTS.
• Run EXPLAIN on your own data. Install PostgreSQL or MySQL locally, load a sample database, and compare plans before and after adding an index.
• Learn your engine's defaults, starting with the ones above.
• Prepare one real story about a slow query or a data problem you fixed, with numbers.

For role-specific questions, see our database administrator interview questions, and for the topics a typical SQL test covers, our SQL assessment page. Cohesyve, which publishes this guide, also runs practice job simulations for database roles, with a scored report on where you lost marks.