Consider the following schema for Patients, Temperatures, Medications and Prescriptions...

First: Insert some fake data in each of your tables. Remember that you will need to make sure that prescriptions have the proper medication_id and patient_id linkages, and that medications have the proper patient_id linkages.

Then: Write queries for the following:

  1. 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)
  2. 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)
  3. 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.
  4. 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.

Submit a SQL script that creates your tables, inserts some sample data, and performs the requested queries.