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: How many rooms whose capacity is less than 50 does the Lamberton building have?
SQL: SELECT count(*) FROM classroom WHERE building  =  'Lamberton' AND capacity  <  50

Question: Count the number of rooms in Lamberton with capacity lower than 50.
SQL: SELECT count(*) FROM classroom WHERE building  =  'Lamberton' AND capacity  <  50

Question: Find the name and budget of departments whose budgets are more than the average budget.
SQL: SELECT dept_name ,  budget FROM department WHERE budget  >  (SELECT avg(budget) FROM department)

Question: What are the names and budgets of departments with budgets greater than the average?
SQL: SELECT dept_name ,  budget FROM department WHERE budget  >  (SELECT avg(budget) FROM department)

Question: what is the name of the instructor who is in Statistics department and earns the lowest salary?
SQL: SELECT name FROM instructor WHERE dept_name  =  'Statistics' ORDER BY salary LIMIT 1

Question: Give the name of the lowest earning instructor in the Statistics department.
SQL: SELECT name FROM instructor WHERE dept_name  =  'Statistics' ORDER BY salary LIMIT 1

Question: Find the title of course that is provided by both Statistics and Psychology departments.
SQL: SELECT title FROM course WHERE dept_name  =  'Statistics' INTERSECT SELECT title FROM course WHERE dept_name  =  'Psychology'

Question: What is the title of a course that is listed in both the Statistics and Psychology departments?
SQL: SELECT title FROM course WHERE dept_name  =  'Statistics' INTERSECT SELECT title FROM course WHERE dept_name  =  'Psychology'

Question: Find the title of course that is provided by Statistics but not Psychology departments.
SQL: SELECT title FROM course WHERE dept_name  =  'Statistics' EXCEPT SELECT title FROM course WHERE dept_name  =  'Psychology'

Question: What are the titles of courses that are in the Statistics department but not the Psychology department?
SQL: SELECT title FROM course WHERE dept_name  =  'Statistics' EXCEPT SELECT title FROM course WHERE dept_name  =  'Psychology'

Question: Find the id of instructors who taught a class in Fall 2009 but not in Spring 2010.
SQL: SELECT id FROM teaches WHERE semester  =  'Fall' AND YEAR  =  2009 EXCEPT SELECT id FROM teaches WHERE semester  =  'Spring' AND YEAR  =  2010

Question: What are the ids of instructors who taught in the Fall of 2009 but not in the Spring of 2010?
SQL: SELECT id FROM teaches WHERE semester  =  'Fall' AND YEAR  =  2009 EXCEPT SELECT id FROM teaches WHERE semester  =  'Spring' AND YEAR  =  2010

Question: Find the name of students who took any class in the years of 2009 and 2010.
SQL: SELECT DISTINCT T1.name FROM student AS T1 JOIN takes AS T2 ON T1.id  =  T2.id WHERE YEAR  =  2009 OR YEAR  =  2010

Question: What are the names of the students who took classes in 2009 or 2010?
SQL: SELECT DISTINCT T1.name FROM student AS T1 JOIN takes AS T2 ON T1.id  =  T2.id WHERE YEAR  =  2009 OR YEAR  =  2010

Question: Find the names of the top 3 departments that provide the largest amount of courses?
SQL: SELECT dept_name FROM course GROUP BY dept_name ORDER BY count(*) DESC LIMIT 3

Question: What are the names of the 3 departments with the most courses?
SQL: SELECT dept_name FROM course GROUP BY dept_name ORDER BY count(*) DESC LIMIT 3

Question: Find the name of the department that offers the highest total credits?
SQL: SELECT dept_name FROM course GROUP BY dept_name ORDER BY sum(credits) DESC LIMIT 1

Question: What is the name of the department with the most credits?
SQL: SELECT dept_name FROM course GROUP BY dept_name ORDER BY sum(credits) DESC LIMIT 1

Question: List the names of all courses ordered by their titles and credits.
SQL: SELECT title FROM course ORDER BY title ,  credits

Question: Given the titles of all courses, in order of titles and credits.
SQL: SELECT title FROM course ORDER BY title ,  credits

Question: Which department has the lowest budget?
SQL: SELECT dept_name FROM department ORDER BY budget LIMIT 1

Question: Give the name of the department with the lowest budget.
SQL: SELECT dept_name FROM department ORDER BY budget LIMIT 1

Question: List the names and buildings of all departments sorted by the budget from large to small.
SQL: SELECT dept_name ,  building FROM department ORDER BY budget DESC

Question: What are the names and buildings of the deparments, sorted by budget descending?
SQL: SELECT dept_name ,  building FROM department ORDER BY budget DESC

Question: Who is the instructor with the highest salary?
SQL: SELECT name FROM instructor ORDER BY salary DESC LIMIT 1

Question: Give the name of the highest paid instructor.
SQL: SELECT name FROM instructor ORDER BY salary DESC LIMIT 1

Question: List the information of all instructors ordered by their salary in ascending order.
SQL: SELECT * FROM instructor ORDER BY salary

Question: Give all information regarding instructors, in order of salary from least to greatest.
SQL: SELECT * FROM instructor ORDER BY salary

Question: Find the name of the students and their department names sorted by their total credits in ascending order.
SQL: SELECT name ,  dept_name FROM student ORDER BY tot_cred

Question: What are the names of students and their respective departments, ordered by number of credits from least to greatest?
SQL: SELECT name ,  dept_name FROM student ORDER BY tot_cred