SQL for Data People: Think in Tables, Not Spreadsheets

Evaluate

• Choose whether a step belongs in SQL or in Python, R or Excel, and justify the choice

  Where I teach it: Creating and changing data · SQL, or Python, R or Excel?

  Builds on:

  • Explain that SQL is declarative: you describe the result you want and the DBMS decides how to get it
  • Explain how a database table differs from a DataFrame: it lives in the database, not in your session's memory
  • Summarize per group with GROUP BY
  • Combine two tables with INNER JOIN on a key

  Your Maestro asks you to judge when it fits and why and saves it as evidence when you do.

Synthesize

• Design a multi-step query with CTEs that filters, joins and summarizes to answer a question

  Where I teach it: Building bigger queries · Capstone: a multi-step query

  Builds on:

  • Break a query into named steps with WITH (common table expressions)
  • Join three or more tables in one query by following the schema's keys
  • Filter groups by an aggregate with HAVING

  Your Maestro asks you to build something of your own with it and saves it as evidence when you do.

Analyze

• Trace the logical order in which a query's clauses run (FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT) to predict what each clause can see

  Where I teach it: Asking one table questions · The order clauses really run in

  Builds on:

  • Filter rows with WHERE using comparison operators
  • Sort results with ORDER BY on one or more columns, ascending or descending
  • Compute new columns with expressions and rename result columns with AS
  • Explain that SQL is declarative: you describe the result you want and the DBMS decides how to get it

  Your Maestro asks you to break a problem down with it and saves it as evidence when you do.

• Diagnose a failing query from its error message (unknown column, syntax error, alias used too early)

  Where I teach it: Asking one table questions · Reading error messages

  Builds on:

  • Trace the logical order in which a query's clauses run (FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT) to predict what each clause can see

  Your Maestro asks you to break a problem down with it and saves it as evidence when you do.

• Diagnose a GROUP BY error caused by a selected column that is neither grouped nor aggregated

  Where I teach it: Summarizing · GROUP BY: one summary row per group

  Builds on:

  • Summarize per group with GROUP BY
  • Trace the logical order in which a query's clauses run (FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT) to predict what each clause can see

  Your Maestro asks you to break a problem down with it and saves it as evidence when you do.

• Decide whether a filter belongs in WHERE or HAVING based on when each runs

  Where I teach it: Summarizing · HAVING vs. WHERE

  Builds on:

  • Filter groups by an aggregate with HAVING
  • Trace the logical order in which a query's clauses run (FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT) to predict what each clause can see

  Your Maestro asks you to break a problem down with it and saves it as evidence when you do.

• Diagnose inflated totals caused by joining a one-to-many table before aggregating

  Where I teach it: Combining tables · Predicting row counts, and totals that grow

  Builds on:

  • Predict how a join changes the row count with one-to-one, one-to-many and many-to-many relationships
  • Summarize per group with GROUP BY

  Your Maestro asks you to break a problem down with it and saves it as evidence when you do.

• Predict how a join changes the row count with one-to-one, one-to-many and many-to-many relationships

  Where I teach it: Combining tables · Predicting row counts, and totals that grow

  Builds on:

  • Combine two tables with INNER JOIN on a key
  • Explain what a primary key is: the column or columns whose values uniquely identify each row and are never NULL

  Your Maestro asks you to break a problem down with it and saves it as evidence when you do.

Apply

• Run a query from a SQL client and read the result set it shows

  Where I teach it: Your first query · Connect and run your first query

  Builds on:

  • Distinguish the database, the DBMS (engine) that manages it, and the client tool used to talk to it
  • Explain what a database connection is and what connection details (host, database, user, credentials) specify
  • Explain what a query is: a statement sent to the DBMS that asks for data and gets back a result

  Your Maestro asks you to use it on a new case and saves it as evidence when you do.

• Write a SELECT that returns chosen columns from a table

  Where I teach it: Your first query · SELECT the columns you want

  Builds on:

  • Run a query from a SQL client and read the result set it shows
  • Name the parts of a table: table, row (record), column (field) and value

  Your Maestro asks you to use it on a new case and saves it as evidence when you do.

