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
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 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 name of instructors who didn't teach any courses?
assistant
SELECT name FROM instructor WHERE id NOT IN (SELECT id FROM teaches)
human
List in alphabetic order the names of all distinct instructors.
assistant
SELECT DISTINCT name FROM instructor ORDER BY name
human
What are the names of all instructors with names that include 'dar'?
assistant
SELECT name FROM instructor WHERE name LIKE "%dar%" ;