lianghsun/bird-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE city
(
    id        INTEGER not null
            primary key,
    city_name TEXT default NULL
)

CREATE TABLE games
(
    id         INTEGER not null
            primary key,
    games_year INTEGER          default NULL,
    games_name TEXT default NULL,
    season     TEXT default NULL
)

CREATE TABLE games_city
(
    games_id INTEGER default NULL,
    city_id  INTEGER default NULL,
    foreign key (city_id) references city(id),
    foreign key (games_id) references games(id)
)

CREATE TABLE medal
(
    id         INTEGER not null
            primary key,
    medal_name TEXT default NULL
)

CREATE TABLE noc_region
(
    id          INTEGER not null
            primary key,
    noc         TEXT   default NULL,
    region_name TEXT default NULL
)

CREATE TABLE person
(
    id        INTEGER not null
        primary key,
    full_name TEXT default NULL,
    gender    TEXT  default NULL,
    height    INTEGER          default NULL,
    weight    INTEGER          default NULL
)

CREATE TABLE games_competitor
(
    id        INTEGER not null
            primary key,
    games_id  INTEGER default NULL,
    person_id INTEGER default NULL,
    age       INTEGER default NULL,
    foreign key (games_id) references games(id),
    foreign key (person_id) references person(id)
)

CREATE TABLE person_region
(
    person_id INTEGER default NULL,
    region_id INTEGER default NULL,
    foreign key (person_id) references person(id),
    foreign key (region_id) references noc_region(id)
)

CREATE TABLE sport
(
    id         INTEGER not null
            primary key,
    sport_name TEXT default NULL
)

CREATE TABLE event
(
    id         INTEGER not null
            primary key,
    sport_id   INTEGER          default NULL,
    event_name TEXT default NULL,
    foreign key (sport_id) references sport(id)
)

CREATE TABLE competitor_event
(
    event_id      INTEGER default NULL,
    competitor_id INTEGER default NULL,
    medal_id      INTEGER default NULL,
    foreign key (competitor_id) references games_competitor(id),
    foreign key (event_id) references event(id),
    foreign key (medal_id) references medal(id)
)

### Question
Which summer Olympic have the highest and lowest number of participants?

### Hint
the highest number of participants refers to MAX(COUNT(person_id)); the lowest number of participants refers to MIN(COUNT(person_id)); Which summer Olympic refers to games_name where season = 'Summer';

### SQL
SELECT ( SELECT T1.games_name FROM games AS T1 INNER JOIN games_competitor AS T2 ON T1.id = T2.games_id WHERE T1.season = 'Summer' GROUP BY T1.games_year ORDER BY COUNT(T2.person_id) DESC LIMIT 1 ) AS HIGHEST , ( SELECT T1.games_name FROM games AS T1 INNER JOIN games_competitor AS T2 ON T1.id = T2.games_id WHERE T1.season = 'Summer' GROUP BY T1.games_year ORDER BY COUNT(T2.person_id) LIMIT 1 ) AS LOWEST
### Database Schema
CREATE TABLE city
(
    id        INTEGER not null
            primary key,
    city_name TEXT default NULL
)

CREATE TABLE games
(
    id         INTEGER not null
            primary key,
    games_year INTEGER          default NULL,
    games_name TEXT default NULL,
    season     TEXT default NULL
)

CREATE TABLE games_city
(
    games_id INTEGER default NULL,
    city_id  INTEGER default NULL,
    foreign key (city_id) references city(id),
    foreign key (games_id) references games(id)
)

CREATE TABLE medal
(
    id         INTEGER not null
            primary key,
    medal_name TEXT default NULL
)

CREATE TABLE noc_region
(
    id          INTEGER not null
            primary key,
    noc         TEXT   default NULL,
    region_name TEXT default NULL
)

CREATE TABLE person
(
    id        INTEGER not null
        primary key,
    full_name TEXT default NULL,
    gender    TEXT  default NULL,
    height    INTEGER          default NULL,
    weight    INTEGER          default NULL
)

