task_categories:text-generation task_categories:question-answering task_categories:table-question-answering size_categories:10K<n<100K language:en license:cc-by-4.0 SQL code NLP text-to-sql context-sql spider wikisql sqlglot region:us
---
license: cc-by-4.0
task_categories:
- text-generation
- question-answering
- table-question-answering
language:
- en
tags:
- SQL
- code
- NLP
- text-to-sql
- context-sql
- spider
- wikisql
- sqlglot
pretty_name: sql-create-context
size_categories:
- 10K<n<100K
---

## Overview
This dataset is built upon [SQL Create Context](https://huggingface.co/datasets/b-mc2/sql-create-context), which in turn was constructed using data from [WikiSQL](https://huggingface.co/datasets/wikisql) and [Spider](https://huggingface.co/datasets/spider).

There are 78,577 examples of natural language queries, SQL CREATE TABLE statements, and SQL Query answering the question using the CREATE statement as context. This dataset was built with text-to-SQL LLMs in mind, intending to prevent hallucination of column and table names often seen when trained on text-to-SQL datasets. The CREATE TABLE statement can often be copy and pasted from different DBMS and provides table names, column names, and their data types. By providing just the CREATE TABLE statement as context, we can hopefully provide better grounding for models without having to provide actual rows of data, limiting token usage and exposure to private, sensitive, or proprietary data.

## Cleansing and Augmentation
Cleansing and data augmentation have been done on the combined WikiSQL and Spider data. [SQLGlot](https://github.com/tobymao/sqlglot) was used on queries from Spider and WikiSQL to parse them into different tables and columns. Column data types were inferred based on the usage of `>` `<` operators as well as the use of `MIN()`, `MAX()`, `AVG()`, and `SUM()` on columns. While this isn't perfect, it increases the likelihood of inferring the correct datatype for a column; otherwise, columns default to VARCHAR type. These tables and columns are then used to generate CREATE TABLE statements using the inferred types. SQLGlot is used again to ensure both the SQL queries and CREATE TABLE statements parse without errors.

Some queries that do not have column names, e.g., SELECT * FROM table, have a default Id column added to the CREATE TABLE statement. Some other queries which use the generic `table` as the FROM table have instead been changed to a variation of `table_name_1` or some other number which is also reflected in the CREATE TABLE statement.

## TODO
- Further augment the data by converting queries and CREATE TABLE statements into different SQL dialects. This can be done with SQLGlot. Reference to the dialect might also be added to the question.

**Random sample:**
```json
{
  "text" : "[INST] Write SQLite query to answer the following question given the database schema. Please wrap your code answer using ```: Schema: CREATE TABLE head (age INTEGER) Question: How many heads of the departments are older than 56 ? [/INST] Here is the SQLite query to answer to the question: How many heads of the departments are older than 56 ?: ``` SELECT COUNT(*) FROM head WHERE age > 56 ```", 
  "text" : "[INST] Write SQLite query to answer the following question given the database schema. Please wrap your code answer using ```: Schema: CREATE TABLE head (name VARCHAR, born_state VARCHAR, age VARCHAR) Question: List the name, born state and age of the heads of departments ordered by age. [/INST] Here is the SQLite query to answer to the question: List the name, born state and age of the heads of departments ordered by age.: ``` SELECT name, born_state, age FROM head ORDER BY age ```"
}
```

The dataset was used to create code-llama-2 style prompts. The basic prompt template is:
```
[INST] Instruction/context [/INST]
Model output
```