15 Prompts for Writing SQL Queries and Optimizing Databases

Expert guide for developers: from query generation to performance tuning

Introduction

SQL is the backbone of modern data-driven applications. Whether you're building a SaaS platform, analyzing user behavior, or maintaining a legacy system, writing efficient SQL queries is a critical skill. But even experienced developers often struggle with complex joins, subqueries, or performance bottlenecks. That's where AI-powered prompts come in. By using the right prompts, you can generate accurate SQL, optimize slow queries, and even design database schemas — all while saving hours of manual work.

In this article, I'll share 15 battle-tested prompts that I use daily in my workflow. Each prompt includes a real-world example, expected output, and pro tips for maximum effectiveness. These prompts work with any modern AI assistant (ChatGPT, Claude, Copilot) and are designed for PostgreSQL, MySQL, and SQL Server. No fluff — just practical value.

Why Use Prompts for SQL?

According to a 2023 Stack Overflow survey, over 70% of professional developers use SQL regularly. Yet, writing complex queries from scratch is time-consuming and error-prone. AI prompts help you:

Generate correct syntax for specific databases (e.g., PostgreSQL vs MySQL recursive CTEs)
Optimize slow queries by suggesting indexes, rewriting joins, or adding WHERE clauses
Learn best practices through examples — instead of reading documentation, you get instant code
Automate repetitive tasks like generating reports, data migrations, or schema changes

But prompts alone aren't magic. You need to understand the output and verify it against your data. That's why I'll also explain the reasoning behind each prompt.

The 15 Prompts: Generation & Optimization

1. Generate a SELECT Query from a Natural Language Description

Prompt:
"Write a SQL query to find the top 5 customers by total order value in the last 30 days. Use the tables: customers (id, name, email), orders (id, customer_id, order_date, total_amount). Assume PostgreSQL syntax."

Example Output:

SELECT
  c.id,
  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 >= CURRENT_DATE - 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 tables and columns, the metric (total order value), the time window (last 30 days), and the sort order (top 5). This eliminates ambiguity.

Pro Tip: Always include the exact table schemas in your prompt. If you have many columns, list only the relevant ones.

2. Translate a Complex Business Rule into SQL

Prompt:
"I need a query that returns all products that have been ordered at least 3 times in the past year, but never ordered by customers from the 'VIP' tier. Tables: products (id, name, price), orders (id, product_id, customer_id, order_date), customers (id, name, tier). Use MySQL syntax."

Example Output:

SELECT p.id, p.name, p.price
FROM products p
JOIN orders o ON p.id = o.product_id
JOIN customers c ON o.customer_id = c.id
WHERE o.order_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
  AND c.tier != 'VIP'
GROUP BY p.id, p.name, p.price
HAVING COUNT(DISTINCT o.id) >= 3;

Why it works: The prompt breaks down the business logic into clear conditions: count of orders, time filter, and customer tier exclusion. The AI translates this into a GROUP BY with HAVING clause.

Pro Tip: For complex conditions, use bullet points in your prompt: "- condition 1: ... - condition 2: ..."

3. Optimize a Slow Running Query

Prompt:
*"Here is a SQL query that takes 10 seconds to run. Optimize it by adding indexes, rewriting joins, or using CTEs. Explain each change.

SELECT *
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
WHERE o.order_date > '2024-01-01'
  AND c.country = 'USA';

The table orders has 2 million rows, customers has 500k rows. Indexes exist only on primary keys."*

Example Output:

-- Optimized query with index recommendations
-- 1. Create composite index on orders(order_date, customer_id) to speed up WHERE and JOIN
-- 2. Create index on customers(country, id) to filter country early

CREATE INDEX idx_orders_date_customer ON orders(order_date, customer_id);
CREATE INDEX idx_customers_country_id ON customers(country, id);

