Prepare for SQL questions about result correctness, joins, transactions and query plans. The selected bank examples use PostgreSQL; syntax, locking and isolation behaviour can differ in other database engines.
Correctness: reason about row cardinality, duplicates, nulls and the business invariant.
Concurrency: explain what a transaction protects and which conflicts still need constraints or locking.
Performance: interpret a plan and estimates instead of assuming an index must always be used.
How to approach your answer
Define the result you need and identify the keys and relationship cardinalities.
Build a small example that exposes duplicates, nulls or competing writes. Explain the correctness mechanism.
For a slow query, inspect its plan and representative data before proposing an index or rewriting it.
A join unexpectedly multiplies the revenue total.
Illustrative approach: I would inspect the grain of each table and check whether one revenue row joins to several detail rows. I would reproduce the issue with a small order containing two details and compare the row count before aggregation. Depending on the requested result, I could aggregate the details to one row per order first or use an existence test when I only need to know whether a match exists. Adding DISTINCT without understanding the relationship can hide the symptom while producing the wrong total.
Mistakes to avoid
Using DISTINCT to hide a faulty join.
Relying on an application check alone to enforce a concurrent uniqueness rule.
Running EXPLAIN ANALYZE on a modifying query without considering that it executes the statement.
Questions and answer guidance
Start with the level closest to your experience. Each question links to its exact practice exercise; the answer is also available here without opening the app.
Foundations
Start with the concepts and explain them using a small example.
Technical · Fresher
1. What does a primary key guarantee in PostgreSQL, and what does it not guarantee about business identity?
Read the answer guide
A primary key makes each row identifiable: the column or columns must be unique and cannot be null, and PostgreSQL builds an index to enforce that. It says nothing about whether two rows describe the same real-world thing. For example, two customer rows with different generated ids may still be the same person entered twice. To protect business rules, add a separate unique constraint on the natural identifier, such as an external id or email, in addition to the key. Use the key for joins and references, and use constraints to express what makes an entity genuinely unique.
What the interviewer is assessing
Whether you know what a primary key enforces in the database and separate technical row identity from business uniqueness.
Common mistakes
They say a primary key prevents duplicate real-world entities such as duplicate customers.
They believe a primary key can contain null values as long as the rest is unique.
Practise a follow-up
How would you prevent duplicate customers with the same external ID?
Why might you choose a generated key instead of a natural key?
Show how you would apply the idea to a constraint, disagreement or failure.
Technical · Mid-level
2. When would you use a composite unique constraint rather than a single-column unique constraint?
Read the answer guide
A composite unique constraint fits when uniqueness belongs to a combination of columns, such as one external_id being unique only inside a single tenant_id. A single-column constraint would wrongly block two tenants from using the same external_id. Remember that nulls are treated as distinct by default, so a nullable column can let duplicates through, and text may need case normalisation first. Concurrent inserts are handled safely because the database itself enforces the rule, so a failed insert becomes a clear conflict the application can report.
What the interviewer is assessing
Whether you can tell when an invariant belongs to a group of columns and trust the database to enforce it.
Common mistakes
Says every uniqueness need is solved by one unique column on each field.
Relies on an application check before insert and ignores concurrent requests racing.
Practise a follow-up
How would you make an email unique ignoring case?
How does a nullable column change what the constraint actually guarantees?
3. What is the difference between WHERE and HAVING in an aggregate query?
Read the answer guide
WHERE filters individual rows before any grouping happens, while HAVING filters the groups after aggregation, so it can use aggregates such as COUNT or SUM. For example, WHERE status = 'paid' limits which orders are counted, and HAVING COUNT(*) > 3 keeps only customers with more than three of them. Put any condition that does not need an aggregate into WHERE, because fewer rows then reach the grouping step and the query is cheaper. Putting row conditions in HAVING works but wastes effort and hides intent.
What the interviewer is assessing
Whether you know the order in which a query filters rows and groups and can place each condition correctly.
Common mistakes
Uses the two interchangeably without knowing one runs before grouping.
Tries to use an aggregate function such as COUNT inside a WHERE clause.
Practise a follow-up
How would you find customers with more than three orders?
Why can you not reference a select alias inside WHERE?
4. When is EXISTS preferable to an IN subquery or JOIN?
Read the answer guide
EXISTS answers whether at least one matching row is present and can stop at the first match, so it does not multiply rows the way a join to a many-side table can. It reads well for semi-join logic such as customers who have any order. IN with a subquery is often planned similarly, so on large tables look at the actual plan instead of trusting folklore. The sharper difference is NOT IN: if the subquery returns a null, the comparison becomes unknown and no rows come back, whereas NOT EXISTS behaves as most people expect. I would therefore default to NOT EXISTS for anti-joins.
What the interviewer is assessing
Whether you choose between EXISTS, IN and JOIN on semantics and null behaviour rather than habit.
Common mistakes
Claims EXISTS is always faster than IN without looking at a plan.
Uses NOT IN against a nullable column and is surprised when nothing returns.
Practise a follow-up
Why can NOT IN return no rows when the subquery contains null?
How would you confirm from the plan that two forms behave the same?
5. What does a window function let you do that GROUP BY alone does not?
Read the answer guide
A window function calculates a value across a set of related rows while still returning every individual row, which GROUP BY cannot do because it collapses each group into one row. You describe the set with PARTITION BY and the order with ORDER BY, then apply functions such as row_number, rank, lag or a running SUM. For example, a running total of sales per customer keeps each sale visible beside the total. The pitfall is an unspecified or non-unique ordering, which gives unstable numbering when rows tie. Decide the partition and ordering deliberately and add a tie-breaker.
What the interviewer is assessing
Whether you understand that windows keep row detail while aggregating and can choose partition and ordering with care.
Common mistakes
Describes it as just another GROUP BY that returns fewer rows.
Leaves the ordering incomplete so row_number gives different results between runs.
Practise a follow-up
How would you select the latest order per customer?
What is the difference between rank and row_number when values tie?
6. What problem does a database transaction solve, and what does isolation affect?
Read the answer guide
A transaction bundles several changes so they either all take effect at commit or none does after a rollback, for example debiting one account and crediting another. Isolation controls what a transaction may see of other transactions running at the same time, trading correctness against concurrency: lower levels allow anomalies such as non-repeatable reads or phantoms, higher levels prevent more of them but block or abort more often. Choose by the anomaly the operation cannot tolerate, adding explicit locking or constraints where needed, and learn your database's default level.
What the interviewer is assessing
Whether you can state what atomic commit protects and how isolation levels trade anomalies against concurrency.
Common mistakes
They say a transaction makes everything safe and ignore what concurrent transactions can observe.
They set the strictest isolation level everywhere, causing needless blocking and deadlocks.
Practise a follow-up
Can you give an example of a non-repeatable read?
How do you handle a deadlock that the database reports to your application?
7. What does MVCC let a reader see while another transaction updates a row?
Read the answer guide
With MVCC, each statement or transaction works from a snapshot, so a reader sees the row version that was committed when its snapshot was taken, and an ordinary update by another transaction does not block it. Writers create new row versions instead of overwriting in place. What exactly a reader sees depends on the isolation level: under read committed each statement takes a fresh snapshot, while repeatable read keeps one for the transaction. The cost is that old versions pile up until vacuum can remove them, so long-running transactions hold cleanup back.
What the interviewer is assessing
Whether you can explain snapshot visibility and its cost without claiming readers and writers never interact.
Common mistakes
Says readers always wait for writers to finish, which is what MVCC avoids.
Ignores that old row versions need vacuum and long transactions delay it.
Practise a follow-up
What can a new statement under READ COMMITTED see?
Why does a long-running read transaction affect table size?
SELECT FOR UPDATE locks the rows it returns until the transaction ends, so other transactions that try to update, delete or lock the same rows must wait. It is used to read a row and then modify it safely, such as checking a balance before debiting. Keep the transaction short, because held locks block others, and lock rows in a consistent order to avoid deadlocks. It only protects rows that exist: if the row is missing, there is nothing to lock, so a unique constraint is needed for that case. Options such as SKIP LOCKED suit job queues.
What the interviewer is assessing
Whether you understand what row locks cover, their cost, and the limits when a row does not exist yet.
Common mistakes
Thinks it locks the whole table or blocks all plain reads.
Holds the lock across slow external calls, stalling other transactions.
9. Why might PostgreSQL use a sequential scan even when an index exists?
Read the answer guide
The planner chooses the cheapest plan by estimated cost, not by whether an index exists. If a query needs a large share of the table, reading it sequentially is cheaper than thousands of random index lookups. Other causes are a very small table, stale statistics that mislead estimates, a predicate that cannot use the index such as a function on the column, or a data type mismatch. Use EXPLAIN ANALYZE to compare estimated and actual rows and see real timings. Disabling sequential scans is only a diagnostic step, never a production setting.
What the interviewer is assessing
Understanding that PostgreSQL chooses plans by cost and estimates, and a method to investigate rather than force them.
Common mistakes
Assuming the planner is wrong whenever it ignores an index and switching off sequential scans.
Ignoring statistics and the fraction of rows a predicate returns.
Practise a follow-up
How would you tell from EXPLAIN ANALYZE that row estimates are wrong?
When would a partial index or an expression index be the right answer?
10. What is the difference between estimated and actual rows in EXPLAIN ANALYZE?
Read the answer guide
Estimated rows are the planner's guess, produced from table statistics, while actual rows are what the executor really found. When they differ by a large factor, the planner may pick a poor join order, join method or scan type because it believed the wrong size. Common causes are stale statistics, correlated columns treated as independent, and skewed values. Comparing them node by node shows where the estimate first went wrong. Running ANALYZE, raising statistics detail on a column, or creating extended statistics are typical responses, followed by checking the plan again.
What the interviewer is assessing
Whether you can read a plan critically and connect estimate gaps to statistics problems and plan quality.
Common mistakes
Looks only at total execution time and ignores row estimate differences.
Assumes a large gap between estimate and actual always means a missing index.
Explain trade-offs, wider consequences and the evidence behind your decision.
Technical · Senior
11. A JOIN multiplies a revenue total unexpectedly. How would you diagnose it?
Read the answer guide
A join that multiplies revenue usually means a one-to-many relationship repeats each parent row, so summing the parent amount counts it several times. I would first check the cardinality of each join key, then compare the row count before and after each join. Fix it by aggregating the child table to the right grain in a subquery or CTE before joining, or use EXISTS when you only need to know a match exists. Reconcile a few known order IDs against the source to confirm the total. Adding DISTINCT can hide the symptom while leaving the model wrong, so I would explain that to the team.
What the interviewer is assessing
Whether you can trace a wrong total to join cardinality and fix the grain rather than patch the symptom.
Common mistakes
Wraps the query in DISTINCT or SUM(DISTINCT) and calls it fixed.
Checks only the final number and never inspects row counts after each join.
Practise a follow-up
Would DISTINCT repair the underlying model error?
How would you add a guard so this double counting is caught automatically?
12. Two transactions both check 'no active reservation' and then insert. How do you enforce the invariant?
Read the answer guide
Both transactions read an empty state and then both insert, because a check followed by an insert is not atomic under concurrency. The reliable fix is to let the database enforce the rule: a partial unique index on the reservation key where the status is active, or an exclusion constraint when the rule involves overlapping time ranges. A lock or a stricter isolation level can also work, but it needs careful testing and retries. The application then catches the constraint violation and turns it into a clear conflict. A constraint survives code changes and other writers, which is the main reason to prefer it.
What the interviewer is assessing
Whether you know application check-then-insert races and can push the invariant into a constraint.
Common mistakes
Adds an application-level existence check and assumes that is safe.
Raises isolation everywhere instead of modelling the rule as a constraint.
Practise a follow-up
How would you return a useful conflict response?
When would an exclusion constraint be better than a unique index?