Sunday, January 10, 2010

SQL- UNIVERSITY EXAM REGISTRATION SYSTEM



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



 

SQL- RECRUITMENT DATABASE



RECRUITMENT DATABASE



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

 

 

create table registration(regno number(6) primary key,name char(15),college char(15),date_of_reg date,degree char(10));

 

 

create table company_details(company_name char(15) primary key,salary number(8));

 

 

create table result(regno number(6) references registration(regno),marks number(5),selected char(3) default 'N');

 

 

create table agency_income_details(company_name char(15),agency_profit number(10,2));

 

 

>>>>>>>>>>>>>>>>>>>>PROCEDURE<<<<<<<<<<<<<<<<<<<<

 

create or replace procedure insertreg (pname char,pcoll char,pdgr char) is

cname char(15);

no number(6);

r number(6);

cnt number(2);

 

 

begin

cname:='&cname';

 

            select max(regno) into no from registration;

            if no is null then

            r:=1;

            else

            r:=no+1;

            end if;

           

            insert into registration values(r,pname,pcoll,sysdate,pdgr);

            dbms_output.put_line(' Registration success, your registration number is '||r);

            select COUNT(company_name) into cnt from company_details where company_name=cname;

            if cnt=0 then

            insert into company_details values(cname,&salary);

            end if;

 

end;

/

Enter value for cname: Wipro

old   7: cname:='&cname';

new   7: cname:='Wipro';

Enter value for salary: 25000

old  18:  insert into company_details values(cname,&salary);

new  18:  insert into company_details values(cname,25000);

 

Procedure created.

 

 

COMPANY_NAME                SALARY

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

Wipro                                      25000

 

 

 

 

 

>>>>>>>>>>>>>>>>>>>>TRIGGER<<<<<<<<<<<<<<<<<<<<

 

create or replace trigger trig after update on company_details for each row

declare

cnam char(15);

profit number(10,2);

intake number(4);

no_reg number(4);

no_appeared number(5);

sal number(10,2);

begin

if updating then

cnam:=:old.company_name;

sal:=:old.salary;

select COUNT(*) into no_reg from registration;

select COUNT(*) into no_appeared from result;

select COUNT(*) into intake from result where selected='Y';

 

 

profit:=((100*no_reg + 0.25*sal*intake + 6000*intake) - (50*no_appeared));

 

insert into agency_income_details values(cnam,profit);

 

end if;

end;

 /

 

Trigger created.

 

 

 

 

 

>>>>>>>>>>>>>>>>>>>>REGISTER<<<<<<<<<<<<<<<<<<<<

 

declare

 

pname char(15);

pcoll char(15);

pdgr char(10);

begin

pname:='&pname';

pcoll:='&pcoll';

pdgr:='&pdgr';

insertreg(pname,pcoll,pdgr);

 

end;

/

 

Enter value for pname: Tintumon

old   6: pname:='&pname';

new   6: pname:='Tintumon';

Enter value for pcoll: UCE

old   7: pcoll:='&pcoll';

new   7: pcoll:='UCE';

Enter value for pdgr: B tech

old   8: pdgr:='&pdgr';

new   8: pdgr:='B tech';

Registration success, your registration number is 1

 

PL/SQL procedure successfully completed.

 

SQL> /

Enter value for pname: Kuttoos

old   6: pname:='&pname';

new   6: pname:='Kuttoos';

Enter value for pcoll: VJC

old   7: pcoll:='&pcoll';

new   7: pcoll:='VJC';

Enter value for pdgr: B tech

old   8: pdgr:='&pdgr';

new   8: pdgr:='B tech';

Registration success, your registration number is 2

 

PL/SQL procedure successfully completed.

 

SQL> /

Enter value for pname: Tuttu

old   6: pname:='&pname';

new   6: pname:='Tuttu';

Enter value for pcoll: UCE

old   7: pcoll:='&pcoll';

new   7: pcoll:='UCE';

Enter value for pdgr: B tech

old   8: pdgr:='&pdgr';

new   8: pdgr:='B tech';

Registration success, your registration number is 3

 

PL/SQL procedure successfully completed.

 

SQL> select * from registration;

 

    REGNO       NAME             COLLEGE       DATE_OF_R             DEGREE

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

        1             Tintumon           UCE                 08-JAN-10                   B tech

        2             Kuttoos             VJC                  08-JAN-10                   B tech

        3             Tuttu                 UCE                 08-JAN-10                   B tech

 

 

>>>>>>>>>>>>>>>>>>>>RESULT<<<<<<<<<<<<<<<<<<<<

 

 

 

insert into result values(&regno,&marks,'&selected');

 

 

SQL> insert into result values(&regno,&marks,'&selected');

Enter value for regno: 1

Enter value for marks: 98

Enter value for selected:

old   1: insert into result values(&regno,&marks,'&selected')

new   1: insert into result values(1,98,'')

 

