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