Question

1 Approved Answer

Posted on Sep 22, 2024

Question 1 [6] Create a query to display the number of times patient cancelled appointment made with the doctors by making use of NOT operator.

Question 1 [6] Create a query to display the number of times patient cancelled appointment made with the doctors by making use of NOT operator. Your output must resemble the one below. Cancelled Appointments MHLANGA... J(7809124123456) cancelled 2 appointment(s) DE WAARRIN. W(7609097812034) cancelled 1 appointment(s) DE WAARDTW2 J(8106070534083) cancelled 1 appointment(s) LEWIS. J(8110102467081) cancelled 5 appointment(s) Question 2 [6] Create a query that retrieves all doctors that has treated patients. The query must also display the date and consulations that patients partook in. Your output must resemble the one below. Treatments Gama Cj treated Due De Waardt on the Fifth of January 2016 for FULL MEDICAL Gama Ci treated Dire Mhlanga on the Thirteenth of January 2016 for CONSULTATION Gama Ci treated W., De Waardt on the Thirteenth of February 2016 for CONSULTATION Van Wyk Di treated La De Waardt on the Thirteenth of January 2016 for CONSULTATION Gama Cj treated Pure De Waardt on the Fifth of January 2016 for INJECTION - VOLTAREN 40 Gama Ci treated Due De Waardt on the Fifth of January 2016 for REMOVE STITCHES 6 rows selected. Question 3 [7] Create a query to display all patients who made appointments. The query must also display patient's treatment and whether the patient has allergies or not. For every patient with no allergies, the word "None" should be inserted as detailed in the output below. PATIENT PRESCRIPTION ALLERGY De Waardt. W , 7609097812034 Mhlanga. J, 7809124123456 De Waardt. W , 7609097812034 Mhlanga. J, 7809124123456 De Waardt. W , 7609097812034 De Waardt, W, 7609097812034 Satafast 50mg Imodium 2mg Ultracet Probiflora Probiflora Degoran None None None None None None 6 rows selected. QUESTION 4 (25) a. Write a SQL statement to create a table mydoctor that has the following columns and rules. (5) Column Doctor identity Doctor name Data type & size Must be 5 characters Must be at most 15 characters Must be numeric Doctor_pay b. Write a sql script that can be used to insert into mydoctor's table by prompting the user to do so. (Substitution variables) (62 C. Insert the following rows into mydoctor table. (Do not use substitution variables) (6) 14586 54612 17896 ZuluSiphamandla Ndlovu Nomphilo Johnson Johnson 14000 15260 11520 d. Add another doctor into the mydoctor table. This doctor does not have his pay set yet. The doctor is Mrs. Van Wissel and the id is 96385. e. Remove all the doctors who are currently paid from the mydoctor table. (3) (3) f. Remove from the table all the doctor(s) left. (1) g. Remove mydoctor table from the database. (1) DATABASE STRUCTURE CONTRA PRESCODE CHAR(5) ALLERGY VARCHAR2(30) MEDICALCONDITIONS V EVENT CHARIS) NOT NULL DESCRIPTION VARCHAR2(30) PATIENTVISIT V PATIENTID VARCHAR2(13) NOT NULL COMMENTS LONG VISITDATE DATE NOT NULL DIAGNOSIS VARCHAR2(30) ALLERGYLOOKUP VALLERGY VARCHAR2(30) NOT NULL PRESCRIBED PRESCODE CHAR(S) NOT NULL DESCRIPTION VARCHAR2(30) PATIENTHISTORY V PATENTD VARCHAR2(13) NOT NULL MEDICALEVENT CHAR(5) NOT NULL DATE_OF_EVENT DATE NOT NULL DOCTOR DOCTORIO VARCHAR2(13) NOT NULL DOCTORNAME VARCHAR2(25) DOCTORCODE CHAR(T) NOT NULL PATIENTALLERGY PATENTD VARCHAR2(13) NOT NULL ALLERGY VARCHAR2(30) NOT NULL TREATMENT TREATO CHAR(5) NOT NULL TREATDESCRIPTION VARCHAR2(30) PATIENTMEDICINE U PATIENTE VARCHAR2(13) NOT NULL PRESC_DATE DATE NOT NULL PRESCODE CHAR(S) NOT NULL APPOINTMENT V PATENTO VARCHAR2(13) NOT NULL U DOCTORID VARCHAR2(13) NOT NULL APPOINTTYPE VARCHAR2(20) APPOINTDATE DATE NOT NULL CANCELLED CHAR(1) TREATCOST TREATE CHAR(5) NOT NULL STARTDATE DATE NOT NULL FEE NUMBER(6,2) COST NUMBER(6,2) PATIENTTREATMENT TREATID CHAR(S) NOT NULL PATIENTE VARCHAR2(13) NOT NULL TREATMENTDATE DATE NOT NULL DOCTORID VARCHAR2(13) PATIENT U PATIENTID VARCHAR213) NOT NULL FIRSTNAME VARCHAR2(15) LASTNAME VARCHAR2(30) RESP VARCHAR2(13) SEX CHAR(1) INITIALS VARCHAR2/5) ( MEDICALFUND V MEDFUND VARCHAR2(30) NOT NULL ADDRESS: VARCHAR2(20) ADDRESS VARCHAR2(20) POSTALCODE CHAR(4) TELNO VARCHAR2(20) E_MAIL VARCHAR2(20) PAYMENTS RESPACCOUNT VARCHAR2(13) NOT NULL V PAYDATE DATE NOT NULL METHOD VARCHAR2(10) PAYTYPE VARCHAR2(15) CHEQUENUMBER VARCHAR2(10) BANKNUMBER VARCHAR2(20) AMOUNT NUMBER(8,2) RESPACCOUNT VIONUMBER VARCHAR2(13) NOT NULL TITLE VARCHAR215) FIRSTNAME VARCHAR2(15) INITIALS VARCHAR2(3) LASTNAME VARCHAR2(25) ADDRESS: VARCHAR2(15) ADDRESS VARCHAR2(15) POSTALADDRES51 VARCHAR2(20) POSTALADDRESS2 VARCHAR2(30) POSTALCODE CHAR(4) EMPNAME VARCHAR2(30) EMPADORESS VARCHAR2/30) TELNO VARCHAR220) CELLNO VARCHAR2(15) TELNOWORK VARCHAR2(20) EMPTELNO VARCHAR2(15) Key Symbols RESPMEDFUND U RESPO VARCHAR2(13) NOT NULL MEDFUND VARCHAR2(30) NOT NULL MEDFUNDTYPE VARCHAR2(15) MEDFUNDNUMBER VARCHAR2(15) Primary key 4 Foreign Key U Unige Key Question 1 [6] Create a query to display the number of times patient cancelled appointment made with the doctors by making use of NOT operator. Your output must resemble the one below. Cancelled Appointments MHLANGA... J(7809124123456) cancelled 2 appointment(s) DE WAARRIN. W(7609097812034) cancelled 1 appointment(s) DE WAARDTW2 J(8106070534083) cancelled 1 appointment(s) LEWIS. J(8110102467081) cancelled 5 appointment(s) Question 2 [6] Create a query that retrieves all doctors that has treated patients. The query must also display the date and consulations that patients partook in. Your output must resemble the one below. Treatments Gama Cj treated Due De Waardt on the Fifth of January 2016 for FULL MEDICAL Gama Ci treated Dire Mhlanga on the Thirteenth of January 2016 for CONSULTATION Gama Ci treated W., De Waardt on the Thirteenth of February 2016 for CONSULTATION Van Wyk Di treated La De Waardt on the Thirteenth of January 2016 for CONSULTATION Gama Cj treated Pure De Waardt on the Fifth of January 2016 for INJECTION - VOLTAREN 40 Gama Ci treated Due De Waardt on the Fifth of January 2016 for REMOVE STITCHES 6 rows selected. Question 3 [7] Create a query to display all patients who made appointments. The query must also display patient's treatment and whether the patient has allergies or not. For every patient with no allergies, the word "None" should be inserted as detailed in the output below. PATIENT PRESCRIPTION ALLERGY De Waardt. W , 7609097812034 Mhlanga. J, 7809124123456 De Waardt. W , 7609097812034 Mhlanga. J, 7809124123456 De Waardt. W , 7609097812034 De Waardt, W, 7609097812034 Satafast 50mg Imodium 2mg Ultracet Probiflora Probiflora Degoran None None None None None None 6 rows selected. QUESTION 4 (25) a. Write a SQL statement to create a table mydoctor that has the following columns and rules. (5) Column Doctor identity Doctor name Data type & size Must be 5 characters Must be at most 15 characters Must be numeric Doctor_pay b. Write a sql script that can be used to insert into mydoctor's table by prompting the user to do so. (Substitution variables) (62 C. Insert the following rows into mydoctor table. (Do not use substitution variables) (6) 14586 54612 17896 ZuluSiphamandla Ndlovu Nomphilo Johnson Johnson 14000 15260 11520 d. Add another doctor into the mydoctor table. This doctor does not have his pay set yet. The doctor is Mrs. Van Wissel and the id is 96385. e. Remove all the doctors who are currently paid from the mydoctor table. (3) (3) f. Remove from the table all the doctor(s) left. (1) g. Remove mydoctor table from the database. (1) DATABASE STRUCTURE CONTRA PRESCODE CHAR(5) ALLERGY VARCHAR2(30) MEDICALCONDITIONS V EVENT CHARIS) NOT NULL DESCRIPTION VARCHAR2(30) PATIENTVISIT V PATIENTID VARCHAR2(13) NOT NULL COMMENTS LONG VISITDATE DATE NOT NULL DIAGNOSIS VARCHAR2(30) ALLERGYLOOKUP VALLERGY VARCHAR2(30) NOT NULL PRESCRIBED PRESCODE CHAR(S) NOT NULL DESCRIPTION VARCHAR2(30) PATIENTHISTORY V PATENTD VARCHAR2(13) NOT NULL MEDICALEVENT CHAR(5) NOT NULL DATE_OF_EVENT DATE NOT NULL DOCTOR DOCTORIO VARCHAR2(13) NOT NULL DOCTORNAME VARCHAR2(25) DOCTORCODE CHAR(T) NOT NULL PATIENTALLERGY PATENTD VARCHAR2(13) NOT NULL ALLERGY VARCHAR2(30) NOT NULL TREATMENT TREATO CHAR(5) NOT NULL TREATDESCRIPTION VARCHAR2(30) PATIENTMEDICINE U PATIENTE VARCHAR2(13) NOT NULL PRESC_DATE DATE NOT NULL PRESCODE CHAR(S) NOT NULL APPOINTMENT V PATENTO VARCHAR2(13) NOT NULL U DOCTORID VARCHAR2(13) NOT NULL APPOINTTYPE VARCHAR2(20) APPOINTDATE DATE NOT NULL CANCELLED CHAR(1) TREATCOST TREATE CHAR(5) NOT NULL STARTDATE DATE NOT NULL FEE NUMBER(6,2) COST NUMBER(6,2) PATIENTTREATMENT TREATID CHAR(S) NOT NULL PATIENTE VARCHAR2(13) NOT NULL TREATMENTDATE DATE NOT NULL DOCTORID VARCHAR2(13) PATIENT U PATIENTID VARCHAR213) NOT NULL FIRSTNAME VARCHAR2(15) LASTNAME VARCHAR2(30) RESP VARCHAR2(13) SEX CHAR(1) INITIALS VARCHAR2/5) ( MEDICALFUND V MEDFUND VARCHAR2(30) NOT NULL ADDRESS: VARCHAR2(20) ADDRESS VARCHAR2(20) POSTALCODE CHAR(4) TELNO VARCHAR2(20) E_MAIL VARCHAR2(20) PAYMENTS RESPACCOUNT VARCHAR2(13) NOT NULL V PAYDATE DATE NOT NULL METHOD VARCHAR2(10) PAYTYPE VARCHAR2(15) CHEQUENUMBER VARCHAR2(10) BANKNUMBER VARCHAR2(20) AMOUNT NUMBER(8,2) RESPACCOUNT VIONUMBER VARCHAR2(13) NOT NULL TITLE VARCHAR215) FIRSTNAME VARCHAR2(15) INITIALS VARCHAR2(3) LASTNAME VARCHAR2(25) ADDRESS: VARCHAR2(15) ADDRESS VARCHAR2(15) POSTALADDRES51 VARCHAR2(20) POSTALADDRESS2 VARCHAR2(30) POSTALCODE CHAR(4) EMPNAME VARCHAR2(30) EMPADORESS VARCHAR2/30) TELNO VARCHAR220) CELLNO VARCHAR2(15) TELNOWORK VARCHAR2(20) EMPTELNO VARCHAR2(15) Key Symbols RESPMEDFUND U RESPO VARCHAR2(13) NOT NULL MEDFUND VARCHAR2(30) NOT NULL MEDFUNDTYPE VARCHAR2(15) MEDFUNDNUMBER VARCHAR2(15) Primary key 4 Foreign Key U Unige Key