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
What are the city addresses of the customers located in the United States of America?

### Hint
"United States of America" is the country_name

### SQL
SELECT DISTINCT T2.city FROM country AS T1 INNER JOIN address AS T2 ON T1.country_id = T2.country_id WHERE T1.country_name = 'United States of America'
### 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 books have the most expensive price?

### Hint
most expensive book refers to Max(price)

### SQL
SELECT T2.title FROM order_line AS T1 INNER JOIN book AS T2 ON T1.book_id = T2.book_id ORDER BY T1.price 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 customers ordered the book titled "Anleitung zum Zickigsein"

### Hint
"Anleitung zum Zickigsein" is the title of the book

### SQL
SELECT COUNT(*) FROM book AS T1 INNER JOIN order_line AS T2 ON T1.book_id = T2.book_id WHERE T1.title = 'Anleitung zum Zickigsein'
### 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 customers ordered the oldest book?

### Hint
oldest book refers to Min(publiation_date)

### SQL
SELECT COUNT(*) FROM book AS T1 INNER JOIN order_line AS T2 ON T1.book_id = T2.book_id GROUP BY T1.publication_date 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
What are the languages of the first two published books?

### Hint
first two published book refers to Min(publication_date); language refers to language_name

### SQL
SELECT T2.language_name FROM book AS T1 INNER JOIN book_language AS T2 ON T1.language_id = T2.language_id ORDER BY T1.publication_date ASC LIMIT 2
### 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 published the book "The Secret Garden"?

### Hint
"The Secret Garden" is the title of the book; who published the book refers to publisher_name

### SQL
SELECT DISTINCT T2.publisher_name FROM book AS T1 INNER JOIN publisher AS T2 ON T1.publisher_id = T2.publisher_id WHERE T1.title = 'The Secret Garden'
### 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 names of all the publishers who have published at least 30 books?

### Hint
published at least 30 books refers to Count(book_id) > = 30

### SQL
SELECT T2.publisher_name FROM book AS T1 INNER JOIN publisher AS T2 ON T1.publisher_id = T2.publisher_id GROUP BY T2.publisher_name HAVING COUNT(T2.publisher_name) >= 30