lianghsun/bird-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE country
(
    id           INTEGER not null
            primary key,
    country_name TEXT default NULL
)

CREATE TABLE ranking_system
(
    id          INTEGER not null
            primary key,
    system_name TEXT default NULL
)

CREATE TABLE ranking_criteria
(
    id                INTEGER not null
            primary key,
    ranking_system_id INTEGER          default NULL,
    criteria_name     TEXT default NULL,
    foreign key (ranking_system_id) references ranking_system(id)
)

CREATE TABLE university
(
    id              INTEGER not null
            primary key,
    country_id      INTEGER          default NULL,
    university_name TEXT default NULL,
    foreign key (country_id) references country(id)
)

CREATE TABLE university_ranking_year
(
    university_id       INTEGER default NULL,
    ranking_criteria_id INTEGER default NULL,
    year                INTEGER default NULL,
    score               INTEGER default NULL,
    foreign key (ranking_criteria_id) references ranking_criteria(id),
    foreign key (university_id) references university(id)
)

CREATE TABLE university_year
(
    university_id              INTEGER           default NULL,
    year                       INTEGER           default NULL,
    num_students               INTEGER           default NULL,
    student_staff_ratio        REAL default NULL,
    pct_international_students INTEGER           default NULL,
    pct_female_students        INTEGER           default NULL,
    foreign key (university_id) references university(id)
)

### Question
List down all universities that scored below 50.

### Hint
scored below 50 refers to score < 50; all universities refers to university_name;

### SQL
SELECT DISTINCT T2.university_name FROM university_ranking_year AS T1 INNER JOIN university AS T2 ON T1.university_id = T2.id WHERE T1.score < 50
### Database Schema
CREATE TABLE country
(
    id           INTEGER not null
            primary key,
    country_name TEXT default NULL
)

CREATE TABLE ranking_system
(
    id          INTEGER not null
            primary key,
    system_name TEXT default NULL
)

CREATE TABLE ranking_criteria
(
    id                INTEGER not null
            primary key,
    ranking_system_id INTEGER          default NULL,
    criteria_name     TEXT default NULL,
    foreign key (ranking_system_id) references ranking_system(id)
)

CREATE TABLE university
(
    id              INTEGER not null
            primary key,
    country_id      INTEGER          default NULL,
    university_name TEXT default NULL,
    foreign key (country_id) references country(id)
)

CREATE TABLE university_ranking_year
(
    university_id       INTEGER default NULL,
    ranking_criteria_id INTEGER default NULL,
    year                INTEGER default NULL,
    score               INTEGER default NULL,
    foreign key (ranking_criteria_id) references ranking_criteria(id),
    foreign key (university_id) references university(id)
)

CREATE TABLE university_year
(
    university_id              INTEGER           default NULL,
    year                       INTEGER           default NULL,
    num_students               INTEGER           default NULL,
    student_staff_ratio        REAL default NULL,
    pct_international_students INTEGER           default NULL,
    pct_female_students        INTEGER           default NULL,
    foreign key (university_id) references university(id)
)

### Question
How many universities are located in Japan?

### Hint
located in Japan refers to country_name = 'Japan';

### SQL
SELECT COUNT(*) FROM university AS T1 INNER JOIN country AS T2 ON T1.country_id = T2.id WHERE T2.country_name = 'Japan'
### Database Schema
CREATE TABLE country
(
    id           INTEGER not null
            primary key,
    country_name TEXT default NULL
)

CREATE TABLE ranking_system
(
    id          INTEGER not null
            primary key,
    system_name TEXT default NULL
)

CREATE TABLE ranking_criteria
(
    id                INTEGER not null
            primary key,
    ranking_system_id INTEGER          default NULL,
    criteria_name     TEXT default NULL,
    foreign key (ranking_system_id) references ranking_system(id)
)

CREATE TABLE university
(
    id              INTEGER not null
            primary key,
    country_id      INTEGER          default NULL,
    university_name TEXT default NULL,
    foreign key (country_id) references country(id)
)

CREATE TABLE university_ranking_year
(
    university_id       INTEGER default NULL,
    ranking_criteria_id INTEGER default NULL,
    year                INTEGER default NULL,
    score               INTEGER default NULL,
    foreign key (ranking_criteria_id) references ranking_criteria(id),
    foreign key (university_id) references university(id)
)

