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