SQL Interview Questions: Basic to Advanced

1What is a relation in the relational model, and why does it matter that a table has no inherent row order?Basicconceptual30 minIn writing
2In what order does the database logically process FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY and LIMIT, and what bug does knowing that order prevent?Basicconceptual40 minIn writing
3SQL is declarative. What does that mean in practice for how you should reason about performance?Basicconceptual30 minIn writing
4How do candidate keys, primary keys, and surrogate keys differ, and how do you pick one for a new table?Basicconceptual40 minIn writing
5Why does SELECT return duplicates when relational theory says a relation is a set, and where does that leak into real queries?Intermediateconceptual35 minIn writing
6What separates DDL, DML, DCL and TCL statements, and which of them can you roll back in PostgreSQL?Basicconceptual30 minIn writing
7An interviewer asks you to explain what actually happens between typing a SELECT and getting rows back. How do you answer in two minutes?Scenarioscenario25 minIn writing
8Check your understanding of relations, keys, and logical query processing order.Basicconceptual15 minIn writing

9How do you combine BETWEEN, IN, LIKE and IS NULL in a WHERE clause, and which of them can silently disable an index?Basicpractical35 minIn writing
10Why does WHERE status != 'active' drop rows where status is NULL, and how do you write the filter you actually meant?Basicconceptual40 minIn writing
11When would you reach for COALESCE, NULLIF, or a column DEFAULT, and why is each a different decision?Intermediatepractical35 minIn writing
12How do you use CASE expressions to reshape a result set, and when does that logic belong in the application instead?Intermediatepractical35 minIn writing
13What does DISTINCT actually cost, and why is ORDER BY without a tiebreaker column a source of flaky pagination?Basicpractical40 minIn writing
14Why does OFFSET 100000 get slower the deeper a user pages, and how does keyset pagination fix it?Intermediatepractical45 minIn writing
15A report is missing rows that the user swears exist, and the WHERE clause looks correct. How do you find the cause?Scenariotroubleshooting25 minIn writing
16Check your understanding of filtering, NULL handling, ordering, and pagination.Basicconceptual15 minIn writing

17Walk through INNER, LEFT, RIGHT and FULL OUTER joins on the same two tables. What does each row set actually contain?Basicconceptual40 minIn writing
18Why does moving a condition from the ON clause to the WHERE clause turn a LEFT JOIN into an INNER JOIN?Intermediatepractical40 minIn writing
19A join to a one-to-many table doubled your revenue total. Explain the fan-out and how you would fix the aggregate.Intermediatetroubleshooting45 minIn writing
20How do you express "customers with no orders" three different ways, and which one would you ship?Intermediatepractical45 minIn writing
21When do you need a self-join, and how would you use one to find employees who earn more than their manager?Intermediatepractical40 minIn writing
22What are nested loop, hash, and merge joins, and what makes the planner choose each one?Advancedconceptual50 minIn writing
23You are asked at a whiteboard to report revenue per customer including customers who bought nothing this month. How do you write it?Scenariodesign25 minIn writing
24Check your understanding of join types, filter placement, fan-out, and join algorithms.Intermediateconceptual15 minIn writing

25What rule decides which columns are allowed in the SELECT list of a GROUP BY query, and why does PostgreSQL reject some that MySQL accepts?Basicpractical40 minIn writing
26When must a condition go in HAVING rather than WHERE, and why should you still push everything you can into WHERE?Basicconceptual35 minIn writing
27How do COUNT(*), COUNT(column) and COUNT(DISTINCT column) differ once NULLs are in the data?Intermediatepractical35 minIn writing
28How do you produce several conditional counts in a single pass using FILTER or CASE inside aggregates?Intermediatepractical45 minIn writing
29When is GROUPING SETS or ROLLUP worth the complexity compared with running several UNION ALL queries?Advancedpractical50 minIn writing
30What is the difference between UNION and UNION ALL, and when do INTERSECT and EXCEPT beat a join?Intermediatepractical40 minIn writing
31Design a single query that returns, per product category, total revenue, order count, and the share of orders that were refunded.Scenariodesign25 minIn writing
32Check your understanding of grouping, HAVING, aggregate NULL handling, and set operations.Intermediateconceptual15 minIn writing

