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 didn't teach any courses?
assistant
SELECT id FROM instructor EXCEPT SELECT id FROM teaches
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 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 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 name of instructors who didn't teach any courses?
assistant
SELECT name FROM instructor WHERE id NOT IN (SELECT id FROM teaches)
human
What are the names of students who haven't taken any Biology courses?
assistant
SELECT name FROM student WHERE id NOT IN ( SELECT T1.id FROM takes AS T1 JOIN course AS T2 ON T1.course_id = T2.course_id WHERE T2.dept_name = "Biology" ) ;