SQL

Please use the attached SQL file to answer the associated question. 

001

8

01

33

Specialty

4

3

are

1004

Primary Care

1004

443

Primary Care

1007

48

Surgeon

Specialty

7

Specialty

4

61

Primary Care

	Physician_ID				Patient_No	Role
			1							5	8						3					Specialty
	10					6		4
				100						111				Primary Save Time On Research and Writing Hire a Pro to Write You a 100% Plagiarism-Free Paper. Get My Paper			C
							6558
			7
		1007			5883		Surgeon
		9							2
	10	27			11	73
	102			3188
		103			32

Patient_No

1113 6

3 5

3188 6

6

5883 9

5

6558 1
6558 4
6558 5

5

7443 10

2

	Illness_Code
117
3261
		6334
	7443
		9248

Illness_Code

1

ngina

2

racture

3

Orthopedics

4

5

Cardiology

6

Cardiology

7

Orthopedics

8

9

10

Industrial Health

Illness_Desc	Department
			A				Cardiology
Compound			F			Orthopedics
Simple Fracture
Pneumonia	Pulmonary Functions
Cardiac Arrest
Chest Pain
Arthoscopy
Chicken Pox	Pediatrics
Heat Exhaustion		Industrial Health
Work Stress

edication_Code

00

00

000

5000

35000

¤ 2.50

							M	Medication_Desc	On_Hand	On_Order	Unit_Cost
	ASP	Aspirin	88000			50	¤ 1.	35
		KEF	Keflin		29	43000		¤ 2.50
		PCN	Penicillin	250000	9000	¤ 1.63
	TAG	Tagament		23	15000	¤ 4.00
		VAL	Valium		35000	¤ 1.00
VI			B	Vibramycin	13000

Patient_No

1113

27 F

C

s

M

111 C

3188

32 M

A

3261

73 M

A

5883

Daniel 50 M

B

6334

23 F

111 B

6558

M

117 B

7443

35 F

111 A

9248

29 M

109 C

	Last_Name		First_Name	Age	Gender	Admit_Date	Room	Bed_Assignment
Franklin	Janet	8/25/20	202
		1173		Daniel	Wilson	21	7/10/20
Suarez	Robert	8/22/20			109
Knauer	Gary	7/13/20	223
Jameson	8/12/20	132
Johnson		Abigail	8/30/20
Wood	Oscar	62	8/10/20
Leonard		Allison	9/4/20
Peterson	Brett	8/31/20

Physician_ID Last_Name First_Name

Specialty

1004

1007

1027

Abigail

1

Cardiology

1034

Allison

Tel_Extension
1001	McDonald	Brad	4565	Oncology
Brown	Katherine	1265	Internal Medicine
Miller	Sarah	2398	Surgery
1022	Lincoln	Bobby	1992	Dermatology
Kretz		101
Branson	2048	Geriatrics

Patient_No Medication_Code

100 1173 PCN 3
101 6334

3

102 6558 KEF 2
103 1173 ASP 2

6558 VIB 3

7443 KEF 2

5883 TAG 2

6558 VAL 3

9248 PCN 3

109 1173 VAL 2

Pres_No	Dosage
	VIB
104
105
106
107
108

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”));

IS3030 Database Design & Tools Spring 2021

Using the relational database for the ‘Patient Care System’ from the starter database provided (note this database is similar to but not exactly like what you created in Assignment #1, do not use your solution from Assignment #1 for this lab), you will construct queries in
SQL
to answer the following questions:

Construct SQL queries to answer the following questions
(1.5 points each. You will receive full points off for incorrect query results, and half points off if you have the correct query results but did not follow explicit instructions).
Be sure to save each query separately and label appropriately (Query 1, Query 2, etc).

1. Produce a list of Physician IDs of all physicians, sorted by last name alphabetically, showing their ID, Specialty, and Last Name. Also, show the number of patients to whom each physician is assigned to. [Use joins]

2. Find the oldest and youngest patients admitted into the hospital, grouped by gender (male and female). Names of patients do not need to be listed. Give the query columns appropriate titles.

3. Produce a list of all medication codes used in the clinic, sorted by Medication_Desc alphabetically. Also show the number of patients to whom each medication has been prescribed.

4. List the medication prescribed for patients who are in for ‘Pneumonia’; Show patient first and last name, date of admission, room number, medication code, and medication dosage. [Use joins]

5. List the doctor(s) that are attending to patients diagnosed with ‘Cardiac Arrest’ by their name (first & last) and Physician_ID. [Use nested sub queries]

6. Produce a list of patients who were prescribed ‘Penicillin’. The list should contain the patient’s first and last name, dosage, illness description, and the last name and Physician ID of their doctor. [Use joins]

7. List names (first and last), specialty and Physician_ID of doctors who have a patient that is in for ‘Work Stress’. [Write nested sub queries]

8. List names (first and last), specialties, and IDs of doctors who have no patients. [Use NOT IN query]

9. List the patients who do not have a prescription. The list should show the patient’s number, first name, last name, age, attending physician’s last name and physician ID, and illness description. [Use combination of Join and nested sub query]

10.

List the physicians name (first and last), telephone extension, and specialty for those who are assigned the role ‘Specialty’. Make sure this list is distinct. [Use join query]

Deliverables

The database and queries should be submitted via Canvas – Assignments – Lab Assignment 2 (One file saved as username_Assign2.accdb, ex. eberlemn_Assign2.accdb)

Note
: Access generates (behind the scene) SQL statements (queries) for the queries you wrote using the QBE-like interface in Assignment 1; in fact, Access lets you have access to these SQL statements. However, you must write SQL statements from scratch for this homework since the statements generated by Access doesn’t quite conform to ANSI/ISO standards and you need to learn ANSI/ISO standard SQL. If you turn in Access generated SQL statements, you will get no credit for it.