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
Which different movies from France got the least ratings?

### Hint
France is a country

### SQL
SELECT DISTINCT T1.movieid FROM u2base AS T1 INNER JOIN movies AS T2 ON T1.movieid = T2.movieid WHERE T2.country = 'France' AND T1.rating = 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
How many female actors have been played a role in any of French or USA movies?

### Hint
French and USA are two countries; Female actors mean that a_gender = 'F'

### SQL
SELECT COUNT(T2.actorid) FROM movies AS T1 INNER JOIN movies2actors AS T2 ON T1.movieid = T2.movieid WHERE T1.country IN ('France', 'USA')
### 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 different actors have played a role in the highest rating movie?

### Hint
highest rating of a movie is 5

### SQL
SELECT COUNT(DISTINCT T2.actorid) FROM u2base AS T1 INNER JOIN movies2actors AS T2 ON T1.movieid = T2.movieid WHERE T1.rating = 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
Which Crime film got the lowest average rating?

### SQL
SELECT T2.movieid FROM u2base AS T2 INNER JOIN movies2directors AS T3 ON T2.movieid = T3.movieid WHERE T3.genre = 'Crime' GROUP BY T2.movieid ORDER BY AVG(T2.rating) 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's the ratio of gender in actors to actress in all the UK movies?

### Hint
UK is a country; Male actors mean that a_gender = 'M'; Female actors mean that a_gender = 'F'; ratio; ratio of gender in actors = count(a_gender = 'M') / a_gender = 'F'

### SQL
SELECT CAST(SUM(IIF(T3.a_gender = 'M', 1, 0)) AS REAL) / SUM(IIF(T3.a_gender = 'F', 1, 0)) FROM movies AS T1 INNER JOIN movies2actors AS T2 ON T1.movieid = T2.movieid INNER JOIN actors AS T3 ON T2.actorid = T3.actorid 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
How many 35-year-old female users gave the movie 1711133 a rating of 3?

### Hint
Female users mean that u_gender = 'F'

### SQL
SELECT COUNT(T1.userid) FROM users AS T1 INNER JOIN u2base AS T2 ON T1.userid = T2.userid WHERE T2.rating = 3 AND T2.movieid = '1711133' AND T1.age = 35 AND T1.u_gender = 'F'
### 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 users have rated 1 each for the UK's second newest movies with a running time of 2?

### Hint
second newest movies refers to year = 2 since year in this database is a relative value, less is the newer

### SQL
SELECT COUNT(T2.userid) FROM movies AS T1 INNER JOIN u2base AS T2 ON T1.movieid = T2.movieid WHERE T1.country = 'UK' AND T1.runningtime = 2 AND T2.rating = 1 AND T1.year = 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
How many unique directors with an average earnings of 2 and a quality of 3 have not made comedy films? List them.

### SQL
SELECT DISTINCT T1.directorid FROM directors AS T1 INNER JOIN movies2directors AS T2 ON T1.directorid = T2.directorid WHERE T1.d_quality = 3 AND T1.avg_revenue = 2 AND T2.genre != 'Comedy'
### 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
Calculate the percentage of female actors and quality 2 who have appeared twice at the casting of the film 1672580.

### Hint
Female actors mean that a_gender = 'F'; percentage can be computed by [cast_num = 2 AND a_quality = 2 in female) / (all female actors)] * 100%

### SQL
SELECT CAST(SUM(IIF(T2.cast_num = 2 AND T1.a_quality = 2, 1, 0)) AS REAL) * 100 / COUNT(T1.actorid) FROM actors AS T1 INNER JOIN movies2actors AS T2 ON T1.actorid = T2.actorid WHERE T2.movieid = 1672580 AND T1.a_gender = 'F'
### 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 of the worst actors are men and how many of the worst actors are women? Indicate your answer in ratio form.

### Hint
The worst actors means a_quality = 0; Men and male share the same meaning; men actors refers to a_gender = 'M'

### SQL
SELECT CAST(SUM(IIF(a_gender = 'M', 1, 0)) AS REAL) / SUM(IIF(a_gender = 'F', 1, 0)) FROM actors WHERE a_quality = 0
### 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
Which actor has appeared in the most films?

### SQL
SELECT actorid FROM movies2actors GROUP BY actorid ORDER BY COUNT(movieid) DESC LIMIT 1