Home / GenAI Tutorial / Day 9
Day 9 — AI for SQL
Give it the schema and it writes good SQL. Skip the schema and it invents your tables.
Always start with the schema
The status column is the part that matters
Nothing in a schema tells a model that cancelled orders exist and should be excluded. That is business knowledge, it lives only in your head, and leaving it out is how you get a query that runs perfectly and reports the wrong revenue.
Debugging a join
Fan-out from a one-to-many join is the most common cause of a silently wrong total in SQL. A model recognises the pattern instantly - if you show it the joins and mention that addresses can repeat.
Explaining an inherited query
There is a real bug in that query
The
WHERE clause filters on the right-hand table of a LEFT JOIN, which quietly turns it into an inner join and drops every order whose region_id has no match. A good model catches it. The point of the exercise is that you should catch it too - Day 4 of the SQL tutorial covers exactly this.Generating test data
Edge cases are the point
Anyone can write ten clean test rows. Asking specifically for the NULL, the cancelled order and the boundary date gives you data that actually exercises the query - and those are the rows that reveal bugs.
Optimising
The interesting one here is YEAR(o.order_date): wrapping an indexed column in a function stops the index being used. Rewriting it as a date range is usually the single biggest win, and it is the kind of thing a model spots reliably.
The rule that never changes
Run it on a subset first
An AI-written query is a draft. Before it goes near a report: run it with a LIMIT, check the row count against something you already know, and confirm the totals against an existing number. A query that runs is not a query that is right.
Try these yourself
- Write a prompt that includes a schema and one piece of business knowledge.
- Ask for a query that excludes cancelled orders and explain why you had to say so.
- Find the bug in the LEFT JOIN query and explain it in your own words.
- Write a prompt generating test data with three deliberate edge cases.
- List the checks you run before trusting an AI-written query.