lianghsun/bird-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE address_status
(
    status_id      INTEGER
            primary key,
    address_status TEXT
)

CREATE TABLE author
(
    author_id   INTEGER
            primary key,
    author_name TEXT
)

CREATE TABLE book_language
(
    language_id   INTEGER
            primary key,
    language_code TEXT,
    language_name TEXT
)

CREATE TABLE country
(
    country_id   INTEGER
            primary key,
    country_name TEXT
)

CREATE TABLE address
(
    address_id    INTEGER
            primary key,
    street_number TEXT,
    street_name   TEXT,
    city          TEXT,
    country_id    INTEGER,
    foreign key (country_id) references country(country_id)
)

CREATE TABLE customer
(
    customer_id INTEGER
            primary key,
    first_name  TEXT,
    last_name   TEXT,
    email       TEXT
)

CREATE TABLE customer_address
(
    customer_id INTEGER,
    address_id  INTEGER,
    status_id   INTEGER,
    primary key (customer_id, address_id),
    foreign key (address_id) references address(address_id),
    foreign key (customer_id) references customer(customer_id)
)

CREATE TABLE order_status
(
    status_id    INTEGER
            primary key,
    status_value TEXT
)

CREATE TABLE publisher
(
    publisher_id   INTEGER
            primary key,
    publisher_name TEXT
)

CREATE TABLE book
(
    book_id          INTEGER
            primary key,
    title            TEXT,
    isbn13           TEXT,
    language_id      INTEGER,
    num_pages        INTEGER,
    publication_date DATE,
    publisher_id     INTEGER,
    foreign key (language_id) references book_language(language_id),
    foreign key (publisher_id) references publisher(publisher_id)
)

CREATE TABLE book_author
(
    book_id   INTEGER,
    author_id INTEGER,
    primary key (book_id, author_id),
    foreign key (author_id) references author(author_id),
    foreign key (book_id) references book(book_id)
)

CREATE TABLE shipping_method
(
    method_id   INTEGER
            primary key,
    method_name TEXT,
    cost        REAL
)

CREATE TABLE "cust_order"
(
    order_id           INTEGER
        primary key autoincrement,
    order_date         DATETIME,
    customer_id        INTEGER
        references customer,
    shipping_method_id INTEGER
        references shipping_method,
    dest_address_id    INTEGER
        references address
)

CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE "order_history"
(
    history_id  INTEGER
        primary key autoincrement,
    order_id    INTEGER
        references cust_order,
    status_id   INTEGER
        references order_status,
    status_date DATETIME
)

CREATE TABLE "order_line"
(
    line_id  INTEGER
        primary key autoincrement,
    order_id INTEGER
        references cust_order,
    book_id  INTEGER
        references book,
    price    REAL
)

### Question
How many publishers have the word "book" in their name?

### Hint
publisher have the word 'book' refers to publisher_name LIKE '%book%'

### SQL
SELECT COUNT(*) FROM publisher WHERE publisher_name LIKE '%book%'
### Database Schema
CREATE TABLE address_status
(
    status_id      INTEGER
            primary key,
    address_status TEXT
)

CREATE TABLE author
(
    author_id   INTEGER
            primary key,
    author_name TEXT
)

CREATE TABLE book_language
(
    language_id   INTEGER
            primary key,
    language_code TEXT,
    language_name TEXT
)

CREATE TABLE country
(
    country_id   INTEGER
            primary key,
    country_name TEXT
)

CREATE TABLE address
(
    address_id    INTEGER
            primary key,
    street_number TEXT,
    street_name   TEXT,
    city          TEXT,
    country_id    INTEGER,
    foreign key (country_id) references country(country_id)
)

CREATE TABLE customer
(
    customer_id INTEGER
            primary key,
    first_name  TEXT,
    last_name   TEXT,
    email       TEXT
)

CREATE TABLE customer_address
(
    customer_id INTEGER,
    address_id  INTEGER,
    status_id   INTEGER,
    primary key (customer_id, address_id),
    foreign key (address_id) references address(address_id),
    foreign key (customer_id) references customer(customer_id)
)

CREATE TABLE order_status
(
    status_id    INTEGER
            primary key,
    status_value TEXT
)

CREATE TABLE publisher
(
    publisher_id   INTEGER
            primary key,
    publisher_name TEXT
)

