SQL Interview Questions for 5 Years Experience

By five years, nobody is testing whether you can write a JOIN. The SQL interview at this level is about whether you can keep a real database fast and correct under load — reading execution plans, choosing isolation levels, breaking deadlocks and evolving schemas without downtime. The questions assume you have owned production tables and want to hear judgment earned from that, not textbook recitations.

The examples use orders(id, customer_id, status, amount, created_at) and standard SQL, with dialect notes where PostgreSQL, MySQL/InnoDB and SQL Server differ.

What changes in a SQL interview at five years?

The focus shifts from writing queries to diagnosing and operating them. Expect execution plans, concurrency control, index and partition strategy, and migration safety — all framed as "you have this problem in production, what do you do?"

Answer these from a process, not a trick: measure, form a hypothesis, change one thing, verify. Demonstrating that loop is worth more than naming a clever fix, because it is what the job actually requires.

Q1. Walk me through optimising a slow query.

Capture the query and its execution plan, locate the costly operator (sequential scan, hash join spilling to disk, a large sort), check whether the predicate is sargable and statistics are current, then adjust an index or rewrite the predicate and re-check the plan.

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, SUM(amount)
FROM orders
WHERE created_at >= '2026-01-01' AND status = 'PAID'
GROUP BY customer_id;
-- Look for: Seq Scan on a big table, Sort spilling to disk, rows estimate vs actual gap

The five-year signal is naming why the plan is bad — a huge estimate-vs-actual row gap means stale statistics; a full scan on a filtered query means a missing or unusable index. Then propose (status, created_at) and confirm the scan becomes an index scan.

Interview note: Follow-up: "the estimate is way off from actual rows — why?" Stale statistics or correlated columns the optimizer models as independent. Run ANALYZE, or add extended/multi-column statistics.

Q2. Explain the isolation levels and the anomalies they prevent.

READ UNCOMMITTED allows dirty reads; READ COMMITTED prevents dirty reads; REPEATABLE READ also prevents non-repeatable reads; SERIALIZABLE additionally prevents phantoms. Each higher level trades concurrency for consistency.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
  SELECT SUM(amount) FROM orders WHERE customer_id = 5;  -- stable within this txn
COMMIT;

The experienced nuance: MVCC databases don't implement these with plain locks. PostgreSQL's REPEATABLE READ is snapshot isolation (prevents phantoms in practice), and its SERIALIZABLE adds serialization-failure detection you must retry. MySQL/InnoDB defaults to REPEATABLE READ with next-key locks. Naming your database's actual default and mechanism is the differentiator.

Interview note: Trap: "which level stops a lost update?" Not READ COMMITTED — you need REPEATABLE READ/SERIALIZABLE, or explicit SELECT ... FOR UPDATE to lock the row you intend to modify.

Q3. How do you diagnose and prevent deadlocks?

Deadlocks happen when two transactions lock resources in opposite orders. Prevent them by acquiring locks in a consistent order, keeping transactions short, locking the fewest rows via good indexes, and making the application retry the aborted victim.

-- Transaction A and B updating two accounts in opposite orders can deadlock.
-- Fix: always lock the lower id first.
BEGIN;
  SELECT * FROM accounts WHERE id = LEAST(:a, :b) FOR UPDATE;
  SELECT * FROM accounts WHERE id = GREATEST(:a, :b) FOR UPDATE;
  -- ... perform the transfer ...
COMMIT;

The database resolves a deadlock by killing one transaction, so robust systems catch the deadlock error and retry. Explaining both prevention (consistent lock order) and recovery (retry the victim) is the complete answer.

Interview note: Follow-up: "how do you find what deadlocked?" Read the deadlock log — Postgres logs it, SQL Server has the deadlock graph, InnoDB's SHOW ENGINE INNODB STATUS. Always inspect the actual lock waits.

Q4. When and how would you partition a large table?

When a table grows to hundreds of millions of rows and queries filter on a natural key like date. Range-partition by that key so the optimizer prunes to relevant partitions, and archiving becomes a partition drop instead of a mass DELETE.

-- PostgreSQL declarative range partitioning by month
CREATE TABLE orders (id BIGINT, created_at DATE, amount NUMERIC)
PARTITION BY RANGE (created_at);

CREATE TABLE orders_2026_07 PARTITION OF orders
  FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

The wins to name: partition pruning (queries touch only matching partitions), cheap archival (DROP TABLE orders_2026_01 instead of deleting millions of rows), and smaller per-partition indexes. The caveat: the partition key must be in your common query predicates or pruning doesn't happen.

