Let's face it: staring at a sluggish query or a cryptic EXPLAIN output can feel like deciphering ancient runes. You know the data is there, but coaxing it out efficiently often requires a level of SQL fluency that's easy to lose when you're juggling deadlines. That's where prompt engineering steps in. By learning to communicate with AI tools like ChatGPT with surgical precision, you can transform them into a tireless DBA analyst—one that's on call 24/7, writes complex JOINs in seconds, and pinpoints performance bottlenecks without breaking a sweat.
This guide isn't about generic 'write a query' prompts. It's a curated collection of 12 battle-tested patterns, each one designed to solve a specific SQL challenge. From designing intricate schemas to dissecting query execution plans, these prompts will help you leverage AI as your personal SQL mentor and productivity booster. We'll cover both PostgreSQL and MySQL, with real-world examples and tips for providing the right context to get the best possible answers.
1. The Schema Architect: From Idea to Normalized Tables
Task: Generate a normalized database schema from a high-level description.
Prompt:
Act as a database architect. Design a normalized (3NF) relational schema for an e-commerce platform. Include tables for customers, orders, products, and categories. For each table, provide the SQL DDL (PostgreSQL syntax) with appropriate primary keys, foreign keys, and data types. Also, include a brief explanation of the relationships and any assumptions you made.
Example Result:
The AI generates a set of CREATE TABLE statements with SERIAL PRIMARY KEY, VARCHAR, INTEGER, and DECIMAL types, plus FOREIGN KEY constraints. It might include a category table with a self-referencing foreign key for hierarchical categories. The explanation would clarify the orders and customers relationship as one-to-many, and the many-to-many relationship between products and orders via an order_items table.
2. The JOIN Maestro: Crafting Complex Queries with Precision
Task: Build a complex query involving multiple JOINs and aggregations.
Prompt:
Write a PostgreSQL query that shows the top 10 customers by total revenue in the last quarter. Include their name, email, and total spent. Consider only orders with status 'completed'. Use tables: customers(id, name, email), orders(id, customer_id, status, created_at), order_items(order_id, product_id, quantity, price). Explain the logic behind your query.
Example Result:
The AI returns a query using JOIN and GROUP BY, filtering with WHERE and HAVING. It might look like:
SELECT c.name, c.email, SUM(oi.quantity * oi.price) AS total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
WHERE o.status = 'completed' AND o.created_at >= NOW() - INTERVAL '3 months'
GROUP BY c.id, c.name, c.email
ORDER BY total_spent DESC
LIMIT 10;
3. The Index Strategist: Optimizing for Speed
Task: Identify missing indexes for a set of queries.
Prompt:
Given the following PostgreSQL queries, recommend the optimal indexes to add. Provide the `CREATE INDEX` statements and explain why each index is beneficial.
Query 1: SELECT * FROM orders WHERE customer_id = 123 AND status = 'pending';
Query 2: SELECT * FROM order_items WHERE product_id = 456 AND order_id = 789;
Example Result:
The AI suggests a composite index on orders(customer_id, status) and another on order_items(product_id, order_id). It explains that a composite index can reduce the number of rows scanned and speed up lookups.
4. The Query Optimizer: Refactoring for Performance
Task: Refactor a slow query to improve performance.
Prompt:
I have a MySQL query that runs slowly on a 1M row table:
SELECT * FROM products WHERE category_id = 5 AND price > 100 ORDER BY created_at DESC;
The current index is on (category_id, price). How can I optimize this query? Consider indexing strategies and query rewriting.
Example Result:
The AI might suggest creating a composite index on (category_id, price, created_at) to allow an index-only scan and avoid a filesort. It might also rewrite the query to use a JOIN if necessary.
5. The EXPLAIN Whisperer: Deciphering Execution Plans
Task: Analyze an EXPLAIN output and diagnose performance issues.
Prompt:
Here is the EXPLAIN output for a PostgreSQL query:
Seq Scan on orders (cost=0.00..5432.10 rows=100000 width=16)
Filter: (customer_id = 123)
What does this tell me, and how can I improve the query?
Example Result:
The AI explains that a sequential scan indicates a missing index, and suggests creating an index on customer_id. It might also mention the cost estimates and how to read them.
6. The Migration Maestro: Writing Safe Schema Migrations
Task: Generate a schema migration script with rollback.
Prompt:
Write a PostgreSQL migration that adds a 'last_login' TIMESTAMP column to the 'users' table, and also a migration to roll it back. Use a transaction block.
Example Result:
The AI provides:
BEGIN;
ALTER TABLE users ADD COLUMN last_login TIMESTAMP;
COMMIT;
-- Rollback
BEGIN;
ALTER TABLE users DROP COLUMN last_login;
COMMIT;
7. The Data Detective: Writing Queries to Find Anomalies
Task: Create queries to find duplicate records or data inconsistencies.
Prompt:
Write a PostgreSQL query to find duplicate email addresses in the 'users' table. The query should show the email and the number of occurrences, and ideally return only one representative row.
Example Result:
SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;
8. The SQL Tutor: Explaining Queries in Plain English
Task: Get a human-readable explanation of a complex query.
Prompt:
Explain the following SQL query to me like I'm a junior developer. Break it down step by step and explain the purpose of each clause.
SELECT d.name, COUNT(e.id) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
WHERE e.status = 'active'
GROUP BY d.id, d.name
HAVING COUNT(e.id) > 5
ORDER BY employee_count DESC;
Example Result:
The AI explains the LEFT JOIN, the WHERE clause filtering, the GROUP BY and HAVING clauses, and the ORDER BY, all in simple terms with analogies.
9. The Query Generator: From Natural Language to SQL
Task: Convert a natural language request into a SQL query.
Prompt:
Convert the following request into a MySQL query: 'List all products that have been ordered more than 10 times, along with the total quantity ordered. Sort by total quantity descending.'
Example Result:
SELECT p.id, p.name, SUM(oi.quantity) AS total_ordered
FROM products p
JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name
HAVING COUNT(oi.order_id) > 10
ORDER BY total_ordered DESC;
10. The Data Formatter: Converting SQL Output to Other Formats
Task: Transform query results into a specific format (e.g., JSON, CSV).
Prompt:
Take the following PostgreSQL query result and format it as a JSON array, where each object has keys: 'id', 'name', 'total'.
Query: SELECT id, name, SUM(amount) AS total FROM sales GROUP BY id, name;
Result:
1, 'Widget', 100
2, 'Gadget', 200
Example Result:
The AI provides a JSON array like [{"id":1, "name":"Widget", "total":100}, {"id":2, "name":"Gadget", "total":200}].
11. The Query Debugger: Finding Errors and Fixing Them
Task: Debug a SQL query that isn't working.
Prompt:
I'm getting an error in MySQL: 'Unknown column 'o.total' in 'field list''. The query is:
SELECT o.id, o.total FROM orders o WHERE o.total > 100;
The orders table has columns: id, customer_id, total_amount. Fix the query.
Example Result:
The AI corrects the column name to total_amount and explains the importance of checking column names.
12. The SQL Reviewer: Auditing a Query for Best Practices
Task: Review a query for potential issues and improvements.
Prompt:
Review the following PostgreSQL query for best practices. Look for potential SQL injection, performance issues, and readability improvements. Suggest a revised version.
Query: SELECT * FROM products WHERE name = '$user_input';
Example Result:
The AI points out SQL injection risk and suggests using parameterized queries. It also suggests using WHERE name = ? and possibly an index.
13. The Cross-Dialect Guru: Writing Portable SQL
Task: Write SQL that works across multiple DBMS (PostgreSQL, MySQL, SQLite).
Prompt:
Write a query that works in both PostgreSQL and MySQL to get the top 5 most recent orders. Use standard SQL syntax where possible, but note any dialect-specific functions.
Example Result:
The AI provides a query using LIMIT and ORDER BY, which is standard, but notes that for date functions, NOW() works in both, but CURRENT_TIMESTAMP is more standard.
14. The Performance Predictor: Estimating Query Cost
Task: Estimate the cost of a query before running it.
Prompt:
Given a table 'orders' with 1 million rows and an index on 'customer_id', estimate the cost of this query in PostgreSQL: SELECT * FROM orders WHERE customer_id = 123. Explain your reasoning.
Example Result:
The AI explains that with an index, the cost would be low (around 1-2) due to index scan, whereas without an index, it would be a sequential scan costing thousands.
15. The N+1 Solver: Optimizing ORM Queries
Task: Solve N+1 query problems by generating a single SQL query.
Prompt:
I have a Django ORM query that results in N+1 queries. The models are Author and Book (with a ForeignKey). I want to list all authors with the count of their books. Write the equivalent SQL query that fetches this in one go.
Example Result:
SELECT a.id, a.name, COUNT(b.id) AS book_count
FROM author a
LEFT JOIN book b ON a.id = b.author_id
GROUP BY a.id, a.name;
The AI explains how this avoids N+1 by using a single query with a JOIN and GROUP BY.
Wrapping Up: Your SQL Wingman Awaits
These 15 prompt patterns are just the beginning. The key is to treat AI as a collaborative partner: clearly state your database dialect, provide sample data or schema context, and ask for explanations, not just code. With practice, you'll find yourself writing faster, more optimized queries and gaining deeper insights into your database's behavior. So next time you're stuck on a tricky query, don't just Google—prompt your way to a solution. Your future self (and your database) will thank you.
Comments