CREATE TABLE games_competitor
(
    id        INTEGER not null
            primary key,
    games_id  INTEGER default NULL,
    person_id INTEGER default NULL,
    age       INTEGER default NULL,
    foreign key (games_id) references games(id),
    foreign key (person_id) references person(id)
)

CREATE TABLE person_region
(
    person_id INTEGER default NULL,
    region_id INTEGER default NULL,
    foreign key (person_id) references person(id),
    foreign key (region_id) references noc_region(id)
)

CREATE TABLE sport
(
    id         INTEGER not null
            primary key,
    sport_name TEXT default NULL
)

CREATE TABLE event
(
    id         INTEGER not null
            primary key,
    sport_id   INTEGER          default NULL,
    event_name TEXT default NULL,
    foreign key (sport_id) references sport(id)
)

CREATE TABLE competitor_event
(
    event_id      INTEGER default NULL,
    competitor_id INTEGER default NULL,
    medal_id      INTEGER default NULL,
    foreign key (competitor_id) references games_competitor(id),
    foreign key (event_id) references event(id),
    foreign key (medal_id) references medal(id)
)

### Question
How many athletes from Malaysia have won a medal?

### Hint
Malaysia refers to region_name = 'Malaysia'; athletes won a medal refer to competitor_id where medal_id <> 4;

### SQL
SELECT COUNT(T3.person_id) FROM medal AS T1 INNER JOIN competitor_event AS T2 ON T1.id = T2.medal_id INNER JOIN games_competitor AS T3 ON T2.competitor_id = T3.id INNER JOIN person_region AS T4 ON T3.person_id = T4.person_id INNER JOIN noc_region AS T5 ON T4.region_id = T5.id WHERE T5.region_name = 'Malaysia' AND T1.id != 4
### Database Schema
CREATE TABLE city
(
    id        INTEGER not null
            primary key,
    city_name TEXT default NULL
)

CREATE TABLE games
(
    id         INTEGER not null
            primary key,
    games_year INTEGER          default NULL,
    games_name TEXT default NULL,
    season     TEXT default NULL
)

CREATE TABLE games_city
(
    games_id INTEGER default NULL,
    city_id  INTEGER default NULL,
    foreign key (city_id) references city(id),
    foreign key (games_id) references games(id)
)

CREATE TABLE medal
(
    id         INTEGER not null
            primary key,
    medal_name TEXT default NULL
)

CREATE TABLE noc_region
(
    id          INTEGER not null
            primary key,
    noc         TEXT   default NULL,
    region_name TEXT default NULL
)

CREATE TABLE person
(
    id        INTEGER not null
        primary key,
    full_name TEXT default NULL,
    gender    TEXT  default NULL,
    height    INTEGER          default NULL,
    weight    INTEGER          default NULL
)

CREATE TABLE games_competitor
(
    id        INTEGER not null
            primary key,
    games_id  INTEGER default NULL,
    person_id INTEGER default NULL,
    age       INTEGER default NULL,
    foreign key (games_id) references games(id),
    foreign key (person_id) references person(id)
)

CREATE TABLE person_region
(
    person_id INTEGER default NULL,
    region_id INTEGER default NULL,
    foreign key (person_id) references person(id),
    foreign key (region_id) references noc_region(id)
)

CREATE TABLE sport
(
    id         INTEGER not null
            primary key,
    sport_name TEXT default NULL
)

CREATE TABLE event
(
    id         INTEGER not null
            primary key,
    sport_id   INTEGER          default NULL,
    event_name TEXT default NULL,
    foreign key (sport_id) references sport(id)
)

CREATE TABLE competitor_event
(
    event_id      INTEGER default NULL,
    competitor_id INTEGER default NULL,
    medal_id      INTEGER default NULL,
    foreign key (competitor_id) references games_competitor(id),
    foreign key (event_id) references event(id),
    foreign key (medal_id) references medal(id)
)

### Question
How many athletes playing in Canoeing fall into overweight BMI category in the 2008 Summer games?

### Hint
fall into overweight BMI category refers to DIVIDE(weight), MULTIPLY(height, height) < 30; Canoeing refers to a sport name; the 2008 Summer games refer to games_name = '2008 Summer';

