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

CREATE TABLE person
(
    p_id           INTEGER
        constraint person_pk
            primary key,
    professor      INTEGER,
    student        INTEGER,
    hasPosition    TEXT,
    inPhase        TEXT,
    yearsInProgram TEXT
)

CREATE TABLE "advisedBy"
(
    p_id       INTEGER,
    p_id_dummy INTEGER,
    constraint advisedBy_pk
        primary key (p_id, p_id_dummy),
    constraint advisedBy_person_p_id_p_id_fk
        foreign key (p_id, p_id_dummy) references person (p_id, p_id)
)

CREATE TABLE taughtBy
(
    course_id INTEGER,
    p_id      INTEGER,
    primary key (course_id, p_id),
    foreign key (p_id) references person(p_id),
    foreign key (course_id) references course(course_id)
)

### Question
How many basic and medium undergraduate courses are there?

### Hint
basic and medium undergraduate courses refers to courseLevel = 'Level_300' and courses refers to course.course_id

### SQL
SELECT COUNT(*) FROM course WHERE courseLevel = 'Level_300'
### Database Schema
CREATE TABLE course
(
    course_id   INTEGER
        constraint course_pk
            primary key,
    courseLevel TEXT
)

CREATE TABLE person
(
    p_id           INTEGER
        constraint person_pk
            primary key,
    professor      INTEGER,
    student        INTEGER,
    hasPosition    TEXT,
    inPhase        TEXT,
    yearsInProgram TEXT
)

CREATE TABLE "advisedBy"
(
    p_id       INTEGER,
    p_id_dummy INTEGER,
    constraint advisedBy_pk
        primary key (p_id, p_id_dummy),
    constraint advisedBy_person_p_id_p_id_fk
        foreign key (p_id, p_id_dummy) references person (p_id, p_id)
)

CREATE TABLE taughtBy
(
    course_id INTEGER,
    p_id      INTEGER,
    primary key (course_id, p_id),
    foreign key (p_id) references person(p_id),
    foreign key (course_id) references course(course_id)
)

### Question
How many people teaches course no.11?

### Hint
people refers to taughtBy.p_id; course no.11 refers to course_id = 11

### SQL
SELECT COUNT(*) FROM taughtBy WHERE course_id = 11
### Database Schema
CREATE TABLE course
(
    course_id   INTEGER
        constraint course_pk
            primary key,
    courseLevel TEXT
)

CREATE TABLE person
(
    p_id           INTEGER
        constraint person_pk
            primary key,
    professor      INTEGER,
    student        INTEGER,
    hasPosition    TEXT,
    inPhase        TEXT,
    yearsInProgram TEXT
)

CREATE TABLE "advisedBy"
(
    p_id       INTEGER,
    p_id_dummy INTEGER,
    constraint advisedBy_pk
        primary key (p_id, p_id_dummy),
    constraint advisedBy_person_p_id_p_id_fk
        foreign key (p_id, p_id_dummy) references person (p_id, p_id)
)

CREATE TABLE taughtBy
(
    course_id INTEGER,
    p_id      INTEGER,
    primary key (course_id, p_id),
    foreign key (p_id) references person(p_id),
    foreign key (course_id) references course(course_id)
)

### Question
Which course has more teachers, course no.16 or course no.18?

### Hint
teachers refers to taughtBy.p_id; course no.16 refers to course_id = 16; course no.18 refers to course_id = 18

### SQL
SELECT course_id FROM taughtBy WHERE course_id = 11 OR course_id = 18 GROUP BY course_id ORDER BY COUNT(course_id) DESC LIMIT 1
### Database Schema
CREATE TABLE course
(
    course_id   INTEGER
        constraint course_pk
            primary key,
    courseLevel TEXT
)

CREATE TABLE person
(
    p_id           INTEGER
        constraint person_pk
            primary key,
    professor      INTEGER,
    student        INTEGER,
    hasPosition    TEXT,
    inPhase        TEXT,
    yearsInProgram TEXT
)

