10 Prompts for Writing SQL Queries and Optimizing Databases

## Introduction Writing efficient SQL queries and optimizing database performance are critical skills for any developer or data professional. However, even experienced engineers can spend hours debugging slow queries or crafting complex joins. Large language models (LLMs) like GPT-4 and Claude can dramatically accelerate this process—if you know how to prompt them correctly. This article presents 10 ready-to-use prompts for generating, debugging, and optimizing SQL queries, with real-world examples and best practices. Whether you're a beginner trying to understand window functions or a senior engineer tuning a PostgreSQL database, these prompts will save you time and help you write cleaner, faster SQL. ## 1. Generate a Query from a Natural Language Description **Prompt:** ``` I need a SQL query for [database type: PostgreSQL/MySQL/SQL Server] that does the following: [describe business logic]. The relevant tables are: - [table1] with columns [col1, col2, ...] - [table2] with columns [col1, col2, ...] Foreign keys: [describe relationships] Please output only the SQL query, with comments explaining each part. ``` **Explanation:** This prompt forces the AI to understand your schema and produce a syntactically correct query. By specifying the database type, you get dialect-specific syntax (e.g., `LIMIT` vs `TOP`, `ILIKE` vs `LIKE`). **Example:** *User input:* "Find the top 5 customers by total order value in the last 30 days. Database: PostgreSQL. Tables: customers(id, name), orders(id, customer_id, amount, created_at)." *AI output:* ```sql SELECT c.name, SUM(o.amount) AS total_spent FROM customers c JOIN orders o ON c.id = o.customer_id WHERE o.created_at >= CURRENT_DATE - INTERVAL '30 days' GROUP BY c.id, c.name ORDER BY total_spent DESC LIMIT 5; ``` ## 2. Debug a Slow Query **Prompt:** ``` I have a query that takes [X seconds] to run. Here is the query: [your SQL code] Here is the EXPLAIN ANALYZE output: [output] Please identify the bottleneck and suggest specific indexes or rewrites to improve performance. ``` **Explanation:** This prompt leverages the AI's ability to read execution plans and suggest concrete improvements like missing indexes, inefficient joins, or suboptimal WHERE clauses. **Example:** *User input:* Query runs 12 seconds on a 5M-row table. EXPLAIN shows a sequential scan on `orders` with a filter on `status`. *AI suggestion:* "Add a composite index on `orders(status, created_at)` to reduce the scan. Also, rewrite the `IN` subquery as a `JOIN` for better performance." ## 3. Rewrite a Query Using Window Functions **Prompt:** ``` Rewrite the following query using window functions (ROW_NUMBER, RANK, LAG, etc.) to make it more efficient and readable: [your query with self-joins or subqueries] Database: [type]. ``` **Explanation:** Many developers overuse self-joins or correlated subqueries for tasks like "find the latest order per customer." Window functions are often faster and cleaner. **Example:** *User query (inefficient):* ```sql SELECT c.*, o.* FROM customers c JOIN orders o ON c.id = o.customer_id WHERE o.created_at = (SELECT MAX(created_at) FROM orders o2 WHERE o2.customer_id = c.id); ``` *AI rewrite:* ```sql SELECT c.*, o.* FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn FROM orders ) o JOIN customers c ON c.id = o.customer_id WHERE o.rn = 1; ``` ## 4. Normalize a Denormalized Table **Prompt:** ``` I have a denormalized table [table_name] with columns [list]. It violates [1NF/2NF/3NF] because [describe redundancy]. Generate SQL to split it into normalized tables, including foreign keys and indexes. ``` **Explanation:** This prompt helps you design a proper relational schema from a flat file or legacy table. **Example:** *User input:* Table `orders_flat` has columns: order_id, customer_name, customer_email, product1, product2, product3. *AI output:* Creates `customers`, `orders`, `order_items` tables with proper normalization. ## 5. Optimize a JOIN-Heavy Query with Execution Plan Analysis **Prompt:** ``` Here is a query that joins 6 tables and runs slowly. Database: PostgreSQL. [query] EXPLAIN ANALYZE output: [output] Please suggest: - Which indexes to add - Whether to use hash join vs merge join - Any CTE or temp table optimizations ``` **Explanation:** This is an advanced prompt for performance tuning. It combines execution plan reading with indexing advice. ## 6. Convert Between SQL Dialects **Prompt:** ``` Convert this [MySQL] query to [PostgreSQL]. Pay attention to: - Date functions - LIMIT / TOP - String concatenation - Auto-increment syntax Original query: [query] ``` **Explanation:** When migrating databases, syntax differences can break queries. This prompt handles dialect-specific functions. ## 7. Generate Test Data for a Schema **Prompt:** ``` Generate a set of INSERT statements to populate the following schema with [N] rows of realistic test data: [CREATE TABLE statements] Consider: - Foreign key constraints (insert parent tables first) - Realistic names, dates, prices - Use random functions like RANDOM() or UUID generation ``` **Explanation:** Quickly create sample data for development or testing without manual work. ## 8. Write a Recursive CTE for Hierarchical Data **Prompt:** ``` I have a table [table_name] with a parent_id column representing a tree structure. Write a recursive CTE to: - Get all descendants of a given node - Include depth level - Order by hierarchy ``` **Explanation:** Recursive CTEs are powerful but tricky. This prompt yields a correct template for any tree structure (categories, org charts, etc.). ## 9. Optimize a Query with Anti-Joins and EXISTS **Prompt:** ``` Rewrite the following query to use anti-joins (NOT EXISTS) or EXISTS instead of NOT IN to improve performance: [query with NOT IN or LEFT JOIN ... IS NULL] Explain why the rewrite might be faster. ``` **Explanation:** `NOT IN` can be slow with NULLs and large datasets. This prompt teaches best practices. ## 10. Add Indexing Recommendations Based on Query Patterns **Prompt:** ``` Given the following query workload (list of queries with their frequency), suggest an indexing strategy: - Query 1: [SQL, runs 1000 times/day] - Query 2: [SQL, runs 500 times/day] - Table size: [N] rows - Database: [type] For each index, specify: - Columns and order - Index type (B-tree, hash, GIN, etc.) - Whether it's a covering index ``` **Explanation:** This prompt helps design indexes based on actual usage, not just single queries. ## Real-World Case Study **Problem:** An e-commerce platform (PostgreSQL) had a product search page that took 8–15 seconds to load. The query joined 5 tables (`products`, `categories`, `inventory`, `reviews`, `prices`) and used multiple `OR` conditions. **Solution using prompts:** 1. Used Prompt #2 to debug the slow query. The AI identified a missing composite index on `inventory(product_id, warehouse_id)` and a full table scan on `reviews`. 2. Used Prompt #5 to rewrite the query with a CTE that pre-filtered `reviews` by rating. 3. Used Prompt #10 to suggest a covering index on `products` for the search columns. **Results:** - Query time dropped from 12s to 0.3s. - Index size increased by 2GB, but storage is cheap compared to user frustration. - Page load time improved from 15s to 1.2s. **Lessons learned:** - Always run `EXPLAIN ANALYZE` before optimizing. - Composite indexes with column order matter: put high-selectivity columns first. - CTEs can help but may materialize data—test with `EXPLAIN ANALYZE`. ## Conclusion Mastering SQL prompts can turn an AI into your personal database consultant. Use the 10 prompts above to generate, debug, and optimize queries faster than ever. Remember to always test generated SQL against your actual data and execution plans—AI can make mistakes, especially with complex joins or database-specific quirks. Start with a simple prompt today, and you might be surprised how much time you save. And if you're looking for a platform that integrates AI-powered SQL generation with your data stack, ASI Biont supports connecting to PostgreSQL, MySQL, and SQL Server via API — learn more at asibiont.com/courses.

10 Prompts for Writing SQL Queries and Optimizing Databases

Comments

Recent articles

7 Prompts for SMM and Content Planning: Posts, Stories, Reels That Actually Drive Engagement

10 Prompts for HTML/CSS Layout: From Wireframe to Responsive Design

GLM5.2 on AMD MI355X at 2626 tok/s/node at over 2x lower cost than Blackwell

IFRS — International Financial Reporting Standards (Advanced): How AI Learning on asibiont.com Helps Master IFRS in 2026

12 Smart Prompts for Terraform and IaC: From Modules to Multi-Cloud

Курс GDPR на asibiont.com: как AI-обучение помогает освоить защиту данных в ЕС быстрее и эффективнее

Vibe Coding vs. Classical Programming: Which Path Gets You Hired in 2026?

NYT Connections Hints and Answers for Saturday, July 4 (Game #1119): A Tech Tool Review for AI and Data Workflows

How to Integrate ASI Biont AI Agent with Mindbox: No-Code Automation for Customer Segmentation and Campaigns