Create table INS ( INS_CODE char(5), INS_NAME char(30), INS_ADDRESS char(30), IN
ID: 3706486 • Letter: C
Question
Create table INS ( INS_CODE char(5), INS_NAME char(30), INS_ADDRESS char(30), INS_CITY char(30), INS_STATE char(2), INS_ZIP char(5), CONSTRAINT INS_PK PRIMARY KEY(INS_CODE)); CREATE TABLE PATIENTS (P_CODE CHAR(5), P_NAME CHAR(30), P_ADDRESS CHAR(30), P_CITY CHAR(30), P_STATE CHAR(2), P_ZIP CHAR(5), INS_CODE CHAR(5), CONSTRAINT PATIENTS_PK PRIMARY KEY (P_CODE), CONSTRAINT PATIENTS_FK FOREIGN KEY (INS_CODE) REFERENCES INS (INS_CODE)); CREATE TABLE MDS (MDS_CODE CHAR(5), MDS_NAME CHAR(30), MDS_ADDRESS CHAR(30), MDS_CITY CHAR(15), MDS_STATE CHAR(2), MDS_ZIP CHAR(5), CONSTRAINT MDS_PK PRIMARY KEY (MDS_CODE)); CREATE TABLE MEDICAL (MP_CODE CHAR(5), MP_DESCRIPTION CHAR(30), MP_PRICE DECIMAL(3,2), CONSTRAINT MEDICAL_PK PRIMARY KEY(MP_CODE)); CREATE TABLE TREATMENTS (P_CODE CHAR(5), MD_CODE CHAR(5), MP_CODE CHAR(5), DOT DATE, CONSTRAINT TREATMENT_FK FOREIGN KEY (P_CODE) REFERENCES PATIENTS (P_CODE), CONSTRAINT TREATMENT_FK1 FOREIGN KEY (MD_CODE) REFERENCES MDS(MDS_CODE), CONSTRAINT TREATMENT_FK2 FOREIGN KEY (MP_CODE) REFERENCES MEDICAL (MP_CODE));
Part I
1. Create a Table called INS containing the following:
Field name
Field properties
INS code
5
Insurance name
30
Address
30
City
30
State
2
Zipcode
5
Add the following records to the table:
Code
description
GHI
Group Health Insurance
BC
Blue Cross
AHS
American Health System
2. Create a Table called Patients containing information regarding patients in a Hospital:
Field Name
Field Property
Code
Character 5
Name
Character 30
Ins company Code
Character 5
Must be from INS table
Address line 1
Character 30
Address line 2
Character 30
City
Character 15
State
Character 2
Zip
Character 5
Add the following records to the table:
Code
Name:
Ins
P1
Frank (your last name)
GHI
P2
Rosi (your last name)
BC
P3
Alex (your last name)
BC
P4
Albert (your last name)
BC
P5
Sue (your last name)
GHI
P6
Richard (your last name)
AHS
3. Create a Table called MDS containing information regarding doctors and dentists:
Field Name
Field Property
Code
Character 5
Name
Character 30
Address
Character 30
City
Character 15
State
Character 2
Zip
Character 5
Add the following records to the table:
Code
Name
Address
(make up the rest of the data)
M1
Dr. Anthony (your last name)
M2
Dr. Jack (your last name)
M3
Dr. Jane (your last name)
M4
Dr. Louis (your last name)
M5
Dr. Kate (your last name)
4. Create a Table called Medical containing the following:
Field name
Field properties
Mp code
5
description
30
Price
Numeric with 2 decimal places
Add the following records to the table:
Code
description
price
1111
Throat culture
1.11
2222
Routine blood test
2.22
3333
Ingrown toenail removal
3.33
4444
Fill a cavity
4.44
5555
Dental x-ray
5.55
6666
Dental cleaning
6.66
7777
Nose bleed treatment
7.77
8888
Splinter removal
8.88
5. Create a Table called Treatments containing information regarding treatments received by patients.
Field name
Field properties
Patient code
5
Md code
5
Mp code
5
Date of treatment
date
6. You cannot have the same treatment twice in a given date for a specific patient
7. A patient can have the same medical procedure on different days.
8. There are 6 patients. The Patient codes are: P1, P2, P3, P4, P5, P6
9. There are 5 Doctors. The Doctor codes are: M1, M2, M3, M4, M5
For Patient P1
Add 2 treatments from one MD
For Patient P2
Add 5 treatments from 2 different MDs
For Patient P3
Add no treatments
For Patient P4
Add 4 treatments for one MD. Two of the treatments must be the same But on different days
For Patient P5
Add 5 treatments from 3 different MDs
For Patient P6
Add 1 treatment
Part II
Create a Query for each of the following questions and print out the result.
Query 1.- Print all INS records for State = “NY”
Query 2.- Print the cpt (Mp_code) record that has the highest price
Query 3.-Print the name of the patient that has the most treatments (# of records in the treatments file) Query 4.- Print the name of the patient that cost the most money (i.e. sum of all treatments costs was highest)
Query 5.- Print the name of the patient that did not have treatment 11111
Query 6.- Print the name of the INS company that had to pay the most money.
Query 7.- Print the name of the MD that prescribed the treatments that cost the most money.
Part III - Extra Credit ( 5 points – Web pages connected to the database)
1. Generate the following report for all patients –one report per patient.
Name of the patient
Address of the patient
Date of service name of md cpt cpt description price
… … … … …
… … … … …
Total …
2. Generate the following report for all MDs –one report per MD
Name of MD
Address of MD
Date of service name of patient cpt cpt-description
price
… … … …
…
… … … …
…
Total
This report should be in DATE order- subtotaling after each date.
3. Generate the following report for all CPTs – one report per cpt
CPT code and description
…
Date of service name of patient name of MD
Price
… … …
…
… … …
…
Total
…
This report should be in md order –subtotaling after each md.
4. Generate the following report for all INSURANCE companies – one report per INS
Insurance Company name
Insurance Company address
Date of service name of patient name of MD cpt cpt description price
… … … … … …
… … … … … …
Field name
Field properties
INS code
5
Insurance name
30
Address
30
City
30
State
2
Zipcode
5
Explanation / Answer
PArt 1:
insert into INS( INS_CODE,INS_NAME)
values ('GHI', 'Group Health Insurance');
insert into INS( INS_CODE,INS_NAME)
values ('BC', 'Blue Cross');
insert into INS( INS_CODE,INS_NAME)
values ('AHS', 'American Health System');
Part 2:
insert into patients(P_CODE,P_NAME,INS_CODE )
values ('P1','Frank Chhimpa', 'GHI')
insert into patients(P_CODE,P_NAME,INS_CODE )
values ('P2','Rosi Chhimpa', 'BC')
insert into patients(P_CODE,P_NAME,INS_CODE )
values ('P3','Alex Chhimpa', 'BC')
insert into patients(P_CODE,P_NAME,INS_CODE )
values ('P4','Albert Chhimpa', 'BC')
insert into patients(P_CODE,P_NAME,INS_CODE )
values ('P5','Sue Chhimpa', 'GHI')
insert into patients(P_CODE,P_NAME,INS_CODE )
values ('P6','Richard Chhimpa', 'AHS')
Part 3:
CREATE TABLE MDS
(MDS_CODE CHAR(5),
MDS_NAME CHAR(30),
MDS_ADDRESS CHAR(30),
MDS_CITY CHAR(15),
MDS_STATE CHAR(2),
MDS_ZIP CHAR(5),
CONSTRAINT MDS_PK PRIMARY KEY (MDS_CODE));
insert into MDS(MDS_CODE,MDS_NAME,MDS_ADDRESS ,MDS_CITY ,MDS_STATE,MDS_ZIP)
values ('M1', 'Dr. Anthony Chhimpa', 'Pune','Pune','Mah','443223')
insert into MDS(MDS_CODE,MDS_NAME,MDS_ADDRESS ,MDS_CITY ,MDS_STATE,MDS_ZIP)
values ('M2', 'Dr. Jack Chhimpa', 'Pune','Pune','Mah','443223')
insert into MDS(MDS_CODE,MDS_NAME,MDS_ADDRESS ,MDS_CITY ,MDS_STATE,MDS_ZIP)
values ('M3', 'Dr. Jane Chhimpa', 'Pune','Pune','Mah','443223')
insert into MDS(MDS_CODE,MDS_NAME,MDS_ADDRESS ,MDS_CITY ,MDS_STATE,MDS_ZIP)
values ('M4', 'Dr. Loius Chhimpa', 'Pune','Pune','Mah','443223')
insert into MDS(MDS_CODE,MDS_NAME,MDS_ADDRESS ,MDS_CITY ,MDS_STATE,MDS_ZIP)
values ('M5', 'Dr. Kate Chhimpa', 'Pune','Pune','Mah','443223')
Part 4:
CREATE TABLE MEDICAL
(MP_CODE CHAR(5),
MP_DESCRIPTION CHAR(30),
MP_PRICE DECIMAL(3,2),
CONSTRAINT MEDICAL_PK PRIMARY KEY(MP_CODE));
insert into MEDICAL(MP_CODE,MP_DESCRIPTION,MP_PRICE)
values ('1111', 'Throat culture',1.11)
insert into MEDICAL(MP_CODE,MP_DESCRIPTION,MP_PRICE)
values ('3333', 'Ingrown toenail removal',3.33)
insert into MEDICAL(MP_CODE,MP_DESCRIPTION,MP_PRICE)
values ('4444', 'Fill a cavity,4.44)
insert into MEDICAL(MP_CODE,MP_DESCRIPTION,MP_PRICE)
values ('5555', 'Dental x-ray',5.55)
insert into MEDICAL(MP_CODE,MP_DESCRIPTION,MP_PRICE)
values ('6666', 'Dental cleaning',6.66)
insert into MEDICAL(MP_CODE,MP_DESCRIPTION,MP_PRICE)
values ('7777', 'Nose bleed treatment',7.77)
insert into MEDICAL(MP_CODE,MP_DESCRIPTION,MP_PRICE)
values ('8888', 'Splinter removal',8.88)
Part 5:
CREATE TABLE TREATMENTS
(P_CODE CHAR(5),
MD_CODE CHAR(5),
MP_CODE CHAR(5),
DOT DATE,
CONSTRAINT TREATMENT_FK FOREIGN KEY (P_CODE) REFERENCES PATIENTS (P_CODE),
CONSTRAINT TREATMENT_FK1 FOREIGN KEY (MD_CODE) REFERENCES MDS(MDS_CODE),
CONSTRAINT TREATMENT_FK2 FOREIGN KEY (MP_CODE) REFERENCES MEDICAL (MP_CODE));
insert into TREATMENTS(P_CODE, MD_CODE, MP_CODE,DOT)
values ('P1', 'M1', '1111','04/09/2018')
insert into TREATMENTS(P_CODE, MD_CODE, MP_CODE)
values ('P1', 'M1', '4444')
insert into TREATMENTS(P_CODE, MD_CODE, MP_CODE)
values ('P2', 'M1', '1111')
insert into TREATMENTS(P_CODE, MD_CODE, MP_CODE)
values ('P2', 'M1', '2222')
insert into TREATMENTS(P_CODE, MD_CODE, MP_CODE)
values ('P2', 'M2', '5555')
insert into TREATMENTS(P_CODE, MD_CODE, MP_CODE)
values ('P2', 'M2', '7777')
insert into TREATMENTS(P_CODE, MD_CODE, MP_CODE)
values ('P2', 'M1', '4444')
insert into TREATMENTS(P_CODE, MD_CODE, MP_CODE,DOT)
values ('P4', 'M1', '1111','04/09/2018')
insert into TREATMENTS(P_CODE, MD_CODE, MP_CODE,DOT)
values ('P4', 'M1', '1111','07/09/2018')
insert into TREATMENTS(P_CODE, MD_CODE, MP_CODE,DOT)
values ('P4', 'M1', '3333','04/09/2018')
insert into TREATMENTS(P_CODE, MD_CODE, MP_CODE,DOT)
values ('P4', 'M1', '8888','04/09/2018')
insert into TREATMENTS(P_CODE, MD_CODE, MP_CODE,DOT)
values ('P5', 'M1', '1111','04/09/2018')
insert into TREATMENTS(P_CODE, MD_CODE, MP_CODE,DOT)
values ('P5', 'M2', '1111','04/09/2018')
insert into TREATMENTS(P_CODE, MD_CODE, MP_CODE,DOT)
values ('P5', 'M4', '1111','04/09/2018')
insert into TREATMENTS(P_CODE, MD_CODE, MP_CODE,DOT)
values ('P5', 'M1', '1111','04/09/2018')
insert into TREATMENTS(P_CODE, MD_CODE, MP_CODE,DOT)
values ('P5', 'M1', '1111','04/09/2018')
insert into TREATMENTS(P_CODE, MD_CODE, MP_CODE,DOT)
values ('P6', 'M1', '1111','04/09/2018')
Part II
Query 1:
Select * from INS where INS_STATE='NY';
Query 2:
Select MP_CODE from MEDICAL where MP_PRICE = (select max(MP_PRICE) from MEDICAL)
Query 3:
select P_NAME from patients
where P_CODE = (select P_CODE from TREATMENTS Group By P_CODE Order BY Count(*) DESC LIMIT 1)
Query 5:
select P_NAME
from patients
where P_CODE IN (select P_CODE from TREATMENTS where MP_CODE != '1111' Order BY P_CODE
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.