lianghsun/bird-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE "course"
(
    course_id INTEGER
        constraint course_pk
            primary key,
    name      TEXT,
    credit    INTEGER,
    diff      INTEGER
)

CREATE TABLE prof
(
    prof_id         INTEGER
        constraint prof_pk
            primary key,
    gender          TEXT,
    first_name      TEXT,
    last_name       TEXT,
    email           TEXT,
    popularity      INTEGER,
    teachingability INTEGER,
    graduate_from   TEXT
)

CREATE TABLE RA
(
    student_id INTEGER,
    capability INTEGER,
    prof_id    INTEGER,
    salary     TEXT,
    primary key (student_id, prof_id),
    foreign key (prof_id) references prof(prof_id),
    foreign key (student_id) references student(student_id)
)

CREATE TABLE registration
(
    course_id  INTEGER,
    student_id INTEGER,
    grade      TEXT,
    sat        INTEGER,
    primary key (course_id, student_id),
    foreign key (course_id) references course(course_id),
    foreign key (student_id) references student(student_id)
)

CREATE TABLE student
(
    student_id   INTEGER
            primary key,
    f_name       TEXT,
    l_name       TEXT,
    phone_number TEXT,
    email        TEXT,
    intelligence INTEGER,
    gpa          REAL,
    type         TEXT
)

### Question
How many research assistants does the female professor with the lowest teaching ability have?

### Hint
research assistant refers to the student who serves for research where the abbreviation is RA; professor with the lowest teaching ability refers to prof_id where teachability = '1';

### SQL
SELECT COUNT(T1.student_id) FROM RA AS T1 INNER JOIN prof AS T2 ON T1.prof_id = T2.prof_id WHERE T2.teachingability = '1' AND T2.gender = 'Female'
### Database Schema
CREATE TABLE "course"
(
    course_id INTEGER
        constraint course_pk
            primary key,
    name      TEXT,
    credit    INTEGER,
    diff      INTEGER
)

CREATE TABLE prof
(
    prof_id         INTEGER
        constraint prof_pk
            primary key,
    gender          TEXT,
    first_name      TEXT,
    last_name       TEXT,
    email           TEXT,
    popularity      INTEGER,
    teachingability INTEGER,
    graduate_from   TEXT
)

CREATE TABLE RA
(
    student_id INTEGER,
    capability INTEGER,
    prof_id    INTEGER,
    salary     TEXT,
    primary key (student_id, prof_id),
    foreign key (prof_id) references prof(prof_id),
    foreign key (student_id) references student(student_id)
)

CREATE TABLE registration
(
    course_id  INTEGER,
    student_id INTEGER,
    grade      TEXT,
    sat        INTEGER,
    primary key (course_id, student_id),
    foreign key (course_id) references course(course_id),
    foreign key (student_id) references student(student_id)
)

CREATE TABLE student
(
    student_id   INTEGER
            primary key,
    f_name       TEXT,
    l_name       TEXT,
    phone_number TEXT,
    email        TEXT,
    intelligence INTEGER,
    gpa          REAL,
    type         TEXT
)

### Question
For the professors who advise more than 2 students, which professor has a higher teaching ability? Give the full name.

### Hint
professor advising more than 2 students refers to COUNT(student_id) > 2; higher teachability refers to MAX(teachingability); full name refers to f_name and l_name;

### SQL
SELECT T.first_name, T.last_name FROM ( SELECT T2.first_name, T2.last_name, T2.teachingability FROM RA AS T1 INNER JOIN prof AS T2 ON T1.prof_id = T2.prof_id GROUP BY T1.prof_id HAVING COUNT(student_id) > 2 ) T ORDER BY T.teachingability DESC LIMIT 1
### Database Schema
CREATE TABLE "course"
(
    course_id INTEGER
        constraint course_pk
            primary key,
    name      TEXT,
    credit    INTEGER,
    diff      INTEGER
)

CREATE TABLE prof
(
    prof_id         INTEGER
        constraint prof_pk
            primary key,
    gender          TEXT,
    first_name      TEXT,
    last_name       TEXT,
    email           TEXT,
    popularity      INTEGER,
    teachingability INTEGER,
    graduate_from   TEXT
)

