Sunday, January 10, 2010

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



0 comments:

Post a Comment

Contact Form

Name

Email *

Message *

The Insane Techie - Android App

Launched an android app for the blog on 07th June 2016. Get it from google play store... Tips for using the app Use in landscape mo...