CHIP 490.297: Into to Databases in Healthcare
https://jonathantweedy.com/www/work/CHIP/CHIP490DB…
Using the Patient, Medications, Prescriptions and Temperatures Schema (located in link above) that we arrived at in class, insert some fake data and then write SQLto do the following:
[5%]
Write a SELECT that lists all patients (one per row) and the COUNT of the prescriptions that they currently have. (This should return one row per patient with their name in one column and the number of prescriptions in another.) – use GROUP BY (OUTER JOIN)
[5%]
Write a SELECT to find the maximum and minimum temperatures taken for each of your patients. If the patient has no temps in their list, then their name should still come back, but with NULLs in their min and max columns. (This should return one row per patient with columns for their name, their max temp, and their min temp.) (GROUP BY, OUTER JOIN)
[5%]
In the same select (as ^above^), make the NULL values say “(None)” instead of just ‘NULL’ by using CASE WHEN ELSE syntax in your SELECT statement. You can write this as a separate select, or you can just modify the one above to include it.
[5%]
Write a SELECT that returns the name(s) of the patient(s) with the absolute highest and lowest recorded temperatures. It should be one row with a maxtemp column and a mintemp column, and those columns should contain the name(s) of the patient(s) who had those maximums.
