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