SQL Prompt Engineering: Queries, Optimization & Schema Design

SQL is deceptively simple to write but notoriously hard to write well. A query that looks correct can perform orders of magnitude worse than an equivalent optimized version. For data analysts, backend engineers, and anyone who works with relational databases, AI-assisted SQL prompting is one of the highest-leverage skills you can develop.

The difference between a query that takes 10 milliseconds and one that takes 10 seconds often comes down to indexing, join order, or whether you are accidentally triggering a full table scan. AI can spot these issues — but only if your prompt includes the execution context, not just the query text.

This guide covers four core SQL prompting workflows: writing queries from requirements, optimizing slow queries, designing schemas, and translating business questions into SQL. All examples are tested against PostgreSQL and MySQL.

Prompt Pattern 1: Requirements to Query

The most common SQL task is translating a business requirement into a working query. The key to getting good AI output is providing the schema context explicitly.

Act as a senior database engineer specializing in [PostgreSQL / MySQL / SQL Server].

SCHEMA:
[table_name]
- id (INT, PK)
- customer_id (INT, FK → customers.id)
- order_date (DATE)
- total_amount (DECIMAL(10,2))
- status (VARCHAR) — values: 'pending', 'shipped', 'delivered', 'cancelled'

BUSINESS REQUIREMENT:
[Describe what data you need]

Please:
1. Write the SQL query to satisfy this requirement
2. Explain your query logic step by step
3. Suggest any indexes that would help this query perform well
4. Identify any edge cases (NULL values, missing data) the query should handle

Prompt Pattern 2: Query Optimization

When a query is slow, the AI can suggest optimizations — but only if you provide execution context.

My [DATABASE] query is running slowly. Here are the details:

QUERY:
[PASTE SQL]

EXPLAIN ANALYZE OUTPUT:
[PASTE OUTPUT]

TABLE SIZES:
- table_a: ~[X] rows
- table_b: ~[Y] rows

CURRENT EXECUTION TIME: [X] ms
TARGET EXECUTION TIME: [Y] ms

Please:
1. Identify the slowest operation from the EXPLAIN output
2. Explain why it is slow
3. Suggest specific optimizations (rewrites, indexes, structural changes)
4. Show the optimized query
5. Estimate the expected performance improvement

Prompt Pattern 3: Schema Design Review

Schema mistakes made early in a project become exponentially harder to fix as data grows. Catching them during design review saves months of migration work later.

Before building a new feature, use AI to review your schema design for scalability and correctness.

Review this database schema for a [TYPE OF APPLICATION]. Assume [EXPECTED SCALE] rows in the largest table and [READ/WRITE PATTERN].

SCHEMA:
[PASTE DDL OR DESCRIPTION]

Please evaluate:
1. Normalization level — is it appropriate for the use case?
2. Index strategy — are the right columns indexed?
3. Data type choices — any precision or storage concerns?
4. Foreign key constraints — are cascading rules correct?
5. Scalability concerns — any bottlenecks at higher scale?
6. One thing you would change and why

Prompt Pattern 4: Business Question Translation

Non-technical stakeholders ask questions in English. The AI can bridge the gap.

I have a database with these tables:
[SCHEMA DESCRIPTION]

My stakeholder asked: "[ENGLISH QUESTION]"

Please:
1. Translate this into a precise SQL query
2. Explain what the query does in non-technical terms I can relay to the stakeholder
3. Note any assumptions the query makes
4. Suggest a visualization or chart that would best communicate the answer

Writing Complex Queries

For complex analytical queries, use a step-by-step decomposition approach:

I need a complex SQL query for [DATABASE]. Break this into logical steps:

1. First, identify [subtask 1]
2. Then, join with [subtask 2]
3. Finally, calculate [subtask 3]

Here is the schema:
[SCHEMA]

Please write the query as a CTE (WITH clause) with one CTE per step so I can understand each part independently. Add comments explaining what each CTE does.

What AI Gets Wrong with SQL

• Dialect-specific syntax: Always specify your database engine. MySQL and PostgreSQL handle window functions, CTEs, and JSON operations differently.
• Missing indexes: The AI will suggest indexes, but it does not know your existing indexes. Always cross-check.
• Scale assumptions: A query that works on 1,000 rows may crash on 1,000,000. Tell the AI your table sizes.

Next Steps

Continue to our API Documentation Prompts guide to learn how to generate clear, developer-friendly documentation for your database-backed APIs.