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 the names of all distinct instructors.
assistant
SELECT DISTINCT name FROM instructor ORDER BY name
human
Find the name of instructors who are advising 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
human
Who is the instructor with the highest salary?
assistant
SELECT name FROM instructor ORDER BY salary DESC LIMIT 1
human
Find the names of all instructors whose salary is greater than the salary of all instructors in the Biology department.
assistant
SELECT name FROM instructor WHERE salary > (SELECT max(salary) FROM instructor WHERE dept_name = 'Biology')
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
What are the names of instructors who earn more than at least one instructor from the Biology department?
assistant
SELECT name FROM instructor WHERE salary > ( SELECT MIN( salary ) FROM instructor WHERE dept_name = "Biology" ) ;