This is the longer version of the exercise at the center of Week 2: asking a chat about a database, reading the SQL it writes, and deciding whether to believe the number. If the loop went by too fast in class, or you want the reasoning behind it, start here. The SQL we covered (SELECT, WHERE, ORDER BY, LIMIT, joins, keys) is in SQL 1 and SQL 2.

You were the stakeholder

Every company that runs on data has the same three kinds of people touching it. Analysts read data and turn it into decisions. Developers build the applications that read and write it. Database administrators change its structure. And then there is everyone else, which is most people, and probably you: the stakeholder who has a question, hands it to someone who can answer it, and gets a number back.

In class, the chat was the analyst and you were the stakeholder. You did not write the SQL. Your job was harder: decide whether to believe it.

That is the job this course keeps giving you, and it gets more important, not less, as the tools get better. An analyst who is right nine times out of ten is extremely useful, as long as somebody can tell which time is the tenth.

The analyst knows only what you give it

Before the first question, you pasted a brief into your chat. It said: this is a SQLite database, these are the tables and their columns, the seasons run from 1871 to 2020, and when I ask a question, give me the SQL and the answer you expect it to return.

That brief is the whole of what the chat knew about your database. It never saw the file. It could not look at a row. Everything it got right came from what you gave it, and everything it got wrong came from what you did not.

If you skipped the brief, you saw what happens instead: the chat invents a schema. It writes a confident query against a franchise column or an HR column that does not exist, and the query fails. That is the cheap version of the lesson. The expensive version is a question with just enough context to run and not enough to be right.

The word for what you pasted is context, and it will come back all quarter.

Steps

  1. Ask. Paste the question exactly as written.
  2. Read. What comes back is two things: the SQL, and the number the chat expects it to return.
  3. Run. Paste the SQL into the Run SQL page yourself, and compare what the database returns with what the chat predicted.
  4. Save. Keep the query in queries/, with the question on the first line, so the Queries page shows it next to the others.
  5. Decide. Write one sentence: trust, or don't trust, because.

The "because" is the skill. "Trust, because the SQL filters on the right year and 107 wins is a believable season" is a judgment. "Trust, because it looked right" is a coin flip with extra steps.

The number was memory. The SQL was work.

Asking for both the SQL and the expected answer was deliberate, because they come from different places.

The chat has read an enormous amount about baseball. Ask it who won the most games in 2019 and it will tell you the Houston Astros, 107, before it has written a line of SQL, because it remembers. That expected answer is a prediction from memory. It is often right. It is not evidence.

The SQL is the only part of the reply that touches your data. When you run it, the database answers the question the SQL asks, which is not always the question you asked.

On the first question, the two agreed: the query returned the Astros with 107, and the chat had said so. That agreement is what trust looks like. On the Ken Griffey question, some of you saw them disagree: the chat said 630 home runs, and its own SQL returned 782. Only one of those numbers came from the data, and it was not the one it was confident about.

Three ways a correct-looking answer goes wrong

None of these produce an error. The query runs, a number appears, and nothing on the screen tells you anything is wrong.

The question meant more than one thing

"Which team has lost the most games in history?" In this database there are at least three answers:

  • The worst single season: the 1899 Cleveland Spiders, 20 wins and 134 losses.
  • The most losses added up by team name: the Philadelphia Phillies, 10,426.
  • The most losses by franchise, which is what a fan means: unanswerable. The database has no idea that the Cleveland Bronchos, Naps and Indians are one club, or that the Cleveland Spiders were a different one. It has team names per season, and Cleveland alone appears under seven of them.

The chat had to pick one, and most of them picked without telling you. Some asked which one you meant, or gave you both, which is what a good analyst does. Either way, the ambiguity was in the question before it was in the SQL. If you do not say which answer you want, someone else decides.

And when none of the readings is the one you meant, that is not a SQL problem. It is a data model problem: the database is missing something (here, a franchise) that the question assumes. Which is where the second half of the class went.

The query matched on a name

"How many home runs did Ken Griffey hit?" There are two Ken Griffeys: the father, with 152, and the son, the Hall of Famer, with 630. A query that finds him with WHERE first_name = 'Ken' AND last_name = 'Griffey' adds them together and returns 782, a number that is exactly right for a question nobody asked.

The same thing turned up earlier in the day, on purpose. WHERE park = 'Wrigley Field' finds 107 seasons, all in Chicago. WHERE park LIKE '%Wrigley%' finds 108, because it also catches the 1961 Los Angeles Angels, who played their first season at a different ballpark named Wrigley Field. The question you asked and the question you typed are not always the same question.

