lianghsun/bird-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE Dish
(
    id             INTEGER
            primary key,
    name           TEXT,
    description    TEXT,
    menus_appeared INTEGER,
    times_appeared INTEGER,
    first_appeared INTEGER,
    last_appeared  INTEGER,
    lowest_price   REAL,
    highest_price  REAL
)

CREATE TABLE Menu
(
    id                   INTEGER
            primary key,
    name                 TEXT,
    sponsor              TEXT,
    event                TEXT,
    venue                TEXT,
    place                TEXT,
    physical_description TEXT,
    occasion             TEXT,
    notes                TEXT,
    call_number          TEXT,
    keywords             TEXT,
    language             TEXT,
    date                 DATE,
    location             TEXT,
    location_type        TEXT,
    currency             TEXT,
    currency_symbol      TEXT,
    status               TEXT,
    page_count           INTEGER,
    dish_count           INTEGER
)

CREATE TABLE MenuPage
(
    id          INTEGER
            primary key,
    menu_id     INTEGER,
    page_number INTEGER,
    image_id    REAL,
    full_height INTEGER,
    full_width  INTEGER,
    uuid        TEXT,
    foreign key (menu_id) references Menu(id)
)

CREATE TABLE MenuItem
(
    id           INTEGER
            primary key,
    menu_page_id INTEGER,
    price        REAL,
    high_price   REAL,
    dish_id      INTEGER,
    created_at   TEXT,
    updated_at   TEXT,
    xpos         REAL,
    ypos         REAL,
    foreign key (dish_id) references Dish(id),
    foreign key (menu_page_id) references MenuPage(id)
)

### Question
How much does the dish on page 2 of menu ID 12474 cost?

### Hint
page 2 refers to page_number = 2;

### SQL
SELECT T1.price FROM MenuItem AS T1 INNER JOIN MenuPage AS T2 ON T2.id = T1.menu_page_id WHERE T2.menu_id = 12474 AND T2.page_number = 2
### Database Schema
CREATE TABLE Dish
(
    id             INTEGER
            primary key,
    name           TEXT,
    description    TEXT,
    menus_appeared INTEGER,
    times_appeared INTEGER,
    first_appeared INTEGER,
    last_appeared  INTEGER,
    lowest_price   REAL,
    highest_price  REAL
)

CREATE TABLE Menu
(
    id                   INTEGER
            primary key,
    name                 TEXT,
    sponsor              TEXT,
    event                TEXT,
    venue                TEXT,
    place                TEXT,
    physical_description TEXT,
    occasion             TEXT,
    notes                TEXT,
    call_number          TEXT,
    keywords             TEXT,
    language             TEXT,
    date                 DATE,
    location             TEXT,
    location_type        TEXT,
    currency             TEXT,
    currency_symbol      TEXT,
    status               TEXT,
    page_count           INTEGER,
    dish_count           INTEGER
)

CREATE TABLE MenuPage
(
    id          INTEGER
            primary key,
    menu_id     INTEGER,
    page_number INTEGER,
    image_id    REAL,
    full_height INTEGER,
    full_width  INTEGER,
    uuid        TEXT,
    foreign key (menu_id) references Menu(id)
)

CREATE TABLE MenuItem
(
    id           INTEGER
            primary key,
    menu_page_id INTEGER,
    price        REAL,
    high_price   REAL,
    dish_id      INTEGER,
    created_at   TEXT,
    updated_at   TEXT,
    xpos         REAL,
    ypos         REAL,
    foreign key (dish_id) references Dish(id),
    foreign key (menu_page_id) references MenuPage(id)
)

### Question
Among the dishes, how many of them were created between 2011-03-31 at 20:24:46 UTC and 2011-04-15 at 23:09:51 UTC.

### Hint
created between 2011-03-31 at 20:24:46 UTC and 2011-04-15 at 23:09:51 UTC refers to created_at between '2011-03-31 20:24:46 UTC' AND '2011-04-15 23:09:51 UTC';

