SQL Interview Questions and Answers for 2026

Answer: SQL is the declarative language for relational databases: you describe the results you want, and the engine decides how to fetch or change them. It covers querying and modifying data, defining schemas, and controlling access. That declarative twist, specifying what rather than how, is the detail interviewers listen for, because everything about planners and optimization later in SQL interview questions flows from it.

Answer: SQL is the language; MySQL and PostgreSQL are database engines that implement it, the software that stores data, executes queries, and manages transactions. The analogy that lands: SQL is to MySQL what HTTP is to nginx. Engines differ in dialect, defaults, and tooling, so strong answers name the engine they actually used, which is precisely the habit SQL interview questions at screening level are fishing for.

Answer: DDL, data definition language, defines structure: CREATE, ALTER, DROP. DML, data manipulation language, works on rows: SELECT, INSERT, UPDATE, DELETE. The consequence worth adding: DDL usually takes schema locks and often cannot run safely on busy production tables without online procedures, which is why the DDL versus DML distinction shows up in operational rounds, not just vocabulary checkers.

Answer: The column, or column set, that uniquely identifies each row: implicitly NOT NULL, limited to one per table, and usually backed by an index, often the clustered one in InnoDB. It gives every row a stable address that foreign keys and replication rely on. It is among the most common opening questions in the whole genre, so name the three properties, unique, non-null, indexed, without hesitation.

Answer: A foreign key is a column constrained to match values in a referenced table’s key, which enforces referential integrity: you cannot point an order at a customer that does not exist, and you cannot delete a customer whose orders would dangle unless the constraint defines an action like CASCADE. It moves integrity into the database where every writer obeys it, which is the sentence interviewers want from SQL interview questions about constraints.

Answer: Both reject duplicates, but a table has one primary key, which forbids NULLs, while a table can carry many unique constraints, which in standard SQL and PostgreSQL permit multiple NULLs because NULL is never equal to NULL. Practically: primary key is identity, unique constraints are alternate business keys like email. That NULL subtlety is a reliable differentiator in written SQL interview questions.

Answer: NOT NULL rejects inserts and updates that leave the column empty, and DEFAULT supplies a value when the insert omits one. They look trivial and are not: every nullable column doubles the branch count of the code that reads it, so deliberate nullability is a design decision, not an accident. Saying that consequence is what turns this small topic into a signal about schema judgment.

Answer: CHAR stores fixed-length values padded to the declared width; VARCHAR stores variable-length strings with a length limit. CHAR suits two-letter country codes and hashes; VARCHAR suits almost everything else. Add the practical note that padding can silently break equality comparisons in some engines, and that overwide VARCHAR limits do not cost storage in PostgreSQL, where the limit is only a constraint, and you have covered what this question usually probes.

Answer: NULL means unknown or absent, not zero and not an empty string, and any comparison with it yields unknown rather than true or false. That is why = NULL matches nothing and the correct tests are IS NULL and IS NOT NULL. The downstream effects, COUNT(column) skipping NULLs and NULL falling last in most ORDER BY rules, are the follow-up SQL interview questions this answer invites, so volunteer one of them.

Answer: COUNT(*) counts rows, including rows where every column is NULL; COUNT(column) counts rows where that specific column is not NULL, so the two numbers differ exactly on nullable columns. COUNT(DISTINCT column) counts distinct non-null values. The trap, reporting active users with COUNT(email) while some rows have no email, is the reason COUNT variants keep appearing in reporting-accuracy rounds.

Answer: DISTINCT collapses duplicate rows in the result, which forces the engine to deduplicate, usually by sorting or hashing the whole result set before returning anything. It is often a smell: duplicates appearing usually means a join is fanning out rows, and DISTINCT papers over the real bug. Naming that habit is exactly what SQL interview questions on DISTINCT are listening for, because senior engineers fix the join instead of hiding its symptoms.

Answer: WHERE filters rows before grouping, so it cannot see aggregates; HAVING filters groups after aggregation, so it can. WHERE status = 'paid' GROUP BY customer HAVING SUM(amount) > 1000 keeps only paid rows, then only customers whose paid total clears 1,000. Filtering early in WHERE is also cheaper, and saying why, fewer rows reach the aggregate, is the half of this entry in SQL interview questions that earns the second sentence.

Answer: GROUP BY collapses rows that share the grouped values into one row per group, so aggregate functions can summarize each bucket: count, sum, average, min, max. Every selected column must be either grouped or aggregated, a rule engines like MySQL now enforce by default through ONLY_FULL_GROUP_BY. The mental model, buckets plus one aggregate per bucket, carries you through most grouping follow-ups without improvisation.

Answer: ORDER BY sorts the full result set, and LIMIT cuts it to the first N rows after that sort. The important consequence: without an index that already provides the order, the engine sorts everything and only then discards, so page 500 of OFFSET pagination gets slower with every page because it still builds and throws away the first 499 pages. Keyset pagination, WHERE created_at < last_seen, is the fix interviewers hope to hear about in SQL interview questions on pagination.

Answer: DELETE removes rows one by one, fires triggers, can take a WHERE, and is fully logged. TRUNCATE removes every row fast by deallocating the table’s storage, usually cannot be filtered, and resets identity counters. DROP removes the table itself, schema and all. Choosing among them by intent, some rows, all rows, or the table’s existence, is the whole answer, and it is one of those SQL interview questions where the speed explanation matters as much as the definitions.

Answer: Only the row pairs where the join condition matches on both sides: FROM orders o INNER JOIN customers c ON o.customer_id = c.id yields orders that have a matching customer and customers that have at least one order, nothing else. Non-matching rows vanish silently, which is the failure mode worth stating, because missing-data bugs caused by unnoticed INNER JOIN semantics are a staple of join rounds at every level.

