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

CREATE TABLE game
(
    id        INTEGER not null
            primary key,
    genre_id  INTEGER          default NULL,
    game_name TEXT default NULL,
    foreign key (genre_id) references genre(id)
)

CREATE TABLE platform
(
    id            INTEGER not null
            primary key,
    platform_name TEXT default NULL
)

CREATE TABLE publisher
(
    id             INTEGER not null
            primary key,
    publisher_name TEXT default NULL
)

CREATE TABLE game_publisher
(
    id           INTEGER not null
            primary key,
    game_id      INTEGER default NULL,
    publisher_id INTEGER default NULL,
    foreign key (game_id) references game(id),
    foreign key (publisher_id) references publisher(id)
)

CREATE TABLE game_platform
(
    id                INTEGER not null
            primary key,
    game_publisher_id INTEGER default NULL,
    platform_id       INTEGER default NULL,
    release_year      INTEGER default NULL,
    foreign key (game_publisher_id) references game_publisher(id),
    foreign key (platform_id) references platform(id)
)

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

CREATE TABLE region_sales
(
    region_id        INTEGER           default NULL,
    game_platform_id INTEGER           default NULL,
    num_sales        REAL default NULL,
    foreign key (game_platform_id) references game_platform(id),
    foreign key (region_id) references region(id)
)

### Question
Calculate the average game sales for the PS2 platform.

### Hint
average = AVG(MULTIPLY(num_sales), 100000); PS2 refers to platform_name = 'PS2';

### SQL
SELECT SUM(T3.num_sales * 100000) / COUNT(T1.id) FROM platform AS T1 INNER JOIN game_platform AS T2 ON T1.id = T2.platform_id INNER JOIN region_sales AS T3 ON T2.id = T3.game_platform_id WHERE T1.platform_name = 'PS2'
### Database Schema
CREATE TABLE genre
(
    id         INTEGER not null
            primary key,
    genre_name TEXT default NULL
)

CREATE TABLE game
(
    id        INTEGER not null
            primary key,
    genre_id  INTEGER          default NULL,
    game_name TEXT default NULL,
    foreign key (genre_id) references genre(id)
)

CREATE TABLE platform
(
    id            INTEGER not null
            primary key,
    platform_name TEXT default NULL
)

CREATE TABLE publisher
(
    id             INTEGER not null
            primary key,
    publisher_name TEXT default NULL
)

CREATE TABLE game_publisher
(
    id           INTEGER not null
            primary key,
    game_id      INTEGER default NULL,
    publisher_id INTEGER default NULL,
    foreign key (game_id) references game(id),
    foreign key (publisher_id) references publisher(id)
)

CREATE TABLE game_platform
(
    id                INTEGER not null
            primary key,
    game_publisher_id INTEGER default NULL,
    platform_id       INTEGER default NULL,
    release_year      INTEGER default NULL,
    foreign key (game_publisher_id) references game_publisher(id),
    foreign key (platform_id) references platform(id)
)

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

CREATE TABLE region_sales
(
    region_id        INTEGER           default NULL,
    game_platform_id INTEGER           default NULL,
    num_sales        REAL default NULL,
    foreign key (game_platform_id) references game_platform(id),
    foreign key (region_id) references region(id)
)

### Question
Calculate the percentage of games published by 'Brash Entertainment'?

### Hint
percentage = MULTIPLY(DIVIDE(SUM(publisher_name = 'Brash Entertainment'), COUNT(game_id)), 100.0); 'Brash Entertainment' refers to publisher_name = 'Brash Entertainment';

### SQL
SELECT CAST(COUNT(CASE WHEN T1.publisher_name = 'Brash Entertainment' THEN T2.game_id ELSE NULL END) AS REAL) * 100 / COUNT(T2.game_id) FROM publisher AS T1 INNER JOIN game_publisher AS T2 ON T1.id = T2.publisher_id
### Database Schema
CREATE TABLE genre
(
    id         INTEGER not null
            primary key,
    genre_name TEXT default NULL
)

CREATE TABLE game
(
    id        INTEGER not null
            primary key,
    genre_id  INTEGER          default NULL,
    game_name TEXT default NULL,
    foreign key (genre_id) references genre(id)
)

CREATE TABLE platform
(
    id            INTEGER not null
            primary key,
    platform_name TEXT default NULL
)

CREATE TABLE publisher
(
    id             INTEGER not null
            primary key,
    publisher_name TEXT default NULL
)

