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 total credits of courses provided by different department.
assistant
SELECT sum(credits) , dept_name FROM course GROUP BY dept_name
human
Find the name of department has the highest amount of students?
assistant
SELECT dept_name FROM student GROUP BY dept_name ORDER BY count(*) DESC LIMIT 1
human
Find the total number of students in each department.
assistant
SELECT count(*) , dept_name FROM student GROUP BY dept_name
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 of the students and their department names sorted by their total credits in ascending order.
assistant
SELECT name , dept_name FROM student ORDER BY tot_cred ;