Joins - the heart of every SQL interview
Inner vs left vs right vs full outer join?
Inner join keeps only rows that match in both tables. Left join keeps all left-table rows (NULLs where the right has no match); right join is the mirror image. Full outer join keeps all rows from both, matched where possible.
What is a self join and when is it used?
A self join joins a table to itself using aliases. Classic example: an employees table joined to itself on manager_id to list each employee alongside their manager.
Join vs subquery - which do you use?
Joins combine columns from multiple tables into one result; subqueries nest a query to produce a value or filter. Joins often read more clearly and let the optimiser work better when you're combining data, while subqueries suit existence checks and derived values.
Grouping, filtering & functions
GROUP BY, and the difference between WHERE and HAVING?
GROUP BY collapses rows into groups for aggregation. WHERE filters individual rows before grouping; HAVING filters the aggregated groups after. You can't use an aggregate in WHERE - that's what HAVING is for.
How do NULLs affect aggregate functions?
COUNT(*) counts every row, but COUNT(column), SUM and AVG ignore NULLs. This is a classic trap - an average over a column with NULLs divides by the count of non-null values, not all rows.
What are window functions?
Functions like ROW_NUMBER, RANK and running totals compute a value across a set of rows (a 'window') without collapsing them like GROUP BY does. They're ideal for 'top N per group' and running-total questions.
Performance & correctness
What is an index and what does it cost?
An index is a sorted structure (often a B-tree) that lets the engine find rows without scanning the whole table, speeding up reads. The cost is slower writes and extra storage, since the index must be updated too.
Primary key vs unique key vs foreign key?
A primary key uniquely identifies a row and can't be null (one per table). A unique key enforces uniqueness but allows a null. A foreign key references another table's key to enforce referential integrity.
DELETE vs TRUNCATE vs DROP?
DELETE removes selected rows and can be rolled back (it's logged per row). TRUNCATE quickly empties a whole table. DROP removes the table structure entirely.
How do you answer a 'predict the output' question?
Read the query against the sample rows step by step - apply WHERE, then GROUP BY, then HAVING, then SELECT - and state exactly which rows come back. This is the single most common live SQL test, so practise it.
Practice this now