SQL HW

DROP DATABASE clinic;

CREATE DATABASE clinic;

USE clinic;

CREATE TABLE InsurancePolicy (
company			VARCHAR(25),
policyNo		INT(6),
insuredName		VARCHAR(25),
policyType		VARCHAR(10),
medicalCopay	INT(3),
labCopay		INT(3),
pharmacyCopay	INT(3),
startDate		DATE,
endingDate		DATE,
PRIMARY KEY (company, policyNo));

CREATE TABLE Patient (
patientNo				INT(6),
patientName				VARCHAR(25),
address					VARCHAR(50),
phone					VARCHAR(10),
dateOfBirth				DATE,
sex						CHAR(1),
insuranceCo				VARCHAR(25),
policyNo				INT(6),
relationshipToInsured	VARCHAR(10),
PRIMARY KEY (patientNo),
FOREIGN KEY (insuranceCo, policyNo) REFERENCES InsurancePolicy(company, policyNo));

CREATE TABLE Staff (
staffNo		INT(6),
staffName	VARCHAR(25),
title		VARCHAR(15),
specialty	VARCHAR(15),
address		VARCHAR(50),
phone		VARCHAR(10),
PRIMARY KEY (staffNo));

CREATE TABLE Room (
roomNo			INT(2),
roomType		VARCHAR(15),
roomCondition	VARCHAR(10),
PRIMARY KEY (roomNo));

CREATE TABLE Visit (
visitNo		INT(6),
patientNo	INT(6),
visitDate	DATE,
visitTime	VARCHAR(8),
duration	VARCHAR(10),
reason		VARCHAR(20),
staffNo		INT(6),
roomNo		INT(2),
visitType	VARCHAR(25),
visitCost	DECIMAL(6, 2),
PRIMARY KEY (visitNo),
FOREIGN KEY (patientNo) REFERENCES Patient(patientNo),
FOREIGN KEY (staffNo) REFERENCES Staff(staffNo),
FOREIGN KEY (roomNo) REFERENCES Room(roomNo));

CREATE TABLE Appointment (
patientNo	INT(6),
apptDate	DATE,
apptTime	VARCHAR(8),
reason		VARCHAR(50),
staffNo		INT(6),
visitNo		INT(6),
PRIMARY KEY(patientNo, apptDate, apptTime),
FOREIGN KEY(staffNo) REFERENCES Staff(staffNo),
FOREIGN KEY(visitNo) REFERENCES Visit(visitNo));

CREATE TABLE Availability (
staffNo		INT(6),
dateAvail	date,
startTime	VARCHAR(8),
endTime		VARCHAR(8),
PRIMARY KEY(staffNo, dateAvail),
FOREIGN KEY(staffNo) REFERENCES Staff(staffNo));

CREATE TABLE DiagnosisMenu (
diagCode	INT(3),
diagName	VARCHAR(30),
PRIMARY KEY(diagCode));

CREATE TABLE ProcedureMenu (
procCode	INT(3),
procName	VARCHAR(15),
cost		DECIMAL(7,2),
PRIMARY KEY(procCode));


CREATE TABLE PrescriptionScript (
scriptNo			INT(6),
visitNo				INT(6),
dateWritten			DATE,
itemPrescribed		VARCHAR(20),
quanityPrescribed	INT(3),
directions			VARCHAR(20),
numberRefills		INT(2),
PRIMARY KEY(scriptNo),
FOREIGN KEY(visitNo) REFERENCES Visit(visitNo));

CREATE TABLE LabTest (
testNo			INT(6),
prescriptionNo	INT(6),
testType		VARCHAR(20),
testDate		date,
testTime		VARCHAR(8),
cost			DECIMAL(6,2),
result			VARCHAR(20),
PRIMARY KEY(testNo),
FOREIGN KEY (prescriptionNo) REFERENCES PrescriptionScript(scriptNo));

CREATE TABLE PrescriptionMedication (
RXNo				INT(6),
scriptNo			INT(6),
drugDispensed		VARCHAR(20),
dateDispensed		DATE,
quantityDispensed	INT(3),
refillsRemaining	INT(2),
cost				DECIMAL(6,2),
PRIMARY KEY(RXNo),
FOREIGN KEY(scriptNo) REFERENCES PrescriptionScript(scriptNo));

CREATE TABLE ProcedurePerformed (
visitNo		INT(6),
procCode	INT(3),
result		VARCHAR(20),
PRIMARY KEY(visitNo, procCode),
FOREIGN KEY (visitNo) REFERENCES Visit(visitNo),
FOREIGN KEY(procCode) REFERENCES ProcedureMenu(procCode));

