cgpa.sql

SET SERVEROUTPUT ON;

CREATE OR REPLACE FUNCTION getvalue(grade IN VARCHAR2)
RETURN NUMBER IS
BEGIN
    IF grade = 'S' THEN
        RETURN 100;
    ELSIF grade = 'A+' THEN
        RETURN 90;
    ELSIF grade = 'A' THEN
        RETURN 80;
    ELSIF grade = 'B+' THEN
        RETURN 70;
    ELSIF grade = 'B' THEN
        RETURN 60;
    ELSIF grade = 'C+' THEN
        RETURN 50;
    ELSIF grade = 'C' THEN
        RETURN 40;
    ELSIF grade = 'F' THEN
        RETURN 0;
    END IF;
END getvalue;
/ 

CREATE TABLE studentmarks (
    name VARCHAR(30),
    ktuid VARCHAR(10),
    semester NUMBER,
    subject_name VARCHAR(30),
    subject_code VARCHAR(10),
    credit NUMBER,
    internal NUMBER,
    grade VARCHAR(2),
    PRIMARY KEY(ktuid, semester, subject_code)
);

CREATE TABLE sgpatable (
    ktuid VARCHAR(10),
    semester INT,
    sgpa NUMBER(4,2)
);

CREATE TABLE cgpatable (
    ktuid VARCHAR(10),
    cgpa NUMBER(4,2)
);

CREATE OR REPLACE TRIGGER mark_modified
    BEFORE UPDATE OR INSERT
    ON studentmarks
    FOR EACH ROW
DECLARE
    CURSOR crstudent(id VARCHAR2, sem NUMBER) IS 
        SELECT * FROM studentmarks WHERE ktuid = id AND semester = sem;
    temp NUMBER(5);
    sgpav NUMBER(4,2);
    cnt NUMBER;
    present NUMBER;
    sgpasum NUMBER(5,2);
    sem NUMBER;
    cgpav NUMBER(4,2);
BEGIN
    temp := :NEW.credit * getvalue(:NEW.grade) / 10;
    cnt := :NEW.credit;
    
    FOR rec IN crstudent(:NEW.ktuid, :NEW.semester) LOOP
        cnt := cnt + rec.credit;
        temp := temp + (rec.credit * getvalue(rec.grade) / 10);
    END LOOP;
    
    sgpav := TRUNC(temp / cnt, 2);
    
    SELECT COUNT(*) INTO present FROM sgpatable WHERE 
    ktuid = :NEW.ktuid AND semester = :NEW.semester;
    
    IF present = 0 THEN
        INSERT INTO sgpatable VALUES (:NEW.ktuid, :NEW.semester, sgpav);
    ELSE
        UPDATE sgpatable SET sgpa = sgpav WHERE 
        ktuid = :NEW.ktuid AND semester = :NEW.semester;
    END IF;

    SELECT SUM(sgpa), COUNT(*) INTO sgpasum, sem FROM sgpatable WHERE 
    ktuid = :NEW.ktuid;
    cgpav := TRUNC((sgpasum / sem), 2);  

    SELECT COUNT(*) INTO present FROM cgpatable WHERE 
    ktuid = :NEW.ktuid;
    
    IF present = 0 THEN
        INSERT INTO cgpatable VALUES (:NEW.ktuid, cgpav);
    ELSE
        UPDATE cgpatable SET cgpa = cgpav WHERE ktuid = :NEW.ktuid;
    END IF;
END; 
/

INSERT INTO studentmarks VALUES ('Kannan', 'kte23cs007', 
1, 'maths', 'mat001', 3, 50, 'A');
INSERT INTO studentmarks VALUES ('Kannan', 'kte23cs007', 
1, 'physics', 'phy001', 3, 50, 'B');
INSERT INTO studentmarks VALUES ('Kannan', 'kte23cs007', 
1, 'python', 'cst002', 2, 50, 'B');

INSERT INTO studentmarks VALUES ('Kannan', 'kte23cs007', 
2, 'graphics', 'grp003', 4, 50, 'A');
INSERT INTO studentmarks VALUES ('Kannan', 'kte23cs007', 
2, 'workshop', 'wrk004', 5, 50, 'S');

SELECT * FROM studentmarks;
SELECT * FROM sgpatable;


DROP TABLE cgpatable;
DROP TABLE sgpatable;
DROP TABLE studentmarks;