human
Schema:
classroom (building, room_number, capacity)
department (dept_name, building, budget)
course (course_id, title, dept_name, credits)
instructor (ID, name, dept_name, salary)
section (course_id, sec_id, semester, year, building, room_number, time_slot_id)
teaches (ID, course_id, sec_id, semester, year)
student (ID, name, dept_name, tot_cred)
takes (ID, course_id, sec_id, semester, year, grade)
advisor (s_ID, i_ID)
time_slot (time_slot_id, day, start_hr, start_min, end_hr, end_min)
prereq (course_id, prereq_id)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the title of course whose prerequisite is course Differential Geometry.
assistant
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')
human
Find the title of courses that have two prerequisites?
assistant
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
human
Find the title of the course that is offered by more than one department.
assistant
SELECT title FROM course GROUP BY title HAVING count(*) > 1
human
Find the title, credit, and department name of courses that have more than one prerequisites?
assistant
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
human
What is the title and credits of the course that is taught in the largest classroom (with the highest capacity)?
assistant
SELECT T3.title , T3.credits FROM classroom AS T1 JOIN SECTION AS T2 ON T1.building = T2.building AND T1.room_number = T2.room_number JOIN course AS T3 ON T2.course_id = T3.course_id WHERE T1.capacity = (SELECT max(capacity) FROM classroom)
human
What is the title of the course that was offered at building Chandler during the fall semester in the year of 2010?
assistant
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 ;