Click here to Skip to main content
15,078,444 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i am trying to calculate the cgpa of a student from his term gpa. and store the gpa value in gpa coloumn in the result table.

my code link:http://paste.ubuntu.com/21677789/.

finally it's showing the output: hello term1

1307001 2 1 2

hello year2

hello term1

1307001 3 1 1.7

hello year3

hello roll1307001

hello term1

1307002 2 1 1.71315789473684210526315789473684210526

hello year2

hello term1

1307002 3 1 1.76315789473684210526315789473684210526

hello year3

hello roll1307002

PL/SQL procedure successfully completed.

and gpa value is not updated in result value..why value of tt showing too long ...help please..thanks in advance.

What I have tried:

drop table result;
drop table cgpa_cal;
drop table courses;
drop table student;
create table student(
roll number(10),
fname varchar(30),
lname varchar(30),
department varchar(50)
);
ALTER TABLE student ADD PRIMARY KEY (roll)


create table courses(
c_id varchar(30) NOT NULL,
c_name varchar(30),
credit integer CHECK(credit>0 and credit<5)
);

ALTER TABLE COURSES ADD PRIMARY KEY (c_id)
ALTER TABLE COURSES ADD UNIQUE KEY (c_id)



create table result(
id number(2),
roll number(10),
c_id varchar(30),
marks number (6,2),
year number(3),
term number(3),
points number(3,2),
credit NUMBER CHECK(credit>0 and credit<5),
grade varchar (4),
gpa number(3,2)
);

ALTER TABLE result ADD FOREIGN KEY (roll) REFERENCES student(roll)
ALTER TABLE result ADD FOREIGN KEY (c_id) REFERENCES courses(c_id)



describe student;
describe courses;
describe result




insert into student (roll,fname,lname,department) values (1307001,'a','ss','cse');
insert into student (roll,fname,lname,department) values (1307002,'b','gg','cse');
insert into student (roll,fname,lname,department) values (1307003,'c','ll','cse');
insert into student (roll,fname,lname,department) values (1307004,'d','kk','cse');


insert into courses (c_id,c_name,credit) values ('cse3101','toc',3.00);
insert into courses (c_id,c_name,credit) values ('cse3103','micro',3.00);
insert into courses (c_id,c_name,credit) values ('cse3109','database',3.00);
insert into courses (c_id,c_name,credit) values ('cse3119','se',3.00);


set serveroutput on
CREATE TRIGGER TR_GRAD
BEFORE INSERT OR UPDATE ON result
FOR EACH ROW
BEGIN
IF :NEW.MARKS>=80 THEN
:NEW.GRADE:='A+';
:NEW.POINTS:=4.00;
ELSIF :NEW.MARKS>=70 AND :NEW.MARKS<80 THEN
:NEW.GRADE:='B';
:NEW.POINTS:=3.30;
ELSIF :NEW.MARKS>=60 AND :NEW.MARKS<70 THEN
:NEW.GRADE:='C';
:NEW.POINTS:=3.10;
ELSIF :NEW.MARKS>=50 AND :NEW.MARKS<60 THEN
:NEW.GRADE:='D';
:NEW.POINTS:=3.00;
ELSIF :NEW.MARKS>40 AND :NEW.MARKS<50 THEN
:NEW.GRADE:='E';
:NEW.POINTS:=2.75;
ELSIF :NEW.MARKS<=40 THEN
:NEW.GRADE:='F';
:NEW.POINTS:=0.00;
END IF;
END TR_GRAD;
/



insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (1,1307001,'cse 3103',200,2,1,3,NULL,NULL,NULL);
insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (2,1307001,'cse 3105',200,2,1,3,NULL,NULL,NULL);
insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (3,1307001,'cse 3109',200,2,1,4,NULL,NULL,NULL);
insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (4,1307001,'cse 3119',200,2,1,3,NULL,NULL,NULL);
insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (5,1307001,'cse 3103',200,2,1,3,NULL,NULL,NULL);
insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (6,1307001,'cse 3103',200,2,1,3,NULL,NULL,NULL);

insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (1,1307001,'cse 3103',69,3,1,4,NULL,NULL,NULL);
insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (2,1307001,'cse 3103',78,3,1,3,NULL,NULL,NULL);
insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (3,1307001,'cse 3103',89,3,1,3,NULL,NULL,NULL);
insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (4,1307001,'cse 3103',57,3,1,3,NULL,NULL,NULL);
insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (5,1307001,'cse 3103',60,3,1,3,NULL,NULL,NULL);
insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (6,1307001,'cse 3103',90,3,1,3,NULL,NULL,NULL);

insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (1,1307002,'cse 3103',200,2,1,3,NULL,NULL,NULL);
insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (2,1307002,'cse 3103',64,2,1,3,NULL,NULL,NULL);
insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (3,1307002,'cse 3103',56,2,1,4,NULL,NULL,NULL);
insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (4,1307002,'cse 3103',80,2,1,3,NULL,NULL,NULL);
insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (5,1307002,'cse 3103',70,2,1,3,NULL,NULL,NULL);
insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (6,1307002,'cse 3103',78,2,1,3,NULL,NULL,NULL);

insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (1,1307002,'cse 3103',100,3,1,4,NULL,NULL,NULL);
insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (2,1307002,'cse 3103',70,3,1,3,NULL,NULL,NULL);
insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (3,1307002,'cse 3103',78,3,1,3,NULL,NULL,NULL);
insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (4,1307002,'cse 3103',60,3,1,3,NULL,NULL,NULL);
insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (5,1307002,'cse 3103',77,3,1,3,NULL,NULL,NULL);
insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (6,1307002,'cse 3103',89,3,1,3,NULL,NULL,NULL);

commit;



select * from student;
select * from courses;
select * from result;

SET SERVEROUTPUT ON;
DECLARE
rol result.roll%type;
total NUMBER ;
rid number(2);
pointt result.points%type;
credits result.credit%type;
summ result.credit%type;
gpa number;
digit number;
y result.year%type;
t result.term%type;
course result.c_id%type;
TT result.credit%type;
CR result.credit%type;

BEGIN
rol:=1307001;
y:=2;
t:=1;
rid:=1;
total:=0;
TT:=0;
course:=0;
SUMM:=0;
CR:=0;
LOOP
y:=2;
loop
t:=1;




RID:=1;
TT:=0;
TOTAL:=0;
CR:=0;
summ:=0;


LOOP
SELECT points,credit,year,term,c_id into pointt,credits,y,t,course FROM result WHERE roll=rol and year=y and term=t and id=rid;
total:=pointt;


TOTAL:=TOTAL*credits;


TT:=TT+TOTAL;

CR:=CR+credits;

RID:=RID+1;

EXIT WHEN RID>6;




END LOOP;


dbms_output.put_line('hello term'|| t);

SELECT SUM(CREDIT) INTO SUMM FROM result WHERE ROLL=ROL;

TT:=TT/SUMM;
update result set gpa=tt where rol=roll and id=1 and year=y and term=t;
dbms_output.put_line(rol||' '||y||' '||t||' '||tt);

dbms_output.put_line('hello year'|| y);
y:=y+1;
exit when y>3;
end loop;
dbms_output.put_line('hello roll'|| rol);
rol:=rol+1;
exit when rol>1307002;
end loop;




END;
/
Posted
Comments
Suvendu Shekhar Giri 31-Jul-16 22:55pm
   
Debug and see?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900