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 and salary of the instructors who are advisors of any student from History department?
SQL: SELECT T2.name ,  T2.salary FROM advisor AS T1 JOIN instructor AS T2 ON T1.i_id  =  T2.id JOIN student AS T3 ON T1.s_id  =  T3.id WHERE T3.dept_name  =  'History'

Question: What are the names and salaries of instructors who advises students in the History department?
SQL: SELECT T2.name ,  T2.salary FROM advisor AS T1 JOIN instructor AS T2 ON T1.i_id  =  T2.id JOIN student AS T3 ON T1.s_id  =  T3.id WHERE T3.dept_name  =  'History'

Question: Find the id of the courses that do not have any prerequisite?
SQL: SELECT course_id FROM course EXCEPT SELECT course_id FROM prereq

Question: What are the ids of courses without prerequisites?
SQL: SELECT course_id FROM course EXCEPT SELECT course_id FROM prereq

Question: Find the name of the courses that do not have any prerequisite?
SQL: SELECT title FROM course WHERE course_id NOT IN (SELECT course_id FROM prereq)

Question: What are the names of courses without prerequisites?
SQL: SELECT title FROM course WHERE course_id NOT IN (SELECT course_id FROM prereq)

Question: What is the title of the prerequisite class of International Finance course?
SQL: SELECT title FROM course WHERE course_id IN (SELECT T1.prereq_id FROM prereq AS T1 JOIN course AS T2 ON T1.course_id  =  T2.course_id WHERE T2.title  =  'International Finance')

Question: Give the title of the prerequisite to the course International Finance.
SQL: SELECT title FROM course WHERE course_id IN (SELECT T1.prereq_id FROM prereq AS T1 JOIN course AS T2 ON T1.course_id  =  T2.course_id WHERE T2.title  =  'International Finance')

Question: Find the title of course whose prerequisite is course Differential Geometry.
SQL: SELECT title FROM course WHERE course_id IN (SELECT T1.course_id FROM prereq AS T1 JOIN course AS T2 ON T1.prereq_id  =  T2.course_id WHERE T2.title  =  'Differential Geometry')

Question: What is the title of the course with Differential Geometry as a prerequisite?
SQL: SELECT title FROM course WHERE course_id IN (SELECT T1.course_id FROM prereq AS T1 JOIN course AS T2 ON T1.prereq_id  =  T2.course_id WHERE T2.title  =  'Differential Geometry')

Question: Find the names of students who have taken any course in the fall semester of year 2003.
SQL: SELECT name FROM student WHERE id IN (SELECT id FROM takes WHERE semester  =  'Fall' AND YEAR  =  2003)

Question: What are the names of students who took a course in the Fall of 2003?
SQL: SELECT name FROM student WHERE id IN (SELECT id FROM takes WHERE semester  =  'Fall' AND YEAR  =  2003)

Question: What is the title of the course that was offered at building Chandler during the fall semester in the year of 2010?
SQL: SELECT T1.title FROM course AS T1 JOIN SECTION AS T2 ON T1.course_id  =  T2.course_id WHERE building  =  'Chandler' AND semester  =  'Fall' AND YEAR  =  2010

Question: Give the title of the course offered in Chandler during the Fall of 2010.
SQL: SELECT T1.title FROM course AS T1 JOIN SECTION AS T2 ON T1.course_id  =  T2.course_id WHERE building  =  'Chandler' AND semester  =  'Fall' AND YEAR  =  2010

Question: Find the name of the instructors who taught C Programming course before.
SQL: SELECT T1.name FROM instructor AS T1 JOIN teaches AS T2 ON T1.id  =  T2.id JOIN course AS T3 ON T2.course_id  =  T3.course_id WHERE T3.title  =  'C Programming'

Question: What are the names of instructors who have taught C Programming courses?
SQL: SELECT T1.name FROM instructor AS T1 JOIN teaches AS T2 ON T1.id  =  T2.id JOIN course AS T3 ON T2.course_id  =  T3.course_id WHERE T3.title  =  'C Programming'

Question: Find the name and salary of instructors who are advisors of the students from the Math department.
SQL: SELECT T2.name ,  T2.salary FROM advisor AS T1 JOIN instructor AS T2 ON T1.i_id  =  T2.id JOIN student AS T3 ON T1.s_id  =  T3.id WHERE T3.dept_name  =  'Math'

Question: What are the names and salaries of instructors who advise students in the Math department?
SQL: SELECT T2.name ,  T2.salary FROM advisor AS T1 JOIN instructor AS T2 ON T1.i_id  =  T2.id JOIN student AS T3 ON T1.s_id  =  T3.id WHERE T3.dept_name  =  'Math'

Question: Find the name of instructors who are advisors of the students from the Math department, and sort the results by students' total credit.
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 WHERE T3.dept_name  =  'Math' ORDER BY T3.tot_cred

Question: What are the names of all instructors who advise students in the math depart sorted by total credits of the student.
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 WHERE T3.dept_name  =  'Math' ORDER BY T3.tot_cred

Question: What is the course title of the prerequisite of course Mobile Computing?
SQL: SELECT title FROM course WHERE course_id IN (SELECT T1.prereq_id FROM prereq AS T1 JOIN course AS T2 ON T1.course_id  =  T2.course_id WHERE T2.title  =  'Mobile Computing')

Question: What is the title of the course that is a prerequisite for Mobile Computing?
SQL: SELECT title FROM course WHERE course_id IN (SELECT T1.prereq_id FROM prereq AS T1 JOIN course AS T2 ON T1.course_id  =  T2.course_id WHERE T2.title  =  'Mobile Computing')