All categories/Data and analysis
Level Advanced · SQL Query Database
Natural language to SQL: schema first
You have tables and a business question, and you do not want invented column names.
Prompt
Write SQL for this question.
Question: {{question}}
Database: {{db}}
Schema (only the relevant tables and columns, with types):
{{schema}}
Requirements:
1. First list which columns you use and which table each comes from, plus how you read the relationships (one-to-many, many-to-many). Mark inferred relationships as 'inferred, needs confirmation'.
2. Give the SQL using only the column names above; invent none. If something is missing, stop and ask.
3. State the aggregation rules: deduplicated or not, how nulls are handled, whether the date range is open or closed.
4. Add a sampling query that returns 20 rows so I can sanity-check the detail.What to state before it starts
Leave these unsaid and the model fills them with an assumption. Most 'wrong answers' start here.
- {{question}}: write it in business language, do not pre-translate to SQL
- {{db}}: MySQL / PostgreSQL / SQLite; dialects differ a lot
- {{schema}}: pasting CREATE TABLE is easiest
Go through each of these
Where this kind of work most often goes wrong. Already in the prompt, so you do not have to remember it.
- Say 'invent no columns' explicitly, or it adds a plausible one.
- The sampling query is how you catch a wrong join.