CREATE TABLE RA
(
    student_id INTEGER,
    capability INTEGER,
    prof_id    INTEGER,
    salary     TEXT,
    primary key (student_id, prof_id),
    foreign key (prof_id) references prof(prof_id),
    foreign key (student_id) references student(student_id)
)

CREATE TABLE registration
(
    course_id  INTEGER,
    student_id INTEGER,
    grade      TEXT,
    sat        INTEGER,
    primary key (course_id, student_id),
    foreign key (course_id) references course(course_id),
    foreign key (student_id) references student(student_id)
)

CREATE TABLE student
(
    student_id   INTEGER
            primary key,
    f_name       TEXT,
    l_name       TEXT,
    phone_number TEXT,
    email        TEXT,
    intelligence INTEGER,
    gpa          REAL,
    type         TEXT
)

### Question
Give the grade score for Rik Unsworth in "Computer Network".

### Hint
Academic grades awarded for participation in a course are A, B, C, D and F where Grade 'A' means excellent, Grade 'B' means good, Grade 'C' means fair, Grade 'D' means poorly pass, if grade is null or empty, it means that this student fails to pass this course in which grade = NULL;

### SQL
SELECT CASE grade WHEN 'A' THEN 4 WHEN 'B' THEN 3 WHEN 'C' THEN 2 ELSE 1 END AS result FROM registration WHERE student_id IN ( SELECT student_id FROM student WHERE f_name = 'Rik' AND l_name = 'Unsworth' AND course_id IN ( SELECT course_id FROM course WHERE name = 'Computer Network' ) )
### Database Schema
CREATE TABLE "course"
(
    course_id INTEGER
        constraint course_pk
            primary key,
    name      TEXT,
    credit    INTEGER,
    diff      INTEGER
)

CREATE TABLE prof
(
    prof_id         INTEGER
        constraint prof_pk
            primary key,
    gender          TEXT,
    first_name      TEXT,
    last_name       TEXT,
    email           TEXT,
    popularity      INTEGER,
    teachingability INTEGER,
    graduate_from   TEXT
)

CREATE TABLE RA
(
    student_id INTEGER,
    capability INTEGER,
    prof_id    INTEGER,
    salary     TEXT,
    primary key (student_id, prof_id),
    foreign key (prof_id) references prof(prof_id),
    foreign key (student_id) references student(student_id)
)

CREATE TABLE registration
(
    course_id  INTEGER,
    student_id INTEGER,
    grade      TEXT,
    sat        INTEGER,
    primary key (course_id, student_id),
    foreign key (course_id) references course(course_id),
    foreign key (student_id) references student(student_id)
)

CREATE TABLE student
(
    student_id   INTEGER
            primary key,
    f_name       TEXT,
    l_name       TEXT,
    phone_number TEXT,
    email        TEXT,
    intelligence INTEGER,
    gpa          REAL,
    type         TEXT
)

### Question
How many courses does Alvera McQuillin take?

### SQL
SELECT COUNT(T1.course_id) FROM registration AS T1 INNER JOIN student AS T2 ON T1.student_id = T2.student_id WHERE T2.f_name = 'Alvera' AND T2.l_name = 'McQuillin'
### Database Schema
CREATE TABLE "course"
(
    course_id INTEGER
        constraint course_pk
            primary key,
    name      TEXT,
    credit    INTEGER,
    diff      INTEGER
)

CREATE TABLE prof
(
    prof_id         INTEGER
        constraint prof_pk
            primary key,
    gender          TEXT,
    first_name      TEXT,
    last_name       TEXT,
    email           TEXT,
    popularity      INTEGER,
    teachingability INTEGER,
    graduate_from   TEXT
)

CREATE TABLE RA
(
    student_id INTEGER,
    capability INTEGER,
    prof_id    INTEGER,
    salary     TEXT,
    primary key (student_id, prof_id),
    foreign key (prof_id) references prof(prof_id),
    foreign key (student_id) references student(student_id)
)

CREATE TABLE registration
(
    course_id  INTEGER,
    student_id INTEGER,
    grade      TEXT,
    sat        INTEGER,
    primary key (course_id, student_id),
    foreign key (course_id) references course(course_id),
    foreign key (student_id) references student(student_id)
)

