INFO 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 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.
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'));
Extracted text: INSERT INTO patient VALUES(168, James', "Paul, TO DATE(14-MAR-97), 432, TO DATE(01-JUL-03). TO DATE(01-JUN-037): 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-0CT-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', DATE(12-JUL-57), 235, TO DATE(02-FEB-04"), TO DATE(O2-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(O6-JUN-03): INSERT INTO patient VALUES(943, Crow. Lewis", 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-0CT-43'), 235, TO DATE(21-JUL-03). TO DATE(10-NOV-02)): INSERT INTO patient VALUES(809, Kowalczyk', Paur. 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, Moginnis'. 'Allen', TO DATE(03-MAY-59), 504, TO DATE(). TO_DATÉ(15-JUN-03'): INSERT INTO patient VALUES(669, 'Sakic', Joe', TO DATE(16-SEP-76'), 504, TO DATE("), TO_DATE(15-JUN-03'):
Extracted text: CREATE TABLE PATIENT (PT_ID PT LNAME PT_FNAME PTDOB NUMBER(3), VARCHAR2(15), VARCHAR2(15), DATE, NUMBER(3), DATE, DATE, DOC_ID NEXTAPPTDATE LASTAPPTDATE CONSTRAINT PATIENT_PT_ID_PK PRIMARY KEY (PT_ID), CONSTRAINT PATIENT_DOC_ID_FK FOREIGN KEY(DOC_ID) REFERENCES DOCTOR);