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
Name the most famous university in Argentina.

### Hint
in Argentina refers to country_name = 'Argentina';  most famous refers to MAX(SUM(score))

### SQL
SELECT T1.university_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 T3.country_name = 'Argentina' GROUP BY T1.university_name ORDER BY SUM(T2.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
In Argentina, how many universities are there?

### Hint
In Argentina refers to country_name = 'Argentina';

### SQL
SELECT COUNT(*) FROM university AS T1 INNER JOIN country AS T2 ON T1.country_id = T2.id WHERE T2.country_name = 'Argentina'
### 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
Which universities have more than 100,000 students in 2011?

### Hint
in 2011 refers to year 2011; more than 100,000 students refers to num_students > 100000; which 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 = 2011 AND T1.num_students > 100000
### 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 criteria are associated with ranking system Center for World University Rankings?

### Hint
ranking system Center for World University Rankings refers to system_name = 'Center for World University Rankings';

### SQL
SELECT COUNT(T2.criteria_name) FROM ranking_system AS T1 INNER JOIN ranking_criteria AS T2 ON T1.id = T2.ranking_system_id WHERE T1.system_name = 'Center for World University Rankings'
### 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 at the university earned a score of 90 in 2011?

### Hint
in 2011 refers to year 2011; earned a score of 90 refers to score = 90;

### SQL
SELECT COUNT(*) FROM university_year AS T1 INNER JOIN university_ranking_year AS T2 ON T1.university_id = T2.university_id WHERE T2.score = 90 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 are the top three universities with the most international students?

### Hint
most international students refers to MAX(SUM(DIVIDE(MULTIPLE(pct_international_students, num_students), 100))); 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 GROUP BY T2.university_name ORDER BY SUM(T1.num_students * T1.pct_international_students / 100) 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
What is the difference in overall student enrollment and international student enrollment at the Harvard university from 2011 to 2012?

### Hint
Harvard University refers to university_name = 'Harvard University'; difference in overall student enrollment and international student refers to SUBTRACT(SUM(num_students), SUM(DIVIDE(MULTIPLY(pct_international_students, num_students), 100))); from 2011 to 2012 refers to year BETWEEN 2011 AND 2012

### SQL
SELECT SUM(T1.num_students) - SUM(CAST(T1.num_students * T1.pct_international_students AS REAL) / 100) 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 BETWEEN 2011 AND 2012
### 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 had over 30000 students in 2011?

### Hint
in 2011 refers to year 2011; had over 30000 students refers to num_students > 30000;

### SQL
SELECT COUNT(*) FROM university_year WHERE year = 2011 AND num_students > 30000
### 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 country ID of the University of Tokyo?

### Hint
University of Tokyo refers to university_name = 'University of Tokyo';

### SQL
SELECT country_id FROM university WHERE university_name = 'University of Tokyo'
### 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 system ID of the Center for World University Rankings.

### Hint
the Center for World University Rankings refers to system_name = 'Center for World University Rankings';

### SQL
SELECT id FROM ranking_system WHERE system_name = 'Center for World University Rankings'
### 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 Publications Rank criteria?

### Hint
Publications Rank criteria refers to criteria_name = 'Publications Rank';

### SQL
SELECT id FROM ranking_criteria WHERE criteria_name = 'Publications Rank'
### 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 had above 30% of international students in 2013?

### Hint
had above 30% of international students refers to pct_international_students > 30; in 2013 refers to year = 2013

### SQL
SELECT COUNT(*) FROM university_year WHERE pct_international_students > 30 AND year = 2013