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 students who took any class in the years of 2009 and 2010.
assistant
SELECT DISTINCT T1.name FROM student AS T1 JOIN takes AS T2 ON T1.id = T2.id WHERE YEAR = 2009 OR YEAR = 2010
human
Find the names of instructors who didn't each any courses in any Spring semester.
assistant
SELECT name FROM instructor WHERE id NOT IN (SELECT id FROM teaches WHERE semester = 'Spring')
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 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
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 but not in Spring 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 ;