Create a query that retrieves total number of allergies per patient. Your query must display patient first and last names. Your output must resemble the one below
Question 1 [05] Create a query that retrieves total number of allergies per patient. Your query must display patient first and last names. Your output must resemble the one below. FIRSTNAME LASTNAME Total allergies 1 JOHNSON PETRO WANDA LEWIS DE WAARDT DE WAARDT Question 2 [11] Create a query that displays the doctor who had most appointments with patients. Your output must resemble the one below. DOCTORNAME Number of Appointments PADIACHY KL 5 Question 3 [09] Write a query that displays the idnumber, first, last name and the address of all respaccount that received payments. The query must calculate total payment received. Your output should resemble the one below. Payments received 3806070156084 GERDA. VAN DER WATT from EMALAHLENI has received a total payment of R350.00 5106137845084 LARA, DE JAGER from MIDDELBURG has received a total payment of R1,225.00 6806035034074 INNOCENTI MDLULI from MIDDELBURG has received a total payment of R825.00 7412087432082 LUCAS... VAN WYK from MIDDELBURG has received a total payment of R1,175.00 Question 4 [18] Write SQL code that will retrieve patients whose appointment months are the same as with a patient whose patient number ends with 3456 by making use of the LIKE operator. The query must display patientid, patient names, doctor's names and appointment date as detailed in the output. Do not include the patient id ending with 3456 in your output. Your output must resemble the one below. DOCTORNAME PATIENTID Full Name APPOINTTYPE APPOINTDA VAN WYK PJ PADIACHY KL 8106070534083 DE WAARDT, JOHAN 8110102467081 LEWIS JOHNSON Full medical Full medical 05/JAN/16 17/NOV/15 DOCTOR database SQL> SELECT * 2* FROM patient PATIENTID FIRSTNAME LASTNAME RESP SEX INITIALS 7911020534084 PETRO 8106070534083 JOHAN 7609097812034 WANDA 7503305057803 PATRICIA 8110102467081 JOHNSON 54321 PATRICIA 7809124123456 JAMES 9310012356082 PRETTY 9001085607084 LUCKY 9 rows selected. DE WAARDT DE WAARDT DE WAARDT MHLANGA LEWIS LEWIS MHLANGA MDLULI MANANA 5601145053083 F M.M 5601145053083 M 5601145053083 FR 8910091656086 F E 5106137845084 FP 5106137845084 FT 7809124123456 M | 6806035034074 F 9001085607084 MR.S SQL> SELECT idnumber, title, firstname, lastname, addressi, address2 2 FROM respaccount; IDNUMBER TITLE FIRSTNAME LASTNAME ADDRESS1 ADDRESS2 12345 MR 5601145053083 MR 4909085015089 DR 5106137845084 MS 7412087432082 MR 3806070156084 MRS 7504157082085 MR 6806035034074 DR 6502213405789 MR 7412256509558 MRS 6811095768082 MS 8910091656086 MR 9001085607084 DR 7809124123456 DR 14 rows selected. JOHN WOUTER PETRUS LARA LUCAS GERDA PETRUS INNOCENT CLEVER SARAH PRECIOUS INNOCENT LUCKY JAMES LEWIS DE WAARDT MHLANGA DE JAGER VAN WYK VAN DER WATT MAHLANGU MDLULI NKOSI MDLULI MANANA MHLANGA MANANA MHLANGA 1459 CHURCH ST 223 SADC ST 788 VOS ST 25 SADC ST PRESIDENT AVE 25 FIRST ST 23 LIMPOPO AVE 76 MAIN ST MIDDELBURG MIDDELBURG MIDDELBURG MIDDELBURG EMALAHLENI MIDDELBURG MIDDELBURG MBOMBELA 34 IVY CRESCENT EMALAHLENI STAND2 DAANTJIE MBOMBELA CRYSTAL PALACE MBOMBELA 788 IVY AVE EMALAHLENI WA SQL> SELECT idnumber, postaladdressl, postaladdress2, postalcode 2 FROM respaccount; IDNUMBER POSTALADDRESS1 POSTALADDRESS2 POSTALCODE MIDDELBURG MIDDELBURG MIDDELBURG 1050 1050 1145 1050 1051 1050 MIDDELBURG 12345 5601145053083 P.O. BOX 543 4909085015089 5106137845084 P/BAG X12 7412087432082 P.O. BOX 123 3806070156084 7504157082085 P/BAG X76 6806035034074 6502213405789 7412256509558 P.O. BOX 75 6811095768082 P/BAG X56 8910091656086 9001085607084 7809124123456 P/BAG X56 14 rows selected. EMAKHAZENI EMALAHLENI 1201 1100 1053 1201 1201 1053 EMALAHLENI SQL> SELECT idnumber, empname, empaddress, telno, cellno, telnowork, emptelno 2 FROM respaccount; IDNUMBER EMPNAME EMPADDRESS TELNO CELLNO TELNOWORK EMPTELNO 013-6901911 013-2479121 013-69019111 013-2479114 013-247912 013-247911 013-2486789013-2486789 12345 5601145053083 HIGHVELD STEEL 4909085015089 COLUMBUS STAINLESS 5106137845084 HIGHVELD STEEL 7412087432082 COLUMBUS STAINLESS 3806070156084 7504157082085 BARLOWS 6806035034074 6502213405789 UNIVERSITY OF MP 7412256509558 SHOPRITE CHECKERS 6811095768082 THE RIDGE CASINO 8910091656086 RIVERSIDE CASINO 9001085607084 RIVERSIDE CASINO 7809124123456 THE RIDGE CASINO 12 rows selected. P.O.BOX 3546 EMALAHLENI 013-2456835982-6712309 P.O. BOX 2467 013-2454888 082-8912370 P.O.BOX 4213 EMALAHLENI 013-2446610 013-6905678 084-1122334 MILLI STREET MIDDELBURG 013-2546879 013-2546234 072-3455432 P/BAG 43, MBOMBELA 084-5234676 071-3626756 HIGHVELD MALL EMALAHLENI 083-4561235 RIVERSIDE MALL MBOMBELA 083-3567089 RIVERSIDE MALL MBOMBELA 087-3567089 HIGHVELD MALL EMALAHLENI 083-7651235 SQL> SELECT * 2 FROM payments; RESPACCOUNT PAYDATE METHOD PAYTYPE CHEQUENUMBER BANKNUMBER AMOUNT 5106137845084 15/DEC/15 MEDFUND 5106137845084 12/JUL/15 MEDFUND 5106137845084 12/JAN/16 MEDFUND 7412087432082 14/JAN/16 MEDFUND 7412087432082 17/JUN/15 MEDFUND 7412087432082 17/FEB/16 MEDFUND 6806035034074 14/FEB/16 MEDFUND 6806035034074 01/MAR/16 PRIVATE 6806035034074 19/AUG/15 PRIVATE 3806070156084 14/AUG/15 PRIVATE 10 rows selected. BANK BANK TRANSFER BANK TRANSFER BANK TRANSFER BANK TRANSFER BANK TRANSFER BANK TRANSFER CHEQUE CHEQUE CASH 000-346 000-346 000-346 000-213 000-213 000-213 000-213 000-213 000-213 425 300 500 450 275 450 250 250 325 350 244 275 SQL> SELECT * 2 FROM respmedfund; RESPID MEDFUND MEDFUNDTYPE MEDFUNDNUMBER TA12345 TB560114 DCP490908 DCC510613 12345 TOPMED 5601145053083 TOPMED 4909085015089 DISCOVERY 5106137845084 DISCOVERY 7412087432082 DISCOVERY 3806070156084 BESTMED 7504157082085 TOPMED 6502213405789 TOPMED 6811095768082 BONITAS 7809124123456 BONITAS 10 rows selected. PLAN A PLAN B CLASSIC PRIOR CLASSIC COMP KEYCARE PLUS PLATINUM TOP EXEC TOP CLASSIC BON STANDARD BON STANDARD TTE750415 TTC650221 BB3681109 BBS780912 SQL> SELECT * 2 FROM medicalfund; MEDFUND ADDRESS1 ADDRESS2 POSTALCODE TELNO E MAIL TOPMED DISCOVERY BESTMED BONITAS 4 rows selected. P.O.BOX 2467 PRETORIA P.O.BOX 5367 PRETORIA 236 OBERON AVE PORT ELIZABETH 876 MILNER DRIVE CAPE TOWN 0001 0001 6000 8000 012-6534747 012-5724848 041-4653345 021-3506000 topmed@mweb.com discovery@medweb.com bestmed@mweb.com bonitas@yahoo.com SQL> SELECT * 2 FROM patientmedicine; PATIENTID PRESC DATE PRESCODE 7911020534084 05/JAN/16 AMO50 7609097812034 13/JAN/16 ULTRA 7809124123456 25/FEB/16 IMOD2 7609097812034 12/FEB/16 PROBI 7809124123456 25/FEB/16 PROBI 7609097812034 13/JAN/16 CAT50 7609097812034 13/JAN/16 DEGOR 7 rows selected. SQL> SELECT * 2 FROM prescribed; PRESCODE DESCRIPTION AMO50 AMOXICICILLIN 50 MG PEN10 PENDINE 10MG PHA20 PHARMAPRESS 20MG AVA40 AVASPOR 40MG ULTRA ULTRACET DEGOR DEGORAN IMOD2 IMODIUM 2MG CAT50 CATAFAST 50MG PROBI PROBIFLORA 9 rows selected. SQL> SELECT * 2 FROM patientallergy; PATIENTID ALLERGY 7911020534084 ASPIRIN 7911020534084 BEE STING 8110102467081 PET'S HAIR 7609097812034 PET'S HAIR 7609097812034 POLLEN 7609097812034 DUST 7911020534084 PENICYLLIN 7 rows selected. SQL> SELECT * 2 FROM contra; PRESCODE ALLERGY AMO50 PENICYLLIN 1 rows selected. SQL> SELECT * FROM allergylookup; ALLERGY IODINE BEE STING ASPIRIN PENICYLLIN LACTOSE POLLEN DUST LATEX PET'S HAIR 9 rows selected. SQL> SELECT * 2 FROM medicalconditions; EVENT DESCRIPTION TONSI TONSILLECTOMY APPEN APPENDECTOMY HEPAA HEPATITISA HEPAB HEPATITIS B HEPAC HEPATITIS C CARDI CARDIOVASCULAR HYPER HYPERTENSION TUBER TUBERCULOSIS THROM THROMBOSIS ASTHM ASTHMA BRONC BRONCHITIS LEUKE LEUKEMIA 12 rows selected. SQL> SELECT * 2 FROM patienthistory; PATIENTID MEDICALEVENT DATE OF EVENT wwwwwwwwwwwww 7911020534084 TONSI 05/JAN/16 7609097812034 ASTHM 13/JAN/16 8106070534083 BRONC 05/JAN/16 8110102467081 ASTHM 12/DEC/15 7609097812034 TUBER 12/FEB/16 8110102467081 BRONC 05/MAR/16 7809124123456 LEUKE 31/JAN/16 8110102467081 CARDI 21/FEB/16 8 rows selected. SQL> SELECT * 2 FROM patienttreatment; TREATID PATIENTID TREATMENT DATE DOCTORID FMO01 7911020534084 05/JAN/16 CSTTN 8106070534083 13/JAN/16 IVN40 7911020534084 05/JAN/16 CSTTN 7609097812034 13/FEB/16 STRMV 7911020534084 05/JAN/16 CSTTN 7503305057803 13/JAN/16 6 rows selected. 5302140156083 7309185346084 5302140156083 5302140156083 5302140156083 5302140156083 SQL> SELECT * 2 FROM doctor; DOCTORID DOCTORNAME DOCTORCODE 5302140156083 GAMA CJ 7309185346084 VAN WYK PJ 6812313524082 THUKWANE TF 7101136748073 PADIACHY KL 8401015312068 CHILD P 8312123444356 MALUMANE TF 7602213456112 GUMEDE R 6812238898344 NKOSI KL 8 rows selected. 1234567 5555555 9876543 4646464 1231231 6666667 1234455 6677889 SQL> SELECT * 2 FROM treatment; TREATID TREATDESCRIPTION FM001 FULL MEDICAL CSTTN CONSULTATION IVN40 INJECTION - VOLTAREN 40 IVN20 INJECTION VOLTAREN 20 PHA20 PHARMAPRESS 20mg PEN10 PENDINE 10mg STRMV REMOVE STITCHES CTSCN PERFORM CT SCAN ECG45 PERFORM ECG TEST PHYSI PHYSIOTHERAPY MRSCN PERFORM MR SCAN STTHS INSERT STITCHES 12 rows selected. SQL> SELECT * 2 FROM treatcost; TREATID STARTDATE FEE COST IVN40 01/JUL/15 IVN40 01/JUL/16 CSTTN 01/JUL/15 FM001 01/JAN/16 STRMV 01/DEC/15 STTHS 01/JAN/16 IVN40 01/JAN/16 STRMV 01/JAN/16 PHYSI 01/JAN/16 CTSCN 01/JAN/16 MRSCN 01/JAN/16 ECG45 01/JAN/16 12 rows selected. 225 250 1800 480 150 225 300 200 350 2400 6500 900 150 175 1400 360 100 175 200 150 250 1800 4000 500 SQL> SELECT * 2 FROM appointment; PATIENTID DOCTORID APPOINTTYPE APPOINTDATE CANCELLED 8106070534083 7309185346084 Full medical 7809124123456 7309185346084 Consultation 8110102467081 7101136748073 Consultation 8110102467081 7101136748073 Full medical 7809124123456 7309185346084 Remove stitches 8110102467081 7101136748073 Blood tests 7609097812034 7309185346084 Consultation 8110102467081 7101136748073 ECG 8110102467081 7101136748073 MR scan 7809124123456 5302140156083 Consultation 9001085607084 7602213456112 Remove stitches 11 rows selected. 05/JAN/16 18/JAN/16 08/OCT/15 17/NOV/15 12/JAN/16 12/DEC/15 12/FEB/16 21/FEB/16 02/MAR/16 11/NOV/15 12/FEB/16 N N N N Y N N N N N SQL> SELECT * 2 FROM patientvisit; PATIENTID COMMENTS VISITDATE DIAGNOSIS 7911020534084 FEVER, SORE THROAT, PULSE 85, BODY ACHES AND PAINS 7609097812034 FEVER, RUNNY NOSE 8106070534083 FEVER, WET COUGH, VOMITING 7809124123456 CRAVINGS, VOMITING, TIRED 8110102467081 COUGHING, CHEST PAIN 7809124123456 BLEEDING NOSE 7609097812034 BODY ACHES AND PAINS, FEVER, NIGHT SWEAT 8110102467081 FATIQUE, CHEST PAIN, COUGHING 7809124123456 FREQUENT BLEEDING, FATIQUE, FEVER 8110102467081 CHEST PAIN, FATIQUE, NIGHT SWEAT 7809124123456 RUNNING STOMACH 11 rows selected. 05/JAN/16 FLUE 13/JAN/16 UPPER AIRWAYS INFECTION 05/JAN/16 BRONCHITUS 18/FEB/16 PREGNANT 12/DEC/15 ASTHMATIC 11/NOV/15 BLEEDING 12/FEB/16 INFLUENZA 05/MAR/16 UPPER AIRWAYS INFECTION 31/JAN/16 LEUKEMIA 21/FEB/16 POSSIBLE STROKE 25/FEB/16 DIAREE DOCTOR database CONTRA PRESCODE CHAS ALLERGY WROW VEDICAL CONDITIONS EVENT CHAPS NOT NULL DESCAPTION WACHACOM PATENTIST PATENTD WPCHAR213) NOT NULL COMMENTS LONG VISITDATE DATE NOT NULL DUGNOSE VARCHAR220 ALLERGLOOKUP ALLERGY WARCR230) NOT NULL PRESCRIBED V PRESODE IS NOT NULL DESCRIPTION VAROMAX PATENTHISTORY V PATIENTO VARCHAROS) NOT NUL V MEXCALEVENT CHAS NOT NULL V DATE_OF_EVENT DATE NOT NULL DOCTOR V DOCTORID VARCHAR(13) NOT NULL DOCTORNE VARCHARIS DOCTORCODE CHAROJ NOT NULL PATIENTALLERGY PATENTO WRCRAIS NOT NULL ALLERGY WW220NOT NULL TREATMENT V TREATO CHARIS) NOT NULL TREATIESCAPTION VARCHAR230 PATENTVEDICHE PATENTD WROW2113) NOT NULL V PRESC DATE DATE NOT NULL V PRESODE HAS NOT NULL APPOINTMENT PATENTO WRCHAR213) NOT NULL DOCTORD WACHAR213) NOT NUL APPOINTTYPE WARCHAR22 APPONTOATE DATE NOT NULL CANCELLE CHAP TREAT006 TREATO CHARS NOT NULL STARTDATE DATE NOT NULL FEE NUMBERS, COST NUMBER 5,2 PATENT PATENTO WRCHAR213) NOT NUL FESTNINE WROWAR2115 LASTIMME WRONARZO VARCHAR2/17) SEX CHAT NTIALS WROWS PATENT TREATVENT TREATO CHARIS) NOT NULL PATENTD WAACHAR213) NOT NUL TREATVENTOATE DATE NOT NULL DOCTORID VARCHAR213) VEDICALFUND VEDFUND VARCHAR230 NOT NULL ADOPESSI VARCHAR2,20 HOOPES2 VARCHAR220 POSTALCODE CHAM TELNO VARCSAP2,20 EMAL VARCHAR220 PAYMENTS VRESPACCOUNT VARCHAR2:13) NOT NULL PAYDATE DATE NOT NULL METHOD VARCHAR2:10 + PAYTYPE VARCHAR215) CHEQUEN MEER WACH10 BANON MEER VARCHAR220) AMOUNT NUDEER8,2 RESPACCOUNT V ON MEER VARCHAR NOT NULL TITLE VARCHAR25) FIRSTNAME VARCHARTS NTIALS VARCHARAT LASTME VARCHARAZS ADOPE55 WARCRATS ADOPESE2 VIPCHARATS POSTALADORE561 VOR20 POSTALATORESS2 WARCR23 POSTALOGUE CHARM EMPRAVE VARCHAR2301 EMPADORES VAROR TELNO VARCHAR220 CELINO VARIAS TELNOWORK VARCIA EMPTELNO VARCHARTS Kay Stel PESVEOFUND RESPO VARCHAR213) NOT NULL VEDFUND VARCHARCU NOT NULL VEDFUNOTYPE WARCR2/19 VEDFUNONUMBER VARCHAR2/19 ve Foreply U Question 1 [05] Create a query that retrieves total number of allergies per patient. Your query must display patient first and last names. Your output must resemble the one below. FIRSTNAME LASTNAME Total allergies 1 JOHNSON PETRO WANDA LEWIS DE WAARDT DE WAARDT Question 2 [11] Create a query that displays the doctor who had most appointments with patients. Your output must resemble the one below. DOCTORNAME Number of Appointments PADIACHY KL 5 Question 3 [09] Write a query that displays the idnumber, first, last name and the address of all respaccount that received payments. The query must calculate total payment received. Your output should resemble the one below. Payments received 3806070156084 GERDA. VAN DER WATT from EMALAHLENI has received a total payment of R350.00 5106137845084 LARA, DE JAGER from MIDDELBURG has received a total payment of R1,225.00 6806035034074 INNOCENTI MDLULI from MIDDELBURG has received a total payment of R825.00 7412087432082 LUCAS... VAN WYK from MIDDELBURG has received a total payment of R1,175.00 Question 4 [18] Write SQL code that will retrieve patients whose appointment months are the same as with a patient whose patient number ends with 3456 by making use of the LIKE operator. The query must display patientid, patient names, doctor's names and appointment date as detailed in the output. Do not include the patient id ending with 3456 in your output. Your output must resemble the one below. DOCTORNAME PATIENTID Full Name APPOINTTYPE APPOINTDA VAN WYK PJ PADIACHY KL 8106070534083 DE WAARDT, JOHAN 8110102467081 LEWIS JOHNSON Full medical Full medical 05/JAN/16 17/NOV/15 DOCTOR database SQL> SELECT * 2* FROM patient PATIENTID FIRSTNAME LASTNAME RESP SEX INITIALS 7911020534084 PETRO 8106070534083 JOHAN 7609097812034 WANDA 7503305057803 PATRICIA 8110102467081 JOHNSON 54321 PATRICIA 7809124123456 JAMES 9310012356082 PRETTY 9001085607084 LUCKY 9 rows selected. DE WAARDT DE WAARDT DE WAARDT MHLANGA LEWIS LEWIS MHLANGA MDLULI MANANA 5601145053083 F M.M 5601145053083 M 5601145053083 FR 8910091656086 F E 5106137845084 FP 5106137845084 FT 7809124123456 M | 6806035034074 F 9001085607084 MR.S SQL> SELECT idnumber, title, firstname, lastname, addressi, address2 2 FROM respaccount; IDNUMBER TITLE FIRSTNAME LASTNAME ADDRESS1 ADDRESS2 12345 MR 5601145053083 MR 4909085015089 DR 5106137845084 MS 7412087432082 MR 3806070156084 MRS 7504157082085 MR 6806035034074 DR 6502213405789 MR 7412256509558 MRS 6811095768082 MS 8910091656086 MR 9001085607084 DR 7809124123456 DR 14 rows selected. JOHN WOUTER PETRUS LARA LUCAS GERDA PETRUS INNOCENT CLEVER SARAH PRECIOUS INNOCENT LUCKY JAMES LEWIS DE WAARDT MHLANGA DE JAGER VAN WYK VAN DER WATT MAHLANGU MDLULI NKOSI MDLULI MANANA MHLANGA MANANA MHLANGA 1459 CHURCH ST 223 SADC ST 788 VOS ST 25 SADC ST PRESIDENT AVE 25 FIRST ST 23 LIMPOPO AVE 76 MAIN ST MIDDELBURG MIDDELBURG MIDDELBURG MIDDELBURG EMALAHLENI MIDDELBURG MIDDELBURG MBOMBELA 34 IVY CRESCENT EMALAHLENI STAND2 DAANTJIE MBOMBELA CRYSTAL PALACE MBOMBELA 788 IVY AVE EMALAHLENI WA SQL> SELECT idnumber, postaladdressl, postaladdress2, postalcode 2 FROM respaccount; IDNUMBER POSTALADDRESS1 POSTALADDRESS2 POSTALCODE MIDDELBURG MIDDELBURG MIDDELBURG 1050 1050 1145 1050 1051 1050 MIDDELBURG 12345 5601145053083 P.O. BOX 543 4909085015089 5106137845084 P/BAG X12 7412087432082 P.O. BOX 123 3806070156084 7504157082085 P/BAG X76 6806035034074 6502213405789 7412256509558 P.O. BOX 75 6811095768082 P/BAG X56 8910091656086 9001085607084 7809124123456 P/BAG X56 14 rows selected. EMAKHAZENI EMALAHLENI 1201 1100 1053 1201 1201 1053 EMALAHLENI SQL> SELECT idnumber, empname, empaddress, telno, cellno, telnowork, emptelno 2 FROM respaccount; IDNUMBER EMPNAME EMPADDRESS TELNO CELLNO TELNOWORK EMPTELNO 013-6901911 013-2479121 013-69019111 013-2479114 013-247912 013-247911 013-2486789013-2486789 12345 5601145053083 HIGHVELD STEEL 4909085015089 COLUMBUS STAINLESS 5106137845084 HIGHVELD STEEL 7412087432082 COLUMBUS STAINLESS 3806070156084 7504157082085 BARLOWS 6806035034074 6502213405789 UNIVERSITY OF MP 7412256509558 SHOPRITE CHECKERS 6811095768082 THE RIDGE CASINO 8910091656086 RIVERSIDE CASINO 9001085607084 RIVERSIDE CASINO 7809124123456 THE RIDGE CASINO 12 rows selected. P.O.BOX 3546 EMALAHLENI 013-2456835982-6712309 P.O. BOX 2467 013-2454888 082-8912370 P.O.BOX 4213 EMALAHLENI 013-2446610 013-6905678 084-1122334 MILLI STREET MIDDELBURG 013-2546879 013-2546234 072-3455432 P/BAG 43, MBOMBELA 084-5234676 071-3626756 HIGHVELD MALL EMALAHLENI 083-4561235 RIVERSIDE MALL MBOMBELA 083-3567089 RIVERSIDE MALL MBOMBELA 087-3567089 HIGHVELD MALL EMALAHLENI 083-7651235 SQL> SELECT * 2 FROM payments; RESPACCOUNT PAYDATE METHOD PAYTYPE CHEQUENUMBER BANKNUMBER AMOUNT 5106137845084 15/DEC/15 MEDFUND 5106137845084 12/JUL/15 MEDFUND 5106137845084 12/JAN/16 MEDFUND 7412087432082 14/JAN/16 MEDFUND 7412087432082 17/JUN/15 MEDFUND 7412087432082 17/FEB/16 MEDFUND 6806035034074 14/FEB/16 MEDFUND 6806035034074 01/MAR/16 PRIVATE 6806035034074 19/AUG/15 PRIVATE 3806070156084 14/AUG/15 PRIVATE 10 rows selected. BANK BANK TRANSFER BANK TRANSFER BANK TRANSFER BANK TRANSFER BANK TRANSFER BANK TRANSFER CHEQUE CHEQUE CASH 000-346 000-346 000-346 000-213 000-213 000-213 000-213 000-213 000-213 425 300 500 450 275 450 250 250 325 350 244 275 SQL> SELECT * 2 FROM respmedfund; RESPID MEDFUND MEDFUNDTYPE MEDFUNDNUMBER TA12345 TB560114 DCP490908 DCC510613 12345 TOPMED 5601145053083 TOPMED 4909085015089 DISCOVERY 5106137845084 DISCOVERY 7412087432082 DISCOVERY 3806070156084 BESTMED 7504157082085 TOPMED 6502213405789 TOPMED 6811095768082 BONITAS 7809124123456 BONITAS 10 rows selected. PLAN A PLAN B CLASSIC PRIOR CLASSIC COMP KEYCARE PLUS PLATINUM TOP EXEC TOP CLASSIC BON STANDARD BON STANDARD TTE750415 TTC650221 BB3681109 BBS780912 SQL> SELECT * 2 FROM medicalfund; MEDFUND ADDRESS1 ADDRESS2 POSTALCODE TELNO E MAIL TOPMED DISCOVERY BESTMED BONITAS 4 rows selected. P.O.BOX 2467 PRETORIA P.O.BOX 5367 PRETORIA 236 OBERON AVE PORT ELIZABETH 876 MILNER DRIVE CAPE TOWN 0001 0001 6000 8000 012-6534747 012-5724848 041-4653345 021-3506000 topmed@mweb.com discovery@medweb.com bestmed@mweb.com bonitas@yahoo.com SQL> SELECT * 2 FROM patientmedicine; PATIENTID PRESC DATE PRESCODE 7911020534084 05/JAN/16 AMO50 7609097812034 13/JAN/16 ULTRA 7809124123456 25/FEB/16 IMOD2 7609097812034 12/FEB/16 PROBI 7809124123456 25/FEB/16 PROBI 7609097812034 13/JAN/16 CAT50 7609097812034 13/JAN/16 DEGOR 7 rows selected. SQL> SELECT * 2 FROM prescribed; PRESCODE DESCRIPTION AMO50 AMOXICICILLIN 50 MG PEN10 PENDINE 10MG PHA20 PHARMAPRESS 20MG AVA40 AVASPOR 40MG ULTRA ULTRACET DEGOR DEGORAN IMOD2 IMODIUM 2MG CAT50 CATAFAST 50MG PROBI PROBIFLORA 9 rows selected. SQL> SELECT * 2 FROM patientallergy; PATIENTID ALLERGY 7911020534084 ASPIRIN 7911020534084 BEE STING 8110102467081 PET'S HAIR 7609097812034 PET'S HAIR 7609097812034 POLLEN 7609097812034 DUST 7911020534084 PENICYLLIN 7 rows selected. SQL> SELECT * 2 FROM contra; PRESCODE ALLERGY AMO50 PENICYLLIN 1 rows selected. SQL> SELECT * FROM allergylookup; ALLERGY IODINE BEE STING ASPIRIN PENICYLLIN LACTOSE POLLEN DUST LATEX PET'S HAIR 9 rows selected. SQL> SELECT * 2 FROM medicalconditions; EVENT DESCRIPTION TONSI TONSILLECTOMY APPEN APPENDECTOMY HEPAA HEPATITISA HEPAB HEPATITIS B HEPAC HEPATITIS C CARDI CARDIOVASCULAR HYPER HYPERTENSION TUBER TUBERCULOSIS THROM THROMBOSIS ASTHM ASTHMA BRONC BRONCHITIS LEUKE LEUKEMIA 12 rows selected. SQL> SELECT * 2 FROM patienthistory; PATIENTID MEDICALEVENT DATE OF EVENT wwwwwwwwwwwww 7911020534084 TONSI 05/JAN/16 7609097812034 ASTHM 13/JAN/16 8106070534083 BRONC 05/JAN/16 8110102467081 ASTHM 12/DEC/15 7609097812034 TUBER 12/FEB/16 8110102467081 BRONC 05/MAR/16 7809124123456 LEUKE 31/JAN/16 8110102467081 CARDI 21/FEB/16 8 rows selected. SQL> SELECT * 2 FROM patienttreatment; TREATID PATIENTID TREATMENT DATE DOCTORID FMO01 7911020534084 05/JAN/16 CSTTN 8106070534083 13/JAN/16 IVN40 7911020534084 05/JAN/16 CSTTN 7609097812034 13/FEB/16 STRMV 7911020534084 05/JAN/16 CSTTN 7503305057803 13/JAN/16 6 rows selected. 5302140156083 7309185346084 5302140156083 5302140156083 5302140156083 5302140156083 SQL> SELECT * 2 FROM doctor; DOCTORID DOCTORNAME DOCTORCODE 5302140156083 GAMA CJ 7309185346084 VAN WYK PJ 6812313524082 THUKWANE TF 7101136748073 PADIACHY KL 8401015312068 CHILD P 8312123444356 MALUMANE TF 7602213456112 GUMEDE R 6812238898344 NKOSI KL 8 rows selected. 1234567 5555555 9876543 4646464 1231231 6666667 1234455 6677889 SQL> SELECT * 2 FROM treatment; TREATID TREATDESCRIPTION FM001 FULL MEDICAL CSTTN CONSULTATION IVN40 INJECTION - VOLTAREN 40 IVN20 INJECTION VOLTAREN 20 PHA20 PHARMAPRESS 20mg PEN10 PENDINE 10mg STRMV REMOVE STITCHES CTSCN PERFORM CT SCAN ECG45 PERFORM ECG TEST PHYSI PHYSIOTHERAPY MRSCN PERFORM MR SCAN STTHS INSERT STITCHES 12 rows selected. SQL> SELECT * 2 FROM treatcost; TREATID STARTDATE FEE COST IVN40 01/JUL/15 IVN40 01/JUL/16 CSTTN 01/JUL/15 FM001 01/JAN/16 STRMV 01/DEC/15 STTHS 01/JAN/16 IVN40 01/JAN/16 STRMV 01/JAN/16 PHYSI 01/JAN/16 CTSCN 01/JAN/16 MRSCN 01/JAN/16 ECG45 01/JAN/16 12 rows selected. 225 250 1800 480 150 225 300 200 350 2400 6500 900 150 175 1400 360 100 175 200 150 250 1800 4000 500 SQL> SELECT * 2 FROM appointment; PATIENTID DOCTORID APPOINTTYPE APPOINTDATE CANCELLED 8106070534083 7309185346084 Full medical 7809124123456 7309185346084 Consultation 8110102467081 7101136748073 Consultation 8110102467081 7101136748073 Full medical 7809124123456 7309185346084 Remove stitches 8110102467081 7101136748073 Blood tests 7609097812034 7309185346084 Consultation 8110102467081 7101136748073 ECG 8110102467081 7101136748073 MR scan 7809124123456 5302140156083 Consultation 9001085607084 7602213456112 Remove stitches 11 rows selected. 05/JAN/16 18/JAN/16 08/OCT/15 17/NOV/15 12/JAN/16 12/DEC/15 12/FEB/16 21/FEB/16 02/MAR/16 11/NOV/15 12/FEB/16 N N N N Y N N N N N SQL> SELECT * 2 FROM patientvisit; PATIENTID COMMENTS VISITDATE DIAGNOSIS 7911020534084 FEVER, SORE THROAT, PULSE 85, BODY ACHES AND PAINS 7609097812034 FEVER, RUNNY NOSE 8106070534083 FEVER, WET COUGH, VOMITING 7809124123456 CRAVINGS, VOMITING, TIRED 8110102467081 COUGHING, CHEST PAIN 7809124123456 BLEEDING NOSE 7609097812034 BODY ACHES AND PAINS, FEVER, NIGHT SWEAT 8110102467081 FATIQUE, CHEST PAIN, COUGHING 7809124123456 FREQUENT BLEEDING, FATIQUE, FEVER 8110102467081 CHEST PAIN, FATIQUE, NIGHT SWEAT 7809124123456 RUNNING STOMACH 11 rows selected. 05/JAN/16 FLUE 13/JAN/16 UPPER AIRWAYS INFECTION 05/JAN/16 BRONCHITUS 18/FEB/16 PREGNANT 12/DEC/15 ASTHMATIC 11/NOV/15 BLEEDING 12/FEB/16 INFLUENZA 05/MAR/16 UPPER AIRWAYS INFECTION 31/JAN/16 LEUKEMIA 21/FEB/16 POSSIBLE STROKE 25/FEB/16 DIAREE DOCTOR database CONTRA PRESCODE CHAS ALLERGY WROW VEDICAL CONDITIONS EVENT CHAPS NOT NULL DESCAPTION WACHACOM PATENTIST PATENTD WPCHAR213) NOT NULL COMMENTS LONG VISITDATE DATE NOT NULL DUGNOSE VARCHAR220 ALLERGLOOKUP ALLERGY WARCR230) NOT NULL PRESCRIBED V PRESODE IS NOT NULL DESCRIPTION VAROMAX PATENTHISTORY V PATIENTO VARCHAROS) NOT NUL V MEXCALEVENT CHAS NOT NULL V DATE_OF_EVENT DATE NOT NULL DOCTOR V DOCTORID VARCHAR(13) NOT NULL DOCTORNE VARCHARIS DOCTORCODE CHAROJ NOT NULL PATIENTALLERGY PATENTO WRCRAIS NOT NULL ALLERGY WW220NOT NULL TREATMENT V TREATO CHARIS) NOT NULL TREATIESCAPTION VARCHAR230 PATENTVEDICHE PATENTD WROW2113) NOT NULL V PRESC DATE DATE NOT NULL V PRESODE HAS NOT NULL APPOINTMENT PATENTO WRCHAR213) NOT NULL DOCTORD WACHAR213) NOT NUL APPOINTTYPE WARCHAR22 APPONTOATE DATE NOT NULL CANCELLE CHAP TREAT006 TREATO CHARS NOT NULL STARTDATE DATE NOT NULL FEE NUMBERS, COST NUMBER 5,2 PATENT PATENTO WRCHAR213) NOT NUL FESTNINE WROWAR2115 LASTIMME WRONARZO VARCHAR2/17) SEX CHAT NTIALS WROWS PATENT TREATVENT TREATO CHARIS) NOT NULL PATENTD WAACHAR213) NOT NUL TREATVENTOATE DATE NOT NULL DOCTORID VARCHAR213) VEDICALFUND VEDFUND VARCHAR230 NOT NULL ADOPESSI VARCHAR2,20 HOOPES2 VARCHAR220 POSTALCODE CHAM TELNO VARCSAP2,20 EMAL VARCHAR220 PAYMENTS VRESPACCOUNT VARCHAR2:13) NOT NULL PAYDATE DATE NOT NULL METHOD VARCHAR2:10 + PAYTYPE VARCHAR215) CHEQUEN MEER WACH10 BANON MEER VARCHAR220) AMOUNT NUDEER8,2 RESPACCOUNT V ON MEER VARCHAR NOT NULL TITLE VARCHAR25) FIRSTNAME VARCHARTS NTIALS VARCHARAT LASTME VARCHARAZS ADOPE55 WARCRATS ADOPESE2 VIPCHARATS POSTALADORE561 VOR20 POSTALATORESS2 WARCR23 POSTALOGUE CHARM EMPRAVE VARCHAR2301 EMPADORES VAROR TELNO VARCHAR220 CELINO VARIAS TELNOWORK VARCIA EMPTELNO VARCHARTS Kay Stel PESVEOFUND RESPO VARCHAR213) NOT NULL VEDFUND VARCHARCU NOT NULL VEDFUNOTYPE WARCR2/19 VEDFUNONUMBER VARCHAR2/19 ve Foreply U
