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
Provide the ranking criteria and scores in 2005 that were received by Harvard University.

### Hint
Harvard University refers to university_name = 'Harvard University'; in 2005 refers to year = 2005; ranking criteria refers to criteria_name;

### SQL
SELECT T1.criteria_name, T2.score FROM ranking_criteria AS T1 INNER JOIN university_ranking_year AS T2 ON T1.id = T2.ranking_criteria_id INNER JOIN university AS T3 ON T3.id = T2.university_id WHERE T3.university_name = 'Harvard University' AND T2.year = 2005
### 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 per university under Alumni criteria in 2008.

### Hint
under Alumni criteria refers to criteria_name = 'Alumni'; in 2008 refers to year = 2008; average score refers to DIVIDE(SUM(score), COUNT(university_id))

### SQL
SELECT AVG(T2.score) FROM ranking_criteria AS T1 INNER JOIN university_ranking_year AS T2 ON T1.id = T2.ranking_criteria_id WHERE T1.criteria_name = 'Alumni' AND T2.year = 2008
### 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
Name the university and country which had the highest number of international students in 2015.

### Hint
highest number of international students refers to MAX(DIVIDE(MULTIPLY(num_students, pct_international_students), 100)); in 2015 refers to year = 2015; name of university refers to university_name;

### SQL
SELECT T1.university_name, T3.country_name 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 = 2015 ORDER BY T2.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
How many students were there in university ID 1 in 2011?

### Hint
in 2011 refers to year 2011;

### SQL
SELECT num_students FROM university_year WHERE year = 2011 AND university_id = 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 ID of the university with the most students in 2011?

### Hint
in 2011 refers to year 2011; with the most students refers to MAX(num_students); ID of the university refers to university_id

### SQL
SELECT university_id FROM university_year WHERE year = 2011 ORDER BY 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
Please list the IDs of the universities with a student staff ratio of over 15 in 2011.

### Hint
in 2011 refers to year 2011; student staff ratio of over 15 refers to student_staff_ratio > 15; ID of the university refers to university_id

### SQL
SELECT university_id FROM university_year WHERE year = 2011 AND student_staff_ratio > 15
### 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
Among the universities with over 20000 students in 2011, how many of them have an international students percentage of over 25% in the same year?

### Hint
in 2011 refers to year 2011; with over 20000 students refers to num_students > 20000; international students percentage of over 25% refers to pct_international_students > 25;

### SQL
SELECT COUNT(*) FROM university_year WHERE year = 2011 AND pct_international_students > 25 AND num_students > 20000
### 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
Please list the IDs of the universities with the top 3 female students percentage in 2011.

### Hint
in 2011 refers to year 2011; top 3 female students percentage refers to MAX(pct_female_students) LIMIT 3; ID of the university refers to university_id

### SQL
SELECT university_id FROM university_year WHERE year = 2011 ORDER BY pct_female_students DESC LIMIT 3
### 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 year did university ID 1 have the most students?

### Hint
have the most students refers to MAX(num_students)

### SQL
SELECT year FROM university_year WHERE university_id = 1 ORDER BY 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
How many students did Harvard University have in 2011?

### Hint
in 2011 refers to year 2011; Harvard University refers to university_name = 'Harvard University';

### SQL
SELECT T1.num_students FROM university_year AS T1 INNER JOIN university AS T2 ON T1.university_id = T2.id WHERE T2.university_name = 'Harvard 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
What is the name of the university with the most international students in 2011?

### Hint
in 2011 refers to year 2011; the most international students refers to MAX(DIVIDE(MULTIPLY(num_students,  pct_international_students), 100)); name of university refers to university_id

### SQL
SELECT T2.university_name FROM university_year AS T1 INNER JOIN university AS T2 ON T1.university_id = T2.id WHERE T1.year = 2011 ORDER BY T1.pct_international_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
Please list the names of all the universities in Australia.

### Hint
in Australia refers to country_name = 'Australia'; name of university refers to university_name

### SQL
SELECT T1.university_name FROM university AS T1 INNER JOIN country AS T2 ON T1.country_id = T2.id WHERE T2.country_name = 'Australia'