Databases
SQL is a declarative language. A query states what result is wanted and says nothing about how to get it; choosing the method is the planner's job, and it is free to pick a different plan tomorrow once the table grows. Hence the first rule of the topic — think in sets, not in rows. A Python developer instinctively pictures a for loop over rows, and nearly every interview mistake grows out of that instinct: COUNT(*) after a LEFT JOIN counts the NULL-padded row; WHERE cannot see COUNT because it runs before grouping; GROUP BY collapses the rows where a window function was needed; a correlated subquery silently re-runs for every row of the outer query.
The other half of the topic is governed by what the code never shows — transactions. A DB-API driver opens a transaction by itself on the very first statement and keeps it open until an explicit commit(); an ORM does the same. While a transaction is open the database holds a snapshot, locks, and old row versions, and VACUUM cannot reclaim the garbage — which is how a harmless unclosed cursor turns into table bloat. The traps worth naming upfront live here too: SQL has no true nested transactions, PostgreSQL's REPEATABLE READ is stricter than the standard, the cost numbers in EXPLAIN are not milliseconds, and a plain VACUUM does not hand disk space back to the operating system.
Topic map
- JOIN types —
INNER,LEFT,RIGHT,FULL, and how a join multiplies rows. - Aggregation and GROUP BY — grouping collapses rows,
HAVINGfilters already-computed groups, andCOUNT(*)andCOUNT(col)count different things. - Subqueries — an uncorrelated subquery runs once, a correlated one can run per row of the outer query.
- Window functions —
OVER,PARTITION BYand the frame give you an aggregate without losing a single row. - EXPLAIN and the query plan —
Seq ScanversusIndex Scan, estimate versus actual, and whycostis not measured in milliseconds. - Transactions and ACID — what each of the four letters actually guarantees, and what none of them promise.
- Transaction control commands —
COMMIT,ROLLBACK,SAVEPOINT,SET TRANSACTION, and how DDL behaves. - Nested transactions and SAVEPOINT — real nesting does not exist; partial rollback is what
SAVEPOINTprovides. - Isolation levels — a level is defined by the anomalies it permits, and
PostgreSQLdelivers more than the standard promises. - MVCC and VACUUM — row versions, dead tuples, table bloat, and freezing against transaction-ID wraparound.
- Cursors and the DB-API — the cursor as a pointer into a result set, and server-side cursors for huge results.
- PostgreSQL vs MySQL — where the differences are real and where they went stale.
Common mistakes and traps
| Mistake | Consequence |
|---|---|
COUNT(*) after a LEFT JOIN | The NULL-padded row counts as one — an empty group reports 1 instead of 0 |
Filtering an aggregate in WHERE | WHERE runs before grouping, and the query fails with aggregate functions are not allowed in WHERE |
Treating JOIN as gluing columns together | With several matches on the right, the left rows multiply and sums silently double |
Using GROUP BY where a window function was needed | The rows are collapsed, so there is nothing left to show beside the aggregate |
Reading cost in EXPLAIN as milliseconds | These are abstract units where one sequential page read equals 1.0; only EXPLAIN ANALYZE reports real time |
Writing BEGIN inside an active transaction | No nested transaction appears — PostgreSQL answers WARNING: there is already a transaction in progress |
Expecting repeatable reads from READ COMMITTED | Every statement takes a fresh snapshot, so a second SELECT sees another session's COMMIT mid-transaction |
Testing for absence with NOT IN over a subquery | A single NULL in the subquery makes the result empty — use NOT EXISTS or an anti-join |
What interviews check
The topic is the second block of almost every Python interview after the language itself, and the shape of the questioning is stable. First come the quick fundamentals: JOIN types, what a transaction is, what the letters of ACID mean, which commands control a transaction. What is wanted here is not a textbook definition but a mechanism — "atomicity means the engine rolls the applied changes back from its journal after a failure", not "atomicity means atomically". Then come three or four standard SQL exercises — duplicates via GROUP BY … HAVING, customers with no orders via an anti-join, the second-highest salary, the top earner per department — and the interviewer watches whether you reach for a window function on your own or start building a correlated subquery.
After that the questions turn into a comprehension check. Isolation levels are asked through anomalies — what a dirty, a non-repeatable and a phantom read are, and which level forbids which; a strong answer adds that PostgreSQL implements REPEATABLE READ with a snapshot and therefore closes phantoms too, while its READ UNCOMMITTED behaves as READ COMMITTED. EXPLAIN is probed on telling the estimate from the actual and explaining why the planner chose a Seq Scan. VACUUM is probed on understanding MVCC — where dead tuples come from at all. The typical failure across the whole block is the same one — the candidate recites names ("there are four isolation levels") and cannot name a single mechanism underneath, and the next question is always about the mechanism.