### SQL
SELECT SUM(CASE WHEN T2.created_at BETWEEN '2011-03-31 20:24:46 UTC' AND '2011-04-15 23:09:51 UTC' THEN 1 ELSE 0 END) FROM Dish AS T1 INNER JOIN MenuItem AS T2 ON T1.id = T2.dish_id
### Database Schema
CREATE TABLE Dish
(
    id             INTEGER
            primary key,
    name           TEXT,
    description    TEXT,
    menus_appeared INTEGER,
    times_appeared INTEGER,
    first_appeared INTEGER,
    last_appeared  INTEGER,
    lowest_price   REAL,
    highest_price  REAL
)

CREATE TABLE Menu
(
    id                   INTEGER
            primary key,
    name                 TEXT,
    sponsor              TEXT,
    event                TEXT,
    venue                TEXT,
    place                TEXT,
    physical_description TEXT,
    occasion             TEXT,
    notes                TEXT,
    call_number          TEXT,
    keywords             TEXT,
    language             TEXT,
    date                 DATE,
    location             TEXT,
    location_type        TEXT,
    currency             TEXT,
    currency_symbol      TEXT,
    status               TEXT,
    page_count           INTEGER,
    dish_count           INTEGER
)

CREATE TABLE MenuPage
(
    id          INTEGER
            primary key,
    menu_id     INTEGER,
    page_number INTEGER,
    image_id    REAL,
    full_height INTEGER,
    full_width  INTEGER,
    uuid        TEXT,
    foreign key (menu_id) references Menu(id)
)

CREATE TABLE MenuItem
(
    id           INTEGER
            primary key,
    menu_page_id INTEGER,
    price        REAL,
    high_price   REAL,
    dish_id      INTEGER,
    created_at   TEXT,
    updated_at   TEXT,
    xpos         REAL,
    ypos         REAL,
    foreign key (dish_id) references Dish(id),
    foreign key (menu_page_id) references MenuPage(id)
)

### Question
Who are the sponsors of the menu whose image full height is more than 10000 mm?

### Hint
image full height is more than 10000 mm refers to full_height > 10000;

### SQL
SELECT T2.sponsor FROM MenuPage AS T1 INNER JOIN Menu AS T2 ON T2.id = T1.menu_id WHERE T1.full_height = 10000
### Database Schema
CREATE TABLE Dish
(
    id             INTEGER
            primary key,
    name           TEXT,
    description    TEXT,
    menus_appeared INTEGER,
    times_appeared INTEGER,
    first_appeared INTEGER,
    last_appeared  INTEGER,
    lowest_price   REAL,
    highest_price  REAL
)

CREATE TABLE Menu
(
    id                   INTEGER
            primary key,
    name                 TEXT,
    sponsor              TEXT,
    event                TEXT,
    venue                TEXT,
    place                TEXT,
    physical_description TEXT,
    occasion             TEXT,
    notes                TEXT,
    call_number          TEXT,
    keywords             TEXT,
    language             TEXT,
    date                 DATE,
    location             TEXT,
    location_type        TEXT,
    currency             TEXT,
    currency_symbol      TEXT,
    status               TEXT,
    page_count           INTEGER,
    dish_count           INTEGER
)

CREATE TABLE MenuPage
(
    id          INTEGER
            primary key,
    menu_id     INTEGER,
    page_number INTEGER,
    image_id    REAL,
    full_height INTEGER,
    full_width  INTEGER,
    uuid        TEXT,
    foreign key (menu_id) references Menu(id)
)

CREATE TABLE MenuItem
(
    id           INTEGER
            primary key,
    menu_page_id INTEGER,
    price        REAL,
    high_price   REAL,
    dish_id      INTEGER,
    created_at   TEXT,
    updated_at   TEXT,
    xpos         REAL,
    ypos         REAL,
    foreign key (dish_id) references Dish(id),
    foreign key (menu_page_id) references MenuPage(id)
)

### Question
Write down the image ID, full height, and full width of the menu that were used in the "100TH ANNIVERSARY OF BIRTH OF DANIEL WEBSTER" event.

### Hint
FALSE;