CREATE TABLE "advisedBy"
(
    p_id       INTEGER,
    p_id_dummy INTEGER,
    constraint advisedBy_pk
        primary key (p_id, p_id_dummy),
    constraint advisedBy_person_p_id_p_id_fk
        foreign key (p_id, p_id_dummy) references person (p_id, p_id)
)

CREATE TABLE taughtBy
(
    course_id INTEGER,
    p_id      INTEGER,
    primary key (course_id, p_id),
    foreign key (p_id) references person(p_id),
    foreign key (course_id) references course(course_id)
)

### Question
How many teachers are faculty employees?

### Hint
teachers refers to professor = 1; faculty employees refers to hasPosition = 'Faculty_eme'

### SQL
SELECT COUNT(*) FROM person WHERE hasPosition = 'Faculty_eme'
### Database Schema
CREATE TABLE course
(
    course_id   INTEGER
        constraint course_pk
            primary key,
    courseLevel TEXT
)

CREATE TABLE person
(
    p_id           INTEGER
        constraint person_pk
            primary key,
    professor      INTEGER,
    student        INTEGER,
    hasPosition    TEXT,
    inPhase        TEXT,
    yearsInProgram TEXT
)

CREATE TABLE "advisedBy"
(
    p_id       INTEGER,
    p_id_dummy INTEGER,
    constraint advisedBy_pk
        primary key (p_id, p_id_dummy),
    constraint advisedBy_person_p_id_p_id_fk
        foreign key (p_id, p_id_dummy) references person (p_id, p_id)
)

CREATE TABLE taughtBy
(
    course_id INTEGER,
    p_id      INTEGER,
    primary key (course_id, p_id),
    foreign key (p_id) references person(p_id),
    foreign key (course_id) references course(course_id)
)

### Question
Please list the IDs of the teachers who have advised more than 4 others to teach.

### Hint
teachers refers to p_id_dummy; have advised more than 4 others refers to count(advisedBy.p_id) > 4

### SQL
SELECT p_id_dummy FROM advisedBy GROUP BY p_id_dummy HAVING COUNT(p_id_dummy) > 4
### Database Schema
CREATE TABLE course
(
    course_id   INTEGER
        constraint course_pk
            primary key,
    courseLevel TEXT
)

CREATE TABLE person
(
    p_id           INTEGER
        constraint person_pk
            primary key,
    professor      INTEGER,
    student        INTEGER,
    hasPosition    TEXT,
    inPhase        TEXT,
    yearsInProgram TEXT
)

CREATE TABLE "advisedBy"
(
    p_id       INTEGER,
    p_id_dummy INTEGER,
    constraint advisedBy_pk
        primary key (p_id, p_id_dummy),
    constraint advisedBy_person_p_id_p_id_fk
        foreign key (p_id, p_id_dummy) references person (p_id, p_id)
)

CREATE TABLE taughtBy
(
    course_id INTEGER,
    p_id      INTEGER,
    primary key (course_id, p_id),
    foreign key (p_id) references person(p_id),
    foreign key (course_id) references course(course_id)
)

### Question
How many basic or medium undergraduate courses are taught by a professor?

### Hint
basic or medium undergraduate courses refers to courseLevel = 'Level_300'; professor refers to professor = 1

### SQL
SELECT COUNT(*) FROM course AS T1 INNER JOIN taughtBy AS T2 ON T1.course_id = T2.course_id INNER JOIN person AS T3 ON T3.p_id = T2.p_id WHERE T1.courseLevel = 'Level_300' AND T3.professor = 1
### Database Schema
CREATE TABLE course
(
    course_id   INTEGER
        constraint course_pk
            primary key,
    courseLevel TEXT
)

CREATE TABLE person
(
    p_id           INTEGER
        constraint person_pk
            primary key,
    professor      INTEGER,
    student        INTEGER,
    hasPosition    TEXT,
    inPhase        TEXT,
    yearsInProgram TEXT
)

