DataBase Managemanet Systems

Project 2 SQL spool 'c:\Users\sijan\OneDrive\Desktop\database\Project2_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