CREATE TABLE game_publisher
(
    id           INTEGER not null
            primary key,
    game_id      INTEGER default NULL,
    publisher_id INTEGER default NULL,
    foreign key (game_id) references game(id),
    foreign key (publisher_id) references publisher(id)
)

CREATE TABLE game_platform
(
    id                INTEGER not null
            primary key,
    game_publisher_id INTEGER default NULL,
    platform_id       INTEGER default NULL,
    release_year      INTEGER default NULL,
    foreign key (game_publisher_id) references game_publisher(id),
    foreign key (platform_id) references platform(id)
)

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

CREATE TABLE region_sales
(
    region_id        INTEGER           default NULL,
    game_platform_id INTEGER           default NULL,
    num_sales        REAL default NULL,
    foreign key (game_platform_id) references game_platform(id),
    foreign key (region_id) references region(id)
)

### Question
What is the total number of games sold in region ID 1?

### Hint
total number of games sold = MULTIPLY(SUM(num_sales), 100000);

### SQL
SELECT SUM(T.num_sales * 100000) FROM region_sales AS T WHERE T.region_id = 1
### Database Schema
CREATE TABLE genre
(
    id         INTEGER not null
            primary key,
    genre_name TEXT default NULL
)

CREATE TABLE game
(
    id        INTEGER not null
            primary key,
    genre_id  INTEGER          default NULL,
    game_name TEXT default NULL,
    foreign key (genre_id) references genre(id)
)

CREATE TABLE platform
(
    id            INTEGER not null
            primary key,
    platform_name TEXT default NULL
)

CREATE TABLE publisher
(
    id             INTEGER not null
            primary key,
    publisher_name TEXT default NULL
)

CREATE TABLE game_publisher
(
    id           INTEGER not null
            primary key,
    game_id      INTEGER default NULL,
    publisher_id INTEGER default NULL,
    foreign key (game_id) references game(id),
    foreign key (publisher_id) references publisher(id)
)

CREATE TABLE game_platform
(
    id                INTEGER not null
            primary key,
    game_publisher_id INTEGER default NULL,
    platform_id       INTEGER default NULL,
    release_year      INTEGER default NULL,
    foreign key (game_publisher_id) references game_publisher(id),
    foreign key (platform_id) references platform(id)
)

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

CREATE TABLE region_sales
(
    region_id        INTEGER           default NULL,
    game_platform_id INTEGER           default NULL,
    num_sales        REAL default NULL,
    foreign key (game_platform_id) references game_platform(id),
    foreign key (region_id) references region(id)
)

### Question
How many FIFA games are there across all platforms?

### Hint
FIFA games refers to game_name LIKE '%FIFA%';

### SQL
SELECT COUNT(*) FROM ( SELECT T.game_name FROM game AS T WHERE T.game_name LIKE '%FIFA%' )
### Database Schema
CREATE TABLE genre
(
    id         INTEGER not null
            primary key,
    genre_name TEXT default NULL
)

CREATE TABLE game
(
    id        INTEGER not null
            primary key,
    genre_id  INTEGER          default NULL,
    game_name TEXT default NULL,
    foreign key (genre_id) references genre(id)
)

CREATE TABLE platform
(
    id            INTEGER not null
            primary key,
    platform_name TEXT default NULL
)

CREATE TABLE publisher
(
    id             INTEGER not null
            primary key,
    publisher_name TEXT default NULL
)

CREATE TABLE game_publisher
(
    id           INTEGER not null
            primary key,
    game_id      INTEGER default NULL,
    publisher_id INTEGER default NULL,
    foreign key (game_id) references game(id),
    foreign key (publisher_id) references publisher(id)
)

CREATE TABLE game_platform
(
    id                INTEGER not null
            primary key,
    game_publisher_id INTEGER default NULL,
    platform_id       INTEGER default NULL,
    release_year      INTEGER default NULL,
    foreign key (game_publisher_id) references game_publisher(id),
    foreign key (platform_id) references platform(id)
)

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

CREATE TABLE region_sales
(
    region_id        INTEGER           default NULL,
    game_platform_id INTEGER           default NULL,
    num_sales        REAL default NULL,
    foreign key (game_platform_id) references game_platform(id),
    foreign key (region_id) references region(id)
)

### Question
Which platform is the most popular in Europe?

### Hint
platform that is the most popular refers to platform_name WHERE MAX(num_sales); in Europe refers to region_name = 'Europe' ;

