SQL Interview: Normalisation, NULL and Query Optimisation

Normalisation

Q. What is normalisation and why does it matter?

Restructuring tables so each fact is stored exactly once. It matters because redundancy makes contradiction possible: the same fact in two places can disagree. Normalisation eliminates the insert, update and delete anomalies that follow.

Q. Define 1NF, 2NF and 3NF.

• 1NF - every column holds a single atomic value; no repeating groups.
• 2NF - in 1NF, and every non key column depends on the whole primary key. Only composite keys can violate it.
• 3NF - in 2NF, and no non key column depends on another non key column.

The memorable form: every non key attribute depends on the key, the whole key, and nothing but the key.

Q. What is BCNF and how does it differ from 3NF?

BCNF requires every determinant to be a superkey. 3NF allows an exception when the dependent attribute is part of some candidate key; BCNF removes that exception. The difference only shows up with overlapping candidate keys - and BCNF decomposition, unlike 3NF, is not always dependency preserving. That is why 3NF is the usual practical target.

Q. Name the three anomalies.

• Insert - a fact cannot be recorded until an unrelated one exists (a course with no students yet cannot be stored).
• Update - one change must be applied in many rows, and one may be missed.
• Delete - removing one fact destroys another (the last student leaving erases the course).

Q. When would you denormalise?

When a measured read cost justifies it, the duplicated value changes rarely, and there is a mechanism keeping the copy correct plus a reconciliation query proving it still is. Note that order_items.unit_price is not denormalisation - the price at the time of sale is a different fact from the current price.

NULL

Q. What does NULL mean?

Unknown or not applicable. It is not zero, not an empty string, and not equal to itself - NULL = NULL evaluates to UNKNOWN.

Q. How do you test for NULL?

IS NULL and IS NOT NULL, only. = NULL is always UNKNOWN and matches nothing.

Q. What is three valued logic?

Conditions evaluate to TRUE, FALSE or UNKNOWN. WHERE keeps a row only when the result is TRUE, so UNKNOWN rows are dropped exactly like FALSE ones. The consequence worth stating: a condition and its negation do not partition the table - rows with NULL fall through both.

-- Neither query returns the employee whose dept_id is NULL
SELECT * FROM employees WHERE dept_id =  10;
SELECT * FROM employees WHERE dept_id <> 10;

-- To cover every row you must say so
SELECT * FROM employees WHERE dept_id <> 10 OR dept_id IS NULL;

Q. Where are NULLs treated as equal?

In GROUP BY and DISTINCT - all NULLs form one group and count as one value - and in UNION de duplication. In comparisons and joins they are not equal to anything. Both behaviours are in the standard and both are intentional.

Q. Does a UNIQUE constraint allow NULLs?

In MySQL, PostgreSQL, Oracle and SQLite, several - because two NULLs are not "the same value". SQL Server allows exactly one.

Query optimisation

Q. A query is slow. Walk me through what you do.

This is the question they are really asking. Answer it as a process, not a trick:

1. Confirm it matters. Check the slow query log ordered by total time - a 50ms query run a million times beats a 5 second nightly report.
2. Read the plan. EXPLAIN: look at type, key, rows and Extra. Compare rows examined with rows returned.
3. Form one hypothesis. Missing index, non sargable predicate, join with no index on the inner side, stale statistics, or simply returning too much.
4. Change one thing.
5. Re measure on production sized data and compare both plans.

Q. What are the usual causes?

• No index on a WHERE or JOIN column.
• A function wrapped around an indexed column.
• SELECT * preventing a covering index.
• Joining before aggregating instead of after.
• Deep OFFSET pagination.
• Stale statistics giving the optimiser a wrong picture.

Q. Why is OFFSET 100000 slow, and what do you do instead?

The engine must produce 100,020 rows and discard 100,000 - there is no index structure that can jump to the hundred thousandth row, so no index fixes it. Use keyset pagination: remember the last row seen and filter WHERE (sort_col, id) < (last_sort, last_id). Every page then costs what page one costs. The trade off is that you cannot jump to an arbitrary page number.

Q. How do you prevent SQL injection?

Parameterised queries, always. The statement is parsed and planned before the value arrives, so input cannot change its structure. Identifiers and sort directions cannot be parameterised - allowlist those. Then defence in depth: least privilege database accounts, type validation, and never returning database error text to users.

Questions to have an answer ready for

1. Normalise invoice(id, date, customer_name, customer_city, product_name, price, qty) to 3NF, out loud.
2. Explain the NOT IN NULL trap and give two fixes.
3. Walk through diagnosing a slow query end to end.
4. When is denormalisation the right answer, and what must ship alongside it?