SQL Last-Minute Revision

30 quick revision notes for SQL

1. Logical query order is FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY and finally LIMIT or OFFSET.
2. WHERE filters rows before grouping, while HAVING filters groups after aggregation and can use aggregate functions.
3. DELETE removes selected rows and can be rolled back, TRUNCATE empties a table quickly, and DROP removes the table itself.
4. NULL comparisons with = or <> return unknown, so always use IS NULL or IS NOT NULL to test for missing values.
5. COUNT(*) counts all rows, COUNT(column) skips NULLs, and COUNT(DISTINCT column) counts unique non-null values.
6. UNION removes duplicate rows and is slower, while UNION ALL keeps all rows and should be the default when possible.
7. INNER JOIN keeps matches only, while LEFT JOIN keeps all left rows and fills unmatched right columns with NULL.
8. A condition on the right table in WHERE turns a LEFT JOIN into an inner join; place it in the ON clause instead.
9. Join fan-out happens when the right table has many rows per key, inflating SUM results; pre-aggregate before joining.
10. Anti joins find missing matches using NOT EXISTS or LEFT JOIN with WHERE right_key IS NULL.
11. NOT IN returns no rows when the subquery contains a NULL, so prefer NOT EXISTS for safe results.
12. Correlated subqueries reference the outer query and run logically per row, while non-correlated ones run once.
13. CTEs improve readability, can be referenced multiple times in a query, and support recursion for hierarchies.
14. Recursive CTEs have an anchor member and a recursive member joined with UNION ALL, and need a stopping condition.
15. ROW_NUMBER gives unique numbers, RANK skips positions after ties, and DENSE_RANK gives ties the same rank without gaps.
16. LAG reads a previous row and LEAD reads a following row in the window order, ideal for period-over-period change.
17. With ORDER BY and no frame, the default window frame is RANGE UNBOUNDED PRECEDING to CURRENT ROW, which groups ties.
18. Window function results cannot be filtered in WHERE; compute them in a CTE and filter in the outer query.
19. Consecutive day problems use date minus ROW_NUMBER to create a group key for each streak of dates.
20. A primary key is unique and not null, one per table, while a table can have many unique constraints.
21. 1NF needs atomic values, 2NF removes partial dependency on a composite key, and 3NF removes transitive dependency.
22. OLTP systems use normalised schemas for fast transactions, while OLAP warehouses use star schemas for analysis.
23. Slowly changing dimension Type 2 keeps history by adding a new row with effective dates and a current flag.
24. A clustered index sets physical row order and only one exists per table; non-clustered indexes are separate structures.
25. Composite indexes follow the leftmost prefix rule, so column order must match the most common filter patterns.
26. Functions on indexed columns, leading wildcards and implicit conversions make predicates non-SARGable and block index seeks.
27. Keyset pagination with WHERE id > last_id is much faster than large OFFSET values on big tables.
28. ACID means Atomicity, Consistency, Isolation and Durability, guaranteeing reliable transactions such as fund transfers.
29. Read Committed prevents dirty reads, Repeatable Read prevents non-repeatable reads, and Serializable prevents phantoms too.
30. Deadlocks are resolved by the database killing one victim transaction; reduce them with consistent lock order and short transactions.

Night-before checklist

• Revise your saved questions on this page once, aloud.
• Read the quick notes and mark any you cannot explain in one line.
• Rehearse a two-minute self-introduction and two project stories.
• Keep CV, ID, laptop charger and interview link ready; sleep on time.