How to Write SQL Using AI

In short: paste the CREATE TABLE lines (not real data), name your database (PostgreSQL, MySQL, SQLite, SQL Server), state the question, and test on a tiny sample with a known answer.

Why SQL with AI

Almost every company stores data in SQL databases: banks, e-commerce, hospitals. Analysts and developers write queries daily. AI writes them fast, but a wrong JOIN or date filter gives wrong numbers that look right.

What you need

• The schema: the CREATE TABLE statements: table names, columns, types, keys.
• Your database name and version: SQL differs a little between PostgreSQL, MySQL, SQLite and SQL Server.
• A small sample: a few rows where you know the right answer.
• A safe place to run it: a local copy or test database, never production first.

How it flows

Set up a safe practice database

1. Python already includes SQLite. No install needed.
2. Or install PostgreSQL / MySQL locally, or use a Docker container.
3. Create the sample tables below and load the 5 rows.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  amount NUMERIC(10,2) NOT NULL,
  created_at DATE NOT NULL
);
INSERT INTO customers VALUES (1,'Asha','Pune'),(2,'Ravi','Mumbai'),(3,'Meena','Nagpur');
INSERT INTO orders VALUES
 (1,1,1200.00,'2026-01-15'),(2,1,800.00,'2026-03-02'),(3,2,5000.00,'2026-02-20'),
 (4,3,300.00,'2025-12-30'),(5,2,700.00,'2026-07-01');

Step 1 — the prompt

Step 2 — the query (tested here)

-- Total spend per customer in 2026, highest first. Built with an AI assistant, then tested.
SELECT c.name,
       c.city,
       COUNT(o.id)   AS orders,
       SUM(o.amount) AS total
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id
WHERE o.created_at >= '2026-01-01'
  AND o.created_at <  '2027-01-01'
GROUP BY c.id, c.name, c.city
ORDER BY total DESC;

# expected on the sample (count by hand first):
# Ravi   Mumbai  2  5700   (5000 + 700)
# Asha   Pune    2  2000   (1200 + 800)
# Meena is not listed: her only order is in 2025

Step 3 — run queries from code safely

Ask: “Show me how to run this from Python with a parameter for the city, no string joining.”

"""Run a query safely with a parameter (no string joining). Tested with SQLite."""
import sqlite3

def orders_for_city(conn, city):
    sql = """SELECT o.id, o.amount FROM orders o
             JOIN customers c ON c.id = o.customer_id
             WHERE c.city = ? ORDER BY o.id"""
    return conn.execute(sql, (city,)).fetchall()

import sqlite3
from safe_query import orders_for_city

def db():
    conn = sqlite3.connect(":memory:")
    conn.executescript(open("schema.sql").read())
    return conn

def test_top_customers_2026():
    rows = db().execute(open("top_customers.sql").read()).fetchall()
    assert rows == [("Ravi", "Mumbai", 2, 5700), ("Asha", "Pune", 2, 2000)]  # Meena's order is 2025

def test_parameter_blocks_injection():
    conn = db()
    assert orders_for_city(conn, "Pune") == [(1, 1200), (2, 800)]
    assert orders_for_city(conn, "Pune' OR '1'='1") == []

pytest -q
# -> 2 passed

The second test passes an attack string as the city. Because of the ? parameter, it is treated as plain text and returns nothing.

How to check any AI query

1. Count by hand on 5–10 rows first.
2. Check every JOIN: INNER JOIN drops rows with no match; LEFT JOIN keeps them.
3. Check date ranges: use >= start AND < next_start; avoid BETWEEN with timestamps.
4. Check GROUP BY includes every non-aggregated column.
5. Look at the plan: EXPLAIN (PostgreSQL/MySQL) before running on big tables.
6. Run as a read-only user when exploring real data.

Watch out (common AI mistakes in SQL)

Quick quiz

Save tokens and money

Paste only the tables the query needs, not the whole database schema. Ask for the query only, without long explanations. Reuse a working prompt as a template for similar reports.

Save these cards