30 SQL Interview Questions for Experienced Professionals

Experienced professionals are rarely asked only to define a join. They are given a slow endpoint, a blocking incident, duplicate business rows, or a table that has grown from ten million to one billion records—and asked what they would inspect, change, and measure.

For a structured path from SQL semantics through hands-on query challenges, execution plans, transactions, and Staff-level database scenarios, use the SQL Interview Knowledge & Query Lab. This article remains the focused advanced-question companion to that canonical hub.

This guide contains 30 advanced SQL interview questions and answers for experienced professionals with 6–10 years of experience. The sample answers are concise enough to rehearse, but each includes the production judgment expected from senior engineers, developers, data engineers, and database specialists.

Dialect note: SQL syntax and database behavior differ. Examples use portable SQL where practical. EXPLAIN ANALYZE examples follow PostgreSQL-style syntax; included columns, row-level security, online DDL, materialized views, and locking behavior vary across PostgreSQL, MySQL, SQL Server, Oracle, and cloud warehouses. State your database and version before making an engine-specific claim.

What SQL interviews test at 6–10 years of experience

Years of experience are only a rough signal; employers calibrate scope differently. Still, these are the most common expectations behind searches for SQL interview questions for experienced professionals:

The practical difference is not harder syntax. It is the ability to connect SQL behavior to production impact and communicate a defensible decision.

How to answer senior SQL interview questions

A strong answer usually follows this sequence:

1. Clarify the workload. Ask about the database engine, data volume, read/write ratio, latency target, concurrency, and correctness requirements.
2. Measure before changing anything. Use the actual execution plan, runtime statistics, waits, locks, and production-like parameters.
3. Explain the trade-off. Faster reads may mean slower writes; stronger isolation may mean less concurrency; denormalization may mean harder consistency.
4. Verify the result. Compare the same metrics before and after, test representative values, and describe rollout and rollback.

That structure demonstrates production judgment even when the prompt is broad.

Query performance and indexing

1. A query became slow in production. How would you diagnose it?

Strong answer: First define “slow”: latency, throughput, CPU, I/O, lock wait, or regression from a known baseline. Capture the exact SQL, bind values, engine version, table sizes, and an actual execution plan. Compare estimated and actual rows at each operator; large differences often indicate stale statistics, skew, or correlated predicates. Then inspect scans, joins, sorts, spills, repeated lookups, and lock or resource waits.

Change one cause at a time—query shape, index, statistics, or data access pattern—and rerun with representative parameters. Do not assume the highest-cost plan node is automatically the root cause, and do not test only a warm-cache, low-concurrency case.

-- PostgreSQL-style example; this executes the SELECT.
EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, ordered_at, total_amount
FROM orders
WHERE customer_id = 42
  AND ordered_at >= DATE '2026-01-01';

For UPDATE or DELETE, use a safe non-production copy or an engine-specific estimated-plan facility before running an analyzed plan.

2. How do you choose column order in a composite index?

Strong answer: Start from recurring query shapes, not a rule such as “put the most selective column first.” For a B-tree index, equality predicates commonly come before the first range predicate, followed by columns useful for ordering or grouping. The useful prefix and skip-scan behavior are engine-specific.

For this query, (customer_id, ordered_at) is a natural candidate because the workload filters by equality on customer_id and then a range on ordered_at:

CREATE INDEX idx_orders_customer_date
    ON orders (customer_id, ordered_at);

Also test whether the reverse order serves another important workload, whether either single-column index is now redundant, and how the extra index affects writes and storage.

3. What is a covering index, and when can it hurt?

Strong answer: A covering index supplies all columns required by a query, so the engine may avoid extra base-table lookups. SQL Server can place non-search columns in INCLUDE; PostgreSQL supports included columns but an index-only scan still depends on visibility information; other engines differ.

-- SQL Server and PostgreSQL support INCLUDE with different internals.
CREATE INDEX idx_orders_customer_date
    ON orders (customer_id, ordered_at)
    INCLUDE (status, total_amount);

It can help high-frequency, selective reads. It can hurt when included columns make the index wide, change often, duplicate another index, or turn every write into more I/O and maintenance. Verify the chosen access path and workload impact rather than calling every multi-column index “covering.”

4. Why might the optimizer ignore an available index?

