SQL Case File: 001 | Correct Code Framework

Case Brief

Query Fundamentals Investigation

A support export filters by a display alias, but SQL refuses to recognize it. The written order lied.

This is not a syntax tour. The case closes only when you can predict, reason, prove, write, debug, explain, and defend the SQL.

Learning Contract

What Mastery Means Here

Predict
Given data plus SQL, determine the output before execution.

Construct
Given a requirement and schema, build the SQL progressively.

Defend
Explain correctness, traps, edge cases, and likely SQL Server work.

Evidence

Evidence Locker

Customers: Ada Active San Diego; Lin Inactive Austin; Grace Active Seattle. Orders include completed and pending rows.

Business requirement	Only rows surviving FROM and WHERE can be projected; ORDER BY and TOP/OFFSET affect final presentation and deterministic selection.
Relevant constructs	SELECT, FROM, WHERE, ORDER BY, DISTINCT, TOP, OFFSET / FETCH
Reasoning engine focus	Use the engine to narrow columns, apply rows before projection, avoid SELECT *, and prove that ORDER BY makes TOP/OFFSET deterministic.
Distractor to watch	A SELECT alias used in WHERE and TOP without deterministic ORDER BY.

SQL Reasoning Engine

Questions To Ask Before Writing SQL

The infographic is the reusable reasoning engine for this case: prove the answer, then ask how much work SQL Server must do to retrieve it.

1. What data do I actually need?	Return only required columns; avoid SELECT * when extra data causes reads, memory, or lookups.
2. Which tables contain the data?	Identify the correct tables first because they define FROM and JOIN.
3. How are the tables related?	Use primary and foreign keys as evidence; join on relationships, not guesses.
4. What rows do I need?	Use selective WHERE logic so fewer rows flow through the plan.
5. Are the filter columns indexed?	Check WHERE and JOIN columns for useful access paths.
6. Is the predicate sargable?	Prefer predicates SQL Server can seek, such as date ranges instead of functions on columns.
7. Seek, Scan, or Lookup?	Distinguish jumping to keys, reading many pages, and fetching extra columns after an index match.
8. How many rows do I expect?	Cardinality changes the best plan; few rows and many rows often want different strategies.
9. Will joins, sorts, or aggregates add work?	Account for CPU, memory, and tempdb work introduced by joins, ORDER BY, GROUP BY, SUM, and COUNT.
10. Are NULLs or transactions part of the problem?	Use NULL-aware predicates and keep long transactions from increasing locking or blocking.
11. Can the database do less work?	Return the correct result while reducing unnecessary reads, lookups, sorts, and row flow.

Query Fundamentals Engine Focus

Name the exact output columns before writing SELECT.
State the rows needed before adding ORDER BY, DISTINCT, TOP, or OFFSET.
Ask whether the filter can stay sargable and whether an index can support it.

Goal: Return the correct result while making SQL Server do less work.

CCF Reasoning

Spaces Before Syntax

Base Component
Row and result set

Answer Space
Only rows surviving FROM and WHERE can be projected; ORDER BY and TOP/OFFSET affect final presentation and deterministic selection.

Problem Space
Tables, rows, columns, keys, relationships, ordering, NULLs, duplicates, and constraints.

Decision Space
Scanner/access pattern, filter, relationship, recorder, invariant, and finalizer.

Invariant: the final result must satisfy the business contract after every relational transformation.

Predict

Predict Before Execution

Before reading the final SQL, decide which rows survive, which rows disappear, whether rows multiply or collapse, what one output row represents, and whether ordering is deterministic.

Reveal Prediction Ruling

Only rows surviving FROM and WHERE can be projected; ORDER BY and TOP/OFFSET affect final presentation and deterministic selection.

Build

Build The Query Progressively

Ask what result is needed and what one output row represents.
Find where the evidence lives: tables, columns, keys, and relationships.
Filter the rows with selective, sargable predicates whenever the requirement allows it.
Check whether SQL Server can seek, whether it must scan, and whether lookups are being invited.
Estimate row flow before adding joins, sorts, aggregates, windows, or transaction scope.
Apply the final condition and projection only after the Answer Space is stable.

-- SQL Case File 001 pattern
-- FROM/JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> TOP/OFFSET
-- Build from evidence, not from memorized syntax.

Prove

Prove The Result

Compare the input evidence, intermediate state, and final output against the Answer Space. The proof fails if the query returns the right-looking shape for the wrong reason.

SQL rule: FROM/JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> TOP/OFFSET

Wrong Turn

The Query That Runs But Lies

Debugging Challenge

A SELECT alias used in WHERE and TOP without deterministic ORDER BY.

Engine checkpoint: Re-run the checklist and find the first question the wrong query cannot answer honestly.

Compile?
Often yes.

Run?
Often yes.

Correct?
Only if the Answer Space survives.

Efficient?
Only if SQL Server avoids unnecessary work.

SQL Server Lens

Logical SQL vs Physical SQL Server

Logical SQL
What result is SQL required to produce? Which rows, columns, groups, partitions, or relationships prove it?

Physical SQL Server
How might SQL Server seek, scan, lookup, sort, group, join, lock, or compute that result?

Seek
Jump directly to matching keys when predicates and indexes allow it.

Scan
Read many rows or pages when SQL Server has no narrower path or the row count justifies it.

Lookup
Fetch extra columns after a nonclustered index match, which can repeat work at scale.

Do not assume one execution plan without indexes, statistics, row counts, predicates, and expected cardinality.

Interview Room

Progressive Interview Simulation

Foundational: Define Query Fundamentals in plain language.
Predict: Walk through a tiny result without running SQL.
Edge case: Explain how NULL, duplicates, ordering, cardinality, or concurrency can change the result.
Wrong turn: Repair this issue: A SELECT alias used in WHERE and TOP without deterministic ORDER BY.
Scale: Discuss what changes when the largest table has 100 million rows, then name the likely seek, scan, lookup, sort, join, aggregate, lock, or tempdb pressure.
Defense: Explain the tradeoff of one alternative solution.

Case Challenge

Unguided Variation

Create Customers and Orders, then predict a filtered, ordered customer report by hand.

Change one business rule, one edge case, and one scale constraint. Predict how each change affects the Answer Space before changing the SQL.

Mastery Gate

Before You Close The Case

Predict
Determine output from tables and SQL.

Construct
Write SQL from the requirement.

Explain
State why the query works.

Debug
Repair the plausible wrong turn.

Defend
Answer follow-ups under interview pressure.

Case Closed

SQL Case File: 001

Case Closed

Query Fundamentals

Base Component	Row and result set
Answer Space	Only rows surviving FROM and WHERE can be projected; ORDER BY and TOP/OFFSET affect final presentation and deterministic selection.
Problem Space	Relevant data, constraints, relationships, ordering, NULLs, duplicates, and scale.
Decision Space	Choose the operation that lawfully transforms evidence into the answer.
SQL Reasoning Engine	Use the engine to narrow columns, apply rows before projection, avoid SELECT *, and prove that ORDER BY makes TOP/OFFSET deterministic.
SQL Pattern	FROM/JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> TOP/OFFSET
Common Trap	A SELECT alias used in WHERE and TOP without deterministic ORDER BY.
Hands-On Lab	Create Customers and Orders, then predict a filtered, ordered customer report by hand.
Interview Principle	Explain the result, then the query, then the likely SQL Server work.

If you remember only one thing from this Case File: before writing SQL, ask what data is needed, how to filter it, whether the columns are indexed, whether the predicate is sargable, and how much work SQL Server has to do.