Module 3 Introduction: Prompting for Correct SQL

Description

In Module 2 you gave the model Reservo's schema as context: the CREATE TABLE statements, column descriptions, sample rows, and relationships. That was a huge leap —the model went from guessing tables to naming them correctly—. But knowing what tables exist isn't enough for the SQL to come out right. A model with the perfect schema can still add up cancelled bookings when computing revenue, write DATE_TRUNC('month', start_at) (a function that doesn't exist in SQLite), return a paragraph of prose around the SQL that's a pain to parse later, or —worse— silently guess what you meant by "the best members" and hand you an answer to a different question than yours.

This module closes that gap. You'll learn to write the prompt so the generated SQL is correct, safe, and easy to process. The central tool is a SQL assistant's system prompt: a system message that pins down the model's role (read-only analyst), target dialect (SQLite), schema (M2's context), and rules. On top of that, you'll learn few-shot —giving it 2-3 examples of already-solved questions—, how to pin the dialect so it doesn't mix engines, how to write constraint instructions that bound the shape of the output, and how to handle ambiguity so the assistant asks for clarification or states its assumption instead of guessing.

As throughout the guide, the call to the model is conceptual: I'll show you the system prompt you'd send and a plausible response from claude-sonnet-5, labeled as an example. But the SQL it "generates" actually runs against Reservo. That way you'll see, with real output, the difference between a poor prompt and a rich one: the poor one gives a wrong number or fails with a dialect error; the rich one gets it right.

---

Where are we in the guide?

You're on the third step. Module 1 laid out the text-to-SQL problem and its dangers; Module 2 solved the first link —the schema as context—; this one solves the second —the prompt that produces correct SQL—.

   M1  Why SQL and LLMs             the problem, the dangers, the first round trip
        │
        ▼
   M2  Giving the LLM the schema    the schema as context (CREATE TABLE, samples, FKs)
        │   the model now knows WHAT tables exist
        ▼
   YOU ARE HERE
   M3  Prompting for correct SQL    role, dialect, rules, few-shot, ambiguity
        │   the model generates SQL that respects your engine, your business, your format
        ▼
   M4  Validating and executing     does it parse? is it SELECT? real tables? correction loop
        │
        ▼
   M5  Guardrails and security      read-only, allowlist, limits, anti-injection
        │
        ▼
   M6  The SQL agent loop           the run_sql tool, tool-calling, multi-step

The division of labor with the neighboring modules is fine-grained, and it's worth having it clear from the start, because on this topic it's easy to trespass into the module next door:

• The schema as context was M2. Here we reuse it —it goes inside the system prompt—, but we don't re-explain how to introspect sqlite_master or PRAGMA table_info. If you need a refresher on how that block gets generated, that's Module 2.
• Validating the generated SQL is M4. Here a poor prompt will generate bad SQL, and we'll watch it fail or lie —but as motivation for prompting better, not as something we catch with a validator. The "does it start with SELECT?" check that shows up here is deliberately minimal; the real validator is M4.
• Hard guardrails are M5. When in this module we write the rule "generate only SELECT, never DELETE," that's a prompt instruction: a request to the model. Forcing read-only at the connection level (PRAGMA query_only), the enforced allowlist that actually blocks destructive statements, limits, and anti-injection are M5. The distinction is the guide's backbone: the prompt raises the probability; the guardrail guarantees it.

---

The guiding idea: the model answers with what you ask for and show it

