SQL interview questions and answers

Question 1

What are the sublanguages of SQL?

Accepted Answer

DDL, Data Definition Language, defines structure: CREATE, ALTER, DROP, TRUNCATE, RENAME. DML, Data Manipulation Language, changes data: INSERT, UPDATE, DELETE, and in most classifications SELECT, though SELECT is often separated out as DQL, Data Query Language. DCL, Data Control Language, handles permissions: GRANT and REVOKE. TCL, Transaction Control Language, manages transactions: COMMIT, ROLLBACK, and SAVEPOINT. A key difference is that DDL statements are usually auto-committed and cannot be rolled back in many engines, while DML can. Analogy: building a house. DDL puts up the walls, DML moves the furniture in and out, DCL decides who gets a key, and TCL is the removals contract that either completes fully or is cancelled. In an interview: The auto-commit behaviour of DDL is the detail worth naming. It is why a failed migration can leave a schema half-changed, and why PostgreSQL's transactional DDL is a genuine operational advantage.

Question 2

What is the logical order of execution of a SELECT statement?

Accepted Answer

SQL is written in one order and evaluated in another. Logically the engine processes FROM and JOIN first to build the working set, then WHERE to filter rows, then GROUP BY to form groups, then HAVING to filter groups, then SELECT to compute the output expressions, then DISTINCT, then ORDER BY, and finally LIMIT or OFFSET. This ordering explains almost every beginner error, most importantly why a column alias defined in SELECT cannot be used in WHERE, but can be used in ORDER BY. Analogy: cooking. You gather ingredients, discard the bad ones, group them into dishes, reject dishes that are too small, plate up, then arrange the plates and serve only the first few. In an interview: This ordering is the single most useful thing to have memorised for SQL interviews. It answers alias-scope questions, WHERE-versus-HAVING questions, and why aggregate functions cannot appear in WHERE, all from the same diagram.

Question 3

What is the difference between WHERE and HAVING?

Accepted Answer

WHERE filters individual rows before any grouping happens, so it cannot reference aggregate functions. HAVING filters groups after GROUP BY has run, so it can and normally does reference aggregates. If a condition does not involve an aggregate, put it in WHERE, filtering earlier means fewer rows to group, which is faster. Analogy: a talent competition. WHERE is the door policy deciding who is allowed in at all. HAVING is the judging round that eliminates whole teams based on their combined score. In an interview: The performance point is the one that separates a good answer. Anything that can go in WHERE should go in WHERE, because it reduces the input to the grouping step rather than discarding work already done.

Question 4

How do GROUP BY and aggregate functions work?

Accepted Answer

GROUP BY collapses rows that share the same values in the listed columns into a single output row per group. Aggregate functions then summarise each group: COUNT, SUM, AVG, MIN, MAX. The rule in standard SQL is that every column in the SELECT list must either appear in the GROUP BY clause or be inside an aggregate function, because anything else would be ambiguous, the engine would not know which row's value to show. Analogy: sorting receipts into piles by month, then writing one summary line per pile. You can report the month and the total, but not 'the shop name' unless every receipt in that pile came from the same shop. In an interview: MySQL historically allowed selecting ungrouped columns and returned an arbitrary value, which produced silently wrong reports. Modern versions reject it by default under ONLY_FULL_GROUP_BY, which is the correct behaviour.

Question 5

What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)?

Accepted Answer

COUNT(*) counts every row in the group, including rows that are entirely NULL. COUNT(column) counts only the rows where that column is not NULL. COUNT(DISTINCT column) counts the number of different non-null values. This is one of the most common sources of quietly wrong numbers in reports, because the three can all return different results on the same data. Analogy: counting people in a room. COUNT(*) counts everyone. COUNT(badge) counts everyone wearing a badge. COUNT(DISTINCT company) counts how many companies are represented. In an interview: The AVG behaviour is the dangerous one. AVG ignores NULLs entirely rather than treating them as zero, so an average over a column with missing values is an average of the present values only, which may or may not be what the business meant.

Question 6

Explain DISTINCT, ORDER BY, LIMIT and OFFSET.

Accepted Answer

