2020

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

--- Q2 ---
select name
from Student
left join Mark
on Student.id = Mark.id
where Mark.result > 60;

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

--- Q4 ---
with aa as (
	select Student.id as "id", Student.name as "name", 
	sum(nvl2(Mark.noOfAttempts, Mark.noOfAttempts, 0)) as "attempts"
	from Student, Mark
	where Student.id = Mark.studentID
	group by Student.id, Student.name;
)
select aa.name, aa.attempts, 
avg(nvl2(Mark.result, Mark.result, 0)) as "avg result"
from aa, Mark
where aa.id = Mark.studentID
group by aa.name, aa.attempts
having avg(nvl2(Mark.result, Mark.result, 0)) > 60;

--- Q5 ---
create procedure numberOfStudents (examID Exam.id%type) as
num Exam.noOfStudents%type;
begin
	num := (select sum(noOfStudents) 
	from Exam 
	where id = examID);
	dbms_output.put_line(examID||' '||num);
end;
/

execute numberOfStudents('Physics')

--- 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;
/

--- 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;
/

--- Q8 ---
304180

--- Q9 ---
grant update (id, subject) on Exam to userName1 with grant option;
grant update (id, subject) on Exam to userName2;
revoke update (id, subject) on Exam to userName1;