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 title of course that is provided by both Statistics and Psychology departments.
assistant
SELECT title FROM course WHERE dept_name = 'Statistics' INTERSECT SELECT title FROM course WHERE dept_name = 'Psychology'
human
what is the name of the instructor who is in Statistics department and earns the lowest salary?
assistant
SELECT name FROM instructor WHERE dept_name = 'Statistics' ORDER BY salary LIMIT 1
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 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
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 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" ;