10 Prompts for Writing SQL Queries and Optimizing Database Performance

Introduction

Structured Query Language (SQL) remains the lingua franca of data manipulation, but even seasoned developers spend hours debugging slow queries or crafting complex joins. In 2026, AI-assisted coding has matured into a reliable partner for database work, but only if you feed it the right prompts. This article delivers 10 battle-tested prompts for generating and optimizing SQL queries, plus practical examples and expert tips. Whether you're a data analyst or a backend engineer, these prompts will help you write cleaner, faster SQL with less trial and error.

According to the official PostgreSQL documentation (postgresql.org/docs/current/performance-tips.html), poor indexing and suboptimal join orders account for the majority of performance issues in production databases. AI prompts can proactively address these pitfalls by generating execution-plan-aware SQL. Let's dive into the prompts.

1. Prompt for Writing a Basic SELECT Query

Task: Generate a simple SELECT statement from scratch.
Prompt: "Write a SQL query to select the top 10 most recent orders from the orders table, including customer name (from customers table via customer_id), order date, and total amount. Use an INNER JOIN and order by order_date descending. Assume orders.total_amount is DECIMAL(10,2)."
Why it works: This prompt specifies table names, columns, join conditions, ordering, and data types — eliminating ambiguity that leads to incorrect joins or type mismatches.
Example output:

SELECT c.name,
       o.order_date,
       o.total_amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
ORDER BY o.order_date DESC
LIMIT 10;

2. Prompt for Debugging a Slow Query

Task: Identify performance bottlenecks in an existing query.
Prompt: "Analyze the following SQL query and suggest improvements. The query runs in 12 seconds on a table with 5 million rows. Use an execution plan approach: check for missing indexes, full table scans, and inefficient joins. Then rewrite the query for better performance." Follow with the query.
Why it works: This prompt forces the AI to simulate an EXPLAIN ANALYZE step, which is critical for optimization. According to MySQL documentation (dev.mysql.com/doc/refman/8.0/en/using-explain.html), understanding execution plans can reduce query time by 90%.
Example rewrite:

-- Original: SELECT * FROM orders WHERE YEAR(order_date) = 2025;
-- Optimized: SELECT * FROM orders WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01';

The second version uses a range scan instead of a function-based index scan.

3. Prompt for Generating Complex Joins

Task: Create multi-table joins with aggregations.
Prompt: "Write a SQL query that returns, for each product category, the total revenue and number of units sold in Q1 2026. Use tables: products (id, category_id, price), categories (id, name), order_items (order_id, product_id, quantity). Assume revenue = price * quantity. Group by category name and order by revenue descending."
Why it works: Including business logic (revenue calculation) and explicit grouping reduces the chance of incorrect aggregations.
Example output:

SELECT cat.name AS category,
       SUM(p.price * oi.quantity) AS total_revenue,
       SUM(oi.quantity) AS units_sold
FROM order_items oi
JOIN products p ON oi.product_id = p.id
JOIN categories cat ON p.category_id = cat.id
JOIN orders o ON oi.order_id = o.id
WHERE o.order_date BETWEEN '2026-01-01' AND '2026-03-31'
GROUP BY cat.name
ORDER BY total_revenue DESC;

4. Prompt for Indexing Recommendations

Task: Get index suggestions for a slow table.
Prompt: "Given the orders table with columns: id (PK), customer_id (FK), order_date, status, total_amount. Queries frequently filter on status and order_date, and join on customer_id. Recommend indexes using B-tree and partial index strategies. Write the CREATE INDEX statements."
Why it works: Index recommendations must consider query patterns. A partial index on status where status = 'pending' can drastically reduce index size — a technique highlighted in PostgreSQL documentation (postgresql.org/docs/current/indexes-partial.html).
Example output:

CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_status_date ON orders(status, order_date);
CREATE INDEX idx_orders_pending ON orders(order_date) WHERE status = 'pending';

5. Prompt for Query Refactoring

Task: Convert a subquery into a JOIN for performance.
Prompt: "Rewrite the following query using a JOIN instead of a subquery. Original: SELECT * FROM products WHERE id IN (SELECT product_id FROM order_items WHERE quantity > 10); Explain why the JOIN version is faster in most databases."
Why it works: Many databases optimize JOINs better than IN subqueries, especially with large datasets. SQL Server documentation (learn.microsoft.com/en-us/sql/relational-databases/performance/subqueries) notes that JOINs often produce more efficient execution plans.
Example output:

SELECT DISTINCT p.*
FROM products p
JOIN order_items oi ON p.id = oi.product_id
WHERE oi.quantity > 10;

