SQL Interview Questions for Experienced Professionals

For experienced professionals, the SQL interview stops rewarding recall and starts rewarding judgment. Interviewers hand you open-ended problems — "design the schema for this feature", "this database is struggling under load, what now?" — and listen for how you reason about trade-offs, state assumptions, and own outcomes. The right answer is usually "it depends, and here is what it depends on."

The examples use an e-commerce domain and standard SQL. Because these questions are about design decisions, the code illustrates a point rather than being the whole answer.

What is different about SQL interviews for experienced candidates?

The questions become design- and ownership-oriented: modeling, store selection, scaling, data integrity across services, and refactoring. There is rarely one correct answer, so interviewers grade the reasoning — the assumptions you surface and the trade-offs you weigh.

Lead every answer by clarifying the workload: read/write ratio, data volume, consistency needs, growth. Naming those before proposing a solution is exactly the senior behavior being scored.

Q1. How do you design a schema for a new feature?

Start from the entities and their relationships, model to 3NF for the transactional core so each fact lives once, define keys and constraints to enforce integrity, then denormalize specific read paths only where measured performance requires it.

-- A wishlist feature: normalized core with a junction table for many-to-many
CREATE TABLE wishlists (id BIGINT PRIMARY KEY, customer_id BIGINT NOT NULL);
CREATE TABLE wishlist_items (
  wishlist_id BIGINT REFERENCES wishlists(id) ON DELETE CASCADE,
  product_id  BIGINT REFERENCES products(id),
  added_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (wishlist_id, product_id)
);

The experienced move is designing the constraints alongside the tables — the composite key preventing duplicate items, the cascade defining cleanup — so integrity is declarative from day one. Talking through relationships and keys, not just column lists, is what separates you from a junior.

Interview note: Follow-up: "when would you break 3NF here?" If a product's name is read on every wishlist render and rarely changes, a cached copy or a join to a well-indexed products table — measured, not assumed.

Q2. OLTP vs OLAP — how do you choose?

OLTP for many small concurrent transactions on a normalized, write-optimised schema (order processing). OLAP for large analytical scans over denormalized, often columnar data (reporting, dashboards). Mixing heavy analytics on your OLTP primary is a classic mistake.

The pattern to describe: keep the transactional database lean for the app, and move analytics to a replica, a warehouse, or a columnar store fed by ETL/CDC — so a giant reporting query never contends with customer checkouts.

-- OLAP-style query you do NOT want hammering the OLTP primary
SELECT date_trunc('month', created_at) AS m, SUM(amount)
FROM orders GROUP BY 1 ORDER BY 1;   -- run this on a replica/warehouse

Recognizing the contention problem — analytics starving transactional workload — is the insight that shows real operational experience.

Interview note: Follow-up: "how does data get from OLTP to OLAP?" Batch ETL or streaming CDC into a warehouse. Naming the pipeline shows you've seen the full picture, not just two boxes.

Q3. How do you scale a SQL database under growing load?

Climb a ladder: tune queries and indexes, scale up hardware, add read replicas for read-heavy load, add caching for hot reads, and only when a single primary can't absorb the writes do you partition or shard. Each rung adds complexity.

The framing interviewers want is that sharding is a last resort, not a first move — it breaks cross-shard joins and transactions, so you exhaust cheaper options first. Read replicas plus caching solve most read-scaling problems without that pain.

-- Read replica routing (application-level): reads go to a replica, writes to primary
-- SELECT ... FROM orders  -> replica
-- INSERT/UPDATE ...       -> primary

Explaining replication lag as the cost of read replicas — a read may not see a just-written value — proves you understand the trade-off, not just the term.

Interview note: Trap: "why is sharding hard?" Cross-shard joins and distributed transactions are painful; choosing a shard key that avoids hotspots and cross-shard queries is the real challenge.

Q4. Explain the ACID properties as they play out in real transactions.

Atomicity: all-or-nothing commits. Consistency: constraints hold across the transaction. Isolation: concurrent transactions don't corrupt each other, tuned by isolation level. Durability: committed writes survive a crash. In practice you trade isolation strength against concurrency.

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;   -- atomic: both or neither; durable once committed

The experienced depth is the isolation trade-off: SERIALIZABLE is safest but costs concurrency and may force retries, so most systems run READ COMMITTED or REPEATABLE READ and add explicit locking (SELECT ... FOR UPDATE) only where a specific race demands it. Naming that per-use-case tuning is the senior answer.