CREATE TABLE "advisedBy"
(
    p_id       INTEGER,
    p_id_dummy INTEGER,
    constraint advisedBy_pk
        primary key (p_id, p_id_dummy),
    constraint advisedBy_person_p_id_p_id_fk
        foreign key (p_id, p_id_dummy) references person (p_id, p_id)
)

CREATE TABLE taughtBy
(
    course_id INTEGER,
    p_id      INTEGER,
    primary key (course_id, p_id),
    foreign key (p_id) references person(p_id),
    foreign key (course_id) references course(course_id)
)

### Question
Please list the IDs of all the faculty employees who teaches a basic or medium undergraduate course.

### Hint
faculty employees refers to hasPosition = 'Faculty_eme'; basic or medium undergraduate course refers to courseLevel = 'Level_300'

### SQL
SELECT T2.p_id FROM course AS T1 INNER JOIN taughtBy AS T2 ON T1.course_id = T2.course_id INNER JOIN person AS T3 ON T3.p_id = T2.p_id WHERE T1.courseLevel = 'Level_300' AND T3.hasPosition = 'Faculty_eme'
### Database Schema
CREATE TABLE course
(
    course_id   INTEGER
        constraint course_pk
            primary key,
    courseLevel TEXT
)

CREATE TABLE person
(
    p_id           INTEGER
        constraint person_pk
            primary key,
    professor      INTEGER,
    student        INTEGER,
    hasPosition    TEXT,
    inPhase        TEXT,
    yearsInProgram TEXT
)

CREATE TABLE "advisedBy"
(
    p_id       INTEGER,
    p_id_dummy INTEGER,
    constraint advisedBy_pk
        primary key (p_id, p_id_dummy),
    constraint advisedBy_person_p_id_p_id_fk
        foreign key (p_id, p_id_dummy) references person (p_id, p_id)
)

CREATE TABLE taughtBy
(
    course_id INTEGER,
    p_id      INTEGER,
    primary key (course_id, p_id),
    foreign key (p_id) references person(p_id),
    foreign key (course_id) references course(course_id)
)

### Question
Is the teacher who teaches course no.9 a faculty member?

### Hint
teacher refers to taughtBy.p_id; course no.9 refers to taughtBy.course_id = 9; faculty member refers to hasPosition ! = 0

### SQL
SELECT T2.hasPosition FROM taughtBy AS T1 INNER JOIN person AS T2 ON T1.p_id = T2.p_id WHERE T1.course_id = 9
### Database Schema
CREATE TABLE course
(
    course_id   INTEGER
        constraint course_pk
            primary key,
    courseLevel TEXT
)

CREATE TABLE person
(
    p_id           INTEGER
        constraint person_pk
            primary key,
    professor      INTEGER,
    student        INTEGER,
    hasPosition    TEXT,
    inPhase        TEXT,
    yearsInProgram TEXT
)

CREATE TABLE "advisedBy"
(
    p_id       INTEGER,
    p_id_dummy INTEGER,
    constraint advisedBy_pk
        primary key (p_id, p_id_dummy),
    constraint advisedBy_person_p_id_p_id_fk
        foreign key (p_id, p_id_dummy) references person (p_id, p_id)
)

CREATE TABLE taughtBy
(
    course_id INTEGER,
    p_id      INTEGER,
    primary key (course_id, p_id),
    foreign key (p_id) references person(p_id),
    foreign key (course_id) references course(course_id)
)

### Question
Please list the levels of the all courses taught by teacher no.79.

### Hint
levels of the all courses refers to courseLevel; teacher no.79 refers to taughtBy.p_id = 79

### SQL
SELECT T1.courseLevel FROM course AS T1 INNER JOIN taughtBy AS T2 ON T1.course_id = T2.course_id WHERE T2.p_id = 79
### Database Schema
CREATE TABLE course
(
    course_id   INTEGER
        constraint course_pk
            primary key,
    courseLevel TEXT
)

