Please read the instructions carefully before beginning this assignment.
A few of the queries we ask you to write will be easier if you use MS SQL Server functions located at https://docs.microsoft.com/en-us/sql/t-sql/functions/functions
We have tried to note every place below you would need to research a particular function.
Before you can write each SELECT query below, you will need to have run the script 1 – SQL_HMQK-DDL.sql to build the tables you need, and 2 – SQL_HMWK-DML.sql to populate those tables with data.
When you have finished your homework, please upload all of your queries (in order) as a .sql or .txt file to the Assignment in Canvas.
1. Write a query to show the name, sex, and room number for any patient who was admitted between September 15, 2015 and September 30, 2015. Order the results by the patient’s name ascending. (5 points)
2. Show the unique patient names and Result Name, for patients with vital signs recorded. (If a patient exists in the PATIENT_INFORMATION_TBL, and they exist in the PATIENT_VITALS_TBL, their name and the Result Name of the vital sign should appear in your output). (5 points)
3. Show the same output (patient name, Result Name), only this time, make sure all patient names appear in the output for all patients in the PATIENT_INFORMATION_TBL, even if they do not have a record in the PATIENT_VITALS_TBL. Your query should show NULL for Result Name wherever there is not vital sign recorded for a patient. (5 points)
4. Age is incorrect in the PATIENT_INFORMATION_TBL. Age is actually something that should not be stored in a database, but derived using the date of birth. Write a query to select the name, age (currently in the database), and a new field to show the actual age. You can calculate age a few ways. We recommend you research the datediff function here : https://docs.microsoft.com/en-us/sql/t-sql/functions/functions Also, make sure when you pass in today’s date, it is dynamic and automatically puts in today’s date. (5 points)
5. Write a query to select the patient care unit (PCU) and show the number of patients staying in each PCU. Order the results by the number of patients per pcu descending. (5 points)
6. There are some patients who are in the PATIENT_INFORMATION_TBL, but not in the PATIENT_THERAPY_TBL. Write a query to output the hospital number, patient name, sex, and patient care unit (pcu), for any patient who is in the PATIENT_INFORMATION_TBL, but not in the PATIENT_THERAPY_TBL. (10 points)
7. The PATIENT_MEDICATION_TBL contains all drugs a patient has been prescribed. Write a query to show the patient name, the item description (PATIENT_MEDICATION_TBL), and start date of the drug for any patient who was prescribed an “INSULIN” drug and the drug is ACTIVE. We recommend you use LIKE in your WHERE clause, as there are several Insulin drugs. (10 points)
8. We have several drugs in the FORMULARY_CLINICAL table that have duplicate names. Check out the GENERICNAME field, and you can see some of the names repeated. Write a query to output the genericname for all FALLRISK drugs who have a duplicate name in the genericname field. (10 points)
9. Patients are charged for drugs using the charge from the FORMULARY_CLINICAL table (this is a charge per dose), multiplied by the number of doses the patient was given (PATIENT_THERAPY_TBL). Write a query to show the Top 10 most expensive patients. Your output should include the patient name, and the “total charge” for each patient, who are in the top 10 most expensive total charged. Order your results by the “total charge” in descending order. (10 points)
10. Patient height and weight is recorded in centimeters and kilograms. Write a query to show the patient name, their height in feet (1 cm = 0.0328084 feet), and their weight in pounds (1 kg = 2.20462 lbs) for any patient on an ACTIVE Narrow Therapeutic Window drug. Not every patient has a recorded height or weight, so your results may include null values for these fields for some patients. You may need to research CAST (convert a text field to a numeric field). (10 points)
11. Write a query to show the name, room number, and “total doses” received for each patient who was given and ANTIBIOTIC, had and admit date between 9/12 and 10/5 and was in either PCU of 4N or 2N. Recall DOSE in the THERAPY table shows how many doses of a drug a patient received. Order the results by the admit date (descending) (10 points)
12. The following example is how you would nest two queries together. Lets say you wanted to write a query to show the patient name for any patient who’s ideal body weight was greater than the average ideal body weight of all patients.
Step 1 : write a simple query to extract the average ideal body weight –
FROM PATIENT_INFORMATION_TBL <– this will return 9.52
Step 2 : write the query to show the patient name –
WHERE IDEAL_BODY_WEIGHT > 9.52 <— see the next step for replacing the 9.52 with a query
Step 3 : combine the two queries –
WHERE IDEAL_BODY_WEIGHT > (
In this example, I replaced the 9.52 from the second step, with the query from step 1 (placing it inside parenthesis). The data in operational databases change often. 9.52 is the average IDEAL_BODY_WEIGHT at this moment, but in 2 hours, several new patients could be admitted or discharged, so it is necessary to write the query in a way that is dynamic. No values are hard coded in Step 3, which is key for dynamic queries.
Say you wanted to find the top 5 patients who were given the highest total doses. This query is tricky, because there are actually 6 patients who meet this criterion. Two patients Cecilia Lopez and Gayle Carson, tie for fifth place with the exact same number of total doses. Write a dynamic query to show the hospital number, patient name, and total doses for the patients who are in the top 5 most dosed patients. Since you are looking for the top 5, it is acceptable to hard code in a criteria to limit output of a query to 5 rows, however it is not okay to hard code in to return 6 rows of data, in order to return all 6 patients who meet the criteria of being in the top 5 highest dosed patients. To complete this query, you will likely need to nest two queries together in order to make it dynamic. No hard coded values here (other than limiting a query output to five values). (15 points)