Answer: Every row from the left table, plus columns from the right where a match exists, filled with NULLs where it does not. Reach for it when the left side must survive regardless: all customers, including those with zero orders, which is how you count non-buyers by adding WHERE o.id IS NULL. That pattern, anti-join by LEFT JOIN plus NULL test, is one of the most rehearsed answers in SQL interview questions, so say it precisely.

Answer: UNION concatenates result sets and then deduplicates them, which costs a sort or hash over everything; UNION ALL just concatenates and keeps duplicates. If duplicates are impossible by construction or acceptable by design, UNION ALL is strictly cheaper. Interviewers like this pair because the performance reasoning, not the syntax, is the graded half in SQL interview questions about combining result sets.

Answer: A query nested inside another query, usable in the FROM clause as a derived table, in expressions like WHERE price = (SELECT MAX(price) FROM products), or with IN and EXISTS for membership tests. Uncorrelated subqueries run once; the correlated kind, next question, runs per outer row. Being able to classify a subquery by where it sits and how often it runs is the vocabulary that later performance questions assume you already have.

Answer: A subquery that references columns from the outer query, so it conceptually re-runs once per outer row: WHERE price > (SELECT AVG(price) FROM products p2 WHERE p2.category = p1.category). That per-row execution is the performance story: it can degrade into a loop unless the optimizer rewrites it into a join or the inner query has a good index. Spotting one and stating its cost model is what SQL interview questions on subqueries are really grading.

Answer: A view is a saved query with a name that behaves like a table: it stores no rows by default, just the definition, executed fresh at each use. Views centralize complicated or sensitive logic, give reporting a stable interface while tables evolve, and with column grants can expose some columns while hiding others. Mentioning materialized views, which do store results and must be refreshed, is the usual bonus point in SQL interview questions about views.

Answer: A sorted lookup structure, typically a B-tree, that lets the engine jump close to the rows a predicate matches instead of reading the table end to end, the way a book index skips you to a page. The trade is real: every index speeds some reads and taxes every write plus storage. One sentence of mechanics plus the trade-off is the complete beginner answer, and it sets up the deeper SQL interview questions that come next.

Answer: A key made of two or more columns whose combination must be unique, classic in join tables like (student_id, course_id) for enrollments. It can back a primary key or a unique constraint, and it pairs naturally with the leftmost prefix rule once an index is built on it. That link between key design and index design is why composite keys open schema-reasoning rounds rather than syntax checks.

Answer: A numeric column whose value the engine generates on insert: AUTO_INCREMENT in MySQL, identity or serial in PostgreSQL. Caveats worth naming: gaps are normal because rolled-back inserts still consume values, generated IDs leak business volume through the URL, and sequences live outside transactional rollback. Those three observations are the substance behind SQL interview questions about ID generation, and they signal you have inserted real data at scale.

Answer: AS names a column or table for the duration of the query: SELECT SUM(amount) AS revenue, FROM orders AS o. The rule people trip on: aliases declared in the SELECT list are not visible to WHERE, because WHERE runs conceptually before the select list is computed, but they are visible to ORDER BY. Knowing which clauses can see which aliases is a small precision check that surfaces often in written SQL interview questions.

Answer: CASE WHEN is SQL’s inline conditional: it returns a value per row based on ordered conditions, with an ELSE fallback. It shines in conditional aggregation, SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) builds per-status columns in one pass, and in translating codes to labels near the data. That pivot-table-in-one-query pattern is the working answer to most practical CASE prompts.

Answer: Single quotes wrap string literals; double quotes, or backticks in MySQL, wrap identifiers like table and column names, which is how you tolerate reserved words or mixed-case names. Confusing them is the classic error, and the security angle matters too: values belong in parameters, never concatenated into the statement. The literal-versus-identifier split is one of those small SQL interview questions that reliably separates people who type SQL from people who read it.

Answer: Injection is when untrusted input becomes part of the statement text, so the database executes attacker-authored SQL. The fix is parameterized queries: send SQL and values separately so the driver can never reinterpret a value as code, and ORM query builders help only if you never concatenate raw strings. Because one gap undoes every other control, injection remains the most security-weighted of the beginner SQL interview questions, so answer it without hesitation.

Answer: A group of statements that succeeds or fails as one unit: money leaves one account and lands in the other, or nothing happens. BEGIN starts it, COMMIT makes it permanent, ROLLBACK undoes it. The sentence interviewers want in SQL interview questions here is why transactions exist: mid-failure states like a debit without its credit would otherwise be visible to other sessions, and that visibility is what isolation and atomicity jointly prevent.

Answer: COMMIT writes the transaction’s changes permanently, with durability guaranteed by the write-ahead log reaching disk; ROLLBACK discards them using the undo information the engine has kept since the first statement. After COMMIT, undoing requires a new compensating transaction, which is why production systems require confirmation before large commits. That permanence distinction is the operational half of this entry in SQL interview questions, and it matters in every incident story you will tell.

Answer: A join of a table to itself using two aliases, so the engine treats it as two copies: FROM employees e JOIN employees m ON e.manager_id = m.id pairs each employee with their manager. It is the standard tool for hierarchical or pairwise questions within one table, org charts, referrals, upstream dependencies. Recognizing when a problem needs aliases is the pattern-recognition half of SQL interview questions about self joins.

Answer: Structuring tables so each fact lives in exactly one place: entities get their own tables, repeating groups become rows, and computed or duplicated values are removed so updates happen once and cannot drift out of sync. The cost is more joins on read. Delivering both halves, integrity won and read complexity paid, is what interviewers expect from SQL interview questions on normalization, because the trade is the point, not the textbook definitions.

Answer: A natural key comes from the data itself, like a national ID or an ISBN; a surrogate is an engine-generated value with no meaning outside the table. Surrogates win in practice because real-world natural keys change, get reused, or arrive with typos, and updating a key cascades into every referencing table. Explaining why the industry defaults to surrogates, while keeping a unique constraint on the natural key, is the complete answer to this SQL interview questions staple.

