lianghsun/bird-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE country
(
    origin  INTEGER
            primary key,
    country TEXT
)

CREATE TABLE price
(
    ID    INTEGER
            primary key,
    price REAL
)

CREATE TABLE data
(
    ID           INTEGER
            primary key,
    mpg          REAL,
    cylinders    INTEGER,
    displacement REAL,
    horsepower   INTEGER,
    weight       INTEGER,
    acceleration REAL,
    model        INTEGER,
    car_name     TEXT,
    foreign key (ID) references price(ID)
)

CREATE TABLE production
(
    ID         INTEGER,
    model_year INTEGER,
    country    INTEGER,
    primary key (ID, model_year),
    foreign key (country) references country(origin),
    foreign key (ID) references data(ID),
    foreign key (ID) references price(ID)
)

### Question
Calculate the percentage of American cars among all cars.

### Hint
American car refers to country = 'USA'; percentage = divide(count(ID where country = 'USA'), count(ID)) * 100%

### SQL
SELECT CAST(SUM(CASE WHEN T3.country = 'USA' THEN 1 ELSE 0 END) AS REAL) * 100 / COUNT(*) FROM data AS T1 INNER JOIN production AS T2 ON T1.ID = T2.ID INNER JOIN country AS T3 ON T3.origin = T2.country
### Database Schema
CREATE TABLE country
(
    origin  INTEGER
            primary key,
    country TEXT
)

CREATE TABLE price
(
    ID    INTEGER
            primary key,
    price REAL
)

CREATE TABLE data
(
    ID           INTEGER
            primary key,
    mpg          REAL,
    cylinders    INTEGER,
    displacement REAL,
    horsepower   INTEGER,
    weight       INTEGER,
    acceleration REAL,
    model        INTEGER,
    car_name     TEXT,
    foreign key (ID) references price(ID)
)

CREATE TABLE production
(
    ID         INTEGER,
    model_year INTEGER,
    country    INTEGER,
    primary key (ID, model_year),
    foreign key (country) references country(origin),
    foreign key (ID) references data(ID),
    foreign key (ID) references price(ID)
)

### Question
What is the average weight of Japanese cars with 4 cylinders that were produced from 1975 to 1980?

### Hint
Japanese car refers to country = 'Japan'; with 4 cylinders refers to cylinders = 4; produced from 1975 to 1980 refers to model_year BETWEEN 1975 AND 1980; average weight = avg(weight)

### SQL
SELECT AVG(T1.weight) FROM data AS T1 INNER JOIN production AS T2 ON T1.ID = T2.ID INNER JOIN country AS T3 ON T3.origin = T2.country WHERE T2.model_year BETWEEN 1975 AND 1980 AND T1.cylinders = 4 AND T3.country = 'Japan'
### 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 more games were sold on game platform ID 50 than on game platform ID 51 in region ID 1?

### Hint
result = subtract(sum(num_sales where game_platform_id = 50), sum(num_sales where game_platform_id = 51))

### SQL
SELECT (SUM(CASE WHEN T.game_platform_id = 50 THEN T.num_sales ELSE 0 END) - SUM(CASE WHEN T.game_platform_id = 51 THEN T.num_sales ELSE 0 END)) * 100000 AS nums 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
Please list all the games that have the same game genre as 3D Lemmings.

### Hint
game refers to game_name; 3D Lemmings refers to game_name = '3D Lemmings'

### SQL
SELECT T1.game_name FROM game AS T1 WHERE T1.genre_id = ( SELECT T.genre_id FROM game AS T WHERE T.game_name = '3D Lemmings' )
### 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 action games are there in total?

### Hint
action game refers to genre_name = 'Action'

### SQL
SELECT COUNT(T1.id) FROM game AS T1 INNER JOIN genre AS T2 ON T1.genre_id = T2.id WHERE T2.genre_name = 'Action'
### 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 3D Lemmings?

### Hint
genre refers to genre_name; 3D Lemmings refers to game_name = '3D Lemmings'

### SQL
SELECT T2.genre_name FROM game AS T1 INNER JOIN genre AS T2 ON T1.genre_id = T2.id WHERE T1.game_name = '3D Lemmings'
### 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 3D Lemmings?

### Hint
publisher refers to publisher_name; 3D Lemmings refers to game_name = '3D Lemmings'

### SQL
SELECT T3.publisher_name FROM game AS T1 INNER JOIN game_publisher AS T2 ON T1.id = T2.game_id INNER JOIN publisher AS T3 ON T2.publisher_id = T3.id WHERE T1.game_name = '3D Lemmings'
### 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
Please list the names of all the games published by 10TACLE Studios.

### Hint
name refers to game_name; published by 10TACLE Studios refers to publisher_name = '10TACLE Studios'

### SQL
SELECT T1.game_name FROM game AS T1 INNER JOIN game_publisher AS T2 ON T1.id = T2.game_id INNER JOIN publisher AS T3 ON T2.publisher_id = T3.id WHERE T3.publisher_name = '10TACLE Studios'
### 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
Among the games published by 10TACLE Studios, how many of them are puzzles?

### Hint
published by 10TACLE Studios refers to publisher_name = '10TACLE Studios'; puzzle refers to genre_name = 'Puzzle'

### SQL
SELECT COUNT(T1.id) FROM game AS T1 INNER JOIN game_publisher AS T2 ON T1.id = T2.game_id INNER JOIN publisher AS T3 ON T2.publisher_id = T3.id INNER JOIN genre AS T4 ON T1.genre_id = T4.id WHERE T4.genre_name = 'Puzzle' AND T3.publisher_name = '10TACLE Studios'
### 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
Please list the names of all the games published by 10TACLE Studios and are puzzles.

### Hint
name refers to game_name; published by 10TACLE Studios refers to publisher_name = '10TACLE Studios'; puzzle refers to genre_name = 'Puzzle'

### SQL
SELECT T1.game_name FROM game AS T1 INNER JOIN game_publisher AS T2 ON T1.id = T2.game_id INNER JOIN publisher AS T3 ON T2.publisher_id = T3.id INNER JOIN genre AS T4 ON T1.genre_id = T4.id WHERE T3.publisher_name = '10TACLE Studios' AND T4.genre_name = 'Puzzle'
### 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 publisher has published the most games?

### Hint
publisher refers to publisher_name; the most games refers to max(count(game_id))

### SQL
SELECT T.publisher_name FROM ( SELECT T2.publisher_name, COUNT(DISTINCT T2.id) FROM game_publisher AS T1 INNER JOIN publisher AS T2 ON T1.publisher_id = T2.id GROUP BY T1.publisher_id ORDER BY COUNT(T2.id) 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
On which platform was Panzer Tactics released in 2007?

### Hint
platform refers to platform_name; Panzer Tactics refers to game_name = 'Panzer Tactics'; released in 2007 refers to release_year = 2007

### SQL
SELECT T5.platform_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 INNER JOIN game_platform AS T4 ON T1.id = T4.game_publisher_id INNER JOIN platform AS T5 ON T4.platform_id = T5.id WHERE T3.game_name = 'Panzer Tactics' AND T4.release_year = 2007