Does the model's query answer the question?
Reading model-written SQL means checking the query against the question before you trust it. A query can run cleanly and still answer a different question.
Module 4 teaches joins and grouping. This lesson reads SQL that a model wrote for a question, and looks for the three faults models make most.
By the end of this lesson
Note
I can read a model's query and say whether it answers the question asked.
Key words
| Word | What it means | Example |
|---|---|---|
| Clause | one part of a query, such as WHERE | |
| Filter | a test that keeps only some rows | returned = 0 |
| Duplicate | the same value counted more than once | |
| DISTINCT | keeps each value once | COUNT(DISTINCT member_id) |
| Join column | the column two tables are matched on |
The cells students fill in are left blank in this preview.
Read the question before the query
First list what the question asks for. Which rows count? Is it a number, a list or one value? Must each thing be counted once?
Then read the query clause by clause. FROM and JOIN say where the rows come from, WHERE keeps some of them, and SELECT says what comes back.
Worked example: question parts and clauses
| The question says | The query needs | Clause |
|---|---|---|
| in month 3 | month = 3 | WHERE |
| different members | DISTINCT member_id | SELECT |
| how many | COUNT | SELECT |
| titles | a join from loans to books on book_id | FROM and JOIN |
The cells students fill in are left blank in this preview.
Three faults a model makes often
Counting duplicates: COUNT(member_id) counts Asha twice if she borrowed twice. A missing filter: the query counts every loan, not only the ones still out.
The wrong join: loans.id = books.id matches loan 3 to book 3 only because both are numbered 3. The join has to use loans.book_id.
Worked example: wrong and right
| Question | Wrong query part | Right query part |
|---|---|---|
| Different members in month 3 | COUNT(member_id) | COUNT(DISTINCT member_id) |
| Books still out | No WHERE | WHERE returned = 0 |
| Titles member 2 borrowed | ON l.id = b.id | ON l.book_id = b.id |
The cells students fill in are left blank in this preview.
A common mistake
Warning
Many people think a query that runs without an error answers the question. An error only means the SQL is broken. A wrong join or a missing filter runs cleanly and returns a wrong answer.
Write the model helper, part 1
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'const base = process.env.AI_URL || \"http://ai:4000\";' 'function count(data, name) {' ' return data.usage && typeof data.usage[name] === \"number\" ? data.usage[name] : null;' '}' '// A reply may wrap the query in a fenced block: keep what is inside it.' 'function sqlOf(text) {' ' const parts = text.split(String.fromCharCode(96).repeat(3));' ' const inner = (parts.length > 2 ? parts[1] : text).trim();' ' return inner.toLowerCase().startsWith(\"sql\") ? inner.slice(3).trim() : inner;' '}' > model.js"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Write the model helper, part 2
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'async function ask(messages, sample, max) {' ' try {' ' const res = await fetch(base + \"/v1/chat/completions\", {' ' method: \"POST\",' ' headers: { \"content-type\": \"application/json\", authorization: \"Bearer lab\" },' ' body: JSON.stringify({ model: \"lab\", messages: messages, max_tokens: max || 100 }),' ' signal: AbortSignal.timeout(30000),' ' });' ' if (res.ok === false) throw new Error(\"status \" + res.status);' ' const data = await res.json();' ' const text = data.choices[0].message.content;' ' return { text, inTokens: count(data, \"prompt_tokens\"), outTokens: count(data, \"completion_tokens\"), recorded: false };' ' } catch (error) {' ' console.log(\"Recorded sample: no model answered (\" + error.message + \").\");' ' return { text: sample, inTokens: null, outTokens: null, recorded: true };' ' }' '}' 'module.exports = { ask, sqlOf };' >> model.js"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Write the reading checklist, part 1
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'const { ask, sqlOf } = require(\"./model\");' 'const questions = [' ' { q: \"How many different members borrowed a book in month 3?\", needs: [\"DISTINCT\", \"month = 3\"],' ' a: \"SELECT COUNT(member_id) FROM loans WHERE month = 3\",' ' b: \"SELECT COUNT(DISTINCT member_id) FROM loans WHERE month = 3\" },' ' { q: \"How many books are still out?\", needs: [\"returned = 0\"],' ' a: \"SELECT COUNT(*) FROM loans\",' ' b: \"SELECT COUNT(*) FROM loans WHERE returned = 0\" },' ' { q: \"Which titles has member 2 borrowed?\", needs: [\"book_id\", \"member_id = 2\"],' ' a: \"SELECT b.title FROM loans l JOIN books b ON l.id = b.id WHERE l.member_id = 2\",' ' b: \"SELECT b.title FROM loans l JOIN books b ON l.book_id = b.id WHERE l.member_id = 2\" },' '];' > read.js"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Write the reading checklist, part 2
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'function squash(sql) { return sql.toUpperCase().split(/\s+/).join(\" \").trim(); }' 'function review(sql, needs) {' ' const text = squash(sql);' ' const problems = needs.filter(function (n) { return text.indexOf(squash(n)) < 0; })' ' .map(function (n) { return \"missing \" + n; });' ' if (/ON \w+\.ID = \w+\.ID\b/.test(text)) problems.push(\"joins id to id\");' ' return problems.length ? problems.join(\", \") : \"looks right\";' '}' >> read.js"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Write the reading checklist, part 3
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'async function main() {' ' questions.forEach(function (x, i) {' ' console.log((i + 1) + \" \" + x.q);' ' console.log(\" A: \" + review(x.a, x.needs));' ' console.log(\" B: \" + review(x.b, x.needs));' ' });' ' const schema = \"loans(id, book_id, member_id, month, returned)\nmembers(id, name)\nloans.member_id -> members.id\";' ' const system = \"Answer with one SQLite SELECT query and nothing else.\";' ' const sample = \"SELECT COUNT(DISTINCT member_id) FROM loans WHERE month = 3;\";' ' const reply = await ask([{ role: \"system\", content: system },' ' { role: \"user\", content: schema + \"\nQuestion: \" + questions[0].q }], sample, 120);' ' const sql = sqlOf(reply.text);' ' console.log(\"model wrote: \" + sql);' ' console.log(\" reading check: \" + review(sql, questions[0].needs));' '}' 'main();' >> read.js"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Run the reading checklist
docker exec -w /home/node/project agent node read.jsShown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
What the Run prints
| Line | What it shows |
|---|---|
| A: missing DISTINCT | Question 1, candidate A counts duplicates |
| A: missing returned = 0 | Question 2, candidate A has no filter |
| A: missing book_id, joins id to id | Question 3, candidate A joins on the wrong column |
| B: looks right | Each candidate B passes the reading check |
| model wrote: ... | The model's query for question 1, read the same way, or a recorded sample |
The cells students fill in are left blank in this preview.