Answer: LIKE matches patterns: percent matches any run of characters, underscore matches exactly one. The trap is the leading wildcard: LIKE '%son' cannot use a B-tree index because the tree is sorted from the left, so the engine scans. Trailing patterns index fine, and leading-wildcard search is a signal to reach for full-text or trigram indexes. Knowing why the index refuses is the layer SQL interview questions on LIKE are testing.

Answer: IN tests membership against a list or subquery; EXISTS tests whether any matching row exists and stops at the first one. With small literal lists, IN is perfect. Against large subqueries, EXISTS often wins because it can short-circuit and pairs well with indexed probes, while a giant IN list gets materialized and scanned. Modern planners often rewrite one into the other, and saying that openly is the mature way to answer this SQL interview questions pairing.

Answer: Query the catalog: information_schema.tables and information_schema.columns work across engines, while PostgreSQL also offers \dt and \d table in psql and MySQL offers SHOW TABLES and SHOW CREATE TABLE. Reading the schema from SQL itself is the portable skill, because scripts and migrations cannot rely on interactive clients. That catalog fluency is quietly checked in hands-on SQL interview questions, since it is how you explore an unfamiliar schema under pressure.

Answer: A database is the top-level container with its own users and storage; a schema is a namespace inside it that groups tables, views, and functions. PostgreSQL is schema-rich, one database with many schemas per project; MySQL effectively treats the two words as synonyms. The concrete use case, staging namespaces or multi-tenant separation inside one instance, is what elevates answers to SQL interview questions on this topic above the definition.

Answer: An upsert inserts a row or, if the key already exists, updates it in one atomic statement: INSERT ... ON CONFLICT (email) DO UPDATE in PostgreSQL, INSERT ... ON DUPLICATE KEY UPDATE in MySQL. The atomicity is the point: check-then-insert from application code races under concurrency and loses, while upsert makes the database the referee. Race-safe writes are a recurring theme in modern SQL interview questions, and upsert is the canonical example.

Answer: The parser checks syntax and resolves names against the catalog; the rewriter applies rules and expands views; the planner enumerates candidate plans, scanning tables directly or through indexes, choosing join orders and algorithms, and prices each using statistics and its cost model; the executor then runs the winning plan, pulling rows through its operator tree. Most of that time budget goes to planning only for complex queries, and knowing the stages is what lets performance discussions get specific instead of hand-wavy.

Answer: The tree keeps keys sorted in its pages: a root page, intermediate levels, and leaf pages that hold index entries pointing at rows. A seek descends from the root, binary-searching each page and following one child pointer per level, so lookups cost a handful of page reads even on billions of rows, and the leaf level’s sibling links make range scans sequential. That page-and-pointer model, not the cartoon triangle, is what SQL interview questions on B-trees want you to narrate.

Answer: Selectivity is the fraction of rows a predicate is expected to keep, estimated from statistics like distinct counts and histograms. It drives everything: a highly selective predicate makes an index seek obviously cheaper than a scan, while a predicate matching 40 percent of a table usually wins by scanning sequentially because random index lookups cost more per row than a sequential read. Reasoning about percentages rather than absolutes is exactly the framing this topic deserves at intermediate level.

Answer: A composite index is sorted by its first column, then the second within equal firsts, so it serves predicates on the leading columns contiguously: an index on (customer_id, status, created_at) supports customer_id alone, customer_id plus status, and all three, but not status alone or a date range on created_at alone. Equality columns should lead and range columns come last, because a range on an earlier column ends the usable prefix. Designing column order deliberately is the skill behind most SQL interview questions on composite indexes.

Answer: A covering index contains every column the query references, so the engine answers from the index alone and never touches the table, shown as Using index in MySQL’s Extra or an Index Only Scan in PostgreSQL. It saves one random table access per matching row, which on a seek returning thousands of rows is the dominant cost. The trade, wider indexes and slower writes, is the caveat that rounds out any covering-index answer.

Answer: A clustered index stores the table’s rows in the leaves of the primary key tree, InnoDB’s model, so the table is the index; secondary indexes store the key plus the primary key, so lookups through them pay one extra hop into the clustered tree. Heap tables, PostgreSQL’s model, keep rows unordered with all indexes pointing at physical row locations, which avoids that hop but makes fills and vacuum more visible. Choosing a clustered key that grows monotonically is the practical advice intermediate SQL interview questions on this topic are fishing for.

Answer: The index is sorted by the raw column values, so WHERE LOWER(email) = ? or WHERE created_at + 30 > now() asks the engine to sort by something it never stored, forcing a scan with per-row evaluation. Fix the predicate by shifting the computation to the constant side, create an expression index on exactly the function used, or, in MySQL 8 and PostgreSQL, index the generated form. Recognizing the pattern in a query handed to you is why SQL interview questions love this trap.

Answer: A table or sequential scan reads every row and is optimal when you need most of them. An index scan walks the whole index, cheaper only because it touches fewer, narrower pages. An index seek descends the tree to a starting point and reads a contiguous range, the cheapest access when selectivity is high. Reading EXPLAIN’s access type and asking whether the count justifies the method is the working skill that plan-reading rounds keep returning to.

Answer: The plan tree: each operator, its access method, estimated rows and cost, the join algorithm and order, and extras like sort method or whether the index covered the query. Read the estimated row counts first, because every join order decision downstream of a bad estimate inherits it; then the access type per table; then anything involving disk spills. In PostgreSQL add ANALYZE to see actuals, in MySQL use EXPLAIN ANALYZE since 8.0.18. That reading order is precisely what a scenario round expects you to narrate.

Answer: Nested loop probes the inner side once per outer row, unbeatable when the outer side is small and the inner probe is an indexed seek; it is catastrophic when both sides are large. Hash join builds an in-memory hash table on the smaller side and probes it with the larger, winning for big equi-joins without useful indexes. Merge join consumes both sides in sorted order, efficient for large sorted inputs and the only natural choice for range conditions. Explaining the cost model behind each is the full answer to this SQL interview questions favorite.

