DatabasesJunior Developer to Senior Backend / Data Engineer17 min read·Updated for PostgreSQL 17 & MySQL 8.4 Optimizer Behavior

SQL Interview Questions & Preparation Guide: Beginner to Advanced

From JOIN Logic and B-Tree Indexing to Window Functions, Isolation Levels & Real Execution Plans

Priya Nataraj

Staff Data Engineer & Query Performance Consultant

11+ Years Tuning OLTP & Analytical Workloads

Prep Timeline

3 to 5 Weeks

Format

SQL Screen, Take-Home Query Round, System/Schema Design

Conversion

+72% SQL Round Pass Rate

SQL Interview Questions & Preparation Guide: Beginner to Advanced

Executive Summary & Key Takeaways

What You Must Master to Clear This Track

Treat every JOIN as a Cartesian product filtered by a predicate, not a magical merge — this mental model prevents 90% of join bugs.
Learn to read an EXPLAIN / EXPLAIN ANALYZE plan before memorizing index syntax; the plan tells you whether your index is even being used.
Normalize for write integrity, denormalize deliberately for read latency — know why you are doing each, not just how.
Window functions and CTEs replace most self-joins and correlated subqueries; interviewers use them to test whether you write set-based or row-by-row SQL.
Isolation levels are a trade-off between anomaly prevention (dirty/non-repeatable/phantom reads) and concurrency throughput — be ready to name the default for Postgres, MySQL/InnoDB, and SQL Server.

Structured Preparation Timeline

Step-by-Step Study Plan

Follow this sequential roadmap designed to take you from core foundations to advanced architecture and mock interviews.

Phase 1 (Weeks 1-2)

Relational Fundamentals & Query Writing Speed

Core SQL Fluency: Joins, Set Operations & Aggregation

INNER/LEFT/RIGHT/FULL OUTER/CROSS/SELF joins, UNION vs UNION ALL vs INTERSECT/EXCEPT, GROUP BY/HAVING, and NULL-handling semantics.

Key Milestones

•Write every join type from scratch on a 3-table schema without looking up syntax.
•Explain why WHERE filters rows before grouping while HAVING filters after aggregation.
•Master NULL logic: NULL = NULL evaluates to UNKNOWN, not TRUE, and breaks naive equality filters.

Recommended Actions

•Practice on a schema with at least 4 related tables (orders, customers, products, order_items) rather than toy 2-column tables.
•Time yourself: a Medium join+aggregation query should take under 4 minutes to write correctly.

Phase 2 (Weeks 3-4)

Reading the Optimizer's Mind

Indexing, Execution Plans & Schema Design

B-Tree index internals, composite index column ordering, covering indexes, index seek vs scan, EXPLAIN/EXPLAIN ANALYZE, and normalization (1NF-BCNF) vs denormalization trade-offs.

Key Milestones

•Run EXPLAIN ANALYZE on 10 of your own queries and identify at least 3 sequential scans that should be index scans.
•Design a composite index and justify column order using selectivity and the leftmost-prefix rule.
•Normalize a messy spreadsheet-style table to 3NF, then explain one realistic case for deliberately denormalizing it back.

Recommended Actions

•Install a local Postgres or MySQL instance and generate a synthetic table with 1M+ rows to see plan changes at scale.
•Practice explaining the cost difference between a Nested Loop Join, Hash Join, and Merge Join out loud.

Phase 3 (Weeks 5-6)

Advanced Analytical SQL & Concurrency Correctness

Window Functions, CTEs, Transactions & Mock Drills

ROW_NUMBER/RANK/LAG/LEAD, recursive CTEs, ACID properties, isolation levels and their anomalies, optimistic vs pessimistic locking, and SQL vs NoSQL trade-off framing.

Key Milestones

•Solve 'top-N per group', 'running total', and 'gaps and islands' problems using window functions without a self-join.
•Write a recursive CTE to walk an employee-manager hierarchy or a category tree.
•Articulate the four transaction isolation levels and name which anomaly each one still permits.

Recommended Actions

•Do at least 3 timed mock SQL screens (45 minutes, schema + 4-5 progressively harder queries).
•Rehearse a 90-second answer to 'when would you reach for NoSQL instead of a relational database?'

Deep-Dive Architecture & Concepts

1. Joins & Set Operations: The Foundation Interviewers Test First

Nearly every SQL interview opens with a multi-table join question, because it reveals whether you think in sets or in loops. Get the mental model right before chasing edge cases.

INNER JOIN

Returns only rows where the join predicate matches on both sides. Conceptually: Cartesian product of both tables, filtered down to matching rows.

LEFT / RIGHT OUTER JOIN

Keeps every row from the preserved side even without a match; unmatched columns from the other side return NULL. Interviewers probe whether you know LEFT JOIN + WHERE right.col IS NULL is the classic 'find rows with no match' pattern.

FULL OUTER JOIN & CROSS JOIN

FULL OUTER keeps unmatched rows from both sides (MySQL lacks native support; emulate with LEFT JOIN UNION RIGHT JOIN). CROSS JOIN produces the full Cartesian product with no predicate — used deliberately for generating combinations, dangerously by accident when a join condition is forgotten.

SELF JOIN

A table joined to itself via aliases, used for hierarchical comparisons (employee vs manager) or finding duplicate/adjacent rows within the same entity set.

UNION vs UNION ALL vs INTERSECT/EXCEPT

UNION de-duplicates and sorts internally (costlier); UNION ALL preserves duplicates and is nearly free. INTERSECT returns rows common to both queries, EXCEPT (MINUS in Oracle) returns rows in the first query absent from the second.

Self Join: Employees Who Earn More Than Their Manager

sql

SELECT
      e.employee_id,
      e.name        AS employee_name,
      e.salary      AS employee_salary,
      m.name        AS manager_name,
      m.salary      AS manager_salary
  FROM employees e
  JOIN employees m
      ON e.manager_id = m.employee_id
  WHERE e.salary > m.salary
  ORDER BY (e.salary - m.salary) DESC;

Why it matters: The employees table is aliased twice (e for the employee row, m for the manager row) and joined on manager_id = employee_id. This is the canonical self-join interview question — the same rows, two roles, one predicate.

Interviewer Insights & Pro Tips