This is what the primary key lecture was for. Names are for humans: they get misspelled, shared and reused. An id is the one thing guaranteed to mean one row. When the answer is about a particular person, place or thing, look at the WHERE and ask whether it identifies that thing, or just something with the same name.

Rows went missing

"Which team won the most games in 2002?" The natural query is:

SELECT year, name, wins
FROM teams
WHERE year = 2002
ORDER BY wins DESC
LIMIT 1;

It returns the New York Yankees, 103 wins. It is also wrong, or at least half of the answer: the Oakland Athletics won 103 games that year too. That is the Moneyball season. LIMIT 1 kept the Yankees only because of the order the rows happened to be stored in. Change it to LIMIT 2 and the A's appear.

A missing row is the hardest mistake to catch, because there is nothing on the screen to look at. You can read a wrong WHERE clause. You cannot read a row that is not there.

You can test a feature. You can only check a query.

When a developer builds a feature or fixes a bug, there is a way to know it worked. Click through it the way a user would. Better, write a test: a small program that says what should happen and checks that it does, run every time the code changes. At the end of class you watched an agent do exactly that, adding a test for 140-character captions and running it.

A query does not get that. It runs, a number comes back, and nothing on the screen says whether it answered the question. Some teams build a small test database, stocked with the edge cases they already know about (the legacy rows, the duplicates, the weird old records), and run their queries against it. That helps. Mostly, though, someone spot-checks the result, and spot-checking takes knowing the data: its edge cases, its quirks, and the places where it is not as clean as it looks.

Look at the traps above again. The 2002 tie, the two Ken Griffeys, Cleveland under seven names, the other Wrigley Field: none of them was a SQL mistake. Each was a fact about the data that you had to already know to notice. And nobody hands you the expected result. If the stakeholder knew the answer, they would not be asking.

None of this is new with AI. It was true when analysts wrote every line of SQL by hand, and it is still true now that a chat writes it. What changed is how fast a confident answer arrives. The checking did not change.

Four cheap checks

None of these need more SQL than you already know.

  1. Say what you expect before you run it. Even roughly: "a team, a hundred-ish wins, this century." A result that surprises you is the only alarm you get, and you cannot be surprised if you had no expectation.
  2. Ask for one more row than you need. If the question is "which one", run it with LIMIT 3. Ties and near-ties show up immediately, and so does a second Ken Griffey.
  3. Count what came back. If you asked for the best team in every season since 1960, you should get at least 61 rows, one per season. Fewer means something was dropped. More is not necessarily wrong (in six of those seasons there was a tie), but you should know why.
  4. Read the WHERE out loud. Does it say what you asked? A name where an id should be, a LIKE where you meant an exact match, a year range that is off by one: the WHERE clause is where most wrong answers live, and it is usually one line.

When the analyst is good

Some of you watched your chat get everything right: it named both readings of the losing question, split the two Ken Griffeys by id, or wrote the 2002 query so that it returned both teams. The better chats are good at this, and increasingly so.

Check anyway. The chat caught the 2002 tie because it remembers the 2002 season. It does not remember your company's customers, your pricing table or last quarter's refunds, and on that data it will write the same careful-looking SQL with nothing to check it against but you. Good analyst. Now check that it was right. The loop does not change.

Never run UPDATE or DELETE without WHERE

Everything above is about reading. This is about the one time it stops being safe to get it wrong.

UPDATE and DELETE change data, and both apply to every row the WHERE clause matches. With no WHERE clause, that is every row in the table. DELETE FROM teams; does not ask which teams. It removes all 2,955 of them.

In class you watched an AI agent (a chat that can run commands itself, not just suggest them) get asked to "clear out the teams table so we can reload it from a fresh export." It wrote exactly that statement. It stopped and asked first only because the repository has a file, AGENTS.md, telling any agent working there to show a change to the data and wait for confirmation. Without that line, it runs.

Then it ran, and the table was empty. The database came back because in this class the database is a single file that lives in Git, and git checkout db/baseball.sqlite3 restored it. Production databases do not work like that. Most of the time there is no undo, only a backup, if someone made one, from some time before the mistake.

So: when you see UPDATE or DELETE, from a chat, an agent, a colleague or yourself, find the WHERE before anything else. If there is not one, stop.

The thread through all of it

Every trap in this post ran without an error. The SQL was valid, a number came back, and the only thing between that number and a decision was someone reading it. Writing SQL is increasingly something you will hand off. Deciding whether to believe it is not.