CREATE TABLE book
(
    book_id          INTEGER
            primary key,
    title            TEXT,
    isbn13           TEXT,
    language_id      INTEGER,
    num_pages        INTEGER,
    publication_date DATE,
    publisher_id     INTEGER,
    foreign key (language_id) references book_language(language_id),
    foreign key (publisher_id) references publisher(publisher_id)
)

CREATE TABLE book_author
(
    book_id   INTEGER,
    author_id INTEGER,
    primary key (book_id, author_id),
    foreign key (author_id) references author(author_id),
    foreign key (book_id) references book(book_id)
)

CREATE TABLE shipping_method
(
    method_id   INTEGER
            primary key,
    method_name TEXT,
    cost        REAL
)

CREATE TABLE "cust_order"
(
    order_id           INTEGER
        primary key autoincrement,
    order_date         DATETIME,
    customer_id        INTEGER
        references customer,
    shipping_method_id INTEGER
        references shipping_method,
    dest_address_id    INTEGER
        references address
)

CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE "order_history"
(
    history_id  INTEGER
        primary key autoincrement,
    order_id    INTEGER
        references cust_order,
    status_id   INTEGER
        references order_status,
    status_date DATETIME
)

CREATE TABLE "order_line"
(
    line_id  INTEGER
        primary key autoincrement,
    order_id INTEGER
        references cust_order,
    book_id  INTEGER
        references book,
    price    REAL
)

### Question
Which language is the rarest among all the books?

### Hint
language written in refers to language_name; rarest refers to Min(Count(book_id))

### SQL
SELECT T2.language_name FROM book AS T1 INNER JOIN book_language AS T2 ON T1.language_id = T2.language_id GROUP BY T2.language_name ORDER BY COUNT(T2.language_name) ASC LIMIT 1
### Database Schema
CREATE TABLE address_status
(
    status_id      INTEGER
            primary key,
    address_status TEXT
)

CREATE TABLE author
(
    author_id   INTEGER
            primary key,
    author_name TEXT
)

CREATE TABLE book_language
(
    language_id   INTEGER
            primary key,
    language_code TEXT,
    language_name TEXT
)

CREATE TABLE country
(
    country_id   INTEGER
            primary key,
    country_name TEXT
)

CREATE TABLE address
(
    address_id    INTEGER
            primary key,
    street_number TEXT,
    street_name   TEXT,
    city          TEXT,
    country_id    INTEGER,
    foreign key (country_id) references country(country_id)
)

CREATE TABLE customer
(
    customer_id INTEGER
            primary key,
    first_name  TEXT,
    last_name   TEXT,
    email       TEXT
)

CREATE TABLE customer_address
(
    customer_id INTEGER,
    address_id  INTEGER,
    status_id   INTEGER,
    primary key (customer_id, address_id),
    foreign key (address_id) references address(address_id),
    foreign key (customer_id) references customer(customer_id)
)

CREATE TABLE order_status
(
    status_id    INTEGER
            primary key,
    status_value TEXT
)

CREATE TABLE publisher
(
    publisher_id   INTEGER
            primary key,
    publisher_name TEXT
)

CREATE TABLE book
(
    book_id          INTEGER
            primary key,
    title            TEXT,
    isbn13           TEXT,
    language_id      INTEGER,
    num_pages        INTEGER,
    publication_date DATE,
    publisher_id     INTEGER,
    foreign key (language_id) references book_language(language_id),
    foreign key (publisher_id) references publisher(publisher_id)
)

CREATE TABLE book_author
(
    book_id   INTEGER,
    author_id INTEGER,
    primary key (book_id, author_id),
    foreign key (author_id) references author(author_id),
    foreign key (book_id) references book(book_id)
)

CREATE TABLE shipping_method
(
    method_id   INTEGER
            primary key,
    method_name TEXT,
    cost        REAL
)

CREATE TABLE "cust_order"
(
    order_id           INTEGER
        primary key autoincrement,
    order_date         DATETIME,
    customer_id        INTEGER
        references customer,
    shipping_method_id INTEGER
        references shipping_method,
    dest_address_id    INTEGER
        references address
)

CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE "order_history"
(
    history_id  INTEGER
        primary key autoincrement,
    order_id    INTEGER
        references cust_order,
    status_id   INTEGER
        references order_status,
    status_date DATETIME
)

