using mysql command line
Mediveron Clinic is a family clinic that provides a wide range of family health services, including advice on health problems, vaccinations, examinations and treatment, prescriptions for medicines, referrals to other health services and social services. For smooth management and services to its patients, Mediveron Clinic keeps all important information related to its clinic in its newly developed clinic management system. Below are the business rules Mediveron Clinic set out for its management system and Figure 1 illustrate the ERD for Mediveron Clinic Management System: . A doctor can be scheduled for many appointments, but may not have any scheduled at all. Each appointment is scheduled with exactly 1 doctor. A patient can schedule 1 or more appointments. One appointment is scheduled with exactly 1 patient. One patient can make many payments, but a single payment is made by only 1 patient. Some patients are insured by an insurance company. If they are insured, they can only carry insurance with one company. An insurance company can have many patients carry their policies. For patients that carry insurance, the insurance company will make payments, each single payment is made by exactly 1 insurance company. DOCTOR D_RegiD makes PAYMENT P_RecetNum (PK) D Name P_Amount D_Phone PDate schedules for PATIENT PDP) P_Method P_DCFK P Name APPOINTMENT App_Date (PK) is made by P_Address schedule + P State App_Time (PK ADD Reason INS ID (FK) INSURANCE COMP INS ID (PK D_RegiD (FK) insured by INS Name P_ID FK) INS_PhoneNo INS_Address Requirement: Download a file named Mclinic.word from kalam. Run this file in your MySQL by copy paste it to your MySQL Command Client. This is to setup Mediveron Clinic Management System in you MySQL server. Having the tables created and data inserted, write SQL query to answer the following questions: 1. Display patient name and address that have Kuala Lumpur or Selangor as State. [3 Marks ] 2. You are require to change the phone number of Dr.Jane to 0122213453. Update the particular table. [3 Marks] 3. Select patient id, patient name and patient address who is covered under an insurance company called Tokio Marine. [4 Marks) 4. Find total payment made to each insurance company and the total number of payment receipt sent out. [6 Marks] 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] 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 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
