CREATE TABLE classroom (
    building TEXT PRIMARY KEY,
    room_number TEXT,
    capacity NUMBER
);
CREATE TABLE department (
    dept_name TEXT PRIMARY KEY,
    building TEXT,
    budget NUMBER
);
CREATE TABLE course (
    course_id TEXT PRIMARY KEY,
    title TEXT,
    dept_name TEXT,
    credits NUMBER,
    FOREIGN KEY ("dept_name") REFERENCES department("dept_name")
);
CREATE TABLE instructor (
    ID TEXT PRIMARY KEY,
    name TEXT,
    dept_name TEXT,
    salary NUMBER,
    FOREIGN KEY ("dept_name") REFERENCES department("dept_name")
);
CREATE TABLE section (
    course_id TEXT PRIMARY KEY,
    sec_id TEXT,
    semester TEXT,
    year NUMBER,
    building TEXT,
    room_number TEXT,
    time_slot_id TEXT,
    FOREIGN KEY ("building") REFERENCES classroom("building"),
    FOREIGN KEY ("room_number") REFERENCES classroom("room_number"),
    FOREIGN KEY ("course_id") REFERENCES course("course_id")
);
CREATE TABLE teaches (
    ID TEXT PRIMARY KEY,
    course_id TEXT,
    sec_id TEXT,
    semester TEXT,
    year NUMBER,
    FOREIGN KEY ("ID") REFERENCES instructor("ID"),
    FOREIGN KEY ("course_id") REFERENCES section("course_id"),
    FOREIGN KEY ("sec_id") REFERENCES section("sec_id"),
    FOREIGN KEY ("semester") REFERENCES section("semester"),
    FOREIGN KEY ("year") REFERENCES section("year")
);
CREATE TABLE student (
    ID TEXT PRIMARY KEY,
    name TEXT,
    dept_name TEXT,
    tot_cred NUMBER,
    FOREIGN KEY ("dept_name") REFERENCES department("dept_name")
);
CREATE TABLE takes (
    ID TEXT PRIMARY KEY,
    course_id TEXT,
    sec_id TEXT,
    semester TEXT,
    year NUMBER,
    grade TEXT,
    FOREIGN KEY ("ID") REFERENCES student("ID"),
    FOREIGN KEY ("course_id") REFERENCES section("course_id"),
    FOREIGN KEY ("sec_id") REFERENCES section("sec_id"),
    FOREIGN KEY ("semester") REFERENCES section("semester"),
    FOREIGN KEY ("year") REFERENCES section("year")
);
CREATE TABLE advisor (
    s_ID TEXT PRIMARY KEY,
    i_ID TEXT,
    FOREIGN KEY ("s_ID") REFERENCES student("ID"),
    FOREIGN KEY ("i_ID") REFERENCES instructor("ID")
);
CREATE TABLE time_slot (
    time_slot_id TEXT PRIMARY KEY,
    day TEXT,
    start_hr NUMBER,
    start_min NUMBER,
    end_hr NUMBER,
    end_min NUMBER
);
CREATE TABLE prereq (
    course_id TEXT PRIMARY KEY,
    prereq_id TEXT,
    FOREIGN KEY ("prereq_id") REFERENCES course("course_id"),
    FOREIGN KEY ("course_id") REFERENCES course("course_id")
);

Question: Find the name of instructor who is the advisor of the student who has the highest number of total credits.
SQL: SELECT T2.name FROM advisor AS T1 JOIN instructor AS T2 ON T1.i_id  =  T2.id JOIN student AS T3 ON T1.s_id  =  T3.id ORDER BY T3.tot_cred DESC LIMIT 1

Question: What is the name of the instructor who advises the student with the greatest number of total credits?
SQL: SELECT T2.name FROM advisor AS T1 JOIN instructor AS T2 ON T1.i_id  =  T2.id JOIN student AS T3 ON T1.s_id  =  T3.id ORDER BY T3.tot_cred DESC LIMIT 1

Question: Find the name of instructors who didn't teach any courses?
SQL: SELECT name FROM instructor WHERE id NOT IN (SELECT id FROM teaches)

Question: What are the names of instructors who didn't teach?
SQL: SELECT name FROM instructor WHERE id NOT IN (SELECT id FROM teaches)

Question: Find the id of instructors who didn't teach any courses?
SQL: SELECT id FROM instructor EXCEPT SELECT id FROM teaches

Question: What are the ids of instructors who didnt' teach?
SQL: SELECT id FROM instructor EXCEPT SELECT id FROM teaches

Question: Find the names of instructors who didn't each any courses in any Spring semester.
SQL: SELECT name FROM instructor WHERE id NOT IN (SELECT id FROM teaches WHERE semester  =  'Spring')