CREATE TABLE person
(
    p_id           INTEGER
        constraint person_pk
            primary key,
    professor      INTEGER,
    student        INTEGER,
    hasPosition    TEXT,
    inPhase        TEXT,
    yearsInProgram TEXT
)

CREATE TABLE "advisedBy"
(
    p_id       INTEGER,
    p_id_dummy INTEGER,
    constraint advisedBy_pk
        primary key (p_id, p_id_dummy),
    constraint advisedBy_person_p_id_p_id_fk
        foreign key (p_id, p_id_dummy) references person (p_id, p_id)
)

CREATE TABLE taughtBy
(
    course_id INTEGER,
    p_id      INTEGER,
    primary key (course_id, p_id),
    foreign key (p_id) references person(p_id),
    foreign key (course_id) references course(course_id)
)

### Question
Please list the IDs of the advisors of the students who are in the 5th year of their program.

### Hint
IDs of the advisors refers to p_id_dummy; in the 5th year of their program refers to yearsInProgram = 'Year_5'

### SQL
SELECT T1.p_id_dummy FROM advisedBy AS T1 INNER JOIN person AS T2 ON T1.p_id = T2.p_id WHERE T2.yearsInProgram = 'Year_5'
### Database Schema
CREATE TABLE course
(
    course_id   INTEGER
        constraint course_pk
            primary key,
    courseLevel TEXT
)

CREATE TABLE person
(
    p_id           INTEGER
        constraint person_pk
            primary key,
    professor      INTEGER,
    student        INTEGER,
    hasPosition    TEXT,
    inPhase        TEXT,
    yearsInProgram TEXT
)

CREATE TABLE "advisedBy"
(
    p_id       INTEGER,
    p_id_dummy INTEGER,
    constraint advisedBy_pk
        primary key (p_id, p_id_dummy),
    constraint advisedBy_person_p_id_p_id_fk
        foreign key (p_id, p_id_dummy) references person (p_id, p_id)
)

CREATE TABLE taughtBy
(
    course_id INTEGER,
    p_id      INTEGER,
    primary key (course_id, p_id),
    foreign key (p_id) references person(p_id),
    foreign key (course_id) references course(course_id)
)

### Question
How many students are advised to teach by a professor teaching basic or medium undergraduate courses?

### Hint
students refers to advisedBy.p_id; professor refers to p_id_dummy and taughtBy.p_id and professor = 1; basic or medium undergraduate courses refers to courseLevel = 'Level_300'

### SQL
SELECT COUNT(DISTINCT T4.p_id) FROM person AS T1 INNER JOIN taughtBy AS T2 ON T1.p_id = T2.p_id INNER JOIN course AS T3 ON T3.course_id = T2.course_id INNER JOIN advisedBy AS T4 ON T4.p_id = T1.p_id WHERE T1.professor = 1 AND T3.courseLevel = 'Level_300'
### Database Schema
CREATE TABLE course
(
    course_id   INTEGER
        constraint course_pk
            primary key,
    courseLevel TEXT
)

CREATE TABLE person
(
    p_id           INTEGER
        constraint person_pk
            primary key,
    professor      INTEGER,
    student        INTEGER,
    hasPosition    TEXT,
    inPhase        TEXT,
    yearsInProgram TEXT
)

CREATE TABLE "advisedBy"
(
    p_id       INTEGER,
    p_id_dummy INTEGER,
    constraint advisedBy_pk
        primary key (p_id, p_id_dummy),
    constraint advisedBy_person_p_id_p_id_fk
        foreign key (p_id, p_id_dummy) references person (p_id, p_id)
)

CREATE TABLE taughtBy
(
    course_id INTEGER,
    p_id      INTEGER,
    primary key (course_id, p_id),
    foreign key (p_id) references person(p_id),
    foreign key (course_id) references course(course_id)
)

