SQL & Transactions
An analyst rarely writes production queries but constantly reasons about data: they read a requirement and must see at once whether it is a JOIN or a subquery, whether a report needs WHERE or HAVING, whether a write is safe to repeat. And behind a single query lies concurrency: the moment two users touch the same row, correctness rests on transactions and isolation levels. This topic gives both halves — the query language itself and the guarantees that sit beneath it.
The traps, named up front — the ones candidates stumble on: LEFT JOIN is confused with INNER, an aggregate is filtered in WHERE, x = NULL is treated as an equality test, NOT IN breaks on a NULL in the subquery, SERIALIZABLE is called always right, and 2PC is treated as free atomicity. Each is a classic interview failure. The full map is in the two layers below.
Topic map
- SQL for analysts —
JOINtypes,GROUP BYwith aggregates andHAVING, subqueries versus CTEs, normalization versus denormalization, keys and constraints,NULLsemantics, indexes, and turning a requirement into a correct query. - Transactions & concurrency — ACID, isolation levels and the anomalies each permits, locking (optimistic and pessimistic), deadlocks, MVCC, the cost of a long transaction, and the difficulty of distributed transactions with the saga alternative.
Common traps
| Mistake | Consequence |
|---|---|
Thinking LEFT JOIN and INNER JOIN return the same result | Unmatched rows are silently lost, the report under-reports data |
Filtering an aggregate SUM(x) in WHERE | Invalid: WHERE runs before grouping, the condition belongs in HAVING |
Testing x = NULL instead of x IS NULL | The comparison yields UNKNOWN, the row silently fails the filter |
Believing SERIALIZABLE is always the right choice | Throughput drops and serialization-conflict rollbacks rise |
Expecting READ COMMITTED to prevent a non-repeatable read | A re-run SELECT in one transaction returns different data |
Treating 2PC as free cross-service atomicity | Locks held across the network and a stall if the coordinator dies |
Interview relevance
The topic is asked to check whether you can translate a requirement into a correct query and reason about what happens under simultaneous access. A candidate who explains LEFT JOIN as "keeps every left row, filling NULL where there is no match" and an isolation level as "which anomaly it still permits" immediately gets ahead of one who recalls only a list of keywords.
Typical checks:
- The difference between
INNER/LEFT/RIGHT/FULLand when each is correct;WHEREversusHAVING. NULLsemantics (three-valued logic) and whyCOUNT(col)skipsNULL.- ACID and what each letter means, isolation especially.
- Which anomaly each isolation level permits, and how
SERIALIZABLEdiffers fromREPEATABLE READ. - Optimistic versus pessimistic locking, deadlocks, and the idea of MVCC.
- Why a distributed transaction is hard and when to prefer a saga.
Common wrong answer: "just set SERIALIZABLE and all concurrency problems disappear". In reality the highest isolation level costs throughput and produces serialization-conflict rollbacks; the skilled choice is the lowest level that rules out the anomaly actually dangerous for the specific scenario.