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 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 number of courses provided in each semester and year.
assistant
SELECT count(*) , semester , YEAR FROM SECTION GROUP BY semester , YEAR
human
Find the names of all instructors who have taught some course and the course_id.
assistant
SELECT name , course_id FROM instructor AS T1 JOIN teaches AS T2 ON T1.ID = T2.ID
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 total number of students and total number of instructors for each department.
assistant
SELECT count(DISTINCT T2.id) , count(DISTINCT T3.id) , T3.dept_name FROM department AS T1 JOIN student AS T2 ON T1.dept_name = T2.dept_name JOIN instructor AS T3 ON T1.dept_name = T3.dept_name GROUP BY T3.dept_name
human
Find the total number of instructors who teach a course in the Spring 2010 semester.
assistant
SELECT COUNT( DISTINCT ID ) FROM teaches WHERE semester = "Spring" AND YEAR = 2010 ;