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
List the student's first and last name that got a C in the course named "Applied Deep Learning".

### Hint
student's first name refers to f_name; student's last name refers to l_name; got a C refers to grade = 'C';

### 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 = 'C'
### 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 research postgraduate students, give the name of the course with the student satisfaction value of 1.

### Hint
research postgraduate students refers to type = 'RPG'; name of the course refers to name; satisfaction refers to sat; sat = 1;

### 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 T2.sat = 1 AND T1.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
Among the students with a capability below 3, what is the difference of undergraduate students from research postgraduate students?

### Hint
capability < 3; difference = subtract(count(type = 'UG')), (count(type = 'RPG')); undergraduate students refers to type = 'UG'; research postgraduate students refers to type = 'RPG';

### SQL
SELECT SUM(CASE WHEN T2.type = 'UG' THEN 1 ELSE 0 END) - SUM(CASE WHEN T2.type = 'RPG' THEN 1 ELSE 0 END) FROM RA AS T1 INNER JOIN student AS T2 ON T1.student_id = T2.student_id WHERE T1.capability < 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 students for each advisor?

### Hint
students refers to advisedBy.p_id; advisor refers to p_id_dummy; average number = avg(count(advisedBy.p_id))

### SQL
SELECT CAST(COUNT(p_id) AS REAL) / COUNT(DISTINCT p_id_dummy) AS avgnum FROM advisedBy GROUP BY p_id_dummy
### 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 professors are teaching course ID 18?

### Hint
professors refers to taughtBy.p_id; course ID 18 refers to taughtBy.course_id

### SQL
SELECT COUNT(DISTINCT p_id) FROM taughtBy WHERE course_id = 18
### 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
List all the course IDs for professional or master/graduate courses.

### Hint
professional or master/graduate courses refers to courseLevel = 'Level_500'

### SQL
SELECT course_id FROM course WHERE courseLevel = 'Level_500'
### 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 courses are there for basic or medium undergraduate courses?

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

### SQL
SELECT COUNT(course_id) 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
List the ID of all professors who are not faculty member along with the courses taught by him/her.

### Hint
ID of all professors refers to person.p_id where professor = 1; not faculty member refers to hasPosition = 0; courses refers to taughtBy.course_id

### SQL
SELECT T2.p_id, T2.course_id FROM person AS T1 INNER JOIN taughtBy AS T2 ON T1.p_id = T2.p_id WHERE T1.professor = 1 AND T1.hasPosition <> 0
### 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
Provide the ID of professors who are teaching high-level or harder undergraduate course.

### Hint
ID of professors refers to taughtBy.p_id; high-level or harder undergraduate course refers to courseLevel = 'Level_400'

### SQL
SELECT T2.p_id FROM course AS T1 INNER JOIN taughtBy AS T2 ON T1.course_id = T2.course_id WHERE T1.courseLevel = 'Level_400'
### 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 are the courses taught by the advisors who gave advice to student with ID 376?

### Hint
courses refers to course_id; advisors refers to p_id_dummy and taughtBy.p_id; student with ID 376 refers to advisedBy.p_id = 376

### SQL
SELECT T3.course_id FROM advisedBy AS T1 INNER JOIN person AS T2 ON T1.p_id = T2.p_id INNER JOIN taughtBy AS T3 ON T2.p_id = T3.p_id WHERE T1.p_id = 141
### 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
Name the advisors for students in Year 3 of the program.

### Hint
advisors refers to p_id_dummy; students in Year 3 of the program refers to yearsInProgram = 'Year_3'

### SQL
SELECT T1.p_id 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
Which level of courses is taught by professor ID 297?

### Hint
professor ID 297 refers to taughtBy.p_id = 297

### SQL
SELECT T1.courseLevel FROM course AS T1 INNER JOIN taughtBy AS T2 ON T1.course_id = T2.course_id WHERE T2.p_id = 297
### 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 level is course 165? List the professors who teach the course.

### Hint
course 165 refers to course_id = 165; professors refers to taughtBy.p_id

### SQL
SELECT T1.courseLevel, T2.p_id FROM course AS T1 INNER JOIN taughtBy AS T2 ON T1.course_id = T2.course_id WHERE T2.course_id = 165
### 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
List the ID and years in program for students taught by advisor with ID 5.

### Hint
advisor with ID 5 refers to p_id_dummy = 5

### SQL
SELECT T1.p_id, T2.yearsInProgram FROM advisedBy AS T1 INNER JOIN person AS T2 ON T1.p_id = T2.p_id WHERE T1.p_id_dummy = 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
State the courses and level of courses by professors who are faculty employees.

### Hint
professors who are faculty employees refers to professor = 1; faculty employees refers to hasPosition = 'Faculty_eme'

### SQL
SELECT T3.course_id, T3.courseLevel FROM taughtBy AS T1 INNER JOIN person AS T2 ON T1.p_id = T2.p_id INNER JOIN course AS T3 ON T3.course_id = T1.course_id WHERE T2.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
Find the ID of advisor of student ID 80 and state the level of courses taught by him/her.

### Hint
ID of advisor refers to p_id_dummy; student ID 80 refers to advisedBy.p_id = 80; level of courses refers to courseLevel

### SQL
SELECT T1.p_id_dummy, T2.courseLevel FROM advisedBy AS T1 INNER JOIN course AS T2 ON T1.p_id = T2.course_id INNER JOIN taughtBy AS T3 ON T2.course_id = T3.course_id WHERE T1.p_id = 80
### 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
Provide the ID of professors who teach in both harder undergraduate course and master/graduate courses.

### Hint
harder undergraduate course refers to courseLevel = 'Level_400'; master/graduate courses refers to courseLevel = 'Level_500'; ID of professors refers to taughtBy.p_id

### SQL
SELECT DISTINCT T2.p_id FROM course AS T1 INNER JOIN taughtBy AS T2 ON T1.course_id = T2.course_id WHERE T1.courseLevel = 'Level_400' OR T1.courseLevel = 'Level_500'
### 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
Who are the professors who gave advice to students in the 12th years of program?

### Hint
professors refers to p_id_dummy; 12th years of program refers to yearsInProgram = 'Year_12'

### 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_12'
### 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 are the courses with the most number of professors? State the course ID and the level of the course.

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

### SQL
SELECT T1.course_id, T1.courseLevel FROM course AS T1 INNER JOIN taughtBy AS T2 ON T1.course_id = T2.course_id GROUP BY T1.course_id, T1.courseLevel ORDER BY COUNT(T1.course_id) DESC LIMIT 1