33Where can a subquery legally appear in a statement, and what changes about it in each position?Basicpractical40 minIn writing
34What makes a subquery correlated, and why can one turn an otherwise fast query into a per-row loop?Intermediatetroubleshooting45 minIn writing
35How do EXISTS, IN and a JOIN differ in results and in plan, especially when the inner query can return NULL?Intermediatepractical45 minIn writing
36Are CTEs an optimization fence in PostgreSQL, and when would you use MATERIALIZED or NOT MATERIALIZED deliberately?Intermediatepractical45 minIn writing
37How would you use a recursive CTE to walk an org chart, and what stops it from looping forever on bad data?Advancedpractical55 minIn writing
38What problem does LATERAL solve that a plain subquery cannot, and how would you use it to fetch the three most recent orders per customer?Advancedpractical50 minIn writing
39You need the latest row per group across a 50 million row table. Compare the approaches you would consider and defend your pick.Scenariodesign25 minIn writing
40Check your understanding of subqueries, EXISTS, CTEs, recursion, and LATERAL.Intermediateconceptual15 minIn writing

41How does a window function differ from an aggregate, and why can you not use one in a WHERE clause?Intermediateconceptual45 minIn writing
42What does the frame clause (ROWS versus RANGE) actually change, and what is the default frame you get when you omit it?Advancedpractical50 minIn writing
43When would you choose ROW_NUMBER over RANK or DENSE_RANK, and how does the tiebreaker affect reproducibility?Intermediatepractical40 minIn writing
44How do you compute a running total and a seven-day moving average over daily revenue in one pass?Advancedpractical50 minIn writing
45How would you delete duplicate rows that have no unique key, keeping only the earliest of each group?Intermediatepractical45 minIn writing
46How would you find each user's longest streak of consecutive active days, and how does the same technique power a cohort retention table?Advanceddesign60 minIn writing
47An interviewer asks for month-over-month revenue growth per region as a single query. Talk through your solution.Scenariodesign25 minIn writing
48Check your understanding of windows, frames, ranking, and analytics patterns.Advancedconceptual15 minIn writing

49Why should money never be stored as a float, and what would you use in PostgreSQL instead?Basicconceptual35 minIn writing
50Does VARCHAR(255) buy you anything over TEXT in PostgreSQL, and where does collation change query results?Basicconceptual35 minIn writing
51What is the difference between timestamp and timestamptz, and how do you store an event that happened in another time zone?Intermediatepractical45 minIn writing
52When is a JSONB column the right call instead of a proper table, and what do you give up by choosing it?Intermediatedesign45 minIn writing
53Which constraints would you put on an orders table, and what does each one prevent that application code would miss?Intermediatepractical45 minIn writing
54What does ON DELETE CASCADE actually do, and when is it a trap rather than a convenience?Intermediatedesign45 minIn writing
55You inherit a table where every column is TEXT and nullable. How do you argue for and stage a fix?Scenariodesign25 minIn writing
56Check your understanding of types, time zones, JSONB, and constraints.Intermediateconceptual15 minIn writing

57Explain 1NF, 2NF and 3NF using a real orders table, and say what each step actually removed.Basicconceptual45 minIn writing
58What would justify duplicating a column into a second table, and how do you keep the copy correct afterwards?Advanceddesign50 minIn writing
59How do you model one-to-many and many-to-many relationships, and what belongs on a join table besides the two keys?Intermediatedesign45 minIn writing
60What are the real costs of a deleted_at soft-delete column, and what would you use instead for auditable history?Intermediatedesign45 minIn writing
61How would you isolate tenants in a shared database, and what makes a tenant_id column safer or riskier than a schema per tenant?Advanceddesign55 minIn writing
62A product manager wants user-defined fields. Compare EAV, JSONB, and adding columns, and pick one.Advanceddesign50 minIn writing
63Design the schema for a job board with applications, resumes, and interview stages. Defend your key choices.Scenariodesign25 minIn writing
64Check your understanding of normalization, relationships, history, and multi-tenancy.Intermediateconceptual15 minIn writing

