genius IT ians™

UNIVERSITY EXAM REGISTRATION SYSTEM

>>>>>>>>>>>>>>>>>>>>CREATE TABLE<<<<<<<<<<<<<<<<<<<<

 

create table application(slno number(5) primary key,student_name char(15),branch char(7),college char(15),date_apply date);

 

 

create table university(student_name char(15),branch char(7) check( branch in('CS','IT','EC')),college char(15));

 

 

create table register_nos(regno number(5),student_name char(15),branch char(7),college char(15));

 

 

 

>>>>>>>>>>>>>>>>>>>>UNIVERSITY DATABASE<<<<<<<<<<<<<<<<<<<<<<

 

 

 

insert into university values('&student_name','&branch','&college');

 

SQL> insert into university values('&student_name','&branch','&college');

Enter value for student_name: Kuttoos

Enter value for branch: IT

Enter value for college: UCE

old   1: insert into university values('&student_name','&branch','&college')

new   1: insert into university values('Kuttoos','IT','UCE')

 

1 row created.

 

SQL> /

Enter value for student_name: Tuttu

Enter value for branch: CS

Enter value for college: VJC

old   1: insert into university values('&student_name','&branch','&college')

new   1: insert into university values('Tuttu','CS','VJC')

 

1 row created.

 

SQL> /

Enter value for student_name: Tintumon

Enter value for branch: CS

Enter value for college: UCE

old   1: insert into university values('&student_name','&branch','&college')

new   1: insert into university values('Tintumon','CS','UCE')

 

1 row created.

 

SQL> /

Enter value for student_name: Ammu

Enter value for branch: EC

Enter value for college: UCE

old   1: insert into university values('&student_name','&branch','&college')

new   1: insert into university values('Ammu','EC','UCE')

 

1 row created.

 

SQL> select * from university;

 

STUDENT_NAME      BRANCH        COLLEGE

---------------------------------------------------------------

Kuttoos                        IT                     UCE

Tuttu                            CS                    VJC

Tintumon                      CS                    UCE

Ammu                          EC                   UCE

 

 

>>>>>>>>>>>>>>>>>>>>APPLICATION<<<<<<<<<<<<<<<<<<<<

 

 

declare

 

last_date date;

reg number(5);

no number(5);

rcs number(5);

rec number(5);

rit number(5);

sno number(5);

sname char(15);

brch char(7);

clg char(15);

dat number(7,2);

nam char(15);

 

begin

 

rcs:=1000;

rec:=1100;

rit:=1200;

last_date:='07-JAN-10';

 

sno:=&sno;

sname:=&sname;

brch:=&brch;

clg:=&clg;

 

insert into application values(sno,sname,brch,clg,sysdate);

select student_name into nam from university where student_name=sname and branch=brch and college=clg;

select months_between(sysdate,last_date) into dat from dual;

 

if nam is null or dat>0 then

            dbms_output.put_line('Application Rejected');

else

            select MAX(regno) into no from register_nos where branch=brch;

            if no is null then

                        if brch='IT' then

                        reg:=rit;

                        elsif brch='CS' then

                        reg:=rcs;

                        elsif brch='EC' then

                        reg:=rec;

                        end if;

            else

                        reg:=no+1;

            end if;

            insert into register_nos values(reg,sname,brch,clg);

            dbms_output.put_line('Register No: '||reg);

end if;

 

end;

SQL> /

Enter value for sno: 1

old  19: sno:=&sno;

new  19: sno:=1;

Enter value for sname: 'Tintumon'

old  20: sname:=&sname;

new  20: sname:='Tintumon';

Enter value for brch: 'CS'

old  21: brch:=&brch;

new  21: brch:='CS';

Enter value for clg: 'UCE'

old  22: clg:=&clg;

new  22: clg:='UCE';

Register No: 1000

 

PL/SQL procedure successfully completed.

 

SQL> /

Enter value for sno: 2

old  19: sno:=&sno;

new  19: sno:=2;

Enter value for sname: 'Kuttoos'

old  20: sname:=&sname;

new  20: sname:='Kuttoos';

Enter value for brch: 'IT'

old  21: brch:=&brch;

new  21: brch:='IT';

Enter value for clg: 'UCE'

old  22: clg:=&clg;

new  22: clg:='UCE';

Register No: 1200

 

PL/SQL procedure successfully completed.

 

SQL> select * from register_nos;

 

    REGNO       STUDENT_NAME      BRANCH        COLLEGE

------------------------------------------------------------------------------------

     1000           Tintumon                       CS                    UCE

     1200           Kuttoos                         IT                     UCE

 

 

SQL> select * from application;

 

     SLNO         STUDENT_NAME      BRANCH        COLLEGE       DATE_APPL

--------------------------------------------------------------------------------------------------------------

        1             Tintumon                        CS                    UCE                  07-JAN-10

        2             Kuttoos                           IT                     UCE                 07-JAN-10