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 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
human
Find names of instructors with salary greater than that of some (at least one) instructor in the Biology department.
assistant
SELECT name FROM instructor WHERE salary > (SELECT min(salary) FROM instructor WHERE dept_name = 'Biology')
human
List the information of all instructors ordered by their salary in ascending order.
assistant
SELECT * FROM instructor ORDER BY salary
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 names of all instructors whose salary is greater than the salary of all instructors in the Biology department.
assistant
SELECT name FROM instructor WHERE salary > (SELECT max(salary) FROM instructor WHERE dept_name = 'Biology')
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 ) ;