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 total number of students and total number of instructors for each department.
assistant
SELECT count(DISTINCT T2.id) , count(DISTINCT T3.id) , T3.dept_name FROM department AS T1 JOIN student AS T2 ON T1.dept_name = T2.dept_name JOIN instructor AS T3 ON T1.dept_name = T3.dept_name GROUP BY T3.dept_name
human
Find the salaries of all distinct instructors that are less than the largest salary.
assistant
SELECT DISTINCT salary FROM instructor WHERE salary < (SELECT max(salary) FROM instructor)
human
Find the name and budget of departments whose budgets are more than the average budget.
assistant
SELECT dept_name , budget FROM department WHERE budget > (SELECT avg(budget) FROM department)
human
Find the name of the department which has the highest average salary of professors.
assistant
SELECT dept_name FROM instructor GROUP BY dept_name ORDER BY avg(salary) DESC LIMIT 1
human
Find the minimum salary for the departments whose average salary is above the average payment of all instructors.
assistant
SELECT min(salary) , dept_name FROM instructor GROUP BY dept_name HAVING avg(salary) > (SELECT avg(salary) FROM instructor)
human
Find the number and averaged salary of all instructors who are in the department with the highest budget.
assistant
SELECT avg ( T1.salary ) , COUNT( * ) FROM instructor AS T1 JOIN department AS T2 ON T1.dept_name = T2.dept_name ORDER BY T2.budget DESC LIMIT 1 ;