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 the information of all instructors ordered by their salary in ascending order.
assistant
SELECT * FROM instructor ORDER BY salary
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
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 name of instructors who are advisors of the students from the Math department, and sort the results by students' total credit.
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 WHERE T3.dept_name = 'Math' ORDER BY T3.tot_cred
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" ;