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 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 title of the course that is offered by more than one department.
assistant
SELECT title FROM course GROUP BY title HAVING count(*) > 1
human
Find the name and building of the department with the highest budget.
assistant
SELECT dept_name , building FROM department ORDER BY budget DESC LIMIT 1
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 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
What is the name of the department with an instructure who has a name like 'Soisalon'?
assistant
SELECT dept_name FROM instructor WHERE name LIKE "%Soisalon%" ;