DISTINCT removes duplicate rows from the result, considering all selected columns together, not just the first one. ORDER BY sorts the result, ascending by default, and you can sort by several columns or by position. LIMIT restricts how many rows come back and OFFSET skips rows before starting, which together give pagination. Because a relation is unordered, a query without ORDER BY has no guaranteed row order, so LIMIT without ORDER BY returns arbitrary rows. Analogy: a list of search results. Deduplicate, sort by relevance, then show results 21 to 30. Skipping the sort step means page two might contain the same entries as page one. In an interview: For deep pagination, OFFSET gets slower the further you go because the engine still has to produce and discard every skipped row. Keyset pagination, filtering on the last seen sort value, is the scalable alternative.

Question 7

What are the main SQL operators and predicates?

Accepted Answer

Comparison operators are equals, not equals, greater and less than. Logical operators are AND, OR, and NOT. BETWEEN tests an inclusive range. IN tests membership in a list or subquery. LIKE does pattern matching, where percent matches any sequence of characters and underscore matches exactly one. IS NULL and IS NOT NULL test for missing values. EXISTS tests whether a subquery returns any row at all. Analogy: a set of filters on a shopping site. Price between two values, brand in a checklist, product name containing a word, and 'has a discount' as a yes-or-no flag. In an interview: That last point is worth volunteering. A LIKE pattern with a leading wildcard cannot use a normal B-tree index, which is why a naive search box on a large table is often the slowest query in the system.

Question 8

What do CASE, COALESCE, NULLIF and CAST do?

Accepted Answer

CASE is SQL's conditional expression, it evaluates conditions in order and returns the first matching result, which lets you do if-then-else inside a query. COALESCE returns the first non-null argument from a list, so it is the standard way to supply a default for a missing value. NULLIF returns NULL if two expressions are equal, which is the classic guard against division by zero. CAST converts a value from one data type to another. Analogy: CASE is a decision tree. COALESCE is a fallback chain, try the mobile, then the landline, then the office. NULLIF is a safety catch that turns a dangerous value into a harmless blank. In an interview: The conditional aggregate pattern in the last line is one of the most useful things in practical SQL, it lets you produce several differently-filtered counts in a single pass over the table instead of several queries.

Question 9

What is the difference between the SQL data types CHAR, VARCHAR and TEXT?

Accepted Answer

CHAR is fixed length, it pads shorter values with spaces and always occupies the declared width, which makes it fast for genuinely fixed-size codes. VARCHAR is variable length, storing only the characters present plus a small length prefix, and is the sensible default for most text. TEXT or CLOB types store very large values, often out of line from the row, and frequently come with restrictions such as not being fully indexable. Analogy: CHAR is a printed form with fixed-width boxes for each letter. VARCHAR is a ruled line where you write as much as you need. TEXT is an attached sheet referenced from the form. In an interview: The comparison padding is the trap. Trailing-space semantics differ between CHAR and VARCHAR and between vendors, so mixing them in a join key can produce rows that mysteriously fail to match.

Question 10

What is a join and what are the types?

Accepted Answer

A join combines rows from two or more tables based on a related column. INNER JOIN returns only rows where the condition matches in both tables. LEFT OUTER JOIN returns all rows from the left table plus matches from the right, with NULLs where there is no match, and RIGHT OUTER JOIN is the mirror image. FULL OUTER JOIN returns all rows from both sides. CROSS JOIN returns the Cartesian product, every row paired with every row. A SELF JOIN is a table joined to itself using aliases. Analogy: matching two guest lists. Inner join is the people on both lists. Left join is everyone on the first list, with a note about whether they are on the second. Full join is everyone from either list. Cross join is every possible pairing for a seating chart. In an interview: The classic follow-up is finding rows with no match. A LEFT JOIN with a WHERE clause testing that the right-hand key IS NULL is the anti-join idiom, and it is usually faster than NOT IN.

Question 11

What is the difference between a WHERE condition and an ON condition in an outer join?

Accepted Answer

In an INNER JOIN it makes no practical difference. In an OUTER JOIN it changes the result completely. The ON condition decides which rows get matched, and unmatched rows from the preserved side are still kept with NULLs. The WHERE condition runs after the join, and since NULL fails almost any comparison, filtering on a column from the optional side in WHERE silently converts your outer join into an inner join. Analogy: ON is the matchmaking rule. WHERE is the bouncer standing after the matchmaking, and the bouncer throws out everyone who did not get matched, undoing the point of the outer join. In an interview: This is one of the most common real bugs in reporting SQL, because the query still runs and still returns plausible-looking rows, it just quietly loses the customers who bought nothing, which is often exactly the group the report was about.