### SQL
SELECT T1.image_id, T1.full_height, T1.full_width FROM MenuPage AS T1 INNER JOIN Menu AS T2 ON T2.id = T1.menu_id WHERE T2.event = '100TH ANNIVERSARY OF BIRTH OF DANIEL WEBSTER'
### Database Schema
CREATE TABLE Dish
(
    id             INTEGER
            primary key,
    name           TEXT,
    description    TEXT,
    menus_appeared INTEGER,
    times_appeared INTEGER,
    first_appeared INTEGER,
    last_appeared  INTEGER,
    lowest_price   REAL,
    highest_price  REAL
)

CREATE TABLE Menu
(
    id                   INTEGER
            primary key,
    name                 TEXT,
    sponsor              TEXT,
    event                TEXT,
    venue                TEXT,
    place                TEXT,
    physical_description TEXT,
    occasion             TEXT,
    notes                TEXT,
    call_number          TEXT,
    keywords             TEXT,
    language             TEXT,
    date                 DATE,
    location             TEXT,
    location_type        TEXT,
    currency             TEXT,
    currency_symbol      TEXT,
    status               TEXT,
    page_count           INTEGER,
    dish_count           INTEGER
)

CREATE TABLE MenuPage
(
    id          INTEGER
            primary key,
    menu_id     INTEGER,
    page_number INTEGER,
    image_id    REAL,
    full_height INTEGER,
    full_width  INTEGER,
    uuid        TEXT,
    foreign key (menu_id) references Menu(id)
)

CREATE TABLE MenuItem
(
    id           INTEGER
            primary key,
    menu_page_id INTEGER,
    price        REAL,
    high_price   REAL,
    dish_id      INTEGER,
    created_at   TEXT,
    updated_at   TEXT,
    xpos         REAL,
    ypos         REAL,
    foreign key (dish_id) references Dish(id),
    foreign key (menu_page_id) references MenuPage(id)
)

### Question
Under what events was the menu page's full width less than 2000 mm?

### Hint
full width less than 2000 mm refers to full_width < 2000;

### SQL
SELECT T1.event FROM Menu AS T1 INNER JOIN MenuPage AS T2 ON T1.id = T2.menu_id WHERE T2.full_width = 2000
### Database Schema
CREATE TABLE Dish
(
    id             INTEGER
            primary key,
    name           TEXT,
    description    TEXT,
    menus_appeared INTEGER,
    times_appeared INTEGER,
    first_appeared INTEGER,
    last_appeared  INTEGER,
    lowest_price   REAL,
    highest_price  REAL
)

CREATE TABLE Menu
(
    id                   INTEGER
            primary key,
    name                 TEXT,
    sponsor              TEXT,
    event                TEXT,
    venue                TEXT,
    place                TEXT,
    physical_description TEXT,
    occasion             TEXT,
    notes                TEXT,
    call_number          TEXT,
    keywords             TEXT,
    language             TEXT,
    date                 DATE,
    location             TEXT,
    location_type        TEXT,
    currency             TEXT,
    currency_symbol      TEXT,
    status               TEXT,
    page_count           INTEGER,
    dish_count           INTEGER
)

CREATE TABLE MenuPage
(
    id          INTEGER
            primary key,
    menu_id     INTEGER,
    page_number INTEGER,
    image_id    REAL,
    full_height INTEGER,
    full_width  INTEGER,
    uuid        TEXT,
    foreign key (menu_id) references Menu(id)
)

CREATE TABLE MenuItem
(
    id           INTEGER
            primary key,
    menu_page_id INTEGER,
    price        REAL,
    high_price   REAL,
    dish_id      INTEGER,
    created_at   TEXT,
    updated_at   TEXT,
    xpos         REAL,
    ypos         REAL,
    foreign key (dish_id) references Dish(id),
    foreign key (menu_page_id) references MenuPage(id)
)

### Question
Name the dishes that cost 180,000.

### Hint
cost 180,000 refers to price = 180000;