### SQL
SELECT COUNT(T5.id) FROM sport AS T1 INNER JOIN event AS T2 ON T1.id = T2.sport_id INNER JOIN competitor_event AS T3 ON T2.id = T3.event_id INNER JOIN games_competitor AS T4 ON T3.competitor_id = T4.id INNER JOIN person AS T5 ON T4.person_id = T5.id INNER JOIN games AS T6 ON T4.games_id = T6.id WHERE T1.sport_name = 'Canoeing' AND T6.games_name = '2008 Summer' AND T5.weight * 10000.0 / (T5.height * T5.height) BETWEEN 25.0 AND 30
### Database Schema
CREATE TABLE city
(
    id        INTEGER not null
            primary key,
    city_name TEXT default NULL
)

CREATE TABLE games
(
    id         INTEGER not null
            primary key,
    games_year INTEGER          default NULL,
    games_name TEXT default NULL,
    season     TEXT default NULL
)

CREATE TABLE games_city
(
    games_id INTEGER default NULL,
    city_id  INTEGER default NULL,
    foreign key (city_id) references city(id),
    foreign key (games_id) references games(id)
)

CREATE TABLE medal
(
    id         INTEGER not null
            primary key,
    medal_name TEXT default NULL
)

CREATE TABLE noc_region
(
    id          INTEGER not null
            primary key,
    noc         TEXT   default NULL,
    region_name TEXT default NULL
)

CREATE TABLE person
(
    id        INTEGER not null
        primary key,
    full_name TEXT default NULL,
    gender    TEXT  default NULL,
    height    INTEGER          default NULL,
    weight    INTEGER          default NULL
)

CREATE TABLE games_competitor
(
    id        INTEGER not null
            primary key,
    games_id  INTEGER default NULL,
    person_id INTEGER default NULL,
    age       INTEGER default NULL,
    foreign key (games_id) references games(id),
    foreign key (person_id) references person(id)
)

CREATE TABLE person_region
(
    person_id INTEGER default NULL,
    region_id INTEGER default NULL,
    foreign key (person_id) references person(id),
    foreign key (region_id) references noc_region(id)
)

CREATE TABLE sport
(
    id         INTEGER not null
            primary key,
    sport_name TEXT default NULL
)

CREATE TABLE event
(
    id         INTEGER not null
            primary key,
    sport_id   INTEGER          default NULL,
    event_name TEXT default NULL,
    foreign key (sport_id) references sport(id)
)

CREATE TABLE competitor_event
(
    event_id      INTEGER default NULL,
    competitor_id INTEGER default NULL,
    medal_id      INTEGER default NULL,
    foreign key (competitor_id) references games_competitor(id),
    foreign key (event_id) references event(id),
    foreign key (medal_id) references medal(id)
)

### Question
What is the average height of male athletes playing basketball sport?

### Hint
AVG(height) where sport_name = 'Basketball' and event_name = 'Basketball Men''s';

### SQL
SELECT AVG(T5.height) FROM sport AS T1 INNER JOIN event AS T2 ON T1.id = T2.sport_id INNER JOIN competitor_event AS T3 ON T2.id = T3.event_id INNER JOIN games_competitor AS T4 ON T3.competitor_id = T4.id INNER JOIN person AS T5 ON T4.person_id = T5.id WHERE T1.sport_name = 'Basketball' AND T5.gender = 'M'
### Database Schema
CREATE TABLE city
(
    id        INTEGER not null
            primary key,
    city_name TEXT default NULL
)

CREATE TABLE games
(
    id         INTEGER not null
            primary key,
    games_year INTEGER          default NULL,
    games_name TEXT default NULL,
    season     TEXT default NULL
)

CREATE TABLE games_city
(
    games_id INTEGER default NULL,
    city_id  INTEGER default NULL,
    foreign key (city_id) references city(id),
    foreign key (games_id) references games(id)
)

CREATE TABLE medal
(
    id         INTEGER not null
            primary key,
    medal_name TEXT default NULL
)