Question 12

What is a subquery and what types are there?

Accepted Answer

A subquery is a query nested inside another. A scalar subquery returns a single value and can be used anywhere a value is expected. A row or table subquery returns multiple rows and is used with IN, ANY, or ALL, or in the FROM clause as a derived table. The key distinction is between a non-correlated subquery, which can be evaluated once independently, and a correlated subquery, which references a column from the outer query and therefore is conceptually re-evaluated for each outer row. Analogy: a non-correlated subquery is looking up a fact once and writing it on a sticky note. A correlated subquery is having to make a fresh phone call for every single row you process. In an interview: Modern optimisers often rewrite correlated subqueries into joins, so the performance gap is smaller than it used to be, but it is not guaranteed, and on large tables an explicit join or window function is still the safer choice.

Question 13

When would you use IN, EXISTS, or a JOIN?

Accepted Answer

IN checks membership in a list of values and is clearest when the subquery returns a small, fixed set. EXISTS checks only whether at least one matching row exists and can stop as soon as it finds one, which makes it better for large correlated checks. A JOIN is right when you actually need columns from the other table in the output. The critical difference is NULL handling: NOT IN returns no rows at all if the subquery produces even one NULL, whereas NOT EXISTS behaves correctly. Analogy: IN is checking a name against a printed guest list. EXISTS is asking the doorman 'is anyone from that company here?' and being satisfied by the first yes. In an interview: I default to NOT EXISTS over NOT IN purely for the NULL safety. The failure mode of NOT IN is silent and total, an empty result set rather than an error, which makes it very hard to notice in testing.

Question 14

What is the difference between UNION and UNION ALL, and what are INTERSECT and EXCEPT?

Accepted Answer

UNION combines the results of two queries and removes duplicates, which requires a sort or hash and therefore costs time. UNION ALL concatenates them keeping all duplicates, and is significantly faster because no deduplication happens. INTERSECT returns only rows appearing in both results, and EXCEPT, called MINUS in Oracle, returns rows in the first result that are not in the second. All of them require both queries to have the same number of columns with compatible types. Analogy: UNION is merging two guest lists and crossing out repeats. UNION ALL is stapling the two lists together. INTERSECT is the people on both. EXCEPT is the people on the first list only. In an interview: The rule I follow is to use UNION ALL unless duplicates are genuinely possible and genuinely unwanted. People write UNION reflexively and pay for a deduplication sort that removes nothing.

Question 15

How do you find the second highest salary?

Accepted Answer

There are three standard approaches. The subquery approach finds the maximum salary that is less than the overall maximum. The LIMIT with OFFSET approach sorts distinct salaries descending and skips one. The window function approach uses DENSE_RANK, which is the most flexible because it generalises to the Nth value and handles ties in a defined way. The important discussion point is what should happen with duplicate salaries and what to return when there is no second value. Analogy: finding the runner-up. If two athletes tied for first, is the runner-up the third person across the line, or is there no runner-up at all? The query has to take a position. In an interview: I would point out that RANK and DENSE_RANK behave differently with ties, RANK leaves gaps, DENSE_RANK does not, so which one is correct depends on whether tied employees should both count as first place.

Question 16

How do you find and delete duplicate rows?

Accepted Answer

To find duplicates you group by the columns that define sameness and keep groups with a count above one. To delete them while keeping one copy, the reliable method is a window function that numbers the rows within each duplicate group and then deletes everything numbered above one. If the table has no unique identifier at all, some engines expose a physical row identifier you can use instead. Analogy: sorting a pile of forms into stacks of identical ones, then keeping the top form from each stack and shredding the rest. In an interview: The last statement is the part people forget. Deleting duplicates without adding a unique constraint just means you will be running the same cleanup again in three months.

Question 17

What is a self join and when do you need one?

Accepted Answer

