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 students who took some course offered by Statistics department.
SQL: SELECT T3.name FROM course AS T1 JOIN takes AS T2 ON T1.course_id  =  T2.course_id JOIN student AS T3 ON T2.id  =  T3.id WHERE T1.dept_name  =  'Statistics'

Question: What are the names of students who have taken Statistics courses?
SQL: SELECT T3.name FROM course AS T1 JOIN takes AS T2 ON T1.course_id  =  T2.course_id JOIN student AS T3 ON T2.id  =  T3.id WHERE T1.dept_name  =  'Statistics'

Question: Find the building, room number, semester and year of all courses offered by Psychology department sorted by course titles.
SQL: SELECT T2.building ,  T2.room_number ,  T2.semester ,  T2.year FROM course AS T1 JOIN SECTION AS T2 ON T1.course_id  =  T2.course_id WHERE T1.dept_name  =  'Psychology' ORDER BY T1.title

Question: What are the building, room number, semester and year of courses in the Psychology department, sorted using course title?
SQL: SELECT T2.building ,  T2.room_number ,  T2.semester ,  T2.year FROM course AS T1 JOIN SECTION AS T2 ON T1.course_id  =  T2.course_id WHERE T1.dept_name  =  'Psychology' ORDER BY T1.title

Question: Find the names of all instructors in computer science department
SQL: SELECT name FROM instructor WHERE dept_name  =  'Comp. Sci.'

Question: What are the names of all instructors in the Comp. Sci. department?
SQL: SELECT name FROM instructor WHERE dept_name  =  'Comp. Sci.'

Question: Find the names of all instructors in Comp. Sci. department with salary > 80000.
SQL: SELECT name FROM instructor WHERE dept_name  =  'Comp. Sci.'  AND salary  >  80000

Question: What are the names of the instructors in the Comp. Sci. department who earn more than 80000?
SQL: SELECT name FROM instructor WHERE dept_name  =  'Comp. Sci.'  AND salary  >  80000

Question: Find the names of all instructors who have taught some course and the course_id.
SQL: SELECT name ,  course_id FROM instructor AS T1 JOIN teaches AS T2 ON T1.ID  =  T2.ID

Question: What are the names of all instructors who have taught a course, as well as the corresponding course id?
SQL: SELECT name ,  course_id FROM instructor AS T1 JOIN teaches AS T2 ON T1.ID  =  T2.ID

Question: Find the names of all instructors in the Art department who have taught some course and the course_id.
SQL: SELECT name ,  course_id FROM instructor AS T1 JOIN teaches AS T2 ON T1.ID  =  T2.ID WHERE T1.dept_name  =  'Art'

Question: What are the names of Art instructors who have taught a course, and the corresponding course id?
SQL: SELECT name ,  course_id FROM instructor AS T1 JOIN teaches AS T2 ON T1.ID  =  T2.ID WHERE T1.dept_name  =  'Art'

Question: Find the names of all instructors whose name includes the substring “dar”.
SQL: SELECT name FROM instructor WHERE name LIKE '%dar%'

Question: What are the names of all instructors with names that include "dar"?
SQL: SELECT name FROM instructor WHERE name LIKE '%dar%'

Question: List in alphabetic order the names of all distinct instructors.
SQL: SELECT DISTINCT name FROM  instructor ORDER BY name

Question: List the distinct names of the instructors, ordered by name.
SQL: SELECT DISTINCT name FROM  instructor ORDER BY name

Question: Find courses that ran in Fall 2009 or in Spring 2010.
SQL: SELECT course_id FROM SECTION WHERE semester  =  'Fall' AND YEAR  =  2009 UNION SELECT course_id FROM SECTION WHERE semester  =  'Spring' AND YEAR  =  2010

Question: What are the ids for courses in the Fall of 2009 or the Spring of 2010?
SQL: SELECT course_id FROM SECTION WHERE semester  =  'Fall' AND YEAR  =  2009 UNION SELECT course_id FROM SECTION WHERE semester  =  'Spring' AND YEAR  =  2010

Question: Find courses that ran in Fall 2009 and in Spring 2010.
SQL: SELECT course_id FROM SECTION WHERE semester  =  'Fall' AND YEAR  =  2009 INTERSECT SELECT course_id FROM SECTION WHERE semester  =  'Spring' AND YEAR  =  2010

Question: What are the ids for courses that were offered in both Fall of 2009 and Spring of 2010?
SQL: SELECT course_id FROM SECTION WHERE semester  =  'Fall' AND YEAR  =  2009 INTERSECT SELECT course_id FROM SECTION WHERE semester  =  'Spring' AND YEAR  =  2010

Question: Find courses that ran in Fall 2009 but not in Spring 2010.
SQL: SELECT course_id FROM SECTION WHERE semester  =  'Fall' AND YEAR  =  2009 EXCEPT SELECT course_id FROM SECTION WHERE semester  =  'Spring' AND YEAR  =  2010

Question: What are the ids of courses offered in Fall of 2009 but not in Spring of 2010?
SQL: SELECT course_id FROM SECTION WHERE semester  =  'Fall' AND YEAR  =  2009 EXCEPT SELECT course_id FROM SECTION WHERE semester  =  'Spring' AND YEAR  =  2010

Question: Find the salaries of all distinct instructors that are less than the largest salary.
SQL: SELECT DISTINCT salary FROM instructor WHERE salary  <  (SELECT max(salary) FROM instructor)

Question: What are the distinct salaries of all instructors who earned less than the maximum salary?
SQL: SELECT DISTINCT salary FROM instructor WHERE salary  <  (SELECT max(salary) FROM instructor)

Question: Find the total number of instructors who teach a course in the Spring 2010 semester.
SQL: SELECT COUNT (DISTINCT ID) FROM teaches WHERE semester  =  'Spring' AND YEAR  =  2010

Question: How many instructors teach a course in the Spring of 2010?
SQL: SELECT COUNT (DISTINCT ID) FROM teaches WHERE semester  =  'Spring' AND YEAR  =  2010