lianghsun/bird-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE "torrents"
(
    groupName     TEXT,
    totalSnatched INTEGER,
    artist        TEXT,
    groupYear     INTEGER,
    releaseType   TEXT,
    groupId       INTEGER,
    id            INTEGER
        constraint torrents_pk
            primary key
)

CREATE TABLE "tags"
(
    "index" INTEGER
        constraint tags_pk
            primary key,
    id      INTEGER
        constraint tags_torrents_id_fk
            references torrents,
    tag     TEXT
)

### Question
How many singles were released in 1979?

### Hint
releaseType = 'single'; groupYear = 1979;

### SQL
SELECT COUNT(releaseType) FROM torrents WHERE releaseType LIKE 'single' AND groupYear = 1979
### Database Schema
CREATE TABLE "torrents"
(
    groupName     TEXT,
    totalSnatched INTEGER,
    artist        TEXT,
    groupYear     INTEGER,
    releaseType   TEXT,
    groupId       INTEGER,
    id            INTEGER
        constraint torrents_pk
            primary key
)

CREATE TABLE "tags"
(
    "index" INTEGER
        constraint tags_pk
            primary key,
    id      INTEGER
        constraint tags_torrents_id_fk
            references torrents,
    tag     TEXT
)

### Question
In 1980, how many singles were released by sugar daddy?

### Hint
sugar daddy is an artist; releaseType = 'single'; groupYear = 1980;

### SQL
SELECT COUNT(releaseType) FROM torrents WHERE artist LIKE 'sugar daddy' AND releaseType LIKE 'Single' AND groupYear = 1980
### Database Schema
CREATE TABLE "torrents"
(
    groupName     TEXT,
    totalSnatched INTEGER,
    artist        TEXT,
    groupYear     INTEGER,
    releaseType   TEXT,
    groupId       INTEGER,
    id            INTEGER
        constraint torrents_pk
            primary key
)

CREATE TABLE "tags"
(
    "index" INTEGER
        constraint tags_pk
            primary key,
    id      INTEGER
        constraint tags_torrents_id_fk
            references torrents,
    tag     TEXT
)

### Question
How many christmas albums were released in 2004?

### Hint
album refers to releaseType; groupYear = 2004; tag = 'christmas';

### SQL
SELECT COUNT(T1.id) FROM torrents AS T1 INNER JOIN tags AS T2 ON T1.id = T2.id WHERE T2.tag = 'christmas' AND T1.groupYear = 2004 AND T1.releaseType LIKE 'album'
### Database Schema
CREATE TABLE "torrents"
(
    groupName     TEXT,
    totalSnatched INTEGER,
    artist        TEXT,
    groupYear     INTEGER,
    releaseType   TEXT,
    groupId       INTEGER,
    id            INTEGER
        constraint torrents_pk
            primary key
)

CREATE TABLE "tags"
(
    "index" INTEGER
        constraint tags_pk
            primary key,
    id      INTEGER
        constraint tags_torrents_id_fk
            references torrents,
    tag     TEXT
)

### Question
Please list all tags of kurtis blow from 2000 to 2010.

### Hint
kurtis blow is an artist; from 2000 to 2010 refers to groupYear between 2000 and 2010;

### SQL
SELECT T2.tag FROM torrents AS T1 INNER JOIN tags AS T2 ON T1.id = T2.id WHERE T1.groupYear BETWEEN 2000 AND 2010 AND T1.artist LIKE 'kurtis blow'
### Database Schema
CREATE TABLE "torrents"
(
    groupName     TEXT,
    totalSnatched INTEGER,
    artist        TEXT,
    groupYear     INTEGER,
    releaseType   TEXT,
    groupId       INTEGER,
    id            INTEGER
        constraint torrents_pk
            primary key
)

CREATE TABLE "tags"
(
    "index" INTEGER
        constraint tags_pk
            primary key,
    id      INTEGER
        constraint tags_torrents_id_fk
            references torrents,
    tag     TEXT
)