State the join type out loud before writing SQL: 'I need every customer even if they have zero orders, so this is a LEFT JOIN from customers to orders.'
When a query returns more rows than expected, suspect an unintended fan-out from a one-to-many join — check cardinality on both sides before adding DISTINCT as a band-aid.

Red Flags & Common Pitfalls

Filtering a LEFT JOIN's right-side table in the WHERE clause instead of the ON clause silently turns it back into an INNER JOIN.
Forgetting that UNION (not UNION ALL) requires matching column count and compatible types across both SELECTs, and pays a sort/dedup cost on every call.

Deep-Dive Architecture & Concepts

2. Indexing Internals, B-Trees & Reading Execution Plans

Knowing index syntax is not the same as knowing when the optimizer will actually use one. This is the section that separates candidates who've memorized CREATE INDEX from candidates who've actually debugged a slow query in production.

B-Tree Index Structure

A balanced tree of sorted keys where each leaf node points to a row (clustered) or a row locator (non-clustered/secondary). Lookups, range scans, and ORDER BY on the indexed column all cost O(log N) to reach the first matching leaf, then a linear scan across leaves.

Composite Index Column Order

A composite index on (a, b, c) only helps queries filtering on 'a', or 'a AND b', or 'a AND b AND c' — the leftmost-prefix rule. Put the highest-selectivity equality column first, range-filtered columns last.

Covering Indexes

An index that contains every column a query needs (via key columns or INCLUDE/covered columns) lets the optimizer answer entirely from the index without touching the heap/table — visible in a plan as 'Index Only Scan'.

Index Seek vs Scan

A seek uses the B-Tree to jump directly to matching rows (cheap, selective). A scan walks the entire index or table (cheap only when most rows qualify). The optimizer chooses based on cardinality estimates from table statistics/histograms — stale statistics are a common real-world cause of a 'wrong' plan.

Query Execution Pipeline: From SQL Text to Result Set

What actually happens between hitting Enter on a query and rows coming back, and where an index changes the path.

Parse & Rewrite

SQL text is tokenized, validated against the schema catalog, and views/rules are expanded into their underlying query trees.

Query Optimizer

A cost-based optimizer enumerates candidate plans (join orders, access paths) using table statistics and histograms, estimating I/O and CPU cost for each.

Access Path Selection

For each table, the optimizer picks an Index Seek/Range Scan (selective predicate, matching leftmost-prefix index) or a Sequential Scan (low selectivity, missing/unusable index).

Execution Engine

The chosen plan runs as a tree of iterator operators (Nested Loop, Hash Join, Merge Join) pulling rows page-by-page through the buffer pool/cache.

Result Streaming & Plan Cache

Rows stream back to the client as they're produced; the compiled plan is cached keyed by query shape so future executions can skip re-optimization.

Non-Sargable Query vs Sargable Rewrite

sql

-- Non-sargable: wrapping the indexed column in a function
  -- forces a full scan because the optimizer can't use the
  -- index's sorted order to seek.
  SELECT * FROM orders
  WHERE YEAR(order_date) = 2026;
  
  -- Sargable rewrite: the column stays bare, so an index
  -- on order_date supports an Index Range Scan.
  SELECT * FROM orders
  WHERE order_date >= '2026-01-01'
    AND order_date <  '2027-01-01';

Why it matters: EXPLAIN on the first query shows a full table scan even with an index on order_date, because YEAR(order_date) must be evaluated per row before comparison. The rewrite keeps the column bare on one side of the predicate, letting the optimizer perform an index range scan directly.

Interviewer Insights & Pro Tips

Always run EXPLAIN ANALYZE (not just EXPLAIN) when tuning — EXPLAIN only shows the estimated plan, ANALYZE actually executes it and shows real row counts and timing, exposing bad cardinality estimates.
An index doesn't always help: on a small table, or when a predicate matches most rows, a sequential scan can genuinely be cheaper than an index seek plus row lookups.

Red Flags & Common Pitfalls

Adding an index on every column 'just in case' — each index slows down every INSERT/UPDATE/DELETE and consumes storage/cache.
Building a composite index with the low-selectivity or range column first, making the index unusable for the equality-filtered queries it was meant to serve.

Deep-Dive Architecture & Concepts

3. Normalization vs Denormalization & Schema Design

Interviewers use schema design questions to see if you can reason about trade-offs, not recite normal-form definitions. Know the rule, then know exactly when to deliberately break it.

1NF -> 2NF -> 3NF Progression

1NF: atomic values, no repeating groups. 2NF: 1NF plus every non-key column depends on the whole primary key (relevant for composite keys). 3NF: 2NF plus no transitive dependency — non-key columns depend only on the key, not on other non-key columns.

BCNF (Boyce-Codd Normal Form)

A stricter 3NF: for every functional dependency X -> Y, X must be a superkey. Fixes rare anomalies that survive 3NF when a table has multiple overlapping candidate keys.

Why Denormalize Deliberately

Read-heavy analytical/reporting workloads (star schemas, dashboards) trade write-time redundancy and update anomalies for fewer joins and faster reads — e.g. storing a customer_name snapshot on an order row instead of always joining customers.

Star Schema for Analytics

A central fact table (measurable events, e.g. sales) surrounded by denormalized dimension tables (date, product, customer) — optimized for aggregation-heavy OLAP queries rather than transactional integrity.

Interviewer Insights & Pro Tips

Frame your answer as 'normalize the system of record (OLTP), denormalize the read/reporting layer (OLAP or a materialized view)' rather than picking one universally.
Mention materialized views as a middle ground: get denormalized read speed while a background refresh keeps a normalized source of truth authoritative.

Red Flags & Common Pitfalls

Over-normalizing a reporting schema to 5+ joins per dashboard query, then trying to fix the resulting latency with caching instead of a denormalized read model.
Denormalizing a frequently-updated field (like a running balance) without a clear reconciliation strategy, causing silent data drift.

Deep-Dive Architecture & Concepts

4. Window Functions, CTEs & Transaction Isolation

This section covers the two areas that most reliably separate mid-level from senior candidates: writing set-based analytical SQL instead of procedural loops, and reasoning correctly about concurrent transactions.

ROW_NUMBER, RANK, DENSE_RANK

All three number rows within a PARTITION BY group ordered by an expression. ROW_NUMBER never ties; RANK leaves gaps after ties (1,2,2,4); DENSE_RANK doesn't (1,2,2,3).

