LECTURES / MODULE 8
Develop and defend an analysis
Build readable reports and defend the results. Lab 11 covers views, ranking, running totals, LAG and transactions; Lab 12 adds CTE refactoring, recursive calendars, triggers and measured index experiments.
Detailed lessons in this module
Open the full explanations, examples, and SQL exercises directly.
1 / 3
Give intermediate results a name
A common table expression, introduced by WITH, names an intermediate query for the statement that follows. It can make a multi-stage analysis easier to read without creating a persistent table.
Name the grain of each CTE. A director-level summary joined back to movies repeats that summary for each movie; summing those repeated totals would inflate the result.
Refactoring should preserve the intended result. Compare values, duplicates, NULL handling, and ordering when the question requires an order.
WITH counts AS (SELECT director, COUNT(*) AS films FROM movies GROUP BY director) SELECT director, films FROM counts ORDER BY director;
The counts CTE first creates one row per director. The outer SELECT chooses its fields and display order; the intermediate result exists only for this statement.
The practice applies a threshold to this director-level result. A direct HAVING clause can express the same logic, making it a useful equivalence check.
Explore this concept →Explain it in your own words
Would a single GROUP BY/HAVING query be equally correct? Which is clearer for this task?
2 / 3
Separate a clear query from a fast query
An index can help locate rows but also consumes space and adds maintenance work when data changes. A faster claim requires measurement on the relevant workload.
EXPLAIN QUERY PLAN describes a strategy; it is not a timing result. Tiny classroom datasets are useful for learning plans, but do not establish production-scale performance.
A view saves a query definition for reuse, not a frozen result snapshot. Triggers run on configured changes and need explicit tests for their side effects. Practice creating views, indexes, and triggers in a disposable desktop database before applying them elsewhere.
SELECT id, title FROM movies WHERE year >= 2000 ORDER BY id;
The filter includes the boundary year 2000. The final id ordering is part of this output contract and must be retained when comparing an equivalent rewrite.
Run EXPLAIN QUERY PLAN followed by this SELECT to inspect the chosen strategy. It does not measure elapsed time or prove that an index would improve a larger workload.
Explore this concept →Explain it in your own words
What evidence would justify calling one equivalent query faster? What does timing a 14-row fixture fail to establish?
3 / 3
Understand ranking without collapsing rows
GROUP BY collapses rows into group summaries. A window function can attach a group calculation or rank while retaining the individual rows. The choice depends on the desired output grain.
ROW_NUMBER assigns positions within an ordering; RANK gives tied values the same rank and leaves gaps. Choose a tie policy deliberately and add a deterministic tie-breaker when unique positions are required.
The current browser SQLite engine does not support window syntax. The runnable exercise below explores the ordering first. Try SELECT id, title, ROW_NUMBER() OVER (ORDER BY year DESC, id) AS position FROM movies in your assigned modern desktop SQLite environment.
SELECT id, title, year FROM movies ORDER BY year DESC, id;
The preview places newer release years first and breaks ties by id. It establishes the ordering a ROW_NUMBER example would use in a modern engine.
LIMIT 5 returns exactly five rows when at least five exist. A rank-based rule may include more rows when several movies tie, depending on the chosen ranking function and filter.
Explore this concept →Explain it in your own words
How would “all movies tied within the top five ranks” differ from exactly five rows?
SQL reference & course materials
Use these reference chapters alongside the detailed lessons above. Each example identifies its database.
Put it to work in class
Readiness practice and team activities complement the assigned Canvas lab. Official instructions and submission requirements stay in Canvas.