Saturday, January 9, 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(®no,&marks,'&selected');
SQL> insert into result values(®no,&marks,'&selected');
Enter value for regno: 1
Enter value for marks: 98
Enter value for selected:
old 1: insert into result values(®no,&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(®no,&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(®no,&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
Subscribe to Posts [Atom]