65What does a database index actually do, and why shouldn't every column be indexed?Basicconceptual5 min
66What does a B-tree index look like on disk, and why does that structure make range scans cheap but random inserts expensive?Intermediateconceptual45 minIn writing
67You have a query filtering on tenant_id and sorting by created_at. What column order do you use for the composite index, and why?Advancedpractical50 minIn writing
68What makes an index-only scan possible in PostgreSQL, and what does INCLUDE add that a wider index key does not?Advancedpractical50 minIn writing
69When is a partial index the right answer, and how would you index lower(email) for a case-insensitive lookup?Advancedpractical45 minIn writing
70When would you reach for GIN, GiST, BRIN or a hash index instead of a B-tree?Advancedconceptual50 minIn writing
71You added the index the slow query needed and nothing got faster. Walk through why the planner might still ignore it.Scenariotroubleshooting25 minIn writing
72Check your understanding of B-trees, composite order, covering indexes, and index types.Advancedconceptual15 minIn writing

73How does a cost-based optimizer pick a plan, and what inputs is it working from?Advancedconceptual50 minIn writing
74How do you read an EXPLAIN plan tree, and which numbers do you look at first?Intermediatepractical50 minIn writing
75What do EXPLAIN ANALYZE and BUFFERS tell you that plain EXPLAIN cannot, and when is running it in production unsafe?Advancedpractical45 minIn writing
76The planner estimates 12 rows and gets 400,000. What is wrong, and how do you fix it?Advancedtroubleshooting50 minIn writing
77Why is a sequential scan sometimes faster than an index scan, and at roughly what selectivity does that flip?Intermediateconceptual45 minIn writing
78Which common ways of writing a WHERE clause stop an index from being used, and how do you rewrite them?Advancedtroubleshooting50 minIn writing
79An interviewer hands you an EXPLAIN ANALYZE output with a nested loop over 2 million rows. What do you say?Scenariotroubleshooting25 minIn writing
80Check your understanding of planning, EXPLAIN output, statistics, and scan choices.Advancedconceptual15 minIn writing

81What do atomicity, consistency, isolation and durability each guarantee, and which one do developers most often assume they have but do not?Basicconceptual40 minIn writing
82Where should a transaction begin and end in a web request, and what goes wrong when it wraps an HTTP call to a payment provider?Intermediatepractical45 minIn writing
83Define dirty read, non-repeatable read, phantom read and write skew, and give a business example of each.Intermediateconceptual45 minIn writing
84How would you choose a transaction isolation level for a feature that decrements inventory stock on checkout, and what could go wrong at the default level?Advanceddesign5 min
85Which anomalies does each PostgreSQL isolation level prevent, and why is PostgreSQL REPEATABLE READ stricter than the standard requires?Advancedconceptual50 minIn writing
86What do savepoints let you do inside a transaction, and what is the cost of using one per loop iteration?Intermediatepractical40 minIn writing
87Design the transaction boundaries for a checkout that charges a card, writes an order, and decrements stock.Scenariodesign25 minIn writing
88Check your understanding of ACID, anomalies, isolation levels, and transaction scope.Advancedconceptual15 minIn writing

89How does MVCC let a reader and a writer touch the same row at once, and what is stored to make that work?Advancedconceptual50 minIn writing
90Which lock does a plain UPDATE take, and which statements conflict with it?Advancedconceptual45 minIn writing
91How would you build a job queue table using SELECT ... FOR UPDATE SKIP LOCKED, and why is that better than a status flag alone?Advancedpractical50 minIn writing
92Two transactions deadlocked in production. How do you read the log entry and stop it happening again?Advancedtroubleshooting50 minIn writing
93When would you use a version column instead of a row lock, and what does the client have to do differently?Advanceddesign45 minIn writing
94Why does one idle-in-transaction connection stop autovacuum from cleaning up an entire database?Advancedtroubleshooting50 minIn writing
95Writes to one hot table are queueing behind each other during peak traffic. Walk through your investigation.Scenariotroubleshooting25 minIn writing
96Check your understanding of MVCC, lock modes, deadlocks, and vacuum.Advancedconceptual15 minIn writing

