Introduction
Structured Query Language (SQL) remains the backbone of modern data management, powering everything from small business applications to massive distributed systems. Yet, even experienced developers often struggle with complex queries, performance bottlenecks, and schema design pitfalls. The difference between a mediocre and a stellar database professional often lies in their ability to ask the right questions—and with AI assistants, that means crafting precise prompts.
This collection of 10 carefully crafted prompts will help you harness AI for a wide range of database tasks: writing complex queries, optimizing slow operations, designing robust schemas, and even debugging errors. Each prompt comes with a real-world example, so you can see exactly how to apply it. Whether you're a database administrator, backend developer, or data analyst, these prompts will save you hours and elevate your SQL game.
1. Write a Complex Query with Multiple Joins and Aggregations
Purpose: Generate a query that joins multiple tables, applies filters, and computes aggregates—perfect for generating reports or dashboards.
Prompt:
Write a PostgreSQL query that returns the total sales per product category for the last quarter. Include the product name, category, and total revenue. Use the tables: products (id, name, category_id, price), categories (id, name), and orders (id, product_id, quantity, created_at). Ensure the query is efficient and handles NULL values.
Example Output:
SELECT c.name AS category, p.name AS product, SUM(o.quantity * p.price) AS total_revenue
FROM products p
JOIN categories c ON p.category_id = c.id
LEFT JOIN orders o ON p.id = o.product_id
WHERE o.created_at >= date_trunc('quarter', CURRENT_DATE) - INTERVAL '3 months'
AND o.created_at < date_trunc('quarter', CURRENT_DATE)
GROUP BY c.name, p.name
ORDER BY total_revenue DESC;
2. Optimize a Slow Query Using EXPLAIN ANALYZE
Purpose: Diagnose and improve a query's performance by interpreting execution plans.
Prompt:
I have a slow query in MySQL. Here is the query: SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 10. The orders table has 10 million rows. Explain how to use EXPLAIN ANALYZE to identify bottlenecks and suggest indexes that would speed it up. Provide the exact commands.
Example Output:
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 10;
-- Look for 'type' = ALL (full scan) and 'rows' high. Add an index: CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC);
3. Design a Normalized Schema for a Given Use Case
Purpose: Generate a fully normalized database schema from a description of entities and relationships.
Prompt:
Design a normalized (3NF) relational schema for a library management system. Include tables for books, authors, members, loans, and categories. Specify primary keys, foreign keys, and the data types for each column. Provide CREATE TABLE statements for PostgreSQL.
Example Output (excerpt):
CREATE TABLE authors (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
CREATE TABLE books (
id SERIAL PRIMARY KEY,
title VARCHAR(200) NOT NULL,
author_id INT REFERENCES authors(id),
category_id INT REFERENCES categories(id)
);
4. Write a Recursive Query for Hierarchical Data
Purpose: Generate a recursive CTE to traverse tree-structured data like employee hierarchies or category trees.
Prompt:
Write a PostgreSQL recursive CTE to list all employees under a given manager (including indirect reports). The employees table has columns: id, name, manager_id. Use manager_id = 1 as the starting point. Include the employee's level in the hierarchy.
Example Output:
WITH RECURSIVE emp_tree AS (
SELECT id, name, manager_id, 0 AS level
FROM employees WHERE id = 1
UNION ALL
SELECT e.id, e.name, e.manager_id, et.level + 1
FROM employees e
JOIN emp_tree et ON e.manager_id = et.id
)
SELECT * FROM emp_tree;
5. Generate a Query to Find Duplicate Records
Purpose: Create a query that identifies duplicate entries based on one or more columns, useful for data cleaning.
Prompt:
Write a MySQL query to find duplicate email addresses in a users table. Show the email and the number of occurrences, and include only emails that appear more than once. Also, suggest a query to delete duplicates keeping the lowest ID.
Example Output:
SELECT email, COUNT(*) AS cnt FROM users GROUP BY email HAVING COUNT(*) > 1;
-- Delete duplicates:
DELETE u1 FROM users u1 INNER JOIN users u2 WHERE u1.email = u2.email AND u1.id > u2.id;
6. Convert a Slow Subquery into a JOIN
Purpose: Refactor a correlated subquery into a more efficient JOIN, often improving performance.
Prompt:
Given the following SQL query, rewrite it using a JOIN instead of a subquery. Explain the performance benefits. Query: SELECT name FROM products WHERE category_id IN (SELECT id FROM categories WHERE is_active = 1);
Example Output:
SELECT p.name FROM products p JOIN categories c ON p.category_id = c.id WHERE c.is_active = 1;
-- This allows the optimizer to use indexes on both tables and avoids a separate subquery execution.
7. Write a Window Function for Running Totals
Purpose: Generate a query using window functions (e.g., ROW_NUMBER, SUM OVER) for analytics like running totals or ranking.
Prompt:
Create a query in PostgreSQL that calculates a running total of sales per day for the last 30 days. Use the table sales (sale_date, amount). Include the daily total and the cumulative sum.
Example Output:
SELECT sale_date, amount,
SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales
WHERE sale_date >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY sale_date;
8. Explain and Fix a Deadlock in a Transaction
Purpose: Diagnose a deadlock error and provide a solution, such as adjusting isolation levels or lock ordering.
Prompt:
I'm getting a deadlock error in PostgreSQL when two transactions update the same tables in different order. Explain what causes deadlocks and provide a step-by-step solution, including setting the isolation level to READ COMMITTED and ordering UPDATE statements consistently.
Example Output:
A deadlock occurs when two transactions hold locks and each waits for the other's lock. Solution: ensure all transactions lock tables in the same order (e.g., always update accounts then transactions). You can also set a lock timeout: SET lock_timeout = '2s'; to fail fast.
9. Create a Backup and Restore Strategy
Purpose: Provide commands and best practices for backing up and restoring PostgreSQL or MySQL databases.
Prompt:
Generate a shell script that backs up a PostgreSQL database daily using pg_dump, compresses it, and retains backups for 7 days. Also, include the command to restore the database from a backup.
Example Output:
#!/bin/bash
BACKUP_DIR="/backups"
db_name="mydb"
DATE=$(date +%Y%m%d)
pg_dump -U postgres $db_name | gzip > $BACKUP_DIR/$db_name_$DATE.sql.gz
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete
# Restore: gunzip -c $BACKUP_DIR/mydb_20260818.sql.gz | psql -U postgres mydb
10. Generate a Query to Pivot Data (Rows to Columns)
Purpose: Transform rows into columns using conditional aggregation or the crosstab function in PostgreSQL.
Prompt:
Write a PostgreSQL query to pivot monthly sales data (columns: month, product, revenue) so that each product becomes a column and each row is a month. Use the sales table with columns: month, product, revenue. Provide both a conditional aggregation and a crosstab version.
Example Output:
-- Conditional aggregation
SELECT month,
SUM(CASE WHEN product = 'A' THEN revenue END) AS product_A,
SUM(CASE WHEN product = 'B' THEN revenue END) AS product_B
FROM sales GROUP BY month;
-- Crosstab (install extension: CREATE EXTENSION tablefunc;)
SELECT * FROM crosstab('SELECT month, product, revenue FROM sales ORDER BY 1,2') AS ct(month text, product_A numeric, product_B numeric);
Conclusion
Mastering SQL is a continuous journey, but with these 10 prompts, you have a powerful toolkit to tackle common database challenges. Whether you're writing complex queries, optimizing performance, or designing schemas, AI can be your expert assistant—if you know how to ask. Try these prompts in your next project and watch your productivity soar. For more advanced scenarios, consider exploring official documentation like the PostgreSQL Manual and MySQL Reference, which are authoritative sources for syntax and best practices.
Remember: the quality of the output depends on the clarity of your prompt. Be specific, provide context, and always verify the results. Happy querying!
Comments