/Python & Data Analytics/Phase 7

Phase 7Days 73-84

SQL Query Foundations

Ask precise questions of relational data and prove result grain

Phase Goal

Write readable SQL that filters, aggregates, joins, and reconciles without changing the intended unit.

Progress

Notes

Open the written lectures for this course before checking off the phase topics.

Projects

Repair Café Parts & Visit Analysis

Day 73: Relational Thinking & First Queries

Build the first-principles mental model for Tables, rows, columns, keys, relationships, and grain; name the question it answers and the unit of analysisPredict and trace a tiny example of Tables, rows, columns, keys, relationships, and grain before inspecting the resultApply Tables, rows, columns, keys, relationships, and grain to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Tables, rows, columns, keys, relationships, and grain; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for SELECT, FROM, aliases, and expressions; name the question it answers and the unit of analysisPredict and trace a tiny example of SELECT, FROM, aliases, and expressions before inspecting the resultApply SELECT, FROM, aliases, and expressions to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind SELECT, FROM, aliases, and expressions; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for LIMIT for inspection; name the question it answers and the unit of analysisPredict and trace a tiny example of LIMIT for inspection before inspecting the resultApply LIMIT for inspection to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind LIMIT for inspection; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Result grain and duplicates; name the question it answers and the unit of analysisPredict and trace a tiny example of Result grain and duplicates before inspecting the resultApply Result grain and duplicates to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Result grain and duplicates; diagnose the visible failure and perform the corrective check

Retrieve one recent mechanism and one older prerequisite before consulting notesComplete a guided example, then reconstruct its critical path from a blank artifactSolve a changed task that requires choosing among already-taught toolsRecord the decision changed, failure tested, help used, and adaptive 1/3/7/14/30-day review cue

Day 74: Filtering & Three-Valued Logic

Build the first-principles mental model for WHERE, BETWEEN, IN, and LIKE; name the question it answers and the unit of analysisPredict and trace a tiny example of WHERE, BETWEEN, IN, and LIKE before inspecting the resultApply WHERE, BETWEEN, IN, and LIKE to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind WHERE, BETWEEN, IN, and LIKE; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for AND/OR precedence; name the question it answers and the unit of analysisPredict and trace a tiny example of AND/OR precedence before inspecting the resultApply AND/OR precedence to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind AND/OR precedence; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for NULL, IS NULL, and UNKNOWN; name the question it answers and the unit of analysisPredict and trace a tiny example of NULL, IS NULL, and UNKNOWN before inspecting the resultApply NULL, IS NULL, and UNKNOWN to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind NULL, IS NULL, and UNKNOWN; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Filters that erase missing rows; name the question it answers and the unit of analysisPredict and trace a tiny example of Filters that erase missing rows before inspecting the resultApply Filters that erase missing rows to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Filters that erase missing rows; diagnose the visible failure and perform the corrective check

Day 75: Sorting, Distinctness & CASE

Build the first-principles mental model for Multi-key ORDER BY and ties; name the question it answers and the unit of analysisPredict and trace a tiny example of Multi-key ORDER BY and ties before inspecting the resultApply Multi-key ORDER BY and ties to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Multi-key ORDER BY and ties; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for DISTINCT as a column question; name the question it answers and the unit of analysisPredict and trace a tiny example of DISTINCT as a column question before inspecting the resultApply DISTINCT as a column question to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind DISTINCT as a column question; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for CASE for documented categories; name the question it answers and the unit of analysisPredict and trace a tiny example of CASE for documented categories before inspecting the resultApply CASE for documented categories to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind CASE for documented categories; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Evaluation order and hidden duplicates; name the question it answers and the unit of analysisPredict and trace a tiny example of Evaluation order and hidden duplicates before inspecting the resultApply Evaluation order and hidden duplicates to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Evaluation order and hidden duplicates; diagnose the visible failure and perform the corrective check

Day 76: Aggregation & GROUP BY

