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;
/