15 Prompts to Write and Optimize SQL Queries Like a Database Pro

# 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:** ```sql 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.*

15 Prompts to Write and Optimize SQL Queries Like a Database Pro

Comments

Recent articles

Cloudflare DNS and Security Automation with ASI Biont AI Agent: No-Code Integration Guide

Psychology for IT: How Understanding Cognitive Biases and Team Dynamics Changes Development — A Review of the Asibiont Course

Summer Heat Is Hard on Your Phone. These Steps Can Help Protect It

Google Loses Final Appeal: The $4.7 Billion EU Android Antitrust Fine and What It Means for the AI/ML Developer Ecosystem

Vibe Coding Just Got Real: How Gemini Spark, Google’s Agentic Assistant, Is Changing Mac Workflows

From Zero to Crypto Pro: My Honest Review of the 'Crypto Trading, DeFi & Web3 Investments' Course on Asibiont

The Funeral for PlayStation Discs Has Begun: A Developer's Hardware Autopsy

Mastering Housing Law with AI: How Asibiont’s Housing Law Course Transforms Property Management

15 Prompts for GPT-4: From Code Generation to System Architecture