• Compute new columns with expressions and rename result columns with AS

  Where I teach it: What's really in a table · Computed columns and AS

  Builds on:

  • Write a SELECT that returns chosen columns from a table
  • Explain why each column has one data type and recognize common SQL types (integer, decimal, text, date, boolean)

  Your Maestro asks you to use it on a new case and saves it as evidence when you do.

• Reference a table by its qualified name (schema.table) to say exactly which table you mean

  Where I teach it: What's really in a table · Schemas: the word with two meanings

  Builds on:

  • Explain the two meanings of schema: a table's structure and a namespace that groups tables

  Your Maestro asks you to use it on a new case and saves it as evidence when you do.

• Use a schema diagram (ERD) to identify tables, their keys and how the tables relate

  Where I teach it: What's really in a table · Reading a schema diagram

  Builds on:

  • Explain what a primary key is: the column or columns whose values uniquely identify each row and are never NULL
  • Explain what a foreign key is and how it relates rows in one table to rows in another

  Your Maestro asks you to use it on a new case and saves it as evidence when you do.

• Filter rows with WHERE using comparison operators

  Where I teach it: Asking one table questions · WHERE: keep only the rows you want

  Builds on:

  • Write a SELECT that returns chosen columns from a table

  Your Maestro asks you to use it on a new case and saves it as evidence when you do.

Understand

• Distinguish the database, the DBMS (engine) that manages it, and the client tool used to talk to it

  Where I teach it: Your first query · Database, DBMS, client: three things people call "the database"

  Builds on:

  • Define a relational database as an organized collection of related tables managed by software

  Your Maestro asks you to explain it in your own words and saves it as evidence when you do.

• Explain how a database table differs from a spreadsheet: no row order, one type per column, no cell addresses

  Where I teach it: Your first query · Tables aren't spreadsheets

  Builds on:

  • Name the parts of a table: table, row (record), column (field) and value

  Your Maestro asks you to explain it in your own words and saves it as evidence when you do.

• Explain how a database table differs from a DataFrame: it lives in the database, not in your session's memory

  Where I teach it: Your first query · Tables aren't DataFrames either

  Builds on:

  • Name the parts of a table: table, row (record), column (field) and value

  Your Maestro asks you to explain it in your own words and saves it as evidence when you do.

• Explain that a SELECT returns a result set that is shown, not stored or assigned to a variable, unless you save it

  Where I teach it: Your first query · What a query is, and why nothing gets saved

  Builds on:

  • Explain what a query is: a statement sent to the DBMS that asks for data and gets back a result
  • Explain how a database table differs from a DataFrame: it lives in the database, not in your session's memory

  Your Maestro asks you to explain it in your own words and saves it as evidence when you do.

• Explain what a query is: a statement sent to the DBMS that asks for data and gets back a result

  Where I teach it: Your first query · What a query is, and why nothing gets saved

  Builds on:

  • Define a relational database as an organized collection of related tables managed by software

  Your Maestro asks you to explain it in your own words and saves it as evidence when you do.

• Explain what a database connection is and what connection details (host, database, user, credentials) specify

  Where I teach it: Your first query · Connect and run your first query

  Builds on:

  • Distinguish the database, the DBMS (engine) that manages it, and the client tool used to talk to it

  Your Maestro asks you to explain it in your own words and saves it as evidence when you do.

Remember

• Define a relational database as an organized collection of related tables managed by software

  Where I teach it: Your first query · Database, DBMS, client: three things people call "the database"

  Your Maestro asks you to recognize and recall it and saves it as evidence when you do.

• Name the parts of a table: table, row (record), column (field) and value

  Where I teach it: Your first query · Tables aren't spreadsheets

  Builds on:

  • Define a relational database as an organized collection of related tables managed by software

  Your Maestro asks you to recognize and recall it and saves it as evidence when you do.