### SQL
SELECT T1.name FROM Dish AS T1 INNER JOIN MenuItem AS T2 ON T1.id = T2.dish_id WHERE T2.price = 180000
### Database Schema
CREATE TABLE Dish
(
    id             INTEGER
            primary key,
    name           TEXT,
    description    TEXT,
    menus_appeared INTEGER,
    times_appeared INTEGER,
    first_appeared INTEGER,
    last_appeared  INTEGER,
    lowest_price   REAL,
    highest_price  REAL
)

CREATE TABLE Menu
(
    id                   INTEGER
            primary key,
    name                 TEXT,
    sponsor              TEXT,
    event                TEXT,
    venue                TEXT,
    place                TEXT,
    physical_description TEXT,
    occasion             TEXT,
    notes                TEXT,
    call_number          TEXT,
    keywords             TEXT,
    language             TEXT,
    date                 DATE,
    location             TEXT,
    location_type        TEXT,
    currency             TEXT,
    currency_symbol      TEXT,
    status               TEXT,
    page_count           INTEGER,
    dish_count           INTEGER
)

CREATE TABLE MenuPage
(
    id          INTEGER
            primary key,
    menu_id     INTEGER,
    page_number INTEGER,
    image_id    REAL,
    full_height INTEGER,
    full_width  INTEGER,
    uuid        TEXT,
    foreign key (menu_id) references Menu(id)
)

CREATE TABLE MenuItem
(
    id           INTEGER
            primary key,
    menu_page_id INTEGER,
    price        REAL,
    high_price   REAL,
    dish_id      INTEGER,
    created_at   TEXT,
    updated_at   TEXT,
    xpos         REAL,
    ypos         REAL,
    foreign key (dish_id) references Dish(id),
    foreign key (menu_page_id) references MenuPage(id)
)

### Question
What is the position coordinate on the page menu of the "Small Hominy" dish and how long did it appear?

### Hint
position coordinate = xpos, ypos; Small Hominy is a name of dish; how long it appear = SUBTRACT(last_appeared, first_appeared);

### SQL
SELECT T2.xpos, T2.ypos, T1.last_appeared - T1.first_appeared FROM Dish AS T1 INNER JOIN MenuItem AS T2 ON T1.id = T2.dish_id WHERE T1.name = 'Small Hominy'
### Database Schema
CREATE TABLE Dish
(
    id             INTEGER
            primary key,
    name           TEXT,
    description    TEXT,
    menus_appeared INTEGER,
    times_appeared INTEGER,
    first_appeared INTEGER,
    last_appeared  INTEGER,
    lowest_price   REAL,
    highest_price  REAL
)

CREATE TABLE Menu
(
    id                   INTEGER
            primary key,
    name                 TEXT,
    sponsor              TEXT,
    event                TEXT,
    venue                TEXT,
    place                TEXT,
    physical_description TEXT,
    occasion             TEXT,
    notes                TEXT,
    call_number          TEXT,
    keywords             TEXT,
    language             TEXT,
    date                 DATE,
    location             TEXT,
    location_type        TEXT,
    currency             TEXT,
    currency_symbol      TEXT,
    status               TEXT,
    page_count           INTEGER,
    dish_count           INTEGER
)

CREATE TABLE MenuPage
(
    id          INTEGER
            primary key,
    menu_id     INTEGER,
    page_number INTEGER,
    image_id    REAL,
    full_height INTEGER,
    full_width  INTEGER,
    uuid        TEXT,
    foreign key (menu_id) references Menu(id)
)

CREATE TABLE MenuItem
(
    id           INTEGER
            primary key,
    menu_page_id INTEGER,
    price        REAL,
    high_price   REAL,
    dish_id      INTEGER,
    created_at   TEXT,
    updated_at   TEXT,
    xpos         REAL,
    ypos         REAL,
    foreign key (dish_id) references Dish(id),
    foreign key (menu_page_id) references MenuPage(id)
)

### Question
Calculate the image area of the page menu for the dish named "Baked Stuffed Mullet & Sauce Pomard". Please include the page number and image ID.