CREATE TABLE student
(
    student_id   INTEGER
            primary key,
    f_name       TEXT,
    l_name       TEXT,
    phone_number TEXT,
    email        TEXT,
    intelligence INTEGER,
    gpa          REAL,
    type         TEXT
)

### Question
State the name of research postgraduate student among Professor Zhihua Zhou's research assistants.

### Hint
research postgraduate student refers to type = 'RPG'; research assistant refers to the student who serves for research where the abbreviation is RA;

### SQL
SELECT T3.f_name, T3.l_name FROM prof AS T1 INNER JOIN RA AS T2 ON T1.prof_id = T2.prof_id INNER JOIN student AS T3 ON T2.student_id = T3.student_id WHERE T1.first_name = 'Zhihua' AND T3.type = 'RPG' AND T1.last_name = 'Zhou'
### Database Schema
CREATE TABLE "course"
(
    course_id INTEGER
        constraint course_pk
            primary key,
    name      TEXT,
    credit    INTEGER,
    diff      INTEGER
)

CREATE TABLE prof
(
    prof_id         INTEGER
        constraint prof_pk
            primary key,
    gender          TEXT,
    first_name      TEXT,
    last_name       TEXT,
    email           TEXT,
    popularity      INTEGER,
    teachingability INTEGER,
    graduate_from   TEXT
)

CREATE TABLE RA
(
    student_id INTEGER,
    capability INTEGER,
    prof_id    INTEGER,
    salary     TEXT,
    primary key (student_id, prof_id),
    foreign key (prof_id) references prof(prof_id),
    foreign key (student_id) references student(student_id)
)

CREATE TABLE registration
(
    course_id  INTEGER,
    student_id INTEGER,
    grade      TEXT,
    sat        INTEGER,
    primary key (course_id, student_id),
    foreign key (course_id) references course(course_id),
    foreign key (student_id) references student(student_id)
)

CREATE TABLE student
(
    student_id   INTEGER
            primary key,
    f_name       TEXT,
    l_name       TEXT,
    phone_number TEXT,
    email        TEXT,
    intelligence INTEGER,
    gpa          REAL,
    type         TEXT
)

### Question
Provide the number of students enrolled in the "Statistical Learning" course.

### SQL
SELECT COUNT(T2.student_id) FROM course AS T1 INNER JOIN registration AS T2 ON T1.course_id = T2.course_id WHERE T1.name = 'Statistical learning'
### Database Schema
CREATE TABLE "course"
(
    course_id INTEGER
        constraint course_pk
            primary key,
    name      TEXT,
    credit    INTEGER,
    diff      INTEGER
)

CREATE TABLE prof
(
    prof_id         INTEGER
        constraint prof_pk
            primary key,
    gender          TEXT,
    first_name      TEXT,
    last_name       TEXT,
    email           TEXT,
    popularity      INTEGER,
    teachingability INTEGER,
    graduate_from   TEXT
)

CREATE TABLE RA
(
    student_id INTEGER,
    capability INTEGER,
    prof_id    INTEGER,
    salary     TEXT,
    primary key (student_id, prof_id),
    foreign key (prof_id) references prof(prof_id),
    foreign key (student_id) references student(student_id)
)

CREATE TABLE registration
(
    course_id  INTEGER,
    student_id INTEGER,
    grade      TEXT,
    sat        INTEGER,
    primary key (course_id, student_id),
    foreign key (course_id) references course(course_id),
    foreign key (student_id) references student(student_id)
)

CREATE TABLE student
(
    student_id   INTEGER
            primary key,
    f_name       TEXT,
    l_name       TEXT,
    phone_number TEXT,
    email        TEXT,
    intelligence INTEGER,
    gpa          REAL,
    type         TEXT
)

### Question
Who were the students who failed the course "Applied Deep Learning"? Give the full name.

### Hint
If grade is null or empty, it means that this student fails to pass the course in which grade = NULL;

### SQL
SELECT T1.f_name, T1.l_name FROM student AS T1 INNER JOIN registration AS T2 ON T1.student_id = T2.student_id INNER JOIN course AS T3 ON T2.course_id = T3.course_id WHERE T3.name = 'Applied Deep Learning ' AND T2.grade IS NULL
### Database Schema
CREATE TABLE "course"
(
    course_id INTEGER
        constraint course_pk
            primary key,
    name      TEXT,
    credit    INTEGER,
    diff      INTEGER
)