A self join is a table joined to itself, using different aliases so the engine can treat the two references as separate. You need it whenever rows in one table relate to other rows in the same table, an employee's manager who is also an employee, a category's parent category, or comparing each row against another row in the same set. It is an ordinary join; the only special thing is that aliases become mandatory rather than optional. Analogy: a family tree stored as one list of people, where each entry names another entry as their parent. To print names alongside parents' names you have to read the same list twice. In an interview: The less-than condition in that last example is the idiom for generating unordered pairs without duplicates or self-matches. Using not-equals instead would give you every pair twice.

Question 18

What is a view and why use one?

Accepted Answer

A view is a stored query that behaves like a virtual table. It holds no data of its own, when you query it, the DBMS substitutes the underlying query and runs that. Views serve three purposes: security, because you can grant access to a view exposing only certain columns or rows without granting access to the base table; simplicity, because a complicated multi-table join becomes a single name; and logical independence, because you can restructure the base tables and keep the view definition compatible so applications do not break. Analogy: a saved filter on a spreadsheet. It looks like its own sheet, but it is really just a lens onto the master data, and it updates automatically whenever the master changes. In an interview: The security use is the strongest justification. Row-level and column-level restrictions through views let you give a reporting team real access without ever exposing the sensitive columns, and the restriction is enforced by the database, not by trust.

Question 19

Can you update a view, and what is a materialised view?

Accepted Answer

A simple view is updatable if the DBMS can unambiguously map each modified row back to exactly one row in one base table, so no aggregates, no GROUP BY, no DISTINCT, no set operators, and usually no joins. Complex views are read-only unless you attach an INSTEAD OF trigger defining what an update should mean. A materialised view is different: it physically stores the query result on disk, so reads are fast, but the data is a snapshot that must be refreshed and can be stale. Analogy: a normal view is a window into the room, always current. A materialised view is a photograph of the room, instant to look at, but only as accurate as the moment it was taken. In an interview: There is also WITH CHECK OPTION, which stops an update through a view from producing a row that would no longer be visible in that view, otherwise you can update a row straight out of your own view and it silently disappears.

Question 20

What is an index and how does it speed up queries?

Accepted Answer

An index is a separate data structure, usually a B+ tree, that stores the indexed column values in sorted order along with pointers to the corresponding rows. Instead of scanning every row to find matches, the engine descends the tree in logarithmic time. Indexes accelerate WHERE filters, JOIN conditions, ORDER BY, and GROUP BY. The cost is extra storage and slower writes, because every insert, update, or delete must also maintain every affected index. Analogy: the index at the back of a textbook. Without it you read all 600 pages looking for a word. With it you check one alphabetical list and jump straight to page 412, but every time the book is revised, the index has to be rebuilt too. In an interview: The leftmost-prefix rule is the detail that separates people who have used indexes from people who have only read about them. A composite index on two columns is useless for a query filtering on the second column alone.

Question 21

What is the difference between a stored procedure and a function?

Accepted Answer

A stored procedure is a named block of SQL stored in the database and executed with CALL or EXECUTE. It may return zero, one, or many result sets, can have input and output parameters, and is allowed to modify data and control transactions. A function must return a single value or table, is called inside an expression like any built-in function, and in standard SQL should not modify data, which is precisely why it can be used inside a SELECT or WHERE clause and a procedure cannot. Analogy: a procedure is an instruction you give someone to go and do a job. A function is a question you ask mid-sentence and get an answer back that you use in the same sentence. In an interview: The benefits of both are the same: precompiled execution plans, reduced network round trips, centralised business logic, and protection against SQL injection because parameters are never concatenated into text.

Question 22

What is a trigger and when should you use one?

Accepted Answer

A trigger is a block of code that the database executes automatically in response to an event on a table, before or after an INSERT, UPDATE, or DELETE. Row-level triggers fire once per affected row; statement-level triggers fire once per statement. They are used for audit logging, enforcing complex rules that constraints cannot express, and maintaining derived data. The downside is that they are invisible at the call site, so a simple INSERT can have surprising side effects that are hard to debug. Analogy: a motion sensor light. Nobody switched it on, and the person walking in may not even realise it exists, it just fires whenever the condition is met. In an interview: My rule is to use triggers for auditing, where invisibility is actually a feature because you do not want application code to be able to skip the audit, and to be very cautious about using them for business logic.

Question 23

What is a cursor and why is it usually a last resort?

Accepted Answer

