--set echo on
-- (a) Get the names of students enrolled in the Automata class in
--     the f96 term.
select fname, lname
from   students, catalog, courses, enrolls
where  students.sid = enrolls.sid and
       catalog.cno = courses.cno and
       courses.term = enrolls.term and
       courses.lineno = enrolls.lineno and
       courses.term = 'f96' and
       catalog.ctitle = 'Automata'; 
-- (b) Get the course numbers and titles of courses in which Timothy
--     Thomas has enrolled.
select catalog.cno, ctitle
from   catalog, courses, enrolls, students
where  catalog.cno = courses.cno and
       courses.term = enrolls.term and
       courses.lineno = enrolls.lineno and
       students.sid = enrolls.sid and 
       fname = 'Timothy' and lname = 'Thomas'; 
-- (c) Get the sid values of students who did not enroll in any class
--     during the f96 term.
select sid
from   students
where  sid not in
          (select sid
           from   enrolls
           where  term = 'f96');
-- (d) Get the sid values of students who have enrolled in csc226 and 
--     csc227.
select sid
from   students
where  sid in (select sid
               from   enrolls, courses
               where  enrolls.term = courses.term and
                      enrolls.lineno = courses.lineno and
                      cno = 'csc226') and
       sid in (select sid
               from   enrolls, courses
               where  enrolls.term = courses.term and
                      enrolls.lineno = courses.lineno and
                      cno = 'csc227');
-- (e) Get the sid values of students who have enrolled in csc226 or 
--     csc227.
select sid
from   students
where  sid in (select sid
               from   enrolls, courses
               where  enrolls.term = courses.term and
                      enrolls.lineno = courses.lineno and
                      cno = 'csc226') or
       sid in (select sid
               from   enrolls, courses
               where  enrolls.term = courses.term and
                      enrolls.lineno = courses.lineno and
                      cno = 'csc227');
-- (f) Get sid values of students who have enrolled in all courses
--     in the catalog.
select sid
from   students
where  not exists (
          select cno
          from   catalog
          where  not exists (
              select '7'
              from   courses, enrolls
              where  courses.term = enrolls.term and
                     courses.lineno = enrolls.lineno and
                     courses.cno = catalog.cno and
                     enrolls.sid = students.sid));
-- (g) Get the names of students who have enrolled in the highest
--     number of courses.
select fname, lname, count(*) NUM_COURSES
from   enrolls, students
where  enrolls.sid = students.sid
group by students.sid, fname, lname
having count(*) >=all
  (select count(*)
   from   enrolls
   group by sid);
-- (h) Get the names of students who have enrolled in the lowest
--     number of courses (the student must have enrolled in at least one
--     course).
select fname, lname, count(*) NUM_COURSES
from   enrolls, students
where  enrolls.sid = students.sid
group by students.sid, fname, lname
having count(*) <=all
  (select count(*)
   from   enrolls
   group by sid);
-- (i) Get the names of students who have not enrolled in any course.
select fname, lname
from   students
where sid not in (
        select sid
        from   enrolls);
-- (j) Get the titles of courses that have had enrollments of 5 or fewer
--     students.
select distinct ctitle
from   catalog, courses, enrolls
where  catalog.cno = courses.cno and
       courses.term = enrolls.term and
       courses.lineno = enrolls.lineno
group by courses.term, courses.lineno, ctitle
having count(*) <= 5;
-- (k) Get the terms, line numbers, course numbers, and course titles of
--     courses along with their total enrollments.
select courses.cno, ctitle, courses.term, courses.lineno, count(*) ENROLLMENT
from   catalog, courses, enrolls
where  catalog.cno = courses.cno and
       courses.term = enrolls.term and
       courses.lineno = enrolls.lineno
group by courses.cno, courses.term, courses.lineno, ctitle;
-- (l) Get the terms, line numbers, and course titles of courses with
--     the highest enrollments.
select ctitle, courses.term, courses.lineno
from   catalog, courses, enrolls
where  catalog.cno = courses.cno and
       courses.term = enrolls.term and
       courses.lineno = enrolls.lineno
group by courses.term, courses.lineno, ctitle
having count(*) >=all (select count(*)
                       from   enrolls
                       group by term, lineno);
-- (m) Get the terms, line numbers, and course titles of courses that have
--     enrollments greater than or equal to the average enrollments of
--     all courses.
select ctitle, courses.term, courses.lineno
from   catalog, courses, enrolls
where  catalog.cno = courses.cno and
       courses.term = enrolls.term and
       courses.lineno = enrolls.lineno
group by courses.term, courses.lineno, ctitle
having count(*) >=all (select avg(count(*))
                       from   enrolls
                       group by term, lineno);