Strong answer: An index is not always cheaper than a scan. The optimizer may expect the query to return a large share of the table, the table may be small, statistics may be stale, the predicate may not match the index prefix, an implicit cast or function may make the predicate non-sargable, or the index may not cover enough columns to avoid many lookups.

Check the plan and cardinality estimates before forcing the index. Also test parameter skew: one cached or generic plan may be good for common values and poor for rare values. A senior answer explains why the cost model chose the plan, not merely how to force a different one.

5. What makes a predicate sargable?

Strong answer: A sargable predicate lets the engine use an index search condition instead of applying a function to every candidate row. Rewrite transformations on the indexed column into an equivalent range when possible.

-- Often non-sargable
WHERE EXTRACT(YEAR FROM ordered_at) = 2026
 
-- Sargable half-open range
WHERE ordered_at >= TIMESTAMP '2026-01-01 00:00:00'
  AND ordered_at <  TIMESTAMP '2027-01-01 00:00:00'

Implicit type conversions, leading-wildcard searches, arithmetic on the indexed column, and mismatched collations can cause similar problems. Functional or expression indexes may be appropriate when the transformed lookup is itself a stable, frequent requirement.

6. Is an INNER JOIN always faster than a LEFT JOIN?

Strong answer: No. Join semantics come first, and performance depends on cardinality, indexes, row width, join algorithm, memory, and optimizer transformations—not on “NULL checks.” An optimizer can sometimes reduce a LEFT JOIN to an inner join when later predicates prove unmatched rows cannot survive.

Use INNER JOIN when only matches are valid; use LEFT JOIN when every left-side row must remain. Then inspect whether the plan uses nested loops, hash join, or merge join appropriately and whether estimates are accurate. Rewriting a left join purely for speed is wrong if it changes the result.

7. When is a table scan better than an index scan?

Strong answer: A scan can be optimal when the table is small, the query returns a large fraction of rows, the data is already cached or stored column-wise, or random base-table lookups would cost more than sequential access. Analytics queries that aggregate most rows are common examples.

The goal is not “eliminate every scan.” The goal is to minimize total work while meeting latency and throughput targets. Compare pages or bytes read, actual rows, elapsed time, CPU, and concurrency effects. A selective query unexpectedly scanning a large table deserves investigation; a full-table report scanning by design may not.

8. How do you detect and fix over-indexing?

Strong answer: Inventory indexes, usage statistics, duplicate prefixes, write amplification, maintenance time, cache pressure, and storage. Look for indexes with identical keys, a narrow index that is fully covered by a frequently used wider index, and indexes that are never read but constantly updated.

Do not drop an index from one short observation window: it may support month-end jobs, a rare incident path, a constraint, or a foreign-key workload. Validate dependencies, observe a representative cycle, disable or remove one candidate through a controlled rollout, and retain a rollback script.

SQL query interview questions for experienced professionals

9. What is the difference between ROW_NUMBER(), RANK(), and DENSE_RANK()?

Strong answer: ROW_NUMBER() assigns every row a unique sequence; ties are broken by the complete ORDER BY, so add a deterministic tiebreaker. RANK() gives ties the same value and leaves gaps. DENSE_RANK() gives ties the same value without gaps.

SELECT employee_id,
       salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC, employee_id) AS row_num,
       RANK()       OVER (ORDER BY salary DESC)              AS rank_num,
       DENSE_RANK() OVER (ORDER BY salary DESC)              AS dense_rank_num
FROM employees;

Use ROW_NUMBER() to pick exactly one row, RANK() for competition ranking, and DENSE_RANK() when the next distinct value should receive the next rank.

10. How do you return the top three orders per customer?

Strong answer: Rank rows inside each customer partition, then filter the ranked result. Include a stable tiebreaker so repeated executions return the same rows.

WITH ranked_orders AS (
    SELECT order_id,
           customer_id,
           total_amount,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id
               ORDER BY total_amount DESC, order_id
           ) AS rn
    FROM orders
)
SELECT order_id, customer_id, total_amount
FROM ranked_orders
WHERE rn <= 3;

If all orders tied at third place must be returned, use RANK() or DENSE_RANK() and clarify which business rule applies. An index beginning with the partition and ordering columns may help, but verify the plan.

11. How do you solve a gaps-and-islands problem?

Strong answer: Define precisely what makes rows consecutive, then derive a grouping key that stays constant inside each island. For consecutive calendar dates per account, subtract the row number from the date and group by the result.