### Question
Which album title and tag that millie jackson released in 1980?

### Hint
millie jackson is an artist; album title refers to groupName where releaseType = 'album'; groupYear = 1980;

### SQL
SELECT T1.groupName, T2.tag FROM torrents AS T1 INNER JOIN tags AS T2 ON T1.id = T2.id WHERE T1.groupYear = 1980 AND T1.artist LIKE 'millie jackson' AND T1.releaseType LIKE 'album'
### Database Schema
CREATE TABLE "torrents"
(
    groupName     TEXT,
    totalSnatched INTEGER,
    artist        TEXT,
    groupYear     INTEGER,
    releaseType   TEXT,
    groupId       INTEGER,
    id            INTEGER
        constraint torrents_pk
            primary key
)

CREATE TABLE "tags"
(
    "index" INTEGER
        constraint tags_pk
            primary key,
    id      INTEGER
        constraint tags_torrents_id_fk
            references torrents,
    tag     TEXT
)

### Question
Please list all release titles whose tag is jazz in 2005.

### Hint
release titles refer to groupName; groupYear = 2005;

### SQL
SELECT T1.groupName FROM torrents AS T1 INNER JOIN tags AS T2 ON T1.id = T2.id WHERE T1.groupYear = 2005 AND T2.tag LIKE 'jazz'
### Database Schema
CREATE TABLE "torrents"
(
    groupName     TEXT,
    totalSnatched INTEGER,
    artist        TEXT,
    groupYear     INTEGER,
    releaseType   TEXT,
    groupId       INTEGER,
    id            INTEGER
        constraint torrents_pk
            primary key
)

CREATE TABLE "tags"
(
    "index" INTEGER
        constraint tags_pk
            primary key,
    id      INTEGER
        constraint tags_torrents_id_fk
            references torrents,
    tag     TEXT
)

### Question
From 1980 to 2000, which artist had the most disco releases?

### Hint
From 1980 to 2000 refers to groupYear between 1980 and 2000; tag = 'disco'; the most releases refer to MAX(COUNT(id));

### SQL
SELECT T1.artist FROM torrents AS T1 INNER JOIN tags AS T2 ON T1.id = T2.id WHERE T1.groupYear BETWEEN 1980 AND 2000 AND T2.tag LIKE 'disco' GROUP BY T1.artist ORDER BY COUNT(T2.tag) DESC LIMIT 1
### Database Schema
CREATE TABLE "torrents"
(
    groupName     TEXT,
    totalSnatched INTEGER,
    artist        TEXT,
    groupYear     INTEGER,
    releaseType   TEXT,
    groupId       INTEGER,
    id            INTEGER
        constraint torrents_pk
            primary key
)

CREATE TABLE "tags"
(
    "index" INTEGER
        constraint tags_pk
            primary key,
    id      INTEGER
        constraint tags_torrents_id_fk
            references torrents,
    tag     TEXT
)

### Question
Which artists have released singles with the tag 1970s?

### Hint
releaseType = 'single';

### SQL
SELECT T1.artist FROM torrents AS T1 INNER JOIN tags AS T2 ON T1.id = T2.id WHERE T1.releaseType = 'single' AND T2.tag LIKE '1970s'
### Database Schema
CREATE TABLE "torrents"
(
    groupName     TEXT,
    totalSnatched INTEGER,
    artist        TEXT,
    groupYear     INTEGER,
    releaseType   TEXT,
    groupId       INTEGER,
    id            INTEGER
        constraint torrents_pk
            primary key
)

CREATE TABLE "tags"
(
    "index" INTEGER
        constraint tags_pk
            primary key,
    id      INTEGER
        constraint tags_torrents_id_fk
            references torrents,
    tag     TEXT
)

### Question
From 1979 to 1982, what was the percentage of united.states albums out of total albums were released?

