Introduction
Writing efficient SQL queries is a core skill for any developer or data analyst. Yet, even experienced professionals spend hours debugging slow queries, missing indexes, or dealing with poorly structured joins. According to a 2023 survey by Stack Overflow, over 70% of developers work with databases regularly, but many report that query optimization is among their top challenges. This article provides 12 practical prompts you can use to generate, refine, and optimize SQL queries—whether you're working with PostgreSQL, MySQL, or SQL Server. Each prompt includes a real-world scenario, the exact prompt text, an example result, and actionable takeaways.
How to Use These Prompts
All prompts are designed to be used with AI assistants like ChatGPT, Claude, or GitHub Copilot. Simply copy the prompt, replace placeholder values (like table names or columns), and adjust for your database dialect. The examples are written for PostgreSQL but can be adapted easily.
1. Basic Query Generation
Task: Generate a SELECT query with filtering and sorting.
Prompt: "Write a PostgreSQL query to find all customers from the customers table who signed up in the last 30 days. Include their name, email, and signup date. Sort by signup date descending. Use WHERE with CURRENT_DATE - INTERVAL '30 days'."
Example Result:
SELECT name, email, signup_date
FROM customers
WHERE signup_date >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY signup_date DESC;
Takeaway: This prompt demonstrates how to specify table, columns, filter condition, and sort order concisely. It avoids ambiguity by using explicit date arithmetic.
2. Joining Multiple Tables
Task: Generate a query that joins three tables with aggregation.
Prompt: "Write a SQL query to show total revenue per product category. Tables: orders (order_id, customer_id, order_date), order_items (order_id, product_id, quantity, price), products (product_id, category_id, product_name), categories (category_id, category_name). Use INNER JOINs and GROUP BY. Only include categories with revenue over $1000."
Example Result:
SELECT c.category_name, SUM(oi.quantity * oi.price) AS total_revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
JOIN categories c ON p.category_id = c.category_id
GROUP BY c.category_name
HAVING SUM(oi.quantity * oi.price) > 1000;
Takeaway: This prompt teaches how to chain multiple joins and use HAVING for post-aggregation filtering.
3. Subqueries for Complex Filtering
Task: Use a subquery to find top-performing items.
Prompt: "Write a query to find products that have been ordered more than the average number of times across all products. Use a subquery in the WHERE clause. Tables: order_items and products."
Example Result:
SELECT product_name
FROM products
WHERE product_id IN (
SELECT product_id
FROM order_items
GROUP BY product_id
HAVING COUNT(*) > (SELECT AVG(order_count) FROM (SELECT COUNT(*) AS order_count FROM order_items GROUP BY product_id) AS avg_table)
);
Takeaway: Subqueries can express complex conditions. This prompt illustrates correlated and scalar subqueries.
4. Common Table Expressions (CTEs)
Task: Use a CTE for readability and recursion.
Prompt: "Write a PostgreSQL query that uses a CTE to calculate the running total of sales per day. Tables: sales (sale_date, amount). Output columns: sale_date, daily_total, running_total."
Example Result:
WITH daily_sales AS (
SELECT sale_date, SUM(amount) AS daily_total
FROM sales
GROUP BY sale_date
)
SELECT sale_date, daily_total, SUM(daily_total) OVER (ORDER BY sale_date) AS running_total
FROM daily_sales;
Takeaway: CTEs improve query readability and enable window functions like SUM() OVER().
5. Window Functions for Rankings
Task: Rank rows within partitions.
Prompt: "Write a query to rank employees by salary within each department. Tables: employees (employee_id, name, salary, department_id). Use RANK() and PARTITION BY."
Example Result:
SELECT name, salary, department_id,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees;
Takeaway: Window functions like RANK() and DENSE_RANK() are essential for analytic queries.
6. Indexing Recommendations
Task: Suggest indexes based on query patterns.
Prompt: "I have a table orders with columns customer_id, order_date, status. I frequently run queries filtering by customer_id and order_date and ordering by order_date. What indexes should I create? Write the SQL statements."
Example Result:
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date DESC);
CREATE INDEX idx_orders_status ON orders (status);
Takeaway: A composite index on (customer_id, order_date) covers both filter and sort, reducing disk I/O.
7. Query Plan Analysis
Task: Interpret an EXPLAIN plan.
Prompt: "Explain the following EXPLAIN output for PostgreSQL: Seq Scan on customers (cost=0.00..35.50 rows=2550 width=40) Filter: (age > 30). What does it mean and how can I improve it?"
Example Result:
"The query uses a sequential scan on the customers table, reading all rows. With 2550 rows and an estimated cost of 35.50, it's not terrible, but if the table grows, you should create an index on age. Use CREATE INDEX idx_customers_age ON customers (age); to enable an index scan."
Takeaway: Learning to read EXPLAIN output helps identify missing indexes or inefficient joins.
8. Optimizing Slow Queries
Task: Rewrite a slow query.
Prompt: "This query takes 10 seconds: SELECT * FROM orders WHERE YEAR(order_date) = 2023. How can I optimize it for MySQL? Write the improved version."
Example Result:
SELECT * FROM orders WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01';
Takeaway: Wrapping columns in functions (like YEAR()) prevents index usage. Use range conditions instead.
9. Normalization and Denormalization Advice
Task: Evaluate schema design.
Prompt: "I have a table with columns: order_id, customer_name, customer_email, product_name, product_price, quantity. Should I normalize it? Provide the normalized schema."
Example Result:
"Yes, separate into customers (customer_id, name, email), products (product_id, name, price), orders (order_id, customer_id, order_date), and order_items (order_id, product_id, quantity). This avoids data duplication and update anomalies."
Takeaway: Normalization reduces redundancy but may require joins. Denormalization can speed up reads at the cost of consistency.
10. Handling NULLs and COALESCE
Task: Write queries that handle NULL values gracefully.
Prompt: "Write a query that returns customer names and their phone numbers. If a phone number is NULL, show 'No phone'. Use COALESCE or CASE. Table: customers."
Example Result:
SELECT name, COALESCE(phone, 'No phone') AS phone
FROM customers;
Takeaway: COALESCE returns the first non-NULL value. It's cleaner than CASE for simple defaults.
11. Pagination with Keyset vs Offset
Task: Implement efficient pagination.
Prompt: "I need to paginate through 1 million rows. Which is better: LIMIT 10 OFFSET 100000 or keyset pagination? Write both queries for a users table with id as primary key."
Example Result:
-- Offset (inefficient for large offsets)
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 100000;
-- Keyset (efficient)
SELECT * FROM users WHERE id > 100000 ORDER BY id LIMIT 10;
Takeaway: Keyset pagination uses a WHERE clause and is O(1) per page, while OFFSET scans skipped rows.
12. Detecting and Removing Duplicates
Task: Find and delete duplicate rows.
Prompt: "Table users has duplicate emails. Write a query to find duplicates and another to delete all but one copy (keeping the row with the smallest id)."
Example Result:
-- Find duplicates
SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
-- Delete duplicates
DELETE FROM users
WHERE id NOT IN (
SELECT MIN(id)
FROM users
GROUP BY email
);
Takeaway: Use GROUP BY with HAVING to identify duplicates, and a subquery to keep the desired row.
13. Full-Text Search with PostgreSQL
Task: Implement full-text search.
Prompt: "Write a PostgreSQL query to search for articles containing the word 'database' in the title or body. Use tsvector and tsquery. Table: articles (id, title, body)."
Example Result:
SELECT id, title
FROM articles
WHERE to_tsvector('english', title
|| ' ' || body) @@ to_tsquery('english', 'database');
Takeaway: Full-text search is more powerful than LIKE because it supports stemming and ranking.
14. Upsert (INSERT ... ON CONFLICT)
Task: Insert or update rows.
Prompt: "Write a PostgreSQL query to insert a new user, or if the email already exists, update the name. Use ON CONFLICT. Table: users (email unique, name)."
Example Result:
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name;
Takeaway: ON CONFLICT handles race conditions gracefully without separate SELECT/UPDATE/INSERT logic.
15. Transaction Management
Task: Write a transaction with rollback on error.
Prompt: "Write a PostgreSQL transaction that transfers $100 from account 1 to account 2. Ensure atomicity: if any step fails, roll back. Tables: accounts (id, balance)."
Example Result:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
Takeaway: Transactions guarantee data consistency. Use SAVEPOINT for partial rollbacks.
Conclusion
Mastering SQL requires both writing correct queries and optimizing them for performance. The 12 prompts above cover the most common scenarios—from basic filtering to advanced indexing and full-text search. By integrating these prompts into your daily workflow, you can reduce debugging time and write production-ready queries faster. Start by trying one prompt today, adapt it to your database, and measure the improvement with EXPLAIN. For deeper learning, consult the PostgreSQL documentation at https://www.postgresql.org/docs/current/indexes.html and the MySQL optimizer guide at https://dev.mysql.com/doc/refman/8.0/en/optimization.html.
Comments