Build the first-principles mental model for COUNT variants, SUM, AVG, MIN, and MAX; name the question it answers and the unit of analysisPredict and trace a tiny example of COUNT variants, SUM, AVG, MIN, and MAX before inspecting the resultApply COUNT variants, SUM, AVG, MIN, and MAX to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind COUNT variants, SUM, AVG, MIN, and MAX; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for GROUP BY grain; name the question it answers and the unit of analysisPredict and trace a tiny example of GROUP BY grain before inspecting the resultApply GROUP BY grain to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind GROUP BY grain; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for HAVING versus WHERE; name the question it answers and the unit of analysisPredict and trace a tiny example of HAVING versus WHERE before inspecting the resultApply HAVING versus WHERE to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind HAVING versus WHERE; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Weights, denominators, and mixed grain; name the question it answers and the unit of analysisPredict and trace a tiny example of Weights, denominators, and mixed grain before inspecting the resultApply Weights, denominators, and mixed grain to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Weights, denominators, and mixed grain; diagnose the visible failure and perform the corrective check

Day 77: Inner & Outer Joins

Build the first-principles mental model for Join-key matching model; name the question it answers and the unit of analysisPredict and trace a tiny example of Join-key matching model before inspecting the resultApply Join-key matching model to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Join-key matching model; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for INNER, LEFT, RIGHT, and FULL outcomes; name the question it answers and the unit of analysisPredict and trace a tiny example of INNER, LEFT, RIGHT, and FULL outcomes before inspecting the resultApply INNER, LEFT, RIGHT, and FULL outcomes to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind INNER, LEFT, RIGHT, and FULL outcomes; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Cardinality checks; name the question it answers and the unit of analysisPredict and trace a tiny example of Cardinality checks before inspecting the resultApply Cardinality checks to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Cardinality checks; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Many-to-many multiplication and erased outer rows; name the question it answers and the unit of analysisPredict and trace a tiny example of Many-to-many multiplication and erased outer rows before inspecting the resultApply Many-to-many multiplication and erased outer rows to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Many-to-many multiplication and erased outer rows; diagnose the visible failure and perform the corrective check

Day 78: Multi-Table Queries

Build the first-principles mental model for Foreign-key paths; name the question it answers and the unit of analysisPredict and trace a tiny example of Foreign-key paths before inspecting the resultApply Foreign-key paths to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Foreign-key paths; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for One-to-many and many-to-many; name the question it answers and the unit of analysisPredict and trace a tiny example of One-to-many and many-to-many before inspecting the resultApply One-to-many and many-to-many to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind One-to-many and many-to-many; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Bridge-table aggregation; name the question it answers and the unit of analysisPredict and trace a tiny example of Bridge-table aggregation before inspecting the resultApply Bridge-table aggregation to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Bridge-table aggregation; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Fan traps and ambiguous routes; name the question it answers and the unit of analysisPredict and trace a tiny example of Fan traps and ambiguous routes before inspecting the resultApply Fan traps and ambiguous routes to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Fan traps and ambiguous routes; diagnose the visible failure and perform the corrective check

Day 79: Subqueries & CTEs

Build the first-principles mental model for Scalar, list, and table subqueries; name the question it answers and the unit of analysisPredict and trace a tiny example of Scalar, list, and table subqueries before inspecting the resultApply Scalar, list, and table subqueries to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Scalar, list, and table subqueries; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for CTEs as named stages; name the question it answers and the unit of analysisPredict and trace a tiny example of CTEs as named stages before inspecting the resultApply CTEs as named stages to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind CTEs as named stages; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Correlated subquery cost; name the question it answers and the unit of analysisPredict and trace a tiny example of Correlated subquery cost before inspecting the resultApply Correlated subquery cost to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Correlated subquery cost; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Needless nesting and repeated logic; name the question it answers and the unit of analysisPredict and trace a tiny example of Needless nesting and repeated logic before inspecting the resultApply Needless nesting and repeated logic to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Needless nesting and repeated logic; diagnose the visible failure and perform the corrective check

Day 80: Dates, Text & Conditional Logic

Build the first-principles mental model for Date types, intervals, extraction, and truncation; name the question it answers and the unit of analysisPredict and trace a tiny example of Date types, intervals, extraction, and truncation before inspecting the resultApply Date types, intervals, extraction, and truncation to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Date types, intervals, extraction, and truncation; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Text normalization and patterns; name the question it answers and the unit of analysisPredict and trace a tiny example of Text normalization and patterns before inspecting the resultApply Text normalization and patterns to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Text normalization and patterns; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for CASE business definitions; name the question it answers and the unit of analysisPredict and trace a tiny example of CASE business definitions before inspecting the resultApply CASE business definitions to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind CASE business definitions; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Dialect, timezone, and implicit casts; name the question it answers and the unit of analysisPredict and trace a tiny example of Dialect, timezone, and implicit casts before inspecting the resultApply Dialect, timezone, and implicit casts to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Dialect, timezone, and implicit casts; diagnose the visible failure and perform the corrective check