### Hint
image area = MULTIPLY(full_height, full_width);

### SQL
SELECT T1.full_height * T1.full_width, T1.page_number, T1.image_id FROM MenuPage AS T1 INNER JOIN MenuItem AS T2 ON T1.id = T2.menu_page_id INNER JOIN Dish AS T3 ON T2.dish_id = T3.id WHERE T3.name = 'Baked Stuffed Mullet & Sauce Pomard'
### Database Schema
CREATE TABLE Dish
(
    id             INTEGER
            primary key,
    name           TEXT,
    description    TEXT,
    menus_appeared INTEGER,
    times_appeared INTEGER,
    first_appeared INTEGER,
    last_appeared  INTEGER,
    lowest_price   REAL,
    highest_price  REAL
)

CREATE TABLE Menu
(
    id                   INTEGER
            primary key,
    name                 TEXT,
    sponsor              TEXT,
    event                TEXT,
    venue                TEXT,
    place                TEXT,
    physical_description TEXT,
    occasion             TEXT,
    notes                TEXT,
    call_number          TEXT,
    keywords             TEXT,
    language             TEXT,
    date                 DATE,
    location             TEXT,
    location_type        TEXT,
    currency             TEXT,
    currency_symbol      TEXT,
    status               TEXT,
    page_count           INTEGER,
    dish_count           INTEGER
)

CREATE TABLE MenuPage
(
    id          INTEGER
            primary key,
    menu_id     INTEGER,
    page_number INTEGER,
    image_id    REAL,
    full_height INTEGER,
    full_width  INTEGER,
    uuid        TEXT,
    foreign key (menu_id) references Menu(id)
)

CREATE TABLE MenuItem
(
    id           INTEGER
            primary key,
    menu_page_id INTEGER,
    price        REAL,
    high_price   REAL,
    dish_id      INTEGER,
    created_at   TEXT,
    updated_at   TEXT,
    xpos         REAL,
    ypos         REAL,
    foreign key (dish_id) references Dish(id),
    foreign key (menu_page_id) references MenuPage(id)
)

### Question
How many dishes appeared more than once on a menu?

### Hint
appeared more than once on a menu refers to times_appeared > menus_appeared;

### SQL
SELECT COUNT(*) FROM Dish WHERE times_appeared > Dish.menus_appeared
### Database Schema
CREATE TABLE Dish
(
    id             INTEGER
            primary key,
    name           TEXT,
    description    TEXT,
    menus_appeared INTEGER,
    times_appeared INTEGER,
    first_appeared INTEGER,
    last_appeared  INTEGER,
    lowest_price   REAL,
    highest_price  REAL
)

CREATE TABLE Menu
(
    id                   INTEGER
            primary key,
    name                 TEXT,
    sponsor              TEXT,
    event                TEXT,
    venue                TEXT,
    place                TEXT,
    physical_description TEXT,
    occasion             TEXT,
    notes                TEXT,
    call_number          TEXT,
    keywords             TEXT,
    language             TEXT,
    date                 DATE,
    location             TEXT,
    location_type        TEXT,
    currency             TEXT,
    currency_symbol      TEXT,
    status               TEXT,
    page_count           INTEGER,
    dish_count           INTEGER
)

CREATE TABLE MenuPage
(
    id          INTEGER
            primary key,
    menu_id     INTEGER,
    page_number INTEGER,
    image_id    REAL,
    full_height INTEGER,
    full_width  INTEGER,
    uuid        TEXT,
    foreign key (menu_id) references Menu(id)
)

CREATE TABLE MenuItem
(
    id           INTEGER
            primary key,
    menu_page_id INTEGER,
    price        REAL,
    high_price   REAL,
    dish_id      INTEGER,
    created_at   TEXT,
    updated_at   TEXT,
    xpos         REAL,
    ypos         REAL,
    foreign key (dish_id) references Dish(id),
    foreign key (menu_page_id) references MenuPage(id)
)

### Question
How many menus sponsored by Krogs Fiske Restaurant were created in April 2015?