CREATE TABLE prof
(
    prof_id         INTEGER
        constraint prof_pk
            primary key,
    gender          TEXT,
    first_name      TEXT,
    last_name       TEXT,
    email           TEXT,
    popularity      INTEGER,
    teachingability INTEGER,
    graduate_from   TEXT
)

CREATE TABLE RA
(
    student_id INTEGER,
    capability INTEGER,
    prof_id    INTEGER,
    salary     TEXT,
    primary key (student_id, prof_id),
    foreign key (prof_id) references prof(prof_id),
    foreign key (student_id) references student(student_id)
)

CREATE TABLE registration
(
    course_id  INTEGER,
    student_id INTEGER,
    grade      TEXT,
    sat        INTEGER,
    primary key (course_id, student_id),
    foreign key (course_id) references course(course_id),
    foreign key (student_id) references student(student_id)
)

CREATE TABLE student
(
    student_id   INTEGER
            primary key,
    f_name       TEXT,
    l_name       TEXT,
    phone_number TEXT,
    email        TEXT,
    intelligence INTEGER,
    gpa          REAL,
    type         TEXT
)

### Question
Give the phone number of the only student who obtained "A" in the course "Intro to BlockChain".

### Hint
A refers to an excellent grade in which grade = 'A' for the course;

### SQL
SELECT T1.phone_number FROM student AS T1 INNER JOIN registration AS T2 ON T1.student_id = T2.student_id INNER JOIN course AS T3 ON T2.course_id = T3.course_id WHERE T3.name = 'Intro to BlockChain' AND T2.grade = 'A'
### Database Schema
CREATE TABLE "course"
(
    course_id INTEGER
        constraint course_pk
            primary key,
    name      TEXT,
    credit    INTEGER,
    diff      INTEGER
)

CREATE TABLE prof
(
    prof_id         INTEGER
        constraint prof_pk
            primary key,
    gender          TEXT,
    first_name      TEXT,
    last_name       TEXT,
    email           TEXT,
    popularity      INTEGER,
    teachingability INTEGER,
    graduate_from   TEXT
)

CREATE TABLE RA
(
    student_id INTEGER,
    capability INTEGER,
    prof_id    INTEGER,
    salary     TEXT,
    primary key (student_id, prof_id),
    foreign key (prof_id) references prof(prof_id),
    foreign key (student_id) references student(student_id)
)

CREATE TABLE registration
(
    course_id  INTEGER,
    student_id INTEGER,
    grade      TEXT,
    sat        INTEGER,
    primary key (course_id, student_id),
    foreign key (course_id) references course(course_id),
    foreign key (student_id) references student(student_id)
)

CREATE TABLE student
(
    student_id   INTEGER
            primary key,
    f_name       TEXT,
    l_name       TEXT,
    phone_number TEXT,
    email        TEXT,
    intelligence INTEGER,
    gpa          REAL,
    type         TEXT
)

### Question
What is the percentage of Professor Ogdon Zywicki's research assistants are taught postgraduate students?

### Hint
research assistant refers to the student who serves for research where the abbreviation is RA; taught postgraduate student refers to type = 'TPG'; DIVIDE(COUNT(student_id where type = 'TPG' and first_name = 'Ogdon', last_name = 'Zywicki'), COUNT(first_name = 'Ogdon', last_name = 'Zywicki')) as percentage;

### SQL
SELECT CAST(SUM(CASE WHEN T3.type = 'TPG' THEN 1 ELSE 0 END) AS REAL) * 100 / COUNT(T1.student_id) FROM RA AS T1 INNER JOIN prof AS T2 ON T1.prof_id = T2.prof_id INNER JOIN student AS T3 ON T1.student_id = T3.student_id WHERE T2.first_name = 'Ogdon' AND T2.last_name = 'Zywicki'
### Database Schema
CREATE TABLE "course"
(
    course_id INTEGER
        constraint course_pk
            primary key,
    name      TEXT,
    credit    INTEGER,
    diff      INTEGER
)