LAG / LEAD & Running Aggregates

LAG/LEAD access a prior/next row's value without a self-join — ideal for period-over-period comparisons. SUM()/AVG() OVER (ORDER BY ...) compute running totals/moving averages within a window frame.

Common Table Expressions (CTEs)

WITH clauses that name a subquery for readability and reuse within one statement. Recursive CTEs (WITH RECURSIVE) walk hierarchical or graph-like data such as org charts or bill-of-materials trees.

ACID & Isolation Levels

Atomicity, Consistency, Isolation, Durability. Isolation level controls how much concurrent transactions can see of each other's uncommitted or in-flight changes — a direct trade-off against throughput.

Isolation Level Trade-Off: Anomalies Prevented vs Concurrency Cost

Moving from Read Uncommitted to Serializable removes anomalies but increases locking/validation overhead at each step.

Read Uncommitted

No isolation guarantee — transactions can see other transactions' uncommitted writes (dirty reads). Highest throughput, rarely used in practice.

Read Committed

Only committed data is visible, but re-reading the same row twice in one transaction can return different values (non-repeatable reads). Default in PostgreSQL and Oracle.

Repeatable Read

A transaction sees a consistent snapshot of rows it has already read, but new rows matching a range predicate can still appear (phantom reads). Default in MySQL/InnoDB (which also blocks most phantoms via gap locks).

Serializable

Transactions behave as if executed one-at-a-time in some serial order, eliminating dirty/non-repeatable/phantom reads via range locks or serialization-failure aborts — lowest concurrency, used for financial-grade correctness.

Top-2 Highest Paid Employees Per Department (Window Function, No Self-Join)

sql

WITH ranked_employees AS (
      SELECT
          employee_id,
          department_id,
          name,
          salary,
          DENSE_RANK() OVER (
              PARTITION BY department_id
              ORDER BY salary DESC
          ) AS salary_rank
      FROM employees
  )
  SELECT employee_id, department_id, name, salary
  FROM ranked_employees
  WHERE salary_rank <= 2
  ORDER BY department_id, salary_rank;

Why it matters: The CTE computes a DENSE_RANK() partitioned per department, so ties share a rank without skipping the next one. Filtering the outer query on salary_rank <= 2 replaces what would otherwise require a correlated subquery or self-join with an O(N log N) sort-based window pass.

Interviewer Insights & Pro Tips

When asked to explain isolation levels, anchor your answer in the anomaly table (dirty/non-repeatable/phantom) rather than reciting definitions — interviewers are checking if you can reason about what actually goes wrong.
Mention MVCC (Multi-Version Concurrency Control): Postgres and InnoDB avoid read locks entirely by having readers see a consistent snapshot version of each row instead of blocking on writers.

Red Flags & Common Pitfalls

Using a window function's result directly in the same SELECT's WHERE clause — window functions evaluate after WHERE/GROUP BY, so you must wrap them in a CTE or subquery to filter on them.
Assuming Repeatable Read fully prevents phantom reads across all databases — it depends on the engine's specific implementation (InnoDB's next-key/gap locks vs the SQL standard's minimum guarantee).

Real-World Example

Cutting Checkout API Latency at a Mid-Size E-Commerce Platform

A backend team at a growing e-commerce company noticed checkout API p95 latency had crept up to 1.2 seconds as their orders table crossed 40 million rows, directly hurting cart-abandonment metrics during a peak sales campaign.

1Ran EXPLAIN ANALYZE on the checkout order-history query and found a sequential scan despite an existing index on customer_id, caused by the query wrapping order_date in a DATE() function.
2Rewrote the predicate to a sargable range filter (order_date >= ? AND order_date < ?) so the existing composite index on (customer_id, order_date) could be used directly.
3Discovered the order_items join was triggering an N+1 pattern from the ORM's lazy loading; replaced it with a single batched query using JOIN plus a covering index on order_items(order_id) including quantity and price.
4Switched the order-history pagination from OFFSET-based paging to keyset pagination on order_id, since customer support dashboards were requesting deep pages during peak load.
5Validated the fix under production-like load in staging with pgbench before rolling out behind a feature flag to 10% of traffic, then 100%.

Outcome: Checkout API p95 latency dropped from 1.2s to 95ms and database CPU utilization during peak sales windows fell by roughly 40%.

Real-World Interview Questions

Top Must-Know Interview Questions & Model Answers

Joins & Set OperationsMust-Know

Q1: What is the difference between INNER JOIN and LEFT OUTER JOIN, and when would unmatched rows appear as NULL?

Executive Answer:INNER JOIN returns only rows with a match on both sides; LEFT OUTER JOIN keeps every row from the left table and fills unmatched right-side columns with NULL.

Deep Dive Analysis:

•Conceptually both start from the same Cartesian product filtered by the ON predicate; LEFT JOIN then re-adds any left-side row that had zero matches, padding the right side with NULLs.
•This makes LEFT JOIN ... WHERE right.key IS NULL the standard pattern for 'find records with no corresponding entry' (e.g. customers with no orders).

Interviewer Takeaway: If a filter condition on the right-hand table is placed in WHERE instead of ON, it silently converts a LEFT JOIN back into an INNER JOIN by discarding the NULL-padded rows.

Joins & Set OperationsMedium

Q2: How would you find duplicate rows in a table and delete all but one copy of each?

Executive Answer:Use ROW_NUMBER() partitioned by the duplicate-defining columns to tag one 'keeper' row per group, then delete every row where the row number is greater than 1.

Deep Dive Analysis:

•WITH ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) rn FROM users) DELETE FROM users WHERE id IN (SELECT id FROM ranked WHERE rn > 1).
•A self-join alternative (DELETE a FROM users a JOIN users b ON a.email = b.email AND a.id > b.id) works too but is less readable and easier to get wrong on which side survives.

Interviewer Takeaway: ROW_NUMBER() PARTITION BY is the modern, safer replacement for ad-hoc self-join deduplication logic.

Joins & Set OperationsMedium

Q3: What is a CROSS JOIN and what is a real, intentional use case for it?

Executive Answer:A CROSS JOIN produces the full Cartesian product of two tables with no join predicate — every row of the first table paired with every row of the second.

Deep Dive Analysis:

