80 SQL Interview Questions and Answers for 2026

Some links on this page are affiliate links: if you buy through them we may earn a commission, at no extra cost to you.

The strongest SQL interview answers do more than produce a query: they state the SQL dialect, define assumptions, handle NULLs and duplicate rows, make ordering deterministic, and explain performance and transaction trade-offs. This guide presents 80 questions in that order, with PostgreSQL-oriented examples unless another dialect is named.

SQL fundamentals (1–10)

1. What is SQL?

SQL is a declarative language for defining, querying, and changing data in relational database systems. You describe the result or change; the optimizer chooses an execution plan.

2. What is a table?

A table is a relation represented as rows and named columns. Each column has a data type and optional constraints; a row represents one record in that relation.

3. What is a primary key?

A primary-key constraint identifies each row uniquely. Its values must be unique and non-NULL. A table has one primary-key constraint, although it can contain several columns (a composite key).

4. What is a foreign key?

A foreign key references a candidate or primary key in another table and enforces relationship integrity. Inserts or updates that would create an invalid reference are rejected unless the constraint is deferred or a referential action applies.

5. What is a candidate key?

A candidate key is any minimal set of columns that uniquely identifies rows. One candidate is chosen as the primary key; other candidates can be protected with UNIQUE.

6. What is a surrogate key?

A surrogate key is a generated identifier with no business meaning, such as an identity integer or UUID. Keep a separate unique constraint for a real-world identifier such as an email address.

7. What does SELECT do?

SELECT projects expressions and columns from a row source. For example, SELECT id, price * quantity AS total FROM order_items; returns a derived result without changing stored data.

8. What does DISTINCT do?

DISTINCT removes duplicate result rows after projection. It is not a repair for an incorrect join: first determine why the join produced extra combinations.

9. What is NULL?

NULL means missing or unknown information; it is neither zero nor an empty string. Comparisons with NULL evaluate to unknown, so use IS NULL, IS NOT NULL, COALESCE, or a dialect’s null-safe operator.

10. What is the logical order of query processing?

The conceptual order is FROM/JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, then LIMIT/OFFSET. Optimizers may execute operations in another physical order while preserving results.

Filtering, sorting and aggregation (11–20)

11. WHERE versus HAVING?

WHERE removes individual rows before grouping; HAVING removes groups after aggregates are calculated. Filtering non-aggregate columns in WHERE usually reduces work.

12. COUNT(*) versus COUNT(column)?

COUNT(*) counts rows, including rows whose columns are NULL. COUNT(column) counts only non-NULL values.

13. How do you count distinct values?

Use COUNT(DISTINCT customer_id). Check the dialect’s treatment of NULL (typically it is not counted) and whether a multi-column distinct form is supported before relying on it.

14. What is conditional aggregation?

It computes several metrics in one grouped query, for example:

SELECT account_id,
       SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid_amount,
       COUNT(*) FILTER (WHERE status = 'failed') AS failed_count
FROM invoices
GROUP BY account_id;

FILTER is PostgreSQL syntax; use SUM(CASE ...) for broader portability.

15. How do ORDER BY ties behave?

Ties have no guaranteed relative order. Add a unique tiebreaker, such as ORDER BY created_at DESC, id DESC, for deterministic pagination and tests.

16. Why avoid relying on implicit row order?

SQL guarantees order only with an outermost ORDER BY. An index, parallel plan, or storage change can alter the order of an otherwise identical query.

17. How do you find duplicates?

Group by the business key and retain groups with more than one row:

SELECT email, COUNT(*) AS n
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Decide whether case, whitespace, or soft-deleted rows belong in the key before adding a constraint.

18. How do you return the top N rows?

Use PostgreSQL/MySQL LIMIT, SQL Server TOP or OFFSET … FETCH, and Oracle FETCH FIRST. Always pair the limit with a deterministic ORDER BY. For top N per group, use a window function (question 48).

19. How do you handle dates?

Use typed date/time columns and half-open ranges: created_at >= :start AND created_at < :end. Name the time zone, convert input explicitly, and avoid applying a function to the indexed column in the predicate.

20. What is CASE used for?

CASE is a conditional expression for labels, custom sort keys, and aggregates. Include an ELSE when an unexpected value should not silently become NULL.

Joins and relational logic (21–30)

21. What is an INNER JOIN?

It returns only rows with a match on the join predicate. If either side has repeated keys, each matching combination appears in the result.

22. What is a LEFT JOIN?