6. Prompt for Window Functions

Task: Generate a query with RANK, ROW_NUMBER, or LAG.
Prompt: "Write a SQL query to rank employees by sales within each department. Use the employees table (id, name, department_id) and sales table (employee_id, amount, sale_date). Return employee name, department name, total sales, and rank within department. Use a window function with PARTITION BY."
Why it works: Window functions are powerful but tricky. Explicitly naming the partition column ensures correct results.
Example output:

SELECT e.name,
       d.name AS department,
       SUM(s.amount) AS total_sales,
       RANK() OVER (PARTITION BY e.department_id ORDER BY SUM(s.amount) DESC) AS rank
FROM employees e
JOIN departments d ON e.department_id = d.id
JOIN sales s ON e.id = s.employee_id
GROUP BY e.id, e.name, d.name, e.department_id;

7. Prompt for CTE (Common Table Expression) Generation

Task: Simplify complex queries with recursive CTEs.
Prompt: "Write a recursive CTE to generate a hierarchy of categories from the categories table with columns: id, parent_id, name. Starting from root categories (parent_id IS NULL), return each category name along with its full path (e.g., 'Electronics > Computers > Laptops'). Limit depth to 5 levels."
Why it works: Recursive CTEs require careful handling of anchor and recursive members. This prompt sets clear boundaries (depth limit) and output format.
Example output:

WITH RECURSIVE category_tree AS (
    SELECT id, name, name AS path, 1 AS depth
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, CONCAT(ct.path, ' > ', c.name), ct.depth + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
    WHERE ct.depth < 5
)
SELECT id, name, path FROM category_tree;

8. Prompt for Data Cleaning Queries

Task: Write SQL to find and fix data quality issues.
Prompt: "Write queries to detect and clean duplicate records in the customers table (id, email, name). First, find duplicates by email. Then, keep the record with the highest id and delete others. Use a CTE with ROW_NUMBER()."
Why it works: Data cleaning often requires transactional safety. Using CTE + ROW_NUMBER allows preview before deletion.
Example output:

WITH duplicates AS (
    SELECT id, email,
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY id DESC) AS rn
    FROM customers
)
DELETE FROM customers WHERE id IN (
    SELECT id FROM duplicates WHERE rn > 1
);

9. Prompt for Performance Benchmarking

Task: Compare two query versions for speed.
Prompt: "Generate two equivalent SQL queries that find customers who placed orders in the last 30 days: one using EXISTS and one using INNER JOIN. Then explain which is faster on a table with 10 million orders and why. Include an estimate of the execution plan differences."
Why it works: This prompt teaches comparative optimization — a skill that separates junior from senior DBAs.
Example explanation: EXISTS often performs better because it stops scanning once a match is found, whereas JOIN may produce duplicates and require DISTINCT. However, modern optimizers may treat them identically — always test with EXPLAIN.

10. Prompt for Writing Stored Procedures

Task: Generate a reusable stored procedure with error handling.
Prompt: "Write a PostgreSQL stored procedure that updates inventory after an order is placed. The procedure should accept order_id as input, deduct quantities from the inventory table, and raise an exception if any product is out of stock. Use BEGIN/EXCEPTION blocks for transaction control."
Why it works: Stored procedures require transactional integrity. Including exception handling makes the prompt production-ready.
Example output:

CREATE OR REPLACE PROCEDURE process_order(p_order_id INT) AS $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT product_id, quantity FROM order_items WHERE order_id = p_order_id LOOP
        UPDATE inventory
        SET stock = stock - rec.quantity
        WHERE product_id = rec.product_id;
        IF NOT FOUND THEN
            RAISE EXCEPTION 'Product % not found in inventory', rec.product_id;
        END IF;
    END LOOP;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
$$ LANGUAGE plpgsql;

Conclusion

Crafting effective SQL prompts is an art that blends database theory with practical syntax. The 10 prompts above cover the most common scenarios: from simple SELECTs to recursive CTEs and stored procedures. For optimal results, always specify table structures, join keys, and expected output format in your prompts. And remember — even the best AI-generated SQL should be reviewed with EXPLAIN ANALYZE before hitting production.

As database workloads grow, tools that integrate AI into SQL workflows are becoming essential. ASI Biont supports direct API connections to major database systems including PostgreSQL, MySQL, and SQL Server, enabling you to generate and test queries from within your existing workflow — see details at asibiont.com/courses. Use these prompts to accelerate your development, but never skip the human review step.

This article was written with expert guidance from database optimization specialists and references official documentation from PostgreSQL, MySQL, and SQL Server as of July 2026.