WITH numbered AS (
    SELECT account_id,
           activity_date,
           activity_date
             - (ROW_NUMBER() OVER (
                    PARTITION BY account_id
                    ORDER BY activity_date
                ) * INTERVAL '1 day') AS island_key
    FROM daily_activity
), islands AS (
    SELECT account_id,
           MIN(activity_date) AS start_date,
           MAX(activity_date) AS end_date,
           COUNT(*) AS day_count
    FROM numbered
    GROUP BY account_id, island_key
)
SELECT * FROM islands;

Date arithmetic differs by engine. Also decide how duplicates, weekends, and missing expected dates should behave before writing the query.

12. How do you remove duplicates while keeping the newest row?

Strong answer: First define the business key and the deterministic “newest” order. Rank duplicates, inspect the rows that would be removed, then delete inside a transaction or controlled batch.

WITH ranked AS (
    SELECT event_id,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id, event_type
               ORDER BY created_at DESC, event_id DESC
           ) AS rn
    FROM customer_events
)
SELECT event_id
FROM ranked
WHERE rn > 1;

The exact DELETE ... FROM syntax is vendor-specific. After cleanup, prevent recurrence with the correct unique constraint or idempotency key. A one-time delete without an invariant is not a complete senior-level solution.

13. EXISTS vs IN vs JOIN: which should you use?

Strong answer: Choose by semantics. EXISTS expresses an existence test and cannot multiply outer rows. IN expresses membership and is often optimized into an equivalent semi-join. An inner JOIN returns combinations and may duplicate outer rows when multiple matches exist.

Modern optimizers can produce the same plan for equivalent EXISTS and IN queries, so “EXISTS is always faster” is not a valid rule. Be especially careful with NOT IN: if the subquery can return NULL, the predicate can become unknown for every row. NOT EXISTS usually expresses the intended anti-join safely.

14. When should you use a CTE, derived table, or temporary table?

Strong answer: Use a CTE or derived table to make a single statement understandable and composable. Do not assume a CTE is always materialized or always inlined; that varies by database, version, and query. Use a temporary table when an intermediate result is reused, needs an index, benefits from its own statistics, or creates a useful optimization boundary.

Temporary tables add writes, logging, allocation, and cleanup, and they may increase contention. Compare plans and runtime for the actual engine. Recursive CTEs are a separate capability for hierarchies and graph-like traversal, with engine-specific recursion limits and cycle handling.

Transactions, locking, and concurrency

15. How do transaction isolation levels differ?

Strong answer: Explain them through anomalies, then map them to the target engine. READ UNCOMMITTED permits dirty reads where supported. READ COMMITTED prevents dirty reads but may allow non-repeatable reads and phantoms. REPEATABLE READ protects previously read rows more strongly, although exact phantom and snapshot behavior varies. SERIALIZABLE aims to make concurrent execution equivalent to some serial order, usually with lower concurrency or retryable serialization failures.

Snapshot-based modes use row versions to reduce read/write blocking, but they do not make every multi-row business invariant automatically safe. Choose the weakest level that protects required invariants, and test contention and retries.

This table shows the standard interview model. PostgreSQL, MySQL, SQL Server, and Oracle implement parts of it differently, particularly repeatable-read and snapshot behavior.

16. How do you diagnose and prevent a deadlock?

Strong answer: A deadlock is a cycle: each transaction holds a resource another transaction needs. Capture the engine's deadlock graph or log to see the exact statements, resources, and lock order. Fix recurring causes by accessing tables and rows in a consistent order, keeping transactions short, indexing predicates so fewer rows are locked, and moving network or user interaction outside the transaction.

Timeouts detect waiting but do not remove the underlying cycle. Even a well-designed system can encounter deadlocks, so retry the victim transaction with bounded backoff. The operation must be idempotent or otherwise safe to repeat.

17. Optimistic vs pessimistic concurrency: when would you use each?

Strong answer: Pessimistic concurrency locks before a conflicting change and suits short, high-contention workflows where conflicts are expected and waiting is acceptable. Optimistic concurrency reads a version, attempts a conditional update, and retries or reports a conflict if the version changed.

UPDATE documents
SET body = :new_body,
    version = version + 1
WHERE document_id = :id
  AND version = :expected_version;

