SQL Query Writing

What You’ll Learn

• How to prompt Claude to write SQL queries from plain-English descriptions of what you need
• Techniques for explaining your schema so Claude generates queries that actually run against your tables
• How to use Claude to optimize slow queries and understand query execution plans

The Use Case

SQL is one of those skills where the gap between knowing the language and knowing how to use it well is enormous. You might be comfortable writing basic SELECT statements but struggle with window functions, lateral joins, or recursive CTEs. Or you might know what data you need but not be sure which join type to use, or how to express a business requirement in set-based logic.

Claude is exceptionally good at translating business requirements into SQL because it understands both natural language and relational algebra. You describe what you want in plain English — “give me the top 10 customers by revenue for each region in Q1 2026” — and Claude handles the translation into a working query with the right aggregations, groupings, and filtering logic.

Beyond generation, Claude excels at query optimization. When a query is running slow, you can share the query and execution plan and ask Claude to identify bottlenecks — missing indexes, full table scans, inefficient join order, or subqueries that should be CTEs. This kind of optimization guidance used to require a DBA on call. Now it’s a conversation.

Step-by-Step Guide

Step 1: Share your schema first

The most important thing you can do before asking for a SQL query is share your table structure. Claude cannot write correct JOIN conditions, use the right column names, or handle data types properly without knowing your schema.

You don’t need to paste a full DDL dump. A concise schema description is enough:

Tables:
- orders (id, customer_id, created_at, status, total_amount)
- customers (id, name, email, region, created_at)
- order_items (id, order_id, product_id, quantity, unit_price)
- products (id, name, category, cost_price)

Database: PostgreSQL 15

Add foreign key relationships if they’re not obvious: “orders.customer_id references customers.id”.

Step 2: Describe what you need in plain English

Write out what you want in natural language, as if explaining it to a colleague. Be specific about:

• What data you want returned (columns, calculations)
• Which records to include (filtering conditions)
• How to group or aggregate (sums, counts, averages per group)
• How to sort and how many results you need

Example: “I need a query that shows, for each customer, their total number of orders, total spend, and average order value — but only for customers who placed at least 3 orders in 2025. Sort by total spend descending, and limit to the top 20.”

Step 3: Specify your SQL dialect

SQL dialects differ enough to matter. Mention your database:

• PostgreSQL, MySQL 8, SQLite, SQL Server, BigQuery, Snowflake, DuckDB, etc.

Each has different syntax for things like date functions, string aggregation, window functions, and CTEs. A query that runs perfectly on PostgreSQL may need adjustments for MySQL.

Step 4: Ask for explanation with the query

When Claude generates a query, ask it to explain the approach — especially for complex queries with multiple CTEs, window functions, or subqueries. Understanding the structure helps you adapt it when requirements change.

Add to your prompt: “After the query, briefly explain how it works — especially any non-obvious parts like window functions or the CTE structure.”

Step 5: Optimize a slow query

For optimization, paste both the slow query and its execution plan (output from EXPLAIN ANALYZE in PostgreSQL, EXPLAIN in MySQL, etc.). Ask Claude to:

1. Identify the most expensive operations in the plan
2. Suggest specific changes to the query or schema (indexes, rewriting subqueries as joins, etc.)
3. Show you the optimized version

Prompt Template

Database: [PostgreSQL / MySQL / SQLite / etc.]

Schema:
[Table name] ([column1, column2, ...])
[Table name] ([column1, column2, ...])
[Any important relationships, e.g., "orders.customer_id references customers.id"]

What I need:
[Plain English description of the data you want, including:]
- Which records to include (filtering)
- How to aggregate or group (if applicable)
- How to sort and limit results

Please write an efficient SQL query for this. After the query, briefly explain how it works.

For query optimization:

Database: [PostgreSQL / MySQL / etc.]

I have a slow query. Here it is:

[PASTE QUERY]

Execution plan (EXPLAIN ANALYZE output):
[PASTE EXPLAIN OUTPUT]

What's causing the slowness, and how would you rewrite the query or add indexes to fix it?

Tips & Best Practices

1. Use CTEs for readability, not just correctness — When Claude generates a complex query, ask it to use CTEs (Common Table Expressions) to break the logic into named steps. This makes the query far easier to understand, test, and modify. Add: “Please structure this using CTEs rather than nested subqueries.”

2. Ask for edge case handling — SQL has sharp edges: NULLs in aggregations, division by zero in calculated columns, empty groups. Add “Handle NULL values appropriately and avoid division-by-zero errors” to get robust queries from the start.

3. Request index recommendations — When you get a query, ask: “What indexes would most improve this query’s performance? Please write the CREATE INDEX statements.” This is often the highest-leverage optimization available.

4. Test with LIMIT first — When Claude gives you a query you haven’t run before, ask it to add a LIMIT 100 so you can validate the output without running an expensive full scan. You can remove the limit once you’ve confirmed correctness.

5. Iterate with data examples — If a query isn’t returning what you expect, share sample input data and the expected output. “Given these 3 rows in orders… I expect to get… but I’m getting…” This lets Claude reason about the exact behavior rather than guessing at your data.

Try It Yourself

Think of a report or data extract you need from a database you work with. Write out what you need in a single plain-English paragraph — the columns, filters, groupings, and sort order. Then open Claude and paste:

“Here is my database schema: [your tables]. I need: [your plain-English description]. Please write a PostgreSQL query for this and explain any complex parts.”

Run the query, check the output, and use follow-up prompts to refine it.