A language model has no intentions about your database. It generates the most probable continuation of the text you gave it. If you give it little —just the question and the schema—, the most probable continuation drags along everything it saw in training: a mix of SQL dialects (Postgres, MySQL, SQL Server, SQLite, all jumbled together), generic business conventions ("revenue = the sum of everything," with no idea that in Reservo cancelled bookings don't count), and a conversational answer format (explanatory prose around the SQL).

Prompting for correct SQL is narrowing that distribution: giving the model enough context and enough rules that the most probable continuation is exactly the SQL you consider correct. There are two levers for doing this:

1. Telling it (instructions): the role, the dialect, the rules, the format. "You are a read-only SQLite analyst. Generate a single SELECT statement. Revenue only counts confirmed bookings."
2. Showing it (examples, few-shot): 2-3 correct question→SQL pairs. Sometimes one example communicates in three lines what a paragraph of instructions doesn't manage —especially a subtle convention, like which date column to filter on or how to write an idiomatic JOIN—.

This module teaches you to use both levers. And since the result is SQL, we can run it and check whether the lever worked.

---

The artifact you're building in this module: the system prompt

Everything you learn here converges into a single artifact: Reservo's assistant's system prompt. It's the system message that accompanies every user question, and it has four pieces. Look at it whole once —we'll take it apart piece by piece through the module, and you'll assemble it yourself in the mini-project—:

┌─ RESERVO ASSISTANT SYSTEM PROMPT ────────────────────────────────┐
│                                                                  │
│  [1] ROLE         You are a READ-ONLY data analyst for           │
│                    Reservo. You translate the user's question    │
│                    into a SQL query.                             │
│                                                                  │
│  [2] DIALECT      The engine is SQLite 3.50. Use SQLite          │
│                    functions (strftime, date). NEVER DATE_TRUNC, │
│                    EXTRACT, NOW, or another engine's syntax.      │
│                                                                  │
│  [3] SCHEMA       (M2's context: CREATE TABLE, column notes,     │
│                    relationships. Money is cents.                │
│                    status: 'confirmed'|'cancelled'.)             │
│                                                                  │
│  [4] RULES        - A SINGLE SELECT statement (or WITH...SELECT).│
│                    - Only these tables and columns.               │
│                    - Revenue counts only confirmed bookings.      │
│                    - If the question is ambiguous, state your    │
│                      assumption or ask for clarification.         │
│                    - Reply with only the SQL (or JSON {sql,      │
│                      explanation}).                               │
│                                                                  │
│  + FEW-SHOT       2-3 correct question→SQL examples.             │
│                                                                  │
└──────────────────────────────────────────────────────────────────┘

Each lesson in this module builds and justifies one of these pieces, running the SQL it produces to prove the piece works. By the end you'll have the complete prompt, wired up, ready for M8's assistant.

---

The hard rule, applied to this module

Let's revisit the convention that governs the guide, because in this module it's easy to forget —it feels like we're "talking to the model" the whole time—:

• The call to the model does NOT run. There's no Claude API or network in the environment where you run the examples. Every time you see a system prompt, some few-shot examples, and "assume claude-sonnet-5 returned this SQL," it's a realistic example labeled as such. We use current models (claude-sonnet-5); never claude-3 or anything retired.
• The resulting SQL DOES run, with Python and sqlite3, against the Reservo you populated in M1. When a poor prompt produces a wrong number or a dialect error, that number and that error come from actually running the SQL.
• There are also two processing pieces that run: parsing the model's response (for example, json.loads on a {sql, explanation}) and the minimal "does it start with SELECT?" check. These are plain Python, and we run them.

Quick environment check

The examples assume you already populated Reservo in Module 1 (the reservo.db file, with 5 rooms, 8 members, 23 bookings, 26 payments). If you need to recreate it, rerun M1's seed. Let's confirm it's there and responds:

import sqlite3

con = sqlite3.connect("reservo.db")
print("sqlite engine", sqlite3.sqlite_version)
for t in ("rooms", "members", "bookings", "payments"):
    n = con.execute(f"SELECT COUNT(*) FROM {t}").fetchone()[0]
    print(f"{t:10} {n}")
con.close()

What to expect:

sqlite engine 3.50.4
rooms      5
members    8
bookings   23
payments   26

If you see those four numbers, your playing field is ready. The whole module was run with Python 3.14 and SQLite 3.50.4.

---

A preview: the same question, two prompts

To give you an immediate feel for what this module is about, let's see the pattern we'll repeat lesson after lesson: the same question, a poor prompt and a rich one, and each one's SQL run for real.

The user's question:

"How much revenue did the Focus room bring in total?"

Poor prompt — just the question and the schema, no business rules. Assume claude-sonnet-5 returned:

-- SQL generated with a poor prompt (realistic example, NOT run by the model)
SELECT SUM(b.price_cents) AS revenue_cents
FROM bookings b
JOIN rooms r ON r.id = b.room_id
WHERE r.name = 'Focus';

Rich prompt — with the rule "revenue counts only confirmed bookings." Assume it returned:

-- SQL generated with a rich prompt (realistic example)
SELECT SUM(b.price_cents) AS revenue_cents
FROM bookings b
JOIN rooms r ON r.id = b.room_id
WHERE r.name = 'Focus' AND b.status = 'confirmed';

The only difference is AND b.status = 'confirmed'. Let's run both against Reservo:

import sqlite3

con = sqlite3.connect("reservo.db")

def scalar(sql):
    return con.execute(sql).fetchone()[0]

poor = """
SELECT SUM(b.price_cents)
FROM bookings b JOIN rooms r ON r.id = b.room_id
WHERE r.name = 'Focus'
"""
rich = """
SELECT SUM(b.price_cents)
FROM bookings b JOIN rooms r ON r.id = b.room_id
WHERE r.name = 'Focus' AND b.status = 'confirmed'
"""
print("poor prompt:", scalar(poor))
print("rich prompt:", scalar(rich))
con.close()

What to expect:

poor prompt: 47500
rich prompt: 34000

Both SQL statements run without error. Both return a number that looks reasonable. But only one answers the business question: Focus has two cancelled bookings (7500 + 6000 = 13500 cents) that the poor prompt counted as revenue. The difference between 47500 and 34000 is exactly those cancellations. Nobody notices if they only look at the number —that's what makes it dangerous—, and the rich prompt prevents it because it taught the model your business's convention.

That's the module in one sentence: the prompt doesn't change what's in the database; it changes what SQL the model asks for, and with that, what number you get.

---

Module objective

By completing this module you'll be able to:

• ✅ Write a SQL assistant's system prompt with its four pieces: role, dialect, schema (from M2), and rules.
• ✅ Use few-shot —2-3 question→SQL examples— and explain, by running it, the error they fix.
• ✅ Pin the SQLite dialect and avoid another engine's functions (DATE_TRUNC, EXTRACT, NOW), seeing the real error they produce.
• ✅ Write constraint instructions ("only SELECT," "only these tables," "reply with only the SQL") and understand why they make parsing and validating easier.
• ✅ Design the prompt to handle ambiguity: asking for clarification or stating the assumption, seeing that two interpretations give different answers.
• ✅ Ask for SQL + structured explanation (JSON {sql, explanation} or tool-calling) to show the user what's about to run.
• ✅ Assemble and test Reservo's assistant's complete system prompt with three questions.

---

Prerequisites

• Modules 1 and 2 of this guide. You need Reservo populated (M1) and to understand the schema as context (M2), which we reuse here inside the prompt.
• Python 3.10 or higher with sqlite3 (standard library). You don't need a database server or an API key: the call to the model is conceptual.
• Comfortable SQL reading. To judge whether the "generated" SQL is correct —the heart of this module— you have to be able to read it. Writing it by hand is the querying guide's job; reading it critically is essential here.

---

Module roadmap

Lesson	Topic	What you'll learn
01	Introduction (this lesson)	The guiding idea, the system prompt as an artifact, poor vs. rich
02	A SQL assistant's system prompt	The four pieces: role, dialect, schema, rules; assembling it
03	Few-shot: question→SQL examples	Giving 2-3 examples and the error they fix, run for real
04	Pinning the SQLite dialect	strftime vs. DATE_TRUNC/EXTRACT/NOW; another engine's error
05	Constraint instructions and format	Only SELECT, only these tables, replying with only the SQL; parsing it
06	Handling ambiguity	Asking for clarification or stating the assumption; two interpretations, two answers
07	SQL + structured explanation	JSON {sql, explanation} and a preview of tool-calling (M6)
08	Mini-project: Reservo's assistant prompt	Assembling the complete prompt and testing it with 3 questions

---

What is NOT covered in this module?

• ❌ How to introspect and serialize the schema — Module 2. Here the context block already exists; we reuse it inside the prompt.
• ❌ Validating the generated SQL (parsing, EXPLAIN QUERY PLAN, real references, self-correction loop) — Module 4. The "does it start with SELECT?" check we'll see is minimal and provisional.
• ❌ Hard guardrails (PRAGMA query_only, an enforced allowlist, limits, timeout, anti-injection) — Module 5. "Generate only SELECT" here is a prompt request, not an enforced defense.
• ❌ The agent loop and full tool-calling — Module 6. In lesson 07 we give a preview of the tool's shape, not the loop.
• ❌ Evaluating the assistant (execution accuracy, test set) — Module 7.
• ❌ General LLM prompting (chains of reasoning, RAG, memory) — AI Engineering ecosystem. Here it's the SQL slice: the prompt that produces good SQL.

---

Summary

• Giving the model the schema (M2) was necessary but not sufficient: with the perfect schema it can still sum cancelled bookings, use another engine's functions, or silently guess. This module closes that gap with the prompt.
• The guiding idea: the model answers with what you ask for (instructions) and what you show it (examples). Prompting for correct SQL is narrowing the distribution toward the SQL you consider correct.
• The module's artifact is Reservo's assistant's system prompt: role + dialect + schema (M2) + rules + few-shot. It's built piece by piece.
• Hard rule: the prompt and the model's response are conceptual (claude-sonnet-5); the resulting SQL and its parsing run with Python + sqlite3.
• Preview, run for real: the Focus question gave 47500 with a poor prompt (counted cancellations) and 34000 with a rich one (confirmed only). The prompt changes the number.

---

Additional resources

1. Claude — Giving Claude a role with a system prompt — The system message that pins down role and rules; the heart of this module.
2. Claude — Use examples (multishot prompting) — The few-shot technique you'll see in lesson 03.
3. SQLite — Date and time functions — The strftime/date reference we pin as the dialect in lesson 04.
4. Spider: Yale Semantic Parsing and Text-to-SQL — The academic benchmark where prompting and few-shot move the needle measurably.
5. BIRD: Big Bench for Large-Scale Database Grounded Text-to-SQL — Benchmark focused on execution accuracy over realistic databases.

---

Next lesson: A SQL assistant's system prompt — We'll take apart the four pieces (role, dialect, schema, rules), assemble Reservo's prompt, and run the SQL it produces to see why a precise system prompt beats a vague one.