CREATE TABLE "order_line"
(
    line_id  INTEGER
        primary key autoincrement,
    order_id INTEGER
        references cust_order,
    book_id  INTEGER
        references book,
    price    REAL
)

### Question
List all the order dates for the customer named "Adrian Kunzelmann".

### SQL
SELECT T3.order_date FROM book AS T1 INNER JOIN order_line AS T2 ON T1.book_id = T2.book_id INNER JOIN cust_order AS T3 ON T3.order_id = T2.order_id INNER JOIN customer AS T4 ON T4.customer_id = T3.customer_id WHERE T4.first_name = 'Adrian' AND T4.last_name = 'Kunzelmann'
### Database Schema
CREATE TABLE address_status
(
    status_id      INTEGER
            primary key,
    address_status TEXT
)

CREATE TABLE author
(
    author_id   INTEGER
            primary key,
    author_name TEXT
)

CREATE TABLE book_language
(
    language_id   INTEGER
            primary key,
    language_code TEXT,
    language_name TEXT
)

CREATE TABLE country
(
    country_id   INTEGER
            primary key,
    country_name TEXT
)

CREATE TABLE address
(
    address_id    INTEGER
            primary key,
    street_number TEXT,
    street_name   TEXT,
    city          TEXT,
    country_id    INTEGER,
    foreign key (country_id) references country(country_id)
)

CREATE TABLE customer
(
    customer_id INTEGER
            primary key,
    first_name  TEXT,
    last_name   TEXT,
    email       TEXT
)

CREATE TABLE customer_address
(
    customer_id INTEGER,
    address_id  INTEGER,
    status_id   INTEGER,
    primary key (customer_id, address_id),
    foreign key (address_id) references address(address_id),
    foreign key (customer_id) references customer(customer_id)
)

CREATE TABLE order_status
(
    status_id    INTEGER
            primary key,
    status_value TEXT
)

CREATE TABLE publisher
(
    publisher_id   INTEGER
            primary key,
    publisher_name TEXT
)

CREATE TABLE book
(
    book_id          INTEGER
            primary key,
    title            TEXT,
    isbn13           TEXT,
    language_id      INTEGER,
    num_pages        INTEGER,
    publication_date DATE,
    publisher_id     INTEGER,
    foreign key (language_id) references book_language(language_id),
    foreign key (publisher_id) references publisher(publisher_id)
)

CREATE TABLE book_author
(
    book_id   INTEGER,
    author_id INTEGER,
    primary key (book_id, author_id),
    foreign key (author_id) references author(author_id),
    foreign key (book_id) references book(book_id)
)

CREATE TABLE shipping_method
(
    method_id   INTEGER
            primary key,
    method_name TEXT,
    cost        REAL
)

CREATE TABLE "cust_order"
(
    order_id           INTEGER
        primary key autoincrement,
    order_date         DATETIME,
    customer_id        INTEGER
        references customer,
    shipping_method_id INTEGER
        references shipping_method,
    dest_address_id    INTEGER
        references address
)

CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE "order_history"
(
    history_id  INTEGER
        primary key autoincrement,
    order_id    INTEGER
        references cust_order,
    status_id   INTEGER
        references order_status,
    status_date DATETIME
)

CREATE TABLE "order_line"
(
    line_id  INTEGER
        primary key autoincrement,
    order_id INTEGER
        references cust_order,
    book_id  INTEGER
        references book,
    price    REAL
)

### Question
How many addresses are from the Philippines?

### Hint
"Philippines" is the country_name

### SQL
SELECT COUNT(T2.country_id) FROM address AS T1 INNER JOIN country AS T2 ON T2.country_id = T1.country_id WHERE T2.country_name = 'Philippines'
### Database Schema
CREATE TABLE address_status
(
    status_id      INTEGER
            primary key,
    address_status TEXT
)

CREATE TABLE author
(
    author_id   INTEGER
            primary key,
    author_name TEXT
)

CREATE TABLE book_language
(
    language_id   INTEGER
            primary key,
    language_code TEXT,
    language_name TEXT
)

CREATE TABLE country
(
    country_id   INTEGER
            primary key,
    country_name TEXT
)

CREATE TABLE address
(
    address_id    INTEGER
            primary key,
    street_number TEXT,
    street_name   TEXT,
    city          TEXT,
    country_id    INTEGER,
    foreign key (country_id) references country(country_id)
)

