💼 Interview Q&A

Interview Q&A: Think of a SQL interview as the driving part of a licence exam. The written test asks "what does a red light mean?"; the driving test puts you in a narrow street i

Think of a SQL interview as the driving part of a licence exam. The written test asks "what does a red light mean?"; the driving test puts you in a narrow street in the rain and asks you to park. You cannot pass the second one by memorisation, because what is being tested is not knowledge but the ability to use knowledge under pressure. So why aren't definitions enough — can't someone who correctly explains "the difference between INNER JOIN and LEFT JOIN" do the job? What the interviewer is actually trying to learn is: where do you start when something is wrong? Anyone can memorise "LEFT JOIN preserves the left table"; far fewer people, told that "the report shows 9 testers where it should show 12", reach for the join type as their first instinct. That is why the questions drift toward scenarios: "400 tests failed in last night's run — starting from the database, where do you look?" The Java equivalent is familiar: "how does `HashMap` work" separates fewer candidates than "why does this code throw `ConcurrentModificationException`". In both languages the discriminator is not the definition, it is the order in which you think when facing a failure. The questions in this section are therefore scenario-based, and their answers show the chain of reasoning: which query you wrote and why, how you read the result, what you eliminated next. Don't open the solution before writing your own answer — in a real interview the gap that matters is between knowing the answer and being able to produce it.

Click each question to expand the model answer. Includes code examples.

🎬 Reading an EXPLAIN Plan in an Interview

Question: "Why Is This Query Slow?"

type: ALL (Full Scan)

CREATE INDEX Suggestion

A classic interview scenario question: "This query runs very slowly in production, what do you do?" A weak answer just says "I add an index" — a STRONG answer asks for EVIDENCE first.

Layer 1 of the strong answer: "First I inspect the query plan with `EXPLAIN`" — observing WHAT the engine does instead of guessing.

The output shows `type: ALL` — this means the engine scans EVERY row (Full Table Scan). The candidate must be able to stop here and say "here is the evidence", not just "it's slow".

Layer 2 — solution + rationale: "I add an index on the `status` column used in WHERE, because then `EXPLAIN` switches to `type: ref` and the engine reads only the relevant rows, not EVERY row."

Final — the formula: gather EVIDENCE (EXPLAIN) → NAME the problem (Full Scan) → give solution + RATIONALE (why the index flips it to type: ref). The candidate who explains WHY wins, not the one who memorized a command name.

Step by Step: Building an Answer to a SQL Scenario Question

Clarify the situation

Which query, what table size, how long has it been slow? Asking questions first shows depth.