SQL Learning Outcomes

What the industry expects

Use AI copilots

  • AI copilots can draft routine SQL quickly.
  • You're expected to know when code is correct (and wrong).
  • LLMs are often wrong: on real company data, accuracy ranges from about 20% to 100%. For example, Claude's text-to-SQL answered at most 21% of Anthropic's internal analytics questions correctly until Anthropic built data infrastructure around it (Case Study 1.1).

SQL interviews

  • SQL logic is common in data interviews.
  • Whiteboard rounds have no AI: you write and reason about the query yourself.
  • "Debug this query": right or wrong? Expensive or cheap? How to fix it?

Cloud-scale reality

  • Cloud warehouses such as BigQuery, Snowflake, and Redshift run queries across many machines.
  • Cut cost with partitioning and filter placement.

What you'll do in CS145

Practice (PSETs, exams): reasoning through SQL. Portfolio (projects): LLM and cloud work.

Reason through SQL

  • Write SQL logic on paper or a whiteboard, no IDE.
  • Use debug tables to trace semantics.

Example: NULL and COUNT

  • Project 1: a NULL-values problem. Mismatches across COUNT(rating), COUNT(*), and COUNT(user_id).
  • On the Project 1 dataset (far larger than the 9-row Spotify table): 50 NULL ratings. COUNT(column) skips NULLs; COUNT(*) counts all rows.

Experiment with LLMs

  • Use LLMs to explore subqueries, window functions, and analysis.
  • Test generated SQL against expected results. Collect the bugs you find as anecdotes to build intuition.

Example: checking generated SQL

  • Project 2: Claude built my 3-level CTE in seconds. But level 2 referenced level 3, which did not exist yet, and its LEFT JOIN with COUNT(*) counted NULLs.
  • Fixed: reordered the CTEs, used COUNT(t2.id).

Cloud databases

  • Query gigabytes of data on BigQuery.
  • Understand parallel execution.
  • Tune production queries for cost and speed.

Example: reducing scan cost

  • Project 1: a query kept timing out at $10.50 per run.
  • The execution plan scanned 2.1 TB before filtering. Moving the date filter before the JOIN triggered partition pruning.
  • Now 73 GB, $0.37, 12 seconds, and a Colab to prove it.