CREATE TABLE university_year
(
    university_id              INTEGER           default NULL,
    year                       INTEGER           default NULL,
    num_students               INTEGER           default NULL,
    student_staff_ratio        REAL default NULL,
    pct_international_students INTEGER           default NULL,
    pct_female_students        INTEGER           default NULL,
    foreign key (university_id) references university(id)
)

### Question
Provide the name of the university with the highest number of male students.

### Hint
highest number of female students refers to MAX(SUBTRACT(num_students, DIVIDE(MULTIPLY(num_students, pct_female_students), 100))); name of university refers to university_name

### SQL
SELECT T2.university_name FROM university_year AS T1 INNER JOIN university AS T2 ON T1.university_id = T2.id ORDER BY T1.num_students * T1.pct_female_students / 100 - T1.num_students DESC LIMIT 1
### Database Schema
CREATE TABLE country
(
    id           INTEGER not null
            primary key,
    country_name TEXT default NULL
)

CREATE TABLE ranking_system
(
    id          INTEGER not null
            primary key,
    system_name TEXT default NULL
)

CREATE TABLE ranking_criteria
(
    id                INTEGER not null
            primary key,
    ranking_system_id INTEGER          default NULL,
    criteria_name     TEXT default NULL,
    foreign key (ranking_system_id) references ranking_system(id)
)

CREATE TABLE university
(
    id              INTEGER not null
            primary key,
    country_id      INTEGER          default NULL,
    university_name TEXT default NULL,
    foreign key (country_id) references country(id)
)

CREATE TABLE university_ranking_year
(
    university_id       INTEGER default NULL,
    ranking_criteria_id INTEGER default NULL,
    year                INTEGER default NULL,
    score               INTEGER default NULL,
    foreign key (ranking_criteria_id) references ranking_criteria(id),
    foreign key (university_id) references university(id)
)

CREATE TABLE university_year
(
    university_id              INTEGER           default NULL,
    year                       INTEGER           default NULL,
    num_students               INTEGER           default NULL,
    student_staff_ratio        REAL default NULL,
    pct_international_students INTEGER           default NULL,
    pct_female_students        INTEGER           default NULL,
    foreign key (university_id) references university(id)
)

### Question
List the countries of universities that scored 70 and below in 2016.

### Hint
scored 70 and below refers to score < 70; in 2016 refers to year = 2016

### SQL
SELECT DISTINCT T3.country_name FROM university AS T1 INNER JOIN university_ranking_year AS T2 ON T1.id = T2.university_id INNER JOIN country AS T3 ON T3.id = T1.country_id WHERE T2.score < 70 AND T2.year = 2016
### Database Schema
CREATE TABLE country
(
    id           INTEGER not null
            primary key,
    country_name TEXT default NULL
)

CREATE TABLE ranking_system
(
    id          INTEGER not null
            primary key,
    system_name TEXT default NULL
)

CREATE TABLE ranking_criteria
(
    id                INTEGER not null
            primary key,
    ranking_system_id INTEGER          default NULL,
    criteria_name     TEXT default NULL,
    foreign key (ranking_system_id) references ranking_system(id)
)

CREATE TABLE university
(
    id              INTEGER not null
            primary key,
    country_id      INTEGER          default NULL,
    university_name TEXT default NULL,
    foreign key (country_id) references country(id)
)

CREATE TABLE university_ranking_year
(
    university_id       INTEGER default NULL,
    ranking_criteria_id INTEGER default NULL,
    year                INTEGER default NULL,
    score               INTEGER default NULL,
    foreign key (ranking_criteria_id) references ranking_criteria(id),
    foreign key (university_id) references university(id)
)

CREATE TABLE university_year
(
    university_id              INTEGER           default NULL,
    year                       INTEGER           default NULL,
    num_students               INTEGER           default NULL,
    student_staff_ratio        REAL default NULL,
    pct_international_students INTEGER           default NULL,
    pct_female_students        INTEGER           default NULL,
    foreign key (university_id) references university(id)
)

### Question
Calculate number of male students in Emory University in 2011.

### Hint
in 2011 refers to year 2011; number of male students refers to SUBTRACT(num_students, DIVIDE(MULTIPLY(num_students, pct_male_students), 100)); in Emory University refers to university_name = 'Emory University'

