genius IT ians™

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