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
List the information of all instructors ordered by their salary in ascending order.
assistant
SELECT * FROM instructor ORDER BY salary
human
Find the name and salary of instructors whose salary is below the average salary of the instructors in the Physics department.
assistant
SELECT name , salary FROM instructor WHERE salary < (SELECT avg(salary) FROM instructor WHERE dept_name = 'Physics')
human
Find the names of all instructors in Comp. Sci. department with salary > 80000.
assistant
SELECT name FROM instructor WHERE dept_name = 'Comp. Sci.' AND salary > 80000
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 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 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" ) ;