CREATE TABLE Diagnosis (
visitNo		INT(6),
diagCode	INT(3),
dateOnset	DATE,
symptoms	VARCHAR(50),
severity	VARCHAR(10),
prognosis	VARCHAR(10),
PRIMARY KEY(visitNo, diagCode),
FOREIGN KEY (visitNo) REFERENCES Visit(visitNo),
FOREIGN KEY(diagCode) REFERENCES DiagnosisMenu(diagCode));

CREATE TABLE Referral (
refNo		INT(6),
visitNo		INT(6),
refTo		VARCHAR(20),
reason		VARCHAR(50),
PRIMARY KEY(refNo),
FOREIGN KEY(visitNo) REFERENCES Visit(visitNo));

CREATE TABLE Bill (
invoiceNo	INT(6),
patientNo	INT(6),
billDate	DATE,
totalAmount	DECIMAL(7,2),
dueDate		DATE,
amountPaid	DECIMAL(7,2),
PRIMARY KEY(invoiceNo),
FOREIGN KEY(patientNo) REFERENCES Patient(patientNo));

CREATE TABLE Charge (
invoiceNo		INT(6),
serviceType		VARCHAR(25),
serviceDate		DATE,
amountCharged	DECIMAL(7,2),
PRIMARY KEY(invoiceNo, serviceType, serviceDate));

CREATE TABLE Payment (
invoiceNo			INT(6),
datePaid			date,
amount				DECIMAL(7,2),
patientPayer		INT(6),
insuranceCompany	VARCHAR(25),
policyNo			INT(6),
PRIMARY KEY(invoiceNo, datePaid),
FOREIGN KEY(invoiceNo) REFERENCES Bill(invoiceNo),
FOREIGN KEY(patientPayer) REFERENCES Patient(patientNo),
FOREIGN KEY(insuranceCompany, policyNo) REFERENCES InsurancePolicy(company, policyNo));

-- Insert some records

INSERT INTO InsurancePolicy VALUES('GHI',123456,'Frances Hughes', 'PPO',15.00, 10.00, 15.00,'2015-01-01',null);
INSERT INTO InsurancePolicy VALUES('Empire',234567,'Irene Jacobs', 'HMO',15.00, 10.00, 15.00,'2015-01-01',null);
INSERT INTO InsurancePolicy VALUES('Government',345678,'Winston Lee', 'well care',0, 0, 0,'2015-01-01',null);
INSERT INTO InsurancePolicy VALUES('Health One',456789,'Samantha Babson', 'PPO',10.00, 10.00, 15.00,'2015-01-01','2015-12-31');

INSERT INTO Patient VALUES(111111, 'Frances Hughes','10 Pacific Avenue,San Diego, CA, 92101-6666','6193216789','1990-01-15','F','GHI',123456,'self');
INSERT INTO Patient VALUES(111112, 'Irene Jacobs','1 Windswept Place Chicago, IL 60601-5555','3121239876','1980-04-28','F','Empire',234567,'self');
INSERT INTO Patient VALUES(111113, 'Winston Lee','22 Amazon Street, New York,NY, 10101','2127659876','1985-09-20','M','Government',345678, 'self');
INSERT INTO Patient VALUES(111114, 'Samantha Babson','20 Liffey Avenue, Chicago, IL, 60601','3127654321','1988-03-15','F','Health One',456789,'self');
INSERT INTO Patient VALUES(111115, 'Tara Hughes','10 Pacific Avenue,San Diego, CA, 92101-6666','6193216789','2015-01-30','F','GHI',123456,'child');
INSERT INTO Patient VALUES(111116, 'Thomas Jacobs','1 Windswept Place, Chicago, IL 60601-5555','3121239876','2000-04-01','M','Empire',234567,'child');
INSERT INTO Patient VALUES(111117, 'Valerie King','22 Amazon Street, New York,NY, 10101','2127659876','1985-03-18','F','Government',345678, 'spouse');
INSERT INTO Patient VALUES(111118, 'James Babson','20 Liffey Avenue, Chicago, IL, 60601','3127654321','1985-02-16','M','Health One',456789,'spouse');

INSERT INTO Staff VALUES(123456,'John Smith','Dr.','surgery','10 Sapphire Row, New Rochelle, NY, 10801','9171233333');
INSERT INTO Staff VALUES(987654,'Terrence DeSimone','Dr.','pediatrics','10 Emerald Lane, New York, NY, 10101','2127676767');
INSERT INTO Staff VALUES(234567,'Tracy Williams','Dr.','surgery','25 Ruby Place, New York, NY, 10101','2121231234');
INSERT INTO Staff VALUES(876543,'Brenda Davis','RN','family med','11 Pearl Street, New Rochelle, NY, 10801','9171231234');
INSERT INTO Staff VALUES(345678,'Georgia Griffin','midwife','obstetrics','21 Garnet Street, New Rochelle, NY, 10801','9173334444');

