SQL

IS3030_LabAssignment2_SS21_StarterFile1.accdb

Home >Information Systems homework help >SQL

Physician_ID	Patient_No	Role
1001	5883	Specialty
1001	6334	Specialty
1004	1113	Primary Care
1004	6558	Primary Care
1004	7443	Primary Care
1007	5883	Surgeon
1007	9248	Surgeon
1027	1173	Specialty
1027	3188	Specialty
1034	3261	Primary Care

Patient_No	Illness_Code
1113	6
1173	5
3188	6
3261	6
5883	9
6334	5
6558	1
6558	4
6558	5
7443	5
7443	10
9248	2

Illness_Code	Illness_Desc	Department
1	Angina	Cardiology
2	Compound Fracture	Orthopedics
3	Simple Fracture	Orthopedics
4	Pneumonia	Pulmonary Functions
5	Cardiac Arrest	Cardiology
6	Chest Pain	Cardiology
7	Arthoscopy	Orthopedics
8	Chicken Pox	Pediatrics
9	Heat Exhaustion	Industrial Health
10	Work Stress	Industrial Health

Medication_Code	Medication_Desc	On_Hand	On_Order	Unit_Cost
ASP	Aspirin	88000	5000	¤ 1.35
KEF	Keflin	2900	43000	¤ 2.50
PCN	Penicillin	250000	9000	¤ 1.63
TAG	Tagament	23000	15000	¤ 4.00
VAL	Valium	35000	5000	¤ 1.00
VIB	Vibramycin	35000	13000	¤ 2.50

Patient_No	Last_Name	First_Name	Age	Gender	Admit_Date	Room	Bed_Assignment
1113	Franklin	Janet	27	F	8/25/20	202	C
1173	Daniels	Wilson	21	M	7/10/20	111	C
3188	Suarez	Robert	32	M	8/22/20	109	A
3261	Knauer	Gary	73	M	7/13/20	223	A
5883	Jameson	Daniel	50	M	8/12/20	132	B
6334	Johnson	Abigail	23	F	8/30/20	111	B
6558	Wood	Oscar	62	M	8/10/20	117	B
7443	Leonard	Allison	35	F	9/4/20	111	A
9248	Peterson	Brett	29	M	8/31/20	109	C

Physician_ID	Last_Name	First_Name	Tel_Extension	Specialty
1001	McDonald	Brad	4565	Oncology
1004	Brown	Katherine	1265	Internal Medicine
1007	Miller	Sarah	2398	Surgery
1022	Lincoln	Bobby	1992	Dermatology
1027	Kretz	Abigail	1011	Cardiology
1034	Branson	Allison	2048	Geriatrics

Pres_No	Patient_No	Medication_Code	Dosage
100	1173	PCN	3
101	6334	VIB	3
102	6558	KEF	2
103	1173	ASP	2
104	6558	VIB	3
105	7443	KEF	2
106	5883	TAG	2
107	6558	VAL	3
108	9248	PCN	3
109	1173	VAL	2

SELECT Physician.Physician_ID, Physician.Last_Name, Physician.Specialty, Patient.Last_Name FROM Patient LEFT JOIN (Physician RIGHT JOIN Assignment ON Physician.Physician_ID = Assignment.Physician_ID) ON Patient.Patient_No = Assignment.Patient_No ORDER BY Physician.Last_Name;

SELECT Min(Patient.Age) AS MinOfAge, Max(Patient.Age) AS MaxOfAge, Patient.Gender FROM Patient GROUP BY Patient.Gender;

SELECT Medication.Medication_Desc, Count(Prescription.Patient_No) AS CountOfPatient_No FROM Patient INNER JOIN (Medication INNER JOIN Prescription ON Medication.Medication_Code = Prescription.Medication_Code) ON Patient.Patient_No = Prescription.Patient_No GROUP BY Medication.Medication_Desc ORDER BY Medication.Medication_Desc;

SELECT Patient.Last_Name, Patient.First_Name, Patient.Admit_Date, Patient.Room, Medication.Medication_Code, Prescription.Dosage, Medication.Medication_Desc FROM PATIENT, MEDICATION WHERE (((Medication.Medication_Desc)="Pneumonia"));