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: list in alphabetic order all course names and their instructors' names in year 2008.
SQL: SELECT T1.title ,  T3.name FROM course AS T1 JOIN teaches AS T2 ON T1.course_id  =  T2.course_id JOIN instructor AS T3 ON T2.id  =  T3.id WHERE YEAR  =  2008 ORDER BY T1.title

Question: Show all titles and their instructors' names for courses in 2008, in alphabetical order by title.
SQL: SELECT T1.title ,  T3.name FROM course AS T1 JOIN teaches AS T2 ON T1.course_id  =  T2.course_id JOIN instructor AS T3 ON T2.id  =  T3.id WHERE YEAR  =  2008 ORDER BY T1.title

Question: Find the name of instructors who are advising more than one student.
SQL: SELECT T1.name FROM instructor AS T1 JOIN advisor AS T2 ON T1.id  =  T2.i_id GROUP BY T2.i_id HAVING count(*)  >  1

Question: What are the names of instructors who advise more than one student?
SQL: SELECT T1.name FROM instructor AS T1 JOIN advisor AS T2 ON T1.id  =  T2.i_id GROUP BY T2.i_id HAVING count(*)  >  1

Question: Find the name of the students who have more than one advisor?
SQL: SELECT T1.name FROM student AS T1 JOIN advisor AS T2 ON T1.id  =  T2.s_id GROUP BY T2.s_id HAVING count(*)  >  1

Question: What are the names of students who have more than one advisor?
SQL: SELECT T1.name FROM student AS T1 JOIN advisor AS T2 ON T1.id  =  T2.s_id GROUP BY T2.s_id HAVING count(*)  >  1

Question: Find the number of rooms with more than 50 capacity for each building.
SQL: SELECT count(*) ,  building FROM classroom WHERE capacity  >  50 GROUP BY building

Question: How many rooms in each building have a capacity of over 50?
SQL: SELECT count(*) ,  building FROM classroom WHERE capacity  >  50 GROUP BY building

Question: Find the maximum and average capacity among rooms in each building.
SQL: SELECT max(capacity) ,  avg(capacity) ,  building FROM classroom GROUP BY building

Question: What are the greatest and average capacity for rooms in each building?
SQL: SELECT max(capacity) ,  avg(capacity) ,  building FROM classroom GROUP BY building

Question: Find the title of the course that is offered by more than one department.
SQL: SELECT title FROM course GROUP BY title HAVING count(*)  >  1

Question: What are the titles of courses that are offered in more than one department?
SQL: SELECT title FROM course GROUP BY title HAVING count(*)  >  1

Question: Find the total credits of courses provided by different department.
SQL: SELECT sum(credits) ,  dept_name FROM course GROUP BY dept_name

Question: How many total credits are offered by each department?
SQL: SELECT sum(credits) ,  dept_name FROM course GROUP BY dept_name

Question: Find the minimum salary for the departments whose average salary is above the average payment of all instructors.
SQL: SELECT min(salary) ,  dept_name FROM instructor GROUP BY dept_name HAVING avg(salary)  >  (SELECT avg(salary) FROM instructor)

Question: What is the lowest salary in departments with average salary greater than the overall average.
SQL: SELECT min(salary) ,  dept_name FROM instructor GROUP BY dept_name HAVING avg(salary)  >  (SELECT avg(salary) FROM instructor)

Question: Find the number of courses provided in each semester and year.
SQL: SELECT count(*) ,  semester ,  YEAR FROM SECTION GROUP BY semester ,  YEAR

Question: How many courses are provided in each semester and year?
SQL: SELECT count(*) ,  semester ,  YEAR FROM SECTION GROUP BY semester ,  YEAR

Question: Find the year which offers the largest number of courses.
SQL: SELECT YEAR FROM SECTION GROUP BY YEAR ORDER BY count(*) DESC LIMIT 1

Question: Which year had the greatest number of courses?
SQL: SELECT YEAR FROM SECTION GROUP BY YEAR ORDER BY count(*) DESC LIMIT 1

Question: Find the year and semester when offers the largest number of courses.
SQL: SELECT semester ,  YEAR FROM SECTION GROUP BY semester ,  YEAR ORDER BY count(*) DESC LIMIT 1

Question: What is the year and semester with the most courses?
SQL: SELECT semester ,  YEAR FROM SECTION GROUP BY semester ,  YEAR ORDER BY count(*) DESC LIMIT 1

Question: Find the name of department has the highest amount of students?
SQL: SELECT dept_name FROM student GROUP BY dept_name ORDER BY count(*) DESC LIMIT 1

Question: What is the name of the deparment with the highest enrollment?
SQL: SELECT dept_name FROM student GROUP BY dept_name ORDER BY count(*) DESC LIMIT 1

Question: Find the total number of students in each department.
SQL: SELECT count(*) ,  dept_name FROM student GROUP BY dept_name

Question: How many students are in each department?
SQL: SELECT count(*) ,  dept_name FROM student GROUP BY dept_name

Question: Find the semester and year which has the least number of student taking any class.
SQL: SELECT semester ,  YEAR FROM takes GROUP BY semester ,  YEAR ORDER BY count(*) LIMIT 1

Question: Which semeseter and year had the fewest students?
SQL: SELECT semester ,  YEAR FROM takes GROUP BY semester ,  YEAR ORDER BY count(*) LIMIT 1

Question: What is the id of the instructor who advises of all students from History department?
SQL: SELECT i_id FROM advisor AS T1 JOIN student AS T2 ON T1.s_id  =  T2.id WHERE T2.dept_name  =  'History'

Question: Give id of the instructor who advises students in the History department.
SQL: SELECT i_id FROM advisor AS T1 JOIN student AS T2 ON T1.s_id  =  T2.id WHERE T2.dept_name  =  'History'