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 names of all instructors in the Art department 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 WHERE T1.dept_name = 'Art'
human
How many different instructors have taught some course?
assistant
SELECT COUNT (DISTINCT id) FROM teaches
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 id of instructors who didn't teach any courses?
assistant
SELECT id FROM instructor EXCEPT SELECT id FROM teaches
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 ) ;