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
What is the id of the instructor who advises of all students from History department?
assistant
SELECT i_id FROM advisor AS T1 JOIN student AS T2 ON T1.s_id = T2.id WHERE T2.dept_name = 'History'
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
What is the name of the student who has the highest total credits in the History department.
assistant
SELECT name FROM student WHERE dept_name = 'History' ORDER BY tot_cred DESC LIMIT 1
human
Who is the instructor with the highest salary?
assistant
SELECT name FROM instructor ORDER BY salary DESC LIMIT 1
human
What is the title and credits of the course that is taught in the largest classroom (with the highest capacity)?
assistant
SELECT T3.title , T3.credits FROM classroom AS T1 JOIN SECTION AS T2 ON T1.building = T2.building AND T1.room_number = T2.room_number JOIN course AS T3 ON T2.course_id = T3.course_id WHERE T1.capacity = (SELECT max(capacity) FROM classroom)
human
What is the name of the instructor who advises the student with the greatest 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 ;