It preserves every left-side row and supplies NULLs for missing right-side matches. It is useful for finding optional relationships and missing children.

23. What is a RIGHT JOIN?

It is the mirror image of a left join. Many teams rewrite it by swapping table order because left joins are easier to read consistently.

24. What is a FULL OUTER JOIN?

It preserves unmatched rows from both inputs. PostgreSQL and SQL Server support it directly; MySQL requires an equivalent built from left joins and UNION.

25. What is a CROSS JOIN?

It returns the Cartesian product: every left row paired with every right row. Use it intentionally for combinations or calendar scaffolding; an accidental missing predicate can create an enormous result.

26. What is a self-join?

A self-join joins a table to itself, often for employee-manager hierarchies, predecessor comparisons, or pairing rows. Give each instance a clear alias.

27. Why do joins multiply rows?

A one-to-many match emits one output row per child; a many-to-many match emits one per matching pair. Aggregate or deduplicate at the correct grain instead of hiding multiplication with DISTINCT.

28. ON versus WHERE with LEFT JOIN?

A right-side filter in ON limits which rows match while preserving unmatched left rows. The same filter in WHERE rejects NULL-extended rows and makes the result effectively an inner join.

29. How do you find missing relationships?

Use either LEFT JOIN … WHERE child.id IS NULL or WHERE NOT EXISTS (SELECT 1 …). NOT EXISTS avoids the NULL trap that can make NOT IN return no rows.

30. What is a join key?

It is the column set expressing row identity or a relationship. Verify uniqueness and data types on both sides; joining on a non-unique label instead of an ID is a common source of inflated totals.

Subqueries, CTEs and set operations (31–40)

31. What is a scalar subquery?

A scalar subquery returns one value for each outer evaluation. If it returns more than one row, most engines raise an error; enforce uniqueness or aggregate explicitly.

32. What is a correlated subquery?

It references columns from the outer row. It can express “exists” or per-row comparisons clearly, but compare its plan with a join or window function on large data.

33. EXISTS versus IN?

EXISTS tests whether at least one matching row exists and can stop at the first match. IN compares values; NOT IN behaves unexpectedly when its list contains NULL, so prefer NOT EXISTS for anti-joins.

34. What is a CTE?

A common table expression names a query expression introduced with WITH. It improves composition and readability; it is not automatically a temporary table or a performance optimization.

35. What is a recursive CTE?

It combines a seed query with a recursive member, using UNION ALL, to walk trees, graphs, or generate sequences. Add cycle protection and a termination condition.

36. UNION versus UNION ALL?

UNION removes duplicate rows, requiring deduplication work. UNION ALL preserves rows and is generally cheaper when duplicates are meaningful or impossible.

37. What is INTERSECT?

INTERSECT returns rows present in both inputs, subject to compatible column types and dialect support. It normally removes duplicates; use the dialect’s ALL variant when available and required.

38. What is EXCEPT?

EXCEPT returns rows from the first query absent from the second. Ordering and duplicate rules vary by dialect, so check the target engine before using it in portable code.

39. When can a CTE hurt performance?

An engine may materialize a CTE or prevent predicate pushdown, causing extra scans. Inspect the plan; in PostgreSQL, consider whether MATERIALIZED or NOT MATERIALIZED is appropriate.

40. How do you make a query readable?

Use meaningful aliases, explicit columns, layered CTEs, consistent formatting, and comments for non-obvious business rules. Keep each layer at a known grain and state assumptions near the query.

Window functions (41–50)

41. What is a window function?

It calculates across related rows while retaining one output row per input row, unlike GROUP BY, which collapses rows.

42. What does PARTITION BY do?

It divides rows into independent windows, such as one partition per customer or department.

43. What does window ORDER BY do?

It defines sequence inside each partition. Add a unique tiebreaker when the result must be reproducible.

44. ROW_NUMBER versus RANK?

ROW_NUMBER() assigns a distinct sequence even to ties. RANK() gives tied rows the same rank and leaves gaps after a tie.

45. What is DENSE_RANK?

DENSE_RANK() shares ranks for ties without gaps, so values ranked 1, 1, 3 by RANK become 1, 1, 2.

46. What do LAG and LEAD do?

They read a previous or following row in the window, enabling period-over-period changes without a self-join. Define ordering and a default value for the boundary row.

47. How do you calculate a running total?

