EPPS 6354 Assignment 3

https://www.db-book.com/university-lab-dir/sqljs.html

Question 1:

-- either way outputs the same result for student ID
select ID from student;
select distinct ID from takes
-- getting instructors
select name from instructor
-- ways to get departments, slight order variation from table data
select dept_name from department;
select distinct dept_name from course;
select distinct dept_name from instructor;
select distinct dept_name from student

---

Question 2:

select distinct takes.ID, takes.grade, student.name 
from course,takes,student 
WHERE course.dept_name = 'Comp. Sci.' 
  and takes.course_id = course.course_id 
  and takes.ID = student.ID;
-- two ways to exclude, either returns the one student who is not in the takes relation 
select distinct student.ID, student.name 
from student,takes 
except 
  select distinct student.ID 
  from student,takes 
  where takes.year >= 2017 
    and takes.ID = student.ID;
select ID, name
from student
where ID not in (
    select ID
    from takes
    where year >= 2017);
-- max salary by dept, included name and dept_name to check my work
select max(salary), name, dept_name
from instructor
group by dept_name
-- minimum of the above
with max_salary(salary, name, dept_name) as
    (select max(salary), name, dept_name
    from instructor
    group by dept_name)
select min(salary), name, dept_name
    from max_salary

---

Question 3: Remaining Queries

select name, ID
from instructor
where ID not in (select distinct teaches.ID
  from teaches, takes
  where takes.grade = 'A'
    and takes.course_id = teaches.course_id)
select course_id, sec_id, year, semester, count(distinct ID) as num
from takes
group by sec_id, course_id