A cursor is a pointer into a result set that lets you process rows one at a time in procedural code, declare, open, fetch in a loop, close. It exists because sometimes per-row logic genuinely cannot be expressed as a set operation. It is a last resort because SQL engines are optimised for set-based work, and a cursor forces row-by-row processing that can be orders of magnitude slower, holds locks longer, and consumes server memory. Analogy: moving a pile of bricks one at a time by hand when there is a forklift standing next to you. It works, and occasionally there is a reason for it, but it should never be the first instinct. In an interview: Before writing a cursor I would check whether the logic can be expressed with a CASE expression, a window function, or a MERGE statement. In my experience the answer is yes far more often than people assume.

Question 24

What is the difference between DELETE, TRUNCATE and DROP?

Accepted Answer

DELETE is a DML statement that removes rows one at a time according to a WHERE clause, logs each removal so it can be rolled back, and fires triggers. TRUNCATE is a DDL statement that deallocates the data pages of the whole table at once, much faster, minimally logged, usually not rollback-able, does not fire row triggers, and typically resets auto-increment counters. DROP is DDL that removes the table definition itself along with all its data, indexes, and constraints. Analogy: DELETE is taking specific items out of a filing cabinet and noting each one in a ledger. TRUNCATE is tipping the whole cabinet into a skip. DROP is removing the cabinet from the building entirely. In an interview: One extra detail worth naming: TRUNCATE normally fails if another table has a foreign key referencing yours, whereas DELETE will attempt the operation and apply the referential action.

Question 25

What constraints can you define on a table?

Accepted Answer

NOT NULL requires a value. UNIQUE forbids duplicate values across rows. PRIMARY KEY combines NOT NULL and UNIQUE and designates the row identifier. FOREIGN KEY enforces referential integrity against another table. CHECK enforces a boolean condition on each row. DEFAULT supplies a value when none is given, which is technically a default rather than a constraint. Constraints can be named, which matters because a named constraint produces a readable error and can be dropped by name later. Analogy: validation rules on a form. Required fields, no duplicate reference numbers, valid department codes, and totals that must be positive, enforced by the form itself rather than trusted to whoever fills it in. In an interview: Naming constraints explicitly is a small habit with a big payoff. An error mentioning chk_status tells you immediately what went wrong, whereas an auto-generated name tells you nothing.

Question 26

What is a CTE and how does a recursive CTE work?

Accepted Answer

A Common Table Expression, written with the WITH keyword, defines a named temporary result set that exists only for the duration of the query. It makes complex queries readable by letting you build them in named stages instead of nesting subqueries. A recursive CTE has two parts joined by UNION ALL: an anchor member that produces the starting rows, and a recursive member that references the CTE itself, repeating until it returns no new rows. It is the standard way to walk hierarchies and graphs. Analogy: a CTE is naming your intermediate steps in a long calculation instead of writing one enormous expression. A recursive CTE is following a chain of references, each person's manager, then their manager, and so on until you reach the top. In an interview: The safety point is that a recursive CTE over data containing a cycle will loop forever, so in production you either add a depth limit or track the visited path in an array column.

Question 27

What are window functions and how do they differ from GROUP BY?

Accepted Answer

A window function computes a value across a set of rows related to the current row, but unlike GROUP BY it does not collapse those rows, every input row stays in the output with the computed value attached. The OVER clause defines the window: PARTITION BY splits the rows into groups, ORDER BY orders them within each partition, and a frame clause can restrict which rows around the current one are included. Common functions are ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, and the aggregates used as windows. Analogy: GROUP BY is summarising a class into one line per class. A window function is writing each pupil's rank in the class next to their own name, keeping every pupil on the sheet. In an interview: Window functions run after WHERE, GROUP BY, and HAVING but before ORDER BY, which is why you cannot filter on a window function directly, you have to wrap the query in a CTE or subquery first.

Question 28

What are the pros and cons of stored procedures?

Accepted Answer