Interview note: Trap: "does partitioning always speed things up?" No — if queries don't filter on the partition key, you scan every partition, which can be slower than one well-indexed table.

Q5. How do you design an index strategy for a busy table?

Index the columns your real queries filter and join on, order composite indexes by equality-then-range, add covering columns for hot read paths, and continuously drop indexes that are never used because each one taxes every write.

-- Serve the common filter + sort, covering the selected column
CREATE INDEX idx_orders_hot ON orders (customer_id, status, created_at) INCLUDE (amount);

-- Audit for dead weight (PostgreSQL)
SELECT indexrelname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0;

At five years you're expected to manage the index set over time, not just add indexes: too many cripples write throughput and bloats cache. Framing it as an ongoing read/write balance, with periodic audits, is the senior posture.

Interview note: Follow-up: "a write-heavy table is slow — could indexes be the cause?" Yes — every INSERT maintains every index. Drop unused ones, or drop-load-rebuild for bulk loads.

Q6. How do you run a schema migration with zero downtime?

Make changes backward-compatible and multi-step: add nullable columns or new tables first, backfill in batches, deploy code that writes both old and new, then switch reads and finally remove the old path. Never do a blocking ALTER on a huge table in one shot.

-- Step 1: add nullable column (fast, non-blocking on modern engines)
ALTER TABLE orders ADD COLUMN status_v2 VARCHAR(20);

-- Step 2: backfill in batches to avoid a long lock / huge transaction
UPDATE orders SET status_v2 = status WHERE id BETWEEN 1 AND 100000;
-- repeat in ranges...

The failure mode to avoid: a single ALTER TABLE ... SET NOT NULL or a full-table UPDATE that locks the table or fills the transaction log. Expand-migrate-contract, with batched backfills, is the pattern interviewers want to hear named.

Interview note: Trap: "adding a NOT NULL column with a default — safe?" On modern Postgres/MySQL a constant default is metadata-only and fast; a volatile default or old versions rewrite the whole table. Know your version's behaviour.

Q7. How do you handle the N+1 query problem?

Detect it (one query per parent row in the logs), then batch: fetch the children for all parents in a single query using IN or a join, or use the ORM's eager-loading. The goal is a constant number of queries, not one per row.

-- N+1: one of these per order (bad)
-- SELECT * FROM order_lines WHERE order_id = ?;

-- Batched: all children in one round trip
SELECT * FROM order_lines WHERE order_id IN (1, 2, 3, /* ...all fetched order ids */);

This is a five-year staple because it bridges SQL and application code. The answer that lands names how you spot it (query-count spikes, slow endpoints under load) as well as the fix, showing you debug real systems.

Interview note: Follow-up: "join vs IN for batching?" A join returns parent+child together in one pass; IN needs the parent ids first. Choose by whether you already have the parent set in memory.

Q8. When do you denormalize or add a cache, and how do you keep it correct?

When a hot read path's joins cost more than the redundancy is worth and the data changes infrequently. Introduce a materialized view, a summary table, or a cache — and pair it with a deliberate refresh mechanism so the copy can't silently drift.

CREATE MATERIALIZED VIEW customer_totals AS
SELECT customer_id, COUNT(*) AS orders, SUM(amount) AS lifetime_value
FROM orders GROUP BY customer_id;

REFRESH MATERIALIZED VIEW CONCURRENTLY customer_totals;  -- scheduled

The maturity signal is treating consistency as a managed concern: who refreshes, how often, and what staleness the business tolerates. "Add a cache" without a staleness and invalidation plan is a junior answer; naming the refresh strategy is the senior one.

Interview note: Trap: "why CONCURRENTLY?" A plain REFRESH locks the view against reads; CONCURRENTLY rebuilds without blocking readers, at the cost of needing a unique index and more work.

How to prepare

Take one genuinely slow query from any project, run EXPLAIN ANALYZE, and practise narrating the plan and your fix out loud — that single rehearsal covers the optimisation, index and statistics questions at once. Then set up a two-connection deadlock in a scratch database and resolve it with consistent lock ordering; experiencing the abort-and-retry cycle makes the concurrency answers concrete instead of memorized.

Because your peers at this band are moving toward architecture, pair this with the broader SQL questions for experienced professionals and drill the indexes deep dive, which underpins half the optimisation answers here. The SQL learning path refreshes the transaction and storage model these questions assume. A mock interview that hands you a slow query and a plan is the closest possible simulation of the five-year SQL round.