Scribd
Upload
6 views6 pages

DB2 Lab Week6 PLSQL 2

The document contains multiple PL/SQL code blocks for managing student records, including calculating GPAs, updating grades, and inserting new student entries. It features loops and conditional statements to process data from student and subject tables. Each block serves a specific function, such as displaying GPA levels based on calculated values or inserting multiple student records in a loop.

Uploaded by

markkie1998
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
6 views6 pages

DB2 Lab Week6 PLSQL 2

The document contains multiple PL/SQL code blocks for managing student records, including calculating GPAs, updating grades, and inserting new student entries. It features loops and conditional statements to process data from student and subject tables. Each block serves a specific function, such as displaying GPA levels based on calculated values or inserting multiple student records in a loop.

Uploaded by

markkie1998
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

1--

declare

v_stuid l5_register.stuid%type;

v_stuGPA number(5,2);

begin

select [Link],to_char(sum(case [Link]

when 'A' then 4*[Link]

when 'B' then 3*[Link]

when 'C' then 2*[Link]

when 'D' then 1*[Link]

when 'F' then 0*[Link]

else 0*[Link]

end)/sum([Link]),'99.99') as GPA

into v_stuid,v_stuGPA

from l5_subject s ,l5_register r

where [Link]=[Link] and [Link]=&v_stuid

group by [Link];

dbms_output.put_line('studentid:'||v_stuid||' '||'GPA:'||v_stuGPA);

end;
--2

declare

m_subjid l5_subject.subjid%type;

m_subjname l5_subject.subjname%type;

m_amount number(2);

Begin

for i in 1001..1005 loop

select [Link], [Link], count([Link])

into m_subjid,m_subjname,m_amount

from l5_subject s, l5_register r

where [Link]=[Link] and [Link]='2/2558' and [Link]=i

group by [Link],[Link];

dbms_output.put_line(m_subjid||' '|| m_subjname||' '|| m_amount);

end loop ;

End;

--3

Declare

m_subjid l5_register.SUBJID%type;

m_stuid l5_register.stuid%type;

m_term l5_register.term%type;

m_grade l5_register.grade%type;

Begin

m_subjid:=&subjectid;

m_stuid:=&studentid;
m_term:='&term';

m_grade:='&grade';

update l5_register

set grade=m_grade

where grade is null and subjid=m_subjid

and stuid=m_stuid and term=m_term;

commit;

DBMS_output.put_line('Grade ' || m_grade);

End;

--4

Declare

m_stuid l5_student.stuid%type;

m_stuname L5_STUDENT.STUNAME%type;

m_gender L5_student.gender%type ;

m_birthday l5_student.birthday%type ;

i number :=1;

Begin

select max(stuid) into m_stuid from l5_student;

loop

m_stuname:='&stuname';

m_gender:='&stugender';

m_birthday:='&stuBD';
insert into L5_Student values(m_stuid+i, m_stuname, upper(m_gender), to_date(m_birthday,'dd-
mm-yyyy'));

i:= i+1;

m_stuname:= null;

m_gender:= null;

m_birthday:= null;

exit when i>3;

End loop;

End;

--5

Declare

v_stuid l5_register.stuid%type;

v_stuGPA number(5,2);

Begin

for I in 10001..10005 loop

select [Link],to_char(sum(case [Link]

when 'A' then 4*[Link]

when 'B' then 3*[Link]

when 'C' then 2*[Link]

when 'D' then 1*[Link]

when 'F' then 0*[Link]

else 0*[Link]
end)/sum([Link]),'99.99') as GPA

into v_stuid,v_stuGPA

from l5_subject s ,l5_register r

where [Link]=[Link] and [Link]=I

group by [Link];

If v_stuGPA < 2 then

dbms_output.put_line('studentid:'||v_stuid||' '||'GPA:'||v_stuGPA ||' '||'level= unsatified');

elsif v_stuGPA < 2.49 then

dbms_output.put_line('studentid:'||v_stuid||' '||'GPA:'||v_stuGPA ||' '||'level= Fair');

elsif v_stuGPA < 2.99 then

dbms_output.put_line('studentid:'||v_stuid||' '||'GPA:'||v_stuGPA ||' '||'level= Good');

elsif v_stuGPA < 3.99 then

dbms_output.put_line('studentid:'||v_stuid||' '||'GPA:'||v_stuGPA ||' '||'level= Very Good');

else

dbms_output.put_line('studentid:'||v_stuid||' '||'GPA:'||v_stuGPA ||' '||'level= Excellent');

end if;

end loop;

End;

You might also like