Day 81: Set Operations

Build the first-principles mental model for UNION versus UNION ALL; name the question it answers and the unit of analysisPredict and trace a tiny example of UNION versus UNION ALL before inspecting the resultApply UNION versus UNION ALL to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind UNION versus UNION ALL; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for INTERSECT and EXCEPT; name the question it answers and the unit of analysisPredict and trace a tiny example of INTERSECT and EXCEPT before inspecting the resultApply INTERSECT and EXCEPT to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind INTERSECT and EXCEPT; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Column compatibility; name the question it answers and the unit of analysisPredict and trace a tiny example of Column compatibility before inspecting the resultApply Column compatibility to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Column compatibility; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Unexpected deduplication; name the question it answers and the unit of analysisPredict and trace a tiny example of Unexpected deduplication before inspecting the resultApply Unexpected deduplication to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Unexpected deduplication; diagnose the visible failure and perform the corrective check

Day 82: SQL Data Quality Checks

Build the first-principles mental model for Uniqueness, null, accepted-value, and range checks; name the question it answers and the unit of analysisPredict and trace a tiny example of Uniqueness, null, accepted-value, and range checks before inspecting the resultApply Uniqueness, null, accepted-value, and range checks to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Uniqueness, null, accepted-value, and range checks; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Orphan checks; name the question it answers and the unit of analysisPredict and trace a tiny example of Orphan checks before inspecting the resultApply Orphan checks to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Orphan checks; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Source reconciliation totals; name the question it answers and the unit of analysisPredict and trace a tiny example of Source reconciliation totals before inspecting the resultApply Source reconciliation totals to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Source reconciliation totals; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Exception-returning review queries; name the question it answers and the unit of analysisPredict and trace a tiny example of Exception-returning review queries before inspecting the resultApply Exception-returning review queries to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Exception-returning review queries; diagnose the visible failure and perform the corrective check

Day 83: Query Review & Explain Basics

Build the first-principles mental model for Readable clauses and staged reasoning; name the question it answers and the unit of analysisPredict and trace a tiny example of Readable clauses and staged reasoning before inspecting the resultApply Readable clauses and staged reasoning to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Readable clauses and staged reasoning; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for EXPLAIN plan vocabulary; name the question it answers and the unit of analysisPredict and trace a tiny example of EXPLAIN plan vocabulary before inspecting the resultApply EXPLAIN plan vocabulary to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind EXPLAIN plan vocabulary; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Index and selectivity intuition; name the question it answers and the unit of analysisPredict and trace a tiny example of Index and selectivity intuition before inspecting the resultApply Index and selectivity intuition to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Index and selectivity intuition; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Correctness before speed; name the question it answers and the unit of analysisPredict and trace a tiny example of Correctness before speed before inspecting the resultApply Correctness before speed to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Correctness before speed; diagnose the visible failure and perform the corrective check

Day 84: Phase Project — Repair Café Analysis

Build the first-principles mental model for Visit, device, fault, volunteer, and parts grains; name the question it answers and the unit of analysisPredict and trace a tiny example of Visit, device, fault, volunteer, and parts grains before inspecting the resultApply Visit, device, fault, volunteer, and parts grains to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Visit, device, fault, volunteer, and parts grains; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Join-cardinality map and query stages; name the question it answers and the unit of analysisPredict and trace a tiny example of Join-cardinality map and query stages before inspecting the resultApply Join-cardinality map and query stages to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Join-cardinality map and query stages; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Throughput, repeat visit, and parts-use metrics; name the question it answers and the unit of analysisPredict and trace a tiny example of Throughput, repeat visit, and parts-use metrics before inspecting the resultApply Throughput, repeat visit, and parts-use metrics to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Throughput, repeat visit, and parts-use metrics; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Staffing and budget brief with reconciliation; name the question it answers and the unit of analysisPredict and trace a tiny example of Staffing and budget brief with reconciliation before inspecting the resultApply Staffing and budget brief with reconciliation to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Staffing and budget brief with reconciliation; diagnose the visible failure and perform the corrective check