### SQL
SELECT CAST((T1.num_students - (T1.num_students * T1.pct_female_students)) AS REAL) / 100 FROM university_year AS T1 INNER JOIN university AS T2 ON T1.university_id = T2.id WHERE T2.university_name = 'Emory University' AND T1.year = 2011
### Database Schema
CREATE TABLE country
(
    id           INTEGER not null
            primary key,
    country_name TEXT default NULL
)

CREATE TABLE ranking_system
(
    id          INTEGER not null
            primary key,
    system_name TEXT default NULL
)

CREATE TABLE ranking_criteria
(
    id                INTEGER not null
            primary key,
    ranking_system_id INTEGER          default NULL,
    criteria_name     TEXT default NULL,
    foreign key (ranking_system_id) references ranking_system(id)
)

CREATE TABLE university
(
    id              INTEGER not null
            primary key,
    country_id      INTEGER          default NULL,
    university_name TEXT default NULL,
    foreign key (country_id) references country(id)
)

CREATE TABLE university_ranking_year
(
    university_id       INTEGER default NULL,
    ranking_criteria_id INTEGER default NULL,
    year                INTEGER default NULL,
    score               INTEGER default NULL,
    foreign key (ranking_criteria_id) references ranking_criteria(id),
    foreign key (university_id) references university(id)
)

CREATE TABLE university_year
(
    university_id              INTEGER           default NULL,
    year                       INTEGER           default NULL,
    num_students               INTEGER           default NULL,
    student_staff_ratio        REAL default NULL,
    pct_international_students INTEGER           default NULL,
    pct_female_students        INTEGER           default NULL,
    foreign key (university_id) references university(id)
)

### Question
In which country does Johns Hopkins University located?

### Hint
Johns Hopkins University refers to university_name = 'Johns Hopkins University'; which country refers to country_name

### SQL
SELECT T2.country_name FROM university AS T1 INNER JOIN country AS T2 ON T1.country_id = T2.id WHERE T1.university_name = 'Johns Hopkins University'
### Database Schema
CREATE TABLE country
(
    id           INTEGER not null
            primary key,
    country_name TEXT default NULL
)

CREATE TABLE ranking_system
(
    id          INTEGER not null
            primary key,
    system_name TEXT default NULL
)

CREATE TABLE ranking_criteria
(
    id                INTEGER not null
            primary key,
    ranking_system_id INTEGER          default NULL,
    criteria_name     TEXT default NULL,
    foreign key (ranking_system_id) references ranking_system(id)
)

CREATE TABLE university
(
    id              INTEGER not null
            primary key,
    country_id      INTEGER          default NULL,
    university_name TEXT default NULL,
    foreign key (country_id) references country(id)
)

CREATE TABLE university_ranking_year
(
    university_id       INTEGER default NULL,
    ranking_criteria_id INTEGER default NULL,
    year                INTEGER default NULL,
    score               INTEGER default NULL,
    foreign key (ranking_criteria_id) references ranking_criteria(id),
    foreign key (university_id) references university(id)
)

CREATE TABLE university_year
(
    university_id              INTEGER           default NULL,
    year                       INTEGER           default NULL,
    num_students               INTEGER           default NULL,
    student_staff_ratio        REAL default NULL,
    pct_international_students INTEGER           default NULL,
    pct_female_students        INTEGER           default NULL,
    foreign key (university_id) references university(id)
)

### Question
Give the names of universities with number of students ranges from 400 to 1000.

### Hint
number of students ranges from 400 to 1000 refers to num_students BETWEEN 400 AND 1000; name of university refers to university_name

### SQL
SELECT DISTINCT T2.university_name FROM university_year AS T1 INNER JOIN university AS T2 ON T1.university_id = T2.id WHERE T1.num_students BETWEEN 400 AND 1000
### Database Schema
CREATE TABLE country
(
    id           INTEGER not null
            primary key,
    country_name TEXT default NULL
)

CREATE TABLE ranking_system
(
    id          INTEGER not null
            primary key,
    system_name TEXT default NULL
)

CREATE TABLE ranking_criteria
(
    id                INTEGER not null
            primary key,
    ranking_system_id INTEGER          default NULL,
    criteria_name     TEXT default NULL,
    foreign key (ranking_system_id) references ranking_system(id)
)

