SQL Interview Questions for Experienced Professionals

Start with an execution plan (EXPLAIN / EXPLAIN PLAN) to see the current access path, and compare it against what you'd expect. The usual suspects, roughly in order of likelihood: stale optimizer statistics after a large data change (gather them and re-check), a plan flip caused by a parameter-sensitive query getting cached with an atypical execution plan, a dropped, disabled, or corrupted index, or the data simply growing past the point where the old plan is efficient — a nested-loop join that was fine at 10K rows can be disastrous at 10M. I'd also check whether anything changed operationally around the same time: a deployment, a maintenance window that skipped stats collection, or a spike in concurrent load.

Confirm it with an execution plan first — don't assume. Then check, in order: is a function wrapping the indexed column (UPPER(col) = ... defeats a plain index unless there's a matching function-based index)? Is there an implicit data-type conversion forcing a scan? Does a composite index exist but the query isn't filtering on its leading column? Are statistics stale enough that the optimizer thinks a scan is cheaper than it actually is? And finally — is the optimizer just right, because the query will return a large fraction of the table anyway, in which case an index wouldn't help.

Read the execution plan bottom-up to find where the row count balloons — that's usually a join producing far more intermediate rows than expected, often from a missing or wrong join condition, or from joining before filtering instead of after. I'd push filtering as early as possible (filter each table before joining it, not after), confirm every join column is indexed, and check whether the GROUP BY can operate on a smaller pre-aggregated subset instead of the full joined result. If the query genuinely needs to touch a huge amount of data, materializing an intermediate result (a temp table or materialized view refreshed on a schedule) is often the real fix, not a cleverer single query.

It depends on how the columns are actually queried together. A composite index on (department_id, hire_date) serves queries filtering on department_id alone, or on both columns — but not hire_date alone, because of the leftmost-prefix rule. If queries filter on each column independently in different places, separate single-column indexes (or letting the optimizer combine them via a bitmap/index-merge, where supported) usually serves the workload better than one composite index that only helps a subset of queries. I look at the actual query patterns hitting the table, not just the table's structure, before choosing.

Pull the deadlock graph or trace — Oracle writes a trace file and you can also query DBA_BLOCKERS/DBA_WAITERS; SQL Server surfaces a deadlock graph through Extended Events or the error log; PostgreSQL logs it directly if log_lock_waits is on. That graph shows which two transactions each held a lock the other was waiting for, forming a cycle. The fix is almost always one of two things: make every transaction that touches the same set of resources acquire locks in the same order, or shorten the transaction so it holds locks for less time (commit more often, avoid user-interaction pauses mid-transaction). I'd also check whether an index is missing on a foreign key column, since that's a very common, easy-to-fix deadlock contributor.

I'd start by identifying what's actually write-heavy versus read-heavy and whether they can be separated — a common pattern is an append-only events/log table optimized purely for fast inserts, with a separate, more heavily indexed read-optimized table or materialized view kept in sync asynchronously. On the write-heavy table itself, I'd keep indexes minimal (every index adds write overhead), consider partitioning by time to keep active write regions small, and evaluate whether a queue or batching layer in front of the database can smooth out write spikes rather than hitting the table directly on every event.

Horizontal partitioning splits a table's rows across partitions — commonly by date range or a hash key — so each partition holds a subset of rows with every column. It's the tool for very large tables, letting the optimizer prune irrelevant partitions entirely from a scan (a query for last month's orders only touches last month's partition). Vertical partitioning splits a table's columns into separate tables instead, usually to isolate rarely-accessed or very wide columns (large text, BLOBs) from the compact, frequently-queried columns — keeping the hot table small and cache-friendly.

Make every step additive and backward-compatible rather than a single destructive change. Add a new nullable column instead of altering an existing one in place; backfill it in small batches to avoid a long-held lock or a huge single transaction; deploy application code that writes to both the old and new column during the transition; switch reads over once backfill is confirmed complete and consistent; only drop the old column once nothing references it anymore. The core principle is avoiding any single operation that locks the whole table for the duration of a large rewrite.

Two common approaches, and I'd pick based on query needs: a separate *_audit table populated by a trigger (or application-level logging) on every INSERT/UPDATE/DELETE, capturing old and new values, the user, and a timestamp — good when you need to query history often. Or a temporal/versioned-table pattern where old rows are never physically deleted, just marked with an end_date, and a view exposes only currently-valid rows — good when "what did this look like as of date X" is a first-class query, not just a rare audit lookup.

CREATE TABLE employees_audit (
  audit_id     INT PRIMARY KEY,
  employee_id  INT,
  changed_by   VARCHAR(50),
  changed_at   TIMESTAMP,
  old_salary   NUMERIC(8,2),
  new_salary   NUMERIC(8,2)
);

First, confirm what's actually being asked of the table — most queries against a huge table only need a recent slice of it, which is the strongest argument for date-range partitioning: it turns "scan hundreds of millions of rows" into "scan one partition." Beyond that: make sure every frequent query path is genuinely index-supported (not assumed to be), consider archiving or moving cold historical data to cheaper storage if it's rarely queried, and revisit whether some reporting-style queries should run against a replica or a separate analytical store instead of the primary transactional database.

Give every unit of work a unique idempotency key, and make the write operation check-then-act atomically against it — typically a unique constraint on the key plus an upsert (INSERT ... ON CONFLICT DO NOTHING, or MERGE) rather than a separate "check if it exists" query followed by a conditional insert, which has a race condition. That way, re-running the same batch of work after a crash safely no-ops on anything already applied, instead of double-processing it.

INSERT INTO payments (idempotency_key, amount)
VALUES ('evt_8f21ab', 49.99)
ON CONFLICT (idempotency_key) DO NOTHING;

SELECT * pulls columns the caller may not need, increases network/IO cost, and silently breaks if the table's columns change later — I'd ask for explicit column lists, especially in anything feeding an API or another system. For nested subqueries, I'd check whether any of them are correlated subqueries that could be rewritten as a join for clarity and performance, and whether the nesting depth is actually necessary or just how the query evolved — often a CTE (WITH) rewriting the same logic top-down is easier for the next person to read and debug, even if the execution plan ends up identical.

I lean toward keeping business logic in application code, where it's testable, version-controlled alongside the rest of the system, and visible to every engineer without needing database-specific knowledge. I reach for the database (triggers, constraints, stored procedures) specifically for things that must hold true no matter what touches the data — data integrity rules, and operations that genuinely need to run atomically as part of the same transaction as the write. Triggers in particular I use sparingly, because hidden side effects that fire outside the calling code's visibility are a common source of hard-to-debug production surprises.

I'd avoid the technical vocabulary and anchor it in something they already understand: "the report is combining data from several different tables and looking through a lot of history to build the numbers you're seeing — like re-checking every receipt in a filing cabinet instead of reading a summary sheet someone already prepared." Then I'd translate the fix into terms they care about: "we can pre-calculate a summary overnight so it's instant during the day, at the cost of the numbers being up to a day old" — giving them the actual trade-off (speed vs. freshness) to weigh in, rather than just promising to "optimize it."