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
List every book that Ursola Purdy has ordered.

### Hint
book refers to title

### SQL
SELECT T1.title 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 = 'Ursola' AND T4.last_name = 'Purdy'
### 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 of the book with the biggest page count?

### Hint
author refers to author_name, biggest page count refers to Max(num_pages)

### SQL
SELECT T3.author_name FROM book AS T1 INNER JOIN book_author AS T2 ON T1.book_id = T2.book_id INNER JOIN author AS T3 ON T3.author_id = T2.author_id ORDER BY T1.num_pages 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
How many books written by Akira Watanabe are available on Gravity?

### Hint
"Akira Watanabe" is the author_name

### SQL
SELECT COUNT(*) FROM author AS T1 INNER JOIN book_author AS T2 ON T1.author_id = T2.author_id WHERE T1.author_name = 'Akira Watanabe'
### 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
Identify the publisher of the book Girls' Night In.

### Hint
"Girls' Night In" is the title of the book; publisher is the publisher_name

### SQL
SELECT T2.publisher_name FROM book AS T1 INNER JOIN publisher AS T2 ON T1.publisher_id = T2.publisher_id WHERE T1.title = 'Girls'' Night In'
### 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
Name the publisher of the oldest book.

### Hint
publisher refers to publisher_name;  oldest book refers to Min(publication_date)

### SQL
SELECT T2.publisher_name FROM book AS T1 INNER JOIN publisher AS T2 ON T1.publisher_id = T2.publisher_id ORDER BY T1.publication_date 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
How many orders have been cancelled in 2022?

### Hint
cancelled refers to status_value = 'Cancelled'; in 2022 refers to SUBSTR(status_date, 1, 4) = '2022'

### 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 = 'Cancelled' AND STRFTIME('%Y', T2.status_date) = '2022'
### 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 books published by BBC Audiobooks.

### Hint
"BBC Audiobooks" refers to 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 = 'BBC Audiobooks'