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]