Reference Checklist

One line per idea, across every note in this series.

Execution order

  • Recite the six pipeline stages, then explain why an alias fails in WHERE but works in ORDER BY.

  • State the WHERE-vs-HAVING rule in one sentence, and say why HAVING can't be replaced by WHERE when a condition needs an aggregate.

  • Explain why SELECT * is a liability in shipped code, in at least two distinct ways.

Boolean logic and pattern matching

  • Work out what Department = 'A' OR Department = 'B' AND Salary > 70000 returns, without running it.

  • Name which LIKE patterns can use an index, and explain why in terms of how the index is sorted.

  • Define a non-sargable predicate and give one example that isn't LIKE.

NULL and three-valued logic

  • Explain why WHERE Bonus = NULL always returns nothing.

  • State whether NOT (Bonus = 5000) returns the NULL rows, and why.

  • Explain why NOT IN with a NULL in the list returns zero rows, and what replaces it safely.

  • Give the AVG(Bonus) and AVG(COALESCE(Bonus, 0)) results for this table, and say which one a given report should use.

  • State whether two NULLs are equal under = and under GROUP BY, and reconcile the difference.

Reusable expressions

  • Explain why a CTE is not a temporary table.

  • State whether a repeated expression is computed twice, and why the CTE is preferred anyway.

Aggregation

  • Explain why every non-aggregated SELECT column must appear in GROUP BY.

  • Give the COUNT(*), COUNT(Bonus), and COUNT(DISTINCT Department) results for this table.

  • State what AVG returns over an empty group, and how that differs from COUNT.

Sorting and pagination

  • Explain what TOP 5 without ORDER BY actually guarantees.

  • Describe what happens at a tie on the cutoff row, and two ways to make it deterministic.

  • Explain why deep OFFSET is slow, and describe keyset pagination as the fix.

How to use this

Answer each line as a complete sentence, from memory, before moving to the next section. A pause on any line marks exactly which earlier note to reread.