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
Among professors with the highest popularity, how many of their students have research capability of 5?

### Hint
highest popularity refers to MAX(popularity); research capability refers to capability; capability = 5;

### SQL
SELECT COUNT(T1.student_id) FROM RA AS T1 INNER JOIN prof AS T2 ON T1.prof_id = T2.prof_id WHERE T1.capability = 5 ORDER BY T2.popularity 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
List the course's name where students acquired a grade of D.

### SQL
SELECT T1.name FROM course AS T1 INNER JOIN registration AS T2 ON T1.course_id = T2.course_id WHERE T2.grade = 'D'
### 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 capability on research of the student named Alvera McQuillin?

### Hint
capability on research refers to capability;

### SQL
SELECT T2.capability FROM student AS T1 INNER JOIN RA AS T2 ON T1.student_id = T2.student_id WHERE T1.f_name = 'Alvera' AND T1.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
Of courses with 3 credit, how many students have GPA of 3.2?

### 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.credit = 3 AND T1.gpa = 3.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
Among students with low salary, how many of them have a gpa of 3.5?

### Hint
low salary refers to salary = 'low';

### SQL
SELECT COUNT(T1.student_id) FROM RA AS T1 INNER JOIN student AS T2 ON T1.student_id = T2.student_id WHERE T2.gpa = 3.5 AND T1.salary = 'low'
### 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
List the student's email with grade of B in a course with difficulty greater than the 80% of average difficulty of all courses.

### Hint
difficulty refers to diff; course with difficulty greater than the 80% of average difficulty refers to diff > MULTIPLY(AVG(diff), 80%);

### SQL
SELECT T2.email FROM registration AS T1 INNER JOIN student AS T2 ON T1.student_id = T2.student_id INNER JOIN course AS T3 ON T1.course_id = T3.course_id WHERE T1.grade = 'B' GROUP BY T3.diff HAVING T3.diff > AVG(T3.diff) * 0.8
### 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 with a teachability of 3 and below, what is the percentage of their student advisees with a low salary?

### Hint
teachability < = 3; percentage = MULTIPLY(DIVIDE(COUNT(salary = 'low'), COUNT(salary)), 1.0);

### SQL
SELECT CAST(SUM(CASE WHEN T1.salary = 'low' THEN 1 ELSE 0 END) AS REAL) * 100 / COUNT(T1.salary) FROM RA AS T1 INNER JOIN prof AS T2 ON T1.prof_id = T2.prof_id WHERE T2.teachingability < 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
Find the most important and most difficult courses.

### Hint
most important refers to MAX(credit); most difficult 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
What is the average teaching ability of the most popular professors?

### Hint
average = AVG(teachingability); most popular professor refers to MAX(popularity);

### SQL
SELECT CAST(SUM(teachingability) AS REAL) / COUNT(prof_id) FROM prof WHERE popularity = ( SELECT MAX(popularity) FROM prof )
### 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
Calculate the average satisfaction of the good students with their courses.

### Hint
average satisfaction = DIVIDE(SUM(sat), COUNT(course_id)); satisfaction refers to sat; good student refers to grade = 'B';

### SQL
SELECT CAST(SUM(sat) AS REAL) / COUNT(course_id) FROM registration WHERE grade = 'B'
### 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 with less than four intelligence, list the full name and phone number of students with a greater than 3 GPA.

### Hint
intelligence < 4; full name = f_name, l_name; gpa > 3;

### SQL
SELECT f_name, l_name, phone_number FROM student WHERE gpa > 3 AND intelligence < 4
### 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
Name the students with above-average capability.

### Hint
name of the students = f_name, l_name; above average-capability refers to capability > AVG(capability);

### SQL
SELECT T1.f_name, T1.l_name FROM student AS T1 INNER JOIN RA AS T2 ON T1.student_id = T2.student_id WHERE T2.capability > ( SELECT AVG(capability) FROM RA )
### 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 students with an intelligence of 5, list the full name and courses taken by them who have less than a 3 GPA.

### Hint
full name of the students = f_name, l_name; gpa < 3;

### SQL
SELECT T1.f_name, T1.l_name, 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.intelligence = 5 AND T1.gpa < 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
What is the average capability of students with less than a 2.5 GPA?

### Hint
average capability = AVG(capability); gpa < 2.5;

### SQL
SELECT CAST(SUM(T1.capability) AS REAL) / COUNT(T1.student_id) FROM RA AS T1 INNER JOIN student AS T2 ON T1.student_id = T2.student_id WHERE T2.gpa < 2.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
List the full name of the professors who advised students with intelligence 1.

### Hint
full name of the professors = first_name, last_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.intelligence = 1