15 Prompts to Write and Optimize SQL Queries Like a Database Pro
Structured Query Language (SQL) remains the backbone of data operations in 2026. Whether you're a data analyst pulling reports, a backend engineer building APIs, or a DevOps specialist tuning performance, crafting efficient SQL queries is non-negotiable. Yet many professionals waste hours debugging slow queries or guessing syntax. This article delivers a practical collection of ready-to-use prompts that will transform how you write, debug, and optimize SQL for any relational database management system (RDBMS).
Why Prompt Engineering Matters for SQL
Large language models (LLMs) like GPT-4o and Claude 4 have become indispensable assistants for database work. However, generic prompts like "write a SQL query" produce generic results. The best prompts include schema context, performance constraints, and specific output formats. According to a 2025 study by Stanford's Center for Research on Foundation Models, structured prompts improve query correctness by 42% compared to open-ended requests (source: CRFM Stanford, 2025). The prompts below are battle-tested for MySQL, PostgreSQL, and SQL Server.
Prompts for Writing SQL Queries from Scratch
1. Basic SELECT with Filtering
Task: Generate a query to retrieve active users from a specific date range.
Prompt:
Write a SQL query for PostgreSQL that selects user_id, email, and registration_date from the "users" table where status = 'active' and registration_date is between '2025-01-01' and '2025-12-31'. Order by registration_date descending.
Example output:
SELECT user_id, email, registration_date
FROM users
WHERE status = 'active'
AND registration_date BETWEEN '2025-01-01' AND '2025-12-31'
ORDER BY registration_date DESC;
2. JOIN Across Three Tables
Task: Combine orders, customers, and products data.
Prompt:
For MySQL, write a query that joins orders (o) with customers (c) on o.customer_id = c.id and order_items (oi) on o.id = oi.order_id. Select customer_name, order_date, product_name, and quantity. Only include orders from Q4 2025.
3. Aggregation with GROUP BY and HAVING
Task: Find product categories with average rating above 4.0.
Prompt:
Generate a SQL Server query that calculates the average rating per category_name from the "reviews" table joined with "products". Return only categories where avg_rating > 4.0, sorted by avg_rating descending.
4. Window Functions for Running Totals
Task: Compute cumulative sales per month.
Prompt:
Write a PostgreSQL query using SUM() as a window function to calculate running total of sales_amount over months partitioned by year. Use the "monthly_sales" table with columns: year, month, sales_amount.
5. Common Table Expression (CTE) for Hierarchical Data
Task: Retrieve an employee org chart.
Prompt:
Create a recursive CTE in SQL Server to traverse the "employees" table where each row has employee_id, employee_name, and manager_id. Return all subordinates of a given manager_id = 10, including the manager.
Prompts for Debugging and Performance Optimization
6. Identify Missing Indexes
Task: Find tables with full table scans.
Prompt:
Analyze the following query execution plan (paste EXPLAIN output) and suggest indexes that would eliminate seq scans. For PostgreSQL, recommend B-tree indexes with specific column order.
7. Rewrite Suboptimal Subqueries
Task: Convert correlated subquery to JOIN.
Prompt:
Rewrite this MySQL query to use an explicit JOIN instead of a correlated subquery. Explain why the JOIN version is faster: SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE status = 'vip');
8. Detect and Fix N+1 Query Problem
Task: Batch queries in application code.
Prompt:
I have a Node.js app that loops through 500 users and runs SELECT * FROM orders WHERE user_id = ? for each. Write a single SQL query to fetch all orders for those 500 users at once, then show the equivalent Node.js code using a JOIN.
9. Optimize Slow LIKE Queries
Task: Use full-text search instead of LIKE.
Prompt:
Convert the following PostgreSQL query to use full-text search with GIN index: SELECT * FROM articles WHERE content LIKE '%climate change%'. Provide the CREATE INDEX command and the new query.
10. Partition Pruning for Large Tables
Task: Design partitioning strategy.
Prompt:
Given a "logs" table with 100 million rows partitioned by month (range partitioning on created_at), write a query that only scans January 2026 partition. Show how to verify partition pruning with EXPLAIN.
Prompts for Database Schema Design
11. Normalize a Denormalized Table
Task: Split a flat table into 3NF.
Prompt:
The table "sales_flat" has columns: sale_id, customer_name, customer_email, product_name, product_category, sale_date, amount. Write DDL statements to normalize this into 3NF with separate customers, products, and sales tables.
12. Index Strategy Recommendations
Task: Suggest indexes for mixed read/write workload.
Prompt:
For a PostgreSQL table with 2 million rows that receives 80% reads and 20% writes, suggest a covering index for this query: SELECT user_id, balance FROM accounts WHERE status = 'active' AND balance > 1000. Explain why covering index helps.
13. Foreign Key Constraint Decisions
Task: Decide ON DELETE CASCADE vs SET NULL.
Prompt:
Compare ON DELETE CASCADE vs ON DELETE SET NULL for a child table "order_items" referencing "orders". Provide a scenario where each is appropriate and show the ALTER TABLE syntax.
Prompts for Advanced Use Cases
14. Generate Test Data
Task: Populate a table with realistic dummy data.
Prompt:
Write a PostgreSQL script using generate_series() to insert 10,000 rows into the "customers" table (id, name, email, signup_date). Names should be random from a list of 20, emails should be unique, signup_date random in 2025.
15. Migration Script Between RDBMS
Task: Convert MySQL syntax to PostgreSQL.
Prompt:
Convert this MySQL query to PostgreSQL-compatible syntax. Note differences in LIMIT/OFFSET, string concatenation (CONCAT vs ||), and date functions: SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users WHERE DATE(created_at) = '2025-06-15' ORDER BY id LIMIT 10 OFFSET 20;
Real-World Optimization Case Study
Consider an e-commerce platform processing 500,000 orders daily. Their analytics dashboard query — joining orders, payments, and shipments — took 47 seconds. Using prompt #7 to rewrite subqueries and prompt #6 to add a composite index on (order_date, status), the same query executed in 1.2 seconds. This is a 97.5% improvement, directly impacting user experience and server costs.
Conclusion
Mastering SQL prompt engineering is not about replacing your database knowledge — it's about amplifying it. The 15 prompts above cover the most common scenarios: writing new queries, debugging performance issues, optimizing schema design, and migrating between systems. Start by copying the exact prompts, then customize the table names and conditions to match your environment. In 2026, the best database professionals are those who know how to collaborate effectively with AI tools. Use these prompts to save hours each week and write production-grade SQL with confidence.
Note: For teams integrating SQL optimization into automated workflows, platforms like ASI Biont support connecting to PostgreSQL and MySQL via API for query monitoring and performance analysis — details available at asibiont.com/courses.
Comments