Create a student database (rollno,name,tot_marks,trade). Write a PL/SQL program to create student details for each trade adding an extra field 'rank' which includes the ranking details of each student. Also print top 3 rankers.
create table student(rollno INT,name char(25),tot_marks number(10),trade char(25));
create table IT(rollno INT,name char(25),tot_marks number(10),trade char(25),rank number(3));
create table CS(rollno INT,name char(25),tot_marks number(10),trade char(25),rank number(3));
declare
rno INT;
nam char(25);
tot number(10);
trd char(25);
rnk number(3);
mx number(10);
cursor curr is select * from student;
cursor cit is select * from IT;
cursor ccs is select * from CS;
i number(4);
begin
delete from IT;
delete from CS;
open curr;
loop
fetch curr into rno,nam,tot,trd;
if(curr%found) then
if trd='IT' then
insert into IT values(rno,nam,tot,trd,rnk);
elsif trd='CS' then
insert into CS values(rno,nam,tot,trd,rnk);
end if;
else
exit;
end if;
end loop;
commit;
close curr;
i:=1;
update CS set rank=null;
open ccs;
loop
fetch ccs into rno,nam,tot,trd,rnk;
if(ccs%found) then
select max(tot_marks) into mx from CS where rank is null;
update CS set rank=i where tot_marks=mx;
i:=i+1;
else
exit;
end if;
end loop;
commit;
close ccs;
i:=1;
update IT set rank=null;
open cit;
loop
fetch cit into rno,nam,tot,trd,rnk;
if(cit%found) then
select max(tot_marks) into mx from IT where rank is null;
update IT set rank=i where tot_marks=mx;
i:=i+1;
else
exit;
end if;
end loop;
commit;
close cit;
end;
select * from IT order by rank;
select * from CS order by rank;
SQL> select * from student;
ROLLNO NAME TOT_MARKS TRADE
----------------------------------------------------------------------------
1 Ammu 555 IT
2 Appu 556 IT
1 Tinto 585 CS
2 Tintumon 598 CS
3 Kuttoos 542 IT
3 Tuttu 562 CS
6 rows selected.
SQL> select * from IT;
ROLLNO NAME TOT_MARKS TRADE RANK
----------------------------------------------------------------------------------------------------------
1 Ammu 555 IT 2
2 Appu 556 IT 1
3 Kuttoos 542 IT 3
SQL> select * from CS;
ROLLNO NAME TOT_MARKS TRADE RANK
------------------------------------------------------------------------------------------------------
1 Tinto 585 CS 2
2 Tintumon 598 CS 1
3 Tuttu 562 CS 3
0 Comments