SELECT account_id, posted_at, amount,
       SUM(amount) OVER (
         PARTITION BY account_id
         ORDER BY posted_at, id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS balance
FROM ledger;

An explicit ROWS frame avoids surprises from peer rows under the default frame.

48. How do you return the top row per group?

WITH ranked AS (
  SELECT p.*, ROW_NUMBER() OVER (
    PARTITION BY customer_id ORDER BY created_at DESC, id DESC
  ) AS rn
  FROM purchases AS p
)
SELECT * FROM ranked WHERE rn = 1;

The outer query is required because window results are computed after the filtering phase.

49. Window versus GROUP BY?

GROUP BY reduces each group to one row. A window annotates every source row with a group-level or ordered calculation.

50. When are windows evaluated?

In PostgreSQL’s logical model, windows see rows after grouping and HAVING. Filter their result in an outer query or CTE; do not expect a window alias to work in WHERE.

Data changes and schema design (51–60)

51. What does INSERT do?

INSERT adds rows. Name target columns, let defaults and generated keys work, and handle constraint or conflict behavior deliberately.

52. How do you update safely?

Preview the exact target with a SELECT, use a selective WHERE, wrap the change in a transaction when appropriate, and verify affected-row counts before committing.

53. How do you delete safely?

Confirm the predicate and foreign-key consequences. Use a transaction for reversible work, and prefer a soft-delete column only when retention and uniqueness rules support it.

54. DELETE versus TRUNCATE?

DELETE is row-oriented, supports predicates, and fires row-level behavior according to the engine. TRUNCATE is a bulk operation with engine-specific logging, locking, identity-reset, and rollback semantics.

55. What does DROP do?

DROP removes a database object and its definition. Treat it as destructive DDL; use migrations, dependencies checks, and backups rather than an ad-hoc production command.

56. What is normalization?

Normalization structures relations to reduce redundancy and update anomalies. It usually means storing each fact once and relating tables with keys.

57. What are 1NF, 2NF and 3NF?

First normal form uses atomic values; second removes partial dependency on part of a composite key; third removes transitive dependency on a key. Real designs may deliberately stop short for measured performance reasons.

58. What is denormalization?

Denormalization introduces deliberate redundancy for measured read performance or simpler serving paths. Define how writes keep copies consistent and measure the resulting trade-off.

59. What do CHECK and UNIQUE enforce?

CHECK constrains valid values; UNIQUE prevents duplicate keys. Understand each engine’s NULL behavior and whether a check is enforced for existing rows during migration.

60. What are referential actions?

CASCADE, RESTRICT/NO ACTION, and SET NULL/SET DEFAULT define what happens when a referenced row changes. Choose the action to match the business lifecycle, not convenience.

Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Support on Ko-Fi

Indexes and performance (61–70)

61. Why use an index?

An index can reduce the work needed to locate qualifying rows or produce a requested order. It helps only when its structure matches a real workload.

62. How do you choose composite-index order?

Put equality and join columns first where appropriate, followed by range or sort columns, then validate with the optimizer. There is no universal order independent of predicates and data distribution.

63. What is a covering or index-only scan?

When an index contains every column needed by a query, the engine may avoid fetching table pages. Visibility rules, storage layout, and engine support determine whether this occurs.

64. What is selectivity?

Selectivity describes how narrowly a predicate identifies rows. A low-selectivity index, such as a boolean with an even split, may cost more to use than a sequential scan.

65. Why can indexes hurt?

They consume storage and add maintenance work to inserts, updates, and deletes. Excess indexes also lengthen migrations and can confuse plan selection.

66. What is EXPLAIN?

EXPLAIN shows the chosen plan. Use the engine’s actual-plan option (such as PostgreSQL EXPLAIN (ANALYZE, BUFFERS)) in a safe environment to compare estimates with real row counts and timing.

67. Why might an index be ignored?

Functions or casts on the indexed column, stale statistics, low selectivity, mismatched leading columns, or a cheaper sequential scan can all explain it. Rewrite only after inspecting the plan.

68. What is the N+1 query problem?

Application code runs one query for a list and then one query per row. Replace it with a set-based join, a batched IN query, or a data-loader pattern.

69. Keyset versus offset pagination?

Offset pagination is simple but can scan and shift as earlier rows change. Keyset pagination uses a stable cursor, for example WHERE (created_at, id) < (:last_time, :last_id), and scales better for deep pages.

70. How do you tune honestly?

Capture the exact SQL, parameters, plan, row counts, timing, and workload. Change one thing, retest representative data, and verify write overhead and plan stability rather than relying on intuition.

Transactions, concurrency and advanced reasoning (71–80)

71. What does ACID mean?

Atomicity makes a transaction all-or-nothing; consistency preserves declared rules; isolation controls visibility between concurrent transactions; durability preserves committed changes after failure.

72. COMMIT and ROLLBACK?

COMMIT makes a transaction’s changes durable and visible according to the isolation model. ROLLBACK discards uncommitted changes.

73. What is a savepoint?

A savepoint is a named point inside a transaction. ROLLBACK TO SAVEPOINT undoes later work without discarding the entire transaction.

74. What are isolation levels?

Isolation levels trade anomaly prevention against concurrency. Name the engine and its default; PostgreSQL’s and MySQL’s labels and implementations are not interchangeable, and SQL Server commonly exposes locking and row-versioning choices.

75. What are dirty, non-repeatable and phantom reads?

A dirty read sees uncommitted data; a non-repeatable read sees a changed value on reread; a phantom read sees rows appear or disappear for the same predicate. Which anomalies are possible depends on isolation and implementation.

76. What is a deadlock?

Transactions wait on one another’s locks. Keep lock acquisition order consistent, keep transactions short, index foreign-key access paths where appropriate, and retry the transaction when the engine reports a deadlock.

77. What is a serialization failure?

The engine could not safely order concurrent transactions at the requested isolation level. Abort and retry the complete transaction with bounded backoff; do not retry only the final statement.

78. Optimistic versus pessimistic concurrency?

Optimistic concurrency detects a conflict at update or commit time, often with a version column. Pessimistic concurrency locks before work. Optimistic approaches improve throughput when conflicts are rare; pessimistic ones can simplify high-contention workflows.

79. Stored procedure versus function?

Both are server-side routines, but invocation syntax, transaction control, side effects, return values, and portability vary by engine. State the target dialect before comparing them.

80. How should you answer an ambiguous SQL question?

State assumptions and dialect, show a small query, define the expected grain, and discuss NULLs, duplicates, ties, and date boundaries. Then explain complexity, index use, transaction safety, and what would change at a different data volume or concurrency level.

A practical interview workflow

1. Restate the desired result and identify the row grain.
2. Declare the dialect and relevant schema constraints.
3. Write the simplest correct query, using explicit joins and columns.
4. Test edge cases: NULL, duplicate keys, ties, empty input, and boundary dates.
5. Inspect an execution plan and discuss indexes only in relation to the measured workload.
6. For writes, show transaction boundaries, error handling, and a safe retry policy.

Common interview mistakes and fixes

• Filtering a LEFT JOIN in WHERE: move the right-side predicate into ON when unmatched left rows must remain.
• Using NOT IN with nullable data: use NOT EXISTS or explicitly exclude NULL.
• Returning an arbitrary top row: add a unique tiebreaker to the ordering.
• Counting after a multiplying join: aggregate at the intended grain or pre-aggregate the many-side.
• Guessing about an index: capture an actual plan with representative parameters.
• Retrying one statement after a serialization error: retry the whole transaction.
• Claiming portability: label PostgreSQL, MySQL, SQL Server, or Oracle syntax and identify non-portable clauses.

Capture your practice results without building a browser harness

If you save SQL exercises, explain plans, or interview notes as web pages, ScreenshotNeo can return a clean PNG, JPEG, WebP, or PDF from one request. It accepts consent banners like a visitor and removes more than 60 known consent platforms, newsletter popups, and chat widgets before capture; each of those steps can be disabled. Bot checks or CAPTCHAs, blank pages, timeouts, failed loads, and cache hits are not billed, and response headers identify the page verdict and billing status.

Or skip the browser setup

Use the API documented at ScreenshotNeo’s API documentation:

curl -G "https://api.screenshotneo.com/v1/shot" -d access_key=YOUR_API_KEY --data-urlencode url=https://stripe.com -o shot.webp

import requests
r = requests.get("https://api.screenshotneo.com/v1/shot", params={"access_key": "YOUR_API_KEY", "url": "https://stripe.com"}, timeout=90)
open("shot.webp", "wb").write(r.content)

const q = new URLSearchParams({ access_key: 'YOUR_API_KEY', url: 'https://stripe.com' });
const res = await fetch(`https://api.screenshotneo.com/v1/shot?${q}`);

ScreenshotNeo also provides an MCP server with take_screenshot, get_page_info, and capture_pdf tools for Claude, Cursor, and other MCP clients. Every feature is on every plan: 1,000 screenshots per month are free with no card, and paid plans start at $5 for 3,000 shots. Create a free ScreenshotNeo account.

Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.