Home / Index Page

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).

5 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
  3. Enroll today to get free access to our live demo session which is a great opportunity to interact with the trainer directly which is a placement based Salesforce training India with job placement and certification . Get salesforce training in affordable cost from a best computer institute.

    ReplyDelete
  4. I am so happy to found your blog post because it's really very informative. Please keep writing this kind of blogs and I regularly visit this blog. Have a look at my services.
    I have found this Salesforce training in India worth joining course. Try this Salesforce training in Hyderabad with job assistance. Join Salesforce training institutes in ameerpet with certification. Enroll for Salesforce online training in hyderabad with hands on course.

    ReplyDelete

Popular Posts