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 minimum salary for the departments whose average salary is above the average payment of all instructors.
assistant
SELECT min(salary) , dept_name FROM instructor GROUP BY dept_name HAVING avg(salary) > (SELECT avg(salary) FROM instructor)
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 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 name of students who took some course offered by Statistics department.
assistant
SELECT T3.name FROM course AS T1 JOIN takes AS T2 ON T1.course_id = T2.course_id JOIN student AS T3 ON T2.id = T3.id WHERE T1.dept_name = 'Statistics'
human
Who is the instructor with the highest salary?
assistant
SELECT name FROM instructor ORDER BY salary DESC LIMIT 1
human
Give the name of the lowest earning instructor in the Statistics department.
assistant
SELECT name FROM instructor WHERE dept_name = "Statistics" ORDER BY salary LIMIT 1 ;