•Intentional use: generating a date-dimension table crossed with a store list to produce one row per store per day, even for days with zero sales, for reporting completeness.
•Accidental use (a bug): forgetting the ON/USING predicate in a join, which silently turns an intended INNER JOIN into a row-count explosion.

Interviewer Takeaway: If a join's result set is unexpectedly huge, check first for a missing or mistyped join predicate before assuming a data problem.

Indexing InternalsMust-Know

Q4: Explain how a B-Tree index actually speeds up a lookup, and why it also helps with range queries and ORDER BY.

Executive Answer:A B-Tree stores keys in sorted order across a balanced tree of pages, so an equality lookup descends in O(log N) hops, and because leaves are linked in sorted order, range scans and ORDER BY on the same column can be satisfied by walking leaves sequentially instead of sorting after the fact.

Deep Dive Analysis:

•Internal (non-leaf) pages hold routing keys used only to decide which child page to descend into; leaf pages hold the actual key plus a row pointer (or the row itself, for a clustered index).
•Because leaves are doubly-linked in key order, a range predicate (BETWEEN, >, <) or an ORDER BY on the indexed column can be answered by seeking to the start and scanning forward, avoiding an explicit sort step.

Interviewer Takeaway: A B-Tree's value comes from ordering, not just fast lookup — that's why it also accelerates ORDER BY and range filters, unlike a pure hash index.

Indexing InternalsHard

Q5: You created an index on a column but the query still does a full table scan. What are the possible reasons?

Executive Answer:Common causes are a non-sargable predicate (function/cast wrapping the column), stale statistics leading the optimizer to underestimate selectivity, the query matching most of the table (scan is genuinely cheaper), or the column being the second part of a composite index without the first column filtered.

Deep Dive Analysis:

•Non-sargable predicates like WHERE UPPER(email) = 'X' or WHERE YEAR(order_date) = 2026 prevent the optimizer from using the index's sort order at all.
•If a composite index is (status, created_at) but the query only filters created_at, the leftmost-prefix rule blocks its use entirely.
•Run EXPLAIN ANALYZE and compare estimated vs actual row counts — a large gap usually means outdated table statistics; running ANALYZE/UPDATE STATISTICS often fixes the plan choice.

Interviewer Takeaway: Always diagnose with EXPLAIN ANALYZE before assuming the index is 'broken' — the optimizer's decision is usually explainable from the plan and statistics.

Indexing InternalsHard

Q6: What is a covering index and how does it show up differently in an execution plan?

Executive Answer:A covering index contains every column a query needs (in its key or included columns), letting the engine answer entirely from the index without a lookup back to the table — shown as an 'Index Only Scan' instead of an 'Index Scan' plus heap fetch.

Deep Dive Analysis:

•Without covering, a non-clustered index seek finds matching row pointers, then performs a separate lookup (bookmark lookup / heap fetch) per row to retrieve the remaining SELECTed columns.
•Adding the needed columns via INCLUDE (SQL Server/Postgres) or extending the composite key avoids that second lookup entirely, cutting I/O substantially on wide result sets.

Interviewer Takeaway: If a hot query's WHERE and SELECT columns are known and stable, a covering index is one of the highest-leverage single tuning changes available.

Indexing InternalsMedium

Q7: How should you decide the column order in a composite index?

Executive Answer:Put equality-filtered, high-selectivity columns first (leftmost), and range-filtered or sort columns last, following the leftmost-prefix rule so the index remains usable for the broadest set of real query patterns.

Deep Dive Analysis:

•An index on (a, b, c) supports predicates on 'a', 'a AND b', and 'a AND b AND c', but not 'b' or 'c' alone.
•If a query does WHERE status = ? ORDER BY created_at, an index on (status, created_at) lets the engine seek on status and return already-sorted rows for created_at without an extra sort step.

Interviewer Takeaway: Equality columns go left, range/sort columns go right — get this backwards and the index silently becomes unusable for its intended query.

Indexing InternalsMedium

Q8: What is the difference between a clustered and a non-clustered index?

Executive Answer:A clustered index determines the physical storage order of the table's rows (only one per table), while a non-clustered (secondary) index is a separate structure of sorted keys pointing back to the row's location.

Deep Dive Analysis:

•In InnoDB, the primary key is always the clustered index, and every secondary index stores the primary key value as its row pointer — a very wide primary key bloats every secondary index.
•In SQL Server, a table can exist as a heap (no clustered index) with only non-clustered indexes, each pointing to a row ID instead of a key value.

Interviewer Takeaway: Choosing a small, stable, sequential clustered/primary key (like an auto-increment ID) keeps every secondary index compact and insert-friendly.

Normalization & Schema DesignMust-Know

Q9: Normalize this flat table to Third Normal Form: orders(order_id, customer_name, customer_email, product_name, product_price, quantity).

Executive Answer:Split into customers(customer_id, name, email), products(product_id, name, price), and orders/order_items(order_id, customer_id, product_id, quantity) so every non-key attribute depends only on its own table's primary key.

Deep Dive Analysis:

•In the flat version, customer_email depends only on customer_name (not on order_id) — a transitive dependency that violates 3NF and causes update anomalies (changing an email requires updating every order row for that customer).
•Separating into customers and products removes redundant repetition and lets each fact be updated in exactly one place.

Interviewer Takeaway: If two non-key columns determine each other (customer_name -> customer_email) independent of the primary key, that's a transitive dependency and a 3NF violation.

Normalization & Schema DesignHard

Q10: When would you deliberately denormalize a schema, and what risk are you accepting?

Executive Answer:Denormalize when read latency or join cost dominates (reporting dashboards, high-traffic read paths, event/analytics pipelines), accepting the risk of update anomalies and data drift between the redundant copies.

Deep Dive Analysis:

•Example: storing a snapshot of shipping_address on an orders row instead of always joining to the customer's current address — necessary anyway, since a customer's address may change after the order shipped.
•Mitigate drift risk with materialized views, scheduled ETL/reconciliation jobs, or event-driven cache invalidation rather than manual dual-writes.

Interviewer Takeaway: Denormalization should be a documented, deliberate trade-off with a stated reconciliation strategy — not an ad-hoc shortcut taken under deadline pressure.

Normalization & Schema DesignMedium

Q11: What is the difference between a candidate key, a primary key, and a foreign key?

