genius IT ians™

HOSPITAL MANAGEMENT

 

>>>>>>>>>>>>>>>>>>>>CREATE TABLE<<<<<<<<<<<<<<<<<<<<

 

 

create table doctors_details(doc_id number(3),name char(15),specialization char(15) primary key check(specialization in('ENT','GASTRO','CARDIO','OPTHAL', 'ORTHO', 'PSYCHO')),fees number(8,2));

 

create table register(regno number(5) primary key,name char(15),age number(3),sex char(3) check(sex in('M','F')),address char(20),date_reg date,category char(15) references doctors_details(specialization));

 

create table pat_doc_details(name char(15),doc_name char(15));

 

>>>>>>>>>>>>>>>>>>>>DOCTORS_DETAILS<<<<<<<<<<<<<<<<<<<<

 

insert into doctors_details values(&doc_id,'&name','&specialization',&fees);

 

SQL> insert into doctors_details values(&doc_id,'&name','&specialization',&fees);

Enter value for doc_id: 1

Enter value for name: Tintumon

Enter value for specialization: ENT

Enter value for fees: 100

old   1: insert into doctors_details values(&doc_id,'&name','&specialization',&fees)

new   1: insert into doctors_details values(1,'Tintumon','ENT',100)

 

1 row created.

 

SQL> /

Enter value for doc_id: 2

Enter value for name: Kuttoos

Enter value for specialization: CARDIO

Enter value for fees: 500

old   1: insert into doctors_details values(&doc_id,'&name','&specialization',&fees)

new   1: insert into doctors_details values(2,'Kuttoos','CARDIO',500)

 

1 row created.

 

SQL> select * from doctors_details;

 

   DOC_ID       NAME            SPECIALIZATION       FEES

-----------------------------------------------------------------------------------

        1             Tintumon          ENT                              100

        2             Kuttoos            CARDIO                       500

 

 

>>>>>>>>>>>>>>>>>>>>PL/SQL BLOCK<<<<<<<<<<<<<<<<<<<<

declare

reg number(5);

dname char(15);

pname char(15);

cat char(15);

 

begin

 

reg:=&reg;

insert into register values(reg,'&name',&age,'&sex','&address',sysdate,'&category');

select name into pname from register where regno=reg;

select category into cat from register where regno=reg;

select name into dname from doctors_details where specialization=cat;

 

insert into pat_doc_details values(pname,dname);

 

dbms_output.put_line('Mr/Mrs/Miss'||pname||' has been refered to '||'Dr.'||dname);

 

end;

/

Enter value for reg: 1

old   7: reg:=&reg;

new   7: reg:=1;

Enter value for name: Tuttu

Enter value for age: 17

Enter value for sex: M

Enter value for address: 17/23 vietnam colony

Enter value for category: ENT

old   8: insert into register values(reg,'&name',&age,'&sex','&address',sysdate,'&category');

new   8: insert into register values(reg,'Tuttu',17,'M','17/23 vietnam colony',sysdate,'ENT');

Mr/Mrs/MissTuttu           has been refered to Dr.Tintumon

 

PL/SQL procedure successfully completed.

 

SQL> /

Enter value for reg: 2

old   7: reg:=&reg;

new   7: reg:=2;

Enter value for name: Ammu

Enter value for age: 18

Enter value for sex: F

Enter value for address: 18/23 vietnam colony

Enter value for category: ENT

old   8: insert into register values(reg,'&name',&age,'&sex','&address',sysdate,'&category');

new   8: insert into register values(reg,'Ammu',18,'F','18/23 vietnam colony',sysdate,'ENT');

Mr/Mrs/MissAmmu            has been refered to Dr.Tintumon

 

PL/SQL procedure successfully completed.

 

SQL> /

Enter value for reg: 3

old   7: reg:=&reg;

new   7: reg:=3;

Enter value for name: Appu

Enter value for age: 19

Enter value for sex: M

Enter value for address: 1/23 gandhi colony

Enter value for category: CARDIO

old   8: insert into register values(reg,'&name',&age,'&sex','&address',sysdate,'&category');

new   8: insert into register values(reg,'Appu',19,'M','1/23 gandhi colony',sysdate,'CARDIO');

Mr/Mrs/MissAppu            has been refered to Dr.Kuttoos

 

PL/SQL procedure successfully completed.

 

SQL> select * from register;

 

    REGNO       NAME             AGE    SEX     ADDRESS                   DATE_REG     CATEGORY

-------------------------------------------------------------------------------------------------------------------------------

        1             Tuttu                  17       M         17/23 vietnam colony      07-JAN-10       ENT

        2             Ammu               18        F         18/23 vietnam colony      07-JAN-10        ENT

        3             Appu                 19        M         1/23 gandhi colony        07-JAN-10        CARDIO

 

 

>>>>>>>>>>>>>>>>>>>>QUERIES<<<<<<<<<<<<<<<<<<<<

 

declare

 

dname1 char(15);

dname2 char(15);

dname char(15);

cat char(15);

mx number(3);

cnt number(3);

earn number(8,2);

mxern number(8,2);

f number(8,2);

cursor cur is select distinct category from register;

 

begin

 

mx:=0;

mxern:=0;

dname1:='x';

dname2:='x';

 

open cur;

loop

fetch cur into cat;

 

if cur%found then

            select COUNT(*) into cnt from register where category=cat;

            select name into dname from doctors_details where specialization=cat;

            select fees into f from doctors_details where specialization=cat;

 

            if cnt>mx then

                        mx:=cnt;

                        dname1:=dname;

            end if;

 

            earn:=f*cnt;

            if earn>mxern then

                        mxern:=earn;

                        dname2:=dname;

            end if;

 

else

exit;

end if;

end loop;

commit;

close cur;

 

dbms_output.put_line('The doctor who has attended maximum number of patients is '||dname1);

dbms_output.put_line('The doctor who has earned the maximum is '||dname2);

 

 

end;

/

 

The doctor who has attended maximum number of patients is Tintumon

The doctor who has earned the maximum is Kuttoos

 

PL/SQL procedure successfully completed.