### Hint
From 1979 to 1982 refers to groupYear between 1979 and 1982; United States refer to tag; albums refer to releaseType; DIVIDE(COUNT(releaseType = 'album' where tag = 'united.states' and groupYear between 1979 and 1982), COUNT(releaseType = 'album' where groupYear between 1979 and 1982)) as percentage;

### SQL
SELECT CAST(SUM(CASE WHEN T2.tag LIKE 'united.states' THEN 1 ELSE 0 END) AS REAL) * 100 / COUNT(T1.releaseType) FROM torrents AS T1 INNER JOIN tags AS T2 ON T1.id = T2.id WHERE T1.groupYear BETWEEN 1979 AND 1982 AND T1.releaseType LIKE 'album'
### Database Schema
CREATE TABLE users
(
    userid     INTEGER default 0 not null
        primary key,
    age        TEXT    not null,
    u_gender   TEXT    not null,
    occupation TEXT   not null
)

CREATE TABLE "directors"
(
    directorid  INTEGER not null
        primary key,
    d_quality   INTEGER not null,
    avg_revenue INTEGER not null
)

CREATE TABLE "actors"
(
    actorid   INTEGER not null
        primary key,
    a_gender  TEXT    not null,
    a_quality INTEGER not null
)

CREATE TABLE "movies"
(
    movieid     INTEGER default 0 not null
        primary key,
    year        INTEGER           not null,
    isEnglish   TEXT              not null,
    country     TEXT              not null,
    runningtime INTEGER           not null
)

CREATE TABLE "movies2actors"
(
    movieid  INTEGER not null
        references movies
            on update cascade on delete cascade,
    actorid  INTEGER not null
        references actors
            on update cascade on delete cascade,
    cast_num INTEGER not null,
    primary key (movieid, actorid)
)

CREATE TABLE "movies2directors"
(
    movieid    INTEGER not null
        references movies
            on update cascade on delete cascade,
    directorid INTEGER not null
        references directors
            on update cascade on delete cascade,
    genre      TEXT    not null,
    primary key (movieid, directorid)
)

CREATE TABLE "u2base"
(
    userid  INTEGER default 0 not null
        references users
            on update cascade on delete cascade,
    movieid INTEGER           not null
        references movies
            on update cascade on delete cascade,
    rating  TEXT              not null,
    primary key (userid, movieid)
)

### Question
List the different director IDs of the movies whose user rating is more than 4.

### SQL
SELECT DISTINCT T2.directorid FROM u2base AS T1 INNER JOIN movies2directors AS T2 ON T1.movieid = T2.movieid WHERE T1.rating > 4
### Database Schema
CREATE TABLE users
(
    userid     INTEGER default 0 not null
        primary key,
    age        TEXT    not null,
    u_gender   TEXT    not null,
    occupation TEXT   not null
)

CREATE TABLE "directors"
(
    directorid  INTEGER not null
        primary key,
    d_quality   INTEGER not null,
    avg_revenue INTEGER not null
)

CREATE TABLE "actors"
(
    actorid   INTEGER not null
        primary key,
    a_gender  TEXT    not null,
    a_quality INTEGER not null
)

CREATE TABLE "movies"
(
    movieid     INTEGER default 0 not null
        primary key,
    year        INTEGER           not null,
    isEnglish   TEXT              not null,
    country     TEXT              not null,
    runningtime INTEGER           not null
)

CREATE TABLE "movies2actors"
(
    movieid  INTEGER not null
        references movies
            on update cascade on delete cascade,
    actorid  INTEGER not null
        references actors
            on update cascade on delete cascade,
    cast_num INTEGER not null,
    primary key (movieid, actorid)
)

CREATE TABLE "movies2directors"
(
    movieid    INTEGER not null
        references movies
            on update cascade on delete cascade,
    directorid INTEGER not null
        references directors
            on update cascade on delete cascade,
    genre      TEXT    not null,
    primary key (movieid, directorid)
)

CREATE TABLE "u2base"
(
    userid  INTEGER default 0 not null
        references users
            on update cascade on delete cascade,
    movieid INTEGER           not null
        references movies
            on update cascade on delete cascade,
    rating  TEXT              not null,
    primary key (userid, movieid)
)

