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
Hi, could you please let me know the name of instructors in Comp. Sci. department whose salary is higher than 80000?
assistant
SELECT name FROM instructor WHERE dept_name = 'Comp. Sci.' AND salary > 80000;
human
Oh only two of them. Thank you :) Then could you please let me know the name of the instructor whose salary is the highest in the table?
assistant
SELECT name FROM instructor ORDER by salary desc LIMIT 1;
human
That was quick, thank you again. Then could you please tell me the department name of instructor Wieland?
assistant
SELECT dept_name FROM instructor WHERE name = 'Wieland';
human
Thank you :) If it is not too bothersome, could you please let me know the average salary of the instructors in Pol. Sci.?
assistant
SELECT AVG ( salary ) FROM instructor WHERE dept_name = "Pol. Sci." ;