INFO 2303 Database Programming
Assignment :
PL/SQL Practice
Note: PL/SQL can be executed in SQL*Plus or SQL Developer or Oracle Live SQL.
Write an anonymous block to retrieve the doctor’s ID and name which in charge of certain patient. Allow the user to enter the patient’s ID.
Extracted text: DROP TABLE PATIENT cascade constraints; DROP TABLE BILLING cascade constraints; DROP TABLE DOCTOR cascade constraints; DROP TABLE RENTAL cascade constraints; DROP TABLE MOVIE cascade constraints; DROP TABLE CUSTOMER cascade constraints; CREATE TABLE DOCTOR (DOC_ID DOC_NAME DATEHIRED NUMBER(3), VARCHAR2(9), DATE, NUMBER(12), VARCHAR2(20), NUMBER(3), NUMBER(3), NUMBER(5), SALPERMON AREA SUPERVISOR_ID CHGPERAPPT ANNUAL BONUS CONSTRAINT DOCTOR_DOC_ID_PK PRIMARY KEY (DOC_ID)); CREATE TABLE BILLING (PT_ID BALANCE DUEDATE PHONE ADDR CITY ST ZIP NUMBER(3), NUMBER(9), DATE, VARCHAR2(8), VARCHAR2(20), VARCHAR2(11), CHAR(2), NUMBER(5), VARCHAR2(11), PT_INS CONSTRAINT BILLING_PT_ID_PK PRIMARY KEY (PT_ID)); CREATE TABLE PATIENT (PT ID PT LNAME NUMBER(3), VARCHAR2(15), VARCHAR2(15), DATE, NUMBER(3), DATE, DATE, PT FNAME PTDOB DOC_ID NEXTAPPTDATE LASTAPPTDATE CONSTRAINT PATIENT_PT_ID_PK PRIMARY KEY (PT_ID), CONSTRAINT PATIENT_DOC_ID_FK FOREIGN KEY(DOC_ID) REFERENCES DOCTOR);
Extracted text: INSERT INTO BILLING VALUES(168, 15650, TO DATE(21- AUG-01), '833-9569', '128 W. Apple #4", 'Jonesboro', "IL', 62952', 'SIH'); INSERT INTO BILLING VALUES(331, 300, TO DATE(09-SEP- 01), '833-5587' "3434 Mulberry St., 'Anna', 'IL', '62906", "BCBS'): INSERT INTO BILLING VALUES(313, 0, TO DATE(01-JAN- 02'). '893-9987', '334 Tailgate Ln', 'COBDEN', IL', '62920', "Military'); INSERT INTO BILLING VALUES(816, 0, TO DATE(01-JAN- 02'), '833-6654'. *8814 W. Apple JONESBORO, 'IL', "62952', 'SIH); INSERT INTO BILLING VALUES(314, 100, TO DATE(31- MAR-01), '457-6658', "445 Oak St.", 'Carbondale', "IL', '62901, 'BCBS'); INSERT INTO BILLING VALUES(264, 35000, TO DATE(11- JAN-01'), 942-8065', *8898 Bighil Drive', 'HERRIN', 'IL', '62948', 'MediSupplA'): INSERT INTO BILLING VALUESs(103, 4500, TO DATE(01- JUL-01), "833-5547', '298 Murphy School Rd, 'Anna', 'IL', '62906', HealthCare'); INSERT INTO BILLING VALUES(108, 0, TO_DATE(01-JAN- 03'). '833-5542', *334 Pansie Hil Rd., JONESBORO, 'IL', '62952', HealthCare'); INSERT INTO BILLING VALUES(943, 0, TO_DATE(01-JAN- 05'), '529-9963', '456 E. Grand #14', 'Carbondale', 'IL', 62901", Military'): INSERT INTO BILLING VALUES(847, 98000, TO DATE(31- JAN-00), '549-8854', "6543 W. Parkview Ln.', 'Carbondale', 'IL', '62901', BCBS'); INSERT INTO BILLING VALUES(504, 0, TO DATE(01-JAN- 01). '549-6139', '6657 N. Allen', "Carbondale', IL', '62901', 'QualityCare'); INSERT INTO BILLING VALUES(809, 450, TO_DATE('19-JUL- 01'), '687-8852', "3345 Hwy 127 N., "Murphysboro', 'IL', 62966', QualityCare'); INSERT INTO BILLING VALUES(703, 225, TO DATE('31-AUG- 01), '529-8332', 909 N. Brown St.', "Carbondale", IL', "62901', HealthCare"): INSERT INTO BILLING VALUES(696, 79850, TO_DATE(15- JUL-01), 549-7231', "5546 W. James', 'Carbondale', IL', 62901', "BCBS'): INSERT INTO BILLING VALUES(966, 98700, TO DATE(15- JUL-01), '833-5375', *9009 Taylor Ave.", 'Anna', IL', 62906', BCBS'): INSERT INTO BILLING VALUES(267, 0, TO_DATE(01-JAN- 03'), 942-3321, '6755 US Route 148', HERRIN', 'IL', 62948', 'QualityCare'); INSERT INTO BILLING VALUES(307, 450, TO DATE(31-AUG- 01), '457-6967', "234 N. Allen', "Carbondale', 'IL', '62901', HealthCare'): INSERT INTO BILLING VALUES(719, 0, TO_DATE(01-JAN- 02), '549-7848', *867 Henderson St.', 'Carbondale', "IL', 62901', "HealthCare'); INSERT INTO BILLING VALUES(439, 500, TO DATE(31-AUG- 01), '833-5541'. "4456 N.Springer, 'Anna', 'IL', "62906", QualityCare"): INSERT INTO BILLING VALUES(315, 1500, TO DATE(14- SEP-01'), '833-6272, '404 Williford Rd., JONESBORO', 'IL', 62952', 'HealthCare'): INSERT INTO BILLING VALUES(163, 0, TO DATE(01-JAN- 02'). '833-2133', '129 Fountain St., "Anna', "IL'. 62906", HealthCare'): INSERT INTO BILLING VALUES(669, 128450, TO DATE(15- JUL-01), '833-6654', "353 Tin Bender Rd.", 'Jonesboro', "IL', '62952', 'BCBS');