SQL- student database

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