Introduction

Text to SQL is a natural language processing task that converts human-readable text queries into structured SQL queries. This lets users interact with databases using natural language.

Text to SQL is valuable for several reasons:

1. Accessibility: Non-technical users can query databases without knowing SQL syntax, making data access easier within organizations.

1. Efficiency: Data analysts and scientists can quickly prototype queries using natural language.

1. Integration: It enables more intuitive interfaces for database interactions in applications and chatbots.

1. Complex Query Generation: LLMs can generate complex SQL queries involving multiple joins, subqueries, and aggregations, which can be time-consuming for humans to write.

#What This Guide Covers

QL Prompt

Improving the Prompt with Examples

Using Chain-of-Thought Prompting

Implementing RAG for Complex Database Schemas

Implementing Query Self-Improvement

Evaluations

Further Exploration & Next Steps

#Setup

quot;Finance", "Boston"),

(6, "Customer Support", "Dallas"),

(7, "Research", "Seattle"),

(8, "Legal", "Washington D.C."),

(9, "Product", "Austin"),

(10, "Operations", "Denver"),

],

)

first_names = [

"John",

"Jane",

"Bob",

"Alice",

"Charlie",

"Diana",

"Edward",

"Fiona",

"George",

"Hannah",

"Ian",

"Julia",

"Kevin",

"Laura",

"Michael",

"Nora",

"Oliver",

"Patricia",

"Quentin",

"Rachel",

"Steve",

"Tina",

"Ulysses",

"Victoria",

"William",

"Xena",

"Yannick",

"Zoe",

]

last_names = [

"Smith",

"Johnson",

"Williams",

"Jones",

"Brown",

"Davis",

"Miller",

"Wilson",

"Moore",

"Taylor",

"Anderson",

"Thomas",

"Jackson",

"White",

"Harris",

"Martin",

"Thompson",

"Garcia",

"Martinez",

"Robinson",

"Clark",

"Rodriguez",

"Lewis",

"Lee",

"Walker",

"Hall",

"Allen",

"Young",

"King",

]

employees_data = []

for i in range(1, 201): # Generate 200 employees

name = f"{random.choice(first_names)} {random.choice(last_names)}"

age = random.randint(22, 65)

department_id = random.randint(1, 10)

salary = round(random.uniform(40000, 200000), 2)

hire_date = (datetime.now() - timedelta(days=random.randint(0, 3650))).strftime(

"%Y-%m-%d"

)

employees_data.append((i, name, age, department_id, salary, hire_date))

cursor.executemany("INSERT INTO employees VALUES (?,?,?,?,?,?)", employees_data)

print("Database created and populated successfully.")

else:

print("Database already exists. Skipping creation and population.")

Display table contents

with sqlite3.connect(DATABASE_PATH) as conn:

for table in ["departments", "employees"]:

df = pd.read_sql_query(f"SELECT * FROM {table}", conn)

print(f"\n{table.capitalize()} table:")

display(df)



Database already exists. Skipping creation and population. Departments table: id name location 0 1 HR New York 1 2 Engineering San Francisco 2 3 Marketing Chicago 3 4 Sales Los Angeles 4 5 Finance Boston 5 6 Customer Support Dallas 6 7 Research Seattle 7 8 Legal Washington D.C. 8 9 Product Austin 9 10 Operations Denver Employees table: id name age department_id salary hire_date 0 1 Michael Allen 57 9 151012.98 2016-02-04 1 2 Nora Hall 23 8 186548.83 2018-01-27 2 3 Patricia Miller 49 5 43540.04 2020-06-07 3 4 Alice Martinez 48 7 131993.17 2021-01-21 4 5 Patricia Walker 59 5 167151.15 2020-05-24 .. ... ... ... ... ... ... 195 196 Hannah Clark 31 10 195944.00 2017-11-08 196 197 Alice Davis 46 5 145584.16 2022-02-13 197 198 Charlie Hall 37 1 53690.40 2024-06-18 198 199 Alice Garcia 50 5 92372.26 2024-02-01 199 200 Laura Young 25 9 64738.56 2015-08-02 [200 rows x 6 columns] Creating a Basic Text to SQL Prompt Now that we have our database set up, let's create a basic prompt for Text to SQL conversion. A good prompt should include:

= 2022;

"""

return f"""You are an AI assistant that converts natural language queries into SQL.

Given the following SQL database schema:

{schema}

Here are some examples of natural language queries, thought processes, and their corresponding SQL:

{examples}

Now, convert the following natural language query into SQL:

{query}

Within tags, explain your thought process for creating the SQL query.

Then, within tags, provide your output SQL query.

"""

Test the new prompt

user_query = "What are the names and hire dates of employees in the Engineering department, ordered by their salary?"

prompt = generate_prompt_with_cot(schema, user_query)

print(prompt)



You are an AI assistant that converts natural language queries into SQL. Given the following SQL database schema: Table: departments - id (INTEGER) - name (TEXT) - location (TEXT) Table: employees - id (INTEGER) - name (TEXT) - age (INTEGER) - department_id (INTEGER) - salary (REAL) - hire_date (DATE) Here are some examples of natural language queries, thought processes, and their corresponding SQL: List all employees in the HR department. 1. We need to join the employees and departments tables. 2. We'll match employees.department_id with departments.id. 3. We'll filter for the HR department. 4. We only need to return the employee names. SELECT e.name FROM employees e JOIN departments d ON e.department_id = d.id WHERE d.name = 'HR'; What is the average salary of employees hired in 2022? 1. We need to work with the employees table. 2. We need to filter for employees hired in 2022. 3. We'll use the YEAR function to extract the year from the hire_date. 4. We'll calculate the average of the salary column for the filtered rows. SELECT AVG(salary) FROM employees WHERE YEAR(hire_date) = 2022; Now, convert the following natural language query into SQL: What are the names and hire dates of employees in the Engineering department, ordered by their salary? Within tags, explain your thought process for creating the SQL query. Then, within tags, provide your output SQL query. Now let's use this chain-of-thought prompt with XML tags to generate SQL: