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 the top 5 movies from other countries which to language is not in English.

### Hint
not in English can be represented by isEnglish = 'F'

### SQL
SELECT DISTINCT T1.movieid FROM u2base AS T1 INNER JOIN movies AS T2 ON T1.movieid = T2.movieid WHERE T2.country = 'other' 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
Among the best actors, how many of them got a rating of 5 to the movies they starred?

### SQL
SELECT COUNT(T1.actorid) FROM actors AS T1 INNER JOIN movies2actors AS T2 ON T1.actorid = T2.actorid INNER JOIN u2base AS T3 ON T2.movieid = T3.movieid WHERE T1.a_quality = 5 AND T3.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
What is the average rating of the newest movies from France?

### Hint
France is a country; year = 4 represents the newest date.

### SQL
SELECT AVG(T1.rating) FROM u2base AS T1 INNER JOIN movies AS T2 ON T1.movieid = T2.movieid WHERE T2.country = 'france' AND T2.year = 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 most rated UK movies, how many of them has running time of less than 2?

### Hint
UK is a country

### SQL
SELECT COUNT(T1.movieid) FROM u2base AS T1 INNER JOIN movies AS T2 ON T1.movieid = T2.movieid WHERE T2.country = 'UK' AND T2.runningtime < 2 AND 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
List the id of male users who gave ratings of less than 3 to French movies with running time of 2.

### Hint
Male users mean that u_gender = 'M'; France is a country

### SQL
SELECT T1.userid FROM u2base AS T1 INNER JOIN movies AS T2 ON T1.movieid = T2.movieid INNER JOIN users AS T3 ON T1.userid = T3.userid WHERE T2.country = 'France' AND T2.runningtime = 2 AND T1.rating < 3 AND T3.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
Among the worst actresses, how many of them got a rating of more than 3 to the movies they starred?

### Hint
Worst actresses means a_quality is the least; The least a_quality is 0

### SQL
SELECT COUNT(T1.userid) FROM u2base AS T1 INNER JOIN movies2actors AS T2 ON T1.movieid = T2.movieid INNER JOIN actors AS T3 ON T2.actorid = T3.actorid INNER JOIN users AS T4 ON T1.userid = T4.userid WHERE T3.a_quality = 0 AND T1.rating > 3 AND T4.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 ID of audiences that gave the most rating of 5?

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

### SQL
SELECT userid FROM u2base WHERE rating = 5 GROUP BY userid ORDER BY COUNT(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 are the ID of actors that had worked together with director 22397? What was the genre of that movie?

### SQL
SELECT T2.actorid, T4.genre FROM movies AS T1 INNER JOIN movies2actors AS T2 ON T1.movieid = T2.movieid INNER JOIN actors AS T3 ON T2.actorid = T3.actorid INNER JOIN movies2directors AS T4 ON T1.movieid = T4.movieid WHERE T4.directorid = 22397
### 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 the ID of actors and directors in action movies.

### SQL
SELECT T2.actorid, T1.directorid FROM movies2directors AS T1 INNER JOIN movies2actors AS T2 ON T1.movieid = T2.movieid WHERE T1.genre = 'Action'
### 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 acted in the movies of year 4?

### Hint
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 INNER JOIN actors AS T3 ON T2.actorid = T3.actorid WHERE T3.a_gender = 'F' AND T1.year = 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
What are the ID of actors with quality rating of 3 acted in English USA movies?

### Hint
English movies refers to isEnglish = 'T'; USA is a country

### SQL
SELECT T2.actorid 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 T3.a_quality = 3 AND T1.country = 'USA' AND T1.isEnglish = 'T'