You're staring at a 200-line SQL query that's slower than a snail on a treadmill. You've tried everything: rewriting, indexing, even sacrificing a rubber duck to the database gods. But what if you could just ask an AI to fix it? Welcome to the future of database work, where AI assistants can write, debug, and optimize SQL queries in seconds. This playbook isn't just a list of prompts; it's a tactical manual for modern developers, data analysts, and DBAs who want to harness AI for everything from complex JOINs to schema design. Each prompt is battle-tested, with real-world examples and the 'why' behind it. Let's dive in.
1. The All-Rounder: Generate a Query from a Plain-English Description
When to use: You need to quickly translate a business requirement into SQL without wrestling with syntax.
Prompt:
You are a senior SQL expert. Write a PostgreSQL query that returns the top 5 customers by total order value in the last 30 days, including their name, email, and total spent. Use the 'customers' and 'orders' tables. Assume 'orders' has a 'customer_id' foreign key and an 'order_date' timestamp. Explain the query step by step.
Example output:
SELECT c.name, c.email, SUM(o.total_amount) AS total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.order_date >= NOW() - INTERVAL '30 days'
GROUP BY c.id, c.name, c.email
ORDER BY total_spent DESC
LIMIT 5;
Why it works: The prompt specifies the database (PostgreSQL), the exact tables, the time frame, and the desired output. This reduces ambiguity and produces a ready-to-run query.
2. The Debugger: Find and Fix Errors in a Broken Query
When to use: You have a query that's throwing cryptic errors, and you're about to lose your mind.
Prompt:
Here's a MySQL query that's failing with "Unknown column 'o.total' in 'field list'". Fix it. The 'orders' table has a column named 'amount', not 'total'. Also, check for any other logical errors. Explain each fix.
SELECT c.name, SUM(o.total) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.name HAVING total_spent > 1000;
Example output:
SELECT c.name, SUM(o.amount) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.name HAVING total_spent > 1000;
Fixes:
1. Changed o.total to o.amount to match the actual column.
2. The rest is logically correct, but note that HAVING can't reference an alias in some MySQL versions; it's safer to repeat the SUM expression.
3. The Optimizer: Analyze and Improve Slow Query Performance
When to use: Your query works, but it's as slow as a database without indexes. You need a performance audit.
Prompt:
I have a PostgreSQL query that takes 15 seconds. I have an index on 'orders.customer_id' and 'orders.order_date'. Here's the query:
SELECT c.name, SUM(o.amount) AS total
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.order_date >= '2026-01-01'
GROUP BY c.name;
Use the EXPLAIN ANALYZE output (I'll provide it) to identify bottlenecks and suggest optimizations. If not provided, give general optimization tips based on the schema.
Example output:
- Check if the index is being used; if not, use SET enable_seqscan = off to test.
- Consider a covering index on orders(customer_id, amount, order_date).
- Use EXPLAIN (ANALYZE, BUFFERS) for detailed insight.
4. The JOIN Master: Craft Complex Joins with Multiple Conditions
When to use: You need to combine data from multiple tables with intricate relationships.
Prompt:
Write a MySQL query to get a list of all customers who have placed orders in the last year, along with their most recent order date and the total number of orders. Include customers who haven't ordered (show 0). Use LEFT JOIN, and handle NULLs. Tables: customers (id, name), orders (id, customer_id, order_date).
Example output:
SELECT c.name, MAX(o.order_date) AS last_order, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
AND o.order_date >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
GROUP BY c.id, c.name;
Why it works: The prompt specifies the join type and the condition inside the JOIN, which is crucial for counting only last year's orders.
5. The Window Function Wizard: Rank, Partition, and Aggregate with Finesse
When to use: You need row numbering, running totals, or moving averages—window functions are your best friend.
Prompt:
Using PostgreSQL, write a query to rank customers by total purchase amount, showing rank, name, total, and a running total. Use RANK() and SUM() OVER(). Ensure ties get the same rank.
Example output:
SELECT name, total,
RANK() OVER (ORDER BY total DESC) AS rank,
SUM(total) OVER (ORDER BY total DESC ROWS UNBOUNDED PRECEDING) AS running_total
FROM (
SELECT c.name, SUM(o.amount) AS total
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.name
) AS customer_totals;
6. The Schema Architect: Design a Database Schema for a New Feature
When to use: You're starting a new project and need a normalized schema that follows best practices.
Prompt:
Design a PostgreSQL schema for a simple e-commerce platform with customers, products, orders, and order_items. Include primary keys, foreign keys, indexes, and check constraints (e.g., price > 0). Follow 3NF. Provide the SQL DDL and a brief explanation.
Example output:
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL
);
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
price NUMERIC(10,2) CHECK (price >= 0)
);
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INT REFERENCES customers(id),
order_date TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE order_items (
order_id INT REFERENCES orders(id),
product_id INT REFERENCES products(id),
quantity INT CHECK (quantity > 0),
PRIMARY KEY (order_id, product_id)
);
7. The Index Strategist: Recommend Indexes Based on Query Patterns
When to use: You have a set of slow queries and need to know which indexes will actually help.
Prompt:
Given these MySQL queries, recommend indexes:
1. SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2026-01-01';
2. SELECT * FROM orders WHERE status = 'pending';
Provide the CREATE INDEX statements and explain how they help.
Example output:
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);
CREATE INDEX idx_orders_status ON orders (status);
The composite index handles equality on customer_id and range on order_date. The second is a simple index for filtering by status.
8. The CTE Composer: Break Down Complex Queries with Common Table Expressions
When to use: Your query looks like spaghetti; CTEs make it readable and maintainable.
Prompt:
Rewrite this PostgreSQL query using a CTE to make it more readable. The query finds customers who have spent more than $1000 in the last month:
SELECT c.name FROM customers c WHERE (SELECT SUM(o.amount) FROM orders o WHERE o.customer_id = c.id AND o.order_date >= NOW() - INTERVAL '1 month') > 1000;
Example output:
WITH customer_spending AS (
SELECT customer_id, SUM(amount) AS total
FROM orders
WHERE order_date >= NOW() - INTERVAL '1 month'
GROUP BY customer_id
)
SELECT c.name
FROM customers c
JOIN customer_spending cs ON c.id = cs.customer_id
WHERE cs.total > 1000;
9. The Data Cleaner: Write Queries to Find and Fix Duplicates or Anomalies
When to use: You suspect duplicate records or data quality issues.
Prompt:
Write a PostgreSQL query to find duplicate emails in the 'customers' table. Also, write a query to delete all but the lowest ID duplicate. Use a window function.
Example output:
-- Find duplicates
SELECT email, COUNT(*)
FROM customers
GROUP BY email HAVING COUNT(*) > 1;
-- Delete duplicates, keep lowest id
DELETE FROM customers
WHERE id NOT IN (
SELECT MIN(id)
FROM customers
GROUP BY email
);
10. The Migration Mapper: Translate Between PostgreSQL and MySQL Syntax
When to use: You're moving between database systems and need a quick syntax conversion.
Prompt:
Convert this PostgreSQL query to MySQL. Watch for differences in LIMIT, ILIKE, and interval syntax.
SELECT * FROM products WHERE name ILIKE '%widget%' ORDER BY created_at DESC LIMIT 10;
Example output:
SELECT * FROM products WHERE name LIKE '%widget%' ORDER BY created_at DESC LIMIT 10;
Note: MySQL uses LIKE (case-insensitive by default for ASCII), and no INTERVAL here.
11. The Performance Tester: Generate a Query with EXPLAIN ANALYZE for Benchmarking
When to use: You need to measure query performance before and after optimization.
Prompt:
Generate a PostgreSQL query that uses EXPLAIN ANALYZE to test the performance of a query that joins 'orders' and 'customers' for the top 10 spenders. Include the output format.
Example output:
EXPLAIN (ANALYZE, BUFFERS)
SELECT c.name, SUM(o.amount) AS total
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.name
ORDER BY total DESC
LIMIT 10;
12. The Subquery Simplifier: Replace Complex Subqueries with Joins or CTEs
When to use: You have a query with nested subqueries that are hard to read and slow.
Prompt:
Rewrite this MySQL query to use a JOIN instead of a subquery:
SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE amount > 500);
Example output:
SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.amount > 500;
13. The Data Type Detective: Suggest Appropriate Data Types for a Given Column
When to use: You're unsure whether to use VARCHAR or TEXT, INT or BIGINT, etc.
Prompt:
For a PostgreSQL column storing IP addresses, what data type should I use? Explain the pros and cons of INET vs VARCHAR. Provide an example DDL.
Example output:
Use INET for IP addresses because it supports indexing and functions like << for subnet calculations. Example: ip_address INET. It also validates the format. VARCHAR is simpler but lacks these features.
14. The Transaction Wrapper: Write a Query with Proper Transaction Control (BEGIN, COMMIT, ROLLBACK)
When to use: You need to ensure atomicity for a multi-step operation.
Prompt:
Write a PostgreSQL transaction that inserts a new order and its items. If any item insert fails, roll back the entire transaction. Use BEGIN, COMMIT, and ROLLBACK.
Example output:
BEGIN;
INSERT INTO orders (customer_id) VALUES (1) RETURNING id;
INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 2, 3);
COMMIT;
-- On error, ROLLBACK;
15. The Performance Guru: Provide a Checklist for Optimizing a Slow Database
When to use: You're not sure where to start with database performance tuning.
Prompt:
Give me a checklist for optimizing a PostgreSQL database that's slow. Cover indexing, query rewriting, configuration, and hardware. Include specific commands.
Example output:
- Use EXPLAIN ANALYZE to find hotspots.
- Check for missing indexes with pg_stat_user_indexes.
- Tune shared_buffers and work_mem in postgresql.conf.
- Consider partitioning large tables.
- Use connection pooling (PgBouncer).
Final Thoughts
These 15 prompts are your Swiss Army knife for SQL tasks. The key is to be specific—the more context you give, the better the AI's output. I encourage you to copy, adapt, and experiment with them in your daily workflow. Have a favorite prompt that's not on this list? Share it in the comments below, and let's build a community of AI-powered database wizards!
Comments