The first query we write will create the table using a CREATE TABLE statement. In one version of a CREATE TABLE statement, each variable is spelled out with its corresponding datatype. We used this version to create our six tables from scratch in the previous examples. Alternatively, one can create a table by copying from an existing table. We will opt for the second option here.

Now that we've answered that question, a second one remains–which table should we copy from? It might be tempting to copy the patient information from the PATIENT table to our final table, since it contains one row for each patient, and it contains basic demographic information. However, we must remember that the use case is based on each visit, not patient. Therefore, if a patient has two visits (such as Patient #1), technically that patient will receive two risk scores: one for each visit. Therefore, we should start by copying information from the VISIT table. This will create a table with six rows, one for each visit.

So we start our query using a CREATE TABLE clause, with MORT_FINAL being the name of our new table. Then we use the AS keyword. The next two lines of the query specify which information to copy using a SELECT-FROM-WHERE construct:

sqlite> CREATE TABLE MORT_FINAL AS
SELECT Visit_id, Pid, Attending_md, Visit_date, Pri_dx_icd, Sec_dx_icd
FROM VISIT;

A SELECT-FROM-WHERE statement is a systematic way of selecting the information we want from a table. The SELECT part acts as a column selector–following the SELECT keyword are the columns that we want to copy into the new table. Note that we left out the names of the diagnoses (Pri_dx_name, Sec_dx_name) since those technically aren't predictor variables, as long we have each code and we can refer to their meanings. The FROM keyword specifies the table name from which we wish to copy (VISIT, in this case). The WHERE keyword is an optional clause that allows us to select only those rows that meet certain conditions. For example, if we were interested in restricting our model to those visits in which the patient had heart failure, we could say WHERE Pri_dx_code == 'I50.9'. Because we are interested in including all visits in this example, we do not need a WHERE clause for this query. We will see the WHERE clause in action in the next query set.