NFO 2303 Database Programming Assignment : PL/SQL Practice Note: PL/SQL can be executed in SQL*Plus or SQL Developer or Oracle Live SQL. Question: Write an anonymous PL/SQL block that will delete all patients for doctors that works in the Family Practice Verify that patients have been deleted accordingly. Then do a select * from patients where doc_id = (select doc_id from doctor where area = ‘Family Practice’. You should get no rows. Patietn table:
INFO 2303
Assignment : PL/SQL Practice
Note: PL/SQL can be executed in SQL*Plus or SQL Developer or Oracle Live SQL.
Question:
Write an anonymous PL/SQL block that will delete all patients for doctors that works in the Family Practice Verify that patients have been deleted accordingly. Then do a select * from patients where doc_id = (select doc_id from doctor where area = ‘Family Practice’. You should get no rows.
Patietn table:
CREATE TABLE PATIENT
(PT_ID NUMBER(3),
PT_LNAME VARCHAR2(15),
PT_FNAME VARCHAR2(15),
PTDOB DATE,
DOC_ID NUMBER(3),
NEXTAPPTDATE DATE,
LASTAPPTDATE DATE,
CONSTRAINT PATIENT_PT_ID_PK PRIMARY KEY (PT_ID),
CONSTRAINT PATIENT_DOC_ID_FK FOREIGN KEY(DOC_ID)
REFERENCES DOCTOR);
INSERT INTO patient VALUES(168, 'James', 'Paul', TO_DATE('14-MAR-97'), 432, TO_DATE('01-JUL-03'), TO_DATE('01-JUN-03'));
INSERT INTO patient VALUES(331, 'Anderson', 'Brian', TO_DATE('06-MAR-48'), 235,
TO_DATE('01-JUL-03'), TO_DATE('01-JUN-03'));
INSERT INTO patient VALUES(313, 'James', 'Scott', TO_DATE('01-MAR-33'), 235, TO_DATE('20-JUL-03'), TO_DATE('20-JUN-03'));
INSERT INTO patient VALUES(816, 'Smith', 'Jason', TO_DATE('12-DEC-99'), 509, TO_DATE('15-NOV-03'), TO_DATE('15-MAY-03'));
INSERT INTO patient VALUES(314, 'Porter', 'Susan', TO_DATE('14-NOV-67'), 235, TO_DATE('01-OCT-03'), TO_DATE('01-MAR-03'));
INSERT INTO patient VALUES(315, 'Saillez', 'Debbie',
TO_DATE('09-SEP-55'), 235, TO_DATE('01-JUL-03'), TO_DATE('01-JUN-03'));
INSERT INTO patient VALUES(719, 'Rogers', 'Anthony', TO_DATE('07-DEC-41'), 504,
TO_DATE('01-NOV-03'), TO_DATE('01-JAN-03'));
INSERT INTO patient VALUES(264, 'Walters', 'Stephanie', TO_DATE('01-JAN-45'),
504, TO_DATE('12-DEC-03'), TO_DATE('12-DEC-02'));
INSERT INTO patient VALUES(267, 'Westra', 'Lynn', TO_DATE('12-JUL-57'), 235, TO_DATE('02-FEB-04'), TO_DATE('02-FEB-03'));
INSERT INTO patient VALUES(103, 'Poole', 'Jennifer', TO_DATE('13-MAY-02'), 389, TO_DATE('01-DEC-03'), TO_DATE('01-JUN-03'));
INSERT INTO patient VALUES(108, 'Baily', 'Ryan', TO_DATE('25-DEC-77'), 235, TO_DATE('06-JUN-05'), TO_DATE('06-JUN-03'));
INSERT INTO patient VALUES(943, 'Crow', 'Lewis', TO_DATE('16-OCT-49'), 235, TO_DATE('01-JUL-05'), TO_DATE('01-MAR-02'));
INSERT INTO patient VALUES(847, 'Cochran', 'John', TO_DATE('03-MAR-48'), 356, TO_DATE('02-DEC-05'), TO_DATE('01-JAN-02'));
INSERT INTO patient VALUES(163, 'Roach', 'Becky', TO_DATE('08-SEP-75'), 235, TO_DATE('01-DEC-05'), TO_DATE('01-JAN-02'));
INSERT INTO patient VALUES(504, 'Jackson', 'John', TO_DATE('14-OCT-43'), 235, TO_DATE('21-JUL-03'), TO_DATE('10-NOV-02'));
INSERT INTO patient VALUES(809, 'Kowalczyk', 'Paul', TO_DATE('12-NOV-51'), 558,
TO_DATE('29-JUL-03'), TO_DATE('19-JUN-03'));
INSERT INTO patient VALUES(703, 'Davis', 'Linda', TO_DATE('17-JUL-02'), 509, TO_DATE('21-JUL-03'), TO_DATE('22-MAY-03'));
INSERT INTO patient VALUES(307, 'Jones', 'J.C.', TO_DATE('17-JUL-02'), 509, TO_DATE('21-JUL-03'), TO_DATE('22-MAY-03'));
INSERT INTO patient VALUES(439, 'Wright', 'Chasity', TO_DATE('23-APR-73'), 235,
TO_DATE(''), TO_DATE(''));
INSERT INTO patient VALUES(696, 'Vanderchuck', 'Keith', TO_DATE('08-AUG-68'),
504, TO_DATE(''), TO_DATE('15-JUN-03'));
INSERT INTO patient VALUES(966, 'Mcginnis', 'Allen', TO_DATE('03-MAY-59'), 504,
TO_DATE(''), TO_DATE('15-JUN-03'));
INSERT INTO patient VALUES(669, 'Sakic', 'Joe', TO_DATE('16-SEP-76'), 504,
TO_DATE(''), TO_DATE('15-JUN-03'));
Step by step
Solved in 4 steps with 2 images
