Now that we've populated information from the VISIT table into the MORT_FINAL table, it's time to start integrating the other tables as well. We'll start with the PATIENT table; specifically, we would like to add in the birthdate and sex from this table. We start with the birthdate.

In Query Set #2, we demonstrate the basic query pattern for adding a new column (the birthdate) to our table. We start with an ALTER TABLE statement, followed by the name of the table, the operation (ADD COLUMN, in this case), the name of our new column, and the variable type. Although standard SQL supports a DATE variable type for dates, in SQLite, we use the TEXT type. Dates are always specified in YYYY-MM-DD format.

After we've initialized the new column with our ALTER TABLE statement, the next step is to populate the actual birthdates from the PATIENT table. For this, we use an UPDATE statement. We specify the table we are updating, followed by a SET statement and the name of the column we are modifying, with an equals sign.

The SELECT-FROM-WHERE block is the basic retrieval query of the SQL language. We are trying to retrieve information from the PATIENT table and populate into our new Bdate column, so we use a SELECT-FROM-WHERE statement, enclosed in parentheses, after the equals sign. Think of the SQL statement as issuing the following order to the database with its SELECT statement: "For every row in the MORT_FINAL table, find me the birthdate from the PATIENT table where the Pid in the PATIENT table equals the Pid in the MORT_FINAL table."

Following the UPDATE statement for the Bdate column, we use the same sequence of queries (ALTER TABLE and UPDATE) to retrieve the Sex column from the PATIENT table:

sqlite> ALTER TABLE MORT_FINAL ADD COLUMN Bdate TEXT;

sqlite> UPDATE MORT_FINAL SET Bdate = 
(SELECT P.Bdate
FROM PATIENT AS P
WHERE P.Pid = MORT_FINAL.Pid);

sqlite> ALTER TABLE MORT_FINAL ADD COLUMN Sex CHAR;

sqlite> UPDATE MORT_FINAL SET Sex = 
(SELECT P.Sex
FROM PATIENT AS P
WHERE P.Pid = MORT_FINAL.Pid);