### Question
Among the users who gave a rating of 5, how many of them are male?

### Hint
Male users mean that u_gender = 'M'

### SQL
SELECT COUNT(T1.userid) FROM u2base AS T1 INNER JOIN users AS T2 ON T1.userid = T2.userid WHERE T1.rating = 5 AND T2.u_gender = 'M'
### Database Schema
CREATE TABLE users
(
    userid     INTEGER default 0 not null
        primary key,
    age        TEXT    not null,
    u_gender   TEXT    not null,
    occupation TEXT   not null
)

CREATE TABLE "directors"
(
    directorid  INTEGER not null
        primary key,
    d_quality   INTEGER not null,
    avg_revenue INTEGER not null
)

CREATE TABLE "actors"
(
    actorid   INTEGER not null
        primary key,
    a_gender  TEXT    not null,
    a_quality INTEGER not null
)

CREATE TABLE "movies"
(
    movieid     INTEGER default 0 not null
        primary key,
    year        INTEGER           not null,
    isEnglish   TEXT              not null,
    country     TEXT              not null,
    runningtime INTEGER           not null
)

CREATE TABLE "movies2actors"
(
    movieid  INTEGER not null
        references movies
            on update cascade on delete cascade,
    actorid  INTEGER not null
        references actors
            on update cascade on delete cascade,
    cast_num INTEGER not null,
    primary key (movieid, actorid)
)

CREATE TABLE "movies2directors"
(
    movieid    INTEGER not null
        references movies
            on update cascade on delete cascade,
    directorid INTEGER not null
        references directors
            on update cascade on delete cascade,
    genre      TEXT    not null,
    primary key (movieid, directorid)
)

CREATE TABLE "u2base"
(
    userid  INTEGER default 0 not null
        references users
            on update cascade on delete cascade,
    movieid INTEGER           not null
        references movies
            on update cascade on delete cascade,
    rating  TEXT              not null,
    primary key (userid, movieid)
)

### Question
List the genres of the movies which actor id 851 is the star.

### SQL
SELECT T2.genre FROM movies2actors AS T1 INNER JOIN movies2directors AS T2 ON T1.movieid = T2.movieid INNER JOIN actors AS T3 ON T1.actorid = T3.actorid WHERE T3.actorid = 851
### Database Schema
CREATE TABLE users
(
    userid     INTEGER default 0 not null
        primary key,
    age        TEXT    not null,
    u_gender   TEXT    not null,
    occupation TEXT   not null
)

CREATE TABLE "directors"
(
    directorid  INTEGER not null
        primary key,
    d_quality   INTEGER not null,
    avg_revenue INTEGER not null
)

CREATE TABLE "actors"
(
    actorid   INTEGER not null
        primary key,
    a_gender  TEXT    not null,
    a_quality INTEGER not null
)

CREATE TABLE "movies"
(
    movieid     INTEGER default 0 not null
        primary key,
    year        INTEGER           not null,
    isEnglish   TEXT              not null,
    country     TEXT              not null,
    runningtime INTEGER           not null
)

CREATE TABLE "movies2actors"
(
    movieid  INTEGER not null
        references movies
            on update cascade on delete cascade,
    actorid  INTEGER not null
        references actors
            on update cascade on delete cascade,
    cast_num INTEGER not null,
    primary key (movieid, actorid)
)

CREATE TABLE "movies2directors"
(
    movieid    INTEGER not null
        references movies
            on update cascade on delete cascade,
    directorid INTEGER not null
        references directors
            on update cascade on delete cascade,
    genre      TEXT    not null,
    primary key (movieid, directorid)
)

CREATE TABLE "u2base"
(
    userid  INTEGER default 0 not null
        references users
            on update cascade on delete cascade,
    movieid INTEGER           not null
        references movies
            on update cascade on delete cascade,
    rating  TEXT              not null,
    primary key (userid, movieid)
)

