Introduction
Writing efficient SQL queries and optimizing database performance are critical skills for any developer or data professional. However, even experienced engineers can spend hours debugging slow queries or crafting complex joins. Large language models (LLMs) like GPT-4 and Claude can dramatically accelerate this process—if you know how to prompt them correctly. This article presents 10 ready-to-use prompts for generating, debugging, and optimizing SQL queries, with real-world examples and best practices.
Whether you're a beginner trying to understand window functions or a senior engineer tuning a PostgreSQL database, these prompts will save you time and help you write cleaner, faster SQL.
1. Generate a Query from a Natural Language Description
Prompt:
I need a SQL query for [database type: PostgreSQL/MySQL/SQL Server] that does the following: [describe business logic].
The relevant tables are:
- [table1] with columns [col1, col2, ...]
- [table2] with columns [col1, col2, ...]
Foreign keys: [describe relationships]
Please output only the SQL query, with comments explaining each part.
Explanation: This prompt forces the AI to understand your schema and produce a syntactically correct query. By specifying the database type, you get dialect-specific syntax (e.g., LIMIT vs TOP, ILIKE vs LIKE).
Example:
User input: "Find the top 5 customers by total order value in the last 30 days. Database: PostgreSQL. Tables: customers(id, name), orders(id, customer_id, amount, created_at)."
AI output:
SELECT
c.name,
SUM(o.amount) AS total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY c.id, c.name
ORDER BY total_spent DESC
LIMIT 5;
2. Debug a Slow Query
Prompt:
I have a query that takes [X seconds] to run. Here is the query:
[your SQL code]
Here is the EXPLAIN ANALYZE output:
[output]
Please identify the bottleneck and suggest specific indexes or rewrites to improve performance.
Explanation: This prompt leverages the AI's ability to read execution plans and suggest concrete improvements like missing indexes, inefficient joins, or suboptimal WHERE clauses.
Example:
User input: Query runs 12 seconds on a 5M-row table. EXPLAIN shows a sequential scan on orders with a filter on status.
AI suggestion: "Add a composite index on orders(status, created_at) to reduce the scan. Also, rewrite the IN subquery as a JOIN for better performance."
3. Rewrite a Query Using Window Functions
Prompt:
Rewrite the following query using window functions (ROW_NUMBER, RANK, LAG, etc.) to make it more efficient and readable:
[your query with self-joins or subqueries]
Database: [type].
Explanation: Many developers overuse self-joins or correlated subqueries for tasks like "find the latest order per customer." Window functions are often faster and cleaner.
Example:
User query (inefficient):
SELECT c.*, o.*
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.created_at = (SELECT MAX(created_at) FROM orders o2 WHERE o2.customer_id = c.id);
AI rewrite:
SELECT c.*, o.*
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
FROM orders
) o
JOIN customers c ON c.id = o.customer_id
WHERE o.rn = 1;
4. Normalize a Denormalized Table
Prompt:
I have a denormalized table [table_name] with columns [list]. It violates [1NF/2NF/3NF] because [describe redundancy].
Generate SQL to split it into normalized tables, including foreign keys and indexes.
Explanation: This prompt helps you design a proper relational schema from a flat file or legacy table.
Example:
User input: Table orders_flat has columns: order_id, customer_name, customer_email, product1, product2, product3.
AI output: Creates customers, orders, order_items tables with proper normalization.
5. Optimize a JOIN-Heavy Query with Execution Plan Analysis
Prompt:
Here is a query that joins 6 tables and runs slowly. Database: PostgreSQL.
[query]
EXPLAIN ANALYZE output:
[output]
Please suggest:
- Which indexes to add
- Whether to use hash join vs merge join
- Any CTE or temp table optimizations
Explanation: This is an advanced prompt for performance tuning. It combines execution plan reading with indexing advice.
6. Convert Between SQL Dialects
Prompt:
Convert this [MySQL] query to [PostgreSQL]. Pay attention to:
- Date functions
- LIMIT / TOP
- String concatenation
- Auto-increment syntax
Original query:
[query]
Explanation: When migrating databases, syntax differences can break queries. This prompt handles dialect-specific functions.
7. Generate Test Data for a Schema
Prompt:
Generate a set of INSERT statements to populate the following schema with [N] rows of realistic test data:
[CREATE TABLE statements]
Consider:
- Foreign key constraints (insert parent tables first)
- Realistic names, dates, prices
- Use random functions like RANDOM() or UUID generation
Explanation: Quickly create sample data for development or testing without manual work.
8. Write a Recursive CTE for Hierarchical Data
Prompt:
I have a table [table_name] with a parent_id column representing a tree structure. Write a recursive CTE to:
- Get all descendants of a given node
- Include depth level
- Order by hierarchy
Explanation: Recursive CTEs are powerful but tricky. This prompt yields a correct template for any tree structure (categories, org charts, etc.).
9. Optimize a Query with Anti-Joins and EXISTS
Prompt:
Rewrite the following query to use anti-joins (NOT EXISTS) or EXISTS instead of NOT IN to improve performance:
[query with NOT IN or LEFT JOIN ... IS NULL]
Explain why the rewrite might be faster.
Explanation: NOT IN can be slow with NULLs and large datasets. This prompt teaches best practices.
10. Add Indexing Recommendations Based on Query Patterns
Prompt:
Given the following query workload (list of queries with their frequency), suggest an indexing strategy:
- Query 1: [SQL, runs 1000 times/day]
- Query 2: [SQL, runs 500 times/day]
- Table size: [N] rows
- Database: [type]
For each index, specify:
- Columns and order
- Index type (B-tree, hash, GIN, etc.)
- Whether it's a covering index
Explanation: This prompt helps design indexes based on actual usage, not just single queries.
Real-World Case Study
Problem: An e-commerce platform (PostgreSQL) had a product search page that took 8–15 seconds to load. The query joined 5 tables (products, categories, inventory, reviews, prices) and used multiple OR conditions.
Solution using prompts:
1. Used Prompt #2 to debug the slow query. The AI identified a missing composite index on inventory(product_id, warehouse_id) and a full table scan on reviews.
2. Used Prompt #5 to rewrite the query with a CTE that pre-filtered reviews by rating.
3. Used Prompt #10 to suggest a covering index on products for the search columns.
Results:
- Query time dropped from 12s to 0.3s.
- Index size increased by 2GB, but storage is cheap compared to user frustration.
- Page load time improved from 15s to 1.2s.
Lessons learned:
- Always run EXPLAIN ANALYZE before optimizing.
- Composite indexes with column order matter: put high-selectivity columns first.
- CTEs can help but may materialize data—test with EXPLAIN ANALYZE.
Conclusion
Mastering SQL prompts can turn an AI into your personal database consultant. Use the 10 prompts above to generate, debug, and optimize queries faster than ever. Remember to always test generated SQL against your actual data and execution plans—AI can make mistakes, especially with complex joins or database-specific quirks.
Start with a simple prompt today, and you might be surprised how much time you save. And if you're looking for a platform that integrates AI-powered SQL generation with your data stack, ASI Biont supports connecting to PostgreSQL, MySQL, and SQL Server via API — learn more at asibiont.com/courses.
Comments