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