project2.sql
Project 2 SQL spool ‘c:UserssijanOneDriveDesktopdatabaseProject2_sxt.txt’ — set echo helps to control the echoing of SQL set echo on –Tables Dropped from database DROP TABLE ApptDetail_axs; DROP TABLE Patient_axs; DROP TABLE BillingType_axs; DROP TABLE InsuranceCo_axs; DROP TABLE Provider_axs; DROP TABLE ApptStatus_axs; DROP TABLE PmtStatus_axs; DROP TABLE Treatment_axs; DROP TABLE BlockCode_axs; –Part IA –Creating tables –Creating Treatment Table CREATE TABLE Treatment_axs ( TreatmentCode NUMBER(4), TreatmentName VARCHAR(15), PRIMARY KEY(TreatmentCode) ) ; –Creating PmtStatus Table CREATE TABLE PmtStatus_axs ( PmtStatus CHAR(10), PmtDesc NUMBER(4,2), PRIMARY KEY(PmtStatus) ) ; –Creating ApptStatusTable CREATE TABLE ApptStatus_axs ( ApptStatusCode VARCHAR(10), ApptStatusDesc VARCHAR(20), PRIMARY KEY(ApptStatusCode) ) ; –Creating SalesRep Table CREATE TABLE Provider_axs( ProviderID NUMBER(4), ProviderFName VARCHAR(15) NOT NULL, ProviderLName VARCHAR(15) NOT NULL, PRIMARY KEY(ProviderID) ) ; –Creating Customer Table CREATE TABLE InsuranceCo_axs ( InsCoID VARCHAR(8), InsCoName VARCHAR(15) NOT NULL, PRIMARY KEY(InsCoID) ) ; –Creating Order Table CREATE TABLE BillingType_axs ( BillingType NUMBER(4), BillingTypeDesc DATE NOT NULL, ) ; –Creating Product table CREATE TABLE Patient_axs ( PatientID VARCHAR(10), PatientFName VARCHAR(20) NOT NULL, PatientLName PatientPhone PRIMARY KEY (PatientID), ) ; –Creating Order Detail CREATE TABLE ApptDetail_axs ( ApptID NUMBER(4), TreatmentCode VARCHAR(6), PRIMARY KEY (ApptID, TreatmentCode), ) ; –PART IB –Describing all the Tables DESCRIBE Treatment_axs DESCRIBE PmtStatus_axs DESCRIBE ApptStatus_axs DESCRIBE Provider_axs DESCRIBE InsuranceCo_axs DESCRIBE BillingType_axs DESCRIBE Patient_axs DESCRIBE ApptDetail_axs –PART IIA –Inserting each rows in tables values –PART IIB –Selecting records from the tables SELECT * FROM Treatment_axs; SELECT * FROM PmtStatus_axs; SELECT * FROM ApptStatus_axs; SELECT * FROM Provider_axs; SELECT * FROM InsuranceCo_axs; SELECT * FROM BillingType_axs; SELECT * FROM Patient_axs; SELECT * FROM ApptDetail_axs; –PART III –Part IV –Listing all the tables SELECT * FROM Treatment_axs; SELECT * FROM PmtStatus_axs; SELECT * FROM ApptStatus_axs; SELECT * FROM Provider_axs; SELECT * FROM InsuranceCo_axs; SELECT * FROM BillingType_axs; SELECT * FROM Patient_axs; SELECT * FROM ApptDetail_axs; — to close the file spooling is turned off spool off — turn of the echo set echo off