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 number of courses provided in each semester and year.
assistant
SELECT count(*) , semester , YEAR FROM SECTION GROUP BY semester , YEAR
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 students who have taken any course in the fall semester of year 2003.
assistant
SELECT name FROM student WHERE id IN (SELECT id FROM takes WHERE semester = 'Fall' AND YEAR = 2003)
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
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 ;