CREATE TABLE noc_region
(
    id          INTEGER not null
            primary key,
    noc         TEXT   default NULL,
    region_name TEXT default NULL
)

CREATE TABLE person
(
    id        INTEGER not null
        primary key,
    full_name TEXT default NULL,
    gender    TEXT  default NULL,
    height    INTEGER          default NULL,
    weight    INTEGER          default NULL
)

CREATE TABLE games_competitor
(
    id        INTEGER not null
            primary key,
    games_id  INTEGER default NULL,
    person_id INTEGER default NULL,
    age       INTEGER default NULL,
    foreign key (games_id) references games(id),
    foreign key (person_id) references person(id)
)

CREATE TABLE person_region
(
    person_id INTEGER default NULL,
    region_id INTEGER default NULL,
    foreign key (person_id) references person(id),
    foreign key (region_id) references noc_region(id)
)

CREATE TABLE sport
(
    id         INTEGER not null
            primary key,
    sport_name TEXT default NULL
)

CREATE TABLE event
(
    id         INTEGER not null
            primary key,
    sport_id   INTEGER          default NULL,
    event_name TEXT default NULL,
    foreign key (sport_id) references sport(id)
)

CREATE TABLE competitor_event
(
    event_id      INTEGER default NULL,
    competitor_id INTEGER default NULL,
    medal_id      INTEGER default NULL,
    foreign key (competitor_id) references games_competitor(id),
    foreign key (event_id) references event(id),
    foreign key (medal_id) references medal(id)
)

### Question
How many female athletes are from the Philippines?

### Hint
female athletes refer to id where gender = 'F'; from the Philippines region_name = 'Philippines';

### SQL
SELECT COUNT(T2.person_id) FROM noc_region AS T1 INNER JOIN person_region AS T2 ON T1.id = T2.region_id INNER JOIN person AS T3 ON T2.person_id = T3.id WHERE T1.region_name = 'Philippines' AND T3.gender = 'F'
### Database Schema
CREATE TABLE city
(
    id        INTEGER not null
            primary key,
    city_name TEXT default NULL
)

CREATE TABLE games
(
    id         INTEGER not null
            primary key,
    games_year INTEGER          default NULL,
    games_name TEXT default NULL,
    season     TEXT default NULL
)

CREATE TABLE games_city
(
    games_id INTEGER default NULL,
    city_id  INTEGER default NULL,
    foreign key (city_id) references city(id),
    foreign key (games_id) references games(id)
)

CREATE TABLE medal
(
    id         INTEGER not null
            primary key,
    medal_name TEXT default NULL
)

CREATE TABLE noc_region
(
    id          INTEGER not null
            primary key,
    noc         TEXT   default NULL,
    region_name TEXT default NULL
)

CREATE TABLE person
(
    id        INTEGER not null
        primary key,
    full_name TEXT default NULL,
    gender    TEXT  default NULL,
    height    INTEGER          default NULL,
    weight    INTEGER          default NULL
)

CREATE TABLE games_competitor
(
    id        INTEGER not null
            primary key,
    games_id  INTEGER default NULL,
    person_id INTEGER default NULL,
    age       INTEGER default NULL,
    foreign key (games_id) references games(id),
    foreign key (person_id) references person(id)
)

CREATE TABLE person_region
(
    person_id INTEGER default NULL,
    region_id INTEGER default NULL,
    foreign key (person_id) references person(id),
    foreign key (region_id) references noc_region(id)
)

CREATE TABLE sport
(
    id         INTEGER not null
            primary key,
    sport_name TEXT default NULL
)

CREATE TABLE event
(
    id         INTEGER not null
            primary key,
    sport_id   INTEGER          default NULL,
    event_name TEXT default NULL,
    foreign key (sport_id) references sport(id)
)

CREATE TABLE competitor_event
(
    event_id      INTEGER default NULL,
    competitor_id INTEGER default NULL,
    medal_id      INTEGER default NULL,
    foreign key (competitor_id) references games_competitor(id),
    foreign key (event_id) references event(id),
    foreign key (medal_id) references medal(id)
)