Answer: LATERAL, CROSS APPLY in SQL Server, lets a subquery in the FROM clause reference columns from tables listed before it, so you can run a per-row correlated lookup that still reads like a join: the top three orders per customer, where the inner query sees each customer row. Before lateral joins this required correlated subqueries in SELECT or procedural loops. Naming one concrete top-N-per-group use is what SQL interview questions on LATERAL are after, since the syntax is useless without the use case.

Answer: GROUP BY collapses each group to one row, destroying the rows that built it; window functions compute an aggregate across a partition and attach it to every row, keeping all rows visible. AVG(amount) OVER (PARTITION BY region) puts the regional average beside every sale in that region, enabling comparisons without losing detail. They also run later in the pipeline, after grouping and HAVING, before ORDER BY. That keep-the-rows property is why interviewers open window-function rounds with exactly this comparison.

Answer: All three order rows within a partition, and they differ on ties. ROW_NUMBER assigns unique sequential numbers regardless of ties, which makes it the tool for picking exactly one row per group. RANK gives tied rows the same number and skips the next numbers, so two second places are followed by fourth. DENSE_RANK also ties but never skips, following two seconds with third. Choosing ROW_NUMBER for dedupe and top-N tasks, and explaining the tie behavior, is the complete answer intermediate SQL interview questions expect here.

Answer: LAG pulls a column’s value from a previous row in the partition and LEAD from the next, with an optional default: LAG(total) OVER (ORDER BY month) puts last month beside this one, so growth is an expression, current minus LAG, with no self join. Sessionization, gap detection, and churn analysis all reduce to this pattern. Being able to reach for LAG instead of a contorted self join is one of the clearest level markers in modern SQL interview questions.

Answer: The frame clause controls which rows in the partition each calculation sees: SUM(amount) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) is a running total, and ROWS BETWEEN 6 PRECEDING AND CURRENT ROW is a seven-day moving window. With ORDER BY present the default frame is range-based up to the current row, which includes peer rows with equal order values, a subtlety that silently changes totals. Stating the default-frame nuance is the depth signal in SQL interview questions on window frames.

Answer: A common table expression is a named subquery declared with WITH, usable like a temporary view for the duration of one statement, and referable multiple times; multiple CTEs chain into readable pipelines. Execution differs by engine: PostgreSQL inlines single-use CTEs into the outer query since version 12, unless you write MATERIALIZED, while MySQL typically materializes them once per statement. Those execution semantics, inlined versus materialized, decide whether the CTE is a label or a real performance boundary, which is exactly what SQL interview questions on CTEs are probing.

Answer: WITH RECURSIVE runs an anchor query, then repeatedly executes a recursive term that references the CTE itself, feeding each round’s output back in until it returns nothing: an anchor picking an org chart’s root, the recursive term finding each node’s reports. It handles traversal of adjacency lists, bill-of-materials explosions, and graph walks that fixed joins cannot express. Adding a depth counter and a cycle guard is the production caveat that completes any recursion answer.

Answer: Atomicity: a transaction’s changes all apply or none do, delivered by undo records during rollback. Consistency: constraints hold at every commit boundary, as the outcome of the other three plus declarative constraints. Isolation: concurrent transactions do not observe each other’s partial work, delivered by locks or MVCC snapshots. Durability: once COMMIT returns, the change survives a crash seconds later, delivered by the write-ahead log being flushed before acknowledgment. Mapping each letter to the subsystem that enforces it is what separates rehearsed candidates in SQL interview questions on ACID.

Answer: Multiversion concurrency control keeps multiple versions of each row so readers get a consistent snapshot without acquiring read locks: writers create new versions, snapshots hide uncommitted or newer ones, and old versions are reclaimed by vacuum in PostgreSQL or by undo history in InnoDB. The consequence is that reporting queries no longer block OLTP writes, at the cost of storage bloat and the occasional snapshot-too-old error. Explaining both the win and the garbage-collection bill is what SQL interview questions on MVCC are listening for.

Answer: A shared lock, taken by readers in lock-based engines, is compatible with other shared locks but not with exclusive ones; an exclusive lock, taken by writers, is compatible with nothing, so one writer excludes all other readers and writers on that row. Deadlocks and contention live entirely in the compatibility matrix, and MVCC engines rely on it mainly for writes while reads ride snapshots. Reciting the compatibility rule and its deadlock consequence is the standard pass on this SQL interview questions classic.

Answer: InnoDB under REPEATABLE READ locks not just matching rows but the gaps between them, next-key locks being the row lock plus the gap before it, so a range predicate like WHERE id BETWEEN 10 AND 20 FOR UPDATE blocks inserts of id 15 until the transaction ends. That is how a locking read prevents phantoms from appearing mid-transaction. The cost is real: gap locks collide on pure inserts, which is a leading source of deadlocks on hot tables, and naming that trade is the mature way to frame the feature.

Answer: Pessimistic control locks target rows up front with SELECT FOR UPDATE, guaranteeing exclusive access at the cost of blocking others. Optimistic control reads freely and validates at write time, either by comparing a version column in an UPDATE … WHERE version = seen or by relying on the engine’s conflict detection, retrying on failure. Optimistic wins under low contention because nothing blocks; pessimistic wins under hot rows where retries would thrash. Framing the choice by contention level is the judgment SQL interview questions on this topic grade.

Answer: READ UNCOMMITTED allows reading uncommitted changes, so dirty reads are possible. READ COMMITTED adds the guarantee that you only read committed data, but re-reading a row can give a different value. REPEATABLE READ pins reads within your transaction to one snapshot, eliminating non-repeatable reads, while phantoms, new rows matching your predicate, remain possible in the strict ANSI reading. SERIALIZABLE makes concurrent execution equivalent to some serial order. Tying each step to a concrete anomaly, not just naming the ladder, is what intermediate SQL interview questions require on isolation.