### SQL
SELECT T.platform_name FROM ( SELECT T4.platform_name FROM region AS T1 INNER JOIN region_sales AS T2 ON T1.id = T2.region_id INNER JOIN game_platform AS T3 ON T2.game_platform_id = T3.id INNER JOIN platform AS T4 ON T3.platform_id = T4.id WHERE T1.region_name = 'Europe' ORDER BY T2.num_sales DESC LIMIT 1 ) t
### Database Schema
CREATE TABLE genre
(
    id         INTEGER not null
            primary key,
    genre_name TEXT default NULL
)

CREATE TABLE game
(
    id        INTEGER not null
            primary key,
    genre_id  INTEGER          default NULL,
    game_name TEXT default NULL,
    foreign key (genre_id) references genre(id)
)

CREATE TABLE platform
(
    id            INTEGER not null
            primary key,
    platform_name TEXT default NULL
)

CREATE TABLE publisher
(
    id             INTEGER not null
            primary key,
    publisher_name TEXT default NULL
)

CREATE TABLE game_publisher
(
    id           INTEGER not null
            primary key,
    game_id      INTEGER default NULL,
    publisher_id INTEGER default NULL,
    foreign key (game_id) references game(id),
    foreign key (publisher_id) references publisher(id)
)

CREATE TABLE game_platform
(
    id                INTEGER not null
            primary key,
    game_publisher_id INTEGER default NULL,
    platform_id       INTEGER default NULL,
    release_year      INTEGER default NULL,
    foreign key (game_publisher_id) references game_publisher(id),
    foreign key (platform_id) references platform(id)
)

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

CREATE TABLE region_sales
(
    region_id        INTEGER           default NULL,
    game_platform_id INTEGER           default NULL,
    num_sales        REAL default NULL,
    foreign key (game_platform_id) references game_platform(id),
    foreign key (region_id) references region(id)
)

### Question
Who is the publisher of the game 2002 FIFA World Cup?

### Hint
who is the publisher refers to publisher_name; 2002 FIFA World Cup refers to game_name = '2002 FIFA World Cup';

### SQL
SELECT T2.publisher_name FROM game_publisher AS T1 INNER JOIN publisher AS T2 ON T1.publisher_id = T2.id INNER JOIN game AS T3 ON T1.game_id = T3.id WHERE T3.game_name = '2002 FIFA World Cup'
### Database Schema
CREATE TABLE genre
(
    id         INTEGER not null
            primary key,
    genre_name TEXT default NULL
)

CREATE TABLE game
(
    id        INTEGER not null
            primary key,
    genre_id  INTEGER          default NULL,
    game_name TEXT default NULL,
    foreign key (genre_id) references genre(id)
)

CREATE TABLE platform
(
    id            INTEGER not null
            primary key,
    platform_name TEXT default NULL
)

CREATE TABLE publisher
(
    id             INTEGER not null
            primary key,
    publisher_name TEXT default NULL
)

CREATE TABLE game_publisher
(
    id           INTEGER not null
            primary key,
    game_id      INTEGER default NULL,
    publisher_id INTEGER default NULL,
    foreign key (game_id) references game(id),
    foreign key (publisher_id) references publisher(id)
)

CREATE TABLE game_platform
(
    id                INTEGER not null
            primary key,
    game_publisher_id INTEGER default NULL,
    platform_id       INTEGER default NULL,
    release_year      INTEGER default NULL,
    foreign key (game_publisher_id) references game_publisher(id),
    foreign key (platform_id) references platform(id)
)

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

CREATE TABLE region_sales
(
    region_id        INTEGER           default NULL,
    game_platform_id INTEGER           default NULL,
    num_sales        REAL default NULL,
    foreign key (game_platform_id) references game_platform(id),
    foreign key (region_id) references region(id)
)

### Question
What platform is the game 3Xtreme available on?

### Hint
what platform refers to platform_name; 3Xtreme refers to game_name = '3Xtreme';

### SQL
SELECT T2.platform_name FROM game_platform AS T1 INNER JOIN platform AS T2 ON T1.platform_id = T2.id INNER JOIN game_publisher AS T3 ON T1.game_publisher_id = T3.id INNER JOIN game AS T4 ON T3.game_id = T4.id WHERE T4.game_name = '3Xtreme'
### Database Schema
CREATE TABLE genre
(
    id         INTEGER not null
            primary key,
    genre_name TEXT default NULL
)

CREATE TABLE game
(
    id        INTEGER not null
            primary key,
    genre_id  INTEGER          default NULL,
    game_name TEXT default NULL,
    foreign key (genre_id) references genre(id)
)

CREATE TABLE platform
(
    id            INTEGER not null
            primary key,
    platform_name TEXT default NULL
)