### Question
How many movies from the USA which user rating is less than 3?

### SQL
SELECT COUNT(T1.movieid) FROM u2base AS T1 INNER JOIN movies AS T2 ON T1.movieid = T2.movieid WHERE T2.country = 'USA' AND T1.rating < 3
### Database Schema
CREATE TABLE users
(
    userid     INTEGER default 0 not null
        primary key,
    age        TEXT    not null,
    u_gender   TEXT    not null,
    occupation TEXT   not null
)

CREATE TABLE "directors"
(
    directorid  INTEGER not null
        primary key,
    d_quality   INTEGER not null,
    avg_revenue INTEGER not null
)

CREATE TABLE "actors"
(
    actorid   INTEGER not null
        primary key,
    a_gender  TEXT    not null,
    a_quality INTEGER not null
)

CREATE TABLE "movies"
(
    movieid     INTEGER default 0 not null
        primary key,
    year        INTEGER           not null,
    isEnglish   TEXT              not null,
    country     TEXT              not null,
    runningtime INTEGER           not null
)

CREATE TABLE "movies2actors"
(
    movieid  INTEGER not null
        references movies
            on update cascade on delete cascade,
    actorid  INTEGER not null
        references actors
            on update cascade on delete cascade,
    cast_num INTEGER not null,
    primary key (movieid, actorid)
)

CREATE TABLE "movies2directors"
(
    movieid    INTEGER not null
        references movies
            on update cascade on delete cascade,
    directorid INTEGER not null
        references directors
            on update cascade on delete cascade,
    genre      TEXT    not null,
    primary key (movieid, directorid)
)

CREATE TABLE "u2base"
(
    userid  INTEGER default 0 not null
        references users
            on update cascade on delete cascade,
    movieid INTEGER           not null
        references movies
            on update cascade on delete cascade,
    rating  TEXT              not null,
    primary key (userid, movieid)
)

### Question
Among the movies from France, how many of them are drama?

### Hint
France a one country

### SQL
SELECT COUNT(T1.movieid) FROM movies2directors AS T1 INNER JOIN movies AS T2 ON T1.movieid = T2.movieid WHERE T2.country = 'France' AND T1.genre = 'drama'
### Database Schema
CREATE TABLE users
(
    userid     INTEGER default 0 not null
        primary key,
    age        TEXT    not null,
    u_gender   TEXT    not null,
    occupation TEXT   not null
)

CREATE TABLE "directors"
(
    directorid  INTEGER not null
        primary key,
    d_quality   INTEGER not null,
    avg_revenue INTEGER not null
)

CREATE TABLE "actors"
(
    actorid   INTEGER not null
        primary key,
    a_gender  TEXT    not null,
    a_quality INTEGER not null
)

CREATE TABLE "movies"
(
    movieid     INTEGER default 0 not null
        primary key,
    year        INTEGER           not null,
    isEnglish   TEXT              not null,
    country     TEXT              not null,
    runningtime INTEGER           not null
)

CREATE TABLE "movies2actors"
(
    movieid  INTEGER not null
        references movies
            on update cascade on delete cascade,
    actorid  INTEGER not null
        references actors
            on update cascade on delete cascade,
    cast_num INTEGER not null,
    primary key (movieid, actorid)
)

CREATE TABLE "movies2directors"
(
    movieid    INTEGER not null
        references movies
            on update cascade on delete cascade,
    directorid INTEGER not null
        references directors
            on update cascade on delete cascade,
    genre      TEXT    not null,
    primary key (movieid, directorid)
)

CREATE TABLE "u2base"
(
    userid  INTEGER default 0 not null
        references users
            on update cascade on delete cascade,
    movieid INTEGER           not null
        references movies
            on update cascade on delete cascade,
    rating  TEXT              not null,
    primary key (userid, movieid)
)

### Question
What is the average occupation of users whose ratings are not more than 2 ?

