2020 answer

--- section A ---
--- Q1 ---
create table Mark (
	examID varchar(3);
	studentID int,
	result int,
	occurred date,
	noOfAttempts int,
	primary key (examID, studentID),
	foreign key (examID) references Exam(id),
	foreign key (studentID) references Student(id)
);

--- Q2 ---
select name
from Student, Mark
where studentID = id
and result > 60;

--- Q3 ---
update Mark
set result = result - 2
where examID = 'C30';

--- Q4 ---
select name, sum(noOfAttempts), avg(result)
from Student, Mark
where studentID = id
group by studentID, name
having avg(result) > 60;




--- section B---
--- Q5 ---
create procedure ExamDetails(theSubject varchar) as
cursor someExams is
select if, noOfStudents
from Exam
where subject = theSubject;
anID Exam.id%type;
aNoOfStudents Exam.noOfStudents%type;
begin
	open someExams;
	loop
		fetch someExams into anID, aNoOfStudents;
		exit when someExams%notfound;
		dbms_output.put_line('id: '||anID||' number of students : '||aNoOfStudents);
	end loop;
close someExams;
end;
/

--- Q6 ---
create trigger numOfStudentsUpdater
after insert or delete on mark
for each row 
begin
case 
when inserting then
	update Exam set Exam.noOfStudents = Exam.noOfStudents + 1
	where Exam.id = :new.examID;
when deleting then
	update Exam set Exam.noOfStudents = Exam.noOfStudents - 1
	where Exam.id = :old.examID;
end case;
end;
/




--- section C ---
--- Q7 ---
create type ExamOccurred as object (
	whenOccurred date,
	member function getWhen return varchar(25))
not final;
/

create type body ExamOccurred as 
	member function getWhen return varchar(25) is 
		ans varchar(25);
		begin 
			ans := to_char(whenOccurred, 'HH24.MI DD/MONTH/YYYY');
			return ans;
		end getWhen;
	end;
/

create type ReferredExamOccurred under ExamOccurred (
	attempts integer
)
final;
/




--- section D ---
--- Q8 ---
100 // id = examID
ans + 40 + (40 * 3000) // noOfStudents > 1000
ans + 40 * 20 // project rows
ans = 120940

--- Q9 ---
// user0:
grant update (id, subject) on Exam to user1 with grant option;

// user1:
grant update (id, subject) on Exam to user2 with grant option;

// user0:
revoke update (id, subject) on Exam from user1;