CREATE TABLE publisher
(
    id             INTEGER not null
            primary key,
    publisher_name TEXT default NULL
)

CREATE TABLE game_publisher
(
    id           INTEGER not null
            primary key,
    game_id      INTEGER default NULL,
    publisher_id INTEGER default NULL,
    foreign key (game_id) references game(id),
    foreign key (publisher_id) references publisher(id)
)

CREATE TABLE game_platform
(
    id                INTEGER not null
            primary key,
    game_publisher_id INTEGER default NULL,
    platform_id       INTEGER default NULL,
    release_year      INTEGER default NULL,
    foreign key (game_publisher_id) references game_publisher(id),
    foreign key (platform_id) references platform(id)
)

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

CREATE TABLE region_sales
(
    region_id        INTEGER           default NULL,
    game_platform_id INTEGER           default NULL,
    num_sales        REAL default NULL,
    foreign key (game_platform_id) references game_platform(id),
    foreign key (region_id) references region(id)
)

### Question
What genre is the game 2010 FIFA World Cup South Africa?

### Hint
genre refers to genre_name; 2010 FIFA World Cup South Africa refers to game_name = '2010 FIFA World Cup South Africa';

### SQL
SELECT T2.genre_name FROM game AS T1 INNER JOIN genre AS T2 ON T1.genre_id = T2.id WHERE T1.game_name = '2010 FIFA World Cup South Africa'
### Database Schema
CREATE TABLE genre
(
    id         INTEGER not null
            primary key,
    genre_name TEXT default NULL
)

CREATE TABLE game
(
    id        INTEGER not null
            primary key,
    genre_id  INTEGER          default NULL,
    game_name TEXT default NULL,
    foreign key (genre_id) references genre(id)
)

CREATE TABLE platform
(
    id            INTEGER not null
            primary key,
    platform_name TEXT default NULL
)

CREATE TABLE publisher
(
    id             INTEGER not null
            primary key,
    publisher_name TEXT default NULL
)

CREATE TABLE game_publisher
(
    id           INTEGER not null
            primary key,
    game_id      INTEGER default NULL,
    publisher_id INTEGER default NULL,
    foreign key (game_id) references game(id),
    foreign key (publisher_id) references publisher(id)
)

CREATE TABLE game_platform
(
    id                INTEGER not null
            primary key,
    game_publisher_id INTEGER default NULL,
    platform_id       INTEGER default NULL,
    release_year      INTEGER default NULL,
    foreign key (game_publisher_id) references game_publisher(id),
    foreign key (platform_id) references platform(id)
)

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

CREATE TABLE region_sales
(
    region_id        INTEGER           default NULL,
    game_platform_id INTEGER           default NULL,
    num_sales        REAL default NULL,
    foreign key (game_platform_id) references game_platform(id),
    foreign key (region_id) references region(id)
)

### Question
Which region has the highest number of games sold on all platforms?

### Hint
which region refers to region_name; highest number of games sold on all platforms refers to MAX(SUM(num_sales));

### SQL
SELECT T.region_name FROM ( SELECT T2.region_name, SUM(T1.num_sales) FROM region_sales AS T1 INNER JOIN region AS T2 ON T1.region_id = T2.id INNER JOIN game_platform AS T3 ON T1.game_platform_id = T3.id INNER JOIN platform AS T4 ON T3.platform_id = T4.id GROUP BY T4.platform_name ORDER BY SUM(T1.num_sales) DESC LIMIT 1 ) t
### Database Schema
CREATE TABLE genre
(
    id         INTEGER not null
            primary key,
    genre_name TEXT default NULL
)

CREATE TABLE game
(
    id        INTEGER not null
            primary key,
    genre_id  INTEGER          default NULL,
    game_name TEXT default NULL,
    foreign key (genre_id) references genre(id)
)

CREATE TABLE platform
(
    id            INTEGER not null
            primary key,
    platform_name TEXT default NULL
)

CREATE TABLE publisher
(
    id             INTEGER not null
            primary key,
    publisher_name TEXT default NULL
)

CREATE TABLE game_publisher
(
    id           INTEGER not null
            primary key,
    game_id      INTEGER default NULL,
    publisher_id INTEGER default NULL,
    foreign key (game_id) references game(id),
    foreign key (publisher_id) references publisher(id)
)

CREATE TABLE game_platform
(
    id                INTEGER not null
            primary key,
    game_publisher_id INTEGER default NULL,
    platform_id       INTEGER default NULL,
    release_year      INTEGER default NULL,
    foreign key (game_publisher_id) references game_publisher(id),
    foreign key (platform_id) references platform(id)
)

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