CREATE TABLE university
(
    id              INTEGER not null
            primary key,
    country_id      INTEGER          default NULL,
    university_name TEXT default NULL,
    foreign key (country_id) references country(id)
)

CREATE TABLE university_ranking_year
(
    university_id       INTEGER default NULL,
    ranking_criteria_id INTEGER default NULL,
    year                INTEGER default NULL,
    score               INTEGER default NULL,
    foreign key (ranking_criteria_id) references ranking_criteria(id),
    foreign key (university_id) references university(id)
)

CREATE TABLE university_year
(
    university_id              INTEGER           default NULL,
    year                       INTEGER           default NULL,
    num_students               INTEGER           default NULL,
    student_staff_ratio        REAL default NULL,
    pct_international_students INTEGER           default NULL,
    pct_female_students        INTEGER           default NULL,
    foreign key (university_id) references university(id)
)

### Question
In what year does the Brown University score the highest?

### Hint
Brown University refers to university_name = 'Brown University'; score the highest refers to MAX(score)

### SQL
SELECT T1.year FROM university_ranking_year AS T1 INNER JOIN university AS T2 ON T1.university_id = T2.id WHERE T2.university_name = 'Brown University' ORDER BY T1.score DESC LIMIT 1
### Database Schema
CREATE TABLE country
(
    id           INTEGER not null
            primary key,
    country_name TEXT default NULL
)

CREATE TABLE ranking_system
(
    id          INTEGER not null
            primary key,
    system_name TEXT default NULL
)

CREATE TABLE ranking_criteria
(
    id                INTEGER not null
            primary key,
    ranking_system_id INTEGER          default NULL,
    criteria_name     TEXT default NULL,
    foreign key (ranking_system_id) references ranking_system(id)
)

CREATE TABLE university
(
    id              INTEGER not null
            primary key,
    country_id      INTEGER          default NULL,
    university_name TEXT default NULL,
    foreign key (country_id) references country(id)
)

CREATE TABLE university_ranking_year
(
    university_id       INTEGER default NULL,
    ranking_criteria_id INTEGER default NULL,
    year                INTEGER default NULL,
    score               INTEGER default NULL,
    foreign key (ranking_criteria_id) references ranking_criteria(id),
    foreign key (university_id) references university(id)
)

CREATE TABLE university_year
(
    university_id              INTEGER           default NULL,
    year                       INTEGER           default NULL,
    num_students               INTEGER           default NULL,
    student_staff_ratio        REAL default NULL,
    pct_international_students INTEGER           default NULL,
    pct_female_students        INTEGER           default NULL,
    foreign key (university_id) references university(id)
)

### Question
Calculate the average score of Emory University from 2011 to 2016.

### Hint
average score refers to avg(score); Emory University refers to university_name = 'Emory University'; from 2011 to 2016 refers to year BETWEEN 2011 AND 2016;

### SQL
SELECT AVG(T1.score) FROM university_ranking_year AS T1 INNER JOIN university AS T2 ON T1.university_id = T2.id WHERE T2.university_name = 'Emory University' AND T1.year BETWEEN 2011 AND 2016
### Database Schema
CREATE TABLE country
(
    id           INTEGER not null
            primary key,
    country_name TEXT default NULL
)

CREATE TABLE ranking_system
(
    id          INTEGER not null
            primary key,
    system_name TEXT default NULL
)

CREATE TABLE ranking_criteria
(
    id                INTEGER not null
            primary key,
    ranking_system_id INTEGER          default NULL,
    criteria_name     TEXT default NULL,
    foreign key (ranking_system_id) references ranking_system(id)
)

CREATE TABLE university
(
    id              INTEGER not null
            primary key,
    country_id      INTEGER          default NULL,
    university_name TEXT default NULL,
    foreign key (country_id) references country(id)
)

CREATE TABLE university_ranking_year
(
    university_id       INTEGER default NULL,
    ranking_criteria_id INTEGER default NULL,
    year                INTEGER default NULL,
    score               INTEGER default NULL,
    foreign key (ranking_criteria_id) references ranking_criteria(id),
    foreign key (university_id) references university(id)
)