1 row created.

 

SQL> /

Enter value for regno: 2

Enter value for marks: 99

Enter value for selected:

old   1: insert into result values(&regno,&marks,'&selected')

new   1: insert into result values(2,99,'')

 

1 row created.

 

SQL> /

Enter value for regno: 3

Enter value for marks: 100

Enter value for selected:

old   1: insert into result values(&regno,&marks,'&selected')

new   1: insert into result values(3,100,'')

 

1 row created.

 

SQL> select * from result;

 

    REGNO       MARKS           SEL

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

        1              98

        2              99

        3              100

 

 

>>>>>>>>>>>>>>>>>>>>PL/SQL BLOCK<<<<<<<<<<<<<<<<<<<<

 

 

declare

no_reg number(4);

cnam char(15);

intake number(4);

no_appeared number(5);

i number(3);

rno number(6);

mk number(5);

sel char(3);

mx number(5);

sal number(8);

nam char(15);

cursor cur is select * from result;

begin

cnam:='&cnam';

intake:=&intake;

select COUNT(*) into no_appeared from result;

dbms_output.put_line(' Total no of appearence for the exam: '||no_appeared);

 

open cur;

loop

fetch cur into rno,mk,sel;

if cur%found then

select COUNT(*) into i from result where selected='Y';

if i=intake then

exit;

end if;

select MAX(marks) into mx from result where selected='N' or selected is null;

update result set selected='Y' where marks=mx;

else

exit;

end if;

end loop;

commit;

close cur;

 

 

dbms_output.put_line('Selected Candidates');

open cur;

loop

fetch cur into rno,mk,sel;

if cur%found then

if sel='Y' then

select name into nam from registration where regno=rno;

dbms_output.put_line(nam);

end if;

else

exit;

end if;

end loop;

commit;

close cur;

 

 

select salary into sal from company_details where company_name=cnam;

update company_details set salary=sal*1 where company_name=cnam;

 

end;

/

Enter value for cnam: Wipro

old  15: cnam:='&cnam';

new  15: cnam:='Wipro';

Enter value for intake: 2

old  16: intake:=&intake;

new  16: intake:=2;

Total no of appearence for the exam: 3

Selected Candidates

Kuttoos

Tuttu

 

PL/SQL procedure successfully completed.

 

SQL> select * from agency_income_details;

 

COMPANY_NAME    AGENCY_PROFIT

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

Wipro                          24650

 

SQL> select * from company_details;

 

COMPANY_NAME                SALARY

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

Wipro                                       25000

 

SQL> select * from registration;

 

    REGNO       NAME             COLLEGE       DATE_OF_R               DEGREE

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

        1             Tintumon           UCE                 08-JAN-10                   B tech

        2             Kuttoos             VJC                  08-JAN-10                   B tech

        3             Tuttu                 UCE                 08-JAN-10                   B tech

 

SQL> select * from result;

 

    REGNO       MARKS           SEL

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

        1              98

        2              99                    Y

        3              100                  Y




SQL- RAILWAY RESERVATION SYSTEM




RAILWAY RESERVATION SYSTEM



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

 

create table train_details(train_name char(15) primary key,total_seats number(3),reserved_seats number(3));

 

create table reservation_status(train_name char(15) references train_details(train_name),seat_id number(3),reserved char(2) check (reserved in('y','n')),customer_name char(15));

 

create table waiting_list(slno number(3),customer_name char(15) primary key,train_name char(15) references train_details(train_name));

 

 

>>>>>>>>>>>>>>>>>>>>ENTER TRAIN DETAILS<<<<<<<<<<<<<<<<<<<<

 

 

SQL> declare

  2 

  2  tname char(15);

  3  tot number(3);

  4  resv number(3);

  5  cursor cur is select * from train_details;

  6 

  6  begin

  7 

  7  insert into train_details values('&train_name',&total_seats,0);

  8 

  8  open cur;

  9  loop

 10  fetch cur into tname,tot,resv;

 11  if cur%found then

 12  for i in 1..tot

 13  loop

 14  insert into reservation_status values(tname,i,'n',null);

 15  end loop;

 16  else

 17  exit;

 18  end if;

 19  end loop;

 20  commit;

 21  close cur;

 22  end;

 23  /

Enter value for train_name: AA

Enter value for total_seats: 3

old   7: insert into train_details values('&train_name',&total_seats,0);

new   7: insert into train_details values('AA',3,0);

 

PL/SQL procedure successfully completed.

 

SQL> /

Enter value for train_name: BB

Enter value for total_seats: 2

old   7: insert into train_details values('&train_name',&total_seats,0);

new   7: insert into train_details values('BB',2,0);

 

PL/SQL procedure successfully completed.

 

SQL> select * from train_details;

 

TRAIN_NAME                        TOTAL_SEATS           RESERVED_SEATS

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

AA                                                3                                  0

