STUDENT INFORMATION SYSTEM

create table student (regno varchar2(3),name varchar2(7),paper1
number(3),paper2 number(3),paper3 number(3),paper4 number(3),total
number(3),average number(3),grade varchar2(5));
insert into student values('c42','Hasna',98,85,95,90,null,null,null);
insert into student values('c43','Banu',78,63,45,58,null,null,null);
insert into student values('c44','Sona',65,50,45,68,null,null,null);
insert into student values('c45','Mehu',78,77,75,76,null,null,null);
insert into student values('c46','Renju',68,67,60,74,null,null,null);
insert into student values('c47','rahi',98,84,95,87,null,null,null);
insert into student values('c48','Jumi',55,70,65,67,null,null,null);
insert into student values('c49','Naz',95,94,89,88,null,null,null);
declare
cursor c1 is
select * from student order by regno;
vstud c1%rowtype;
begin
dbms_output.put_line('------------------------------------');
dbms_output.put_line('regno name paper1 paper2 paper3 paper4 total average
grade');
dbms_output.put_line('------------------------------------');
open c1;
loop
fetch c1 into vstud;
exit when c1%notfound;
vstud.total:=vstud.paper1+vstud.paper2+vstud.paper3+vstud.paper4;
vstud.average:=(vstud.total)/4;
if(vstud.average>=90) then
vstud.grade:='A';
else if(vstud.average>=60) then
vstud.grade:='B';
else if(vstud.average>=50) then
vstud.grade:='C';
else if(vstud.average>=40) then
vstud.grade:='D';
else
vstud.grade:='E';
end if;
end if;
end if;
end if;
update student set total=vstud.total,average=vstud.average,grade=vstud.grade where
regno=vstud.regno;
dbms_output.put_line('vstud.regno,vstud.name,vstud.paper1,vstud.paper2,vstud.pap
er3,vstud.paper4,vstud.total,vstud.average,vstud.grade');
end loop;
close c1;
end;
/

OUTPUT

------- --------- --------- --------- --------- --------- --------- ---- --------------- ----------- -
REG NAME PAPER1 PAPER2 PAPER3 PAPER4 TOTAL AVERAGE GRADE
--- ------- --------- --------- --------- --------- --------- --------- - --- ------- --------- -- ------
c42 Hasna 98 85 95 90 368 92 A
c43 Banu 78 63 45 58 244 61 B
c44 Sona 65 50 45 68 228 57 C
c45 Mehu 78 77 75 76 306 77 B
c46 Renju 68 67 60 74 269 67 B
c47 rahi 98 84 95 87 364 91 A
c48 Jumi 55 70 65 67 257 64 B
c49 Naz 95 94 89 88 366 92 A
--------- -- --------- -- --------- -- --------- -- --------- -- --------- -- --------- -- -