CREATE TABLE patient(patient_no number(5) primary key,patient_name varchar(50), p_add varchar(150),p_birth_date date,p_phone number(10) );
Create table Room(Room_id number(5) primary key, Room_type varchar(5), room_rate number(8,2));
create table Doctor(doc_id number(5) primary key,doc_name varchar(50),doc_add varchar(150),doc_type varchar(20),doc_mobile number(10));
create table Patient_transaction_table(patient_no references patient,doc_id references doctor,room_id references room,patient_admit_date date,patient_dischage_date date,total_room_charges number(10),treatment_name varchar(150),tretmenat_charge number(10),total_bill_amout number(10));
2-
insert into patient values(1,'ABC','21 C Street City','12/Mar/1980',9976099870);
insert into patient values(2,'DEF','21 C Street City','12/Mar/1980',9976099870);
insert into patient values(3,'GHI','21 C Street City','12/Mar/1980',9976099870);
insert into patient values(4,'JKL','21 C Street City','12/Mar/1980',9976099870);
insert into patient values(5,'MNO','21 C Street City','12/Mar/1980',9976099870);
insert into patient values(6,'KKK','21 C Street City','12/Mar/1980',9976099870);
insert into room values(1,'A',1000);
insert into room values(2,'A',1000);
insert into room values(3,'B',2000);
insert into room values(4,'B',2000);
insert into room values(5,'C',3000);
insert into room values(6,'C',3000);
insert into room values(7,'D',5000);
insert into room values(8,'D',5000);
insert into room values(9,'Z',10000);
insert into doctor values(1,'lele','21 J Street City', 'Visiting', 998754412);
insert into doctor values(2,'NeNe','21 J Street City', 'RMO', 998754412);
insert into doctor values(3,'SeSe','21 J Street City', 'Visiting', 998754412);
insert into doctor values(4,'Mishra','21 J Street City', 'Visiting', 998754412);
insert into doctor values(5,'Sharma','21 J Street City', 'RMO', 998754412);
insert into doctor values(6,'Darda','21 J Street City', 'RMO', 998754412);
insert into Patient_transaction_table values(1,1,1,'1-feb-2012','1-mar-2012',70000,'A',5000,750000);
insert into Patient_transaction_table values(2,1,2,'1-feb-2012','5-feb-2012',5000,'B',1000,6000);
insert into Patient_transaction_table values(3,3,3,'1-feb-2012','3-feb-2012',7000,'B',1000,8000);
insert into Patient_transaction_table values(4,4,5,'1-feb-2012','15-feb-2012',8000,'B',1000,9000);
insert into Patient_transaction_table values(5,2,9,'1-feb-2012','7-feb-2012',15000,'B',1000,16000);
insert into Patient_transaction_table values(6,2,9,'21-feb-2012','',15000,'B',1000,16000);
3-
Select patient.patient_no,patient.patient_name from patient,Patient_transaction_table,doctor where patient.Patient_no=Patient_transaction_table.patient_no and doctor.doc_id = Patient_transaction_table.doc_id and doc_name='lele';
4- Select * from doctor where doc_type='Visiting';
5-
Select * from patient,Patient_transaction_table where patient.Patient_no=Patient_transaction_table.patient_no and total_bill_amout>20000;
6-
Select count(*) from doctor where doc_type='Visiting';
7-
Select * from patient,Patient_transaction_table where patient.Patient_no=Patient_transaction_table.patient_no and ((sysdate-patient_admit_date)>10);
8-
Select * from patient,Patient_transaction_table where patient.Patient_no=Patient_transaction_table.patient_no and (patient_admit_date=sysdate);