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
How many athletes participated in the 2014 Winter Olympics?

### Hint
athletes refer to person_id; 2014 Winter Olympics refer to games_name = '2014 Winter';

### SQL
SELECT COUNT(T2.person_id) FROM games AS T1 INNER JOIN games_competitor AS T2 ON T1.id = T2.games_id WHERE T1.games_name = '2014 Winter'
### 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 region 151 have won a medal?

### Hint
athletes refer to person_id; region 151 refers to region_id = 151; won a medal refers to medal_id <> 4;

### SQL
SELECT COUNT(T3.person_id) FROM competitor_event AS T1 INNER JOIN games_competitor AS T2 ON T1.competitor_id = T2.id INNER JOIN person_region AS T3 ON T2.person_id = T3.person_id WHERE T3.region_id = 151 AND T1.medal_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 athlete from China participate in the 2016 Summer Olympics?

### Hint
athletes from China refer to person_id where region_name = 'China'; the 2016 Summer Olympics refer to games_name = '2016 Summer';

### SQL
SELECT COUNT(T3.id) FROM games AS T1 INNER JOIN games_competitor AS T2 ON T1.id = T2.games_id INNER JOIN person AS T3 ON T2.person_id = T3.id INNER JOIN person_region AS T4 ON T3.id = T4.person_id INNER JOIN noc_region AS T5 ON T4.region_id = T5.id WHERE T1.games_name = '2016 Summer' AND T5.region_name = 'China'
### 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 gold medals were given to the winners in the Ice Hockey Men's Ice Hockey event?

### Hint
gold medals given to the winners refer to competitor_id where medal_name = 'Gold'; Ice Hockey Men's Ice Hockey refers to event_name = 'Ice Hockey Men''s Ice Hockey';

### SQL
SELECT COUNT(T2.competitor_id) FROM event AS T1 INNER JOIN competitor_event AS T2 ON T1.id = T2.event_id WHERE T1.event_name LIKE 'Ice Hockey Men%s Ice Hockey' AND T2.medal_id = 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
Which region has the highest medal number?

### Hint
region refers to region_name; the highest medal number refers to MAX(COUNT(medal_id <> 4));

### SQL
SELECT T5.region_name 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 T1.id != 4 GROUP BY T5.region_name ORDER BY COUNT(T2.competitor_id) DESC 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
List out all the gold medalist winners in cycling sport.

### Hint
gold medalist winners refer to full_name where medal_name = 'Gold'; cycling sport refers to sport_name = 'Cycling';

### SQL
SELECT DISTINCT T5.full_name FROM event AS T1 INNER JOIN competitor_event AS T2 ON T1.id = T2.event_id INNER JOIN games_competitor AS T3 ON T2.competitor_id = T3.id INNER JOIN sport AS T4 ON T1.sport_id = T4.id INNER JOIN person AS T5 ON T3.person_id = T5.id WHERE T4.sport_name = 'Cycling' AND T2.medal_id = 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
List out all the medals won by Lee Chong Wei.

### Hint
medals refer to medal_id where medal_id <> 4;

### SQL
SELECT DISTINCT T1.medal_name 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 AS T4 ON T3.person_id = T4.id WHERE T4.full_name = 'Lee Chong Wei' AND T2.medal_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
What is the percentage of female athletes below 20s who participated in the 2002 Winter Olympic?

### Hint
DIVIDE(COUNT(person_id where gender = 'F' and age < 20), COUNT(person_id)) as percentage where games_name = '2002 Winter';

### SQL
SELECT CAST(COUNT(CASE WHEN T3.gender = 'F' THEN 1 ELSE NULL END) AS REAL) * 100 / COUNT(T2.person_id) FROM games AS T1 INNER JOIN games_competitor AS T2 ON T1.id = T2.games_id INNER JOIN person AS T3 ON T2.person_id = T3.id WHERE T1.games_name = '2002 Winter' AND T2.age < 20
### 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 ratio male to female athletes in the 2012 Summer Olympic?

### Hint
DIVIDE(COUNT(gender = 'M'), COUNT(gender = 'F')) where games_name = '2012 Summer';

### SQL
SELECT CAST(COUNT(CASE WHEN T3.gender = 'M' THEN 1 ELSE NULL END) AS REAL) / COUNT(CASE WHEN T3.gender = 'F' THEN 1 ELSE NULL END) FROM games AS T1 INNER JOIN games_competitor AS T2 ON T1.id = T2.games_id INNER JOIN person AS T3 ON T2.person_id = T3.id WHERE T1.games_name = '2012 Summer'