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 title, credit, and department name of courses that have more than one prerequisites?
assistant
SELECT T1.title , T1.credits , T1.dept_name FROM course AS T1 JOIN prereq AS T2 ON T1.course_id = T2.course_id GROUP BY T2.course_id HAVING count(*) > 1
human
How many courses that do not have prerequisite?
assistant
SELECT count(*) FROM course WHERE course_id NOT IN (SELECT course_id FROM prereq)
human
Find the title of courses that have two prerequisites?
assistant
SELECT T1.title FROM course AS T1 JOIN prereq AS T2 ON T1.course_id = T2.course_id GROUP BY T2.course_id HAVING count(*) = 2
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 id of the courses that do not have any prerequisite?
assistant
SELECT course_id FROM course EXCEPT SELECT course_id FROM prereq ;