-- SQL Query Examples --

-- 11. Find the sections taught by instructor #1 in 2004.

select * from sections where instructor_id = 1;

-- 12. List the names of the students whose names start with
--     "J" in alphabetic order.

select name from students where name like 'A%' order by name desc;

-- 13. Find the id's of the courses that have been offered before 2009.

select distinct course_id from sections where year < 2009;

-- 14. Find the students who graduated in June.

select * from students where extract( month from graduation_date ) = 6;

-- 15. Find the students who graduated in the last six months.

select * from students where graduation_date < current_date and
    graduation_date > current_date - interval '6 months';

-- 16. Find the names of the departments that offer the course "Databases".

select d.name from departments d, courses c
    where c.title = 'Databases' and c.department_id = d.id;

select d.name from departments d
    inner join courses c on d.id = c.department_id
    where c.title = 'Databases';

-- 17. Find the names of the faculty who taught the course "Databases".

select distinct f.name from faculty f, courses c, sections s
    where c.title = 'Databases' and c.id = s.course_id
    and s.instructor_id = f.id;

select distinct f.name from faculty f
    inner join sections s on s.instructor_id = f.id
    inner join courses c on s.course_id = c.id
    where c.title = 'Databases';

-- 18. Find the courses that have never been offered.

select c.* from courses c left join sections s on c.id = s.course_id
    where s.id is null;

-- 19. Find the student with the earliest graduation date.

select * from students where graduation_date =
    (select min(graduation_date) from students);

-- 20. Find the departments that offered classes in 2007.

select distinct name from departments where id in
    (select department_id from courses where id in
        (select course_id from sections where year = 2007));

-- 21. Find the faculty who taught classes in 2007.

select distinct f.name from faculty f where exists
    (select * from sections where year = 2007 and instructor_id = f.id);

-- 22. List all the names of the students and the faculty.

select name from students
union
select name from faculty;

-- 23. List the names that appear in both the students table and the faculty
--     table.

select name from students
intersect
select name from faculty;

select name from students where name in (select name from faculty);

select s.name from students s, faculty f where s.name = f.name;

-- 24. List the names that appear in the students table but not in the
--     faculty table.

select name from students
except
select name from faculty;

select name from students where name not in (select name from faculty);

select s.name from students s left join faculty f on s.name = f.name
    where f.id is null;

-- 25. Find the earliest graduation date.

select min(graduation_date) from students;

-- 26. Find the number of courses offered by the Computer Science Department.

select count(*) from departments d, courses c
    where d.name = 'Computer Science' and d.id = c.department_id;

-- 27. List the number of students in each section.

select section_id, count(student_id) from enrollment group by section_id;

-- 28. List the number courses offered by department.

select d.name as "Department Name", count(*) as "# of Courses Offered"
    from departments d, courses c
    where d.id = c.department_id
    group by d.name;

-- 29. List the number of students graduated by year.

select extract(year from graduation_date), count(*) from students
    group by extract(year from graduation_date);

select extract(year from graduation_date) as "Year", count(*) from students
    group by "Year";

-- 30. Find the years in which there were more than 2 students graduated.

select extract(year from graduation_date) as "Year", count(*) from students
    group by "Year" having count(*) > 2;

-- 31. Find the top 2 sections with the most students.

select section_id, count(student_id) from enrollment
    group by section_id
    order by count(student_id) desc
    limit 2;

-- 32. Find the names of the top 3 faculty who have taught the most number
--     of sections.

select f.name, count(s.id) from faculty f, sections s
    where f.id = s.instructor_id
    group by f.name
    having count(s.id) >= 
        (select count(s.id) from faculty f, sections s
            where f.id = s.instructor_id
            group by f.name limit 1 offset 2);

-- 33. Change the name and department_id of faculty #5 to "John" and 10,
--     respectively.

update faculty set name = 'John', department_id = 10 where id = 5;

-- 34. Delete all the enrollment records of the Elocution class in 2001

delete from enrollment where section_id =
    (select s.id from sections s, courses c
        where s.year = 2001 and s.course_id = c.id and c.title = 'Elocution');

-- 35. Change all the B+ grades in the Calculus class in 2001 to A-

update enrollment set grade_id = (select id from grades where letter = 'A-')
    where grade_id = (select id from grades where letter = 'B+')
    and section_id = (select s.id from sections s, courses c
        where s.year = 2001 and s.course_id = c.id and c.title = 'Calculus');

-- 36. Use a transaction to add two records to the faculty table; abort the
--     transaction.

begin transaction;
insert into faculty values (6, 'Tom', 30);
insert into faculty values (7, 'Jack', 30);
rollback;

-- 37. Use a transaction to add two records to the faculty table; commit the
--     transaction.

begin transaction;
insert into faculty values (6, 'Tom', 30);
insert into faculty values (7, 'Jack', 30);
commit;

-- 38. Create an index on the name column of the students table.

create index name_idx on students(name);

-- 39. Create a view showing the id, course name, instructor's name, and
--     the number of students in each section.

create view view_sections as
    select s.id, c.title, f.name, s.year, count(e.id)
        from sections s, courses c, faculty f, enrollment e
        where s.course_id = c.id and s.instructor_id = f.id
        and s.id = e.section_id
        group by s.id, c.title, f.name, s.year;

-- 40. Remove the view.

drop view view_sections;