### Question
Which region is Yao Ming from?

### Hint
region refers to region_name;

### SQL
SELECT T1.region_name FROM noc_region AS T1 INNER JOIN person_region AS T2 ON T1.id = T2.region_id INNER JOIN person AS T3 ON T2.person_id = T3.id WHERE T3.full_name = 'Yao Ming'
### Database Schema
CREATE TABLE city
(
    id        INTEGER not null
            primary key,
    city_name TEXT default NULL
)

CREATE TABLE games
(
    id         INTEGER not null
            primary key,
    games_year INTEGER          default NULL,
    games_name TEXT default NULL,
    season     TEXT default NULL
)

CREATE TABLE games_city
(
    games_id INTEGER default NULL,
    city_id  INTEGER default NULL,
    foreign key (city_id) references city(id),
    foreign key (games_id) references games(id)
)

CREATE TABLE medal
(
    id         INTEGER not null
            primary key,
    medal_name TEXT default NULL
)

CREATE TABLE noc_region
(
    id          INTEGER not null
            primary key,
    noc         TEXT   default NULL,
    region_name TEXT default NULL
)

CREATE TABLE person
(
    id        INTEGER not null
        primary key,
    full_name TEXT default NULL,
    gender    TEXT  default NULL,
    height    INTEGER          default NULL,
    weight    INTEGER          default NULL
)

CREATE TABLE games_competitor
(
    id        INTEGER not null
            primary key,
    games_id  INTEGER default NULL,
    person_id INTEGER default NULL,
    age       INTEGER default NULL,
    foreign key (games_id) references games(id),
    foreign key (person_id) references person(id)
)

CREATE TABLE person_region
(
    person_id INTEGER default NULL,
    region_id INTEGER default NULL,
    foreign key (person_id) references person(id),
    foreign key (region_id) references noc_region(id)
)

CREATE TABLE sport
(
    id         INTEGER not null
            primary key,
    sport_name TEXT default NULL
)

CREATE TABLE event
(
    id         INTEGER not null
            primary key,
    sport_id   INTEGER          default NULL,
    event_name TEXT default NULL,
    foreign key (sport_id) references sport(id)
)

CREATE TABLE competitor_event
(
    event_id      INTEGER default NULL,
    competitor_id INTEGER default NULL,
    medal_id      INTEGER default NULL,
    foreign key (competitor_id) references games_competitor(id),
    foreign key (event_id) references event(id),
    foreign key (medal_id) references medal(id)
)

### Question
At what age did Michael Fred Phelps, II join the Olympics?

### Hint
At what age join the Olympics refers to MIN(age);

### SQL
SELECT T2.age FROM person AS T1 INNER JOIN games_competitor AS T2 ON T1.id = T2.person_id WHERE T1.full_name = 'Michael Fred Phelps, II' ORDER BY T2.age LIMIT 1
### Database Schema
CREATE TABLE city
(
    id        INTEGER not null
            primary key,
    city_name TEXT default NULL
)

CREATE TABLE games
(
    id         INTEGER not null
            primary key,
    games_year INTEGER          default NULL,
    games_name TEXT default NULL,
    season     TEXT default NULL
)

CREATE TABLE games_city
(
    games_id INTEGER default NULL,
    city_id  INTEGER default NULL,
    foreign key (city_id) references city(id),
    foreign key (games_id) references games(id)
)

CREATE TABLE medal
(
    id         INTEGER not null
            primary key,
    medal_name TEXT default NULL
)

CREATE TABLE noc_region
(
    id          INTEGER not null
            primary key,
    noc         TEXT   default NULL,
    region_name TEXT default NULL
)

CREATE TABLE person
(
    id        INTEGER not null
        primary key,
    full_name TEXT default NULL,
    gender    TEXT  default NULL,
    height    INTEGER          default NULL,
    weight    INTEGER          default NULL
)

CREATE TABLE games_competitor
(
    id        INTEGER not null
            primary key,
    games_id  INTEGER default NULL,
    person_id INTEGER default NULL,
    age       INTEGER default NULL,
    foreign key (games_id) references games(id),
    foreign key (person_id) references person(id)
)

