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 didn't take any course from Biology department.
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')
human
list in alphabetic order all course names and their instructors' names in year 2008.
assistant
SELECT T1.title , T3.name FROM course AS T1 JOIN teaches AS T2 ON T1.course_id = T2.course_id JOIN instructor AS T3 ON T2.id = T3.id WHERE YEAR = 2008 ORDER BY T1.title
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 students who took some course offered by Statistics department.
assistant
SELECT T3.name FROM course AS T1 JOIN takes AS T2 ON T1.course_id = T2.course_id JOIN student AS T3 ON T2.id = T3.id WHERE T1.dept_name = 'Statistics'
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
What are the names of students who took a course in the Fall of 2003?
assistant
SELECT name FROM student WHERE id IN ( SELECT id FROM takes WHERE semester = "Fall" AND YEAR = 2003 ) ;