INSERT INTO Room VALUES(1, 'examining', 'excellent');
INSERT INTO Room VALUES(2, 'consultation', 'excellent');
INSERT INTO Room VALUES(3, 'examining', 'excellent');
INSERT INTO Room VALUES(4, 'operating', 'excellent');
INSERT INTO Room VALUES(5, 'delivery', 'excellent');
INSERT INTO Room VALUES(6, 'examining', 'good');


INSERT INTO Visit VALUES(112233, 111111, '2015-05-15','10:30 AM', '15 min', 'URI', 123456, 1, 'office visit',75.00);
INSERT INTO Visit VALUES(223344, 111112, '2015-05-15','10:45 AM', '15 min', 'backache', 123456, 1, 'office visit', 75.00);
INSERT INTO Visit VALUES(334455, 111112, '2015-05-20','9:15 AM', '15 min', 'knee injury', 123456, 3, 'emergency visit', 100.00);
INSERT INTO Visit VALUES(445566, 111117, '2015-05-20','9:15 AM', '15 min', 'prenatal checkup', 345678, 3, 'office visit', 75.00);
INSERT INTO Visit VALUES(556677, 111115, '2015-05-21','9:15 AM', '15 min', 'MMP immunization', 876543, 2, 'office visit', 75.00);
INSERT INTO Visit VALUES(667788, 111113, '2015-05-22','9:15 AM', '45 min', 'mole removal', 123456, 4,'surgical visit', 150.00);

INSERT INTO Appointment VALUES(111111, '2015-05-15','10:30 AM','fever', 123456,112233);
INSERT INTO Appointment VALUES(111112, '2015-05-15','10:45 AM','back pain', 876543,223344);
INSERT INTO Appointment VALUES(111113, '2015-05-22','9:15 AM','mole', 123456,667788);
INSERT INTO Appointment VALUES(111115, '2015-05-21','9:15 AM','well baby care', 876543,556677);
INSERT INTO Appointment VALUES(111117, '2015-05-20','9:15 AM','prenatal care', 345678,445566);
INSERT INTO Appointment VALUES(111111, '2015-05-25','11:30 AM','checkup', 123456,null);

INSERT INTO Availability VALUES(123456, '2015-12-01', '9:00 AM','9:00 PM');
INSERT INTO Availability VALUES(123456, '2015-12-02', '9:00 AM','6:00 PM');
INSERT INTO Availability VALUES(123456, '2015-12-03', '12:00 PM','9:00 PM');
INSERT INTO Availability VALUES(123456, '2015-12-04', '9:00 AM','9:00 PM');
INSERT INTO Availability VALUES(123456, '2015-12-05', '9:00 AM','6:00 PM');
INSERT INTO Availability VALUES(123456, '2015-12-06', '12:00 PM','6:00 PM');
INSERT INTO Availability VALUES(123456, '2015-12-07', '9:00 AM','9:00 PM');
INSERT INTO Availability VALUES(987654, '2015-12-01', '9:00 AM','6:00 PM');
INSERT INTO Availability VALUES(987654, '2015-12-02', '9:00 AM','9:00 PM');
INSERT INTO Availability VALUES(987654, '2015-12-03', '10:00 AM','6:00 PM');
INSERT INTO Availability VALUES(987654, '2015-12-04', '9:00 AM','6:00 PM');
INSERT INTO Availability VALUES(987654, '2015-12-05', '9:00 AM','9:00 PM');
INSERT INTO Availability VALUES(987654, '2015-12-06', '10:00 AM','9:00 PM');
INSERT INTO Availability VALUES(987654, '2015-12-07', '12:00 PM','9:00 PM');

INSERT INTO DiagnosisMenu VALUES(010,'torn meniscus');
INSERT INTO DiagnosisMenu VALUES(011,'pregnancy-normal');
INSERT INTO DiagnosisMenu VALUES(015,'upper resp infection');
INSERT INTO DiagnosisMenu VALUES(029, 'immunization');
INSERT INTO DiagnosisMenu VALUES(030, 'benign growth');
INSERT INTO DiagnosisMenu VALUES(040, 'degenerative disk');

INSERT INTO ProcedureMenu VALUES(001, 'throat swabbing', 25.00);
INSERT INTO ProcedureMenu VALUES(002, 'venipuncture', 15.00);
INSERT INTO ProcedureMenu VALUES(003, 'mole excision', 125.00);
INSERT INTO ProcedureMenu VALUES(004, 'wound cleaning', 25.00);
INSERT INTO ProcedureMenu VALUES(005, 'normal delivery', 600.00);


