SQL programming project

spool c:\SQL\project2_sxr.txt set echo on ---Drop all existing tables, since creating them again will cause error messages. DROP TABLE APPOINTMENT_sxr; DROP TABLE APPTDETAIL_sxr; DROP TABLE STATUS_sxr; DROP TABLE INSCO_sxr; DROP TABLE DOCTOR_sxr; DROP TABLE PATIENT_sxr; DROP TABLE BILLINGTYPE_sxr; DROP TABLE BLOCK_sxr; DROP TABLE REASON_sxr CASCADE CONSTRAINTS; --Part Ia. Create tables. ---No foreign keys CREATE TABLE REASON_sxr ( ApptReasonCode CHAR(8), ApptReasonDesc VARCHAR(30) NOT NULL, PRIMARY KEY (ApptReasonCode) ); CREATE TABLE BLOCK_sxr ( BlockCode VARCHAR(9), BlockDesc VARCHAR(13) NOT NULL, BlockTime VARCHAR(13) NOT NULL, PRIMARY KEY (BlockCode) ); CREATE TABLE BILLINGTYPE_sxr ( BillingType CHAR(10), BillingTypeDesc VARCHAR(15) NOT NULL, PRIMARY KEY (BillingType) ); CREATE TABLE PATIENT_sxr ( PatientID NUMBER(4), PatientFName VARCHAR(10) NOT NULL, PatientLName VARCHAR(10) NOT NULL, PatientPhone CHAR(10), PRIMARY KEY (PatientID) ); CREATE TABLE DOCTOR_sxr ( DoctorID NUMBER(2), DoctorFname VARCHAR(15) NOT NULL, DoctorLname VARCHAR(15) NOT NULL, PRIMARY KEY (DoctorID) ); CREATE TABLE INSCO_sxr ( InsCodeID NUMBER(3), InsCodeName CHAR(10) NOT NULL, PRIMARY KEY (InsCodeID) ); CREATE TABLE STATUS_sxr ( ApptStatusCode VARCHAR(2), ApptStatusDesc VARCHAR(14) NOT NULL, PRIMARY KEY (ApptStatusCode) ); ---With Foreign key CREATE TABLE APPTDETAIL_sxr ( ApptID NUMBER(3), ApptReasonCode CHAR(8), BlockCode VARCHAR(9) NOT NULL, PRIMARY KEY (ApptID,ApptReasonCode), FOREIGN KEY (ApptReasonCode) REFERENCES REASON_sxr, FOREIGN KEY (BlockCode) REFERENCES BLOCK_sxr ); CREATE TABLE APPOINTMENT_sxr ( ApptID NUMBER(3), ApptDate VARCHAR(15) NOT NULL, ApptTime VARCHAR(10) NOT NULL, PatientID NUMBER(4) NOT NULL, DoctorID NUMBER(2) NOT NULL, BillingType CHAR(10) NOT NULL, InsCodeID NUMBER(3), ApptStatusCode VARCHAR(2) NOT NULL, PRIMARY KEY (ApptID), FOREIGN KEY (PatientID) REFERENCES PATIENT_sxr, FOREIGN KEY (DoctorID) REFERENCES DOCTOR_sxr, FOREIGN KEY (BillingType) REFERENCES BILLINGTYPE_sxr, FOREIGN KEY (InsCodeID) REFERENCES INSCO_sxr, FOREIGN KEY (ApptStatusCode) REFERENCES STATUS_sxr ); --Part Ib. Describe the tables. DESCRIBE REASON_sxr ; DESCRIBE BLOCK_sxr ; DESCRIBE BILLINGTYPE_sxr ; DESCRIBE PATIENT_sxr ; DESCRIBE DOCTOR_sxr ; DESCRIBE INSCO_sxr ; DESCRIBE STATUS_sxr ; DESCRIBE APPTDETAIL_sxr ; DESCRIBE APPOINTMENT_sxr ; --Part II. Insert the data into the tables. INSERT INTO REASON_sxr VALUES ('NP', 'New Patient'); INSERT INTO REASON_sxr VALUES ('GBP','General Back Pain'); INSERT INTO REASON_sxr VALUES ('PSF','Post-Surgery Follow Up'); INSERT INTO REASON_sxr VALUES ('SR','Suture Removal'); INSERT INTO REASON_sxr VALUES ('PT','Physical Therapy'); INSERT INTO REASON_sxr VALUES ('AI','Auto Injury'); COMMIT ; INSERT INTO BLOCK_sxr VALUES ('L1', 'Level 1', '10 Minutes'); INSERT INTO BLOCK_sxr VALUES ('L2', 'Level 2', '15 Minutes'); INSERT INTO BLOCK_sxr VALUES ('L3', 'Level 3', '20 Minutes'); INSERT INTO BLOCK_sxr VALUES ('L4', 'Level 4', '30 Minutes'); COMMIT ; INSERT INTO BILLINGTYPE_sxr VALUES ('I', 'Insurance'); INSERT INTO BILLINGTYPE_sxr VALUES ('SP', 'Self Pay'); INSERT INTO BILLINGTYPE_sxr VALUES ('WC', 'Workers Comp'); COMMIT ; INSERT INTO PATIENT_sxr VALUES ('101', 'Wesley', 'Tanner', '8175551193'); INSERT INTO PATIENT_sxr VALUES ('100', 'Breanna', 'Rhodes', '2145559191'); INSERT INTO PATIENT_sxr VALUES ('15', 'Jeff', 'Miner', '4695552301'); INSERT INTO PATIENT_sxr VALUES ('77', 'Kim', 'Jackson', '8175554911'); INSERT INTO PATIENT_sxr VALUES ('119', 'Mary', 'Vaughn', '8175552334'); INSERT INTO PATIENT_sxr VALUES ('97', 'Chris', 'Mancha', '4695553440'); INSERT INTO PATIENT_sxr VALUES ('28', 'Renee', 'Walker', '2145559285'); INSERT INTO PATIENT_sxr VALUES ('105', 'Johnny', 'Redmond', '2145551084'); INSERT INTO PATIENT_sxr VALUES ('84', 'James', 'Clayton', '2145559285'); INSERT INTO PATIENT_sxr VALUES ('23', 'Shelby', 'Day', '8175551198'); COMMIT ; INSERT INTO DOCTOR_sxr VALUES ('2', 'Micheal', 'Smith'); INSERT INTO DOCTOR_sxr VALUES ('5', 'Janice', 'May'); INSERT INTO DOCTOR_sxr VALUES ('3', 'Ray', 'Schultz'); COMMIT ; INSERT INTO INSCO_sxr VALUES ('323', 'Humana'); INSERT INTO INSCO_sxr VALUES ('129', 'Blue Cross'); INSERT INTO INSCO_sxr VALUES ('210', 'State Farm'); INSERT INTO INSCO_sxr VALUES ('135', 'TriCare'); COMMIT ; INSERT INTO STATUS_sxr VALUES ('CM', 'Complete'); INSERT INTO STATUS_sxr VALUES ('CN', 'Confirmed'); INSERT INTO STATUS_sxr VALUES ('NC', 'Not Confirmed'); COMMIT ; INSERT INTO APPTDETAIL_sxr VALUES ('101', 'NP', 'L1'); INSERT INTO APPTDETAIL_sxr VALUES ('101', 'GBP', 'L2'); INSERT INTO APPTDETAIL_sxr VALUES ('102', 'PSF', 'L1'); INSERT INTO APPTDETAIL_sxr VALUES ('102', 'SR', 'L1'); INSERT INTO APPTDETAIL_sxr VALUES ('103', 'PSF', 'L1'); INSERT INTO APPTDETAIL_sxr VALUES ('104', 'PT', 'L3'); INSERT INTO APPTDETAIL_sxr VALUES ('105', 'NP', 'L1'); INSERT INTO APPTDETAIL_sxr VALUES ('105', 'AI', 'L2'); INSERT INTO APPTDETAIL_sxr VALUES ('106', 'PT', 'L4'); INSERT INTO APPTDETAIL_sxr VALUES ('107', 'PT', 'L3'); INSERT INTO APPTDETAIL_sxr VALUES ('108', 'GBP', 'L2'); INSERT INTO APPTDETAIL_sxr VALUES ('109', 'PSF', 'L1'); INSERT INTO APPTDETAIL_sxr VALUES ('110', 'PT', 'L4'); INSERT INTO APPTDETAIL_sxr VALUES ('111', 'PT', 'L4'); COMMIT ; INSERT INTO APPOINTMENT_sxr VALUES ('101', '1-SEP-2018', '900AM', '101', '2', 'I', '323', 'CM'); INSERT INTO APPOINTMENT_sxr VALUES ('102', '1-SEP-2018', '900AM', '100', '5', 'I', '129', 'CM'); INSERT INTO APPOINTMENT_sxr VALUES ('103', '1-SEP-2018', '930AM', '15', '2', 'SP', NULL, 'CM'); INSERT INTO APPOINTMENT_sxr VALUES ('104', '1-SEP-2018', '930AM', '77', '3', 'WC', '210', 'CM'); INSERT INTO APPOINTMENT_sxr VALUES ('105', '1-SEP-2018', '945AM', '119', '2', 'I', '129', 'CM'); INSERT INTO APPOINTMENT_sxr VALUES ('106', '1-SEP-2018', '1000AM', '97', '3', 'SP', NULL, 'CN'); INSERT INTO APPOINTMENT_sxr VALUES ('107', '1-SEP-2018', '1030AM', '28', '3', 'I', '129', 'CN'); INSERT INTO APPOINTMENT_sxr VALUES ('108', '1-SEP-2018', '1030AM', '105', '2', 'I', '323', 'NC'); INSERT INTO APPOINTMENT_sxr VALUES ('109', '1-SEP-2018', '1100AM', '84', '5', 'I', '135', 'CN'); INSERT INTO APPOINTMENT_sxr VALUES ('110', '1-SEP-2018', '1115AM', '84', '3', 'I', '135', 'CN'); INSERT INTO APPOINTMENT_sxr VALUES ('111', '1-SEP-2018', '100PM', '23', '3', 'I', '323', 'NC'); COMMIT ; --IIb. Retrieve (SELECT) data from all tables. SELECT * FROM REASON_sxr ; SELECT * FROM BLOCK_sxr ; SELECT * FROM BILLINGTYPE_sxr ; SELECT * FROM PATIENT_sxr ; SELECT * FROM DOCTOR_sxr ; SELECT * FROM INSCO_sxr ; SELECT * FROM STATUS_sxr ; SELECT * FROM APPTDETAIL_sxr ; SELECT * FROM APPOINTMENT_sxr ; -- Part III Make changes to tables UPDATE PATIENT_sxr SET PatientPhone = '2145551234' WHERE PatientID = '15' COMMIT; INSERT INTO PATIENT_sxr VALUES ('120', 'Amanda', 'Green', NULL); COMMIT; INSERT INTO BLOCK_sxr VALUES ('L5', 'Level 5', '45 Minutes'); COMMIT; INSERT INTO REASON_sxr VALUES ('CSW', 'Complete Spinal Workup'); COMMIT; INSERT INTO INSCO_sxr VALUES ('324', 'Cigna'); COMMIT; UPDATE APPOINTMENT_sxr SET ApptDate = '2-SEP-2018' WHERE ApptID = '109'; UPDATE APPOINTMENT_sxr SET ApptDate = '2-SEP-2018' WHERE ApptID = '110'; UPDATE APPOINTMENT_sxr SET ApptDate = '2-SEP-2018' WHERE ApptID = '111'; INSERT INTO APPOINTMENT_sxr VALUES ('112', '3-SEP-2018', '1030AM', '120', '2', 'WC', '324', 'NC'); COMMIT; INSERT INTO APPTDETAIL_sxr VALUES ('112', 'NP', 'L1'); INSERT INTO APPTDETAIL_sxr VALUES ('112', 'CSW', 'L5'); COMMIT; --IV. SELECT all data from all tables, sorting in PK order. SELECT * FROM PATIENT_sxr ORDER BY PatientID; SELECT * FROM DOCTOR_sxr ORDER BY DoctorID; SELECT * FROM INSCO_sxr ORDER BY InsCodeID; SELECT * FROM STATUS_sxr ORDER BY ApptStatusCode; SELECT * FROM BILLINGTYPE_sxr ORDER BY BillingType; SELECT * FROM APPOINTMENT_sxr ORDER BY ApptID; SELECT * FROM REASON_sxr ORDER BY ApptReasonCode; SELECT * FROM BLOCK_sxr ORDER BY BlockCode; SELECT * FROM APPTDETAIL_sxr ORDER BY ApptID, ApptReasonCode; set echo off spool off