Interview note: Follow-up: "how is durability actually achieved?" Write-ahead logging — the commit isn't acknowledged until the log is safely on disk, so a crash can replay it.

Q5. How do you enforce data integrity across services?

Within one database, use constraints and transactions. Across services with separate databases, you can't use a distributed transaction cheaply, so you use patterns like the outbox, idempotent operations, and eventual consistency with compensations (a saga).

The key realization to voice: microservices give up cross-database ACID, so integrity becomes a design problem — you make operations idempotent and retryable, and you reconcile asynchronously rather than pretending a two-phase commit will scale.

-- Outbox pattern: write the event in the same local transaction as the data
BEGIN;
  INSERT INTO orders (id, status) VALUES (42, 'PLACED');
  INSERT INTO outbox (topic, payload) VALUES ('order.placed', '{"id":42}');
COMMIT;   -- a separate publisher reliably ships the outbox row

Mentioning the outbox and idempotency shows you've grappled with integrity beyond a single database — a strong experienced-level signal.

Interview note: Trap: "why not a distributed transaction (2PC) across services?" It's slow, ties services together, and blocks on coordinator failure. Eventual consistency with sagas scales far better.

Q6. How do you refactor a tangled, unreadable query?

Decompose it into named CTEs, one clear step each, name the intermediate results meaningfully, verify each stage independently, then confirm the execution plan didn't regress. CTEs make logic readable top-to-bottom without changing results.

WITH paid_orders AS (
  SELECT * FROM orders WHERE status = 'PAID'
),
customer_totals AS (
  SELECT customer_id, SUM(amount) AS total FROM paid_orders GROUP BY customer_id
)
SELECT c.name, t.total
FROM customer_totals t JOIN customers c ON c.id = t.customer_id
WHERE t.total > 10000;

The maturity note is checking the plan afterward: readability is the goal, but you verify the CTE decomposition didn't cause a materialization that hurt performance. Owning both clarity and speed is the point.

Interview note: Follow-up: "do CTEs hurt performance?" Modern engines mostly inline them; Postgres inlines non-recursive CTEs since v12. Assuming they're always materialized is outdated — check the plan.

Q7. How do you monitor and own query performance in production?

Track slow-query logs and per-query statistics (pg_stat_statements, the slow query log, Query Store), watch for plan regressions after data growth or deploys, keep statistics fresh, and set alerts on latency — treating query performance as an ongoing responsibility, not a one-time fix.

-- PostgreSQL: find the most expensive statements
SELECT query, calls, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

The ownership framing matters: a query that was fast at 1M rows can degrade at 100M as the plan tips over. Experienced engineers monitor for that drift and re-tune, rather than assuming a once-fast query stays fast.

Interview note: Trap: "a query got slow overnight with no code change — why?" Data growth crossing a threshold, stale statistics, or a plan flip from parameter sniffing. Investigate the plan and stats first.

Q8. What SQL anti-patterns do you catch in code review?

SELECT * in application code, non-sargable predicates (functions on indexed columns), NOT IN with nullable subqueries, N+1 query loops, missing indexes on foreign keys, and business logic scattered between triggers and the app. Catching these is part of owning a codebase's data layer.

-- Anti-pattern: NOT IN with a nullable subquery can return zero rows
-- SELECT * FROM employees WHERE id NOT IN (SELECT manager_id FROM employees);

-- Reviewed fix: NULL-safe
SELECT e.* FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM employees m WHERE m.manager_id = e.id);

Listing anti-patterns with the reason each is harmful — and the corrected version — demonstrates the mentoring dimension interviewers look for in experienced hires. It shows you raise the whole team's SQL quality, not just your own.

Interview note: Follow-up: "why is SELECT * a problem?" It fetches unneeded columns (more I/O and network), breaks when the schema changes, and prevents covering-index-only reads. Name the exact columns.

How to prepare

Practise designing a schema out loud from a one-line feature description — narrate the entities, keys, constraints, then where you'd denormalize and why. That single exercise rehearses the modeling, integrity and trade-off questions that dominate this level. Then take a real gnarly query and refactor it into CTEs, checking the plan before and after, so you can speak to both readability and performance ownership.

Because interviewers may push you toward deeper operational detail, pair this with the SQL questions for five years of experience for the execution-plan and concurrency depth, and revisit the normalization questions, which underpin every schema-design answer. The SQL learning path grounds the relational and transactional models these decisions rest on. A mock interview built around an open-ended design prompt is the best rehearsal for the experienced SQL round.