CREATE TABLE customer
(
    customer_id INTEGER
            primary key,
    first_name  TEXT,
    last_name   TEXT,
    email       TEXT
)

CREATE TABLE customer_address
(
    customer_id INTEGER,
    address_id  INTEGER,
    status_id   INTEGER,
    primary key (customer_id, address_id),
    foreign key (address_id) references address(address_id),
    foreign key (customer_id) references customer(customer_id)
)

CREATE TABLE order_status
(
    status_id    INTEGER
            primary key,
    status_value TEXT
)

CREATE TABLE publisher
(
    publisher_id   INTEGER
            primary key,
    publisher_name TEXT
)

CREATE TABLE book
(
    book_id          INTEGER
            primary key,
    title            TEXT,
    isbn13           TEXT,
    language_id      INTEGER,
    num_pages        INTEGER,
    publication_date DATE,
    publisher_id     INTEGER,
    foreign key (language_id) references book_language(language_id),
    foreign key (publisher_id) references publisher(publisher_id)
)

CREATE TABLE book_author
(
    book_id   INTEGER,
    author_id INTEGER,
    primary key (book_id, author_id),
    foreign key (author_id) references author(author_id),
    foreign key (book_id) references book(book_id)
)

CREATE TABLE shipping_method
(
    method_id   INTEGER
            primary key,
    method_name TEXT,
    cost        REAL
)

CREATE TABLE "cust_order"
(
    order_id           INTEGER
        primary key autoincrement,
    order_date         DATETIME,
    customer_id        INTEGER
        references customer,
    shipping_method_id INTEGER
        references shipping_method,
    dest_address_id    INTEGER
        references address
)

CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE "order_history"
(
    history_id  INTEGER
        primary key autoincrement,
    order_id    INTEGER
        references cust_order,
    status_id   INTEGER
        references order_status,
    status_date DATETIME
)

CREATE TABLE "order_line"
(
    line_id  INTEGER
        primary key autoincrement,
    order_id INTEGER
        references cust_order,
    book_id  INTEGER
        references book,
    price    REAL
)

### Question
Who is the author who wrote the most books?

### Hint
author refers to author_name; who wrote the most book refers to Max(Count(author_id))

### SQL
SELECT T1.author_name FROM author AS T1 INNER JOIN book_author AS T2 ON T1.author_id = T2.author_id GROUP BY T1.author_name ORDER BY COUNT(T2.author_id) DESC LIMIT 1
### Database Schema
CREATE TABLE address_status
(
    status_id      INTEGER
            primary key,
    address_status TEXT
)

CREATE TABLE author
(
    author_id   INTEGER
            primary key,
    author_name TEXT
)

CREATE TABLE book_language
(
    language_id   INTEGER
            primary key,
    language_code TEXT,
    language_name TEXT
)

CREATE TABLE country
(
    country_id   INTEGER
            primary key,
    country_name TEXT
)

CREATE TABLE address
(
    address_id    INTEGER
            primary key,
    street_number TEXT,
    street_name   TEXT,
    city          TEXT,
    country_id    INTEGER,
    foreign key (country_id) references country(country_id)
)

CREATE TABLE customer
(
    customer_id INTEGER
            primary key,
    first_name  TEXT,
    last_name   TEXT,
    email       TEXT
)

CREATE TABLE customer_address
(
    customer_id INTEGER,
    address_id  INTEGER,
    status_id   INTEGER,
    primary key (customer_id, address_id),
    foreign key (address_id) references address(address_id),
    foreign key (customer_id) references customer(customer_id)
)

CREATE TABLE order_status
(
    status_id    INTEGER
            primary key,
    status_value TEXT
)

CREATE TABLE publisher
(
    publisher_id   INTEGER
            primary key,
    publisher_name TEXT
)

CREATE TABLE book
(
    book_id          INTEGER
            primary key,
    title            TEXT,
    isbn13           TEXT,
    language_id      INTEGER,
    num_pages        INTEGER,
    publication_date DATE,
    publisher_id     INTEGER,
    foreign key (language_id) references book_language(language_id),
    foreign key (publisher_id) references publisher(publisher_id)
)

CREATE TABLE book_author
(
    book_id   INTEGER,
    author_id INTEGER,
    primary key (book_id, author_id),
    foreign key (author_id) references author(author_id),
    foreign key (book_id) references book(book_id)
)

