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 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
List the information of all instructors ordered by their salary in ascending order.
assistant
SELECT * FROM instructor ORDER BY salary
human
Find the name and salary of the instructors who are advisors of any student from History department?
assistant
SELECT T2.name , T2.salary FROM advisor AS T1 JOIN instructor AS T2 ON T1.i_id = T2.id JOIN student AS T3 ON T1.s_id = T3.id WHERE T3.dept_name = 'History'
human
Find the name and salary of instructors who are advisors of the students from the Math department.
assistant
SELECT T2.name , T2.salary FROM advisor AS T1 JOIN instructor AS T2 ON T1.i_id = T2.id JOIN student AS T3 ON T1.s_id = T3.id WHERE T3.dept_name = 'Math'
human
Find names of instructors with salary greater than that of some (at least one) instructor in the Biology department.
assistant
SELECT name FROM instructor WHERE salary > (SELECT min(salary) FROM instructor WHERE dept_name = 'Biology')
human
Find the names of all instructors in Comp. Sci. department with salary > 80000.
assistant
SELECT name FROM instructor WHERE dept_name = "Comp. Sci." AND salary > 80000 ;