### Question
Among the courses that are basic or medium undergraduate courses, how many of them are taught by a faculty member?

### Hint
courses that are basic or medium undergraduate courses refers to courseLevel = 'Level_300'; faculty member refers to hasPosition ! = 0

### SQL
SELECT COUNT(*) FROM course AS T1 INNER JOIN taughtBy AS T2 ON T1.course_id = T2.course_id INNER JOIN person AS T3 ON T2.p_id = T3.p_id WHERE T3.professor = 1 AND T1.courseLevel = 'Level_300'
### Database Schema
CREATE TABLE course
(
    course_id   INTEGER
        constraint course_pk
            primary key,
    courseLevel TEXT
)

CREATE TABLE person
(
    p_id           INTEGER
        constraint person_pk
            primary key,
    professor      INTEGER,
    student        INTEGER,
    hasPosition    TEXT,
    inPhase        TEXT,
    yearsInProgram TEXT
)

CREATE TABLE "advisedBy"
(
    p_id       INTEGER,
    p_id_dummy INTEGER,
    constraint advisedBy_pk
        primary key (p_id, p_id_dummy),
    constraint advisedBy_person_p_id_p_id_fk
        foreign key (p_id, p_id_dummy) references person (p_id, p_id)
)

CREATE TABLE taughtBy
(
    course_id INTEGER,
    p_id      INTEGER,
    primary key (course_id, p_id),
    foreign key (p_id) references person(p_id),
    foreign key (course_id) references course(course_id)
)

### Question
For the professor who advised student no.6, please list the IDs of the courses he or she teaches.

### Hint
professor refers to p_id_dummy and professor = 1; student no.6 refers to advisedBy.p_id = 6; IDs of the courses refers to taughtBy.course_id

### SQL
SELECT T2.course_id FROM taughtBy AS T1 INNER JOIN course AS T2 ON T1.course_id = T2.course_id INNER JOIN advisedBy AS T3 ON T3.p_id = T1.p_id WHERE T1.p_id = 9
### Database Schema
CREATE TABLE course
(
    course_id   INTEGER
        constraint course_pk
            primary key,
    courseLevel TEXT
)

CREATE TABLE person
(
    p_id           INTEGER
        constraint person_pk
            primary key,
    professor      INTEGER,
    student        INTEGER,
    hasPosition    TEXT,
    inPhase        TEXT,
    yearsInProgram TEXT
)

CREATE TABLE "advisedBy"
(
    p_id       INTEGER,
    p_id_dummy INTEGER,
    constraint advisedBy_pk
        primary key (p_id, p_id_dummy),
    constraint advisedBy_person_p_id_p_id_fk
        foreign key (p_id, p_id_dummy) references person (p_id, p_id)
)

CREATE TABLE taughtBy
(
    course_id INTEGER,
    p_id      INTEGER,
    primary key (course_id, p_id),
    foreign key (p_id) references person(p_id),
    foreign key (course_id) references course(course_id)
)

### Question
What is the level of the course with the most number of teachers?

### Hint
level of the course refers to courseLevel; course with most number of teachers refers to course_id = max(count(taughtBy.p_id))

### SQL
SELECT T1.courseLevel FROM course AS T1 INNER JOIN taughtBy AS T2 ON T1.course_id = T2.course_id GROUP BY T2.course_id ORDER BY COUNT(T2.p_id) DESC LIMIT 1
### Database Schema
CREATE TABLE course
(
    course_id   INTEGER
        constraint course_pk
            primary key,
    courseLevel TEXT
)

CREATE TABLE person
(
    p_id           INTEGER
        constraint person_pk
            primary key,
    professor      INTEGER,
    student        INTEGER,
    hasPosition    TEXT,
    inPhase        TEXT,
    yearsInProgram TEXT
)

CREATE TABLE "advisedBy"
(
    p_id       INTEGER,
    p_id_dummy INTEGER,
    constraint advisedBy_pk
        primary key (p_id, p_id_dummy),
    constraint advisedBy_person_p_id_p_id_fk
        foreign key (p_id, p_id_dummy) references person (p_id, p_id)
)

