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
What are the sales made by the games in Japan region?

### Hint
sales = SUM(num_sales); Japan region refers to region_name = 'Japan';

### SQL
SELECT SUM(CASE WHEN T2.region_name = 'Japan' THEN T1.num_sales ELSE 0 END) AS nums FROM region_sales AS T1 INNER JOIN region AS T2 ON T1.region_id = T2.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
How many game publisher IDs have published games on the X360 platform?

### Hint
X360 refers to platform_name = 'X360';

### SQL
SELECT COUNT(T1.game_publisher_id) FROM game_platform AS T1 INNER JOIN platform AS T2 ON T1.platform_id = T2.id WHERE T2.platform_name = 'X360'
### 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
State the name of the platforms for games released in 2000.

### Hint
name of the platforms refers to platform_name; released in 2000 refers to release_year = 2000;

### SQL
SELECT DISTINCT T2.platform_name FROM game_platform AS T1 INNER JOIN platform AS T2 ON T1.platform_id = T2.id WHERE T1.release_year = 2000
### 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
Find out the difference between the number of publishers who released the games on the PS3 and X360.

### Hint
difference = SUBTRACT(SUM(platform_name = 'PS3'), SUM(platform_name = 'X360')); PS3 refers to platform_name = 'PS3'; X360 refers to platform_name = 'X360';

### SQL
SELECT COUNT(CASE WHEN T2.platform_name = 'PS3' THEN T1.game_publisher_id ELSE NULL END) - COUNT(CASE WHEN T2.platform_name = 'X360' THEN T1.game_publisher_id ELSE NULL END) FROM game_platform AS T1 INNER JOIN platform AS T2 ON T1.platform_id = T2.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 are the game IDs of the games published by Bethesda Softworks?

### Hint
Bethesda Softworks refers to publisher_name = 'Bethesda Softworks';

### SQL
SELECT T1.game_id FROM game_publisher AS T1 INNER JOIN publisher AS T2 ON T1.publisher_id = T2.id WHERE T2.publisher_name = 'Bethesda Softworks'
### 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 total number of IDs for the game published by Capcom and Sony Computer Entertainment.

### Hint
Capcom refers to publisher_name = 'Capcom';  Sony Computer Entertainment refers to publisher_name = 'Sony Computer Entertainment';

### SQL
SELECT COUNT(DISTINCT T1.game_id) FROM game_publisher AS T1 INNER JOIN publisher AS T2 ON T1.publisher_id = T2.id WHERE T2.publisher_name IN ('Capcom', 'Sony Computer Entertainment')
### 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 genre of the game "Grand Theft Auto V"?

### Hint
genre refers to genre_name; "Grand Theft Auto V" refers to game_name = 'Grand Theft Auto V';

### SQL
SELECT T2.genre_name FROM game AS T1 INNER JOIN genre AS T2 ON T1.genre_id = T2.id WHERE T1.game_name = 'Grand Theft Auto V'
### 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
List down the names of the games in the racing genre.

### Hint
name of games refers to game_name; racing genre refers to genre_name = 'Racing';

### SQL
SELECT T1.game_name FROM game AS T1 INNER JOIN genre AS T2 ON T1.genre_id = T2.id WHERE T2.genre_name = 'Racing'
### 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 number of games in the fighting genre.

### Hint
fighting genre refers to genre_name = 'Fighting';

### SQL
SELECT COUNT(T1.id) FROM game AS T1 INNER JOIN genre AS T2 ON T1.genre_id = T2.id WHERE T2.genre_name = 'Fighting'
### 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 genres of games published by the publisher with an ID of 464?

### Hint
genres of games refers to genre_name; publisher with an ID of 464 refers to publisher_id = 464;

### SQL
SELECT DISTINCT T2.genre_name FROM game AS T1 INNER JOIN genre AS T2 ON T1.genre_id = T2.id INNER JOIN game_publisher AS T3 ON T1.id = T3.game_id WHERE T3.publisher_id = 464
### 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
Find out the platform of the game "Final Fantasy XIII-2".

### Hint
platform of the game refers to platform_name; "Final Fantasy XIII-2" refers to game_name = 'Final Fantasy XIII-2';

### SQL
SELECT T4.platform_name FROM game AS T1 INNER JOIN game_publisher AS T2 ON T1.id = T2.game_id INNER JOIN game_platform AS T3 ON T2.id = T3.game_publisher_id INNER JOIN platform AS T4 ON T3.platform_id = T4.id WHERE T1.game_name = 'Final Fantasy XIII-2'
### 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 total sales made by the games released in 2000.

### Hint
total sales = SUM(num_sales); released in 2000 refers to release_year = 2000;

### SQL
SELECT SUM(T1.num_sales) FROM region_sales AS T1 INNER JOIN game_platform AS T2 ON T1.game_platform_id = T2.id WHERE T2.release_year = 2000