-- (n) Get the student ids of students, terms and line numbers of courses
--     they have enrolled in, the component names of the courses, the
--     student scores in the components of the courses, and the weighted
--     average of the component scores.
select enrolls.sid, enrolls.term, enrolls.lineno, components.compname, 
       scores.points, components.maxpoints, components.weight,
       (scores.points/components.maxpoints)*components.weight W_AVG
from   enrolls, components, scores
where  enrolls.term = components.term and
       enrolls.lineno = components.lineno and
       scores.sid = enrolls.sid and
       scores.compname = components.compname and
       scores.term = components.term and
       scores.lineno = components.lineno;
-- (o) Given a term and the line number of a course (for example, w98
--     and 1585), get the student ids, last names, and first names of
--     students enrolled in the class along with each student's course
--     average rounded off to the nearest integer. The course average
--     is the sum of the weighted averages of the individual component
--     scores.
select students.sid, students.lname, students.fname, 
       ceil(sum((scores.points/components.maxpoints)*components.weight)) 
       AVG
from   students, enrolls, components, scores
where  students.sid = enrolls.sid and
       enrolls.term = components.term and
       enrolls.lineno = components.lineno and
       scores.sid = enrolls.sid and
       scores.compname = components.compname and
       scores.term = components.term and
       scores.lineno = components.lineno and
       enrolls.term = &Term and enrolls.lineno = &LineNumber
group by students.sid, students.lname, students.fname
order by students.lname, students.fname;
--set echo off


set echo on
-- 2.7 (a) Update all the null-valued scores to zeros.
-- Before Update
select * from scores where points is null;
--
update scores set points = 0 where points is null;
-- After Update
select * from scores where points is null;
select * from scores where points = 0;
--
set echo off


set echo on
-- 2.7 (b) Delete the component QUIZ2 from the components table.
-- Before Update
select * from components where compname='QUIZ2';
select * from scores where compname='QUIZ2';
--
delete from scores where compname = 'QUIZ2';
delete from components where compname = 'QUIZ2';
-- After Update
select * from components where compname='QUIZ2';
select * from scores where compname='QUIZ2';
set echo off


set echo on
-- 2.7 (c) Drop the student with sid = 1234 from the f97 course with
--         lineno = 1111.
-- Before Update
select * from enrolls where sid = '1234' and term = 'f97' and lineno=1111;
select * from scores where sid = '1234' and term = 'f97' and lineno=1111;
--
delete from scores where sid = '1234' and term = 'f97' and lineno=1111;
delete from enrolls where sid = '1234' and term = 'f97' and lineno=1111;
-- After Update
select * from enrolls where sid = '1234' and term = 'f97' and lineno=1111;
select * from scores where sid = '1234' and term = 'f97' and lineno=1111;
set echo off


set echo on
-- 2.7 (d) Enroll all students in the f97 course with lineno = 1111 into
--         the f97 course with lineno = 1112;
-- Before Update
select * from enrolls where term = 'f97' and lineno=1111;
select * from scores where  term = 'f97' and lineno=1111;
select * from enrolls where term = 'f97' and lineno=1112;
select * from scores where  term = 'f97' and lineno=1112;
--
insert into enrolls select sid,'f97',1112 from enrolls 
where term = 'f97' and lineno = 1111;
insert into scores select sid,'f97',1112,compname,points
from scores where term = 'f97' and lineno = '1111';
delete from scores where term = 'f97' and lineno=1111;
delete from enrolls where term = 'f97' and lineno=1111;
-- After Update
select * from enrolls where term = 'f97' and lineno=1111;
select * from scores where  term = 'f97' and lineno=1111;
select * from enrolls where term = 'f97' and lineno=1112;
select * from scores where  term = 'f97' and lineno=1112;
set echo off


set echo on
-- 2.7 (e) Give all students in the f97 course with lineno = 1111
--         10 extra points in the EXAM1 component.
-- Before Update
select * from scores where (term,lineno) in
(select term,lineno from enrolls group by term,lineno having count(*) < 2); 
select * from components where (term,lineno) in
(select term,lineno from enrolls group by term,lineno having count(*) < 2); 
select * from enrolls where (term,lineno) in
(select term,lineno from enrolls group by term,lineno having count(*) < 2); 
select * from courses where (term,lineno) in
(select term,lineno from enrolls group by term,lineno having count(*) < 2); 
-- Update
delete from courses where (term,lineno) in
(select term,lineno from enrolls group by term,lineno having count(*) < 2);
-- After Update
select * from scores where (term,lineno) in
(select term,lineno from enrolls group by term,lineno having count(*) < 2); 
select * from components where (term,lineno) in
(select term,lineno from enrolls group by term,lineno having count(*) < 2); 
select * from enrolls where (term,lineno) in
(select term,lineno from enrolls group by term,lineno having count(*) < 2); 
select * from courses where (term,lineno) in
(select term,lineno from enrolls group by term,lineno having count(*) < 2); 
set echo off