/*Creating necessary tables with necessary columns */ create table DOCTOR(RegID int, D_Name varchar(20), D_Phone varchar(15), primary key(RegID)); create table INSURANCE_COMP(INS_ID int, INS_Name varchar(20), primary key(INS_ID)); create table PATIENT(P_ID int, P_Name varchar(20), P_Address varchar(20), P_State varchar(20), INS_ID int, primary key(P_ID), foreign key(INS_ID) references INSURANCE_COMP(INS_ID)); create table PAYMENt(P_ReceiptNum int, P_Amount int, P_ID int, INS_ID int, primary key(P_ReceiptNum), foreign key(P_ID) references PATIENT(P_ID), foreign key(INS_ID) references INSURANCE_COMP(INS_ID));

/* inserting dummy data into tables */ insert into DOCTOR values(1,"Jane","123456789"); insert into DOCTOR values(2,"Jack","987654321");

insert into INSURANCE_COMP values(11,"Tokio Marine"); insert into INSURANCE_COMP values(12,"Reliance");

insert into PATIENT values(21,"Siva","Ram Nagar","Kuala Lumpur",11); insert into PATIENT values(22,"Suresh","Temple Street","Selangor",12); insert into PATIENT values(23,"Ramesh","7th Avenue","Mumbai",11);

insert into PAYMENt values(31,1000,21,11); insert into PAYMENt values(32,500,23,12); insert into PAYMENt values(33,1000,22,11); insert into PAYMENt values(34,500,21,12);

5. Find total payment made by an insurance company called Takaful Nasional and Prudential Takaful for payment that is larger than 150. [8 Marks] 6. Find the number of patients treated by each doctor in July with a doctor with most patient appear first in the result list. [7 Marks] 7. Using subquery, find details of all patients who is insured by Takaful Nasional'. [4 Marks] 8. List details of patient who is not covered by an insurance company [4 Marks]