/*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);

9. Using sub-query, create a report that display all patient information that have seen Dr.Jane during their appointment visit. [6 Marks] 10. Create a table name BILL (as in Table 1) that have bill number, bill insured, bil_notinsured, bill_status and bil_datesent as its attributes. Identify primary key and choose suitable datatype for each of the attributes [5 Marks] 11. Add new column name bill_description to table Bill in 11, defined suitable datatype for the new column [4 Marks] 12. Having the tables created in 11, write an INSERT command for the data as in Table 1 [6Marks] Table 1. Bill Bill Bill Bill Bill Num Insured NotInsured Status Date Sent Description B10111 2300.00 0.00 Paid 2020-07-12 Warded B10112 500.00 500.00 Not Paid 2020-07-15 Surgery Bill