Find the names and average salaries of all departments whose average salary is greater than 65000

SELECT dept_name, AVG(salary) AS avg_salary
FROM instructor
GROUP BY dept_name
HAVING AVG(salary) > 65000

Find the average instructors' salaries of those departments where the average salary is greater than $42000

SELECT D.dept_name, D.avg_salary
FROM (SELECT dept_name, AVG(salary) AS avg_salary
			FROM instructor
			GROUP BY dept_name) AS D
WHERE D.avg_salary > 42000;

Find all departments with the maximum budget

WITH max_budget (value) AS
		(SELECT MAX (budget)
		FROM department)
SELECT department.dept_name
FROM department, max_budget
WHERE department.budget = max_budget.value;

List all departments along with the number of instructors in each department

SELECT dept_name,
			(SELECT COUNT(*)
			 FROM instructor
			 WHERE department.dept_name = instructor.dept_name)
			AS num_instructors
FROM department;

Find courses offered in Fall 2017 and in Spring 2018 (교집합, set-intersect)

SELECT DISTINCT course_id
FROM section
WHERE semester = 'Fall' AND year = 2017 AND
			course_id IN (SELECT course_id 
										FROM section
										WHERE semester = 'Spring' AND year = 2018);

SELECT course_id
FROM section AS S
WHERE semester = 'Fall' AND year = 2017 AND
			EXISTS (SELECT * 
							FROM section AS T
							WHERE semester = 'Spring' AND year = 2018
										AND S.course_id = T.course_id);

Find courses offered in Fall 2017 but not in Spring 2018 (차집합, set-difference)

SELECT DISTINCT course_id
FROM section
WHERE semester = 'Fall' AND year = 2017 AND
			course_id NOT IN (SELECT course_id
												FROM section
												WHERE semester = 'Spring' AND year = 2018);

Name all instructors whose name is neither "Mozart" nor "Einstein"

SELECT DISTINCT
FROM instructor
WHERRE name NOT IN ('Mozart', 'Einstein');