97You have a slow endpoint and no idea which statement is at fault. How do you find it with pg_stat_statements and query logs?Intermediatepractical45 minIn writing
98What is the N+1 query problem, and how do you fix it without just caching the result?Intermediatepractical5 min
99A query takes 20ms and the endpoint takes 2 seconds. Where has the time gone, and how do you prove it?Advancedconceptual45 minIn writing
100Why does a serverless app exhaust database connections, and what does a pooler in transaction mode change?Advancedtroubleshooting50 minIn writing
101A query that joins three tables and was fast in staging is timing out in production under real traffic. Walk through how you'd diagnose it.Scenarioscenario5 min
102When is a materialized view the right cache, and how do you keep it fresh without blocking readers?Advanceddesign50 minIn writing
103The database CPU is pinned at 100% and everything is slow. What do you look at, in what order?Scenariotroubleshooting25 minIn writing
104Check your understanding of query profiling, pooling, N+1, and caching strategies.Advancedconceptual15 minIn writing

105What actually starts to hurt as a table grows past a few hundred million rows?Advancedconceptual45 minIn writing
106How would you partition an events table by month in PostgreSQL, and what does partition pruning require from your queries?Advanceddesign55 minIn writing
107How do you choose between range, list and hash partitioning, and what makes a bad partition key?Advanceddesign50 minIn writing
108Design a retention policy that deletes two years of old rows without locking the table for hours.Advanceddesign50 minIn writing
109How do you update 50 million rows without holding one enormous transaction, and how do you make the job resumable?Advancedpractical50 minIn writing
110When do you reach for read replicas, and what breaks the first time you serve a read from a lagging one?Advanceddesign55 minIn writing
111An events table grows by 200 million rows a month and queries only ever look at the last 30 days. Design the storage.Scenariodesign25 minIn writing
112Check your understanding of partitioning, retention, batching, and replicas.Advancedconceptual15 minIn writing

113Why must an applied migration never be edited, and what do you do instead when it was wrong?Intermediatepractical40 minIn writing
114Which ALTER TABLE statements take an ACCESS EXCLUSIVE lock, and why did adding one NOT NULL column take the site down?Advancedtroubleshooting50 minIn writing
115Walk through renaming a column with zero downtime using the expand-and-contract pattern.Advanceddesign50 minIn writing
116What does CREATE INDEX CONCURRENTLY buy you, and what do you do when it leaves an invalid index behind?Advancedpractical45 minIn writing
117How do you backfill a new column across 100 million rows while the application keeps writing?Advancedpractical50 minIn writing
118A migration has half applied in production. Do you roll back or roll forward, and what decides that?Advanceddesign45 minIn writing
119You must split one table into two and ship it without a maintenance window. Plan the sequence of releases.Scenariodesign25 minIn writing
120Check your understanding of DDL locking, expand-contract, concurrent indexes, and backfills.Advancedconceptual15 minIn writing

121How does SQL injection actually work, and why do parameterized queries stop it when escaping does not?Basicconceptual45 minIn writing
122You need a user-chosen sort column. How do you build that query without opening an injection hole?Advancedpractical45 minIn writing
123What privileges should the application role actually have, and why should it not own its own tables?Advanceddesign50 minIn writing
124How does PostgreSQL row-level security enforce tenant isolation, and what does it cost you in plans and testing?Advanceddesign55 minIn writing
125How would you audit access to a table of personal data, and where does encryption at rest stop helping?Advanceddesign50 minIn writing
126Take a production database that has degraded over six months — slow queries, table bloat, a risky pending migration, and a tenant-isolation gap — and produce a prioritized remediation plan you can defend.Scenariodesign240 minIn writing
127A code review shows a query built by string concatenation from a request parameter. How do you assess the blast radius and fix it?Scenariotroubleshooting25 minIn writing
128Check your understanding of injection, privileges, row-level security, and auditing.Advancedconceptual15 minIn writing