Introduction
Writing efficient SQL queries is a skill that separates good database engineers from great ones. Whether you're building a reporting dashboard, debugging a slow application, or migrating legacy data, the quality of your SQL directly impacts performance, scalability, and maintainability. However, even experienced developers often struggle with complex joins, suboptimal indexing, or ugly query patterns. The good news is that modern AI assistants can act as your personal SQL tutor and optimization partner—if you know the right prompts.
In this guide, you'll find 10 ready-to-use prompts for generating, debugging, and optimizing SQL queries. Each prompt is designed to be copy-pasted into any AI tool (ChatGPT, Claude, or similar) and adapted to your specific database schema. We'll cover everything from basic SELECT queries to advanced performance tuning with execution plans. No fluff, just actionable prompts you can use today.
1. Generate a Query from a Natural Language Description
Task: Convert a business question into a correct SQL query.
Prompt:
I have a table named `orders` with columns: id, customer_id, order_date, total_amount, status. I need to find the total revenue for each customer in the last 30 days, but only for orders with status 'completed'. Show customer_id and revenue, sorted by revenue descending. Write the SQL query.
Usage example: Paste the prompt above to an AI. You'll get a query like:
SELECT customer_id, SUM(total_amount) AS revenue
FROM orders
WHERE status = 'completed'
AND order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY customer_id
ORDER BY revenue DESC;
This prompt works for any schema—just replace table and column names. For more complex joins, add foreign key relationships in the description.
2. Optimize a Slow Query with Index Recommendations
Task: Analyze a slow query and suggest indexes or rewrites.
Prompt:
This query is running very slowly on a table with 10 million rows:
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2025-01-01'
GROUP BY u.id;
Please:
1. Explain why it might be slow.
2. Suggest specific indexes (including composite indexes).
3. Recommend any query rewrites that could improve performance.
4. Provide the revised query with indexes.
Usage example: The AI will likely recommend:
- A composite index on orders(user_id, id) for faster join and count.
- An index on users(created_at) for the WHERE clause.
- Possibly a rewrite using a subquery with EXISTS if counting is not needed for all users.
3. Explain an Execution Plan in Plain English
Task: Understand what your database optimizer is doing.
Prompt:
Here is the EXPLAIN ANALYZE output from PostgreSQL for a query:
Seq Scan on orders (cost=0.00..100000.00 rows=5000000 width=8)
Filter: (status = 'pending')
Rows Removed by Filter: 4500000
Planning Time: 0.2 ms
Execution Time: 3500 ms
Explain what this means in simple terms. What is the bottleneck? How can I reduce the execution time?
Usage example: The AI will explain that a sequential scan on 5 million rows is slow, and suggest adding an index on orders(status) to enable an index scan, potentially reducing execution time from 3.5 seconds to milliseconds.
4. Debug a Common SQL Error
Task: Fix syntax, logic, or constraint violations.
Prompt:
I'm running this query and getting error "column 'email' does not exist":
SELECT username, email FROM users WHERE id = 100;
But I know the column is named 'email_address'. How do I fix it? Also, check if there are any other issues with this query.
Usage example: The AI will correct the column name and may suggest checking for typos, quoted identifiers, or case sensitivity. It could also recommend using SELECT * temporarily to verify column names.
5. Convert a Subquery to a JOIN (or Vice Versa)
Task: Refactor queries for better readability or performance.
Prompt:
Rewrite this query using a JOIN instead of a subquery. Also explain which version is likely faster and why.
SELECT name FROM products
WHERE id IN (SELECT product_id FROM inventory WHERE quantity > 0);
Usage example: The AI will produce:
SELECT p.name
FROM products p
INNER JOIN inventory i ON p.id = i.product_id
WHERE i.quantity > 0;
And explain that the JOIN version may be faster if the subquery is not well-optimized, but both can be equivalent depending on the database.
6. Generate Sample Data for Testing
Task: Create dummy data quickly for development or demos.
Prompt:
Generate 10 INSERT statements for a table `employees` with columns: id (SERIAL), name (VARCHAR), department (VARCHAR), salary (INTEGER), hire_date (DATE). Use realistic names and departments like 'Engineering', 'Sales', 'HR'. Make salaries range from 45000 to 120000.
Usage example: You'll get ready-to-run SQL like:
INSERT INTO employees (name, department, salary, hire_date) VALUES
('Alice Johnson', 'Engineering', 95000, '2023-04-15'),
('Bob Smith', 'Sales', 62000, '2024-01-10'),
...
7. Design a Database Schema from Requirements
Task: Create normalized tables with relationships.
Prompt:
Design a normalized database schema for an online bookstore. Requirements:
- Books have title, author, ISBN, price, publication year.
- Authors have name, biography, birth date.
- Customers have name, email, address.
- Orders have order date, total amount, status.
- An order contains multiple order items (book, quantity, unit price).
Generate CREATE TABLE statements with primary keys, foreign keys, and appropriate data types. Use PostgreSQL syntax.
Usage example: The AI will produce tables like books, authors, customers, orders, order_items with proper relationships and constraints.
8. Write a Recursive CTE for Hierarchical Data
Task: Query tree structures (e.g., categories, org charts).
Prompt:
I have a table `categories` with columns: id, name, parent_id (nullable). Write a recursive CTE to get all subcategories under a given parent category (e.g., id = 5). Include the depth level and full path name.
Usage example: The AI will generate:
WITH RECURSIVE category_tree AS (
SELECT id, name, parent_id, 0 AS depth, name AS path
FROM categories WHERE id = 5
UNION ALL
SELECT c.id, c.name, c.parent_id, ct.depth + 1, ct.path
|| ' > ' || c.name
FROM categories c
INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;
9. Format and Beautify Ugly SQL
Task: Clean up messy queries automatically.
Prompt:
Reformat this SQL query to be readable with proper indentation, uppercase keywords, and line breaks:
select u.name,o.total from users u join orders o on u.id=o.user_id where u.active=true order by o.total desc limit 10
Usage example: The AI will return a clean version:
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.active = TRUE
ORDER BY o.total DESC
LIMIT 10;
10. Generate a Query for Database Migration
Task: Write SQL to transform data between schemas.
Prompt:
I need to migrate data from an old table `customer_old` (columns: id, full_name, email, region) to a new table `customers_new` (columns: id, first_name, last_name, email, country). The old full_name is like 'John Smith'. The old region is a numeric code: 1=USA, 2=Canada, 3=UK. Write an INSERT INTO ... SELECT statement that splits the name and maps the region to country name.
Usage example: The AI will generate a migration query using SPLIT_PART and a CASE statement to handle the transformation.
Conclusion
Mastering SQL is not just about memorizing syntax—it's about knowing how to communicate with your database efficiently. The prompts above empower you to leverage AI as a coding partner, saving hours of manual debugging and research. Try them in your next project: start with a natural language description, then optimize with index suggestions, and finally validate with an execution plan analysis. As you practice, you'll develop an intuition for writing performant queries without relying on AI. But when you're stuck, these prompts will be your shortcut to expert-level SQL.
Remember: the best SQL is the one that runs fast, is easy to read, and produces correct results. Use these prompts to achieve all three.
Comments