If zero rows are updated, someone else won. Optimistic control works well when conflicts are rare; pessimistic control may be simpler when the cost of repeated work is high. Engine-specific FOR UPDATE behavior and lock scope must be verified.

18. How would you implement a safe money transfer?

Strong answer: Validate the request and idempotency key, begin a transaction, lock or conditionally update the accounts in a consistent order, enforce that the source cannot violate the balance rule, write immutable ledger entries, and commit both sides atomically. Do not read a balance, calculate in application memory, and later write it without concurrency protection.

Use fixed-precision numeric types, database constraints, and a unique idempotency key so retries cannot create a second transfer. The exact locking and isolation choice depends on the ledger model and database, but every answer should preserve the double-entry or equivalent accounting invariant.

19. Why are long-running transactions dangerous?

Strong answer: They hold locks or old row versions longer, increase blocking and deadlock probability, delay cleanup, grow version or undo storage, and make failure recovery more expensive. Common causes include user interaction, API calls, processing huge batches, and idle sessions inside a transaction.

Keep the transactional unit aligned with one business invariant. Move remote calls outside it, batch large changes with checkpoints, set appropriate timeouts, monitor transaction age, and ensure every error path rolls back or closes the connection. Do not shorten a transaction so aggressively that the business operation stops being atomic.

20. OFFSET vs keyset pagination: what are the trade-offs?

Strong answer: OFFSET is simple and supports arbitrary page numbers, but deep pages may scan and discard many earlier rows. Concurrent inserts or deletes can also shift rows between requests. Keyset pagination uses the last seen sort key and can seek into a matching index, giving stable work per page.

SELECT order_id, created_at, total_amount
FROM orders
WHERE (created_at, order_id) < (:last_created_at, :last_order_id)
ORDER BY created_at DESC, order_id DESC
FETCH FIRST 50 ROWS ONLY;

Row-value comparison and limit syntax vary by engine. The sort must be deterministic, usually with a unique tiebreaker. Keyset pagination does not naturally jump to page 500.

Data modeling and scale

21. When should you normalize or denormalize a schema?

Strong answer: Normalize transactional data to make facts have one owner, reduce update anomalies, and enforce integrity with keys and constraints. Denormalize only for a measured access pattern—for example, a read-heavy aggregate that is too costly to compute repeatedly.

Before duplicating data, define the source of truth, refresh mechanism, acceptable staleness, repair process, and write amplification. Materialized views, caches, or a separate analytical model may be safer than manually duplicating columns. “Normalize for writes, denormalize for reads” is a useful prompt, not a universal rule.

22. What problem does table partitioning solve?

Strong answer: Partitioning splits one logical table into physical partitions, commonly by date or another pruning key. It can reduce scanned data when predicates allow partition pruning, make retention operations cheaper, and isolate maintenance. It does not automatically distribute load across servers, and it does not rescue queries that lack the partition key.

Choose boundaries from real access and retention patterns, avoid too many tiny partitions, align important indexes where appropriate, and verify pruning in the plan. A poor partition key can create hotspots or force most queries to touch every partition.

23. When is a materialized view a good choice?

Strong answer: Use a materialized view when an expensive, repeatable query can tolerate controlled staleness—for example, a dashboard aggregate over a large fact table. Unlike a normal view, it stores results and must be refreshed. The refresh method, locking behavior, incremental support, and indexing options depend on the engine.

Define a freshness service-level objective, refresh trigger, failure monitoring, and fallback. If every request needs current transactional data, a stale materialized result is the wrong abstraction. Also compare it with a maintained summary table or warehouse transformation.

24. When should you shard a relational database?

Strong answer: Shard only after a single primary or cluster cannot meet a measured capacity, locality, or isolation requirement with simpler options such as indexing, query changes, archiving, partitioning, replicas, or vertical scaling. Choose a key that spreads traffic and keeps common transactions on one shard.

Plan for routing, resharding, hot tenants, globally unique IDs, cross-shard queries, transactions, backups, and schema rollout. Sharding trades one database bottleneck for distributed-systems complexity. For deeper practice on these trade-offs, use the system design interview guides.

25. How do you perform a zero-downtime schema migration?

Strong answer: Use an expand-and-contract rollout. First add a backward-compatible schema change, then deploy code that can work with both versions. Backfill in bounded, resumable batches, validate counts and invariants, switch reads, stop old writes, and only later remove the old column or table.

