﻿ Cursor and Repeated SQL

## Topic: Repeated SQL & Query Optimization

Let’s connect the dots about Tracey and Super Computer.  In this situation, Tracey went to the doctor because she was being given medications that were just not working.  The computer arrived and promised that he could save the day. By mining data, the Computer investigated Tracey’s health problems and cross-referenced it to other cases with same problems.  It then found what worked among these cases in the data. Of course, no case in the data is exactly like Tracey. The computer didn’t need to locate a patient that has 100% exactly like Tracey.  No cases matches all the relevant features of the patient. The solution is to find cases that nearly match the patient and then model what it would look like if all features were matched. Then these models can be used to forecast patients' reaction to the medications.  The computer determines Tracey’s likely success based on experience of similar cases with various mediations! And the best part? Tracey doesn’t have to be a guinea pig! She can simply take the medication and know that it will make her feel better.

## Learning Module Objectives:

After completing the activities in this section, you should be able to:

• Repeatedly execute SQL
• Analyze data one row at a time
• Use do-while command
• Find cases that fit subset of patient's features.
• Improve computational speed through indexing

## Learning Material

1. Cursor and do-while commands Slides► Video►
2. Query Optimization Slides► Video►

## Individual Assignments

No individual assignment should be completed in teams.  Submit your work in Blackboard.  Do not discuss the work with other students.

Question Related to Cursor and Do-While Commands:  Refer to the patient 457631 that was analyzed in a previous assignment.  This patient has been hospitalized at age 58.08 with the 5 diagnoses IV17.3, I401.9, I278.00, I786.05, I272.4, calculate how many patients show in the data with 2, 3, 4, and 5 combinations of these diagnoses.  Use a do-while command to go through the various combination of these diagnoses. Calculate the odds of mortality for each of these combination of diagnoses.  Data►

Question Related to Query Optimization:  Use indexes defined on id, icd9, and AgeAtDx to make the code that you and teammates had prepared for predicting mortality.  Report the computational efficiency of the code before and after indexing.  Data►

Ongoing Question: This question is an individual project.  Each week a portion of the work is done.  This week calculate accuracy of predicting diabetes using diagnoses, age and gender as predictors.