Students Result Calculation program using PL/SQL

Objective

An examination has been conducted to a class of 5 students and four scores of each student have been provided in the data along with  register number and name. Write a PL/SQL block to do the following

Assign a  letter grade to each student based on the average score;

Average Score     Grade

90-100                   a

75-89                      b

60-74                     c

50-59                     d

0-49                        e

Program

SQL> create table studres(regno number(4) primary key, name varchar(20), paper1 number(2), paper2 number(2), paper3 number(2), paper4 number(2));

Table created

SQL> insert into studres values(1001,’mini’, 23, 49, 44, 46);

1 row created

SQL> insert into studres values(1002,’safeer’, 40, 30, 20, 10);

1 row created

SQL> insert into studres values(1003,’baby’, 49, 39, 46, 45);

1 row created

 SQL> insert into studres values(1004,’danish’, 40, 10, 20, 22);

1 row created

SQL> insert into studres values(1005,’swetha’, 20, 18, 20, 15);

1 row created

SQL> alter table studres add( averg number(5,2), grade varchar(2));

Table altered

SQL> declare

2    cursor c is select * from studres;

3    v_avg studres.averg %type;

4    v_tot studres.tot%type;

5    v_grade studres.grade%type;

6    begin

7    for i in c loop

8    v_tot = i.paper1+i.paper2+i.paper3+i.paper4;

9 v_avg=v_tot/2;

10   if(v_avg>=85) then

11   v_grade:=’A’

12   elsif (v_avg>=65) then

13   v_good:=’B’;

14  elsif(v_avg>=40) then

15    v_grade:=’C’;

16    elsif(v_avg>=25) then

17 v_grade:=’D’;

18 else

19  v_grade:=’E’;

20 end if ;

21 update studres set averg=v_avg, grade=v_grad, where regno=i.regno;

22 end loop;

23  end;

24 /

PL/SQL procedure successfully completed

SQL> select* from studres;

REGNO     NAME     PAPER1     PAPER2     PAPER3     PAPER4     AVERG     GRADE

1001          mini            23               49              44             46             40.5            C

1002          safeer         40               30              20             10             25               D

1003          baby            49              39              46             45             44.75          C

1004          danish         40              10              20             22             23                E

1005          swetha        20              18              20             15             18.25          E