>>>>>>>>>>>>>>>>>>>>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:=®
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:=®
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:=®
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:=®
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.