CREATE TABLE region_sales
(
    region_id        INTEGER           default NULL,
    game_platform_id INTEGER           default NULL,
    num_sales        REAL default NULL,
    foreign key (game_platform_id) references game_platform(id),
    foreign key (region_id) references region(id)
)

### Question
How many games were sold on PS3 platform in Japan?

### Hint
how many games = MULTIPLY(SUM(num_sales), 100000); PS3 refers to platform_name = 'PS3'; Japan refers to region_name = 'Japan';

### SQL
SELECT SUM(T1.num_sales * 100000) FROM region_sales AS T1 INNER JOIN region AS T2 ON T1.region_id = T2.id INNER JOIN game_platform AS T3 ON T1.game_platform_id = T3.id INNER JOIN platform AS T4 ON T3.platform_id = T4.id WHERE T2.region_name = 'Japan' AND T4.platform_name = 'PS3'
### Database Schema
CREATE TABLE genre
(
    id         INTEGER not null
            primary key,
    genre_name TEXT default NULL
)

CREATE TABLE game
(
    id        INTEGER not null
            primary key,
    genre_id  INTEGER          default NULL,
    game_name TEXT default NULL,
    foreign key (genre_id) references genre(id)
)

CREATE TABLE platform
(
    id            INTEGER not null
            primary key,
    platform_name TEXT default NULL
)

CREATE TABLE publisher
(
    id             INTEGER not null
            primary key,
    publisher_name TEXT default NULL
)

CREATE TABLE game_publisher
(
    id           INTEGER not null
            primary key,
    game_id      INTEGER default NULL,
    publisher_id INTEGER default NULL,
    foreign key (game_id) references game(id),
    foreign key (publisher_id) references publisher(id)
)

CREATE TABLE game_platform
(
    id                INTEGER not null
            primary key,
    game_publisher_id INTEGER default NULL,
    platform_id       INTEGER default NULL,
    release_year      INTEGER default NULL,
    foreign key (game_publisher_id) references game_publisher(id),
    foreign key (platform_id) references platform(id)
)

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

CREATE TABLE region_sales
(
    region_id        INTEGER           default NULL,
    game_platform_id INTEGER           default NULL,
    num_sales        REAL default NULL,
    foreign key (game_platform_id) references game_platform(id),
    foreign key (region_id) references region(id)
)

### Question
What are the names of games that were released in 2007?

### Hint
names of games refers to game_name; released in 2007 refers to release_year = 2007;

### SQL
SELECT T3.game_name FROM game_platform AS T1 INNER JOIN game_publisher AS T2 ON T1.game_publisher_id = T2.id INNER JOIN game AS T3 ON T2.game_id = T3.id WHERE T1.release_year = 2007
### Database Schema
CREATE TABLE genre
(
    id         INTEGER not null
            primary key,
    genre_name TEXT default NULL
)

CREATE TABLE game
(
    id        INTEGER not null
            primary key,
    genre_id  INTEGER          default NULL,
    game_name TEXT default NULL,
    foreign key (genre_id) references genre(id)
)

CREATE TABLE platform
(
    id            INTEGER not null
            primary key,
    platform_name TEXT default NULL
)

CREATE TABLE publisher
(
    id             INTEGER not null
            primary key,
    publisher_name TEXT default NULL
)

CREATE TABLE game_publisher
(
    id           INTEGER not null
            primary key,
    game_id      INTEGER default NULL,
    publisher_id INTEGER default NULL,
    foreign key (game_id) references game(id),
    foreign key (publisher_id) references publisher(id)
)

CREATE TABLE game_platform
(
    id                INTEGER not null
            primary key,
    game_publisher_id INTEGER default NULL,
    platform_id       INTEGER default NULL,
    release_year      INTEGER default NULL,
    foreign key (game_publisher_id) references game_publisher(id),
    foreign key (platform_id) references platform(id)
)

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

CREATE TABLE region_sales
(
    region_id        INTEGER           default NULL,
    game_platform_id INTEGER           default NULL,
    num_sales        REAL default NULL,
    foreign key (game_platform_id) references game_platform(id),
    foreign key (region_id) references region(id)
)

### Question
How many games were published by Activision?

### Hint
Activision refers to publisher_name = 'Activision';

### SQL
SELECT COUNT(DISTINCT T3.id) FROM game_publisher AS T1 INNER JOIN publisher AS T2 ON T1.publisher_id = T2.id INNER JOIN game AS T3 ON T1.game_id = T3.id WHERE T2.publisher_name = 'Activision'