You know that moment when a query that ran in milliseconds during development suddenly takes 40 seconds in production? Or when your JOIN logic works perfectly in PostgreSQL but throws a syntax error in MySQL? SQL is a language of many dialects, and each one has its quirks. Whether you're a backend developer wrestling with ORM-generated queries or a data analyst extracting insights from a data warehouse, the right prompt can turn a frustrating debugging session into a quick win.
Large language models have read the docs, studied the error messages, and analyzed thousands of Stack Overflow threads. But asking "write me a query" is like asking a chef to "make food" — you'll get something, but it won't be tailored to your taste. The prompts below are designed to get you precise, dialect-aware SQL that you can actually use. I've organized them by task, from basic table creation to advanced performance tuning, and each one includes a concrete example you can adapt.
1. The Universal Table Creator: Schema Generation 101
Prompt: "Create a SQL table for storing user information. Include fields for id, email, name, created_at, and last_login. Use appropriate data types and constraints. Write the syntax for PostgreSQL, MySQL, and SQLite."
Why it works: This prompt forces the AI to think about dialect differences. In PostgreSQL, you'll get SERIAL or IDENTITY, in MySQL AUTO_INCREMENT, and in SQLite INTEGER PRIMARY KEY AUTOINCREMENT. The AI will also add NOT NULL constraints and maybe a UNIQUE on email.
Example output (PostgreSQL):
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
name VARCHAR(100) NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW(),
last_login TIMESTAMPTZ
);
Pro tip: Specify the database version (e.g., "MySQL 8.0") to get syntax that matches your actual server. MySQL 5.7 doesn't support CHECK constraints the same way as 8.0.
2. The SELECT All-Rounder: From Basic to Advanced Filtering
Prompt: "Write a SELECT query for an e-commerce orders table. Filter orders from the last 30 days, include only paid orders, and sort by total amount descending. Show the customer name and order total. Adapt for MySQL and PostgreSQL."
Why it works: This covers the core of everyday querying — filtering, sorting, and joining. The AI will use WHERE, ORDER BY, and JOIN and will handle date functions appropriately (e.g., NOW() - INTERVAL 30 DAY in MySQL vs. NOW() - INTERVAL '30 days' in PostgreSQL).
Example output (PostgreSQL):
SELECT c.name, o.total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'paid'
AND o.created_at >= NOW() - INTERVAL '30 days'
ORDER BY o.total_amount DESC;
Pro tip: Add "include an index recommendation" to the prompt. The AI will suggest an index on status and created_at for better performance.
3. The JOIN Whisperer: Demystifying Inner, Left, and Full Joins
Prompt: "Explain the difference between INNER JOIN, LEFT JOIN, and FULL OUTER JOIN using a simple example of two tables: employees and departments. Provide SQL queries for each and describe the result sets. Use PostgreSQL syntax."
Why it works: Joins are a common pain point. This prompt not only gives you the queries but also explains the logic, which helps you choose the right join for your own data.
Example output (PostgreSQL):
-- Inner join: only employees with a department
SELECT e.name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.id;
-- Left join: all employees, even those without a department
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;
-- Full outer join: all employees and all departments
SELECT e.name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.id;
Pro tip: Ask for a visual representation (ASCII diagram) of the result sets to solidify your understanding.
4. The Window Function Wizard: Running Totals and Rankings
Prompt: "Using the sales table (sale_date, region, amount), write a query that calculates a running total of sales per region ordered by date. Also, rank each region by total sales. Use PostgreSQL window functions."
Why it works: Window functions are powerful but often misunderstood. This prompt gives you a concrete use case — running total and ranking — which are common in analytical reporting.
Example output (PostgreSQL):
SELECT
region,
sale_date,
amount,
SUM(amount) OVER (PARTITION BY region ORDER BY sale_date) AS running_total,
RANK() OVER (ORDER BY SUM(amount) DESC) AS region_rank
FROM sales
GROUP BY region, sale_date, amount;
Pro tip: Specify the window frame (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) if you need precise control.
5. The Aggregation Ace: Group By and Having
Prompt: "For a table of customer orders, write a query to find customers who have placed more than 5 orders in the last year. Include customer name and order count. Use MySQL syntax."
Why it works: This tests your understanding of GROUP BY and HAVING — a classic requirement for reporting. The AI will correctly place the filter on HAVING rather than WHERE.
Example output (MySQL):
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.order_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
GROUP BY c.id, c.name
HAVING COUNT(o.id) > 5;
Pro tip: Ask for an alternative using a subquery to see different approaches.
6. The Subquery Specialist: When and How to Use Them
Prompt: "Write a query to find products whose price is higher than the average price of all products. Use a subquery. Then rewrite it using a JOIN or CTE. Compare the readability and performance. Use PostgreSQL."
Why it works: Subqueries can be inefficient if not written properly. This prompt gets you a correct query and a more efficient alternative, teaching you best practices.
Example output (PostgreSQL):
-- Using subquery
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);
-- Using CTE
WITH avg_price AS (
SELECT AVG(price) AS avg FROM products
)
SELECT p.name, p.price
FROM products p, avg_price
WHERE p.price > avg_price.avg;
Pro tip: In PostgreSQL, the CTE version is often easier to read and can be optimized better by the planner.
7. The Performance Doctor: Diagnosing Slow Queries
Prompt: "I have a query that takes 10 seconds to run on a table with 10 million rows. Here is the query: [paste query]. Use EXPLAIN ANALYZE to identify the bottleneck and suggest indexes or rewrites. Assume PostgreSQL."
Why it works: This is the most valuable prompt on this list. You give the AI real context, and it gives you a step-by-step diagnosis. In the example, the AI might suggest adding an index on the join column or rewriting a LIKE pattern to use ILIKE with a trigram index.
Example output:
Seq Scan on orders (cost=0.00..193330.00 rows=10000000 width=16)
Filter: (status = 'paid')
The AI would then suggest:
CREATE INDEX idx_orders_status ON orders (status);
Pro tip: Include the EXPLAIN ANALYZE output in your prompt. The more context, the better the suggestions.
8. The Index Architect: Designing for Speed
Prompt: "Design indexes for a table orders with columns: customer_id, order_date, status, total_amount. The most frequent queries filter by customer_id and order_date, and sometimes sort by total_amount. Provide CREATE INDEX statements for PostgreSQL."
Why it works: Index design is an art. The AI will suggest a composite index on (customer_id, order_date) for the most common filter, and perhaps a partial index for status='paid'.
Example output (PostgreSQL):
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);
CREATE INDEX idx_orders_status ON orders (status) WHERE status = 'paid';
Pro tip: Mention the query patterns explicitly in the prompt — e.g., "queries are of the form WHERE customer_id = ? AND order_date BETWEEN ? AND ?".
9. The CTE Commander: Making Complex Queries Readable
Prompt: "Using a CTE, write a query that calculates the total sales per month, the average monthly sales, and the difference from the previous month. Use PostgreSQL. Include a sample of the result."
Why it works: CTEs break down complex logic into digestible steps. This prompt produces a clear, structured query that's easy to debug.
Example output (PostgreSQL):
WITH monthly_sales AS (
SELECT DATE_TRUNC('month', sale_date) AS month, SUM(amount) AS total
FROM sales
GROUP BY month
),
with_prev AS (
SELECT month, total, LAG(total) OVER (ORDER BY month) AS prev_total
FROM monthly_sales
)
SELECT month, total, prev_total, total - prev_total AS diff
FROM with_prev;
Pro tip: Ask for a final SELECT that shows only the months where the diff is positive.
10. The Analytical Query Builder: Cohort and Retention Analysis
Prompt: "Write a query to calculate the 30-day retention rate for users who signed up in January 2026. Use a subscriptions table with user_id and signup_date, and an activity table with user_id and activity_date. Assume PostgreSQL."
Why it works: Retention analysis is a staple for product analytics. This prompt produces a query that uses date arithmetic, joins, and conditional aggregation.
Example output (PostgreSQL):
WITH jan_users AS (
SELECT user_id, signup_date
FROM subscriptions
WHERE signup_date >= '2026-01-01' AND signup_date < '2026-02-01'
),
activity AS (
SELECT user_id, activity_date
FROM activity
WHERE activity_date BETWEEN '2026-01-01' AND '2026-02-28'
)
SELECT
COUNT(DISTINCT j.user_id) AS signups,
COUNT(DISTINCT a.user_id) AS active_users,
COUNT(DISTINCT a.user_id)::float / COUNT(DISTINCT j.user_id) AS retention_rate
FROM jan_users j
LEFT JOIN activity a ON j.user_id = a.user_id
AND a.activity_date BETWEEN j.signup_date AND j.signup_date + INTERVAL '30 days';
Pro tip: For ClickHouse, ask for a version using uniqExact or retention function.
11. The Schema Refactorer: Safe Migration Scripts
Prompt: "I need to rename a column email_address to email in a table users in PostgreSQL. Write an ALTER TABLE statement that also updates any dependent views or indexes. Include a check for existing dependencies."
Why it works: Renaming columns can break things. The AI will provide the ALTER TABLE ... RENAME COLUMN and suggest checking information_schema for dependent objects.
Example output (PostgreSQL):
ALTER TABLE users RENAME COLUMN email_address TO email;
Pro tip: Always run a SELECT * FROM information_schema.columns WHERE table_name='users' before and after to verify.
12. The Cross-Dialect Converter: MySQL to PostgreSQL (and Back)
Prompt: "Convert this MySQL query to PostgreSQL: [paste query]. Note any syntax differences, especially around date functions and string escaping."
Why it works: This is a lifesaver when migrating databases. The AI will handle DATE_ADD vs + INTERVAL, LIMIT vs FETCH FIRST, and backtick removal.
Example (MySQL):
SELECT * FROM orders WHERE order_date > DATE_SUB(NOW(), INTERVAL 7 DAY) LIMIT 10;
PostgreSQL equivalent:
SELECT * FROM orders WHERE order_date > NOW() - INTERVAL '7 days' LIMIT 10;
Pro tip: Ask for a summary of all differences found; it's a great learning resource.
13. The Data Quality Inspector: Finding Anomalies
Prompt: "Write a query to find duplicate emails in a users table. Also, check for NULLs in critical columns and rows that violate a foreign key. Use PostgreSQL."
Why it works: Data quality is crucial. This prompt gives you a set of diagnostic queries that catch common issues.
Example output (PostgreSQL):
-- Duplicates
SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;
-- Nulls
SELECT * FROM users WHERE email IS NULL OR name IS NULL;
-- Orphaned orders
SELECT * FROM orders WHERE customer_id NOT IN (SELECT id FROM customers);
Pro tip: Use EXPLAIN to see if your duplicate check is efficient; a hash aggregate is usually fine.
14. The ClickHouse Crusher: Analytical Queries for Real-Time Dashboards
Prompt: "Write a ClickHouse query to calculate the daily unique visitors for the last 30 days from a table with columns: event_date, user_id. Use the uniqExact function."
Why it works: ClickHouse has its own syntax and functions. This prompt gets you a working query that leverages ClickHouse's strengths.
Example output (ClickHouse):
SELECT event_date, uniqExact(user_id) AS unique_visitors
FROM events
WHERE event_date >= today() - 30
GROUP BY event_date
ORDER BY event_date;
Pro tip: Mention the engine (MergeTree) in the prompt to get partition-aware suggestions.
15. The Query Explainer: Translating SQL to Plain English
Prompt: "Explain the following SQL query in plain English, step by step. Include what it does and any potential performance issues. [paste query]"
Why it works: Understanding existing queries is half the battle. This prompt is great for onboarding new team members or reviewing code.
Example: For a complex query with multiple joins, the AI will break it down into logical steps, describe the relationships, and flag any missing indexes.
Pro tip: Ask for a visual representation of the query plan using EXPLAIN output.
Putting It All Together: A Workflow for SQL Prompts
These prompts aren't just copy-paste templates — they're starting points. The real power comes from combining them. For example, you might use prompt #7 to diagnose a slow query, then #8 to design the index, then #15 to document the final solution.
Remember, the quality of the output depends on the context you provide. Always include:
- Your database system and version
- Table schemas (or a sample)
- The exact error message, if any
- What you've already tried
The Bottom Line
SQL is a skill that never goes stale. Whether you're working with PostgreSQL, MySQL, or ClickHouse, these prompts can help you write better queries, debug faster, and optimize performance. The examples above are just a taste — try them out, adapt them to your own data, and see how much time you save.
If you're serious about leveling up your data skills, consider diving deeper into the documentation for your specific database. And if you want to see how AI can help you learn these topics even faster, check out what ASI Biont offers. Happy querying!
Comments