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 name of the courses that do not have any prerequisite?
assistant
SELECT title FROM course WHERE course_id NOT IN (SELECT course_id FROM prereq)
human
Find the id of instructors who taught a class in Fall 2009 but not in Spring 2010.
assistant
SELECT id FROM teaches WHERE semester = 'Fall' AND YEAR = 2009 EXCEPT SELECT id FROM teaches WHERE semester = 'Spring' AND YEAR = 2010
human
Find the id of the courses that do not have any prerequisite?
assistant
SELECT course_id FROM course EXCEPT SELECT course_id FROM prereq
human
Find courses that ran in Fall 2009 and in Spring 2010.
assistant
SELECT course_id FROM SECTION WHERE semester = 'Fall' AND YEAR = 2009 INTERSECT SELECT course_id FROM SECTION WHERE semester = 'Spring' AND YEAR = 2010
human
Find courses that ran in Fall 2009 or in Spring 2010.
assistant
SELECT course_id FROM SECTION WHERE semester = 'Fall' AND YEAR = 2009 UNION SELECT course_id FROM SECTION WHERE semester = 'Spring' AND YEAR = 2010
human
What are the ids of courses offered in Fall of 2009 but not in Spring of 2010?
assistant
SELECT course_id FROM SECTION WHERE semester = "Fall" AND YEAR = 2009 EXCEPT SELECT course_id FROM SECTION WHERE semester = "Spring" AND YEAR = 2010 ;