Answer: Pros: logic runs next to the data so it can be fast for heavy multi-step work; the network round trips drop to one call; you can grant access to the procedure without granting access to the tables; and the same logic is reused by any client. Cons: the logic is now split between your application code and the database, which is harder to test, version, and review; stored procedure languages (PL/SQL, T-SQL) are database-specific, so you are locked in; large amounts of business logic in the database are hard to refactor; and it can hide load, since a simple-looking CALL might do a lot of work. Common modern practice: keep business logic in the application, and use stored procedures sparingly for data-heavy operations where the round-trip savings are real. Analogy: a stored procedure is like keeping a recipe in the kitchen rather than at the table. Great for a dish the kitchen makes constantly, awkward when the recipe keeps changing and needs review. In an interview: Pros: speed for data-heavy work, fewer round trips, permission control, reuse. Cons: logic scattered between app and DB, vendor lock-in, and poor testability. I keep business logic in the app and use procedures only where the round-trip savings matter.

Question 29

How do you copy rows from one table into another?

Accepted Answer

Use INSERT ... SELECT. You list the target columns, then write a SELECT that produces matching columns in the same order. The SELECT can filter, join, and transform, so this is also how you back-fill a new column or move rows into an archive table. There is no VALUES keyword, the rows come from the query. Analogy: photocopying selected pages from one binder straight into another, rearranging or skipping some as they go through the machine. In an interview: INSERT ... SELECT is the bulk path: one statement, one transaction, the database does the row movement. I would contrast it with looping in application code, which is slower and not atomic.

Question 30

How do you do an “upsert”, insert or update if it already exists?

Accepted Answer

An upsert writes a row, but if a row with the same key already exists it updates that row instead of failing on the unique-constraint violation. The syntax is engine-specific. PostgreSQL and SQLite use INSERT ... ON CONFLICT ... DO UPDATE. MySQL uses INSERT ... ON DUPLICATE KEY UPDATE. The SQL standard and SQL Server use MERGE. All of them need a unique or primary key to detect the conflict. Analogy: saving a contact on your phone. If the number is new it adds a card; if it matches someone you already have, it updates their card. One action, two possible outcomes. In an interview: The concept is one atomic insert-or-update keyed on a unique column. I would mention that MERGE has had concurrency and bug issues in some engines, so on Postgres I prefer ON CONFLICT.

Question 31

How do you update a table using values from another table?

Accepted Answer

You cannot always just write UPDATE ... JOIN, the syntax differs by engine. PostgreSQL uses UPDATE ... FROM. MySQL allows UPDATE with a join directly. Standard SQL uses a correlated subquery in the SET clause and usually in the WHERE clause too, so you only touch rows that actually have a match. Analogy: going through a class register and writing each pupil's new form-room next to their name, reading the new rooms off a separate list and matching by pupil ID. In an interview: The portable version is a correlated subquery, and the WHERE EXISTS matters, without it, rows with no matching customer get set to NULL instead of being left alone.

Question 32

What is three-valued logic and how does NULL break WHERE and unique constraints?

Accepted Answer

SQL comparisons return TRUE, FALSE, or UNKNOWN. Any comparison with NULL yields UNKNOWN, including NULL = NULL. WHERE only keeps rows where the condition is TRUE, so a predicate that evaluates to UNKNOWN drops the row. That is why you must write IS NULL, not = NULL, and why NOT IN with a NULL in the list returns no rows. For unique constraints, most engines treat NULLs as distinct from each other, so a UNIQUE column can hold many NULL rows. Analogy: a form where a blank field means 'not answered'. You cannot say two unanswered fields are 'equal', you simply do not know. So the system refuses to treat them as a match. In an interview: I would lead with 'NULL means unknown, not zero and not empty', then show the NOT IN trap, because it is the one that silently returns wrong results in interviews and in production.

Question 33

What is soft delete, and what are its trade-offs?

Accepted Answer

A soft delete marks a row as deleted, usually a deleted_at timestamp or an is_active flag, instead of running DELETE. The row stays in the table and every query must filter it out. The upside is recoverability, audit history, and keeping foreign-key references intact. The downside is that every query, index, and unique constraint now has to account for the flag, the table keeps growing, and forgetting the filter leaks 'deleted' data. Analogy: crossing a name off a list with pencil instead of tearing the page out. You can still read it, and you can rub it out later, but everyone reading the list has to remember to skip the crossed-out lines. In an interview: I treat soft delete as a real design decision, not a default. If the data has audit or recovery value it is worth the query overhead; otherwise a proper archive table or a hard delete with backups is cleaner.

Question 34

How do you read an execution plan from EXPLAIN?

Accepted Answer