Executive Answer:A candidate key is any column set that could uniquely identify a row; the primary key is the one candidate key chosen as the table's main identifier; a foreign key references another table's primary (or unique) key to enforce referential integrity.

Deep Dive Analysis:

•A table can have multiple candidate keys (e.g. both employee_id and email uniquely identify an employee) but only one is designated primary — the others become unique constraints.
•Foreign keys don't have to reference a table's primary key; they must reference a column with a UNIQUE or PRIMARY KEY constraint.

Interviewer Takeaway: A foreign key enforces existence (the referenced row must exist), not correctness of the business logic — it's a data integrity guard, not a validation engine.

Window Functions & CTEsMedium

Q12: Write a query to return each employee's salary along with their department's average salary, without collapsing individual rows.

Executive Answer:Use AVG(salary) OVER (PARTITION BY department_id) so the average is computed per department but every individual employee row is preserved, unlike a GROUP BY which would collapse rows.

Deep Dive Analysis:

•SELECT employee_id, department_id, salary, AVG(salary) OVER (PARTITION BY department_id) AS dept_avg_salary FROM employees.
•A GROUP BY approach would require a self-join back to the original table to re-attach per-employee detail — the window function avoids that extra join entirely.

Interviewer Takeaway: Reach for a window function whenever you need an aggregate value alongside (not instead of) each individual row.

Window Functions & CTEsMust-Know

Q13: Explain the difference between RANK(), DENSE_RANK(), and ROW_NUMBER().

Executive Answer:ROW_NUMBER() assigns a strictly increasing unique number with no ties; RANK() gives tied rows the same rank but skips the next number(s) (1,2,2,4); DENSE_RANK() gives tied rows the same rank without skipping (1,2,2,3).

Deep Dive Analysis:

•The choice matters for 'top N per group' queries: using RANK() with ties can return more than N rows for a group, while ROW_NUMBER() guarantees exactly N by construction.
•DENSE_RANK() is typically the right choice for 'top N distinct values' style questions (e.g. top 3 salary tiers, even if multiple employees share a tier).

Interviewer Takeaway: Pick ROW_NUMBER() when you need an exact row cap, RANK()/DENSE_RANK() when ties should be semantically preserved.

Window Functions & CTEsHard

Q14: How do you write a recursive CTE to find all subordinates under a given manager in an employee hierarchy?

Executive Answer:Define an anchor member selecting the starting manager, then a recursive member joining employees back to the growing result set on manager_id, unioned with UNION ALL until no more rows are produced.

Deep Dive Analysis:

•WITH RECURSIVE subordinates AS (SELECT employee_id, manager_id FROM employees WHERE employee_id = :start_id UNION ALL SELECT e.employee_id, e.manager_id FROM employees e JOIN subordinates s ON e.manager_id = s.employee_id) SELECT * FROM subordinates.
•UNION ALL (not UNION) is required for recursive CTEs in most engines' base syntax, and a depth/cycle guard is wise on real-world data that might contain a manager_id cycle.

Interviewer Takeaway: Recursive CTEs replace application-side tree-walking loops for hierarchical/graph-shaped data stored in a single self-referencing table.

Window Functions & CTEsMedium

Q15: What does the SQL clause logical execution order tell you about why you can't use a SELECT alias in a WHERE clause?

Executive Answer:SQL clauses execute logically in the order FROM/JOIN, WHERE, GROUP BY, HAVING, SELECT, ORDER BY — since WHERE runs before SELECT, an alias defined in SELECT doesn't exist yet when WHERE is evaluated.

Deep Dive Analysis:

•This is also why window functions (computed during a phase after WHERE/GROUP BY, alongside SELECT) can't be referenced in the same query's WHERE — you need a wrapping CTE or subquery.
•HAVING runs after GROUP BY specifically to allow filtering on aggregate results, which don't exist yet at WHERE time.

Interviewer Takeaway: Memorizing the logical execution order resolves most 'why doesn't this alias/filter work' confusion in one shot.

Transactions & IsolationMust-Know

Q16: List the ACID properties and give a concrete example of what breaks if each one is violated.

Executive Answer:Atomicity (all-or-nothing execution), Consistency (valid-state-to-valid-state per constraints), Isolation (concurrent transactions don't corrupt each other's view), Durability (committed data survives a crash).

Deep Dive Analysis:

•Broken atomicity: a funds transfer debits one account but a crash before the matching credit leaves money vanished.
•Broken isolation: two concurrent transactions both read a stale inventory count and both decrement it, overselling stock (a lost update).
•Broken durability: a commit is acknowledged to the client but a power failure before the write is flushed to disk loses the transaction.

Interviewer Takeaway: Each ACID letter maps to a distinct, nameable failure mode — interviewers want the concrete example, not just the acronym expansion.

Transactions & IsolationMust-Know

Q17: Name the three classic transaction anomalies (dirty read, non-repeatable read, phantom read) and which isolation level first prevents each.

Executive Answer:Dirty read (seeing another transaction's uncommitted write) is prevented starting at Read Committed. Non-repeatable read (a re-read of the same row returns a different value) is prevented starting at Repeatable Read. Phantom read (a re-run range query returns new rows) is prevented starting at Serializable (Repeatable Read prevents it in some engines like InnoDB, but not by the SQL standard's guarantee).

Deep Dive Analysis:

•Dirty read example: Transaction A updates a balance but hasn't committed; Transaction B reads the updated (uncommitted) balance, then A rolls back, leaving B's view false.
•Phantom read example: Transaction A runs 'SELECT COUNT(*) WHERE status = pending' twice; between the two reads, Transaction B inserts a new pending row, changing A's second count.

Interviewer Takeaway: Build the anomaly-to-isolation-level mapping as a mental table — it's one of the single most frequently asked SQL interview questions.

Transactions & IsolationHard

Q18: What is MVCC (Multi-Version Concurrency Control) and how does it let readers avoid blocking on writers?

Executive Answer:MVCC keeps multiple versions of each row (tagged with transaction/commit IDs) so a reader can be handed a consistent snapshot as of its transaction's start time, without needing a read lock that would block concurrent writers.

Deep Dive Analysis:

•PostgreSQL stores old row versions as tuples until vacuumed; InnoDB reconstructs older versions on demand from the undo log.
•This is why 'readers never block writers, writers never block readers' holds under MVCC-based Read Committed/Repeatable Read, unlike classic two-phase locking schemes.

