From a question to a correct query.

To write SQL with AI, give the model three things: the exact schema, a few sample rows and a precise question. Ask for the query plus an explanation of every clause. Then run it on a small dataset where you already know the answer, and only then on real data, through a read-only user.

The danger is not a query that fails. It is a query that runs and returns the wrong number. We built a small SQLite database to show the four classic traps, and every query and result below is real.

01Step 1: give the model the schema and sample rows

A model that cannot see your database will invent plausible table and column names, a classic AI hallucination. The fix is cheap. In SQLite, two commands print everything the model needs:

sqlite3 shop.db .schema
sqlite3 -markdown shop.db "SELECT * FROM orders LIMIT 5"

CREATE TABLE customers (
  id    INTEGER PRIMARY KEY,
  name  TEXT NOT NULL,
  email TEXT
);
CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),  -- NULL = guest checkout
  created_at  TEXT NOT NULL,                     -- UTC, 'YYYY-MM-DD HH:MM:SS'
  shipping    NUMERIC NOT NULL DEFAULT 0
);
CREATE TABLE order_items (
  order_id   INTEGER NOT NULL REFERENCES orders(id),
  product    TEXT NOT NULL,
  qty        INTEGER NOT NULL,
  unit_price NUMERIC NOT NULL
);
| id  | customer_id |     created_at      | shipping |
|-----|-------------|---------------------|----------|
| 101 | 1           | 2026-08-01 02:15:00 | 5        |
| 102 | 1           | 2026-08-14 16:40:00 | 5        |
| 103 | 2           | 2026-08-31 19:05:00 | 5        |
| 104 | 2           | 2026-09-01 01:30:00 | 5        |
| 105 |             | 2026-08-20 12:00:00 | 5        |

Notice the comments. .schema keeps them, so the model learns that times are in UTC and that an empty customer means a guest checkout. Those two facts decide whether the answer is right. In PostgreSQL, pg_dump --schema-only --no-owner shop prints the schema the same way.

02Step 2: write SQL with AI, and make it explain itself

Say which database engine you use, because date functions differ a lot between SQLite, PostgreSQL and MySQL. Then define every business term. “Revenue” and “August” sound obvious until you ask whether shipping counts and whose midnight starts the month.

Write one SQLite query for the question below.

Schema:
[paste the output of .schema]

Sample rows:
[paste 3 to 5 rows per table]

Question: revenue per customer for August 2026, in New York time, including customers with no orders.
Definitions: revenue is the sum of qty * unit_price for each order, plus that order's shipping. created_at is stored in UTC.

Then:
1. Explain each clause in one line.
2. List every assumption you made.
3. Say how the query handles NULLs, duplicate rows from joins, and the first and last day of the range.
4. Give me one small query I can run to check the result.

The explanation is not decoration. If you cannot follow it, you cannot check the query, so ask again until you can.

03Step 3: test on data where you know the answer

Our test database has three customers and five orders, so the right answer can be worked out by hand: Ben 82.00, Ana 33.50, Chloe 0. Here is a first draft that looks reasonable and runs without an error:

SELECT c.name,
       SUM(i.qty * i.unit_price) + SUM(o.shipping) AS revenue
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
LEFT JOIN order_items i ON i.order_id = o.id
WHERE o.created_at BETWEEN '2026-08-01' AND '2026-08-31'
GROUP BY c.name
ORDER BY revenue DESC;

| name | revenue |
|------|---------|
| Ana  | 56.0    |

One customer instead of three, and Ana’s number is wrong too. Every mistake in it is one of the four classics below. Small test data is what makes them visible: on a million rows, 56.0 would have looked perfectly plausible.

04The 4 classic mistakes in AI-written SQL

1. Joins that multiply rows

Joining orders to their items repeats each order once per item. Order 102 has two items, so its shipping fee appears twice and gets summed twice:

| id  | shipping | product |
|-----|----------|---------|
| 102 | 5        | Tea     |
| 102 | 5        | Mug     |

The fix is to total each order first, then join the totals to customers. A quick check catches this every time: count the rows before and after each join, and ask why the number grew.