Answer: Write skew happens when two transactions read an overlapping set, each makes a decision based on what they saw, and each writes to disjoint rows, so both commit and the invariant dies: two on-call doctors each see the other is on call and go off duty. Snapshot isolation permits it because neither write conflicts with a read it never made. Only serializable execution, PostgreSQL’s serializable snapshot isolation or InnoDB’s locking under SERIALIZABLE, detects and aborts it. That story is the canonical senior answer in SQL interview questions about anomaly hunting.

Answer: The WAL, InnoDB’s redo log or PostgreSQL’s WAL, records every change as a small sequential log entry that is flushed to disk before the transaction may commit; the messier random-page updates to data files happen later. Sequential appends are dramatically cheaper than random writes, so a commit costs one log flush rather than dozens of scattered page writes, and crash recovery replays the log forward. That order-of-operations argument, log first, data later, is the mechanism SQL interview questions on durability want named.

Answer: Redo replays committed changes forward from the log so transactions that acknowledged COMMIT are not lost despite dirty pages never reaching disk. Undo reverses uncommitted work, restoring rows to their pre-transaction state, and its history also serves the MVCC snapshots readers need while recovery runs. Together they deliver the atomicity and durability promises with one mechanism. Explaining the two directions, forward for durability, backward for atomicity, is the crisp form of the whole answer.

Answer: A savepoint is a named marker inside a transaction, and ROLLBACK TO SAVEPOINT undoes work after the marker while keeping the transaction and earlier work alive: insert a thousand rows, and when batch 7 violates a constraint, roll back only batch 7 and continue. They are the tool for partial failure handling inside long transactions, best-effort imports above all. The caveat to volunteer in SQL interview questions: savepoints never substitute for keeping transactions short, because the locks still accumulate.

Answer: First normal form removes repeating groups and non-atomic values, one address list per cell becomes a child table. Second normal form removes partial dependencies, where a non-key column depends on only part of a composite key, so order line items stop storing product names keyed by product id alone. Third normal form removes transitive dependencies, where a non-key column depends on another non-key column, zip code implying city. Reciting the anomaly each form kills, with one example, is the whole of what SQL interview questions on normalization tiers demand.

Answer: Denormalize when reads demand data in a shape normalization forbids: dashboards aggregating across many joins, per-user counters that would otherwise sum millions of rows, or cross-service reporting. Do it deliberately: keep the normalized tables authoritative, add the derived copy, and enforce sync with triggers, application logic, or scheduled jobs, accepting that the copy can drift and must be rebuildable from source. That governance framing, source of truth plus rebuild path, is what senior SQL interview questions expect from a denormalization answer.

Answer: The foreign key action deletes dependent rows automatically when the parent goes: delete a customer, and orders, addresses, and sessions vanish. Risks worth naming: one accidental parent delete becomes a mass delete across tables, deep cascade chains make cleanup slow and lock-heavy, and the data loss is easy to underestimate during code review. Many teams prefer explicit application-level deletes with soft-delete flags. Stating both the convenience and the blast radius is what SQL interview questions on cascades are scoring.

Answer: Engines cache execution plans for prepared or repeated statements to skip planning cost, and the plan is optimized for the first parameter values the optimizer saw, which is parameter sniffing. It backfires when parameter distributions are skewed: the plan tuned for a rare customer id, an index seek, is catastrophically wrong for a predicate matching half the table. Remedies include plan recompilation, OPTIMIZE FOR UNKNOWN in SQL Server, generic plan checks in PostgreSQL, and rewriting so the plan is insensitive. Real-world war stories from this mechanism are a staple of senior SQL interview questions.

Answer: The planner predicts how many rows each predicate and join will emit, using statistics: distinct counts, histograms, correlation. Every downstream choice, join order, join algorithm, access path, inherits those numbers, so a stale estimate after a data migration or a bulk load can flip a seek into a scan or pick the wrong hash side. The fixes are routine ANALYZE and auto-analyze tuning, larger statistics targets on skewed columns, and extended statistics for correlated columns. Connecting estimates to outcomes is the analytic core of SQL interview questions on plan quality.

Answer: A partial index, PostgreSQL’s WHERE clause or SQL Server’s filtered index, indexes only rows matching a predicate: CREATE INDEX ON orders (created_at) WHERE status = 'pending'. The pending backlog is a sliver of the table, so the index is tiny, hot in cache, and cheap to maintain, while queries carrying the same predicate use it. It shines on status-driven queues and soft-delete schemes. Naming the maintenance win, not just the query win, is what rounds out SQL interview questions on partial indexes.

Answer: An expression or functional index stores the result of an expression as its key: CREATE INDEX ON users (LOWER(email)), with MySQL 8 supporting functional key parts like ((LOWER(email))). The optimizer uses it only when the query’s predicate contains the matching expression, so WHERE LOWER(email) = ? seeks it while WHERE email = ? does not. The matching-expression requirement trips people constantly, which is exactly why interviewers reach for it.

Answer: A B-tree orders entries from the left, so a pattern without a fixed prefix cannot bound the search and must scan. The alternatives do real work: PostgreSQL’s pg_trgm extension indexes trigrams in a GIN index and serves arbitrary wildcard LIKE, full-text search with tsvector and GIN handles word-based relevance, MySQL’s FULLTEXT index serves MATCH AGAINST, and beyond the database, dedicated search engines take over. Choosing by use case, autocomplete versus relevance search, is the judgment this entry in SQL interview questions is really testing.