Question: What are the names of instructors who didn't teach courses in the Spring?
SQL: SELECT name FROM instructor WHERE id NOT IN (SELECT id FROM teaches WHERE semester  =  'Spring')

Question: Find the name of the department which has the highest average salary of professors.
SQL: SELECT dept_name FROM instructor GROUP BY dept_name ORDER BY avg(salary) DESC LIMIT 1

Question: Which department has the highest average instructor salary?
SQL: SELECT dept_name FROM instructor GROUP BY dept_name ORDER BY avg(salary) DESC LIMIT 1

Question: Find the number and averaged salary of all instructors who are in the department with the highest budget.
SQL: SELECT avg(T1.salary) ,  count(*) FROM instructor AS T1 JOIN department AS T2 ON T1.dept_name  =  T2.dept_name ORDER BY T2.budget DESC LIMIT 1

Question: How many instructors are in the department with the highest budget, and what is their average salary?
SQL: SELECT avg(T1.salary) ,  count(*) FROM instructor AS T1 JOIN department AS T2 ON T1.dept_name  =  T2.dept_name ORDER BY T2.budget DESC LIMIT 1

Question: What is the title and credits of the course that is taught in the largest classroom (with the highest capacity)?
SQL: SELECT T3.title ,  T3.credits FROM classroom AS T1 JOIN SECTION AS T2 ON T1.building  =  T2.building AND T1.room_number  =  T2.room_number JOIN course AS T3 ON T2.course_id  =  T3.course_id WHERE T1.capacity  =  (SELECT max(capacity) FROM classroom)

Question: Give the title and credits for the course that is taught in the classroom with the greatest capacity.
SQL: SELECT T3.title ,  T3.credits FROM classroom AS T1 JOIN SECTION AS T2 ON T1.building  =  T2.building AND T1.room_number  =  T2.room_number JOIN course AS T3 ON T2.course_id  =  T3.course_id WHERE T1.capacity  =  (SELECT max(capacity) FROM classroom)

Question: Find the name of students who didn't take any course from Biology department.
SQL: SELECT name FROM student WHERE id NOT IN (SELECT T1.id FROM takes AS T1 JOIN course AS T2 ON T1.course_id  =  T2.course_id WHERE T2.dept_name  =  'Biology')

Question: What are the names of students who haven't taken any Biology courses?
SQL: SELECT name FROM student WHERE id NOT IN (SELECT T1.id FROM takes AS T1 JOIN course AS T2 ON T1.course_id  =  T2.course_id WHERE T2.dept_name  =  'Biology')

Question: Find the total number of students and total number of instructors for each department.
SQL: SELECT count(DISTINCT T2.id) ,  count(DISTINCT T3.id) ,  T3.dept_name FROM department AS T1 JOIN student AS T2 ON T1.dept_name  =  T2.dept_name JOIN instructor AS T3 ON T1.dept_name  =  T3.dept_name GROUP BY T3.dept_name

Question: How many students and instructors are in each department?
SQL: SELECT count(DISTINCT T2.id) ,  count(DISTINCT T3.id) ,  T3.dept_name FROM department AS T1 JOIN student AS T2 ON T1.dept_name  =  T2.dept_name JOIN instructor AS T3 ON T1.dept_name  =  T3.dept_name GROUP BY T3.dept_name

Question: Find the name of students who have taken the prerequisite course of the course with title International Finance.
SQL: SELECT T1.name FROM student AS T1 JOIN takes AS T2 ON T1.id  =  T2.id WHERE T2.course_id IN (SELECT T4.prereq_id FROM course AS T3 JOIN prereq AS T4 ON T3.course_id  =  T4.course_id WHERE T3.title  =  'International Finance')

Question: What are the names of students who have taken the prerequisite for the course International Finance?
SQL: SELECT T1.name FROM student AS T1 JOIN takes AS T2 ON T1.id  =  T2.id WHERE T2.course_id IN (SELECT T4.prereq_id FROM course AS T3 JOIN prereq AS T4 ON T3.course_id  =  T4.course_id WHERE T3.title  =  'International Finance')

Question: Find the name and salary of instructors whose salary is below the average salary of the instructors in the Physics department.
SQL: SELECT name ,  salary FROM instructor WHERE salary  <  (SELECT avg(salary) FROM instructor WHERE dept_name  =  'Physics')

Question: What are the names and salaries for instructors who earn less than the average salary of instructors in the Physics department?
SQL: SELECT name ,  salary FROM instructor WHERE salary  <  (SELECT avg(salary) FROM instructor WHERE dept_name  =  'Physics')