Know whether the DDL operation is truly online for the engine, version, table size, and options; “online” may still take metadata locks. Monitor replication lag, lock time, and error rate, and prepare a rollback or roll-forward plan. Never couple an irreversible destructive DDL change to the first application deployment.

26. When should JSON be stored in a relational database?

Strong answer: JSON is useful for sparse, evolving attributes that are usually read and written as a unit. Keep identifiers, relationships, frequently filtered fields, money, and invariant-bearing data in typed columns with constraints. If a JSON path becomes a common predicate, expose and index it using the engine's supported expression, generated-column, or JSON index mechanism.

Do not use JSON merely to avoid schema design. You give up some type safety, referential integrity, discoverability, and portable query behavior. Confirm JSON operators and index syntax for the target engine.

Security and operational judgment

27. How do you prevent SQL injection?

Strong answer: Bind untrusted values through server-side parameterized queries or prepared statements; never build SQL by concatenating input. Parameters cannot represent identifiers such as column names, so map those choices through a strict allowlist. Use least-privilege database accounts, protect secrets, and keep errors from exposing internals.

Input validation is useful defense in depth but is not a replacement for parameterization. An ORM is safe only when its parameter APIs are used correctly; raw-query escape hatches and dynamic fragments can still be vulnerable. OWASP recommends prepared statements as the primary defense.

28. How would you enforce tenant isolation in a shared database?

Strong answer: Put a tenant identifier on every tenant-owned row, include it in keys and access paths where appropriate, and make the application establish tenant context through a trusted path. Row-level security can add database-enforced filtering, but the policy must be tested for every role, privileged connection, maintenance task, and connection-pool reuse case.

Use least privilege and automated cross-tenant tests. For higher-risk tenants, separate schemas or databases may provide a stronger isolation boundary at greater operational cost. Do not rely on developers remembering to append WHERE tenant_id = ? to every query.

29. What is the difference between DELETE, TRUNCATE, and DROP?

Strong answer: DELETE removes rows and can usually filter them; it participates in normal row-level DML semantics. TRUNCATE removes all rows through a DDL-like, engine-specific operation that is often faster and may affect identities, triggers, locks, logging, and rollback differently. DROP removes the database object itself.

Never claim that TRUNCATE is universally non-transactional or cannot be rolled back—behavior varies by engine. Before using it, check foreign keys, replication or change-data-capture behavior, privileges, and recovery requirements. Choose by required semantics, not just speed.

30. When should you use query hints?

Strong answer: Treat a hint as a last-mile, engine-specific intervention after proving the optimizer repeatedly chooses a harmful plan and after addressing statistics, indexes, sargability, parameter skew, and query shape. Record the plan evidence, target engine and version, expected benefit, and removal condition.

Hints can become stale as data distribution, schema, hardware, and optimizer versions change. Test representative parameters and upgrades, monitor the forced plan, and keep rollback simple. A senior engineer can use hints, but does not use them to avoid understanding the plan.

Senior SQL interview preparation checklist

Before the interview, make sure you can do the following without memorized slogans:

• Read an actual execution plan and explain estimated versus actual cardinality.
• Design one composite index for a stated workload and explain why another order is worse.
• Write top-N-per-group, deduplication, running-total, and gaps-and-islands queries.
• Explain a lost update, dirty read, non-repeatable read, phantom, and deadlock.
• Describe one production performance incident using baseline, evidence, change, and measured result.
• Name the database engine and version before discussing vendor-specific behavior.
• Explain safe rollout and rollback for an index or schema change.

If the role also covers application/database integration, review this guide to diagnosing JDBC result-set failures. For broader language-round preparation, see the senior Java interview questions.

Authoritative references and dialect notes

These primary sources are useful when validating engine-specific interview answers:

• PostgreSQL: Using EXPLAIN
• PostgreSQL: Indexes, including multicolumn and covering indexes
• SQL Server: Clustered and nonclustered indexes
• SQL Server: Transaction isolation levels
• SQL Server: Deadlocks guide
• MySQL 8.4: EXPLAIN and EXPLAIN ANALYZE
• MySQL 8.4: Window functions
• OWASP: SQL Injection Prevention Cheat Sheet

The article was technically reviewed against these sources on August 2, 2026.