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 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
List in alphabetic order the names of all distinct instructors.
assistant
SELECT DISTINCT name FROM instructor ORDER BY name
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
Find the name of the students who have more than one advisor?
assistant
SELECT T1.name FROM student AS T1 JOIN advisor AS T2 ON T1.id = T2.s_id GROUP BY T2.s_id HAVING count(*) > 1
human
How many different instructors have taught some course?
assistant
SELECT COUNT (DISTINCT id) FROM teaches
human
What are the names of instructors who advise more than one student?
assistant
SELECT T1.name FROM instructor AS T1 JOIN advisor AS T2 ON T1.id = T2.i_id GROUP BY T2.i_id HAVING COUNT( * ) > 1 ;