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
Please list the names of the courses taken by Laughton Antonio.

### SQL
SELECT T3.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 T1.f_name = 'Laughton' AND T1.l_name = 'Antonio'
### 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
Which student failed the course Intro to Database 2? Please give his or her full name.

### Hint
If grade is NULL, it means that this student fails to pass the course; full name refers to f_name and l_name;

### 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 T2.grade IS NULL AND T3.name = 'Intro to Database 2'
### 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
Which student is more satisfied with the course Machine Learning Theory, Willie Rechert or Laughton Antonio?

### Hint
sat refers to student's satisfaction degree with the course; more satisfied refers to MAX(sat);

### 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 (T1.f_name = 'Laughton' OR T1.f_name = 'Willie') AND (T1.l_name = 'Antonio' OR T1.l_name = 'Rechert') AND T3.name = 'Machine Learning Theory' ORDER BY T2.sat 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
Among the professors who have more than 3 research assistants, how many of them are male?

### Hint
research assistant refers to the student who serves for research where the abbreviation is RA; more than 3 research assistant refers to COUNT(student_id) > 3;

### SQL
SELECT COUNT(*) FROM ( SELECT T2.prof_id FROM RA AS T1 INNER JOIN prof AS T2 ON T1.prof_id = T2.prof_id WHERE T2.gender = 'Male' GROUP BY T1.prof_id HAVING COUNT(T1.student_id) > 3 )
### 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 students who took the course Machine Learning Theory, how many of them are undergraduates?

### Hint
UG is an abbreviated name of undergraduate student in which type = 'UG';

### SQL
SELECT COUNT(T1.student_id) 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 = 'Machine Learning Theory' AND T1.type = 'UG'
### 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
Which professor advised Willie Rechert to work as a research assistant? Please give his or her full name.

### Hint
research assistant refers to the student who serves for research where the abbreviation is RA; prof_id refers to professor’s ID; full name refers to f_name and l_name;

### SQL
SELECT T1.first_name, T1.last_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 T3.f_name = 'Willie' AND T3.l_name = 'Rechert'
### 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 average gpa of Ogdon Zywicki's research assistants?

### Hint
research assistant refers to the student who serves for research where the abbreviation is RA; prof_id refers to professor’s ID; GPA is an abbreviated name of Grade Point Average where average = AVG(gpa);

### SQL
SELECT SUM(T3.gpa) / 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 average satisfying degree of the course Machine Learning Theory?

### Hint
sat refers to student's satisfaction degree with the course;

### SQL
SELECT CAST(SUM(T1.sat) AS REAL) / COUNT(T1.student_id) FROM registration AS T1 INNER JOIN course AS T2 ON T1.course_id = T2.course_id WHERE T2.name = 'Machine Learning Theory'
### 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 number of research postgraduate students.

### Hint
RPG is an abbreviated name of research postgraduate student in which type = 'RPG';

### SQL
SELECT COUNT(student_id) FROM student WHERE type = 'RPG'
### 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
Which student has the highest gpa? Give the full name.

### Hint
GPA is an abbreviated name of Grade Point Average where highest GPA = MAX(gpa); full name refers to f_name and l_name;

### SQL
SELECT f_name, l_name FROM student WHERE gpa = ( SELECT MAX(gpa) FROM student )
### 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 3-credit course with the easiest difficulty, how many students get an "A" in that course?

### Hint
diff refers to difficulty; diff is higher means the course is more difficult in which easiest difficulty refers to diff = 1; 3-credit course refers to credit = '3'; get an "A" refers to grade = 'A' for the course;

### SQL
SELECT COUNT(T1.student_id) FROM registration AS T1 INNER JOIN course AS T2 ON T1.course_id = T2.course_id WHERE T1.grade = 'A' AND T2.credit = '3' AND T2.diff = 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
How many students took the hardest course?

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

### SQL
SELECT COUNT(T1.student_id) FROM registration AS T1 INNER JOIN course AS T2 ON T1.course_id = T2.course_id WHERE T2.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
Which professor is Oliy Spratling working with? Give the full name.

### Hint
research assistant refers to the student who serves for research where the abbreviation is RA; full name refers to f_name and l_name;

### SQL
SELECT T1.first_name, T1.last_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 T3.f_name = 'Oliy' AND T3.l_name = 'Spratling'
### 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 professor who is working with Harrietta Lydford, how is his popularity?

### Hint
research assistant refers to the student who serves for research where the abbreviation is RA; higher popularity means more popular; prof_id refers to professor’s ID;

### SQL
SELECT T1.popularity 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 T3.f_name = 'Harrietta' AND T3.l_name = 'Lydford'