Answer: A deadlock is a cycle in the wait-for graph: transaction 1 holds a lock transaction 2 needs while waiting on one transaction 2 holds, so neither can proceed. The engine’s detector finds the cycle, InnoDB checks continuously, PostgreSQL aborts on detection, and rolls back the transaction whose undo is cheapest, returning a deadlock error, MySQL 1213 or SQLSTATE 40P01, rather than waiting forever. Applications must retry, because a deadlock is a design signal, not a random failure. That retry-and-reframe point is what SQL interview questions on deadlocks exist to hear.

Answer: In MySQL 8, query performance_schema.data_lock_waits or the sys.innodb_lock_waits view for the waiter, the holder, and both statements; SHOW ENGINE INNODB STATUS adds the recent history and the last deadlock graph. In PostgreSQL, join pg_locks with pg_stat_activity and call pg_blocking_pids(pid) to name the blockers directly, or read pg_blocking_pids output per stuck pid. Both engines let you see waiter, holder, and SQL text in one screen. Being able to produce that query from memory is the practical bar intermediate SQL interview questions set for locking topics.

Answer: Opening a database connection costs a TCP handshake, authentication, and, in PostgreSQL, a fresh backend process, milliseconds that dwarf a fast query. A pool keeps connections open and reuses them, bounding concurrency to a number the database can actually serve. The operational truths: every application instance needs a cap, sum them across instances and keep the total below max_connections, and poolers like PgBouncer or ProxySQL exist for fleets. Connection math under load is a recurring scenario theme in SQL interview questions, and pooling is its first answer.

Answer: A derived table or CTE keeps everything in one statement and reads cleanly; prefer it when the intermediate result is used once and is cheap to recompute. A temp table persists for the session, can be indexed and analyzed, and passes between statements or procedures, which is the right call for multi-step pipelines over genuinely expensive intermediates. The deciding questions are reuse and cost: used once, stay inline; reused or huge, materialize. That decision rule, not syntax recall, is what SQL interview questions on this trio grade.

Answer: A cursor processes rows one at a time from the application or procedure, paying per-row round trips or context switches and denying the optimizer the chance to treat the whole set at once. The same logic in one UPDATE with a JOIN lets the engine batch, hash, and pipeline, typically orders of magnitude faster. Cursors remain legitimate for rare per-row side effects like calling a procedure per row. Saying when a cursor is actually acceptable is the nuance that lifts answers to SQL interview questions about set-based thinking.

Answer: Order of operations: capture the query and run EXPLAIN ANALYZE, since a plan that was right at a million rows can be wrong at ten. Read the access type and the estimated-versus-actual row counts first: a seek that became a scan, or estimates off by orders of magnitude, tells me whether I am looking at a statistics problem, an index that no longer covers the pattern, or a scan that was always borderline and got worse linearly. Only then act: refresh statistics, add or extend the composite index the plan is missing, or rewrite the query to filter before it aggregates. Narrating exactly that sequence is the entire grade here.

Answer: The plans differ because the inputs differ, and I check four things in order. Data volume and distribution: production tables are larger and skewed, and a predicate that is selective on staging data can match half of production, where a scan is genuinely cheaper. Statistics: production estimates may be stale after heavy writes, so run ANALYZE and compare. Schema drift: the index may exist in staging and never have been created in production, which a diff of SHOW INDEX or pg_indexes settles in seconds. Server settings and versions: work_mem, cost parameters, and planner versions all shift decisions. Most real cases end at the first two, and enumerating the whole ladder is what distinguishes a strong answer.

Answer: First capture evidence: enable innodb_print_all_deadlocks in MySQL or rely on PostgreSQL’s logging, then read the deadlock graph, LATEST DETECTED DEADLOCK in SHOW ENGINE INNODB STATUS, which names both statements and the exact locks. Almost always the cause is inconsistent access order: job A updates order 1 then 2 while job B updates 2 then 1. Fix in layers: make every writer touch rows in the same order, usually sorted by primary key; shorten transactions so locks live briefly; add retry-with-backoff on the deadlock error because detection is not prevention; and if a queue pattern is hiding underneath, use SELECT FOR UPDATE SKIP LOCKED so workers claim distinct rows. That evidence-then-ordering story is the canonical answer to deadlock SQL interview questions.

Answer: The DDL needed a metadata lock, a long-running query or open transaction held one on that table, and the ALTER queued behind it; because metadata locks queue fairly, every query that arrived after the ALTER queued behind the ALTER, so the whole table went dark even though the DDL never started. Diagnosis: SHOW PROCESSLIST or pg_stat_activity shows the waiting DDL and, via pg_blocking_pids, the offender. The safe path: kill or let the offender finish, set a lock_timeout so future DDL fails fast instead of queueing, and use online procedures, MySQL’s ALGORITHM=INSTANT or INPLACE with LOCK=NONE, PostgreSQL’s metadata-only ADD COLUMN, or pt-online-schema-change and gh-ost for rewrites. Migration discipline is one of the most repeated scenario SQL interview questions because the failure mode is always in production.

Answer: In order: profile first with SELECT email, COUNT(*) FROM customers GROUP BY email HAVING COUNT(*) > 1 to size the problem; decide the canonical row per key, usually the earliest id or the one with recent activity; dry-run the merge plan, because foreign tables referencing the victims need re-pointing or deduplication too; then delete in a transaction using ROW_NUMBER, keeping rn = 1 per email and removing the rest, verifying counts before COMMIT; finally add a UNIQUE constraint on email so the bug cannot return, catching 1062 or 23505 in application code. Wrap-up: schedule the constraint addition before people forget. Duplicates plus constraints is the pairing that cleanup-focused SQL interview questions want demonstrated.

Answer: That is the N+1 pattern: the ORM runs one query for the list of posts, then lazily issues one query per post to fetch its comments, because the association was accessed inside a loop. Diagnose by counting statements per request, the ORM’s query log, or a general log sample. Fix by eager loading: a JOIN fetch or a second query with WHERE post_id IN, so the engine returns everything in one or two round trips; add dataloader-style batching where associations are conditional; and guard the fix with a statement-count assertion in tests. Demonstrating that you can name the pattern from a symptom, then prescribe the eager-loading fix, is exactly what N+1 SQL interview questions are built around.