-- Rewritten query using INNER JOIN instead of LEFT JOIN (since WHERE on customers.country makes it effectively INNER)
SELECT o.*
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE o.order_date > '2024-01-01'
  AND c.country = 'USA';

Explanation: The original LEFT JOIN is unnecessary because the WHERE clause on the customers table filters out NULLs anyway. Changing to INNER JOIN reduces the join complexity. Adding composite indexes allows the database to filter and join in one pass.

Pro Tip: Always include table sizes and existing indexes in your prompt. Without context, the AI might suggest unrealistic solutions.

4. Generate a Recursive CTE for Hierarchical Data

Prompt:
"Write a PostgreSQL recursive CTE to get all employees under a given manager (including the manager) in an employee table with columns: id, name, manager_id (NULL for top-level). Return the employee name, level in hierarchy, and path from top."

Example Output:

WITH RECURSIVE emp_tree AS (
  -- Base case: the starting manager
  SELECT id, name, manager_id, 0 AS level, name::TEXT AS path
  FROM employees
  WHERE id = 1  -- replace with actual manager ID

  UNION ALL

  -- Recursive step: direct reports
SELECT e.id, e.name, e.manager_id, et.level + 1, et.path

|| ' -> ' || e.name
  FROM employees e
  JOIN emp_tree et ON e.manager_id = et.id
)
SELECT *
FROM emp_tree
ORDER BY level, name;

Why it works: Recursive CTEs are notoriously tricky. The prompt specifies exactly what the base case is (the manager), the recursive step (joining on manager_id), and the output columns (level, path).

Pro Tip: If your database doesn't support recursive CTEs (e.g., older MySQL), ask for an alternative using adjacency list or nested sets.

5. Write a Query to Detect Missing Data

Prompt:
"Find all customers who have not placed any orders in the last 6 months. Tables: customers (id, name, email, signup_date), orders (id, customer_id, order_date). Use SQL Server syntax. Also return the number of days since their last order (if any)."

Example Output:

SELECT
  c.id,
  c.name,
  c.email,
  DATEDIFF(DAY, COALESCE(MAX(o.order_date), c.signup_date), GETDATE()) AS days_since_last_activity
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name, c.email, c.signup_date
HAVING MAX(o.order_date) IS NULL
   OR MAX(o.order_date) < DATEADD(MONTH, -6, GETDATE());

Pro Tip: This pattern works for any "missing data" scenario: products without reviews, users without logins, etc.

6. Generate a Pivot Table Query

Prompt:
"Write a PostgreSQL query to pivot monthly sales data. The table sales has columns: product_id, sale_date, amount. I want to see each product as a row, and columns for each month (Jan 2024, Feb 2024, etc.) showing total sales. Use crosstab function."

Example Output:

-- First, ensure the tablefunc extension is installed
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT *
FROM crosstab(
  'SELECT product_id, TO_CHAR(sale_date, ''Mon YYYY'') AS month, SUM(amount)::NUMERIC AS total
   FROM sales
   WHERE sale_date BETWEEN ''2024-01-01'' AND ''2024-12-31''
   GROUP BY product_id, TO_CHAR(sale_date, ''Mon YYYY'')
   ORDER BY product_id, month',
  'SELECT DISTINCT TO_CHAR(sale_date, ''Mon YYYY'') FROM sales WHERE sale_date BETWEEN ''2024-01-01'' AND ''2024-12-31'' ORDER BY 1'
) AS ct (product_id INTEGER, "Jan 2024" NUMERIC, "Feb 2024" NUMERIC, ... );

Pro Tip: For MySQL, use conditional aggregation with SUM(CASE WHEN ...). Specify your database in the prompt.

7. Write a Query to Find Duplicate Records

Prompt:
"Find all duplicate email addresses in the users table (columns: id, email, created_at). Return the email, count of duplicates, and the IDs of the duplicate rows. Keep the earliest created_at record. Use PostgreSQL."

Example Output:

SELECT
  email,
  COUNT(*) AS duplicate_count,
  ARRAY_AGG(id ORDER BY created_at) AS duplicate_ids
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Pro Tip: Add a WHERE clause to exclude soft-deleted rows if applicable.

8. Generate an Index Recommendation Based on a Query Pattern

Prompt:
*"Analyze the following query and recommend indexes to speed it up. The query runs on a table with 10 million rows.

SELECT order_id, total, status
FROM orders
WHERE customer_id = 12345
  AND order_date BETWEEN '2024-01-01' AND '2024-12-31'
  AND status IN ('pending', 'shipped');

Current indexes: primary key on id, no other indexes."*

Example Output:
- Recommended index: CREATE INDEX idx_orders_customer_date_status ON orders(customer_id, order_date, status);
- Rationale: This composite index covers all three WHERE conditions in order of selectivity. The database can do an index-only scan if all selected columns are in the index.
- Additional tip: If you only need total and status, consider a covering index with INCLUDE clause (PostgreSQL: CREATE INDEX ... INCLUDE (total)).

Pro Tip: For very large tables, ask for a partitioning strategy as well.

9. Write a Query to Calculate Running Totals

Prompt:
"Write a PostgreSQL query to calculate a running total of sales per product, ordered by sale_date. Use window functions. Table: sales (id, product_id, sale_date, amount). Return product_id, sale_date, amount, and running_total."

Example Output:

SELECT
  product_id,
  sale_date,
  amount,
  SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM sales
ORDER BY product_id, sale_date;

Pro Tip: For cumulative sums across all products (without partition), remove PARTITION BY.

10. Translate a Query Between Databases

Prompt:
*"Translate this PostgreSQL query to MySQL syntax:

SELECT id, name, EXTRACT(YEAR FROM created_at) AS year
FROM users
WHERE created_at > NOW() - INTERVAL '1 year';

Keep the same logic."*

Example Output:

SELECT id, name, YEAR(created_at) AS year
FROM users
WHERE created_at > DATE_SUB(NOW(), INTERVAL 1 YEAR);

Pro Tip: Specify both source and target databases. For less common databases (SQLite, Oracle), include version info.

11. Generate a Query to Handle NULL Values Properly

Prompt:
"Write a query that calculates the average rating for each product, but treats NULL ratings as 0 (not ignored). Also return the count of ratings. Use PostgreSQL. Table: reviews (product_id, rating INT NULL)."

Example Output:

SELECT
  product_id,
  AVG(COALESCE(rating, 0)) AS avg_rating_including_nulls,
  COUNT(*) AS total_reviews,
  COUNT(rating) AS reviews_with_rating
FROM reviews
GROUP BY product_id;

Pro Tip: Always clarify whether NULL should be treated as 0, ignored, or replaced with a default.

12. Write a Query for Pagination with Total Count

Prompt:
"Write a PostgreSQL query that returns page 3 of products (20 per page), sorted by price ascending. Also return the total count of products. Use two separate queries or a single query with window function."

Example Output:

-- Option 1: Two queries (more efficient for very large tables)
SELECT COUNT(*) FROM products;  -- total count
SELECT * FROM products ORDER BY price ASC LIMIT 20 OFFSET 40;  -- page 3

-- Option 2: Single query with window function
SELECT p.*, COUNT(*) OVER() AS total_count
FROM products p
ORDER BY price ASC
LIMIT 20 OFFSET 40;

Pro Tip: For large datasets, prefer two queries because the window function scans the entire table anyway.

13. Generate a Schema Design for a Given Use Case

Prompt:
"Design a database schema for a multi-tenant SaaS application that tracks user subscriptions and billing. Include tables for tenants, users, plans, subscriptions, and invoices. Use PostgreSQL and include indexes and foreign keys."

Example Output:

CREATE TABLE tenants (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  tenant_id INTEGER REFERENCES tenants(id),
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE plans (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  billing_cycle INTERVAL NOT NULL  -- e.g., '1 month', '1 year'
);

CREATE TABLE subscriptions (
  id SERIAL PRIMARY KEY,
  tenant_id INTEGER REFERENCES tenants(id),
  plan_id INTEGER REFERENCES plans(id),
  status VARCHAR(20) DEFAULT 'active',
  start_date DATE NOT NULL,
  end_date DATE,
  UNIQUE(tenant_id, plan_id)
);

CREATE TABLE invoices (
  id SERIAL PRIMARY KEY,
  subscription_id INTEGER REFERENCES subscriptions(id),
  amount DECIMAL(10,2) NOT NULL,
  due_date DATE NOT NULL,
  paid BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_subscriptions_tenant ON subscriptions(tenant_id);
CREATE INDEX idx_invoices_subscription ON invoices(subscription_id);

Pro Tip: Ask for a normalized schema first, then ask for denormalized version if performance is critical.

14. Write a Query to Detect Performance Bottlenecks

Prompt:
"I have a query that joins 5 tables and runs slowly. Show me how to use EXPLAIN ANALYZE and interpret the output to find bottlenecks. Use PostgreSQL as example."

Example Output:

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT ...  -- your query here

Interpretation:
- Look for "Seq Scan" on large tables (indicates missing index)
- Check "actual time" vs "estimated time" — large differences suggest poor statistics
- "Sort Method: external merge" means the sort used disk (increase work_mem)
- "Parallel Seq Scan" means the query uses parallelism (good for large tables)

Pro Tip: Use EXPLAIN (ANALYZE, BUFFERS) to see cache hit rates.

15. Generate a Data Migration Script

Prompt:
"Write a PostgreSQL migration script that renames the column 'phone' to 'contact_phone' in the users table, adds a UNIQUE constraint on email, and creates an index on created_at. Use transactional DDL."

Example Output:

BEGIN;

ALTER TABLE users RENAME COLUMN phone TO contact_phone;

-- Drop existing unique constraint if any
ALTER TABLE users DROP CONSTRAINT IF EXISTS users_email_key;

ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);

CREATE INDEX idx_users_created_at ON users(created_at);

COMMIT;

Pro Tip: For production migrations, ask for reversible scripts (down migration) as well.

Best Practices for Using SQL Prompts

Always specify the database — PostgreSQL, MySQL, SQL Server, SQLite, etc. Syntax differs.
Include table schemas — even if simplified. This ensures accurate column names and joins.
Define edge cases — ask: "What if there are NULLs? What if no matching rows?"
Ask for explanations — prompt: "Explain why this query is efficient" helps you learn.
Test on a subset — use LIMIT 100 before running on production data.

Common Pitfalls to Avoid

Vague prompts — "Write a query for sales data" leads to generic output. Be specific.
Ignoring indexing — AI can suggest queries that work correctly but perform poorly without indexes.
Blindly trusting output — always verify with EXPLAIN ANALYZE on your own data.
Forgetting about ties — when using LIMIT, ask for tie-breaking logic (e.g., ORDER BY ... LIMIT 5 WITH TIES in PostgreSQL).

Conclusion

Writing SQL queries and optimizing databases doesn't have to be a chore. With the right prompts, you can generate accurate, efficient SQL in seconds — whether you're a beginner or a seasoned DBA. The 15 prompts in this guide cover the most common tasks I encounter daily: from SELECT queries and CTEs to indexing and migration scripts.

Remember: AI is a tool, not a replacement for understanding. Use prompts to speed up your work, but always review the output for correctness and performance. Start with the prompts above, adapt them to your specific database and schema, and you'll save hours every week.

Next steps: Try the prompts on your own database tables. If you get stuck, refine the prompt by adding more context — table sizes, indexes, or specific error messages. And if you're building a data-driven product, consider automating your SQL generation with custom prompts in your CI/CD pipeline.

15 Prompts for Writing SQL Queries and Optimizing Databases