Data engineering, one table at a time with SQL

Let's now look at how to perform data engineering with SQLite. First, we have to create our tables in the database. Then, we will manipulate them, one by one, to get the desired final table.

Query Set #0 – creating the six tables

In this mock assignment, let's pretend that the portal at which the data can be downloaded from the cardiology practice is not working. Instead, one of the technicians sends you SQLite commands that you can use to create the six tables. You can follow along with the book and type each command manually. Alternatively, you can go to the book's official code repository and download the commands from there.

 

Query Set #0a – creating the PATIENT table

One way to create a table in our database is to specify its schema manually. Let's do that here with our first table, the PATIENT table:

sqlite> CREATE TABLE PATIENT(
  Pid VARCHAR(30) NOT NULL,
  Fname VARCHAR(30) NOT NULL,
  Minit CHAR,
  Lname VARCHAR(30) NOT NULL,
  Bdate...