Lab 12 : Grading System using Pl/SQL



Write a program in PL/SQL to Calculate Grade, GPA and CGPA after considering a result table where subject wise marks are inserted by computer operator. Insert your calculated result to another table using Trigger or Procedure or any other suitable way (suggested by you).

3 comments:

  1. create table student(id number, name char(20));

    insert into student values(201310359,'Nayan');
    insert into student values(201310382,'Shazal');

    create table c_credit(c_code varchar(10), c_name char(50),credit number);

    insert into c_credit values('CSE 1102','PL-1',3.0);
    insert into c_credit values('CSE 1102S','PL-1 S',1.5);
    insert into c_credit values('CSE 1206','ES',3);
    insert into c_credit values('CSE 1114','CS',3);

    create table marks(id number,c_code varchar(10),semester char(10),year number,mark number);

    create table result(id number, c_code varchar(10),semester char(10),year number,credit number,grade varchar(2),GP Number,GPA number);

    CREATE or REPLACE TRIGGER stu_result
    AFTER INSERT ON marks
    FOR EACH ROW
    DECLARE
    num NUMBER;
    grd VARCHAR(2);
    c_creditt NUMBER;
    gpt NUMBER;
    gpat Number;
    BEGIN
    Select credit into c_creditt from c_credit where c_code=:new.c_code;
    num := :new.mark;
    IF num >= 80 THEN
    grd := 'A+' ;
    gpat :=4.00;
    gpt:= c_creditt*4.00;
    ELSIF num >= 75 AND
    num <= 79 THEN
    grd := 'A';
    gpat :=3.75;
    gpt:= c_creditt*3.75;
    ELSIF num >= 70 AND
    num <= 74 THEN
    grd := 'A-';
    gpat :=3.50;
    gpt:= c_creditt*3.50;
    ELSIF num >= 65 AND
    num <= 69 THEN
    grd := 'B+';
    gpat :=3.25;
    gpt:= c_creditt*3.25;
    ELSIF num >= 60 AND
    num <= 64 THEN
    grd := 'B';
    gpat :=3.00;
    gpt:= c_creditt*3.00;
    ELSIF num >= 55 AND
    num <= 59 THEN
    grd := 'B-';
    gpat :=2.75;
    gpt:= c_creditt*2.75;
    ELSIF num >= 50 AND
    num <= 54 THEN
    grd := 'C+';
    gpat :=2.50;
    gpt:= c_creditt*2.50;
    ELSIF num >= 45 AND
    num <= 49 THEN
    grd := 'C';
    gpat :=2.25;
    gpt:= c_creditt*2.25;
    ELSIF num >= 40 AND
    num <= 44 THEN
    grd := 'D';
    gpat :=2.00;
    gpt:= c_creditt*2.00;
    ELSE
    grd := 'F';
    gpat :=0.00;
    gpt:= c_creditt*0.0;
    END IF;
    INSERT INTO RESULT (id,c_code,semester,year,credit,grade,gp,gpa)
    VALUES
    (:new.id,
    :new.c_code,
    :new.semester,
    :new.year,
    c_creditt,
    grd,
    gpt,
    gpat
    );
    END;
    /

    commit;

    insert into marks values(201310359,'CSE 1102','Spring',2014,85);
    insert into marks values(201310359,'CSE 1102S','Spring',2014,93);
    insert into marks values(201310382,'CSE 1205','Spring',2014,76);
    insert into marks values(201310382,'CSE 1114','Spring',2014,64);

    commit;

    select * from result;
    select avg(gpa) as "CGPA of Spring Semester" from result where id=201310359 and semester='Spring';

    ReplyDelete
  2. create table student(
    id number,
    name varchar(20)
    );

    insert into student values(201310382,'Shazal');

    create table c_credit(c_code varchar(10), c_name char(50),credit number);

    insert into c_credit values('CSE 1102','PL-1',3.0);
    insert into c_credit values('CSE 1102S','PL-1 S',1.5);
    insert into c_credit values('CSE 1206','ES',3);
    insert into c_credit values('CSE 1114','CS',3);

    create table marks(id number,c_code varchar(10),semester char(10),year number,mark number);

    create table result(id number, c_code varchar(10),semester char(10),year number,credit number,grade varchar(2),GP Number,GPA number);

    CREATE or REPLACE TRIGGER S_Result
    AFTER INSERT ON marks
    FOR EACH ROW
    DECLARE
    num NUMBER;
    grd VARCHAR(2);
    c_creditt NUMBER;
    gpt NUMBER;
    gpat Number;
    BEGIN
    Select credit into c_creditt from c_credit where c_code=:new.c_code;
    num := :new.mark;
    IF num >= 80 THEN
    grd := 'A+' ;
    gpat :=4.00;
    gpt:= c_creditt*4.00;
    ELSIF num >= 75 AND
    num <= 79 THEN
    grd := 'A';
    gpat :=3.75;
    gpt:= c_creditt*3.75;
    ELSIF num >= 70 AND
    num <= 74 THEN
    grd := 'A-';
    gpat :=3.50;
    gpt:= c_creditt*3.50;
    ELSIF num >= 65 AND
    num <= 69 THEN
    grd := 'B+';
    gpat :=3.25;
    gpt:= c_creditt*3.25;
    ELSIF num >= 60 AND
    num <= 64 THEN
    grd := 'B';
    gpat :=3.00;
    gpt:= c_creditt*3.00;
    ELSIF num >= 55 AND
    num <= 59 THEN
    grd := 'B-';
    gpat :=2.75;
    gpt:= c_creditt*2.75;
    ELSIF num >= 50 AND
    num <= 54 THEN
    grd := 'C+';
    gpat :=2.50;
    gpt:= c_creditt*2.50;
    ELSIF num >= 45 AND
    num <= 49 THEN
    grd := 'C';
    gpat :=2.25;
    gpt:= c_creditt*2.25;
    ELSIF num >= 40 AND
    num <= 44 THEN
    grd := 'D';
    gpat :=2.00;
    gpt:= c_creditt*2.00;
    ELSE
    grd := 'F';
    gpat :=0.00;
    gpt:= c_creditt*0.0;
    END IF;
    INSERT INTO RESULT (id,c_code,semester,year,credit,grade,gp,gpa)
    VALUES
    (:new.id,
    :new.c_code,
    :new.semester,
    :new.year,
    c_creditt,
    grd,
    gpt,
    gpat
    );
    END;
    /

    insert into marks values(201310382,'CSE 1102','Summer',2014,77);
    insert into marks values(201310382,'CSE 1102S','Summer',2014,98);
    insert into marks values(201310382,'CSE 1205','Fall',2014,44);
    insert into marks values(201310382,'CSE 1114','Spring',2014,91);

    select * from result;
    select avg(gpa) as "CGPA of Spring Semester" from result where id=201310382 and semester='Summer';

    ReplyDelete

Popular Posts