Answer: Isolation changes the machine, so I check in this order: waiting sessions, pg_stat_activity or SHOW PROCESSLIST, because a fast plan can queue behind lock holders; cache warmth, since a lone run finds everything in the buffer pool while production competes for it, visible as elevated read IO; plan switching, because prepared statements can flip plans depending on parameter values; and resource saturation, CPU run queues and I/O await from other tenants on the box. The tooling is wait-event sampling rather than EXPLAIN, because the plan is innocent here. Separating plan problems from wait problems is precisely the discrimination that scenario SQL interview questions under load are testing.

Answer: First confirm from the plan: an external merge sort line in EXPLAIN ANALYZE, or Using filesort in MySQL, proves the spill. Then attack in order of preference: eliminate the sort by giving the ORDER BY an index that already delivers the rows in order, composite columns matching filter-then-sort; reduce what reaches the sort by filtering earlier or pre-aggregating; and only then raise the memory knobs, work_mem in PostgreSQL or sort buffer settings in MySQL, knowing they are allocated per operation and per session, so a global bump can multiply dangerously. Explaining that per-operation memory math is the detail that separates strong answers to sort-performance SQL interview questions.

Answer: Rows examined versus rows sent is a missing-index signature, so: run EXPLAIN and confirm a scan with a filter, not a seek; collect every predicate, join column, and the ORDER BY from the query, because an index must serve filter, then join, then sort in that priority; check selectivity of the leading column before writing anything, since indexing a low-cardinality flag alone rarely pays; create the composite index, re-run EXPLAIN ANALYZE, and confirm the seek plus a sane row estimate; finally, check for a near-duplicate index the new one makes redundant, because every extra index taxes every write. That walk from symptom to verified CREATE INDEX is the full arc that missing-index SQL interview questions want narrated.

Answer: Stop trying to do it in one transaction. Chunk the update: loop over primary key ranges, update a few thousand rows per transaction, commit, and sleep briefly between chunks so lock queues drain; keyset batching via WHERE id BETWEEN also keeps replication deltas small. Run it off-peak, watch pg_stat_activity or InnoDB lock waits and replica lag while it runs, and make the job resumable by tracking the high-watermark id, because it will be interrupted. Set a statement or lock timeout so a chunk fails fast instead of piling a lock queue behind itself. Chunked, resumable, watched: that trio is the deliverable in batch-job SQL interview questions.

Answer: A deadlock is a cycle the engine detects immediately and resolves by killing one side; a lock wait timeout means no cycle exists, but the holder is simply not releasing, so the waiter gave up after innodb_lock_wait_timeout, fifty seconds by default, or lock_timeout in PostgreSQL. So hunt the holder, not the waiter: find the oldest active transaction, an idle-in-transaction session from a crashed request path, or a long report holding shared locks; fix the code that opens transactions and stalls inside them; and set timeouts so interactive queries and schema jobs cannot hold locks indefinitely. Holder-versus-waiter reasoning is the entire point of this entry in scenario SQL interview questions.

Answer: Replication applies a single large transaction atomically, MySQL’s applier and PostgreSQL’s WAL replay both serialize it, so a transaction touching millions of rows cannot be split downstream: the replica idles, then applies the whole thing in one burst, and Seconds_Behind_Master or replay lag spikes. The primary stays interactive while every replica reader sees stale data, which is the outage users actually notice. Prevent it by committing in chunks, so lag stays proportional to batch size, scheduling bulk work away from peak, and alerting on lag rather than discovering it from users. The atomic-apply mechanic is the explanation that earns full marks in replication-lag SQL interview questions.

Answer: Exact counts must touch the rows, so either make touching cheaper or stop needing exactness. Options in order: a partial index on the active rows makes the count an index-only scan over a sliver of the table; a cached counter, a summary table maintained by triggers or application increments, answers instantly but must be reconciled; a materialized view refreshed on a schedule trades staleness for speed; and an estimate from the planner’s row counts, or an approximate count function where the engine offers one, is often fine for a dashboard. Asking what precision the business actually needs first is the senior move in COUNT-performance SQL interview questions.

Answer: Assume replica lag until proven otherwise: the write lands on the primary, the next read hits a replica still replaying, and the user sees yesterday. Confirm by correlating timestamps: compare the primary write time against replica replay lag at that moment, and reproduce by writing then immediately reading through the read path. Fixes in order of preference: route the post-write read to the primary for that session or a short window, sticky sessions after writes; use monotonic reads where the driver or proxy offers them; and reduce lag itself by fixing the long transactions and bulk jobs causing it. Demonstrating the write-then-read correlation is the diagnostic craft this class of question rewards.

Answer: First prove the cause: diff EXPLAIN output against the known-good plan and look at the row estimates that changed, because new statistics legitimately moved the numbers and the planner honestly picked worse. Then stabilize: raise the statistics target on the skewed columns so estimates stop swinging with each ANALYZE; add extended statistics where correlated columns mislead independence assumptions; force the known-good shape with planner hints such as pg_hint_plan, or SQL Server plan guides, as a bridge, not a destination; and if a version upgrade started the regression, check the upgrade notes for planner changes. Estimate-first reasoning is what separates regression-handling SQL interview questions answers from index-sprinkling.

Answer: Make the load atomic or idempotent, then choose. Atomic: load into a staging table with no constraints, validate and dedupe there, then INSERT into the target inside one transaction, or swap partitions if volumes are large, so a failure leaves the target untouched. Idempotent: ON CONFLICT DO NOTHING or ON DUPLICATE KEY UPDATE makes re-runs safe, though you should log the conflicts rather than silently swallowing data problems. Either way, fix the upstream duplicate generation too, because tomorrow’s run will collide again. Staging-table-then-swap is the pattern that earns the credit in bulk-load SQL interview questions, with idempotency as the running joke of every failed migration.

