Saturday, January 9, 2010

SQL- HOSPITAL MANAGEMENT




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.


4 comments:

Arjun said...

The European Commission has unveiled an action plan to address barriers to the full use of digital solutions in Europe's healthcare systems.

healthmanagement

Olivia Madison said...

Thanks for sharing this Hospital Management database code with us. It’s really very useful for Hospital and Health organization. Hospital Management is a very important factor for hospitals and helps to store important data’s.

Wheelchair said...

Hey, very nice site. I came across this on Google, and I am stoked that I did. I will definitely be coming back here more often. Wish I could add to the conversation and bring a bit more to the table, but am just taking in as much info as I can at the moment. Thanks for sharing.
Powered Wheelchairs

Keep Posting:)

Wheelchair said...

Hey, very nice site. I came across this on Google, and I am stoked that I did. I will definitely be coming back here more often. Wish I could add to the conversation and bring a bit more to the table, but am just taking in as much info as I can at the moment. Thanks for sharing.
Karma KM 2500

Keep Posting:)