2. A LEFT JOIN that quietly becomes an inner join

A LEFT JOIN keeps customers with no orders, filling the order columns with NULL. But the WHERE clause then filters on o.created_at, and NULL never passes a comparison, so Chloe disappears. Filter the optional table before the join, or inside the ON clause. Then wrap the sum in COALESCE(..., 0), so no orders shows 0 instead of NULL.

3. NOT IN with a NULL in the list

Ask “which customers never ordered?” and the obvious query returns nothing at all:

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

The guest order has a NULL customer_id, and NOT IN against a list containing NULL is never true. SQLite returned zero rows, with no warning. NOT EXISTS gives the right answer, Chloe:

SELECT name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

4. Date boundaries and time zones

BETWEEN '2026-08-01' AND '2026-08-31' compares text here, and '2026-08-31 19:05:00' sorts after '2026-08-31'. So almost all of August 31 is left out. Use a half-open range instead: on or after the first moment, and strictly before the next month.

The time zone matters as much. The times are stored in UTC, and New York runs four hours behind UTC all through August. Order 101, at 02:15 UTC on August 1, was placed on July 31 in New York. Order 104 is the reverse: September 1 in UTC, but August 31 in New York. SQLite has no time zone database, so convert the boundaries yourself: midnight in New York is 04:00 UTC.

Here is the corrected query:

-- August 2026 in New York time is UTC-4 all month (daylight saving time)
WITH aug_orders AS (
  SELECT id, customer_id, shipping
  FROM orders
  WHERE created_at >= '2026-08-01 04:00:00'
    AND created_at <  '2026-09-01 04:00:00'
),
order_totals AS (
  SELECT a.id, a.customer_id,
         a.shipping + COALESCE(SUM(i.qty * i.unit_price), 0) AS total
  FROM aug_orders a
  LEFT JOIN order_items i ON i.order_id = a.id
  GROUP BY a.id, a.customer_id, a.shipping
)
SELECT c.id, c.name, ROUND(COALESCE(SUM(t.total), 0), 2) AS revenue
FROM customers c
LEFT JOIN order_totals t ON t.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY revenue DESC;

| id | name  | revenue |
|----|-------|---------|
| 2  | Ben   | 82.0    |
| 1  | Ana   | 33.5    |
| 3  | Chloe | 0.0     |

It matches the hand calculation. In PostgreSQL, with a timestamptz column, let the database convert instead. We ran this on PostgreSQL 16 and got the same three rows:

WHERE created_at >= TIMESTAMP '2026-08-01' AT TIME ZONE 'America/New_York'
  AND created_at <  TIMESTAMP '2026-09-01' AT TIME ZONE 'America/New_York'

When a number still looks off, treat the query like any other bug and debug it one hypothesis at a time.

05Step 4: give AI tools a read-only user

Once you trust the workflow, you may want an AI agent to query the database directly, for example through an MCP server. Never hand it your admin login. Give it a user that can read and nothing else.

In SQLite, open the file read-only. Our test DELETE failed with attempt to write a readonly database:

sqlite3 -readonly shop.db

In PostgreSQL, create a role that can only read the tables it needs:

CREATE ROLE ai_reader LOGIN PASSWORD 'use-a-long-random-password';
GRANT CONNECT ON DATABASE shop TO ai_reader;
GRANT USAGE ON SCHEMA public TO ai_reader;
GRANT SELECT ON orders, order_items TO ai_reader;
GRANT SELECT (id, name) ON customers TO ai_reader;
ALTER ROLE ai_reader SET default_transaction_read_only = on;
ALTER ROLE ai_reader SET statement_timeout = '10s';

We tested each layer as ai_reader. A DELETE failed with cannot execute DELETE in a read-only transaction. Reading email failed with permission denied for table customers, because the role may only see id and name. A query that ran past ten seconds was cancelled.

The read-only default is a seatbelt, not a lock: the user can switch it off. The grants are the lock. After we switched it off, the DELETE still failed with permission denied for table orders.

06FAQ

Read next: if your data lives in sheets rather than a database, see how to use AI with spreadsheets.