CREATE TABLE prof
(
    prof_id         INTEGER
        constraint prof_pk
            primary key,
    gender          TEXT,
    first_name      TEXT,
    last_name       TEXT,
    email           TEXT,
    popularity      INTEGER,
    teachingability INTEGER,
    graduate_from   TEXT
)

CREATE TABLE RA
(
    student_id INTEGER,
    capability INTEGER,
    prof_id    INTEGER,
    salary     TEXT,
    primary key (student_id, prof_id),
    foreign key (prof_id) references prof(prof_id),
    foreign key (student_id) references student(student_id)
)

CREATE TABLE registration
(
    course_id  INTEGER,
    student_id INTEGER,
    grade      TEXT,
    sat        INTEGER,
    primary key (course_id, student_id),
    foreign key (course_id) references course(course_id),
    foreign key (student_id) references student(student_id)
)

CREATE TABLE student
(
    student_id   INTEGER
            primary key,
    f_name       TEXT,
    l_name       TEXT,
    phone_number TEXT,
    email        TEXT,
    intelligence INTEGER,
    gpa          REAL,
    type         TEXT
)

### Question
What is the percentage of students who get a "B" in the course "Computer Network"?

### Hint
DIVIDE(COUNT(student_id(grade = 'B' and name = 'Computer Network')), COUNT(student_id where name = ' Computer Network')) as percentage;

### SQL
SELECT CAST(SUM(CASE WHEN T1.grade = 'B' THEN 1 ELSE 0 END) AS REAL) * 100 / COUNT(T1.student_id) FROM registration AS T1 INNER JOIN course AS T2 ON T1.course_id = T2.course_id WHERE T2.name = 'Computer Network'
### Database Schema
CREATE TABLE "course"
(
    course_id INTEGER
        constraint course_pk
            primary key,
    name      TEXT,
    credit    INTEGER,
    diff      INTEGER
)

CREATE TABLE prof
(
    prof_id         INTEGER
        constraint prof_pk
            primary key,
    gender          TEXT,
    first_name      TEXT,
    last_name       TEXT,
    email           TEXT,
    popularity      INTEGER,
    teachingability INTEGER,
    graduate_from   TEXT
)

CREATE TABLE RA
(
    student_id INTEGER,
    capability INTEGER,
    prof_id    INTEGER,
    salary     TEXT,
    primary key (student_id, prof_id),
    foreign key (prof_id) references prof(prof_id),
    foreign key (student_id) references student(student_id)
)

CREATE TABLE registration
(
    course_id  INTEGER,
    student_id INTEGER,
    grade      TEXT,
    sat        INTEGER,
    primary key (course_id, student_id),
    foreign key (course_id) references course(course_id),
    foreign key (student_id) references student(student_id)
)

CREATE TABLE student
(
    student_id   INTEGER
            primary key,
    f_name       TEXT,
    l_name       TEXT,
    phone_number TEXT,
    email        TEXT,
    intelligence INTEGER,
    gpa          REAL,
    type         TEXT
)

### Question
How many courses have the highest difficulty?

### Hint
diff refers to difficulty; diff is higher means the course is more difficult in which highest difficulty is expessed as diff = 5;

### SQL
SELECT COUNT(course_id) FROM course WHERE diff = 5
### Database Schema
CREATE TABLE "course"
(
    course_id INTEGER
        constraint course_pk
            primary key,
    name      TEXT,
    credit    INTEGER,
    diff      INTEGER
)

CREATE TABLE prof
(
    prof_id         INTEGER
        constraint prof_pk
            primary key,
    gender          TEXT,
    first_name      TEXT,
    last_name       TEXT,
    email           TEXT,
    popularity      INTEGER,
    teachingability INTEGER,
    graduate_from   TEXT
)

CREATE TABLE RA
(
    student_id INTEGER,
    capability INTEGER,
    prof_id    INTEGER,
    salary     TEXT,
    primary key (student_id, prof_id),
    foreign key (prof_id) references prof(prof_id),
    foreign key (student_id) references student(student_id)
)

CREATE TABLE registration
(
    course_id  INTEGER,
    student_id INTEGER,
    grade      TEXT,
    sat        INTEGER,
    primary key (course_id, student_id),
    foreign key (course_id) references course(course_id),
    foreign key (student_id) references student(student_id)
)