EXPLAIN shows the steps the optimiser chose to run a query; EXPLAIN ANALYZE actually runs it and adds real timings and row counts. You read it as a tree, from the innermost/most-indented node outward. Look for: the access method (sequential/full scan vs index scan vs index-only scan), the join method (nested loop, hash join, merge join), the estimated vs actual row counts (a big gap means bad statistics), and which node consumes the most time. Analogy: a route the sat-nav picked, with the reasoning shown: 'take the motorway here, expect 2000 cars, this junction costs the most time'. If the real traffic is wildly different from the estimate, the route was chosen on bad information. In an interview: My checklist is: scan type, join type, estimate-versus-actual rows, and the most expensive node. A sequential scan on a big table with a selective filter, or a nested loop over many rows, is usually where the fix is.

Question 35

Your query has an index on the column but still does a full scan. Why?

Accepted Answer

Common causes: the predicate is not sargable, the column is wrapped in a function or arithmetic (WHERE YEAR(d) = 2024), or there is an implicit type cast (a string literal against an integer column). A leading wildcard LIKE '%foo' cannot use a normal B-tree. The query returns a large fraction of the table, so a scan is genuinely cheaper. Statistics are stale so the optimiser misjudges selectivity. Or the index order does not match, a composite index on (a, b) cannot serve a query filtering only on b. Analogy: a phone book is sorted by surname. 'Find everyone whose surname starts with Mc' is fast. 'Find everyone whose surname contains ack' or 'whose surname has 7 letters' forces you to read every entry, the sort order does not help. In an interview: The word I would use is sargable. Keep the indexed column bare on one side of the comparison, match literal types exactly, and remember composite indexes are usable left-to-right only.

Question 36

What is the N+1 query problem and how do you fix it in SQL?

Accepted Answer

N+1 is running one query to get a list of N rows, then one more query per row to fetch related data, 1 + N round trips. It usually comes from ORM lazy loading inside a loop. Each query may be fast, but the network round trips dominate and it does not scale. The fix is to fetch everything in one query with a JOIN, or in two queries, the list, then all the children WHERE parent_id IN (...), and stitch them in memory. Analogy: shopping by calling the shop, asking for the list of items, then hanging up and calling back separately to ask the price of each one. Ask for the list with prices in a single call. In an interview: I describe it as 1 + N round trips from a loop of queries, name lazy loading as the usual cause, and say the fix is to batch: one join, or a second query with an IN list.

Question 37

Why does OFFSET-based pagination get slow, and what is keyset pagination?

Accepted Answer

LIMIT 20 OFFSET 100000 still makes the database produce and discard the first 100,000 rows before returning 20, cost grows with page number. Keyset (or 'seek') pagination instead remembers the last row's sort key and asks for rows after it: WHERE (created_at, id) < (last_seen_values) ORDER BY created_at DESC, id DESC LIMIT 20. With an index on the sort columns each page is the same cost regardless of how deep you are. The trade-off is you cannot jump to an arbitrary page number. Analogy: reading a long book. OFFSET is 'start at page 4000' by flipping through every page to count to 4000. Keyset is a bookmark, you open straight to where you left off. In an interview: OFFSET is O(offset); keyset is O(page size). For infinite scroll or 'load more' I use keyset with a composite index on the sort key. OFFSET is fine only for shallow, numbered pages.

Question 38

What does SELECT * cost you compared with naming columns?

Accepted Answer

SELECT * reads and transfers every column, including large TEXT/BLOB fields you did not want, which increases I/O and network cost. It defeats covering indexes, the database cannot answer from the index alone if you demand columns not in it. It makes the result shape fragile: adding a column silently changes what your code receives, and it can break INSERT ... SELECT or views. Naming columns is self-documenting and lets the optimiser do index-only scans. Analogy: ordering 'one of everything' at a restaurant when you wanted the soup. More waiting, a bigger bill, and food you did not want. In an interview: In production code I name columns. SELECT * is fine for ad-hoc exploration, but in a query the app runs it costs I/O, blocks covering-index optimisations, and makes the result contract unstable.

Question 39

How do you get a running (cumulative) total?

Accepted Answer