Answer: In order: if the session is still open and uncommitted, in PostgreSQL it holds row locks and nothing else has seen the damage, so roll it back immediately, which is the best case. If it committed, stop the bleeding: assess scope from the statement and binlog or WAL, then recover by restoring a replica or clone to before the statement and extracting the affected rows, or by targeted binlog inversion in MySQL where row images allow it. Communicate early, because legal and support may need to know what users saw wrong data. Then prevent: sql_safe_updates in MySQL, statement timeouts, restricted write access, and mandatory reviews for ad hoc production SQL. Recovery order under pressure is exactly what destructive-mistake SQL interview questions are scoring.

Answer: It is a race: two requests both ran the check, SELECT found no row, and both INSERTed before either committed, so application validation can serialize nothing by itself. The database is the only serialization point, so the fix is layered: keep the UNIQUE constraint as the final arbiter and catch 1062 or 23505, translating it into a friendly conflict response; replace check-then-insert with upsert, ON CONFLICT, where the intent allows it; and where the race is wider than one row, take SELECT FOR UPDATE on the shared parent row to serialize the critical section. The lesson, that constraint violations are concurrency findings, is the insight duplicate-race SQL interview questions exist to surface.

Answer: Retention and slots, in that order. In PostgreSQL, a replication slot belonging to a decommissioned replica pins WAL forever, so check pg_replication_slots for restart_lsn that never advances, and review archive_command failures; then tune wal_keep_size and archiving. In MySQL, binlogs grow until binlog_expire_logs_seconds prunes them, and a stuck replica or a backup job mid-copy pins the purge, so check SHOW REPLICA STATUS first. Never delete WAL or binlog files by hand; the engine must be the one to reclaim them, or recovery breaks. Slot hygiene is the headline fix in WAL-growth SQL interview questions because it is the most common cause by far.

Answer: A client opened a transaction, executed something, and never finished it: an exception path that skipped rollback, an ORM with implicit transactions left open after a forgotten commit, or a developer’s terminal walked away from an editor session. The cost compounds: the snapshot blocks vacuum from reclaiming dead tuples, bloat grows, and its locks block everyone behind it. Immediate fix: terminate the session with pg_terminate_backend. Durable fix: set idle_in_transaction_session_timeout, and fix the code paths with try-finally or framework-managed transaction boundaries. Naming the vacuum-blocking side effect is the depth marker in idle-in-transaction SQL interview questions.

Answer: Diagnose before rewriting: EXPLAIN shows whether the join order is sane and whether any step is scanning when it could seek. Then reduce in stages: filter each table to the minimal row set first, in CTEs or derived tables, so joins happen over slivers instead of full tables; ensure join keys are indexed and statistics fresh; check the plan’s join order, and hint or restructure if a bad nested loop is driving billions of probes; consider materializing an expensive intermediate when it is reused; and if the pattern is permanent, pre-compute it, a summary table maintained incrementally. Stage-then-join is the mental model that join-rewrite SQL interview questions are fishing for.

Answer: Expand and contract. Add the new column alongside the old; deploy application code that writes both; backfill in keyset batches with sleeps, resumable and throttled, watching replica lag; verify with counts and checksums per range; flip reads to the new column behind a feature flag; stop writing the old one; and drop it last, since dropping is itself an online procedure on huge tables. Direct ALTER on that volume, where the engine rewrites the table, means hours of rebuild or a tool like gh-ost or pg_repack doing it with throttling. The phased contract, add, backfill, flip, drop, is the expected answer in big-migration SQL interview questions.

Answer: Separate by blast radius, cheapest first. Point analytics at a read replica so scans never share the primary’s caches and CPU, accepting lag; add workload isolation where the engine supports it, resource groups or workload management, to cap what reports can take; cache the hot aggregates so the same dashboard does not recompute every minute; and when volume or query shapes keep growing, move analytics to a real warehouse or column store fed by replication, which is the point where the OLTP and OLAP workloads stop pretending to share a machine. Replica-first staging is the pragmatic core of workload-contention SQL interview questions.

Answer: The leading wildcard defeats the B-tree, so growth makes every search a full scan and the timeout is inevitable. Redesign by matching the need: word-level search with relevance wants a full-text index, tsvector with a GIN index in PostgreSQL or FULLTEXT with MATCH AGAINST in MySQL; substring autocomplete wants a trigram index, pg_trgm, which indexes fragments and serves arbitrary LIKE; and when requirements grow into facets, typo tolerance, and ranking, that is the honest moment to move to a dedicated search engine fed from the database. Matching the index type to the query shape is the design judgment search-rewrite SQL interview questions test.

Answer: Connections pile up when queries are slow, so treat it as a symptom first: during the peak, count sessions by state in pg_stat_activity or SHOW PROCESSLIST, and if hundreds are active, the real problem is those queries; if hundreds are idle in transaction or sleeping, application code is leaking or over-opening. Then do the connection math: pool size times application instances against max_connections, because twelve instances with default pools of fifty overflow almost any database. Remedies in order: fix the slow query holding connections, right-size pools, put PgBouncer or ProxySQL in front for multiplexing, and tune connection timeouts so dead clients release. Peak-time connection accounting is the full answer this scenario SQL interview questions wants.

Answer: GROUP BY collapses each customer’s orders to one row, and any column that is neither grouped nor aggregated comes from an arbitrary surviving row, so the max amount and the order id can come from different orders. MySQL’s ONLY_FULL_GROUP_BY now rejects it outright. Correct patterns, best first: ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) in a CTE, filtered to rn = 1, which is portable and deterministic; PostgreSQL’s DISTINCT ON (customer_id) with matching ORDER BY; or a correlated subquery on MAX(created_at) where the planner handles it well. Name-and-fix with a window function is the canonical shape of greatest-per-group SQL interview questions.