SQL Interview Theory: The Questions They Actually Ask

The Pattern

Six layers, from the table definition upward. When the interviewer jumps around, you are only moving up and down this stack.

1. 1Keys
2. 2Normalization
3. 3Indexes
4. 4Transactions
5. 5Isolation
6. 6Execution order

1. Keys, how a row is identified

A key is a column, or set of columns, whose value tells you exactly which row you mean. Without one, "delete Asha's DBMS entry" is not a well defined instruction.

A super key is any column set that identifies a row, minimal or not. A candidate key is a super key with nothing spare in it. In enrolments_raw the only candidate key is (student_id, course_code).

You pick one candidate key and call it the primary key: unique, never NULL, exactly one per table. The ones you did not pick are alternate keys, enforced with UNIQUE, which still allows NULL. A key built from two or more columns is a composite key.

A foreign key requires a non-NULL reference to match a key in the referenced table. It can reference a primary or suitable unique key, including one in the same table. The chosen delete action decides whether deleting a parent is blocked, cascades to children, or changes the reference.

Tip

Tip: A surrogate key is an id with no business meaning. A natural key comes from the data itself. A surrogate id is useful when business identifiers may change, but you may still need a unique constraint on the natural identifier.

2. Normalization, store every fact exactly once

Before the sheet above, the clerk kept one row per student with course_code holding "CS301, CS302" and marks holding "78, 81". That is unnormalised. Split those cells and you reach 1NF: atomic values, no repeating groups, one row per student and course. The table above is that 1NF table.

2NF removes partial dependencies, meaning a non-key column depending on only part of the composite key. Here name, branch and hod depend on student_id alone, course_name and fee on course_code alone. Pull both groups out.

Students

Courses

Enrolments

The fee now exists in exactly one place, and Enrolments keeps only what is truly about the pair, the marks.

3NF removes transitive dependencies, a non-key column depending on another non-key column. Inside Students, hod depends on branch, and branch is not a key. One more split.

Students

Branches

Three student rows in CSE and IT now point at one HOD row each, instead of carrying a copy of the name.

Now nothing is stored twice and the three anomalies interviewers name are gone. Change the DBMS fee in one place, no row left behind, that is the update anomaly. Add a course before anybody enrols, the insert anomaly. Remove the last ECE student without losing Prof. Nair, the delete anomaly.

BCNF strengthens 3NF: for every non-trivial functional dependency, the determining columns must form a superkey. In other words, a set of columns should not determine other facts unless it can identify the row.

3. Indexes, how rows are found fast

An index provides another way to find rows without scanning the whole table. A B-tree lookup takes roughly logarithmic work to find a starting point, then additional work to read the matching entries and rows.

A common index structure is a B-tree. Its ordered entries support equality checks, ranges and some ordering requirements. Hash indexes mainly support equality lookups. The useful choice depends on the query and database.

Two useful points to explain:

Leading columns. An index on (course_code, marks) is ordered by course first and marks within that course. It is a natural fit for a course filter, possibly followed by a marks range. A marks-only filter is less direct; the planner may scan the index or use another optimisation. Check the plan rather than treating this as an absolute rule.

Cost. Every insert, update and delete maintains every index on the table, so an index is never free.

A clustered index stores the table data in its index structure in engines that support it. Secondary indexes provide another route to those rows. The details differ: MySQL InnoDB organises data around the primary key, while PostgreSQL does not maintain that same clustered layout automatically.

4. Transactions and ACID

A transaction is a group of statements that must happen as one unit: BEGIN, do the work, then COMMIT to keep it or ROLLBACK to undo all of it. SAVEPOINT marks a spot inside it that you can roll back to partially.

The four letters are one bank transfer told from four angles, which is how you should say it out loud.

5. Isolation levels

Stronger isolation restricts which concurrent outcomes are allowed. It can add waiting or retries. This table shows the anomalies permitted by the SQL standard; actual database behaviour can be stronger.

A dirty read sees another transaction's uncommitted change. A non-repeatable read is the same SELECT twice giving a different value. A phantom read is the same SELECT twice giving a different row count, because somebody inserted a row that matches your condition.

PostgreSQL defaults to READ COMMITTED and MySQL InnoDB to REPEATABLE READ. PostgreSQL's REPEATABLE READ also prevents phantom reads, although it still allows serialization anomalies. SQLite normally serialises writes and provides snapshot isolation in WAL mode. These details matter when discussing a real application. The PostgreSQL isolation documentation explains its guarantees.

6. Execution order, the fence

The clause you write first is not the clause that runs first. Build the query up in three steps and you can see it happen.

1. 1FROM + JOIN
2. 2WHERE
3. 3GROUP BY
4. 4HAVING
5. 5SELECT
6. 6ORDER BY
7. 7LIMIT

Step 1, just read the column. SELECT branch FROM Students; gives five rows, one per student: CSE, IT, CSE, IT, ECE. Nothing is folded yet.

Step 2, group them. GROUP BY takes rows sharing a value and folds them into one row per value, so you can count each pile.

SELECT branch, COUNT(*) AS students
FROM Students
GROUP BY branch;

Five rows have become three, and COUNT(*) is how many original rows went into each pile.

Step 3, filter the piles and sort them. HAVING filters groups, the way WHERE filters rows.

SELECT branch, COUNT(*) AS students
FROM Students
WHERE branch IS NOT NULL
GROUP BY branch
HAVING COUNT(*) >= 2
ORDER BY students DESC, branch ASC;

Read the query line by line:

1. FROM Students reads the five student rows.
2. WHERE branch IS NOT NULL drops rows with no branch, one row at a time. Here nothing is dropped.
3. GROUP BY branch folds those rows into one pile per branch, so three piles.
4. HAVING COUNT(*) >= 2 drops whole piles, so ECE with its single student is gone.
5. SELECT branch, COUNT(*) AS students builds the output columns, and the name students is born right here.
6. ORDER BY students DESC, branch ASC sorts what survived, and the alias is finally visible.

Dry Run, the same query counted step by step:

CSE and IT tie on 2, so the second sort key branch ASC is what fixes the order between them. Drop it and the rows can come out either way on a rerun, which is how a query passes on your laptop and fails on the judge.

What an OA judge sees. Judges never read your query. They capture the printed rows and compare them as text against a saved expected output:

CSE|2
IT|2

Pipe separated, no header, no padding, in exactly the order your ORDER BY produced. The practice judge here works the same way, on SQLite. Get the order or the decimals wrong and the case fails, even with correct logic.

Three consequences of the fence, asked constantly. WHERE runs before GROUP BY, so it can never hold an aggregate. A SELECT alias is invisible to WHERE, GROUP BY and HAVING, but visible to ORDER BY. LIMIT is last, so it cuts the sorted result, not the scan.

Check what you learned

In the query above, you replace HAVING COUNT(*) >= 2 with WHERE students >= 2. What happens?

• AIt works the same way
• BAn error, because WHERE runs before both GROUP BY and SELECT, so neither the count nor the alias exists yet
• CIt returns all five students
• DIt returns only ECE

Need a Hint?