CREATE TABLE student
(
    student_id   INTEGER
            primary key,
    f_name       TEXT,
    l_name       TEXT,
    phone_number TEXT,
    email        TEXT,
    intelligence INTEGER,
    gpa          REAL,
    type         TEXT
)

### Question
What is the full name of the professor who graduated from an Ivy League School?

### Hint
Ivy League school is assembled by 8 universities: Brown University, Columbia University, Cornell University, Dartmouth College, Harvard University, Princeton University, University of Pennsylvania and Yale University;

### SQL
SELECT first_name, last_name FROM prof WHERE graduate_from IN ( 'Brown University',  'Columbia University',  'Cornell University',  'Dartmouth College',  'Harvard University',  'Princeton University',  'University of Pennsylvania',  'Yale University' )
### Database Schema
CREATE TABLE "course"
(
    course_id INTEGER
        constraint course_pk
            primary key,
    name      TEXT,
    credit    INTEGER,
    diff      INTEGER
)

CREATE TABLE prof
(
    prof_id         INTEGER
        constraint prof_pk
            primary key,
    gender          TEXT,
    first_name      TEXT,
    last_name       TEXT,
    email           TEXT,
    popularity      INTEGER,
    teachingability INTEGER,
    graduate_from   TEXT
)

CREATE TABLE RA
(
    student_id INTEGER,
    capability INTEGER,
    prof_id    INTEGER,
    salary     TEXT,
    primary key (student_id, prof_id),
    foreign key (prof_id) references prof(prof_id),
    foreign key (student_id) references student(student_id)
)

CREATE TABLE registration
(
    course_id  INTEGER,
    student_id INTEGER,
    grade      TEXT,
    sat        INTEGER,
    primary key (course_id, student_id),
    foreign key (course_id) references course(course_id),
    foreign key (student_id) references student(student_id)
)

CREATE TABLE student
(
    student_id   INTEGER
            primary key,
    f_name       TEXT,
    l_name       TEXT,
    phone_number TEXT,
    email        TEXT,
    intelligence INTEGER,
    gpa          REAL,
    type         TEXT
)

### Question
Among the most important courses, what is the name of the most difficult course?

### Hint
Higher credit means more important in which most important refers to MAX(credit); diff refers to difficulty; the most difficult course refers to MAX(diff);

### SQL
SELECT name FROM course WHERE credit = ( SELECT MAX(credit) FROM course ) AND diff = ( SELECT MAX(diff) FROM course )
### Database Schema
CREATE TABLE "course"
(
    course_id INTEGER
        constraint course_pk
            primary key,
    name      TEXT,
    credit    INTEGER,
    diff      INTEGER
)

CREATE TABLE prof
(
    prof_id         INTEGER
        constraint prof_pk
            primary key,
    gender          TEXT,
    first_name      TEXT,
    last_name       TEXT,
    email           TEXT,
    popularity      INTEGER,
    teachingability INTEGER,
    graduate_from   TEXT
)

CREATE TABLE RA
(
    student_id INTEGER,
    capability INTEGER,
    prof_id    INTEGER,
    salary     TEXT,
    primary key (student_id, prof_id),
    foreign key (prof_id) references prof(prof_id),
    foreign key (student_id) references student(student_id)
)

CREATE TABLE registration
(
    course_id  INTEGER,
    student_id INTEGER,
    grade      TEXT,
    sat        INTEGER,
    primary key (course_id, student_id),
    foreign key (course_id) references course(course_id),
    foreign key (student_id) references student(student_id)
)

CREATE TABLE student
(
    student_id   INTEGER
            primary key,
    f_name       TEXT,
    l_name       TEXT,
    phone_number TEXT,
    email        TEXT,
    intelligence INTEGER,
    gpa          REAL,
    type         TEXT
)

### Question
How many students have the highest intelligence among those taking a bachelor's degree?

### Hint
bachelor's degree is an undergraduate degree in which type = 'UG'; the highest intelligence refers to MAX(intelligence);

### SQL
SELECT COUNT(student_id) FROM student WHERE type = 'UG' AND intelligence = ( SELECT MAX(intelligence) FROM student )