BB                                                 2                                  0

 

SQL> select * from  reservation_status;

 

TRAIN_NAME                        SEAT_ID         RE       CUSTOMER_NAME

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

AA                                             1                      n

AA                                             2                      n

AA                                             3                      n

BB                                              1                      n

BB                                              2                      n

 

5 rows selected.

 

 


 

>>>>>>>>>>>>>>>>>>>>RESERVE A SEAT<<<<<<<<<<<<<<<<<<<<

 

 

 

declare

 

cname char(15);

tname char(15);

tot number(3);

resv number(3);

sid number(3);

sno number(3);

sl number(3);

 

begin

 

cname:=&cname;

tname:=&tname;

select total_seats into tot from train_details where train_name=tname;

select reserved_seats into resv from train_details where train_name=tname;

if tot>resv then

            select MIN(seat_id) into sid from reservation_status where train_name=tname and reserved='n';

            update reservation_status set reserved='y' where train_name=tname and seat_id=sid;

            update reservation_status set customer_name=cname where train_name=tname and seat_id=sid;

            update train_details set reserved_seats=reserved_seats+1 where train_name=tname;

else

            select MAX(slno) into sno from waiting_list;

            if sno is null then

                        sl:=1;

            else

                        sl:=sno+1;

            end if;

            insert into waiting_list values(sl,cname,tname);

end if;

 

end;

 

 

 

SQL> /

Enter value for cname: 'Tintumon'

old  10: cname:=&cname;

new  10: cname:='Tintumon';

Enter value for tname: 'AA'

old  11: tname:=&tname;

new  11: tname:='AA';

 

PL/SQL procedure successfully completed.

 

SQL> /

Enter value for cname: 'Kuttoos'

old  10: cname:=&cname;

new  10: cname:='Kuttoos';

Enter value for tname: 'BB'

old  11: tname:=&tname;

new  11: tname:='BB';

 

PL/SQL procedure successfully completed.

 

SQL> /

Enter value for cname: 'Appu'

old  10: cname:=&cname;

new  10: cname:='Appu';

Enter value for tname: 'BB'

old  11: tname:=&tname;

new  11: tname:='BB';

 

PL/SQL procedure successfully completed.

 

SQL> /

Enter value for cname: 'Tuttu'

old  10: cname:=&cname;

new  10: cname:='Tuttu';

Enter value for tname: 'BB'

old  11: tname:=&tname;

new  11: tname:='BB';

 

PL/SQL procedure successfully completed.

 

SQL> select * from reservation_status;

 

TRAIN_NAME                        SEAT_ID         RE       CUSTOMER_NAME

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

AA                                             1                      y          Tintumon

AA                                             2                      n

AA                                             3                      n

BB                                             1                      y          Kuttoos

BB                                             2                      y          Appu

 

5 rows selected.

 

SQL> select * from waiting_list;

 

     SLNO         CUSTOMER_NAME               TRAIN_NAME

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

        1                Tuttu                                        BB

 

 

 

 

 

>>>>>>>>>>>>>>>>>>>>CANCEL A RESERVATION<<<<<<<<<<<<<<<<<<<<

 

 

 

declare

 

cname char(15);

tname char(15);

sid number(3);

sno number(3);

sl number(3);

 

begin

 

cname:=&cname;

tname:=&tname;

select seat_id into sid from reservation_status where train_name=tname and customer_name=cname;

select MIN(slno) into sno from waiting_list where train_name=tname;

if sno is not null then

            select customer_name into cname from waiting_list where train_name=tname and slno=sno;

            update reservation_status set customer_name=cname where train_name=tname and seat_id=sid;

            delete from waiting_list where train_name=tname and slno=sno;

else

            update reservation_status set reserved='n' where train_name=tname and seat_id=sid;

            update reservation_status set customer_name=null where train_name=tname and seat_id=sid;

            update train_details set reserved_seats=reserved_seats-1 where train_name=tname;

end if;

 

end;

/

Enter value for cname: 'Appu'

old   8: cname:=&cname;

new   8: cname:='Appu';

Enter value for tname: 'BB'

old   9: tname:=&tname;

new   9: tname:='BB';

 

PL/SQL procedure successfully completed.

 

SQL> select * from reservation_status;

 

TRAIN_NAME                        SEAT_ID         RE       CUSTOMER_NAME

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

AA                                              1                      y          Tintumon

AA                                              2                      n

AA                                              3                      n

BB                                              1                      y          Kuttoos

BB                                              2                      y          Tuttu

 

5 rows selected.

 

SQL> select * from waiting_list;

 

     SLNO         CUSTOMER_NAME                           TRAIN_NAME

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

     

 

SQL> select * from train_details;

 

TRAIN_NAME            TOTAL_SEATS           RESERVED_SEATS

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

AA                                      3                                  1

BB                                      2                                  2





Contact Form

Name

Email *

Message *