CREATE TABLE person_region
(
    person_id INTEGER default NULL,
    region_id INTEGER default NULL,
    foreign key (person_id) references person(id),
    foreign key (region_id) references noc_region(id)
)

CREATE TABLE sport
(
    id         INTEGER not null
            primary key,
    sport_name TEXT default NULL
)

CREATE TABLE event
(
    id         INTEGER not null
            primary key,
    sport_id   INTEGER          default NULL,
    event_name TEXT default NULL,
    foreign key (sport_id) references sport(id)
)

CREATE TABLE competitor_event
(
    event_id      INTEGER default NULL,
    competitor_id INTEGER default NULL,
    medal_id      INTEGER default NULL,
    foreign key (competitor_id) references games_competitor(id),
    foreign key (event_id) references event(id),
    foreign key (medal_id) references medal(id)
)

### Question
How many athletes are there in the region where Clara Hughes is from?

### SQL
SELECT COUNT(person_id) FROM person_region WHERE region_id = ( SELECT T1.region_id FROM person_region AS T1 INNER JOIN person AS T2 ON T1.person_id = T2.id WHERE T2.full_name = 'Clara Hughes' )
### Database Schema
CREATE TABLE city
(
    id        INTEGER not null
            primary key,
    city_name TEXT default NULL
)

CREATE TABLE games
(
    id         INTEGER not null
            primary key,
    games_year INTEGER          default NULL,
    games_name TEXT default NULL,
    season     TEXT default NULL
)

CREATE TABLE games_city
(
    games_id INTEGER default NULL,
    city_id  INTEGER default NULL,
    foreign key (city_id) references city(id),
    foreign key (games_id) references games(id)
)

CREATE TABLE medal
(
    id         INTEGER not null
            primary key,
    medal_name TEXT default NULL
)

CREATE TABLE noc_region
(
    id          INTEGER not null
            primary key,
    noc         TEXT   default NULL,
    region_name TEXT default NULL
)

CREATE TABLE person
(
    id        INTEGER not null
        primary key,
    full_name TEXT default NULL,
    gender    TEXT  default NULL,
    height    INTEGER          default NULL,
    weight    INTEGER          default NULL
)

CREATE TABLE games_competitor
(
    id        INTEGER not null
            primary key,
    games_id  INTEGER default NULL,
    person_id INTEGER default NULL,
    age       INTEGER default NULL,
    foreign key (games_id) references games(id),
    foreign key (person_id) references person(id)
)

CREATE TABLE person_region
(
    person_id INTEGER default NULL,
    region_id INTEGER default NULL,
    foreign key (person_id) references person(id),
    foreign key (region_id) references noc_region(id)
)

CREATE TABLE sport
(
    id         INTEGER not null
            primary key,
    sport_name TEXT default NULL
)

CREATE TABLE event
(
    id         INTEGER not null
            primary key,
    sport_id   INTEGER          default NULL,
    event_name TEXT default NULL,
    foreign key (sport_id) references sport(id)
)

CREATE TABLE competitor_event
(
    event_id      INTEGER default NULL,
    competitor_id INTEGER default NULL,
    medal_id      INTEGER default NULL,
    foreign key (competitor_id) references games_competitor(id),
    foreign key (event_id) references event(id),
    foreign key (medal_id) references medal(id)
)

### Question
How many Men's 200 Metres Freestyle events did Ian James Thorpe compete in?

### Hint
Men's 200 Metres Freestyle events refer to event_name = 'Swimming Men''s 200 metres Freestyle'; events compete in refers to event_id;

### SQL
SELECT COUNT(T1.id) FROM person AS T1 INNER JOIN games_competitor AS T2 ON T1.id = T2.person_id INNER JOIN competitor_event AS T3 ON T2.id = T3.competitor_id INNER JOIN event AS T4 ON T3.event_id = T4.id WHERE T1.full_name = 'Ian James Thorpe' AND T4.event_name LIKE 'Swimming Men%s 200 metres Freestyle'