CREATE TABLE taughtBy
(
    course_id INTEGER,
    p_id      INTEGER,
    primary key (course_id, p_id),
    foreign key (p_id) references person(p_id),
    foreign key (course_id) references course(course_id)
)

### Question
Please list the IDs of the professors that teaches more than 3 courses.

### Hint
IDs of the professors refers to taughtBy.p_id and professor = 1; teaches more than 3 courses  refers to count(course_id) > 3

### SQL
SELECT T1.p_id FROM taughtBy AS T1 INNER JOIN person AS T2 ON T1.p_id = T2.p_id WHERE T2.professor = 1 GROUP BY T1.p_id HAVING COUNT(DISTINCT T1.course_id) > 3
### Database Schema
CREATE TABLE course
(
    course_id   INTEGER
        constraint course_pk
            primary key,
    courseLevel TEXT
)

CREATE TABLE person
(
    p_id           INTEGER
        constraint person_pk
            primary key,
    professor      INTEGER,
    student        INTEGER,
    hasPosition    TEXT,
    inPhase        TEXT,
    yearsInProgram TEXT
)

CREATE TABLE "advisedBy"
(
    p_id       INTEGER,
    p_id_dummy INTEGER,
    constraint advisedBy_pk
        primary key (p_id, p_id_dummy),
    constraint advisedBy_person_p_id_p_id_fk
        foreign key (p_id, p_id_dummy) references person (p_id, p_id)
)

CREATE TABLE taughtBy
(
    course_id INTEGER,
    p_id      INTEGER,
    primary key (course_id, p_id),
    foreign key (p_id) references person(p_id),
    foreign key (course_id) references course(course_id)
)

### Question
Please list the IDs of the top 3 professors that teaches the most courses.

### Hint
IDs of the professors refers to taughtBy.p_id and professor = 1; teaches the most courses refers to max(count(course_id))

### SQL
SELECT T1.p_id FROM taughtBy AS T1 INNER JOIN person AS T2 ON T1.p_id = T2.p_id WHERE T2.professor = 1 GROUP BY T1.p_id ORDER BY COUNT(*) DESC LIMIT 3
### Database Schema
CREATE TABLE course
(
    course_id   INTEGER
        constraint course_pk
            primary key,
    courseLevel TEXT
)

CREATE TABLE person
(
    p_id           INTEGER
        constraint person_pk
            primary key,
    professor      INTEGER,
    student        INTEGER,
    hasPosition    TEXT,
    inPhase        TEXT,
    yearsInProgram TEXT
)

CREATE TABLE "advisedBy"
(
    p_id       INTEGER,
    p_id_dummy INTEGER,
    constraint advisedBy_pk
        primary key (p_id, p_id_dummy),
    constraint advisedBy_person_p_id_p_id_fk
        foreign key (p_id, p_id_dummy) references person (p_id, p_id)
)

CREATE TABLE taughtBy
(
    course_id INTEGER,
    p_id      INTEGER,
    primary key (course_id, p_id),
    foreign key (p_id) references person(p_id),
    foreign key (course_id) references course(course_id)
)

### Question
In total, all the students in the 3rd year of their program are advised by how many professors?

### Hint
3rd year of their program refers to yearsInProgram = 'Year_3'; professors refers to p_id_dummy

### SQL
SELECT COUNT(DISTINCT T1.p_id_dummy) FROM advisedBy AS T1 INNER JOIN person AS T2 ON T1.p_id = T2.p_id WHERE T2.yearsInProgram = 'Year_3'
### Database Schema
CREATE TABLE course
(
    course_id   INTEGER
        constraint course_pk
            primary key,
    courseLevel TEXT
)

CREATE TABLE person
(
    p_id           INTEGER
        constraint person_pk
            primary key,
    professor      INTEGER,
    student        INTEGER,
    hasPosition    TEXT,
    inPhase        TEXT,
    yearsInProgram TEXT
)