Deliver Repair Café Parts & Visit Analysis for a cooperative repair-café coordinator; support the decision: which repair categories need more volunteer coverage and parts budget?Reconstruct the critical workflow without the example open and disclose help usedFind and repair a planted data, logic, or communication failure with evidenceRespond to a changed requirement without being told which technique to useState limitations, non-goals, uncertainty, and what evidence would change the recommendation

Capstone

Repair Café Parts & Visit Analysis

Build for a cooperative repair-café coordinator. The result must support this decision: which repair categories need more volunteer coverage and parts budget? Include reproducible inputs, validation evidence, one tested failure, a changed requirement, and an explicit non-goal.

Reproducible source-to-output workflow
Documented quality checks and assumptions
Decision-ready output for a named user
Independent reconstruction and transfer evidence

Phase Complete!

After this phase, you'll be able to:

Relational Thinking & First Queries
Filtering & Three-Valued Logic
Sorting, Distinctness & CASE
Aggregation & GROUP BY
Inner & Outer Joins
Multi-Table Queries
Subqueries & CTEs
Dates, Text & Conditional Logic

Independently deliver Repair Café Parts & Visit Analysis, diagnose a planted failure, explain the decision logic, and transfer the workflow to a changed requirement.

Back to all phases

Phase Goal

Day 73: Relational Thinking & First Queries

Tables, rows, columns, keys, relationships, and grain

SELECT, FROM, aliases, and expressions

LIMIT for inspection

Result grain and duplicates

Practice, retrieval, and analyst judgment

Day 74: Filtering & Three-Valued Logic

WHERE, BETWEEN, IN, and LIKE

AND/OR precedence

NULL, IS NULL, and UNKNOWN

Filters that erase missing rows

Practice, retrieval, and analyst judgment

Day 75: Sorting, Distinctness & CASE

Multi-key ORDER BY and ties

DISTINCT as a column question

CASE for documented categories

Evaluation order and hidden duplicates

Practice, retrieval, and analyst judgment

Day 76: Aggregation & GROUP BY

COUNT variants, SUM, AVG, MIN, and MAX

GROUP BY grain

HAVING versus WHERE

Weights, denominators, and mixed grain

Practice, retrieval, and analyst judgment

Day 77: Inner & Outer Joins

Join-key matching model

INNER, LEFT, RIGHT, and FULL outcomes

Cardinality checks

Many-to-many multiplication and erased outer rows

Practice, retrieval, and analyst judgment

Day 78: Multi-Table Queries

Foreign-key paths

One-to-many and many-to-many

Bridge-table aggregation

Fan traps and ambiguous routes

Practice, retrieval, and analyst judgment

Day 79: Subqueries & CTEs

Scalar, list, and table subqueries

CTEs as named stages

Correlated subquery cost

Needless nesting and repeated logic

Practice, retrieval, and analyst judgment

Day 80: Dates, Text & Conditional Logic

Date types, intervals, extraction, and truncation

Text normalization and patterns

CASE business definitions

Dialect, timezone, and implicit casts

Practice, retrieval, and analyst judgment

Day 81: Set Operations

UNION versus UNION ALL

INTERSECT and EXCEPT

Column compatibility

Unexpected deduplication

Practice, retrieval, and analyst judgment

Day 82: SQL Data Quality Checks

Uniqueness, null, accepted-value, and range checks

Orphan checks

Source reconciliation totals

Exception-returning review queries

Practice, retrieval, and analyst judgment

Day 83: Query Review & Explain Basics

Readable clauses and staged reasoning

EXPLAIN plan vocabulary

Index and selectivity intuition

Correctness before speed

Practice, retrieval, and analyst judgment

Day 84: Phase Project — Repair Café Analysis

Visit, device, fault, volunteer, and parts grains

Join-cardinality map and query stages

Throughput, repeat visit, and parts-use metrics

Staffing and budget brief with reconciliation

Phase gate: independent delivery and transfer

Repair Café Parts & Visit Analysis

Phase Complete!