### SQL
SELECT AVG(T2.occupation) FROM u2base AS T1 INNER JOIN users AS T2 ON T1.userid = T2.userid WHERE T1.rating < 2
### Database Schema
CREATE TABLE users
(
    userid     INTEGER default 0 not null
        primary key,
    age        TEXT    not null,
    u_gender   TEXT    not null,
    occupation TEXT   not null
)

CREATE TABLE "directors"
(
    directorid  INTEGER not null
        primary key,
    d_quality   INTEGER not null,
    avg_revenue INTEGER not null
)

CREATE TABLE "actors"
(
    actorid   INTEGER not null
        primary key,
    a_gender  TEXT    not null,
    a_quality INTEGER not null
)

CREATE TABLE "movies"
(
    movieid     INTEGER default 0 not null
        primary key,
    year        INTEGER           not null,
    isEnglish   TEXT              not null,
    country     TEXT              not null,
    runningtime INTEGER           not null
)

CREATE TABLE "movies2actors"
(
    movieid  INTEGER not null
        references movies
            on update cascade on delete cascade,
    actorid  INTEGER not null
        references actors
            on update cascade on delete cascade,
    cast_num INTEGER not null,
    primary key (movieid, actorid)
)

CREATE TABLE "movies2directors"
(
    movieid    INTEGER not null
        references movies
            on update cascade on delete cascade,
    directorid INTEGER not null
        references directors
            on update cascade on delete cascade,
    genre      TEXT    not null,
    primary key (movieid, directorid)
)

CREATE TABLE "u2base"
(
    userid  INTEGER default 0 not null
        references users
            on update cascade on delete cascade,
    movieid INTEGER           not null
        references movies
            on update cascade on delete cascade,
    rating  TEXT              not null,
    primary key (userid, movieid)
)

### Question
List the top 10 USA movies, by descending order, from the highest to the lowest, the user rating.

### Hint
USA is a country

### SQL
SELECT T1.movieid FROM u2base AS T1 INNER JOIN movies AS T2 ON T1.movieid = T2.movieid WHERE T2.country = 'USA' GROUP BY T1.movieid ORDER BY AVG(T1.rating) DESC LIMIT 10
### Database Schema
CREATE TABLE users
(
    userid     INTEGER default 0 not null
        primary key,
    age        TEXT    not null,
    u_gender   TEXT    not null,
    occupation TEXT   not null
)

CREATE TABLE "directors"
(
    directorid  INTEGER not null
        primary key,
    d_quality   INTEGER not null,
    avg_revenue INTEGER not null
)

CREATE TABLE "actors"
(
    actorid   INTEGER not null
        primary key,
    a_gender  TEXT    not null,
    a_quality INTEGER not null
)

CREATE TABLE "movies"
(
    movieid     INTEGER default 0 not null
        primary key,
    year        INTEGER           not null,
    isEnglish   TEXT              not null,
    country     TEXT              not null,
    runningtime INTEGER           not null
)

CREATE TABLE "movies2actors"
(
    movieid  INTEGER not null
        references movies
            on update cascade on delete cascade,
    actorid  INTEGER not null
        references actors
            on update cascade on delete cascade,
    cast_num INTEGER not null,
    primary key (movieid, actorid)
)

CREATE TABLE "movies2directors"
(
    movieid    INTEGER not null
        references movies
            on update cascade on delete cascade,
    directorid INTEGER not null
        references directors
            on update cascade on delete cascade,
    genre      TEXT    not null,
    primary key (movieid, directorid)
)

CREATE TABLE "u2base"
(
    userid  INTEGER default 0 not null
        references users
            on update cascade on delete cascade,
    movieid INTEGER           not null
        references movies
            on update cascade on delete cascade,
    rating  TEXT              not null,
    primary key (userid, movieid)
)

### Question
What is the average number of casts of movies that are from the USA?

### Hint
USA is a country

### SQL
SELECT AVG(T2.cast_num) FROM movies AS T1 INNER JOIN movies2actors AS T2 ON T1.movieid = T2.movieid WHERE T1.country = 'USA'