>>>>>>>>>>>>>>>>>>>>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