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 the department that offers the highest total credits?
assistant
SELECT dept_name FROM course GROUP BY dept_name ORDER BY sum(credits) DESC LIMIT 1
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 title, credit, and department name of courses that have more than one prerequisites?
assistant
SELECT T1.title , T1.credits , T1.dept_name FROM course AS T1 JOIN prereq AS T2 ON T1.course_id = T2.course_id GROUP BY T2.course_id HAVING count(*) > 1
human
Find the names of the top 3 departments that provide the largest amount of courses?
assistant
SELECT dept_name FROM course GROUP BY dept_name ORDER BY count(*) DESC LIMIT 3
human
List the names of all courses ordered by their titles and credits.
assistant
SELECT title FROM course ORDER BY title , credits
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 ) ;