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 PL/SQL block that will delete all patients for doctors that works in theFamily Practice Verify that patients have been deleted accordingly. Then do aselect * from patients where doc_id = (select doc_id from doctor where area = ‘Family Practice’. You should get no rows.
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 PATIENT (PT_ID PT LNAME PT FNAME PTDOB DOC ID NEXTAPPTDATE LASTAPPTDATE NUMBER(3), VARCHAR2(15), VARCHAR2(15), DATE, NUMBER(3), DATE, DATE, CONSTRAINT PATIENT_PT_ID PK PRIMARY KEY (PT_ID), CONSTRAINT PATIENT_DOC_ID_FK FOREIGN KEY(DOC_ID) REFERENCES DOCTOR);