Interviewer Takeaway: MVCC trades storage/cleanup overhead (dead tuples, undo log growth) for dramatically better read/write concurrency than lock-based isolation.

Transactions & IsolationMedium

Q19: What is the difference between optimistic and pessimistic locking, and when would you choose each?

Executive Answer:Pessimistic locking acquires a lock (e.g. SELECT ... FOR UPDATE) before modifying a row, blocking other transactions until it's released; optimistic locking allows concurrent reads/writes and detects conflicts at commit time via a version/timestamp column, retrying on conflict.

Deep Dive Analysis:

•Pessimistic locking suits high-contention hotspots (e.g. a single popular inventory row under flash-sale load) where retries would be wasteful.
•Optimistic locking suits low-contention scenarios (most user profile edits) where acquiring a lock for every read would tank throughput for conflicts that rarely actually happen.

Interviewer Takeaway: Pessimistic locking pays cost upfront to avoid conflicts; optimistic locking pays cost only when a conflict actually occurs — pick based on real contention rate, not intuition.

Transactions & IsolationMedium

Q20: What is a deadlock in a database, and how does the engine typically resolve it?

Executive Answer:A deadlock occurs when two or more transactions each hold a lock the other needs, forming a cycle of waits with no possible progress; the database detects the cycle and forcibly rolls back one transaction (the 'victim') to break it.

Deep Dive Analysis:

•Classic example: Transaction A locks row 1 then wants row 2; Transaction B locks row 2 then wants row 1 — neither can proceed.
•Most engines run a periodic deadlock detection graph walk and choose a victim by cost heuristics (e.g. the transaction with the least work done), returning an error the application must retry.

Interviewer Takeaway: Avoid deadlocks proactively by always acquiring locks on multiple rows in a consistent, agreed-upon order across the whole application.

Query OptimizationHard

Q21: How would you paginate a large table efficiently, and why does OFFSET/LIMIT get slower as the offset grows?

Executive Answer:OFFSET/LIMIT forces the engine to scan and discard every row before the offset on each request, making cost grow linearly with page depth; keyset (seek-based) pagination instead filters WHERE id > :last_seen_id ORDER BY id LIMIT n, which stays a fast index seek regardless of depth.

Deep Dive Analysis:

•SELECT * FROM orders ORDER BY id OFFSET 100000 LIMIT 20 must still compute and skip 100,000 rows internally on every call.
•SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20 uses the index to seek directly to the right starting point, independent of how deep into the table that is.

Interviewer Takeaway: Keyset/cursor pagination trades 'jump to arbitrary page N' for consistently fast performance — the right trade-off for infinite-scroll and API pagination at scale.

Query OptimizationMust-Know

Q22: What makes a WHERE clause predicate 'non-sargable', and why does it matter for performance?

Executive Answer:A non-sargable predicate wraps the indexed column in a function, expression, or implicit type conversion (e.g. WHERE UPPER(email) = ? or WHERE price + 10 > ?), preventing the optimizer from using an index seek and forcing it to evaluate the expression per row via a full scan.

Deep Dive Analysis:

•Sargable ('Search ARGument ABLE') means the predicate can be answered directly using an index's stored, sorted key values.
•Fix by keeping the column bare and moving the transformation to the other side: WHERE price > ? - 10 instead of WHERE price + 10 > ?, or storing a normalized/lowercased column with its own index instead of calling UPPER() at query time.

Interviewer Takeaway: Before blaming 'missing indexes,' check whether an existing index is being neutralized by a function wrapped around the filtered column.

Query OptimizationMust-Know

Q23: What is the N+1 query problem and how do you fix it at the SQL/ORM level?

Executive Answer:N+1 happens when code loads N parent rows with one query, then issues one additional query per parent to fetch related child rows, turning what should be 2 queries into N+1; fix it with a JOIN, an ORM eager-load/prefetch directive, or a single batched IN (...) query.

Deep Dive Analysis:

•Example: fetching 50 blog posts, then looping and querying comments for each post individually generates 51 round trips instead of 2.
•Fix with SELECT posts.*, comments.* FROM posts JOIN comments ON ... (one round trip) or a two-step batch: fetch post IDs, then SELECT * FROM comments WHERE post_id IN (...).

Interviewer Takeaway: N+1 is rarely a SQL syntax problem — it's an application/ORM access-pattern problem that shows up as many small, near-identical queries in a query log.

Query OptimizationHard

Q24: How do you use EXPLAIN ANALYZE to diagnose why a query is slow?

Executive Answer:Run EXPLAIN ANALYZE to get both the planner's cost estimate and the actual execution with real timings and row counts, then look for large gaps between estimated and actual rows (stale statistics), unexpected sequential scans on filtered predicates, and the most expensive operator in the tree.

Deep Dive Analysis:

•A big estimated-vs-actual row count mismatch usually means table statistics are stale — running ANALYZE/UPDATE STATISTICS often resolves it without touching the query.
•Look specifically for Nested Loop joins driving a large outer row count into an inner sequential scan — this is one of the most common accidental O(N*M) plan shapes.

Interviewer Takeaway: Never guess at a fix — EXPLAIN ANALYZE's actual vs estimated row counts tell you whether the problem is the query, the index, or the statistics.

Query OptimizationHard

Q25: What is a query execution plan's join algorithm choice (Nested Loop vs Hash Join vs Merge Join), and when does the optimizer pick each?

Executive Answer:Nested Loop suits a small outer set joined to an indexed inner set; Hash Join suits large, unsorted, unindexed sets joined on equality by building an in-memory hash table on the smaller side; Merge Join suits two inputs already sorted on the join key, merging them in one linear pass.

Deep Dive Analysis:

•Nested Loop cost is roughly O(outer_rows * inner_lookup_cost) — cheap only if the outer set is small or the inner side has a great index.
•Hash Join cost is roughly O(build_side + probe_side) but requires enough memory to hold the hash table, spilling to disk if it doesn't fit.

Interviewer Takeaway: A Nested Loop showing up over a large, unindexed table in a plan is a strong signal the optimizer picked a bad plan due to a missing index or bad statistics.

SQL vs NoSQLMust-Know

Q26: When would you choose a NoSQL document or key-value store over a relational database for a given feature?

