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 name of instructor who is the advisor of the student who has the highest number of total credits.
assistant
SELECT T2.name FROM advisor AS T1 JOIN instructor AS T2 ON T1.i_id = T2.id JOIN student AS T3 ON T1.s_id = T3.id ORDER BY T3.tot_cred DESC LIMIT 1
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
human
Find the salaries of all distinct instructors that are less than the largest salary.
assistant
SELECT DISTINCT salary FROM instructor WHERE salary < (SELECT max(salary) FROM instructor)
human
List the information of all instructors ordered by their salary in ascending order.
assistant
SELECT * FROM instructor ORDER BY salary
human
what is the name of the instructor who is in Statistics department and earns the lowest salary?
assistant
SELECT name FROM instructor WHERE dept_name = 'Statistics' ORDER BY salary LIMIT 1
human
Who is the instructor with the highest salary?
assistant
SELECT name FROM instructor ORDER BY salary DESC LIMIT 1 ;