SQL Interview Questions
SQL is one of the most common topics in data, backend, and analyst interviews. These are the questions interviewers actually ask, grouped by theme and tagged by experience level.
101 questions with concise, interview-ready answers.
SQL Basics
1. What is SQL and what are its main sublanguages? Fresher
SQL is the standard language for relational databases. It splits into DDL (CREATE, ALTER, DROP — structure), DML (SELECT, INSERT, UPDATE, DELETE — data), DCL (GRANT, REVOKE — permissions) and TCL (COMMIT, ROLLBACK, SAVEPOINT — transactions). Interviewers ask this to check you can categorise a statement, not to hear the acronyms.
2. What is the difference between DELETE, TRUNCATE and DROP? Fresher
DELETE removes rows one at a time, can have a WHERE clause, fires triggers, and is fully logged and rollback-able. TRUNCATE removes all rows by deallocating pages — much faster, no WHERE, usually resets identity, and cannot be filtered. DROP removes the table definition itself. DELETE is DML; TRUNCATE and DROP are DDL.
3. What is the difference between WHERE and HAVING? Fresher
WHERE filters individual rows before grouping and cannot reference aggregates. HAVING filters groups after GROUP BY and can. If a condition does not involve an aggregate, put it in WHERE — filtering earlier means fewer rows to group, which is usually faster.
4. What is the difference between UNION and UNION ALL? Fresher
UNION combines result sets and removes duplicates, which requires a sort or hash and therefore costs time. UNION ALL concatenates without deduplicating and is significantly faster. Use UNION ALL whenever you know the inputs are already disjoint.
5. What is the difference between CHAR and VARCHAR? Fresher
CHAR(n) is fixed length and pads with spaces to n, so it wastes space for variable data but can be marginally faster for genuinely fixed-width values. VARCHAR(n) stores only what you put in plus a small length prefix. Use VARCHAR by default; CHAR only for things like country codes that are always the same width.
6. What are the main SQL constraints? Fresher
NOT NULL (no missing value), UNIQUE (no duplicates), PRIMARY KEY (unique and not null, one per table), FOREIGN KEY (references a key in another table), CHECK (a boolean condition on values), and DEFAULT (a value when none is supplied). Constraints are the database enforcing correctness rather than trusting application code.
7. What is the difference between a primary key and a unique key? Fresher
Both enforce uniqueness. A primary key cannot contain NULL and there is exactly one per table, and it is normally the clustering key. A unique constraint allows NULLs — how many depends on the engine — and you can have several per table. Use the primary key for identity, unique constraints for alternate keys like email.
8. What is a foreign key? Fresher
A column or set of columns referencing the primary or unique key of another table, enforcing referential integrity so you cannot insert an orphan row or delete a parent that still has children. ON DELETE CASCADE, SET NULL and RESTRICT control what happens to children when the parent goes.
9. What is the difference between NULL and zero or an empty string? Fresher
NULL means unknown or absent, not a value. Zero and the empty string are values. That is why NULL = NULL is not true — comparing two unknowns cannot be true — and why you must use IS NULL. Aggregates like COUNT(column) and AVG skip NULLs, which is a frequent source of surprising results.
10. What is the difference between COUNT(*), COUNT(1) and COUNT(column)? Fresher
COUNT(*) and COUNT(1) both count rows and perform identically on every mainstream engine — the "COUNT(1) is faster" claim is folklore. COUNT(column) counts rows where that column is not NULL, which is genuinely different and is usually what the interviewer is probing.
11. What is the logical execution order of a SELECT? 2–5 yrs
FROM and JOIN, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and finally LIMIT/OFFSET. This explains two common confusions: why you cannot use a SELECT alias in WHERE (the alias does not exist yet), and why you can use it in ORDER BY (it does by then).
12. What is the difference between DISTINCT and GROUP BY? 2–5 yrs
DISTINCT removes duplicate rows from a result. GROUP BY collapses rows into groups so you can aggregate them. A GROUP BY with no aggregate behaves like DISTINCT and most optimisers produce the same plan — but if you are not aggregating, DISTINCT states the intent more clearly.
Joins
13. What are the types of JOIN in SQL? Fresher
INNER JOIN returns rows matching in both tables. LEFT (OUTER) JOIN returns all left rows plus matches, NULL where none. RIGHT JOIN is the mirror. FULL OUTER JOIN returns all rows from both sides. CROSS JOIN produces the Cartesian product. SELF JOIN is a table joined to itself, typically via an alias.
14. What is the difference between INNER JOIN and LEFT JOIN? Fresher
INNER JOIN keeps only rows with a match on both sides, so unmatched rows disappear. LEFT JOIN keeps every row from the left table, filling right-side columns with NULL where there is no match. If you need "customers and their orders, including customers with none", it must be a LEFT JOIN.
15. What is a self join and when do you need one? Fresher
A table joined to itself using aliases, for hierarchical or comparative data within one table — an employees table where manager_id references employee_id is the canonical example. You alias the table twice (e and m) and join e.manager_id = m.employee_id.
16. Why does a filter in WHERE break a LEFT JOIN? 2–5 yrs
Because WHERE runs after the join. A LEFT JOIN produces NULLs for unmatched right rows, and any WHERE condition on a right-side column evaluates to unknown for those, so they get filtered out — silently turning the LEFT JOIN into an INNER JOIN. Put the condition in the ON clause instead if it should apply before the join.
17. What is a CROSS JOIN and when is it legitimately useful? 2–5 yrs
It pairs every row of one table with every row of another, producing n × m rows. Usually it appears by accident from a missing join condition. Deliberately, it is useful for generating combinations — a calendar of dates crossed with every store, so you can report zero-sales days.
18. How do you find duplicate rows in a table? 2–5 yrs
GROUP BY the columns that define a duplicate and use HAVING COUNT(*) > 1. To see the offending rows rather than just the keys, use a window function: ROW_NUMBER() OVER (PARTITION BY those columns ORDER BY id) and keep rows where the number is greater than 1 — which also gives you a safe delete.
19. What join algorithms does a database use? Senior
Nested loop (good when one side is tiny or an index makes lookups cheap), hash join (build a hash table on the smaller side, probe with the larger — good for big unsorted equality joins), and merge join (both inputs sorted on the key, then walked in step). The planner picks based on statistics; seeing a nested loop over millions of rows in a plan usually means the estimates are wrong.
Aggregation & Window Functions
20. What does GROUP BY do? Fresher
It collapses rows sharing the same values in the grouped columns into a single output row, so aggregate functions compute per group. Every column in the SELECT must either appear in the GROUP BY or be inside an aggregate — most engines enforce this, and MySQL historically did not, which produced arbitrary results.
21. What are the common aggregate functions? Fresher
COUNT, SUM, AVG, MIN and MAX. All except COUNT(*) ignore NULLs, which is why AVG over a column with NULLs divides by the number of non-null values rather than the row count — a classic source of wrong numbers in reports.
22. What is a window function? 2–5 yrs
A function computing a value across a set of rows related to the current row, without collapsing them — so you keep row-level detail alongside the aggregate. Written as func() OVER (PARTITION BY ... ORDER BY ...). Use it for running totals, ranking within groups, and comparing a row to its group average.
23. What is the difference between ROW_NUMBER, RANK and DENSE_RANK? 2–5 yrs
ROW_NUMBER assigns a unique sequential number, breaking ties arbitrarily. RANK gives tied rows the same rank and then skips — 1, 2, 2, 4. DENSE_RANK gives ties the same rank without skipping — 1, 2, 2, 3. Which one is correct depends entirely on how the question wants ties handled, so say so explicitly.
24. How do you find the Nth highest salary? 2–5 yrs
The cleanest modern answer is a window function: rank with DENSE_RANK() OVER (ORDER BY salary DESC) in a subquery and filter where the rank equals N. Alternatives are LIMIT with OFFSET, or a correlated subquery counting distinct higher salaries. Mention DENSE_RANK explicitly — it handles duplicate salaries correctly, which is what the question is really testing.
25. What do LAG and LEAD do? 2–5 yrs
They read a value from a previous or following row within the window without a self join. LAG(amount) OVER (PARTITION BY user ORDER BY date) gives the prior transaction, which makes month-over-month change or gap detection a single expression.
26. What is the difference between PARTITION BY and GROUP BY? Senior
GROUP BY reduces the result to one row per group. PARTITION BY divides rows into groups for a window function but returns every original row. So GROUP BY answers "what is the average per department"; PARTITION BY answers "show each employee alongside their department average".
27. What are ROWS and RANGE in a window frame? Senior
They define which rows the window covers relative to the current row. ROWS counts physical rows — ROWS BETWEEN 2 PRECEDING AND CURRENT ROW is a three-row moving window. RANGE works on values of the ORDER BY expression, so ties are all included together. Using RANGE where you meant ROWS silently changes running totals when duplicates exist.
Subqueries & CTEs
28. What is a subquery? Fresher
A query nested inside another, usable in SELECT, FROM, WHERE or HAVING. A scalar subquery returns one value, a row subquery one row, and a table subquery a result set used as a derived table. It is the standard way to filter against an aggregate you cannot compute inline.
29. What is a correlated subquery? 2–5 yrs
A subquery referencing a column from the outer query, so it must be re-evaluated per outer row rather than once. That makes it potentially O(n) executions and a common performance problem. Most correlated subqueries can be rewritten as a join or a window function, which the optimiser handles far better.
30. What is a CTE and why use one? 2–5 yrs
A Common Table Expression is a named temporary result defined with WITH and referenced in the following query. It makes complex logic readable by naming intermediate steps instead of nesting subqueries, and it can be referenced multiple times. Note that whether a CTE is materialised or inlined varies by engine and affects performance.
31. What is a recursive CTE? 2–5 yrs
A CTE that references itself, written as an anchor query UNION ALL a recursive query. It walks hierarchies — an org chart, a category tree, a bill of materials — or generates sequences. Always bound it, either by a depth column or the engine's recursion limit, or a cycle in the data becomes an infinite loop.
32. What is the difference between IN, EXISTS and JOIN? 2–5 yrs
IN checks membership in a value list or subquery result. EXISTS checks whether a subquery returns any row and can short-circuit on the first match. JOIN combines and can multiply rows if the right side has duplicates. EXISTS is usually the safest for "does a related row exist", because unlike JOIN it cannot duplicate the outer rows.
33. Why is NOT IN dangerous with NULLs? Senior
If the subquery returns any NULL, NOT IN evaluates to unknown for every row and the result is empty — silently, with no error. NOT EXISTS does not have this problem because it tests row existence rather than value comparison. This is one of the most common real bugs in production SQL.
Indexes & Performance
34. What is an index and what does it cost? Fresher
A separate data structure, usually a B-tree, that lets the engine find rows without scanning the whole table — turning a linear scan into a logarithmic lookup. The cost is storage plus slower INSERT, UPDATE and DELETE, because every index must be maintained. Indexing every column is a classic mistake.
35. What is the difference between a clustered and a non-clustered index? 2–5 yrs
A clustered index determines the physical order of the rows, so there can only be one, and the table data is the leaf level. A non-clustered index is a separate structure holding the key plus a pointer to the row, so a lookup may need a second read to fetch the remaining columns. In InnoDB the primary key is always the clustered index.
36. What is a composite index, and why does column order matter? 2–5 yrs
An index on several columns in a defined order. It can serve queries filtering on a leftmost prefix — an index on (a, b, c) helps queries on a, on a and b, or all three, but not on b alone. This leftmost-prefix rule is the single most-tested indexing fact in interviews.
37. What is a covering index? 2–5 yrs
An index containing every column a query needs, so the engine answers entirely from the index without touching the table. That avoids the extra lookup per row and can be dramatically faster. In execution plans it shows as an index-only scan.
38. Why might a query not use an available index? 2–5 yrs
Common causes: a function or arithmetic applied to the indexed column (WHERE YEAR(created) = 2026 rather than a range), a leading wildcard LIKE, an implicit type conversion, low selectivity where a scan is genuinely cheaper, or stale statistics misleading the planner. The fix is usually rewriting the predicate to be sargable, not adding another index.
39. What does EXPLAIN tell you, and what do you look for? Senior
It shows the planner's chosen execution plan — access methods, join order and algorithms, and estimated rows and cost. Look for full scans on large tables, nested loops with big estimated row counts, and a large gap between estimated and actual rows, which points at stale or missing statistics. EXPLAIN ANALYZE runs the query and gives real timings.
40. How would you approach a slow query? Senior
Reproduce it, then read EXPLAIN ANALYZE rather than guessing. Check whether the predicate is sargable, whether the right index exists and whether the leftmost prefix matches, whether statistics are current, and how many rows are actually being touched versus returned. Only then consider structural changes like denormalisation, partitioning or caching.
41. What is meant by a sargable predicate? Senior
"Search ARGument ABLE" — a condition the engine can satisfy using an index. WHERE created_at >= '2026-01-01' is sargable; WHERE YEAR(created_at) = 2026 is not, because the function must be evaluated per row. Rewriting non-sargable predicates as ranges is often the single biggest win available.
42. What is the N+1 query problem? Senior
Fetching a list with one query and then issuing one additional query per row to load a relation — 1 + N round trips. It typically comes from lazy loading in an ORM inside a loop. Fix it with a join, an eager-loading directive, or a single batched IN query.
Transactions & Concurrency
43. What are ACID properties? Fresher
Atomicity (all or nothing), Consistency (the database moves between valid states, honouring constraints), Isolation (concurrent transactions do not interfere in ways the isolation level forbids) and Durability (committed data survives a crash). These are the guarantees that distinguish a transactional database from a file.
44. What are the SQL isolation levels? 2–5 yrs
READ UNCOMMITTED allows dirty reads. READ COMMITTED prevents dirty reads but allows non-repeatable reads. REPEATABLE READ prevents those but classically allows phantoms. SERIALIZABLE prevents all three by making concurrent execution equivalent to some serial order. Higher isolation means more blocking or more rollbacks.
45. What are dirty, non-repeatable and phantom reads? 2–5 yrs
A dirty read sees another transaction's uncommitted change. A non-repeatable read gets a different value when re-reading the same row because another transaction committed in between. A phantom read gets a different set of rows for the same query because another transaction inserted or deleted matching rows.
46. What is a deadlock in a database and how do you reduce them? 2–5 yrs
Two transactions each holding a lock the other needs, so neither proceeds; the engine detects it and kills one as the victim. Reduce them by accessing tables and rows in a consistent order across all code paths, keeping transactions short, and avoiding user interaction inside a transaction. Retrying the victim is expected — write for it.
47. What is optimistic versus pessimistic locking? Senior
Pessimistic locking takes a lock up front (SELECT ... FOR UPDATE), blocking others — safe under high contention, but it serialises work. Optimistic locking takes no lock, and on write checks a version column to detect that someone else changed the row, failing so the caller can retry. Optimistic wins when conflicts are rare.
48. What is MVCC? Senior
Multi-Version Concurrency Control keeps multiple versions of a row so readers see a consistent snapshot without blocking writers, and writers do not block readers. It is how Postgres and InnoDB implement isolation. The cost is version storage and the need to clean up dead versions — vacuum in Postgres, purge in InnoDB.
Design & Normalisation
49. What is normalisation, and what are the first three normal forms? Fresher
1NF requires atomic values and no repeating groups. 2NF additionally requires no partial dependency on part of a composite key. 3NF additionally requires no transitive dependency — non-key columns must not depend on other non-key columns. The purpose is eliminating redundancy so an update cannot leave contradictory copies.
50. When would you deliberately denormalise? 2–5 yrs
When read performance matters more than write simplicity and the joins are demonstrably the bottleneck — reporting tables, precomputed aggregates, a cached display name. The trade is duplicated data that must be kept in sync, so it should be a measured decision rather than a default.
51. What is a view, and what is a materialised view? 2–5 yrs
A view is a stored query that behaves like a virtual table, executed each time it is referenced — it simplifies access and can restrict columns, but does not improve performance by itself. A materialised view stores the computed result physically, so reads are fast but the data is stale until refreshed.
52. What is a stored procedure, and what is the argument against them? 2–5 yrs
Precompiled SQL stored in the database and invoked by name, useful for reducing round trips and centralising logic. The argument against is that business logic in the database is harder to version-control, test and review than application code, and it ties you to the vendor's procedural dialect.
53. What is a trigger, and why are they controversial? 2–5 yrs
Code that fires automatically on INSERT, UPDATE or DELETE. They enforce invariants and maintain audit trails without application cooperation. They are controversial because they execute invisibly — a developer reading the application code has no indication anything happened, which makes debugging unexpected side effects genuinely difficult.
54. What is the difference between OLTP and OLAP? Senior
OLTP handles many small concurrent read-write transactions — normalised schemas, row storage, indexes tuned for point lookups. OLAP handles few large analytical scans — denormalised star schemas, often columnar storage, tuned for aggregation. Running heavy analytics against a production OLTP database is the mismatch this distinction exists to warn about.
55. What is partitioning and how does it differ from sharding? Senior
Partitioning splits one table into segments within a single database, typically by range or list on a date, so queries prune irrelevant partitions and old data can be dropped cheaply. Sharding distributes data across separate database instances to scale beyond one machine. Partitioning is a physical layout decision; sharding is a distributed-systems decision with much larger consequences.
56. How would you design a schema for a many-to-many relationship? Senior
With a junction table holding foreign keys to both sides, and typically a composite primary key across the pair to prevent duplicates. Add an index on the second column too, since the composite key only serves the leftmost prefix. Any attributes of the relationship itself — a role, a joined date — belong on the junction table.
Functions, Strings & Dates
57. What is the difference between a scalar and an aggregate function? Fresher
A scalar function operates on one row and returns one value per row — UPPER, LENGTH, ROUND, COALESCE. An aggregate function operates across a set of rows and returns a single value — SUM, COUNT, AVG. That difference is why an aggregate forces a GROUP BY when other columns are selected and a scalar does not.
58. What does COALESCE do? Fresher
It returns the first non-NULL argument from a list, so COALESCE(nickname, first_name, 'Unknown') gives you a fallback chain. It is the standard way to substitute a default for a missing value, and it is portable — unlike ISNULL, IFNULL or NVL, which are vendor-specific.
59. What is the difference between CASE and COALESCE? Fresher
COALESCE handles exactly one situation — NULL substitution. CASE evaluates arbitrary conditions and returns different values per branch, so it handles bucketing, conditional aggregation and pivoting. COALESCE(a, b) is shorthand for CASE WHEN a IS NOT NULL THEN a ELSE b END.
60. How do you concatenate strings in SQL? Fresher
The standard operator is || in Postgres, Oracle and SQLite; MySQL and SQL Server use CONCAT(). Note that in most engines concatenating a NULL yields NULL, whereas CONCAT() treats NULL as an empty string in MySQL — a real source of silently blank values.
61. How do you extract part of a date? 2–5 yrs
EXTRACT(YEAR FROM created_at) is the standard form; DATE_PART, YEAR() and DATEPART are vendor variants. The important caveat is that wrapping an indexed column in a date function makes the predicate non-sargable — for filtering, use a range like created_at >= '2026-01-01' AND created_at < '2027-01-01' instead.
62. How do you calculate the difference between two dates? 2–5 yrs
Subtraction works directly in Postgres and returns an interval; MySQL has DATEDIFF and TIMESTAMPDIFF; SQL Server has DATEDIFF with a unit argument. The interview point is usually to state the units explicitly, because a difference in days versus months is where off-by-one reporting bugs come from.
63. What is the difference between LIKE and a full-text search? 2–5 yrs
LIKE does simple pattern matching and cannot use a standard B-tree index when the pattern has a leading wildcard, so '%term%' forces a scan. Full-text search builds an inverted index over tokens, supports stemming and relevance ranking, and stays fast on large text — Postgres tsvector, MySQL FULLTEXT.
64. What does CAST do, and when do implicit conversions bite? 2–5 yrs
CAST converts a value between types explicitly. Implicit conversion happens when you compare mismatched types, and the danger is that the engine may convert the column rather than the literal — which disables the index on that column. Comparing a VARCHAR id to a numeric literal is the classic case.
65. How would you pivot rows into columns without a PIVOT clause? Senior
Conditional aggregation: SUM(CASE WHEN month = 'Jan' THEN amount ELSE 0 END) AS jan, repeated per column, with a GROUP BY on the row key. It is portable across every engine, unlike PIVOT, and it makes explicit that a pivot is just an aggregate per bucket.
66. How do you handle NULLs in aggregates and comparisons? Senior
Aggregates other than COUNT(*) skip NULLs, so AVG divides by the non-null count. Comparisons with NULL yield unknown, so use IS NULL / IS NOT NULL, and remember that NOT IN with a NULL in the list returns nothing. Use COALESCE deliberately where a NULL should count as zero — but only where that is genuinely true.
Set Operations & Advanced Queries
67. What are INTERSECT and EXCEPT? Fresher
INTERSECT returns rows appearing in both result sets; EXCEPT (MINUS in Oracle) returns rows in the first but not the second. Both deduplicate by default and require the same column count and compatible types. They express "in both" and "in A only" more clearly than the equivalent join or NOT EXISTS.
68. How do you write an upsert? 2–5 yrs
Postgres and SQLite use INSERT ... ON CONFLICT (key) DO UPDATE; MySQL uses INSERT ... ON DUPLICATE KEY UPDATE; the SQL standard is MERGE, which SQL Server and Oracle support. All require a unique constraint on the conflict target — without one there is nothing for the engine to detect.
69. How do you delete duplicate rows but keep one? 2–5 yrs
Number the duplicates with a window function — ROW_NUMBER() OVER (PARTITION BY the duplicate-defining columns ORDER BY id) — inside a CTE, then delete where the number is greater than 1. Doing it with a self join and MIN(id) also works but is easier to get subtly wrong.
70. How do you find rows in one table with no match in another? 2–5 yrs
LEFT JOIN and filter WHERE the right key IS NULL, or NOT EXISTS with a correlated subquery. NOT EXISTS is usually preferable because it is immune to the NULL trap that breaks NOT IN, and most planners handle it as an anti-join either way.
71. How do you get the top N rows per group? 2–5 yrs
A window function: ROW_NUMBER() OVER (PARTITION BY group_col ORDER BY sort_col DESC) in a subquery or CTE, then filter where the number is at most N. This is one of the most frequently asked SQL interview questions and the window-function answer is the one interviewers are looking for.
72. How do you calculate a running total? 2–5 yrs
SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). State the frame explicitly — the default frame for an ordered window is RANGE UNBOUNDED PRECEDING, which groups ties together and gives different numbers when dates repeat.
73. How do you calculate month-over-month growth? 2–5 yrs
Aggregate to monthly totals in a CTE, then use LAG(total) OVER (ORDER BY month) to get the previous month and compute the ratio. Guard against division by zero with NULLIF(prev, 0), and be explicit about whether a missing month should be zero or absent — that choice changes the answer.
74. What is a lateral join and when do you need one? Senior
LATERAL (CROSS APPLY in SQL Server) lets a subquery in the FROM clause reference columns from preceding tables, so it runs per outer row. It is how you express "the three most recent orders for each customer" as a join rather than a window function, and it is often faster when N is small relative to the table.
75. How would you write a query to find gaps in a sequence? Senior
Compare each row to the next with LEAD(id) OVER (ORDER BY id) and return rows where the next value is more than one greater — that gives you the boundaries of each gap. The generate-a-full-series-and-anti-join approach also works and is clearer when you need the missing values themselves rather than the ranges.
76. What is the difference between a derived table, a CTE and a temporary table? Senior
A derived table is an inline subquery in FROM, scoped to that query. A CTE is a named prefix, more readable and referenceable more than once, though whether it is materialised varies by engine. A temporary table is physically written, persists for the session, and can be indexed — worth it when an intermediate result is large and reused.
Practical SQL Problems
77. How do you find the second highest value in a column? Fresher
Cleanest: DENSE_RANK() OVER (ORDER BY col DESC) in a subquery, filtered to rank 2. Alternatives are ORDER BY col DESC LIMIT 1 OFFSET 1, or a subquery selecting MAX where the value is less than the overall MAX. Mention how ties should behave — that is what the question is really testing.
78. How do you find employees earning more than their manager? Fresher
Self join the employees table: e JOIN m ON e.manager_id = m.employee_id, then WHERE e.salary > m.salary. It is the standard self-join exercise, and the thing to say out loud is why you need two aliases of the same table.
79. How do you count rows per group including groups with zero? Fresher
A plain GROUP BY on the fact table cannot produce a zero row, because there is nothing to group. Start from the dimension table and LEFT JOIN the facts, then COUNT the fact key — COUNT of a nullable column returns 0 for unmatched groups, whereas COUNT(*) would return 1.
80. How do you find customers who bought product A but not product B? 2–5 yrs
Aggregate per customer with conditional counts and filter in HAVING: HAVING SUM(CASE WHEN product = 'A' THEN 1 ELSE 0 END) > 0 AND SUM(CASE WHEN product = 'B' THEN 1 ELSE 0 END) = 0. NOT EXISTS on a correlated subquery is the other clean form; NOT IN is the version that breaks on NULLs.
81. How do you calculate a median in SQL? 2–5 yrs
PERCENTILE_CONT(0.5) WHERE the engine supports it, otherwise NTILE or a ROW_NUMBER approach taking the middle row (or the average of the two middle rows for an even count). It is a good interview question precisely because there is no MEDIAN aggregate in standard SQL and the even-count case is easy to get wrong.
82. How do you find consecutive events — three logins in a row, for example? 2–5 yrs
Use LAG or LEAD to bring the neighbouring rows onto the same row and compare, or use the gaps-and-islands technique: subtract a ROW_NUMBER from the date to produce a constant group key for consecutive runs, then GROUP BY that key and filter on COUNT. The second generalises to any run length.
83. How would you write a query for daily active users over 30 days? 2–5 yrs
COUNT(DISTINCT user_id) grouped by the truncated date, filtered to the window. Join against a generated date series so days with no activity appear as zero rather than being missing. Be explicit about the timezone used for truncation — that single decision changes the numbers.
84. How do you find the first order for each customer? 2–5 yrs
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at) filtered to 1. The alternative — grouping to get MIN(created_at) then joining back — needs care, because two orders at the same timestamp will both match and duplicate the customer.
85. How would you paginate a large result set efficiently? Senior
Avoid large OFFSET values — the engine still walks and discards every skipped row, so page 5,000 is slow. Use keyset (cursor) pagination instead: WHERE (sort_key, id) > (last_key, last_id) ORDER BY sort_key, id LIMIT n. It stays constant-time and is stable when rows are inserted between pages.
86. How would you safely add a NOT NULL column to a large live table? Senior
In three steps: add it as nullable (cheap, metadata-only on modern engines), backfill in batches with pauses so you do not hold a long transaction or bloat the WAL, then add the NOT NULL constraint — validating it separately where the engine allows, as Postgres does with NOT VALID then VALIDATE. Doing it in one statement locks the table for the duration.
87. How would you find and fix a query that got slow after a data-volume increase? Senior
Compare the current plan against what you expect: a plan that was a nested loop over a small table may still be a nested loop over a large one because statistics are stale. Refresh statistics first, then check whether the index still matches the predicate's leftmost prefix and whether the query is now returning enough rows that a scan is genuinely cheaper. Plan regressions after growth are usually estimate problems, not missing-index problems.
88. When would you choose a materialised view over a query or a cache? Senior
When the result is expensive, reused often, and tolerant of staleness measured in minutes — a dashboard aggregate, for instance. A plain view saves nothing computationally; an application cache is faster but adds an invalidation problem outside the database. The materialised view keeps the freshness contract in one place, at the cost of refresh scheduling.
89. How do you find all departments with more than five employees? Fresher
GROUP BY department and filter with HAVING COUNT(*) > 5. The reason it must be HAVING rather than WHERE is that the count does not exist until after grouping — that is what the question is checking.
90. How do you get the total per group and the overall total in one query? Fresher
GROUP BY with ROLLUP, which adds a subtotal row where the grouped column is NULL. Use GROUPING(col) to distinguish a genuine NULL from a rollup marker. Without ROLLUP you need a UNION ALL of the grouped query and an ungrouped aggregate.
91. How do you select rows from the last seven days? Fresher
WHERE created_at >= CURRENT_DATE - INTERVAL '7 days' (syntax varies by engine). Keep the function on the literal side, not the column — WHERE DATE(created_at) >= ... prevents the index being used, which is the mistake the question is probing for.
92. How do you copy a table structure without the data? Fresher
CREATE TABLE new_table AS SELECT * FROM old_table WHERE 1 = 0 copies the columns but no rows, though it does not copy indexes or constraints. Engine-specific forms — CREATE TABLE ... LIKE in MySQL, INCLUDING ALL in Postgres — copy more of the definition.
93. How do you update one table from another? 2–5 yrs
UPDATE ... FROM in Postgres and SQL Server, a multi-table UPDATE with a join in MySQL, or MERGE where supported. Always check the join produces at most one source row per target row — if it matches several, most engines silently pick one, which makes the result non-deterministic.
94. How do you find rows where a value appears more than once? 2–5 yrs
GROUP BY the column with HAVING COUNT(*) > 1 to get the values, then join back to the table if you need the full rows. The window-function version — COUNT(*) OVER (PARTITION BY col) — gets you both in one pass without the join.
95. How would you calculate a 7-day rolling average? 2–5 yrs
AVG(value) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). Use ROWS, not RANGE, and make sure the series has no missing dates — otherwise "6 preceding rows" silently spans more than seven calendar days. Join a generated date series first if gaps are possible.
96. How do you write a query that returns a percentage of the group total? 2–5 yrs
Divide the row value by a windowed total: amount * 100.0 / SUM(amount) OVER (PARTITION BY group_col). The 100.0 matters — with integer types, integer division truncates to zero, which is one of the most common silent bugs in reporting SQL.
97. How do you handle a query that must return a row even when there is no data? 2–5 yrs
LEFT JOIN from a source that always has the row — a dimension table or a generated series — and COALESCE the aggregate to zero. Aggregates over an empty set return NULL, not zero, except COUNT which returns 0, and mixing those up produces gaps in charts.
98. How would you audit changes to a table? Senior
Either a trigger writing before and after values to a history table, or application-level event sourcing, or a temporal/system-versioned table where the engine supports it. Triggers are invisible to application developers, which is both their strength for guaranteed coverage and their weakness for debuggability — say which trade-off you are choosing.
99. How would you handle a table that has grown too large to query efficiently? Senior
In rough order: confirm the indexes match the real access patterns, then partition by the natural boundary (usually date) so queries prune and old data can be dropped cheaply, then archive cold rows to separate storage, and only then consider sharding. Sharding solves a capacity problem and creates a distributed-systems problem, so it should be last.
100. How do you prevent SQL injection? Senior
Parameterised queries or prepared statements, always — the driver sends the SQL and the values separately, so a value can never be parsed as syntax. String concatenation or interpolation of user input is the vulnerability, and escaping by hand is not a reliable substitute. ORMs parameterise by default, but raw-SQL escape hatches do not.
101. What is the difference between logical and physical database design? Senior
Logical design defines entities, attributes and relationships independently of any engine — normalised, conceptual, portable. Physical design decides the actual implementation: data types, indexes, partitioning, storage layout, and any deliberate denormalisation. Interviewers ask this to see whether you can separate what the data means from how it is stored.
Get these answered live in your real interview
NostrobeAI is a real-time AI interview copilot — it hears the question and drafts a strong answer on your screen, invisible on Zoom, Meet, and Teams. One-time pricing, no subscription.
Try NostrobeAI free