Write SQL for Microsoft Access Queries Given Tables Assignment Table - Attribute
ID: 3788609 • Letter: W
Question
Write SQL for Microsoft Access Queries
Given Tables
Assignment Table - Attributes: Physician_ID, Patient_No, Role
Diagnosis Table- Attributes: Patient_No , Illness_Code
Illness Table- Attributes: Illness_Code, Illness_Desc , Department
Medication Table- Attributes: Medication_Code, Medication_Desc , On-Hand, On_Order, Unit_Cost
Patient Table - Attributes: Patient_No, Last_Name, First_Name, Gender, Age, Admit_Date, Room, Bed
Physician Table- Attributes: Physician_ID, Last_Name, First_Name, Tel_Ext, Specialty
Prescription Table- Attrubites: Pres_No, Patient_No, Medication_Code, Dosage
Contruct Queries based on the following
Produce a list of all medication codes used in the clinic, sorted alphabetically. Also, show the number of patients to whom each medication has been prescribed.
List the medication prescribed for patients who are in for ‘Cardiac Arrest’; Show patient first and last name, gender, age, room number, illness name, medication name, and dosage.
[Use join query]
Who are the doctor(s) that are attending to ‘Heat Exhaustion’? List the name of the doctor(s) and specialty.
[Write nested sub queries]
Produce a list of patients who were prescribed ‘Keflin’. The list should contain the patient name, medication name, dosage, illness description, and the doctor's name.
[Use join query]
List names (first and last) and telephone extension of doctors who have a patient that is in for ‘Work Stress’.
[Write nested sub queries]
List the names of doctors who have a patient that is NOT IN for ‘Compound Fracture’ OR have no patients.
[Write nested sub queries]
List the patients who do not have a prescription. The list should show the patient's name, gender, age, admission date, room, and illness description.
[Use combination of Join and nested sub query]
Explanation / Answer
Q1. Produce a list of all medication codes used in the clinic, sorted alphabetically. Also, show the number of patients to whom each medication has been prescribed.
Answer.
select a.Medication_Code,count(b.Patient_No) from
Medication_Table a left join Prescription_Table b
on a.Medication_Code = b.Medication_Code where b.Patient_No <> ''
group by a.Medication_Code order by a.Medication_Code;
Q2. List the medication prescribed for patients who are in for ‘Cardiac Arrest’; Show patient first and last name, gender, age, room number, illness name, medication name, and dosage.
Answer :
select d.Patient_No ,p.Last_Name,p.First_Name,p.age,p.gender,p.room,i.Illness_Desc,m.Medication_Desc,pr.Dosage
from Illness_Table i , Diagnosis_Table d , Patient_Table p , Prescription_Table pr , Medication_Table m
where
d.Illness_Code = i.Illness_Code and
p.Patient_No = d.Patient_No and
p.Patient_No = pr.Patient_No and
m.Medication_Code = pr.Medication_Code and
i.Illness_Desc = 'Cardiac Arrest'
Q3. Who are the doctor(s) that are attending to ‘Heat Exhaustion’? List the name of the doctor(s) and specialty.
Answer :
select Physician_ID, Specialty from Physician_Table where Physician_ID in ( select ass.Physician_ID from Illness_Table i , Diagnosis_Table d , Assignment_Table ass
where i.Illness_Code = d.Illness_Code and
ass.Patient_No = d.Patient_No and
i.Illness_Desc = 'Heat Exhaustion' )
Q4. Produce a list of patients who were prescribed ‘Keflin’. The list should contain the patient name, medication name, dosage, illness description, and the doctor's name.
Answer :
select m.Medication_Code,m.Medication_Desc,pr.Patient_No, pr.dosage , ass.Physician_ID , phy.First_Name, phy.Last_Name , i.Illness_Desc
from Medication_Table m , Prescription_Table pr , Assignment_Table ass , Physician_Table phy , Diagnosis_Table d , Illness_Table i
where
pr.Medication_Code = m.Medication_Code and
d.Patient_No = ass.Patient_No and
ass.Patient_No = pr.Patient_No and
phy.Physician_ID = ass.Physician_ID and
d.Illness_Code = i.Illness_Code and
m.Medication_Desc = 'Keflin'
Q5. List names (first and last) and telephone extension of doctors who have a patient that is in for ‘Work Stress’.
Answer :
select Physician_ID,Last_Name, First_Name, Tel_Ext from Physician_Table where Physician_ID in
( select ass.Physician_ID from Illness_Table i , Diagnosis_Table d , Assignment_Table ass
where i.Illness_Code = d.Illness_Code and
ass.Patient_No = d.Patient_No and
i.Illness_Desc = 'Cardiac Arrest' )
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.