Executive Answer:Choose NoSQL when the workload needs massive horizontal write scale, a flexible/evolving schema, and simple key-based or document-shaped access patterns over strict multi-row ACID transactions and complex relational joins.

Deep Dive Analysis:

•A relational database remains the right default for financial ledgers, inventory counts, and anything requiring multi-table transactional consistency.
•A document/key-value store fits session storage, product catalogs with wildly varying attributes per category, or write-heavy event/telemetry ingestion where join complexity is low.

Interviewer Takeaway: Polyglot persistence is normal in production systems: relational for the system of record, NoSQL for specific access patterns that genuinely benefit from it — not an either/or architectural religion.

SQL vs NoSQLHard

Q27: How does eventual consistency in a NoSQL store change how you'd design a feature compared to a strongly consistent relational read?

Executive Answer:With eventual consistency, a read shortly after a write may return stale data across replicas, so the application must tolerate or actively design around that window (e.g. read-your-writes routing, version stamps, idempotent retries) rather than assuming the read always reflects the latest write.

Deep Dive Analysis:

•A relational primary-replica setup with synchronous or near-synchronous replication gives strong or bounded-staleness reads by default for many use cases; many NoSQL stores default to tunable consistency (e.g. Cassandra's quorum settings) that requires an explicit choice per query.
•For a shopping cart, eventual consistency is usually fine; for a bank balance check before approving a withdrawal, it typically is not without additional safeguards.

Interviewer Takeaway: Consistency model is a per-feature design decision, not a single blanket property of 'the database' — even within one NoSQL store, tunable consistency lets you dial it per operation.

Aggregation & GroupingMedium

Q28: What is the difference between HAVING and WHERE, and can you give an example where only HAVING works?

Executive Answer:WHERE filters individual rows before grouping/aggregation happens; HAVING filters groups after aggregation, so it's the only clause that can filter on an aggregate result like COUNT(*) or SUM(x).

Deep Dive Analysis:

•SELECT department_id, COUNT(*) FROM employees GROUP BY department_id HAVING COUNT(*) > 10 — this cannot be written with WHERE COUNT(*) > 10, since WHERE runs before aggregates exist.
•Using WHERE to filter rows before grouping when possible (rather than filtering everything then aggregating) is also a performance win, since fewer rows enter the aggregation step.

Interviewer Takeaway: Filter rows early with WHERE whenever the condition doesn't depend on an aggregate; reserve HAVING strictly for aggregate-based filters.

Aggregation & GroupingMedium

Q29: Write a query to find the second-highest salary in a table without using LIMIT/OFFSET, and explain why the naive MAX approach can fail.

Executive Answer:Use SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees) — the naive 'sort and skip one row' approach can return the same value twice if the top salary is tied across multiple employees.

Deep Dive Analysis:

•A window-function alternative: SELECT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) rnk FROM employees) t WHERE rnk = 2 — this correctly treats a tied top salary as rank 1 for all holders, and reports the true next distinct value as rank 2.
•Both approaches avoid the classic pitfall of ORDER BY salary DESC LIMIT 1 OFFSET 1, which returns a wrong or duplicate answer whenever the highest salary is shared by more than one row.

Interviewer Takeaway: Any 'Nth highest/lowest' question should default to DENSE_RANK() to correctly handle ties, rather than assuming every value is unique.

Constraints & KeysMedium

Q30: What is the difference between a UNIQUE constraint and a PRIMARY KEY constraint?

Executive Answer:A PRIMARY KEY uniquely identifies each row, disallows NULLs, and a table can have only one; a UNIQUE constraint also enforces distinct values but allows NULLs (in most engines, multiple NULLs are treated as not equal to each other) and a table can have many.

Deep Dive Analysis:

•In most relational engines a PRIMARY KEY is implemented as a UNIQUE constraint plus a NOT NULL constraint plus (typically) the clustering key.
•A common design pattern: use a surrogate integer/UUID as the primary key for stability, and add a UNIQUE constraint on a natural business key (like email) to still enforce its real-world uniqueness.

Interviewer Takeaway: A table can have exactly one PRIMARY KEY but multiple UNIQUE constraints — use UNIQUE for every additional business rule beyond the chosen row identifier.

Constraints & KeysMedium

Q31: Explain ON DELETE CASCADE versus ON DELETE SET NULL versus ON DELETE RESTRICT for a foreign key.

Executive Answer:CASCADE automatically deletes child rows when the parent is deleted; SET NULL nulls out the child's foreign key column instead of deleting the child row (requires the FK column to be nullable); RESTRICT (or the default NO ACTION) blocks the parent delete entirely while matching child rows exist.

Deep Dive Analysis:

•CASCADE is convenient but dangerous on deeply nested hierarchies — deleting one root row can silently wipe out large downstream subtrees.
•RESTRICT/NO ACTION is the safest default for irreversible data, forcing an explicit decision (archive, reassign, or manually cascade) before allowing the delete.

Interviewer Takeaway: Default to RESTRICT for anything business-critical and reach for CASCADE only where the child truly has no meaning without its parent (e.g. order_items under an order).

Constraints & KeysHard

Q32: How does a database enforce referential integrity, and what happens under the hood when you insert a row with an invalid foreign key?

Executive Answer:The engine checks, as part of the same transaction, that the foreign key value exists in the referenced table's primary/unique key; if it doesn't, the INSERT is rejected with a constraint violation error and the transaction (or statement) is rolled back.

Deep Dive Analysis:

•This check typically uses the referenced table's existing index on that key, so a missing index on the referenced column can make every insert into the child table noticeably slower.
•Some engines defer constraint checking to commit time (deferred constraints), useful for inserting mutually-referencing rows within a single transaction.

Interviewer Takeaway: Referential integrity checks piggyback on indexes — always ensure the referenced column has one, or every insert/update into the child table pays an unindexed lookup cost.

Common Mistakes

Mistakes That Sink Otherwise Strong Candidates

Wrapping an indexed column in a function inside WHERE (e.g. WHERE YEAR(order_date) = 2026).

Why it happens: It reads naturally and works correctly, so it's easy not to notice the optimizer can no longer use the index's sorted order to seek.

The fix: Rewrite as a sargable range predicate on the bare column, or maintain a separate indexed/generated column if the transformation is unavoidable.

Adding an index on every column that appears in any WHERE clause.

