SQL

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]

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.