# 09. Agent interacting with SQL database

## Agent interacting with SQL database <a href="#sql" id="sql"></a>

In this tutorial **Agent to answer questions about SQL database** Introducing how to build step by step.

The flow of agents running SQL queries is:

1. **Identify database schemas** : Get a list of available tables.
2. **Select related table** : Select the table associated with the question.
3. **DDL lookup** : Get the schema definition (DDL) of the selected table.
4. **Query generation** : Create SQL queries based on questions and DDL information.
5. **Query check** : Use LLM to review common errors and improve queries.
6. **Query execution and error handling** : Run the query on the database engine, fix it in case of an error, and successfully perform the query.
7. **Create response** : Provides final answer based on query results.

**Main contents**

* **database** : SQLite database settings and `chinook` Sample database load
* **Utility function** : Define utility functions for agent implementation
* **Tool definition** : Define tools to interact with the database
* **Define walkflow** : Define the agent's workflow (graph)
* **Graph visualization** : Defined graph visualization
* **Agent execution** : Agent execution and results verification
* **evaluation** : Agent evaluation and performance comparison

### Preferences <a href="#id-1" id="id-1"></a>

First, install the required package and set the API key.

```python
# Configuration file for managing API keys as environment variables
from dotenv import load_dotenv

# Load API key information
load_dotenv()
```

```
 True 
```

```python
# Set up LangSmith tracking. https://smith.langchain.com
# !pip install -qU langchain-teddynote
from langchain_teddynote import logging

# Enter a project name.
logging.langsmith("CH17-LangGraph-Use-Cases")
```

```
 Start tracking LangSmith. 
[Project name] 
CH17-LangGraph-Use-Cases 
```

```python
from langchain_teddynote.models import get_model_name, LLMs

MODEL_NAME = get_model_name(LLMs.GPT4o)
print(f"Model name used: {MODEL_NAME}")
```

```
 Model name used: gpt-4o
```

### Database settings <a href="#id-2" id="id-2"></a>

This tutorial creates an SQLite database. SQLite is a lightweight database that is easy to set up and use.

In this tutorial, it is a sample database `chinook` The database will be loaded, which is a sample database representing the digital media store.

