AI-Powered SQL Writing

The Schema-First Prompt Pattern

The single most important technique for AI-assisted SQL: always provide your schema.

Bad prompt: “Write a query to find inactive customers.”

Good prompt:

Database: PostgreSQL
Tables:
- customers (id SERIAL, name TEXT, email TEXT, created_at TIMESTAMP)
- orders (id SERIAL, customer_id INT REFERENCES customers(id), total DECIMAL, order_date DATE, status TEXT)

Write a query to find customers who haven't placed an order in the last 90 days but had at least one order before that. Include their name, email, last order date, and total lifetime spending.

The schema context eliminates guesswork. AI knows your exact column names, data types, and relationships — producing queries that run on the first try.

✅ Quick Check: Why does specifying DECIMAL for the total column and DATE for order_date matter in the prompt?

Because data types determine which functions work. If AI thinks a date column is TEXT, it might try string comparison instead of proper date arithmetic. If it doesn’t know total is DECIMAL, it might not handle rounding correctly. Explicit types produce more accurate queries — especially for date calculations and numeric aggregations.

Simple Queries: SELECT, WHERE, ORDER BY

Start with straightforward retrieval queries:

Database: [your system]
Table: products (id, name, price, category, stock_quantity, created_at)

Write queries for:
1. All products under $50 in the 'electronics' category, sorted by price
2. Products with stock_quantity below 10 (low stock alert)
3. The 5 most expensive products added this month
4. Count of products per category

AI handles these reliably. But always check the output — even simple queries can have wrong comparison operators or date boundaries.

JOINs: Where Mistakes Happen

JOINs are where AI-generated SQL most often goes wrong. Common errors:

Duplicate rows: AI uses a regular JOIN when you need GROUP BY or DISTINCT.

Missing data: AI uses INNER JOIN when you need LEFT JOIN (customers without orders disappear).

Wrong relationship: AI joins on the wrong column when table names are ambiguous.

Database: PostgreSQL
Tables:
- customers (id, name, email, city)
- orders (id, customer_id, total, order_date)
- order_items (id, order_id, product_id, quantity, unit_price)
- products (id, name, category, price)

Write a query showing each customer's name, total orders, total spending, and their most-purchased product category. Include customers with zero orders (show 0 for totals).

Important: avoid duplicate counting from the order_items join.

Verification tip: After running a JOIN query, check the row count. If you have 1,000 customers and the query returns 5,000 rows, you likely have an unwanted cartesian product from the JOIN.

Aggregations and GROUP BY

Aggregation queries (SUM, COUNT, AVG) with GROUP BY and HAVING are AI’s sweet spot — they’re tedious to write manually but AI handles them well:

Database: MySQL
Tables:
- sales (id, product_id, customer_id, amount, sale_date, region)
- products (id, name, category, cost)

Write these queries:
1. Monthly revenue by region for the last 12 months
2. Top 5 product categories by profit margin (revenue minus cost)
3. Customers who have spent more than $10,000 total, ordered by spending
4. Year-over-year growth rate per product category

✅ Quick Check: Why is query #4 (year-over-year growth rate) harder for AI than query #1 (monthly revenue)?

Because growth rate requires comparing two different time periods — this year vs. last year — for the same category. This typically needs window functions or self-joins, which are more complex. AI can do it, but you should verify the date boundaries carefully. A common AI mistake: comparing calendar years when your business uses fiscal years.

Subqueries and CTEs

For complex analysis, teach AI to use Common Table Expressions (CTEs) for readability:

Database: PostgreSQL

I need a query that shows:
- Each customer's spending rank within their city
- Whether they're above or below the city average
- Their percentile within all customers

Use CTEs (WITH clauses) to make the query readable and maintainable. Break the logic into named steps.

CTEs make complex queries easier to debug. If one section is wrong, you can run just that CTE independently.

The Iterative Refinement Loop

Real-world query writing with AI follows this pattern:

1. First prompt: Describe what you need with full schema context
2. Run the query: Check for syntax errors
3. Verify results: Compare against known data points
4. Refine: Tell AI specifically what’s wrong (“the date range should be inclusive,” “I’m getting duplicate rows from the JOIN”)
5. Repeat until results match expectations

This loop typically takes 2-3 iterations for complex queries — still much faster than writing from scratch.

Exercise: Build a Query Suite

Using your own database (or the example schema above), ask AI to write:

1. A simple SELECT with filtering and sorting
2. A multi-table JOIN with aggregation
3. A query using a window function (ROW_NUMBER, RANK, or running total)
4. A CTE-based query that breaks complex logic into readable steps

For each query: run it, verify the results, and iterate if needed.

Key Takeaways

• Always include your schema (table names, columns, types, relationships) when prompting AI for SQL
• Specify your database system — PostgreSQL, MySQL, and SQL Server have different syntax for dates, strings, and advanced features
• JOINs are where AI makes the most mistakes — verify row counts and check for duplicates or missing data
• Iterative refinement (prompt → run → verify → fix) typically takes 2-3 cycles for complex queries
• CTEs make complex AI-generated queries readable and easier to debug section by section
• The verification mindset from Lesson 1 applies to every query: check results against known data before trusting the output

Up Next: In the next lesson, you’ll learn to design database schemas with AI — turning business requirements into normalized, efficient table structures.