Use a window aggregate: SUM(amount) OVER (ORDER BY the_date). The ORDER BY inside OVER turns the aggregate into a running one, for each row it sums that row and all earlier rows in the order. Add PARTITION BY to restart the total per group (per account, per customer). The default frame with ORDER BY is 'from the start up to the current row', which is exactly a running total. Analogy: a bank statement's balance column: each line shows the sum of everything up to and including that transaction, not just that transaction. In an interview: I reach for SUM(...) OVER (ORDER BY ...). The key point to state is that adding ORDER BY inside OVER changes it from a grand total to a cumulative one, and PARTITION BY resets it per group.

Question 40

How do you get the top N rows per group (e.g. top 3 salaries per department)?

Accepted Answer

This is 'greatest-n-per-group'. The standard solution is a window function: number the rows within each group with ROW_NUMBER() OVER (PARTITION BY group ORDER BY metric DESC), wrap it in a CTE or subquery, then filter WHERE rn <= N. Use RANK or DENSE_RANK instead of ROW_NUMBER if you want ties to share a place. Analogy: medal ceremonies run separately for each sport. Within each sport you rank the athletes and keep the top three; you do not rank across all sports at once. In an interview: Window function, partition by the group, order by the metric, filter on the row number in an outer query. I would call out the ROW_NUMBER vs RANK vs DENSE_RANK choice because interviewers usually follow up on ties.

Question 41

How do you find gaps, or consecutive streaks, in a sequence?

Accepted Answer

Two classic tricks. For gaps: compare each row to the next using LAG or LEAD over an ordered window, and keep rows where the difference is more than one. For streaks ('islands'): the difference between an increasing id and a ROW_NUMBER over the ordered rows is constant within a run of consecutive values, so you GROUP BY that difference. This is the 'gaps and islands' problem. Analogy: seat numbers on a train. Empty seats between two booked seats are the gaps. A block of consecutively numbered booked seats is an island, and you spot a block because 'seat number minus its position in your list' stays the same across the block. In an interview: I name it as the gaps-and-islands pattern. Gaps come from LEAD/LAG plus a difference filter; islands come from the 'value minus row_number is constant within a run' trick, then GROUP BY that constant.

Question 42

How do you pivot rows into columns (e.g. monthly totals across columns)?

Accepted Answer

Conditional aggregation: SUM(CASE WHEN month = 'Jan' THEN amount END) AS jan, one expression per target column, with a GROUP BY on whatever stays as rows. Some engines have a PIVOT keyword (SQL Server, Oracle) but the CASE form is portable and clear. The number of output columns must be known when you write the query, a truly dynamic pivot needs generated SQL. Analogy: turning a long shopping receipt into a spending-by-category table: one row per shop, a column for groceries, a column for fuel, filling each cell by adding up only the matching lines. In an interview: Conditional aggregation with CASE inside SUM. I mention the PIVOT keyword exists but the CASE version is portable, and that the columns have to be hard-coded unless you build the SQL dynamically.

Question 43

How do you find rows in one table with no match in another?

Accepted Answer

This is an anti-join. Three common ways: LEFT JOIN the other table and keep rows WHERE the joined key IS NULL; NOT EXISTS with a correlated subquery; or NOT IN (but only if the subquery column can never be NULL, or NOT IN returns nothing). NOT EXISTS is usually the clearest and the safest, and optimisers handle it well. Analogy: checking a guest list against who actually arrived, to find the no-shows: names on the list with no matching check-in. In an interview: I call it an anti-join and default to NOT EXISTS. LEFT JOIN ... IS NULL is equivalent; NOT IN I avoid unless the inner column is guaranteed NOT NULL because a single NULL makes it return nothing.

Question 44

How do you calculate a median in SQL?

Accepted Answer

There is no portable MEDIAN aggregate. Options: PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x) where supported (Postgres, Oracle, standard SQL), this interpolates for even counts. Otherwise use a window function: number the rows by value ascending and descending, and the median rows are where the two numberings are within one of each other; average them. MySQL before 8.0 has neither and needs the row-numbering trick. Analogy: lining people up by height and picking the person in the middle, and if there are two middle people, taking the average of their heights. In an interview: If PERCENTILE_CONT exists I use it. Otherwise I explain the two-way row-numbering trick: the middle one or two rows are where the ascending and descending positions meet, and I average them for even counts.

SQL interview preparation

SQL fundamentals

Joins, subqueries & set operations

Writing data & schema

Views, indexes, procedures & triggers

Query performance

Common interview queries