All jobs / Database administrator
SQL Fundamentals interview questions
SQL Fundamentals questions a Database administrator can be asked, from first job to senior. Open one to see the strongest answer and why it works.
What is the difference between an INNER JOIN and a LEFT JOIN?
The strongest answer: INNER returns only rows that match on both sides; LEFT returns every row from the left table and fills the right side with NULLs where there is no match — so LEFT is how you keep the customers with no orders
Why it works: Right. The question to ask is: do I want rows with no match to survive?
- INNER: matches on both sides only.
- LEFT: all left rows, NULLs for missing right.
- Use LEFT to keep the ones without a match.
- A WHERE on the right table can silently turn LEFT into INNER.
Why does WHERE status != 'closed' not return rows where status is NULL?
The strongest answer: Because NULL is unknown, and any comparison with NULL is neither true nor false — the row is filtered out; use IS NULL or COALESCE to include them deliberately
Why it works: Exactly. Three-valued logic: comparisons with NULL are unknown and WHERE keeps only true.
- NULL means unknown, not empty.
- Comparisons with NULL are unknown, so WHERE drops them.
- IS NULL / IS NOT NULL to test.
- COALESCE to give NULL a stand-in value.
When do you use HAVING instead of WHERE?
The strongest answer: HAVING filters after grouping, on aggregates — customers with more than five orders; WHERE filters rows before they are grouped
Why it works: Right. WHERE first on rows, GROUP BY, then HAVING on the groups.
- WHERE: rows, before grouping.
- HAVING: groups, after aggregation.
- COUNT, SUM, AVG live in HAVING.
- Order: WHERE → GROUP BY → HAVING.
What is a primary key, and what is a foreign key?
The strongest answer: A primary key uniquely identifies each row and cannot be NULL; a foreign key is a column that references another table's primary key, and the database enforces that the referenced row exists
Why it works: Exactly. Identity on one side, an enforced reference on the other.
- Primary key: unique, not NULL, identifies the row.
- Foreign key: references a primary key elsewhere.
- Referential integrity is enforced by the database.
- Keys are constraints; indexes are structures.
What does an index do, and what does it cost?
The strongest answer: It lets the database find rows by a column without scanning the whole table, like a book's index; it costs storage and makes every insert, update and delete on that column slower because the index must be maintained
Why it works: Right. Faster reads on that column, a tax on every write.
- Index: fast lookup on a column.
- Costs storage and write time.
- Index what you filter and join on.
- Every index is maintained on every write.
What is the difference between LIKE 'smith%' and LIKE '%smith'?
The strongest answer: The first matches values starting with smith and can use an index; the second matches values ending with smith and must scan every row because an index cannot help with a leading wildcard
Why it works: Right. A leading wildcard throws away the index.
- % matches any characters.
- Prefix patterns can use an index.
- Leading % forces a scan.
- Full-text search for real text queries.
A report shows duplicate customers. Is SELECT DISTINCT the fix?
The strongest answer: Usually not — duplicates from a join mean the join or the grouping is wrong; DISTINCT hides the symptom and costs a sort, so find why rows multiply first
Why it works: Right. DISTINCT on a wrong join is a bandage over a bug.
- Ask why rows are duplicated.
- A one-to-many join multiplies rows.
- DISTINCT hides the cause and costs a sort.
- Aggregate or fix the join instead.
What does ORDER BY ... LIMIT 10 without an index on the sort column cost?
The strongest answer: The database must read every matching row and sort them all before it can take ten; with an index on the sort column it can walk the index and stop after ten
Why it works: Right. Sort first, then limit — unless the index already provides the order.
- ORDER BY before LIMIT.
- Without an index: sort everything.
- Index on the sort column: stop after N.
- Top-N queries want the index.
What is the difference between COUNT(*) and COUNT(email)?
The strongest answer: COUNT(*) counts rows; COUNT(email) counts rows where email is not NULL, so the two differ whenever the column has NULLs
Why it works: Right. Column counts skip NULLs; star counts rows.
- COUNT(*): rows.
- COUNT(col): non-NULL values.
- COUNT(DISTINCT col): distinct non-NULL values.
- NULLs are the difference.
A table stores dates as text like `12/03/2024`. Why does that matter?
The strongest answer: Sorting and range queries break, time zones and formats are ambiguous, and every query has to parse — use the engine’s date or timestamp type
Why it works: Exactly — native types give correct ordering, arithmetic and validation for free.
- Use native date types.
- Text dates sort wrongly.
- Formats drift; time zones hide.
- Validation belongs in the type.
UNION versus UNION ALL — which do you reach for by default?
The strongest answer: UNION ALL, unless you need duplicates removed: UNION deduplicates, which means a sort or hash over the whole result, and most of the time the two sides cannot overlap anyway
Why it works: Right. ALL is cheaper; use plain UNION only when duplicates are a real possibility you want gone.
- UNION: deduplicates, costs a sort.
- UNION ALL: keeps everything, cheap.
- Default to ALL.
- Same column count and compatible types.
What does a transaction guarantee, in plain words?
The strongest answer: That a group of statements either all take effect or none do — a transfer debits one account and credits another or does neither — and that once committed the change is durable
Why it works: Right. All or nothing, and permanent once committed.
- All statements commit or none do.
- Committed means durable.
- Wrap multi-step changes in one transaction.
- Keep transactions short.
Why must user input go into a query as a parameter rather than pasted into the string?
The strongest answer: Because a parameter is sent as data and can never change the query's structure; string concatenation lets ' OR 1=1 -- become part of the SQL, which is SQL injection
Why it works: Exactly. Parameters keep data as data.
- Never concatenate user input into SQL.
- Parameters separate data from structure.
- Applies to every input and type.
- ORMs parameterise for you — until you write raw SQL.
Why does `WHERE status = NULL` return nothing?
The strongest answer: NULL means unknown, so comparing with equals yields unknown, not true — you need `IS NULL`, and I watch for the same trap in `NOT IN`
Why it works: Exactly — three-valued logic, and the same trap swallows rows in NOT IN and aggregates.
- NULL is unknown, not a value.
- Use IS NULL.
- NOT IN with NULLs is a trap.
- Standard behaviour, not a bug.
Is `SELECT *` in application code a problem?
The strongest answer: Yes for production code — it pulls columns nobody uses, breaks covering indexes and silently changes shape when someone adds a column; fine for exploring by hand
Why it works: Exactly — name the columns in code; star is for the console.
- Name the columns in code.
- Star breaks covering indexes.
- Schema changes shift the shape.
- Star is for the console.
A colleague is about to run `DELETE FROM orders` on production; the WHERE clause is missing from the pasted script.
What do you do before the statement runs?
The strongest answer: Stop and check together: wrap it in a transaction, run the matching SELECT and count first, confirm the WHERE clause, and make sure a restore path exists
Why it works: Exactly — a transaction and a count turn a possible disaster into a checked change.
- Transaction first.
- SELECT and count before DELETE.
- Confirm the predicate together.
- Backups are the last line, not the first.