Why it happens: It feels like a safe, purely-beneficial optimization since indexes only seem to help reads.

The fix: Every index adds write overhead and storage cost; add indexes based on actual query patterns and EXPLAIN evidence, and periodically audit for unused indexes.

Filtering the outer side of a LEFT JOIN in the WHERE clause instead of the ON clause.

Why it happens: WHERE feels like the 'default' place to put any filter, without realizing it runs after the join and discards the NULL-padded unmatched rows.

The fix: Move filters on the right-hand (nullable) table into the ON clause so unmatched rows are preserved as intended by the LEFT JOIN.

Using OFFSET/LIMIT for deep pagination on a large, frequently-paged table.

Why it happens: It's the simplest pagination syntax to write and works fine in early testing on small datasets.

The fix: Switch to keyset (seek-based) pagination using a WHERE id > :last_id ORDER BY id LIMIT n pattern once tables grow past a few hundred thousand rows.

Assuming NULL = NULL evaluates to TRUE in a WHERE clause.

Why it happens: It matches intuitive equality logic from general-purpose programming languages, but SQL's three-valued logic treats it as UNKNOWN.

The fix: Use IS NULL / IS NOT NULL for null checks, and use IS [NOT] DISTINCT FROM when comparing two columns that might both be null.

Letting an ORM lazily load related rows inside a loop (the N+1 pattern).

Why it happens: The ORM hides the extra queries behind convenient property access, so the problem is invisible until a query log or APM trace is inspected.

The fix: Use eager loading / prefetch directives, or write an explicit JOIN or batched IN (...) query for any list view that touches a related table.

Over-normalizing a reporting/analytics schema to the same degree as the transactional system of record.

Why it happens: 3NF discipline is drilled in as a universal best practice without distinguishing OLTP write-integrity needs from OLAP read-speed needs.

The fix: Keep the OLTP system fully normalized, and build a deliberately denormalized star-schema or materialized view layer specifically for reporting.

Choosing the default transaction isolation level without considering the anomaly it still permits.

Why it happens: Most engines' defaults (Read Committed in Postgres, Repeatable Read in MySQL/InnoDB) work fine most of the time, so the gap only surfaces under real concurrency.

The fix: Explicitly evaluate isolation level per transaction that touches money, inventory, or any invariant that must hold under concurrent access, upgrading to Serializable or adding explicit locking where needed.

Cheat Sheet

Quick-Reference Cheat Sheet

Join Types

INNER JOINOnly rows matching the predicate on both sides

LEFT (OUTER) JOINAll left rows; unmatched right columns become NULL

RIGHT (OUTER) JOINAll right rows; unmatched left columns become NULL

FULL OUTER JOINAll rows from both sides; unmatched columns become NULL

CROSS JOINFull Cartesian product, no predicate

SELF JOINA table joined to itself via two aliases

Transaction Isolation Levels & Anomalies Permitted

Read UncommittedAllows dirty, non-repeatable, and phantom reads

Read CommittedBlocks dirty reads; allows non-repeatable and phantom reads

Repeatable ReadBlocks dirty and non-repeatable reads; phantom reads engine-dependent

SerializableBlocks all three anomalies; lowest concurrency

Index Types

B-TreeDefault general-purpose index; supports equality, range, and sort

Hash IndexO(1) equality lookup only; no range/order support

Composite IndexMulti-column index usable via the leftmost-prefix rule

Covering IndexIncludes all needed columns; enables Index Only Scan

Bitmap IndexEfficient for low-cardinality columns in analytical (OLAP) workloads

Full-Text IndexTokenized/inverted index for text search relevance ranking

SQL Logical Execution Order

1. FROM / JOINResolve source tables and combine rows

2. WHEREFilter individual rows before any grouping

3. GROUP BYAggregate rows into groups

4. HAVINGFilter groups based on aggregate results

5. SELECTCompute output expressions and window functions

6. ORDER BYSort the final result set

7. LIMIT / OFFSETRestrict the number of rows returned

Normal Forms Quick Reference

1NFAtomic column values; no repeating groups

2NF1NF + every non-key column depends on the whole composite key

3NF2NF + no transitive dependency between non-key columns

BCNF3NF + every determinant of a functional dependency is a superkey

Window Function Quick Reference

ROW_NUMBER()Unique sequential number per row, no ties

RANK()Ties share a rank; next rank skips accordingly

DENSE_RANK()Ties share a rank; no gap for the next rank

LAG() / LEAD()Access a prior/next row's value without a self-join

NTILE(n)Splits partition rows into n roughly equal buckets

SUM()/AVG() OVER (...)Running total or moving average within a window frame

Assessment Integration

Recommended Practice Quizzes on QuizCluster

Test your retention and prepare for timed live coding and MCQ technical screening rounds:

310 Quizzes

SQL & NoSQL Engines

Drill joins, indexing, schema design, and relational vs document/key-value trade-offs across real engine behavior.

Start Practice Test

36 Quizzes

SQL Optimization & Transaction Isolation

Practice execution-plan reasoning, index selection, and isolation-level anomaly questions asked in live SQL screens.

Start Practice Test

Frequently Asked Questions

Do I need to memorize exact SQL syntax for every database engine, or just one?

Pick one engine (PostgreSQL or MySQL are the most commonly asked about) and get fluent in its syntax and quirks, but understand the concepts — B-Tree indexing, isolation levels, normalization — at a level that transfers across engines. Interviewers care far more about whether you can reason about a plan than whether you recall vendor-specific keyword spelling.

How deep into query optimization do I need to go for a mid-level backend role versus a data engineering role?

Mid-level backend roles typically expect you to read a basic EXPLAIN output, know when to add an index, and avoid N+1 queries. Data engineering and senior backend roles go further: composite index design, join algorithm trade-offs, statistics/histograms, and diagnosing plans on multi-million-row tables.

Are window functions actually asked in interviews, or is that overkill preparation?

Window functions are now asked routinely, especially for 'top N per group', running totals, and gaps-and-islands problems — they're a strong signal of whether a candidate writes idiomatic set-based SQL instead of falling back to self-joins or application-side loops.

What's the single highest-leverage topic to prioritize if I only have one week to prepare?

Joins plus the anomaly table for transaction isolation levels. Together they cover the majority of 'Must-Know' questions asked across companies, and both have compact, memorizable mental models you can rehearse quickly.