INSERT INTO PrescriptionScript VALUES(555551, 112233,'2015-05-15','amoxicillin',20,'twice daily',1);
INSERT INTO PrescriptionScript VALUES(555552, 667788,'2015-05-22','tetracycline',5,'once daily',0);
INSERT INTO PrescriptionScript VALUES(555553, 334455,'2015-05-20','Lipitor',60,'once daily',5);
INSERT INTO PrescriptionScript VALUES(555554, 112233,'2015-05-15', 'strep throat culture',null, null, null);
INSERT INTO PrescriptionScript VALUES(555555, 334455,'2015-05-20', 'complete blood count',null, null, null);
INSERT INTO PrescriptionScript VALUES(555556, 223344,'2015-05-15', 'X-RAY-hip',null, null, null);

INSERT INTO LabTest VALUES(000001,555554,'strep throat culture','2015-05-15','11:30 AM',10.00,'positive');
INSERT INTO LabTest VALUES(000002,555555,'complete blood count','2015-05-21','11:30 AM',15.00,'normal');
INSERT INTO LabTest VALUES(000003,555556,'X-RAY-hip','2015-05-16','11:30 AM',200.00,'normal');

INSERT INTO PrescriptionMedication VALUES(111111, 555551, 'tetracycline', '2015-05-15', 10, 1,20.00);
INSERT INTO PrescriptionMedication VALUES(111112, 555552, 'tetracycline', '2015-05-22', 5, 0,20.00);
INSERT INTO PrescriptionMedication VALUES(111113, 555553, 'Lipitor', '2015-05-21', 100, 2,50.00);

INSERT INTO ProcedurePerformed VALUES(112233, 001,'positive');
INSERT INTO ProcedurePerformed VALUES(667788, 003,'normal');
INSERT INTO ProcedurePerformed VALUES(334455, 004,'normal');

INSERT INTO Diagnosis VALUES(112233, 015, '2015-05-12','sore throat, fever', 'severe','full recvy');
INSERT INTO Diagnosis VALUES(223344, 040, '2015-01-15','pain, stiffness', 'severe','poor');
INSERT INTO Diagnosis VALUES(334455, 010, '2015-05-19','pain, swelling', 'severe','guarded');
INSERT INTO Diagnosis VALUES(445566, 011, '2015-12-20',null, null,'excellent');
INSERT INTO Diagnosis VALUES(556677, 029, null,null, null,'excellent');
INSERT INTO Diagnosis VALUES(667788, 030, '2015-04-20',null, null,'excellent');

INSERT INTO Referral VALUES (123321,334455, 'Surgical Center', 'knee surgery');
INSERT INTO Referral VALUES (124421,223344, 'Ortho Center', 'physical therapy');

INSERT INTO Bill VALUES(123123,111111, '2015-06-01',160.00,'2015-07-01',0);
INSERT INTO Bill VALUES(123124,111112, '2015-06-01',395.00,'2015-07-01',0);
INSERT INTO Bill VALUES(123125,111113, '2015-06-01',207,'2015-07-01',0);
INSERT INTO Bill VALUES(123127,111115, '2015-06-01',160.00,'2015-07-01',0);
INSERT INTO Bill VALUES(123129,111117, '2015-06-01',160.00,'2015-07-01',0);

INSERT INTO Charge VALUES(123123, 'office visit', '2015-05-15',75.00);
INSERT INTO Charge VALUES(123123, 'missed appt', '2015-05-25',50.00);
INSERT INTO Charge VALUES(123123, 'amoxicillin', '2015-05-15',10.00);
INSERT INTO Charge VALUES(123123, 'throat swabbing', '2015-05-25',25.00);
INSERT INTO Charge VALUES(123123, 'strep throat culture', '2015-05-15',10.00);
INSERT INTO Charge VALUES(123124,'office visit','2015-05-15',75.00);
INSERT INTO Charge VALUES(123124, 'emergency visit','2015-05-20',100.00);
INSERT INTO Charge VALUES(123124, 'X-RAY-hip','2015-05-15', 200.00);
INSERT INTO Charge VALUES(123124, 'tetracycline', '2015-05-22',20.00);

INSERT INTO Payment VALUES(123123, '2015-06-05',60.00,null,'GHI',123456);
INSERT INTO Payment VALUES(123123, '2015-06-30',100.00,111111,null, null);
INSERT INTO Payment VALUES(123124, '2015-06-10',55.00, null, 'Empire', 234567);