CREATE TABLE shipping_method
(
    method_id   INTEGER
            primary key,
    method_name TEXT,
    cost        REAL
)

CREATE TABLE "cust_order"
(
    order_id           INTEGER
        primary key autoincrement,
    order_date         DATETIME,
    customer_id        INTEGER
        references customer,
    shipping_method_id INTEGER
        references shipping_method,
    dest_address_id    INTEGER
        references address
)

CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE "order_history"
(
    history_id  INTEGER
        primary key autoincrement,
    order_id    INTEGER
        references cust_order,
    status_id   INTEGER
        references order_status,
    status_date DATETIME
)

CREATE TABLE "order_line"
(
    line_id  INTEGER
        primary key autoincrement,
    order_id INTEGER
        references cust_order,
    book_id  INTEGER
        references book,
    price    REAL
)

### Question
What are the books published by "Harper Collins"?

### Hint
"Harper Collins" is the publisher_name; books refers to title

### SQL
SELECT T1.title FROM book AS T1 INNER JOIN publisher AS T2 ON T1.publisher_id = T2.publisher_id WHERE T2.publisher_name = 'Harper Collins'
### Database Schema
CREATE TABLE address_status
(
    status_id      INTEGER
            primary key,
    address_status TEXT
)

CREATE TABLE author
(
    author_id   INTEGER
            primary key,
    author_name TEXT
)

CREATE TABLE book_language
(
    language_id   INTEGER
            primary key,
    language_code TEXT,
    language_name TEXT
)

CREATE TABLE country
(
    country_id   INTEGER
            primary key,
    country_name TEXT
)

CREATE TABLE address
(
    address_id    INTEGER
            primary key,
    street_number TEXT,
    street_name   TEXT,
    city          TEXT,
    country_id    INTEGER,
    foreign key (country_id) references country(country_id)
)

CREATE TABLE customer
(
    customer_id INTEGER
            primary key,
    first_name  TEXT,
    last_name   TEXT,
    email       TEXT
)

CREATE TABLE customer_address
(
    customer_id INTEGER,
    address_id  INTEGER,
    status_id   INTEGER,
    primary key (customer_id, address_id),
    foreign key (address_id) references address(address_id),
    foreign key (customer_id) references customer(customer_id)
)

CREATE TABLE order_status
(
    status_id    INTEGER
            primary key,
    status_value TEXT
)

CREATE TABLE publisher
(
    publisher_id   INTEGER
            primary key,
    publisher_name TEXT
)

CREATE TABLE book
(
    book_id          INTEGER
            primary key,
    title            TEXT,
    isbn13           TEXT,
    language_id      INTEGER,
    num_pages        INTEGER,
    publication_date DATE,
    publisher_id     INTEGER,
    foreign key (language_id) references book_language(language_id),
    foreign key (publisher_id) references publisher(publisher_id)
)

CREATE TABLE book_author
(
    book_id   INTEGER,
    author_id INTEGER,
    primary key (book_id, author_id),
    foreign key (author_id) references author(author_id),
    foreign key (book_id) references book(book_id)
)

CREATE TABLE shipping_method
(
    method_id   INTEGER
            primary key,
    method_name TEXT,
    cost        REAL
)

CREATE TABLE "cust_order"
(
    order_id           INTEGER
        primary key autoincrement,
    order_date         DATETIME,
    customer_id        INTEGER
        references customer,
    shipping_method_id INTEGER
        references shipping_method,
    dest_address_id    INTEGER
        references address
)

CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE "order_history"
(
    history_id  INTEGER
        primary key autoincrement,
    order_id    INTEGER
        references cust_order,
    status_id   INTEGER
        references order_status,
    status_date DATETIME
)

CREATE TABLE "order_line"
(
    line_id  INTEGER
        primary key autoincrement,
    order_id INTEGER
        references cust_order,
    book_id  INTEGER
        references book,
    price    REAL
)

### Question
How many orders were returned in the year 2020?

### Hint
returned refers to status_value = 'Returned'; in the year 2020 refers to status_date LIKE '%2020%'

### SQL
SELECT COUNT(*) FROM order_status AS T1 INNER JOIN order_history AS T2 ON T1.status_id = T2.status_id WHERE T1.status_value = 'Returned' AND STRFTIME('%Y', T2.status_date) = '2020'