CIS 330 -- SQL Queries Assignment #2
Joins and Unions
.
The following queries will be done using the same Hospital database that you used for the first SQL assignment.
DELIVERABLES: You will upload to Canvas a single script file that contains all of the following 10 queries. Before each query you should have a comment that gives the query number. For example:
REM Query #1 or
-- Query #1
1) Perform a query that shows all the patients, their rooms, unit, and room phone number for patients in private rooms. Results should look like this, properly sorted and labeled.
2) How many patients are in each unit? Include units with no patients as well. Your results should look like this, with proper ordering and column headings:
3) Show all the rooms where the room’s phone number is not the same as the room’s unit’s phone number. The results are shown below.
4) Show information about all the nurse visits. You should show the nurse and patient involved, the unit the nurse belongs to, the patient’s room number the time of visit and the nurse’s comments. Sorting, formatting, and column labels should be as shown below.
5) For each unit with less than five meds given, show the number of meds given by nurses in that unit. Only include units that have less than five meds given, but also include units with no meds given. Results should look like this, properly ordered and labeled.
6) Is anybody both a patient and a nurse? Perform a join query to find the person or person whose name is in both tables. Note: this is a rare case where the join has nothing to do with primary and foreign keys. Also show both the patient and nurse email addresses for each person found. Results should look like this, properly labeled and sorted.
7) For nurses in units with private rooms, how many private rooms is the nurse responsible for? Your query should produce the following results.
8) Show the actions done by each nurse. We want to know how many visits each nurse made and also how many meds each nurse gave. The results below can be done by combining two queries together in a union. Your results should look exactly like this:
9) How many visits have been made to private rooms vs. non-private rooms? Your results should look like this, properly labeled and ordered. This will also involve a union of two queries.
10) For each floor, show how many nurse visits there are and also show the age of the oldest patient. Only include floors where the oldest patient is at least one year old, but also include floors with no nurse visits. The results should look like this, although the ages may be off by a tenth or so based on when you populated your database.