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 buildings which have rooms with capacity more than 50.
SQL: SELECT DISTINCT building FROM classroom WHERE capacity  >  50

Question: What are the distinct buildings with capacities of greater than 50?
SQL: SELECT DISTINCT building FROM classroom WHERE capacity  >  50

Question: Count the number of rooms that are not in the Lamberton building.
SQL: SELECT count(*) FROM classroom WHERE building != 'Lamberton'

Question: How many classrooms are not in Lamberton?
SQL: SELECT count(*) FROM classroom WHERE building != 'Lamberton'

Question: What is the name and building of the departments whose budget is more than the average budget?
SQL: SELECT dept_name ,  building FROM department WHERE budget  >  (SELECT avg(budget) FROM department)

Question: Give the name and building of the departments with greater than average budget.
SQL: SELECT dept_name ,  building FROM department WHERE budget  >  (SELECT avg(budget) FROM department)

Question: Find the room number of the rooms which can sit 50 to 100 students and their buildings.
SQL: SELECT building ,  room_number FROM classroom WHERE capacity BETWEEN 50 AND 100

Question: What are the room numbers and corresponding buildings for classrooms which can seat between 50 to 100 students?
SQL: SELECT building ,  room_number FROM classroom WHERE capacity BETWEEN 50 AND 100

Question: Find the name and building of the department with the highest budget.
SQL: SELECT dept_name ,  building FROM department ORDER BY budget DESC LIMIT 1

Question: What is the department name and corresponding building for the department with the greatest budget?
SQL: SELECT dept_name ,  building FROM department ORDER BY budget DESC LIMIT 1

Question: What is the name of the student who has the highest total credits in the History department.
SQL: SELECT name FROM student WHERE dept_name  =  'History' ORDER BY tot_cred DESC LIMIT 1

Question: Give the name of the student in the History department with the most credits.
SQL: SELECT name FROM student WHERE dept_name  =  'History' ORDER BY tot_cred DESC LIMIT 1

Question: How many rooms does the Lamberton building have?
SQL: SELECT count(*) FROM classroom WHERE building  =  'Lamberton'

Question: Count the number of classrooms in Lamberton.
SQL: SELECT count(*) FROM classroom WHERE building  =  'Lamberton'

Question: How many students have advisors?
SQL: SELECT count(DISTINCT s_id) FROM advisor

Question: Count the number of students who have advisors.
SQL: SELECT count(DISTINCT s_id) FROM advisor

Question: How many departments offer courses?
SQL: SELECT count(DISTINCT dept_name) FROM course

Question: Count the number of departments which offer courses.
SQL: SELECT count(DISTINCT dept_name) FROM course

Question: How many different courses offered by Physics department?
SQL: SELECT count(DISTINCT course_id) FROM course WHERE dept_name  =  'Physics'

Question: Count the number of courses in the Physics department.
SQL: SELECT count(DISTINCT course_id) FROM course WHERE dept_name  =  'Physics'

Question: Find the title of courses that have two prerequisites?
SQL: SELECT T1.title FROM course AS T1 JOIN prereq AS T2 ON T1.course_id  =  T2.course_id GROUP BY T2.course_id HAVING count(*)  =  2

Question: What are the titles for courses with two prerequisites?
SQL: SELECT T1.title FROM course AS T1 JOIN prereq AS T2 ON T1.course_id  =  T2.course_id GROUP BY T2.course_id HAVING count(*)  =  2

Question: Find the title, credit, and department name of courses that have more than one prerequisites?
SQL: SELECT T1.title ,  T1.credits , T1.dept_name FROM course AS T1 JOIN prereq AS T2 ON T1.course_id  =  T2.course_id GROUP BY T2.course_id HAVING count(*)  >  1

Question: What is the title, credit value, and department name for courses with more than one prerequisite?
SQL: SELECT T1.title ,  T1.credits , T1.dept_name FROM course AS T1 JOIN prereq AS T2 ON T1.course_id  =  T2.course_id GROUP BY T2.course_id HAVING count(*)  >  1

Question: How many courses that do not have prerequisite?
SQL: SELECT count(*) FROM course WHERE course_id NOT IN (SELECT course_id FROM prereq)

Question: Count the number of courses without prerequisites.
SQL: SELECT count(*) FROM course WHERE course_id NOT IN (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 titles of courses without prerequisites?
SQL: SELECT title FROM course WHERE course_id NOT IN (SELECT course_id FROM prereq)

Question: How many different instructors have taught some course?
SQL: SELECT COUNT (DISTINCT id) FROM teaches

Question: Count the number of distinct instructors who have taught a course.
SQL: SELECT COUNT (DISTINCT id) FROM teaches

Question: Find the total budgets of the Marketing or Finance department.
SQL: SELECT sum(budget) FROM department WHERE dept_name  =  'Marketing' OR dept_name  =  'Finance'

Question: What is the sum of budgets of the Marketing and Finance departments?
SQL: SELECT sum(budget) FROM department WHERE dept_name  =  'Marketing' OR dept_name  =  'Finance'

Question: Find the department name of the instructor whose name contains 'Soisalon'.
SQL: SELECT dept_name FROM instructor WHERE name LIKE '%Soisalon%'

Question: What is the name of the department with an instructure who has a name like 'Soisalon'?
SQL: SELECT dept_name FROM instructor WHERE name LIKE '%Soisalon%'