For more information about the database [here ](https://www.sqlitetutorial.net/sqlite-sample-database/)You can check in. First, use it for practice `chinook` Download the database.

```python
import requests

url = "https://storage.googleapis.com/benchmarks-artifacts/chinook/Chinook.db"

response = requests.get(url)

if response.status_code == 200:
    with open("Chinook.db", "wb") as file:
        file.write(response.content)
    print("File downloaded and saved as Chinook.db")
else:
    print(f"Failed to download the file. Status code: {response.status_code}")
```

```
 File downloaded and saved as Chinook.db
```

Next downloadable `chinook` Using the database `SQLDatabase` Generate tools and sample queries `"SELECT * FROM Artist LIMIT 5;"` Run.

```python
from langchain_community.utilities import SQLDatabase

# Creating a SQLDatabase instance from a SQLite database file
db = SQLDatabase.from_uri("sqlite:///Chinook.db")

# DB dialect ouput of power(sqlite)
print(db.dialect)

# Output a list of table names available in the database.
print(db.get_usable_table_names())

# Execute SQL queries
db.run("SELECT * FROM Artist LIMIT 5;")
```

```
 sqlite 
['Album','Artist','Customer','Employee','Genre','Invoice','InvoiceLine','MediaType','Playlist','PlaylistTrack','Track'] 
```

```
 "[(1,'AC/DC'), (2,'Accept'), (3,'Aerosmith'), (4,'Alanis Morissette'), (5,'Alice In Chains')]" 
```

If error occurs, it returns an error message.

`db_query_tool` In the case of, run the query against the database and return the results.

next `db_query_tool` Defines.

```
 Album, Artist, Customer, Employee, Genre, Invoice, InvoiceLine, MediaType, Playlist, PlaylistTrack, Track 

CREATE TABLE "Artist" ( 
    "ArtistId" INTEGER NOT NULL,  
    "Name" NVARCHAR (120),  
    PRIMARY KEY ("ArtistId") 
) 

/* 
3 rows from Artist table: 
ArtistId Name 
1 AC/DC 
2 Accept 
3 Aerosmith 
*/ 
```

```python
# Select a tool to list the tables available in your database
list_tables_tool = next(tool for tool in tools if tool.name == "sql_db_list_tables")

# Select a tool to retrieve the DDL of a specific table
get_schema_tool = next(tool for tool in tools if tool.name == "sql_db_schema")

# Output a list of all tables in the database
print(list_tables_tool.invoke(""))

# Output DDL information of artist table
print(get_schema_tool.invoke("Artist"))
```

Below `list_tables_tool` and `get_schema_tool` Example of execution for.

```
 [QuerySQLDatabaseTool (description=" Input to this tool is a detailed and correct SQL query, output is a result from the database. If the query is not correct, an error message will be be returned. If an error is returned, rewrite the query, check the query, and try again. If you engineer an issue with Unknown column'xxxx' in'field list', use sql_db_schema to query the correct table fields.", db=  ), InfoSQLDatabaseTool (description='Input to this tool is a comma-separated list of tables, output is the schema and sample rows for thatose tables. Be sure that the tables actually exist by calling sql_db_list_tables first! Example Input: table1, table2, table3', db=  ), ListSQLDatabaseTool (db=  ), QuerySQLCheckerTool (description='Use this tool to double check if your query is correct before checking it. Always use this tool before checking a query with sql_db_query!', db=  , llm=ChatOpenAI (client=  , async_client=  , root_client=  , root_async_client=  , model_name='gpt-4o', model_kwargs={}, openai_api_key=SecretStr('*********'), llm_chain=LLMChain (verbose=False, promptn- Using UNION when UNION ALL should have be used\n- Using BETWEEN for exclusive ranges\n- Data type mismatch in predicates\n- Property quoting identifiers\n- Using the correct number If there are no mistakes, just reproduce the original query.\n\nOutput the final SQL query only.\n\nSQL Query:'),  llm=ChatOpenAI(client=  , async_client=  , root_client=  , root_async_client=  , model_name='gpt-4o', model_kwargs={}, openai_api_key=SecretStr('************)), output_parser=StrOutputParser(), llm_kw 
```

```python
from langchain_community.agent_toolkits import SQLDatabaseToolkit
from langchain_openai import ChatOpenAI

# SQLDatabaseToolkit generation
toolkit = SQLDatabaseToolkit(db=db, llm=ChatOpenAI(model=MODEL_NAME))

# List of tools available in SQLDatabaseToolkit
tools = toolkit.get_tools()
tools
```

* **function** : Validation before Query runs
* **Inspection items** :
* Using NULL values and NOT IN
* UNION vs UNION ALL adequacy
* BETWEEN range setting
* Data type match
* Identifier citation adequacy
* Number of Function arguments
* Data type casting
* Join column accuracy
* **Features** : GPT-4 model based verification

**QuerySQLCheckerTool**

* **function** : Database View my table list

**ListSQLDatabaseTool**

* **function** : Table schema and sample data lookup
* **Input** : List of tables separated by comas
* **Example of use** : `table1, table2, table3`
* **caution** : `sql_db_list_tables` Pre-check availability as table

**InfoSQLDatabaseTool**

* **function** : Execute SQL query and return results
* **Input** : Accurate SQL query
* **Output** : Database results or error message
* **Error processing** :
* Query error generation and retry
* `Unknown column` Error `sql_db_schema` Check accurate table fields with

**QuerySQLDataBaseTool**

The following is intended to interact with SQL database. `SQLDatabaseToolkit` List of tools.

#### Database query related tools <a href="#id-4" id="id-4"></a>

* **`CREATE`** : Create database objects.
* Yes: `CREATE TABLE users (id INT, name VARCHAR(100));`
* **`ALTER`** : Modifies existing database objects.
* Yes: `ALTER TABLE users ADD COLUMN email VARCHAR(100);`
* **`DROP`** : Delete database objects.
* Yes: `DROP TABLE users;`

Main DDL instructions

* DDL (Data Definition Language, **Data Definition Language** ) Refers to the structure of the database and the SQL instructions used to define or modify the schema. Mainly used to create, modify, and delete database objects such as tables, indexes, views, and schemas.

**Reference**

1. `list_tables_tool` : Get the available tables from the database.
2. `get_schema_tool` : Get the DDL of the table.
3. `db_query_tool` : Run the query and get the result or return an error message if the query fails.

Define some tools to help agents interact with the database.

### SQL Query Execution Tool <a href="#sql_1" id="sql_1"></a>

```python
from typing import Any

from langchain_core.messages import ToolMessage
from langchain_core.runnables import RunnableLambda, RunnableWithFallbacks
from langgraph.prebuilt import ToolNode


# error handling function
def handle_tool_error(state) -> dict:
    # view error information
    error = state.get("error")
    # view tool information
    tool_calls = state["woolMessage 로 래핑 후 반환
    return {
        "messages": [
            ToolMessage(
                content=f"Here is the error: {repr(error)}\n\nPlease fix your mistakes.",
                tool_call_id=tc["id"],
            )
            for tc in tool_calls
        ]
    }


# Create a ToolNode to handle errors and pass them on to the agent.
def create_tool_node_with_fallback(tools: list) -> RunnableWithFallbacks[Any, dict]:
    """
    Create a ToolNode with a fallback to handle errors and surface them to the agent.
    """
    # Add a ToolNode with a fallback action defined when an error occurs.
    return ToolNode(tools).with_fallbacks(
        [RunnableLambda(handle_tool_error)], exception_key="error"
    )
```

```python
from langchain_core.tools import tool


# Query exwecution tool
@tool
def db_query_tool(query: str) -> str:
    """
    Run SQL queries against a database and return results
    Returns an error message if the query is incorrect
    If an error is returned, rewrite the query, check, and retry
    """
    # exewcute query
    result = db.run_no_throw(query)

    # ERROR: If no result is found, return error message
    if not result:
        return "Error: Query failed. Please rewrite your query and try again."
    # Normal: Returns query execution results
    return result
```

When running normally

```python
# Select top 10 rows from Artist table and output execution results
print(db_query_tool.invoke("SELECT * FROM Artist LIMIT 10;"))
```

```
 [(1,'AC/DC'), (2,'Accept'), (3,'Aerosmith'), (4,'Alanis Morissette'), (5,'Alice In Chains'), (6,'Antônio Carlos Jobim'), (7,'Apocalyptica'), (8,
```

Example if an error occurs

```python
# Select top 10 rows from Artist table and output execution results
print(db_query_tool.invoke("SELECT * FROM Artist LIMITS 10;"))
```

```
 Error: (sqlite3.OperationalError) near "10": syntax error 
[SQL: SELECT * FROM Artist LIMITS 10;] 
(Background on this error at: https://sqlalche.me/e/20/e3q8) 
```

SQL Query Checker

Next, we will use LLM to check for common mistakes in SQL queries.

This is not a tool, strictly speaking, but will be added as a node to the subsequent workflow.

```python
from langchain_core.prompts import ChatPromptTemplate
from langchain_openai import ChatOpenAI

# Define system messages to check for common mistakes in SQL queries.
query_check_system = """You are a SQL expert with a strong attention to detail.
Double check the SQLite query for common mistakes, including:
- Using NOT IN with NULL values
- Using UNION when UNION ALL should have been used
- Using BETWEEN for exclusive ranges
- Data type mismatch in predicates
- Properly quoting identifiers
- Using the correct number of arguments for functions
- Casting to the correct data type
- Using the proper columns for joins

If there are any of the above mistakes, rewrite the query. If there are no mistakes, just reproduce the original query.

You will call the appropriate tool to execute the query after running this check."""

# generate prompt
query_check_prompt = ChatPromptTemplate.from_messages(
    [("system", query_check_system), ("placeholder", "{messages}")]
)

# Creating a Query Checker Chain
query_check = query_check_prompt | ChatOpenAI(
    model=MODEL_NAME, temperature=0
).bind_tools([db_query_tool], tool_choice="db_query_tool")
```

Blow and call the wrong query to make sure the results are well corrected.

**Reference**

* `LIMIT` instead `LIMITS` I used to blow the query.

```python
# Run a query check node using user messages
response = query_check.invoke(
    {"messages": [("user", "SELECT * FROM Artist LIMITS 10;")]}
)
print(response.tool_calls[0])
```

```
 {'name':'db_query_tool','args': {'query':'SELECT * FROM Artist LIMIT 10;'},'id':'call_yX4yc3O8jF9GJNTP1ocsrWkU','type':
```

The results were well corrected.

Graph definition

Defines the agent's workflow.

Agent first `list_tables_tool` After forcibly calling to get the available table from the database, follow the steps mentioned at the beginning of the tutorial.

```python
from typing import Annotated, Literal

from langchain_core.messages import AIMessage
from langchain_openai import ChatOpenAI

from pydantic import BaseModel, Field
from typing_extensions import TypedDict

from langgraph.graph import END, StateGraph, START
from langgraph.graph.message import AnyMessage, add_messages
from langgraph.checkpoint.memory import MemorySaver


# Defining the state of an agent
class State(TypedDict):
    messages: Annotated[list[AnyMessage], add_messages]


# New graph definition
workflow = StateGraph(State)


# Add a node for the first tool call
def first_tool_call(state: State) -> dict[str, list[AIMessage]]:
    return {
        "messages": [
            AIMessage(
                content="",
                tool_calls=[
                    {
                        "name": "sql_db_list_tables",
                        "args": {},
                        "id": "initial_tool_call_abc123",
                    }
                ],
            )
        ]
    }


# Define a function to check the correctness of a query against a model.
def model_check_query(state: State) -> dict[str, list[AIMessage]]:
    """
    Use this tool to check that your query is correct before you run it
    """
    return {"messages": [query_check.invoke({"messages": [state["messages"][-1]]})]}


# Add the first tool call node
workflow.add_node("first_tool_call", first_tool_call)

# Add nodes for the first two tools
workflow.add_node(
    "list_tables_tool", create_tool_node_with_fallback([list_tables_tool])
)
workflow.add_node("get_schema_tool", create_tool_node_with_fallback([get_schema_tool]))

# Add a model node that selects relevant tables based on the question and available tables.
model_get_schema = ChatOpenAI(model=MODEL_NAME, temperature=0).bind_tools(
    [get_schema_tool]
)
workflow.add_node(
    "model_get_schema",
    lambda state: {
        "messages": [model_get_schema.invoke(state["messages"])],
    },
)


# Tooltip indicating the final state
class SubmitFinalAnswer(BaseModel):
    """Submit final answer to user based on query results"""

    final_answer: str = Field(..., description="The final answer to the user")


# Add a model node to generate queries based on questions and schema.
QUERY_GEN_INSTRUCTION = """You are a SQL expert with a strong attention to detail.

You can define SQL queries, analyze queries results and interpretate query results to response an answer.

Read the messages bellow and identify the user question, table schemas, query statement and query result, or error if they exist.

1. If there's not any query result that make sense to answer the question, create a syntactically correct SQLite query to answer the user question. DO NOT make any DML statements (INSERT, UPDATE, DELETE, DROP etc.) to the database.

2. If you create a query, response ONLY the query statement. For example, "SELECT id, name FROM pets;"

3. If a query was already executed, but there was an error. Response with the same error message you found. For example: "Error: Pets table doesn't exist"

4. If a query was already executed successfully interpretate the response and answer the question following this pattern: Answer: <<question answer>>. For example: "Answer: There three cats registered as adopted"
"""

query_gen_prompt = ChatPromptTemplate.from_messages(
    [("system", QUERY_GEN_INSTRUCTION), ("placeholder", "{messages}")]
)
query_gen = query_gen_prompt | ChatOpenAI(model=MODEL_NAME, temperature=0).bind_tools(
    [SubmitFinalAnswer, model_check_query]
)


# Conditional edge definition
def should_continue(state: State) -> Literal[END, "correct_query", "query_gen"]:
    messages = state["messages"]

    last_message = messages[-1]
    if last_message.content.startswith("Answer:"):
        return END
    if last_message.content.startswith("Error:"):
        return "query_gen"
    else:
        return "correct_query"


# defining a query generation node
def query_gen_node(state: State):
    message = query_gen.invoke(state)

    # LLM returns an error message if it calls the wrong tool.
    tool_messages = []
    message.pretty_print()
    if message.tool_calls:
        for tc in message.tool_calls:
            if tc["name"] != "SubmitFinalAnswer":
                tool_messages.append(
                    ToolMessage(
                        content=f"Error: The wrong tool was called: {tc['name']}. Please fix your mistakes. Remember to only call SubmitFinalAnswer to submit the final answer. Generated queries should be outputted WITHOUT a tool call.",
                        tool_call_id=tc["id"],
                    )
                )
    else:
        tool_messages = []
    return {"messages": [message] + tool_messages}


# Add a query generation node
workflow.add_node("query_gen", query_gen_node)

# Add a node to check the model before executing the query
workflow.add_node("correct_query", model_check_query)

# Add a node to run the query
workflow.add_node("execute_query", create_tool_node_with_fallback([db_query_tool]))

# Specifying edges between nodes
workflow.add_edge(START, "first_tool_call")
workflow.add_edge("first_tool_call", "list_tables_tool")
workflow.add_edge("list_tables_tool", "model_get_schema")
workflow.add_edge("model_get_schema", "get_schema_tool")
workflow.add_edge("get_schema_tool", "query_gen")
workflow.add_conditional_edges(
    "query_gen",
    should_continue,
)
workflow.add_edge("correct_query", "execute_query")
workflow.add_edge("execute_query", "query_gen")

# Compile into an executable workflow
app = workflow.compile(checkpointer=MemorySaver())
```

```python
from langchain_teddynote.graphs import visualize_graph

visualize_graph(app, xray=True)
```

```
```

```
 ================================================== 
🔄 Node: first_tool_call 🔄 
- - - - - - - - - - - - - - - - - - - - - - - - - - - -  
================================== Ai Message ================================== 
Tool Calls: 
  sql_db_list_tables (initial_tool_call_abc123) 
 Call ID: initial_tool_call_abc123 
  Args: 
================================================== 

================================================== 
🔄 Node: list_tables_tool 🔄 
- - - - - - - - - - - - - - - - - - - - - - - - - - - -  
================================= Tool Message ================================= 
Name: sql_db_list_tables 

Album, Artist, Customer, Employee, Genre, Invoice, InvoiceLine, MediaType, Playlist, PlaylistTrack, Track 
================================================== 

================================================== 
🔄 Node: model_get_schema 🔄 
- - - - - - - - - - - - - - - - - - - - - - - - - - - -  
================================== Ai Message ================================== 
Tool Calls: 
  sql_db_schema (call_t0Xgf8qajlVCqFYw4TPsWnQJ) 
 Call ID: call_t0Xgf8qajlVCqFYw4TPsWnQJ 
  Args: 
    table_names: Employee 
================================================== 

================================================== 
🔄 Node: get_schema_tool 🔄 
- - - - - - - - - - - - - - - - - - - - - - - - - - - -  
================================= Tool Message ================================= 
Name: sql_db_schema 


CREATE TABLE "Employee" ( 
    "EmployeeId" INTEGER NOT NULL,  
    "LastName" NVARCHAR (20) NOT NULL,  
    "FirstName" NVARCHAR (20) NOT NULL,  
    "Title" NVARCHAR (30),  
    "ReportsTo" INTEGER,  
    "BirthDate" DATETIME,  
    "HireDate" DATETIME,  
    "Address" NVARCHAR (70),  
    "City" NVARCHAR (40),  
    "State" NVARCHAR (40),  
    "Country" NVARCHAR (40),  
    "PostalCode" NVARCHAR (10),  
    "Phone" NVARCHAR (24),  
    "Fax" NVARCHAR (24),  
    "Email" NVARCHAR (60),  
    PRIMARY KEY ("EmployeeId"),  
    FOREIGN KEY ("ReportsTo") REFERENCES "Employee" ("EmployeeId") 
) 

/* 
3 rows from Employee table: 
EmployeeId LastName FirstName Title ReportsTo BirthDate HireDate Address City State Country PostalCode Phone Fax Email 
1 Adams Andrew General Manager None 1962-02-18 00:00:00 2002-08-14 00:00:00 11120 Jasper Ave NW Edmonton AB Canada T5K 2N1 +1 (780) 428-9482 < 
2 Edwards Nancy Sales Manager 1 1958-12-08 00:00:00 2002-05-01 00:00:00 825 8 Ave SW Calgary AB Canada T2P 2T3 +1 (403) 262-3443 <TAG1 
3 Peacock Jane Sales Support Agent 2 1973-08-29 00:00:00 2002-04-01 00:00:00 1111 6 Ave SW Calgary AB Canada T2P 5M5 +1 (403) 262-3443 
*/ 
================================================== 
================================== Ai Message ================================== 
Tool Calls: 
  model_check_query (call_TufzpDXmRKdDDFcg2QhLiHOz) 
 Call ID: call_TufzpDXmRKdDDFcg2QhLiHOz 
  Args: 
    state:'TAG1>'messages':'{'content':'Andrew Adam employee's human information','type':'human'{, }'content':'Album, Artist, InvoiceLine NVARCHAR (30), \n\t"ReportsTo" INTEGER, \n\t" BirthDate" DATETIME, \n\t" HireDate" DATETIME, \n\t"Address" NVARCHAR(70), \n\t"City" NVARCHARn (40)  PostalCode" NVARCHAR(10), \n\t"Phone" NVARCHAR(24), \n\t"Fax" NVARCHAR(24), \n\t"Email" NVARCHAR(60), \n\tPRIMARY KEY ("EmployeeId"), \n\ttAndrew\tGeneral Manager\tNone\t1962-02-18 00:00:00\t2002-08-14 00:00:00\t1120 Jasper Ave NW\tEdmonton\tAB\tCanada\t5K 2N1\t+1(00\t825 8 Ave SW\tCalgary\tAB\tCanada\tT2P 2T3\t+1 (403) 262-3443\t+1 (403) 262-3322\tnancy@chinookcorp.  com\n3\tPeacock\tJane\tSales Support Agent\t2\t1973-08-29 00:00:00\t2002-04-01 00:00:00\t111 6 Ave SW\tCalgary\tAB\tCanada\tT2P 5M5\ 

================================================== 
🔄 Node: query_gen 🔄 
- - - - - - - - - - - - - - - - - - - - - - - - - - - -  
================================== Ai Message ================================== 
Tool Calls: 
  model_check_query (call_TufzpDXmRKdDDFcg2QhLiHOz) 
 Call ID: call_TufzpDXmRKdDDFcg2QhLiHOz 
  Args: 
    state:'TAG1>'messages':'{'content':'Andrew Adam employee's human information','type':'human'{, }'content':'Album, Artist, InvoiceLine NVARCHAR (30), \n\t"ReportsTo" INTEGER, \n\t" BirthDate" DATETIME, \n\t" HireDate" DATETIME, \n\t"Address" NVARCHAR(70), \n\t"City" NVARCHARn (40)  PostalCode" NVARCHAR(10), \n\t"Phone" NVARCHAR(24), \n\t"Fax" NVARCHAR(24), \n\t"Email" NVARCHAR(60), \n\tPRIMARY KEY ("EmployeeId"), \n\ttAndrew\tGeneral Manager\tNone\t1962-02-18 00:00:00\t2002-08-14 00:00:00\t1120 Jasper Ave NW\tEdmonton\tAB\tCanada\t5K 2N1\t+1(00\t825 8 Ave SW\tCalgary\tAB\tCanada\tT2P 2T3\t+1 (403) 262-3443\t+1 (403) 262-3322\tnancy@chinookcorp.  com\n3\tPeacock\tJane\tSales Support Agent\t2\t1973-08-29 00:00:00\t2002-04-01 00:00:00\t111 6 Ave SW\tCalgary\tAB\tCanada\tT2P 5M5\ 
================================= Tool Message ================================= 

Error: The wrong tool was cled: model_check_query. Please fix your mistakes. Remember to only call SubmitFinalAnswer to submit the final answer. Generated queries should be outputted WITHOUT a tool call. 
================================================== 
================================== Ai Message ================================== 

SELECT * FROM Employee WHERE FirstName ='Andrew' AND LastName ='Adams'; 

================================================== 
🔄 Node: query_gen 🔄 
- - - - - - - - - - - - - - - - - - - - - - - - - - - -  
================================== Ai Message ================================== 

SELECT * FROM Employee WHERE FirstName ='Andrew' AND LastName ='Adams'; 
================================================== 

================================================== 
🔄 Node: correct_query 🔄 
- - - - - - - - - - - - - - - - - - - - - - - - - - - -  
================================== Ai Message ================================== 
Tool Calls: 
  db_query_tool (call_WIqyIUSJT1f7cY6f18DFsl9F) 
 Call ID: call_WIqyIUSJT1f7cY6f18DFsl9F 
  Args: 
    query: SELECT * FROM Employee WHERE FirstName ='Andrew' AND LastName ='Adams'; 
================================================== 

================================================== 
🔄 Node: execute_query 🔄 
- - - - - - - - - - - - - - - - - - - - - - - - - - - -  
================================= Tool Message ================================= 
Name: db_query_tool 

[(1,'Adams','Andrew','General Manager', None, '1962-02-18 00:00:00', '2002-08-14 00:00:00', '11120 Jasper Ave NW','Edmonton','AB','Canada', 
================================================== 
================================== Ai Message ================================== 

Answer: Andrew Adams' human information is: 

-Position: General Manager 
-Birth date: 1962-02-18 
-Hiring date: 2002-08-14 
-Address: 11120 Jasper Ave NW, Edmonton, AB, Canada, T5K 2N1 
-Phone number: +1 (780) 428-9482 
-Fax: +1 (780) 428-3457 
-Email: andrew@chinookcorp.com 

================================================== 
🔄 Node: query_gen 🔄 
- - - - - - - - - - - - - - - - - - - - - - - - - - - -  
================================== Ai Message ================================== 

Answer: Andrew Adams' human information is: 

-Position: General Manager 
-Birth date: 1962-02-18 
-Hiring date: 2002-08-14 
-Address: 11120 Jasper Ave NW, Edmonton, AB, Canada, T5K 2N1 
-Phone number: +1 (780) 428-9482 
-Fax: +1 (780) 428-3457 
-Email: andrew@chinookcorp.com 
================================================== 
```

```python
output = run_graph(
    "View all personal information for Andrew Adam's staff",
    stream=False,
)
```

```python
from langchain_core.runnables import RunnableConfig
from langchain_teddynote.messages import random_uuid, invoke_graph, stream_graph
from langchain_core.messages import HumanMessage
from langgraph.errors import GraphRecursionError


def run_graph(
    message: str, recursive_limit: int = 30, node_names=[], stream: bool = False
):
    # config settings (maximum number of recursion, thread_id)
    config = RunnableConfig(
        recursion_limit=recursive_limit, configurable={"thread_id": random_uuid()}
    )

    # Enter your question
    inputs = {
        "messages": [HumanMessage(content=message)],
    }

    try:
        if stream:
            # running the graph
            stream_graph(app, inputs, config, node_names=node_names)
        else:
            invoke_graph(app, inputs, config, node_names=node_names)
        output = app.get_state(config).values
        return output
    except GraphRecursionError as recursion_error:
        print(f"GraphRecursionError: {recursion_error}")
        output = app.get_state(config).values
        return output
```

```sql
================================================== 
🔄 Node: first_tool_call 🔄 
- - - - - - - - - - - - - - - - - - - - - - - - - - - -  
================================== Ai Message ================================== 
Tool Calls: 
  sql_db_list_tables (initial_tool_call_abc123) 
 Call ID: initial_tool_call_abc123 
  Args: 
================================================== 

================================================== 
🔄 Node: list_tables_tool 🔄 
- - - - - - - - - - - - - - - - - - - - - - - - - - - -  
================================= Tool Message ================================= 
Name: sql_db_list_tables 

Album, Artist, Customer, Employee, Genre, Invoice, InvoiceLine, MediaType, Playlist, PlaylistTrack, Track 
================================================== 

================================================== 
🔄 Node: model_get_schema 🔄 
- - - - - - - - - - - - - - - - - - - - - - - - - - - -  
================================== Ai Message ================================== 
Tool Calls: 
  sql_db_schema (call_o1gJuyskM6IxyFpp2yjmr8M4) 
 Call ID: call_o1gJuyskM6IxyFpp2yjmr8M4 
  Args: 
    table_names: Customer, Invoice 
================================================== 

================================================== 
🔄 Node: get_schema_tool 🔄 
- - - - - - - - - - - - - - - - - - - - - - - - - - - -  
================================= Tool Message ================================= 
Name: sql_db_schema 


CREATE TABLE "Customer" ( 
    "CustomerId" INTEGER NOT NULL,  
    "FirstName" NVARCHAR (40) NOT NULL,  
    "LastName" NVARCHAR (20) NOT NULL,  
    "Company" NVARCHAR (80),  
    "Address" NVARCHAR (70),  
    "City" NVARCHAR (40),  
    "State" NVARCHAR (40),  
    "Country" NVARCHAR (40),  
    "PostalCode" NVARCHAR (10),  
    "Phone" NVARCHAR (24),  
    "Fax" NVARCHAR (24),  
    "Email" NVARCHAR (60) NOT NULL,  
    "SupportRepId" INTEGER,  
    PRIMARY KEY ("CustomerId"),  
    FOREIGN KEY ("SupportRepId") REFERENCES "Employee" ("EmployeeId") 
) 

/* 
3 rows from Customer table: 
CustomerId FirstName LastName Company Address City State Country PostalCode Phone Fax Email SupportRepId 
1 Luís Gonçalves Embraer - Empresa Brasileira de Aeronáutica S.A.    Av. Brigadeiro Faria Lima, 2170 São José dos Campos SP Brazil 12227-000 +55 (12) 3923-5555 +55 (12) 3923-5566 luisg@embraer.com.br 3 
2 Leonie Köhler None Theodor-Heuss-Straße 34 Stuttgart None Germany 70174 +49 0711 2842222 None leonekohler@surfeu.de 5 
3 François Tremblay None 1498 rue Bélanger Montréal QC Canada H2G 1A7 +1 (514) 721-4711 None ftremblay@gmail.com 3 
*/ 


CREATE TABLE "Invoice" ( 
    "InvoiceId" INTEGER NOT NULL,  
    "CustomerId" INTEGER NOT NULL,  
    "InvoiceDate" DATETIME NOT NULL,  
    "BillingAddress" NVARCHAR (70),  
    "BillingCity" NVARCHAR (40),  
    "BillingState" NVARCHAR (40),  
    "BillingCountry" NVARCHAR (40),  
    "BillingPostalCode" NVARCHAR (10),  
    "Total" NUMERIC (10, 2) NOT NULL,  
    PRIMARY KEY ("InvoiceId"),  
    FOREIGN KEY ("CustomerId") REFERENCES "Customer" ("CustomerId") 
) 

/* 
3 rows from Invoice table: 
InvoiceId CustomerId InvoiceDate BillingAddress BillingCity BillingState BillingCountry BillingPostalCode Total 
1 2 2009-01-01 00:00:00 Theodor-Heuss-Straße 34 Stuttgart None Germany 70174 1.98 
2 4 2009-01-02 00:00:00 Ullevålsveien 14 Oslo None Norway 0171 3.96 
3 8 2009-01-03 00:00:00 Grétrystraat 63 Brussels None Belgium 1000 5.94 
*/ 
================================================== 
================================== Ai Message ================================== 
Tool Calls: 
  model_check_query (call_nKKhCLjOm713LvqkMSqtn2xI) 
 Call ID: call_nKKhCLjOm713LvqkMSqtn2xI 
  Args: 
    state: {'messages': [{'content': 'Which country's customers spent the most in 2009? And how much did you spend? 'Request':'human'}, {'content':'Album, Artist, Customer, Employee, Genre, Invoice, InvoiceLine, MediaType, Playlist, PlaylistTrack, Track','type':'function', 'name'  
    (...sway...) 
    table:\nInvoiceId\tInvoiceDate\tBillingAddress\tBillingCity\tBillingCountry\tBillingPostalCode\tTotal\n1\t2009\t200\t200sql_db_schema'}]} 

================================================== 
🔄 Node: query_gen 🔄 
- - - - - - - - - - - - - - - - - - - - - - - - - - - -  
================================== Ai Message ================================== 
Tool Calls: 
  model_check_query (call_nKKhCLjOm713LvqkMSqtn2xI) 
 Call ID: call_nKKhCLjOm713LvqkMSqtn2xI 
  Args: 
    state: {'messages': [{'content': 'Which country's customers spent the most in 2009? And how much did you spend? NUL','type':'TAG1>, }'content':'Album, Artist, Customer, Employee, Genre, Invoice, InvoiceLine, MediaType, Playlist, PlaylistTrack, Track','type':'function','name'City" NVARCHAR(40), \n\t" State" NVARCHAR(40), \n\t" Country" NVARCHAR(40), \n\t"PostalCode" NVARCHAR(10), \n\t"Phone" NVARCHAR(24), \n\t"  Fax" NVARCHAR(24), \n\t"Email" NVARCHAR(60) NOT NULL, \n\t"SupportRepId" INTEGER, \n\tPRIMARY KEY ("CustomerId"), \n\tFOREIGN KEY("SupportRep  
    (...sway...) 
    table:\nInvoiceId\tInvoiceDate\tBillingAddress\tBillingCity\tBillingCountry\tBillingPostalCode\tTotal\n1\t2009\t200\t200sql_db_schema'}]} 
================================= Tool Message ================================= 

Error: The wrong tool was cled: model_check_query. Please fix your mistakes. Remember to only call SubmitFinalAnswer to submit the final answer. Generated queries should be outputted WITHOUT a tool call. 
================================================== 
================================== Ai Message ================================== 


SELECT c.Country, SUM(i.Total) AS TotalSpentFROM Customer cJOIN Invoice i ON c.CustomerId = i.CustomerIdWHERE strftime('%Y', i.InvoiceDate) = '2009'GROUP BY c.CountryORDER BY TotalSpent DESCLIMIT 1;


================================================== 
🔄 Node: query_gen 🔄 
- - - - - - - - - - - - - - - - - - - - - - - - - - - -  
================================== Ai Message ================================== 


SELECT c.Country, SUM(i.Total) AS TotalSpentFROM Customer cJOIN Invoice i ON c.CustomerId = i.CustomerIdWHERE strftime('%Y', i.InvoiceDate) = '2009'GROUP BY c.CountryORDER BY TotalSpent DESCLIMIT 1;

================================================== 

================================================== 
🔄 Node: correct_query 🔄 
- - - - - - - - - - - - - - - - - - - - - - - - - - - -  
================================== Ai Message ================================== 
Tool Calls: 
  db_query_tool (call_uugf85ZxwGL9mkEBJetSr3Cr) 
 Call ID: call_uugf85ZxwGL9mkEBJetSr3Cr 
  Args: 
    query: SELECT c.Country, SUM(i.Total) AS TotalSpent 
FROM Customer c 
JOIN Invoice i ON c.CustomerId = i.CustomerId 
WHERE strftime ('%Y', i.InvoiceDate) = '2009' 
GROUP BY c.Country 
ORDER BY TotalSpent DESC 
LIMIT 1; 
================================================== 

================================================== 
🔄 Node: execute_query 🔄 
- - - - - - - - - - - - - - - - - - - - - - - - - - - -  
================================= Tool Message ================================= 
Name: db_query_tool 

[('USA', 103.95)] 
================================================== 
================================== Ai Message ================================== 

Answer: In 2009, US (USA) customers spent the most, and the total expenditure was 103.95. 

================================================== 
🔄 Node: query_gen 🔄 
- - - - - - - - - - - - - - - - - - - - - - - - - - - -  
================================== Ai Message ================================== 

Answer: In 2009, US (USA) customers spent the most, and the total expenditure was 103.95. 
================================================== 
```

SQL Agent evaluation using LangSmith Evaluator

Now evaluate the SQL query response of the Agent you created. Generate a data set for evaluation to evaluate query responses.

Next, define the evaluator and proceed with the evaluation.

The evaluators utilized at this time are LLM-as-judge, and the prompts used utilize the prompts provided by the default hub.

However, it is recommended to tune and use each prompt for a more accurate evaluation.

```python
from langsmith import Client

# client initialization
client = Client()

# create and uplaod a dataset
examples = [
    (
        "Which country's customers spent the most? And how much did they spend?",
        "The country whose customers spent the most is the USA, with a total spending of 523.06.",
    ),
    (
        "What was the most purchased track of 2013?",
        "The most purchased track of 2013 was Hot Girl.",
    ),
    (
        "How many albums does the artist Led Zeppelin have?",
        "Led Zeppelin has 14 albums",
    ),
    (
        "What is the total price for the album “Big Ones”?",
        "The total price for the album 'Big Ones' is 14.85",
    ),
    (
        "Which sales agent made the most in sales in 2009?",
        "Steve Johnson made the most sales in 2009",
    ),
]

dataset_name = "SQL Agent Response"

if not client.has_dataset(dataset_name=dataset_name):
    dataset = client.create_dataset(dataset_name=dataset_name)
    inputs, outputs = zip(
        *[({"input": text}, {"output": label}) for text, label in examples]
    )
    client.create_examples(inputs=inputs, outputs=outputs, dataset_id=dataset.id)
```

The following defines the function to predict the SQL query response of the agent we created.

```python
# Define a function to predict the agent's SQL query response.
def predict_sql_agent_answer(example: dict):
    """Use this for answer evaluation"""
    config = RunnableConfig(configurable={"thread_id": random_uuid()})

    inputs = {
        "messages": [HumanMessage(content=example["input"])],
    }
    # Run the graph to view message results
    messages = app.invoke(inputs, config)
    answer = messages["messages"][-1].content
    # return results
    return {"response": answer}
```

Defines the prompt and evaluator (LLM-as-judge) to evaluate SQL query responses.

```python
from langchain import hub
from langchain_openai import ChatOpenAI

# Grade prompt
grade_prompt_answer_accuracy = hub.pull("langchain-ai/rag-answer-vs-reference")


# Answer Evaluator LLM-as-judge Definition
def answer_evaluator(run, example) -> dict:
    # input: question
    input_question = example.inputs["input"]
    # output: reference answer
    reference = example.outputs["output"]
    # predictive answer
    prediction = run.outputs["response"]

    # LLM Assessor Reset
    llm = ChatOpenAI(model=MODEL_NAME, temperature=0)
    answer_grader = grade_prompt_answer_accuracy | llm

    # run the evaluation
    score = answer_grader.invoke(
        {
            "question": input_question,
            "correct_answer": reference,
            "student_answer": prediction,
        }
    )
    score = score["Score"]

    # return score
    return {"key": "answer_v_reference_score", "score": score}
```

Now, conduct an assessment and confirm the results.

```python
from langsmith.evaluation import evaluate

# name of the dataswet for evaluation
dataset_name = "SQL Agent Response"

try:
    # evaluation in progress
    experiment_results = evaluate(
        predict_sql_agent_answer,  # prediction function to uswe in evaluation
        data=dataset_name,  # Name of the dataset for evaluation
        evaluators=[answer_evaluator],  # liswt of reviewers
        num_repetitions=3,  # Set the number of repetitions of the experiment
        experiment_prefix="sql-agent-eval",
        metadata={"version": "chinook db, sql-agent-eval: gpt-4o"},  # Experimental metadata
    )
except Exception as e:
    print(e)
```

```
 View the evaluation results for expert:'sql-agent-eval-2f44937f' at: 
https://smith.langchain.com/o/42ebd69b-2565-441c-b868-9709c2e20267/datasets/3e801af3-038d-445d-887b-379a30a14939/compare?selectedSessions=6cc0987a-e16f-475c-b574-36c83aa19f2c 


```

```
 0it [00:00, ?it/s] 
```

```sql
 ================================== Ai Message ================================== 
Tool Calls: 
  model_check_query (call_5XnBKYwbPqbktaHqDdZ8gQG4) 
 Call ID: call_5XnBKYwbPqbktaHqDdZ8gQG4 
  Args: 
    state: {'messages': [{'content': "SELECT e.FirstName, e.LastName, SUM(i.Total) as TotalSales FROM Employee e JOIN Customer c ON e.EmployeeId = c.SupportRep 
================================== Ai Message ================================== 
Tool Calls: 
  model_check_query (call_oUXAuRH6WDE6NtGzS9F8NYWm) 
 Call ID: call_oUXAuRH6WDE6NtGzS9F8NYWm 
  Args: 
    state:'TagG1>'messages':'{'content':'Which sales agent made the most in sales in 2009?','type':'human'{, }'content':'Album, Artist, Customer, Employee, Genre 
================================== Ai Message ================================== 


SELECT e.FirstName, e.LastName, SUM(i.Total) AS TotalSalesFROM Employee eJOIN Customer c ON e.EmployeeId = c.SupportRepIdJOIN Invoice i ON c.CustomerId = i.CustomerIdWHERE strftime('%Y', i.InvoiceDate) = '2009'GROUP BY e.EmployeeIdORDER BY TotalSales DESCLIMIT 1;

================================== Ai Message ================================== 

SELECT e.FirstName, e.LastName, SUM (i.Total) as TotalSales  
FROM Employee e  
JOIN Customer c ON e.EmployeeId = c.SupportRepId  
JOIN Invoice i ON c.CustomerId = i.CustomerId  
WHERE strftime ('%Y', i.InvoiceDate) = '2009'  
GROUP BY e.EmployeeId  
ORDER BY TotalSales DESC  
LIMIT 1; 
================================== Ai Message ================================== 
Tool Calls: 
  model_check_query (call_NVlHV7r4RoqyaualVfIagTSW) 
 Call ID: call_NVlHV7r4RoqyaualVfIagTSW 
  Args: 
    state: {'messages':'How many albums does the artist Led Zeppelin have?','type':'human'{,'Album, Artist, Customer, Employee, Genren\tFOREIGN KEY("ArtistId") REFERENCES "Artist" ("ArtistId")\n)\n\n/*\n3 rows from Album table:  \nAlbumId\tTitle\tArtistId\n1\tFor That About To Rock We Salute You\t1\n2\tBalls to the Wall\t2\n3\tRestless and Wild\t2\n*/\n\n\nCREATE TABLE "Artist"type':'function','name':'sql_db_schema'}]} 
================================== Ai Message ================================== 
Tool Calls: 
  model_check_query (call_urK1QsY7wgMQhrKasG3qXjFh) 
 Call ID: call_urK1QsY7wgMQhrKasG3qXjFh 
  Args: 
    state: {'messages':'How many albums does the artist Led Zeppelin have?','type':'human'{,'Album, Artist, Customer, Employee, Genren\tFOREIGN KEY("ArtistId") REFERENCES "Artist" ("ArtistId")\n)\n\n/*\n3 rows from Album table:  \nAlbumId\tTitle\tArtistId\n1\tFor That About To Rock We Salute You\t1\n2\tBalls to the Wall\t2\n3\tRestless and Wild\t2\n*/\n\n\nCREATE TABLE "Artist"type':'function','name':'sql_db_schema'}]} 
================================== Ai Message ================================== 
Tool Calls: 
  model_check_query (call_dh9fi3gIxYV9BEKWsGPdoLpj) 
 Call ID: call_dh9fi3gIxYV9BEKWsGPdoLpj 
  Args: 
    state: {'messages':'How many albums does the artist Led Zeppelin have?','type':'human'{,'Album, Artist, Customer, Employee, Genren\tFOREIGN KEY("ArtistId") REFERENCES "Artist" ("ArtistId")\n)\n\n/*\n3 rows from Album table:  \nAlbumId\tTitle\tArtistId\n1\tFor That About To Rock We Salute You\t1\n2\tBalls to the Wall\t2\n3\tRestless and Wild\t2\n*/\n\n\nCREATE TABLE "Artist"type':'function','name':'sql_db_schema'}]} 
================================== Ai Message ================================== 


SELECT COUNT(*) FROM Album JOIN Artist ON Album.ArtistId = Artist.ArtistId WHERE Artist.Name = 'Led Zeppelin';

================================== Ai Message ================================== 

Answer: The sales agent who made the most in sales in 2009 is Steve Johnson with total sales of 164.34. 
================================== Ai Message ================================== 


SELECT COUNT(*) FROM Album JOIN Artist ON Album.ArtistId = Artist.ArtistId WHERE Artist.Name = 'Led Zeppelin';

================================== Ai Message ================================== 


SELECT COUNT(*) FROM Album JOIN Artist ON Album.ArtistId = Artist.ArtistId WHERE Artist.Name = 'Led Zeppelin';

================================== Ai Message ================================== 

Answer: The sales agent who made the most in sales in 2009 is Steve Johnson with a total of $164.34. 
================================== Ai Message ================================== 
Tool Calls: 
  model_check_query (call_LEgQpLMGNaDhsOvVKH37k79m) 
 Call ID: call_LEgQpLMGNaDhsOvVKH37k79m 
  Args: 
    state:'TAG1>'messages': [{'content':'What is the total price for the album { Big Ones“?','type':'human'”, }'content':'Album, Artist, Customer,  
    (...sway...) 
    ("InvoiceLineId"), \n\tFOREIGN KEY("TrackId") REFERENCES "Track" ("TrackId"), \n\tFOREIGN KEY ("Invoice\n") 
================================== Ai Message ================================== 
Tool Calls: 
  model_check_query (call_r3302vpy5ndcVHTOx9Zev6zs) 
 Call ID: call_r3302vpy5ndcVHTOx9Zev6zs 
  Args: 
    state:'TAG1>'messages':'What is the total price for the album { Big Ones{?','type':'human'“,'TAG1>'  
    (...sway...) 
    \n\t"UnitPrice" NUMERIC (10, 2) NOT NULL, \n\t"Quantity" INTEGER NOT NULL, \n\tPRIMARY KEY ("InvoiceLineId"), \n\tFOREIGNKEY ("TrackId") REFERENCt2\t6\t0.99\t1\n*/','type':'function','name':'sql_db_schema'}]} 
================================== Ai Message ================================== 

Answer: Led Zeppelin has 14 albums. 
================================== Ai Message ================================== 

Answer: Led Zeppelin has 14 albums. 
================================== Ai Message ================================== 

Answer: Led Zeppelin has 14 albums. 
================================== Ai Message ================================== 


SELECT SUM(InvoiceLine.UnitPrice * InvoiceLine.Quantity) AS TotalPriceFROM AlbumJOIN Track ON Album.AlbumId = Track.AlbumIdJOIN InvoiceLine ON Track.TrackId = InvoiceLine.TrackIdWHERE Album.Title = 'Big Ones';

================================== Ai Message ================================== 
Tool Calls: 
  model_check_query (call_D7AbojfxcmXZ5dkxrJgT8hwa) 
 Call ID: call_D7AbojfxcmXZ5dkxrJgT8hwa 
  Args: 
    state: {'messages': [{'content': "Which country's customers spent the most? And how much did they send?",'type':'human'}, {'content':'Album, Artist, Customer, Employee, Genre, Invoice, InvoiceLine, MediaType, Playlist, PlaylistTrack, Track', 'type':namefunction:  
    (...sway...) 
    table:\nInvoiceId\tInvoiceDate\tBillingAddress\tBillingCity\tBillingCountry\tBillingPostalCode\tTotal\n1\t2009\t200\t200sql_db_schema'}]} 
================================== Ai Message ================================== 
Tool Calls: 
  model_check_query (call_lFJl9jvKDaaZVuxsNLP0wUXk) 
 Call ID: call_lFJl9jvKDaaZVuxsNLP0wUXk 
  Args: 
    state:'TAG1>'messages': [{'content':'What is the total price for the album { Big Ones“?','type':'human'”, }'content':'Album, Artist, Customer,  
    (...sway...) 
    \n\tFOREIGN KEY("InvoiceId") REFERENCES "Invoice" ("InvoiceId")\n)\n/*\n3 rows from InvoiceLine table:\nInvoiceLineId\tInvoiceId\tUnitPrice\tant 
================================== Ai Message ================================== 
Tool Calls: 
  model_check_query (call_XqY2Ih4NcvF1sSKHzhOdFF7b) 
 Call ID: call_XqY2Ih4NcvF1sSKHzhOdFF7b 
  Args: 
    state: {'messages': [{'content':'What was the most purchased track of 2013?','type':'human'},'CREATE TABLE "Invoice" (\n\t"n\t"BillingPostalCode" NVARCHAR(10), \n\t" Total" NUMERIC(10, 2) NOT NULL, \n\tPRIMARY KEY ("InvoiceId"), \n\tFOREIGN KEY ("CustomerId") REFERENCES  
    (...sway...) 
    table:\nTrackId\tName\tAlbumId\tComposer\tMilliseconds\tBytes\tUnitPrice\n1\tFor That About To Rock (We Salute You)\t1\t1\t1\tAngus Young, Malcolm Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman\t230619\t3990994\t0.99\n]','type':'ai'}]} 
================================== Ai Message ================================== 
Tool Calls: 
  model_check_query (call_wRjsL2MKclHQfJInY4rz0YI) 
 Call ID: call_wRjsL2MKclHQfJInY4rz0YI 
  Args: 
    state: {'messages': [{'content': "Which country's customers spent the most? And how much did they send?",'type':'human'}, {'content':'Album, Artist, Customer, Employee, Genre, Invoice, InvoiceLine, MediaType, PlaylistTrack, Track','type':n NVARCHAR (70), \n\t" City" NVARCHAR (40), \n\t" State" NVARCHAR (40), \n\t"Country" NVARCHAR (40), \n\t"PostalCode" NVARCHAR (10), \n\t"Phone" NVAR  \n\t" Fax" NVARCHAR(24), \n\t"Email" NVARCHAR(60) NOT NULL, \n\t"SupportRepId" INTEGER, \n\tPRIMARY KEY ("CustomerId"), \n\tFOREIGN KEY(  
    (...sway...) 
    table:\nInvoiceId\tInvoiceDate\tBillingAddress\tBillingCity\tBillingCountry\tBillingPostalCode\tTotal\n1\t2009\t200\t200sql_db_schema'}]} 
================================== Ai Message ================================== 


SELECT SUM(Invoice.Total) AS TotalPriceFROM AlbumJOIN Track ON Album.AlbumId = Track.AlbumIdJOIN InvoiceLine ON Track.TrackId = InvoiceLine.TrackIdJOIN Invoice ON InvoiceLine.InvoiceId = Invoice.InvoiceIdWHERE Album.Title = 'Big Ones';

================================== Ai Message ================================== 
Tool Calls: 
  model_check_query (call_nAcQGCnL0IIstzNY1qGOZUw0) 
 Call ID: call_nAcQGCnL0IIstzNY1qGOZUw0 
  Args: 
    state:'{'messages':'What was the most purchased track of 2013?','type':'human'{,'Album, Artist, Customer, Employee, Genre, NVARCHAR (40), \n\t"BillingState" NVARCHAR (40), \n\t"BillingCountry" NVARCHAR (40), \n\t"BillingPostalCode" NVARCHAR (10), \n\t"Total" NUMERIC (10, 2) NOT  InvoiceId"), \n\tFOREIGN KEY ("CustomerId") REFERENCES "Customer" ("CustomerId")\n)\n\n/*\n3  
    (...sway...) 
    table:\nTrackId\tName\tAlbumId\tComposer\tMilliseconds\tBytes\tUnitPrice\n1\tFor That About To Rock (We Salute You)\t1\t1\t1\tAngus Young, Malcolm Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman\t230619\t3990994\t0.99\n*/','type':'function','name':'sql_db_schema'}]} 
================================== Ai Message ================================== 
Tool Calls: 
  model_check_query (call_hQNm0vVAFZCyl16igsRctogd) 
 Call ID: call_hQNm0vVAFZCyl16igsRctogd 
  Args: 
    state: {'messages': [{'content': "Which country's customers spent the most? And how much did they send?",'type':'human'}, {'content':'Album, Artist, Customer, Employee, Genre, Invoice, InvoiceLine, MediaType, PlaylistTrack, Track','type':n NVARCHAR (70), \n\t" City" NVARCHAR (40), \n\t" State" NVARCHAR (40), \n\t"Country" NVARCHAR (40), \n\t"PostalCode" NVARCHAR (10), \n\t"Phone" NVAR  \n\t" Fax" NVARCHAR(24), \n\t"Email" NVARCHAR(60) NOT NULL, \n\t"SupportRepId" INTEGER, \n\tPRIMARY KEY  
    (...sway...) 
    table:\nInvoiceId\tInvoiceDate\tBillingAddress\tBillingCity\tBillingCountry\tBillingPostalCode\tTotal\n1\t2009\t200\t200sql_db_schema'}]} 
================================== Ai Message ================================== 
Tool Calls: 
  model_check_query (call_80FZIrpx0dPg8lIyBwgJ1cxc) 
 Call ID: call_80FZIrpx0dPg8lIyBwgJ1cxc 
  Args: 
    'Nvoice','Nvoice','Nvoice','EBillingAddress" NVARCHAR(70), \n\t"BillingCity" NVARCHAR(40), \n\t"BillingState" NVARCHAR(40), \n\t"BillingCountry" NVARCHAR(40), \n\t"BillingPostalCode"  NVARCHAR (10), \n\t" Total" NUMERIC (10, 2) NOT NULL, \n\tPRIMARY KEY ("InvoiceId"), \n\tFOREIGN KEY ("CustomerId") REFERENCES "Customer" ("Custom  
    (...sway...) 
    table:\nTrackId\tName\tAlbumId\tComposer\tMilliseconds\tBytes\tUnitPrice\n1\tFor That About To Rock (We Salute You)\t1\t1\t1\tAngus Young, Malcolm Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman\t230619\t3990994\t0.99\n*/','type':'function','name':'sql_db_schema'}]} 
================================== Ai Message ================================== 


SELECT c.Country, SUM(i.Total) AS TotalSpentFROM Customer cJOIN Invoice i ON c.CustomerId = i.CustomerIdGROUP BY c.CountryORDER BY TotalSpent DESCLIMIT 1;

================================== Ai Message ================================== 


SELECT SUM(Invoice.Total) AS TotalPriceFROM AlbumJOIN Track ON Album.AlbumId = Track.AlbumIdJOIN InvoiceLine ON Track.TrackId = InvoiceLine.TrackIdJOIN Invoice ON InvoiceLine.InvoiceId = Invoice.InvoiceIdWHERE Album.Title = 'Big Ones';

================================== Ai Message ================================== 


SELECT c.Country, SUM(i.Total) AS TotalSpentFROM Customer cJOIN Invoice i ON c.CustomerId = i.CustomerIdGROUP BY c.CountryORDER BY TotalSpent DESCLIMIT 1;

================================== Ai Message ================================== 

Answer: The total price for the album “Big Ones” is 9.9. 
================================== Ai Message ================================== 


SELECT t.Name, SUM(il.Quantity) AS TotalPurchasesFROM Track tJOIN InvoiceLine il ON t.TrackId = il.TrackIdJOIN Invoice i ON il.InvoiceId = i.InvoiceIdWHERE strftime('%Y', i.InvoiceDate) = '2013'GROUP BY t.TrackIdORDER BY TotalPurchases DESCLIMIT 1;

================================== Ai Message ================================== 


SELECT c.Country, SUM(i.Total) AS TotalSpentFROM Customer cJOIN Invoice i ON c.CustomerId = i.CustomerIdGROUP BY c.CountryORDER BY TotalSpent DESCLIMIT 1;

================================== Ai Message ================================== 
Tool Calls: 
  model_check_query (call_tyWKRw7J30PswHjcAmiKPW4Q) 
 Call ID: call_tyWKRw7J30PswHjcAmiKPW4Q 
  Args: 
    'Ton':'TonG1>'messages':'Which sales agent made the most in sales in 2009?','type':'human'{,'Album, Artist, Customer, Employee NVARCHAR(20) NOT NULL, \n\t"FirstName" NVARCHAR(20) NOT NULL, \n\t"Title" NVARCHAR(30), \n\t"ReportsTo" INTEGER, \n\t" BirthDate" DATETIME, \n\t  HireDate" DATETIME, \n\t"Address" NVARCHAR(70), \n\t"City" NVARCHAR(40), \n\t"State" NVARCHAR(40), \n\t"Country" NVARCHAR(40), \n\t"PostalCode) REFERENCES "Employee" ("EmployeeId")\n)\n\n/*\n3 rows from Employee   \n\tFOREIGN KEY ("ReportsTo") REFERENCES "Employee" ("EmployeeId")\n)\n\n/*\n3 rows from Employee   \n\tFOREIGN KEY ("ReportsTo") REFERENCES "Employee" ("EmployeeId")\n)\n\n/*\n3 rows from Employee  
    (...sway...) 
    table:\nInvoiceId\tInvoiceDate\tBillingAddress\tBillingCity\tBillingCountry\tBillingPostalCode\tTotal\n1\t2009\t200\t200sql_db_schema'}]} 
================================== Ai Message ================================== 

SELECT t.Name, SUM (il.Quantity) AS TotalQuantity 
FROM Track t 
JOIN InvoiceLine il ON t.TrackId = il.TrackId 
JOIN Invoice i ON il.InvoiceId = i.InvoiceId 
WHERE strftime ('%Y', i.InvoiceDate) = '2013' 
GROUP BY t.TrackId 
ORDER BY TotalQuantity DESC 
LIMIT 1; 
================================== Ai Message ================================== 


SELECT t.Name, SUM(il.Quantity) AS TotalQuantityFROM InvoiceLine ilJOIN Invoice i ON il.InvoiceId = i.InvoiceIdJOIN Track t ON il.TrackId = t.TrackIdWHERE strftime('%Y', i.InvoiceDate) = '2013'GROUP BY t.NameORDER BY TotalQuantity DESCLIMIT 1;

================================== Ai Message ================================== 

Answer: The total price for the album “Big Ones” is 82.17. 
================================== Ai Message ================================== 

Answer: The country white customers spent the most is the USA, with a total speaking of 523.06. 
================================== Ai Message ================================== 


SELECT e.FirstName, e.LastName, SUM(i.Total) as TotalSalesFROM Employee eJOIN Customer c ON e.EmployeeId = c.SupportRepIdJOIN Invoice i ON c.CustomerId = i.CustomerIdWHERE strftime('%Y', i.InvoiceDate) = '2009'GROUP BY e.EmployeeIdORDER BY TotalSales DESCLIMIT 1;

================================== Ai Message ================================== 

Answer: The country white customers spent the most is the USA, with a total speaking of 523.06. 
================================== Ai Message ================================== 

Answer: The total price for the album “Big Ones” is 82.17. 
================================== Ai Message ================================== 

Answer: The country's customers who sent the most are from the USA, with a total speaking of 523.06. 
================================== Ai Message ================================== 

Answer: The most purchased track of 2013 was "Hot Girl" with 1 purchase. 
================================== Ai Message ================================== 

Answer: The most purchased track of 2013 was "Where Eagles Dare". 
================================== Ai Message ================================== 

Answer: The most purchased track of 2013 was "Hot Girl". 
================================== Ai Message ================================== 

Answer: The sales agent who made the most in sales in 2009 is Steve Johnson with total sales of 164.34. 
```

The evaluation results can be checked at the URL created.

<br>