Here’s an expanded lesson on Creating Complex SQL Queries and Integrating Them with AI to empower your AI agent to handle sophisticated data retrieval tasks.

🗃️ Lesson: Creating Complex SQL Queries and Integrating Them with AI

🎯 Lesson Objective

By the end of this lesson, you will be able to:

  • Write and understand complex SQL queries (joins, aggregations, subqueries, etc.)

  • Design prompts and tools that let your AI agent generate or interpret SQL queries

  • Integrate AI-generated SQL queries with your database securely and efficiently

  • Handle dynamic queries based on user input with validation and safety

1️⃣ Understanding Complex SQL Queries

Common SQL Features Used in Complex Queries

Feature Purpose Example
JOINs Combine data from multiple tables SELECT a.name, b.order_total FROM users a JOIN orders b ON a.id = b.user_id
GROUP BY Aggregate rows by a column SELECT user_id, SUM(amount) FROM sales GROUP BY user_id
HAVING Filter after aggregation SELECT user_id, COUNT(*) FROM sales GROUP BY user_id HAVING COUNT(*) > 5
Subqueries Nest queries inside others SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 100)
Window Functions Perform calculations over rows relative to current SELECT user_id, SUM(amount) OVER (PARTITION BY user_id) FROM sales
CTEs (WITH clause) Define temporary named result sets WITH recent_orders AS (SELECT * FROM orders WHERE date > '2025-01-01') SELECT * FROM recent_orders

2️⃣ Designing AI Prompts for SQL Generation and Interpretation

Example prompt to generate SQL:

User: "Show me the top 5 customers by total sales this quarter."
AI system prompt: "Generate an SQL query to retrieve the top 5 customers by sum of sales amount from the sales table where sale_date is in the current quarter."

Expected AI-generated SQL:

SELECT customer_id, SUM(amount) AS total_sales
FROM sales
WHERE sale_date >= DATE_TRUNC('quarter', CURRENT_DATE)
GROUP BY customer_id
ORDER BY total_sales DESC
LIMIT 5;

Example prompt to explain SQL query:

User: "What does this SQL query do?
SELECT user_id, COUNT(*) FROM logins GROUP BY user_id HAVING COUNT(*) > 10;"

AI-generated explanation:

This query counts the number of logins per user and returns only those users who have logged in more than 10 times.

3️⃣ Building an AI Tool to Generate and Execute SQL

Step 1: Setup LangChain Tool for SQL Generation & Execution

from langchain.tools import Tool
from langchain.chat_models import ChatOpenAI  # or Ollama or your LLM

llm = ChatOpenAI(model="gpt-4")  # Example LLM

def generate_sql_from_prompt(prompt: str) -> str:
    """Use LLM to generate a safe SQL query from natural language."""
    response = llm.call_as_chat([
        {"role": "system", "content": "You are an expert SQL generator. Generate only safe SELECT queries."},
        {"role": "user", "content": prompt}
    ])
    return response.content

def execute_sql_safely(query: str):
    # IMPORTANT: Validate query safety here (e.g., only SELECT allowed)
    if not query.strip().lower().startswith("select"):
        return "Only SELECT queries are allowed for safety."
    try:
        rows = query_sql_database(query)  # Use your existing query function
        if not rows:
            return "No results found."
        return "n".join(str(row) for row in rows)
    except Exception as e:
        return f"SQL execution error: {str(e)}"

def sql_agent_tool(user_input: str):
    sql_query = generate_sql_from_prompt(user_input)
    return execute_sql_safely(sql_query)

sql_tool = Tool(
    name="SQLAgentTool",
    func=sql_agent_tool,
    description="Generates and executes SQL queries safely based on user input."
)

Step 2: Integrate Tool into Your Agent

tools = [sql_tool]

agent = initialize_agent(
    tools=tools,
    llm=llm,
    agent=AgentType.ZERO_SHOT_REACT_DESCRIPTION,
    verbose=True
)

4️⃣ Handling Dynamic Queries and Validation

Tips for Secure & Effective SQL Execution

  • Restrict query types: Only allow SELECT queries, disallow any INSERT, UPDATE, DELETE, or DDL.

  • Whitelist tables/columns: Optionally restrict queries to specific tables or columns.

  • Limit result size: Impose limits (e.g., LIMIT 100) to avoid huge data dumps.

  • Sanitize inputs: Avoid SQL injection through validation or parameterized queries.

  • Log all queries: For audit and debugging.

5️⃣ Example Use Cases

Query Type Example User Input Expected SQL Output
Top Sales “Show top 10 products by revenue last month” SQL with GROUP BY product and WHERE on date range
Customer Info “List all customers from New York” SQL with WHERE city='New York'
Trends Analysis “Monthly sales trend for last year” SQL using GROUP BY DATE_TRUNC('month', sale_date)
Recent Activity “Recent orders placed in the last 7 days” SQL with WHERE order_date >= CURRENT_DATE - INTERVAL '7 days'

6️⃣ Prompt Engineering Tips for SQL AI

  • Give the AI a clear system role: “You are a SQL expert. Only output safe SELECT queries.”

  • Provide table schemas or examples if possible.

  • Ask for SQL explanations if users want to understand queries.

  • Implement a fallback to a safe template query if generation fails.

✅ Summary

What You Learned Why It Matters
Complex SQL constructs Enables powerful data retrieval
AI-generated SQL via LLMs Simplifies query writing for non-experts
Safe execution practices Protects your database and system
Dynamic prompt & validation Allows flexible but secure user queries

🔜 Next Steps

Would you like me to help you:

  1. Build a full demo agent that converts natural language to SQL and executes safely?

  2. Create example prompts and training data to improve SQL generation accuracy?

  3. Set up logging and monitoring for SQL queries run by your AI agent?

  4. Add a web interface to interactively generate and run SQL via AI?

Just let me know!

54
PreviousNext