lianghsun/bird-text2sql-bench huggingface dataset:
### 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 down the ID of movies with running time of 3 and average revenue of 1?

### SQL
SELECT T1.movieid FROM movies AS T1 INNER JOIN movies2directors AS T2 ON T1.movieid = T2.movieid INNER JOIN directors AS T3 ON T2.directorid = T3.directorid WHERE T1.runningtime = 3 AND T3.avg_revenue = 1
### 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
UK produced what genre of movies?

### Hint
UK is a country

### SQL
SELECT T2.genre FROM movies AS T1 INNER JOIN movies2directors AS T2 ON T1.movieid = T2.movieid WHERE T1.country = 'UK'
### 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 favourite movie genre for audiences of age 18?

### Hint
The audience and users are the same meaning

### SQL
SELECT T1.genre FROM movies2directors AS T1 INNER JOIN u2base AS T2 ON T1.movieid = T2.movieid INNER JOIN users AS T3 ON T2.userid = T3.userid WHERE T3.age = 18 GROUP BY T1.genre ORDER BY COUNT(T1.movieid) DESC LIMIT 1
### 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 ID of actors that acted in the movies most viewed by audience with occupation 5?

### Hint
The audience and users are the same meaning

### SQL
SELECT T3.actorid FROM users AS T1 INNER JOIN u2base AS T2 ON T1.userid = T2.userid INNER JOIN movies2actors AS T3 ON T2.movieid = T3.movieid WHERE T1.occupation = 5 GROUP BY T2.movieid ORDER BY COUNT(T1.userid) DESC LIMIT 1
### 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
Movies with rating 3 are viewed by audiences in which distinct age group?

### Hint
The audience and users are the same meaning

### SQL
SELECT DISTINCT T2.age FROM u2base AS T1 INNER JOIN users AS T2 ON T1.userid = T2.userid WHERE 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
Action movies are mostly directed by directors of which country?

### SQL
SELECT T3.country FROM movies2directors AS T1 INNER JOIN directors AS T2 ON T1.directorid = T2.directorid INNER JOIN movies AS T3 ON T1.movieid = T3.movieid WHERE T1.genre = 'Action' GROUP BY T3.country ORDER BY COUNT(T3.country) DESC LIMIT 1
### 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
Please list down ID of movies acted by top 5 actors based on actor rating.

### SQL
SELECT T2.movieid FROM actors AS T1 INNER JOIN movies2actors AS T2 ON T1.actorid = T2.actorid GROUP BY T2.actorid ORDER BY AVG(T1.a_quality) DESC LIMIT 5
### 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 down 5 non English adventure movies from UK?

### Hint
not English refers to isEnglish = 'F'; UK is a country

### SQL
SELECT T1.movieid FROM movies2directors AS T1 INNER JOIN movies AS T2 ON T1.movieid = T2.movieid WHERE T2.country = 'UK' AND T1.genre = 'Adventure' AND T2.isEnglish = 'F' LIMIT 5
### 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 percentage of female audiences who viewed movies with rating 2?

### Hint
The audience and users are the same meaning; Female users refers to u_gender = 'F'; Percentage of female users = count(female users) / count(all users); count(all users) = count(female users) + count(male users)

### SQL
SELECT CAST(SUM(IIF(T2.u_gender = 'F', 1, 0)) AS REAL) * 100 / COUNT(T2.userid) 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
What is the difference of female and male audiences in number who viewed horror movies?

### Hint
The audience and users are the same meaning; Difference of female and male audiences = count(female users) - count(male users); Female users refers to u_gender = 'F'; Male users refers to u_gender = 'M'

### SQL
SELECT SUM(IIF(T2.u_gender = 'F', 1, 0)) - SUM(IIF(T2.u_gender = 'M', 1, 0)) FROM u2base AS T1 INNER JOIN users AS T2 ON T1.userid = T2.userid INNER JOIN movies2directors AS T3 ON T3.movieid = T1.movieid WHERE T3.genre = 'horror'
### 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
Please list the genre of the movies that are the newest and is in English.

### Hint
Year contains relative value, higher year value refers to newer date; Year = 4 refers to newest date, Year = 1 refer to oldest date; In English means isEnglish = T

### SQL
SELECT T2.genre FROM movies AS T1 INNER JOIN movies2directors AS T2 ON T1.movieid = T2.movieid WHERE T1.year = 4 AND T1.isEnglish = 'T'