CREATE TABLE "advisedBy"
(
    p_id       INTEGER,
    p_id_dummy INTEGER,
    constraint advisedBy_pk
        primary key (p_id, p_id_dummy),
    constraint advisedBy_person_p_id_p_id_fk
        foreign key (p_id, p_id_dummy) references person (p_id, p_id)
)

CREATE TABLE taughtBy
(
    course_id INTEGER,
    p_id      INTEGER,
    primary key (course_id, p_id),
    foreign key (p_id) references person(p_id),
    foreign key (course_id) references course(course_id)
)

### Question
What is the average number of courses taught by a professor?

### Hint
professor refers to professor = 1; average number of courses = divide(count(taughtBy.course_id), count(taughtBy.p_id) where professor = 1 )

### SQL
SELECT CAST(COUNT(T1.course_id) AS REAL) / COUNT(DISTINCT T2.p_id) AS num FROM taughtBy AS T1 INNER JOIN person AS T2 ON T1.p_id = T2.p_id WHERE T2.professor = 1
### Database Schema
CREATE TABLE course
(
    course_id   INTEGER
        constraint course_pk
            primary key,
    courseLevel TEXT
)

CREATE TABLE person
(
    p_id           INTEGER
        constraint person_pk
            primary key,
    professor      INTEGER,
    student        INTEGER,
    hasPosition    TEXT,
    inPhase        TEXT,
    yearsInProgram TEXT
)

CREATE TABLE "advisedBy"
(
    p_id       INTEGER,
    p_id_dummy INTEGER,
    constraint advisedBy_pk
        primary key (p_id, p_id_dummy),
    constraint advisedBy_person_p_id_p_id_fk
        foreign key (p_id, p_id_dummy) references person (p_id, p_id)
)

CREATE TABLE taughtBy
(
    course_id INTEGER,
    p_id      INTEGER,
    primary key (course_id, p_id),
    foreign key (p_id) references person(p_id),
    foreign key (course_id) references course(course_id)
)

### Question
What is the ratio of professors and students?

### Hint
professors refers to professor = 1; students refers to student = 1; ratio = divide(count(person.p_id) when professor = 1, count(person.p_id) when student = 1)

### SQL
SELECT CAST(SUM(CASE  WHEN professor = 1 THEN 1 ELSE 0 END) AS REAL) * 100 / SUM(CASE  WHEN student = 1 THEN 1 ELSE 0 END) AS per FROM person
### Database Schema
CREATE TABLE course
(
    course_id   INTEGER
        constraint course_pk
            primary key,
    courseLevel TEXT
)

CREATE TABLE person
(
    p_id           INTEGER
        constraint person_pk
            primary key,
    professor      INTEGER,
    student        INTEGER,
    hasPosition    TEXT,
    inPhase        TEXT,
    yearsInProgram TEXT
)

CREATE TABLE "advisedBy"
(
    p_id       INTEGER,
    p_id_dummy INTEGER,
    constraint advisedBy_pk
        primary key (p_id, p_id_dummy),
    constraint advisedBy_person_p_id_p_id_fk
        foreign key (p_id, p_id_dummy) references person (p_id, p_id)
)

CREATE TABLE taughtBy
(
    course_id INTEGER,
    p_id      INTEGER,
    primary key (course_id, p_id),
    foreign key (p_id) references person(p_id),
    foreign key (course_id) references course(course_id)
)

### Question
Calculate the percentage of high-level undergraduate course.

### Hint
high-level undergraduate course refers to courseLevel = 'Level_400'; percentage = divide(count(course.course_id) when courseLevel = 'Level_400', count(course.course_id)) * 100%

### SQL
SELECT CAST(SUM(CASE  WHEN courseLevel = 'Level_400' THEN 1 ELSE 0 END) AS REAL) * 100 / COUNT(*) AS per FROM course