3.11

Write the following queries in SQL, using the university schema.

1. Find the ID and name of each student who has taken at least one Comp. Sci. course; make sure there are no duplicates names in the result.
   
2. Find the ID and name of each student who has not taken any course offered before 2017.
   
3. For each department, find the maximum salary of instructors in that department. You may assume that every department has at least one instructor.
   
4. Find the lowest, across all departments, of the per-department maximum salary computed by the preceding query.

---

1. Find the ID and name of each student who has taken at least one Comp. Sci. course; make sure there are no duplicates names in the result.

SELECT DISTINCT student.ID, student.name
FROM student INNER JOIN takes  ON student.ID = takes.ID 
             INNER JOIN course ON takes.course_id = course.course_id
WHERE course.dept_name = 'Comp. Sci.';

2. Find the ID and name of each student who has not taken any course offered before 2017.

SELECT ID, name 
FROM student AS S
WHERE NOT EXISTS (
    SELECT * 
    FROM takes AS T
    WHERE year < 2017 AND T.ID = S.ID 
)

3. For each department, find the maximum salary of instructors in that department. You may assume that every department has at least one instructor.

SELECT dept_name, MAX(salary)
FROM instructor
GROUP BY dept_name 

4. Find the lowest, across all departments, of the per-department maximum salary computed by the preceding query.

WITH maximum_salary_within_dept(dept_name, max_salary) AS (
    SELECT dept_name, MAX(salary)
    FROM instructor
    GROUP BY dept_name 
) 
SELECT MIN(max_salary) 
FROM maximum_salary_within_dept