CREATE TABLE university_year
(
    university_id              INTEGER           default NULL,
    year                       INTEGER           default NULL,
    num_students               INTEGER           default NULL,
    student_staff_ratio        REAL default NULL,
    pct_international_students INTEGER           default NULL,
    pct_female_students        INTEGER           default NULL,
    foreign key (university_id) references university(id)
)

### Question
Give the name of the university with the most number of students in 2015.

### Hint
most number of students refers to MAX(num_students); in 2015 refers to year = 2015; name of university refers to university_name;

### SQL
SELECT T2.university_name FROM university_year AS T1 INNER JOIN university AS T2 ON T1.university_id = T2.id WHERE T1.year = 2015 ORDER BY T1.num_students DESC LIMIT 1
### Database Schema
CREATE TABLE country
(
    id           INTEGER not null
            primary key,
    country_name TEXT default NULL
)

CREATE TABLE ranking_system
(
    id          INTEGER not null
            primary key,
    system_name TEXT default NULL
)

CREATE TABLE ranking_criteria
(
    id                INTEGER not null
            primary key,
    ranking_system_id INTEGER          default NULL,
    criteria_name     TEXT default NULL,
    foreign key (ranking_system_id) references ranking_system(id)
)

CREATE TABLE university
(
    id              INTEGER not null
            primary key,
    country_id      INTEGER          default NULL,
    university_name TEXT default NULL,
    foreign key (country_id) references country(id)
)

CREATE TABLE university_ranking_year
(
    university_id       INTEGER default NULL,
    ranking_criteria_id INTEGER default NULL,
    year                INTEGER default NULL,
    score               INTEGER default NULL,
    foreign key (ranking_criteria_id) references ranking_criteria(id),
    foreign key (university_id) references university(id)
)

CREATE TABLE university_year
(
    university_id              INTEGER           default NULL,
    year                       INTEGER           default NULL,
    num_students               INTEGER           default NULL,
    student_staff_ratio        REAL default NULL,
    pct_international_students INTEGER           default NULL,
    pct_female_students        INTEGER           default NULL,
    foreign key (university_id) references university(id)
)

### Question
What is the location and number of female students in university ID 23 in 2011?

### Hint
in 2011 refers to year 2011; female students refers to DIVIDE(MULTIPLY(num_students, pct_female_students), 100); location refers to country_name

### SQL
SELECT T3.country_name, CAST(T2.num_students * T2.pct_female_students AS REAL) / 100 FROM university AS T1 INNER JOIN university_year AS T2 ON T1.id = T2.university_id INNER JOIN country AS T3 ON T3.id = T1.country_id WHERE T2.year = 2011 AND T1.id = 23
### Database Schema
CREATE TABLE country
(
    id           INTEGER not null
            primary key,
    country_name TEXT default NULL
)

CREATE TABLE ranking_system
(
    id          INTEGER not null
            primary key,
    system_name TEXT default NULL
)

CREATE TABLE ranking_criteria
(
    id                INTEGER not null
            primary key,
    ranking_system_id INTEGER          default NULL,
    criteria_name     TEXT default NULL,
    foreign key (ranking_system_id) references ranking_system(id)
)

CREATE TABLE university
(
    id              INTEGER not null
            primary key,
    country_id      INTEGER          default NULL,
    university_name TEXT default NULL,
    foreign key (country_id) references country(id)
)

CREATE TABLE university_ranking_year
(
    university_id       INTEGER default NULL,
    ranking_criteria_id INTEGER default NULL,
    year                INTEGER default NULL,
    score               INTEGER default NULL,
    foreign key (ranking_criteria_id) references ranking_criteria(id),
    foreign key (university_id) references university(id)
)

CREATE TABLE university_year
(
    university_id              INTEGER           default NULL,
    year                       INTEGER           default NULL,
    num_students               INTEGER           default NULL,
    student_staff_ratio        REAL default NULL,
    pct_international_students INTEGER           default NULL,
    pct_female_students        INTEGER           default NULL,
    foreign key (university_id) references university(id)
)

### Question
How many universities scored 40 in teaching criteria?

### Hint
scored 40 refers to score = 40; in teaching refers to criteria_name = 'Teaching'

### SQL
SELECT COUNT(*) FROM ranking_criteria AS T1 INNER JOIN university_ranking_year AS T2 ON T1.id = T2.ranking_criteria_id WHERE T2.score = 40 AND T1.criteria_name = 'Teaching' AND T2.score = 40