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
What is the highest average rating for action movies made in the USA?

### Hint
USA is a country

### SQL
SELECT AVG(T2.rating) FROM movies AS T1 INNER JOIN u2base AS T2 ON T1.movieid = T2.movieid INNER JOIN movies2directors AS T3 ON T1.movieid = T3.movieid WHERE T1.country = 'USA' AND T3.genre = 'Action' GROUP BY T1.movieid ORDER BY AVG(T2.rating) 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
Among the films directed by directors who direct the best, how many of them have an average rating of over 3.5?

### Hint
d_quality = 5 refers to direct the best, d_quality = 0 refers to direct the worst

### SQL
SELECT COUNT(*) FROM ( SELECT DISTINCT T2.movieid FROM directors AS T1 INNER JOIN movies2directors AS T2 ON T1.directorid = T2.directorid INNER JOIN u2base AS T3 ON T2.movieid = T3.movieid WHERE T1.d_quality = 5 GROUP BY T2.movieid HAVING AVG(T3.rating) > 3.5 ) AS T1
### 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 adventure movie has the highest average rating?

### SQL
SELECT T1.movieid FROM movies2directors AS T1 INNER JOIN u2base AS T2 ON T1.movieid = T2.movieid WHERE T1.genre = 'Adventure' GROUP BY T1.movieid ORDER BY AVG(T2.rating) 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
How many of the users who rate the movie with the id '2462959' are female?

### 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.userid = 2462959 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
What is the most distinct rated movie with a running time of 0?

### SQL
SELECT DISTINCT T1.movieid FROM movies AS T1 INNER JOIN u2base AS T2 ON T1.movieid = T2.movieid WHERE T1.runningtime = 0 AND T2.rating = ( SELECT MAX(rating) FROM u2base )
### 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 ids and ratings of each actors played in the movie with the id 1722327?

### SQL
SELECT T1.actorid, T1.a_quality FROM actors AS T1 INNER JOIN movies2actors AS T2 ON T1.actorid = T2.actorid WHERE T2.movieid = 1722327
### 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 directors with the best quality directed the most films?

### Hint
d_quality = 5 refers to direct the best

### SQL
SELECT T1.directorid FROM directors AS T1 INNER JOIN movies2directors AS T2 ON T1.directorid = T2.directorid WHERE T1.d_quality = 5 GROUP BY T1.directorid ORDER BY COUNT(T2.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
How many drama movie with the rating of 3?

### SQL
SELECT COUNT(DISTINCT T2.movieid) FROM u2base AS T1 INNER JOIN movies2directors AS T2 ON T1.movieid = T2.movieid WHERE T2.genre = 'drama' 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
How many of the movies rated 5 are rated by a user between the ages of 25 and 35?

### SQL
SELECT COUNT(T1.movieid) FROM u2base AS T1 INNER JOIN users AS T2 ON T1.userid = T2.userid WHERE T1.rating = 5 AND T2.age BETWEEN 25 AND 35
### 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 all horror films that have a rating of 1.

### SQL
SELECT T1.movieid FROM u2base AS T1 INNER JOIN movies2directors AS T2 ON T1.movieid = T2.movieid WHERE T1.rating = 1 AND T2.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
List the IDs of all the directors who worked on French films.

### Hint
France is a country

### SQL
SELECT T2.directorid FROM movies AS T1 INNER JOIN movies2directors AS T2 ON T1.movieid = T2.movieid WHERE T1.country = 'France'
### 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 all of the user ids and ages who rated movies with the id 1695219?

### SQL
SELECT T2.userid, T2.age FROM u2base AS T1 INNER JOIN users AS T2 ON T1.userid = T2.userid WHERE T1.movieid = 1695219