### Hint
sponsored by Krogs Fiske Restaurant refers to sponsor = 'Krogs Fiskerestaurant'; created in April 2015 refers to date like '2015-04%';

### SQL
SELECT COUNT(*) FROM Menu WHERE date LIKE '2015-04%' AND sponsor = 'Krogs Fiskerestaurant'
### Database Schema
CREATE TABLE Dish
(
    id             INTEGER
            primary key,
    name           TEXT,
    description    TEXT,
    menus_appeared INTEGER,
    times_appeared INTEGER,
    first_appeared INTEGER,
    last_appeared  INTEGER,
    lowest_price   REAL,
    highest_price  REAL
)

CREATE TABLE Menu
(
    id                   INTEGER
            primary key,
    name                 TEXT,
    sponsor              TEXT,
    event                TEXT,
    venue                TEXT,
    place                TEXT,
    physical_description TEXT,
    occasion             TEXT,
    notes                TEXT,
    call_number          TEXT,
    keywords             TEXT,
    language             TEXT,
    date                 DATE,
    location             TEXT,
    location_type        TEXT,
    currency             TEXT,
    currency_symbol      TEXT,
    status               TEXT,
    page_count           INTEGER,
    dish_count           INTEGER
)

CREATE TABLE MenuPage
(
    id          INTEGER
            primary key,
    menu_id     INTEGER,
    page_number INTEGER,
    image_id    REAL,
    full_height INTEGER,
    full_width  INTEGER,
    uuid        TEXT,
    foreign key (menu_id) references Menu(id)
)

CREATE TABLE MenuItem
(
    id           INTEGER
            primary key,
    menu_page_id INTEGER,
    price        REAL,
    high_price   REAL,
    dish_id      INTEGER,
    created_at   TEXT,
    updated_at   TEXT,
    xpos         REAL,
    ypos         REAL,
    foreign key (dish_id) references Dish(id),
    foreign key (menu_page_id) references MenuPage(id)
)

### Question
Which dish has the longest history?

### Hint
longest history refers to MAX(SUBTRACT(last_appeared, first_appeared));

### SQL
SELECT name FROM Dish ORDER BY last_appeared - Dish.first_appeared DESC LIMIT 1
### Database Schema
CREATE TABLE Dish
(
    id             INTEGER
            primary key,
    name           TEXT,
    description    TEXT,
    menus_appeared INTEGER,
    times_appeared INTEGER,
    first_appeared INTEGER,
    last_appeared  INTEGER,
    lowest_price   REAL,
    highest_price  REAL
)

CREATE TABLE Menu
(
    id                   INTEGER
            primary key,
    name                 TEXT,
    sponsor              TEXT,
    event                TEXT,
    venue                TEXT,
    place                TEXT,
    physical_description TEXT,
    occasion             TEXT,
    notes                TEXT,
    call_number          TEXT,
    keywords             TEXT,
    language             TEXT,
    date                 DATE,
    location             TEXT,
    location_type        TEXT,
    currency             TEXT,
    currency_symbol      TEXT,
    status               TEXT,
    page_count           INTEGER,
    dish_count           INTEGER
)

CREATE TABLE MenuPage
(
    id          INTEGER
            primary key,
    menu_id     INTEGER,
    page_number INTEGER,
    image_id    REAL,
    full_height INTEGER,
    full_width  INTEGER,
    uuid        TEXT,
    foreign key (menu_id) references Menu(id)
)

CREATE TABLE MenuItem
(
    id           INTEGER
            primary key,
    menu_page_id INTEGER,
    price        REAL,
    high_price   REAL,
    dish_id      INTEGER,
    created_at   TEXT,
    updated_at   TEXT,
    xpos         REAL,
    ypos         REAL,
    foreign key (dish_id) references Dish(id),
    foreign key (menu_page_id) references MenuPage(id)
)

### Question
On 1887-07-21, what was the event that lead to the creation of menu id 21380?

### Hint
On 1887-07-21 refers to date = '1887-07-21';

### SQL
SELECT event FROM Menu WHERE date = '1887-07-21' AND id = 21380