[YCMI_CBDS_Summer_Course]_Data_Management_and_Databases_Lecture_Session.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "[YCMI_CBDS Summer Course] Data Management and Databases - Lecture Session",
      "provenance": [],
      "collapsed_sections": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "NtnVvQyK3Jnl",
        "colab_type": "text"
      },
      "source": [
        "# Lecture: Data Management and Databases"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "n92MnTvj3Y4-",
        "colab_type": "text"
      },
      "source": [
        "In this lecture you will learn how to create and populate a relational database. After it is created, we will walk through the different strategies you can use to pull data from the database across its multiple tables. In this demonstration, we will use Python's sqlite3 library and pandas to write SQL calls."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "GAQew7E73csv",
        "colab_type": "text"
      },
      "source": [
        "## Connect to the Data and Create a Database"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "53RNHmkglkpi",
        "colab_type": "text"
      },
      "source": [
        "Mount Google Drive as the storage location."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "4GPefpLr6KM0",
        "colab_type": "code",
        "outputId": "85006e73-eafc-4859-cc82-05716a990be2",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 122
        }
      },
      "source": [
        "from google.colab import drive\n",
        "drive.mount(\"/content/gdrive\", force_remount=True)\n"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Go to this URL in a browser: https://accounts.google.com/o/oauth2/auth?client_id=947318989803-6bn6qk8qdgf4n4g3pfee6491hc0brc4i.apps.googleusercontent.com&redirect_uri=urn%3aietf%3awg%3aoauth%3a2.0%3aoob&response_type=code&scope=email%20https%3a%2f%2fwww.googleapis.com%2fauth%2fdocs.test%20https%3a%2f%2fwww.googleapis.com%2fauth%2fdrive%20https%3a%2f%2fwww.googleapis.com%2fauth%2fdrive.photos.readonly%20https%3a%2f%2fwww.googleapis.com%2fauth%2fpeopleapi.readonly\n",
            "\n",
            "Enter your authorization code:\n",
            "··········\n",
            "Mounted at /content/gdrive\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "adasz6ErlrtB",
        "colab_type": "text"
      },
      "source": [
        "Establish a database called lecture.db using the sqlite3 python module."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "jNQTn9AZdgM5",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "import sqlite3 #the sqlite3 library allows you to write SQL queries using Python\n",
        "\n",
        "conn = sqlite3.connect('lecture.db') #establish the database and connect to it\n",
        "cursor = conn.cursor() #create a cursor for the db. cursors allow you to retrieve, add, and remove individual database records when using sqlite3 functions.\n"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "QPHN2u9McUlI",
        "colab_type": "text"
      },
      "source": [
        "This data is currently organized in seperate csv files. CSV (comma seperated value) files are tabular data structures (spreadsheets) created in a plain text format. We will need to combine these csv files into our database, but first these csv files need to be brought into Python using the Pandas pd.read_csv function.\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "HjLmqQiOiTtp",
        "colab_type": "code",
        "outputId": "6dbfe025-d79d-444d-85af-88e0db91fa65",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 935
        }
      },
      "source": [
        "import pandas as pd #the pandas library will handle the csvs\n",
        "\n",
        "#create variables to store csvs that will make up the database\n",
        "proceedure_events = pd.read_csv(\"/content/gdrive/My Drive/[YCMI_CBDS Summer Course] Data/mimic_iii/PROCEDURES_ICD.csv\") #this command uses pandas to read in the csv file from its location on the mounted google drive\n",
        "patients = pd.read_csv(\"/content/gdrive/My Drive/[YCMI_CBDS Summer Course] Data/mimic_iii/PATIENTS.csv\") #this is repeated for each of the csvs we want to work with\n",
        "icu_stays = pd.read_csv(\"/content/gdrive/My Drive/[YCMI_CBDS Summer Course] Data/mimic_iii/ICUSTAYS.csv\")\n",
        "microbiology_events = pd.read_csv(\"/content/gdrive/My Drive/[YCMI_CBDS Summer Course] Data/mimic_iii/MICROBIOLOGYEVENTS.csv\")\n",
        "admissions = pd.read_csv(\"/content/gdrive/My Drive/[YCMI_CBDS Summer Course] Data/mimic_iii/ADMISSIONS.csv\")\n",
        "proceedure_icd = pd.read_csv(\"/content/gdrive/My Drive/[YCMI_CBDS Summer Course] Data/mimic_iii/D_ICD_PROCEDURES.csv\") \n",
        "\n",
        "#display the .head() of each csv to confirm it was imported properly\n",
        "print(proceedure_events)\n",
        "print(patients.head())\n",
        "print(icu_stays.head())\n",
        "print(microbiology_events.head())\n",
        "print(admissions.head())\n",
        "print(proceedure_icd.head())"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "     row_id  subject_id  hadm_id  seq_num  icd9_code\n",
            "0      3994       10114   167957        1       3605\n",
            "1      3995       10114   167957        2       3722\n",
            "2      3996       10114   167957        3       8856\n",
            "3      3997       10114   167957        4       9920\n",
            "4      3998       10114   167957        5       9671\n",
            "..      ...         ...      ...      ...        ...\n",
            "501  230170       41976   179418        2       3893\n",
            "502  235193       41976   155297        1       3893\n",
            "503  235194       41976   155297        2        966\n",
            "504  238588       41976   125013        1       3893\n",
            "505  239961       41976   151798        1        966\n",
            "\n",
            "[506 rows x 5 columns]\n",
            "   row_id  subject_id  ...              dod_ssn expire_flag\n",
            "0    9467       10006  ...  2165-08-12 00:00:00           1\n",
            "1    9472       10011  ...                  NaN           1\n",
            "2    9474       10013  ...  2125-10-07 00:00:00           1\n",
            "3    9478       10017  ...  2152-09-12 00:00:00           1\n",
            "4    9479       10019  ...  2163-05-15 00:00:00           1\n",
            "\n",
            "[5 rows x 8 columns]\n",
            "   row_id  subject_id  ...              outtime      los\n",
            "0   12742       10006  ...  2164-10-25 12:21:07   1.6325\n",
            "1   12747       10011  ...  2126-08-28 18:59:00  13.8507\n",
            "2   12749       10013  ...  2125-10-07 15:13:52   2.6499\n",
            "3   12754       10017  ...  2149-05-31 22:19:17   2.1436\n",
            "4   12755       10019  ...  2163-05-16 03:47:04   1.2938\n",
            "\n",
            "[5 rows x 12 columns]\n",
            "   row_id  subject_id  ...  dilution_value interpretation\n",
            "0  134694       10006  ...             NaN            NaN\n",
            "1  134695       10006  ...             2.0              S\n",
            "2  134696       10006  ...             1.0              S\n",
            "3  134697       10006  ...             4.0              I\n",
            "4  134698       10006  ...             4.0              R\n",
            "\n",
            "[5 rows x 16 columns]\n",
            "   row_id  subject_id  ...  hospital_expire_flag has_chartevents_data\n",
            "0   12258       10006  ...                     0                    1\n",
            "1   12263       10011  ...                     1                    1\n",
            "2   12265       10013  ...                     1                    1\n",
            "3   12269       10017  ...                     0                    1\n",
            "4   12270       10019  ...                     1                    1\n",
            "\n",
            "[5 rows x 19 columns]\n",
            "   row_id  ...                                         long_title\n",
            "0       1  ...  Destruction of chorioretinal lesion by xenon a...\n",
            "1       2  ...  Destruction of chorioretinal lesion by laser p...\n",
            "2       3  ...  Destruction of chorioretinal lesion by photoco...\n",
            "3       4  ...  Destruction of chorioretinal lesion by radiati...\n",
            "4       5  ...  Destruction of chorioretinal lesion by implant...\n",
            "\n",
            "[5 rows x 4 columns]\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "voVmbH8V8d8W",
        "colab_type": "code",
        "outputId": "c497aa55-8ab2-46d7-feaa-e80e3f7cf747",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 1000
        }
      },
      "source": [
        "#the following creates a list containing all of the dataframes we have read into our python project\n",
        "mimic_csv_list = [proceedure_events, patients, icu_stays, microbiology_events, admissions, proceedure_icd]\n",
        "\n",
        "#the following for loop prints every column name for each of our dataframes contained in the list created above. The empty print statement results in a blank space between \n",
        "#the lists of column names\n",
        "for csv in mimic_csv_list: \n",
        "    for column_name in csv.columns:\n",
        "      print(column_name)\n",
        "    print()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "row_id\n",
            "subject_id\n",
            "hadm_id\n",
            "seq_num\n",
            "icd9_code\n",
            "\n",
            "row_id\n",
            "subject_id\n",
            "gender\n",
            "dob\n",
            "dod\n",
            "dod_hosp\n",
            "dod_ssn\n",
            "expire_flag\n",
            "\n",
            "row_id\n",
            "subject_id\n",
            "hadm_id\n",
            "icustay_id\n",
            "dbsource\n",
            "first_careunit\n",
            "last_careunit\n",
            "first_wardid\n",
            "last_wardid\n",
            "intime\n",
            "outtime\n",
            "los\n",
            "\n",
            "row_id\n",
            "subject_id\n",
            "hadm_id\n",
            "chartdate\n",
            "charttime\n",
            "spec_itemid\n",
            "spec_type_desc\n",
            "org_itemid\n",
            "org_name\n",
            "isolate_num\n",
            "ab_itemid\n",
            "ab_name\n",
            "dilution_text\n",
            "dilution_comparison\n",
            "dilution_value\n",
            "interpretation\n",
            "\n",
            "row_id\n",
            "subject_id\n",
            "hadm_id\n",
            "admittime\n",
            "dischtime\n",
            "deathtime\n",
            "admission_type\n",
            "admission_location\n",
            "discharge_location\n",
            "insurance\n",
            "language\n",
            "religion\n",
            "marital_status\n",
            "ethnicity\n",
            "edregtime\n",
            "edouttime\n",
            "diagnosis\n",
            "hospital_expire_flag\n",
            "has_chartevents_data\n",
            "\n",
            "row_id\n",
            "icd9_code\n",
            "short_title\n",
            "long_title\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "DHfeP3Gimu1Z",
        "colab_type": "text"
      },
      "source": [
        "Next, the pandas .to_sql method statements below will add the data from these six dataframes to lecture.db database. Each dataframe will become a seperate table within the database. "
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "bGgonbxDlUMr",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "proceedure_events.to_sql(\"proceedure_events\", conn, if_exists=\"replace\", index = False) #\"proceedure_events\" becomes the name of the table in the database. \n",
        "patients.to_sql(\"patients\", conn, if_exists=\"replace\", index = False) #conn is the cursor that points to the lecture.db.\n",
        "icu_stays.to_sql(\"icu_stays\", conn, if_exists=\"replace\", index = False) #\"index = false\" indicates that there is no row headers or an index column in the dataframe.\n",
        "microbiology_events.to_sql(\"microbiology_events\", conn, if_exists=\"replace\", index = False)\n",
        "admissions.to_sql(\"admissions\", conn, if_exists=\"replace\", index = False)\n",
        "proceedure_icd.to_sql(\"proceedure_icd\", conn, if_exists = \"replace\", index = False)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "trME1wtE3q8I",
        "colab_type": "text"
      },
      "source": [
        "## Exploring the MIMIC III Demo Database"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "KIDuuSwz33Dz",
        "colab_type": "text"
      },
      "source": [
        "### Quality Checking Considerations\n",
        "* Who created this dataset originally?\n",
        "* Does this dataset have any corrisponding documentation? \n",
        "* Are there any limitations on how this database can be used?\n",
        "\n",
        "Find information on the MIMIC III database here: https://mimic.physionet.org/gettingstarted/demo/\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "h81L3tdv5Bik",
        "colab_type": "text"
      },
      "source": [
        "### Data Management Considerations\n",
        "\n",
        "Take time to look through the documentation associated with the mimic iii database. Understanding what data exists in a database is vital in order to form queries.\n",
        "\n",
        "* Where is the data stored and in what format?\n",
        "  * The csvs are stored in Google Drive, and mounted to our project a command used above\n",
        "* What is included in the database? What is not included that you might expect to be included?\n",
        "  * The database schema can be found here: https://mit-lcp.github.io/mimic-schema-spy/\n",
        "* What is the structure of this database?\n",
        "  * What are the names of each of the tables included in the database?\n",
        "  * What data do the tables contain?\n",
        "  * Each of the tables in this database has documentation about its contents: https://mimic.physionet.org/mimictables/admissions/\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "vfc_X-GeWBHX",
        "colab_type": "text"
      },
      "source": [
        "##### Return the names of each of the tables in the database\n",
        "\n",
        "read_sql_query is a Pandas function that allows you to create SQL statements in Python. The SQL statement exists between quotation marks. \n",
        "\n",
        "The statement below selects the name column from the database metadata table, which is created automatically and is called sqlite_master. This allows us to see all of the table names in a database. If you did not have available database documentation, this is a strategy you could use to learn about which tables exist within a database.\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "R0AR3-O9zb1k",
        "colab_type": "code",
        "outputId": "c41c1769-216f-4d62-dd81-a63402272af5",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 235
        }
      },
      "source": [
        "pd.read_sql_query('SELECT name FROM sqlite_master WHERE type = \"table\";', conn) #pd.read_sql_query is the function, the first argument in the function is the SQL script, and the second\n",
        "#argument is the conn variable, whichs connects the function to the database.\n"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>name</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>proceedure_events</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>patients</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>icu_stays</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>microbiology_events</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>admissions</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>proceedure_icd</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                  name\n",
              "0    proceedure_events\n",
              "1             patients\n",
              "2            icu_stays\n",
              "3  microbiology_events\n",
              "4           admissions\n",
              "5       proceedure_icd"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 11
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-UFah-De1QI1",
        "colab_type": "text"
      },
      "source": [
        "##### Return the data types of each column within a table\n",
        "\n",
        "Next, here are two ways to do the same task. The first uses the pandas .read_sql method, and the second uses a method from the sqlite3 library .fetchall.\n",
        "\n",
        "Both use a cursor / conn method to point to and connect to the lecture.db database in order to look at the metadata contained in the sqlite_master file assocateed with the individual database table, \"icu_stays\".\n",
        "\n",
        "Each provides the same data output, but in differing views. I point this out because data output can determine which function you may or may not want to use. In the output below, the sqlite3 output is more clear (because it is not truncated) than the pandas output. \n",
        "\n",
        "However, in this work book, we will rely on the pandas functions because when performing data subset queries, the output is easier to read. The code below results in a return that provides metadata about the data contained in the tables of the database.You can see that row_id is an integer, subject_id is an integer, first_careunit is text. So, we see the column name as well as the data type held within that column. "
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MyCoLb8j4bUF",
        "colab_type": "code",
        "outputId": "e8d1e6ea-bb2f-4628-f5c8-24f278445315",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 105
        }
      },
      "source": [
        "print(\"pandas output:\", pd.read_sql_query('SELECT sql FROM sqlite_master WHERE type = \"table\" AND tbl_name = \"icu_stays\";', conn)) #this \n",
        "print() #prints a blank line\n",
        "cursor.execute(\"select sql from sqlite_master where type = 'table' and tbl_name = 'icu_stays';\") #establishes a sql query in quotation marks and connect this to the cursor \n",
        "print(\"sqlite3 output:\", cursor.fetchall()) #this prints the result of what is gathered from the cursor execution \n",
        "\n",
        "\n",
        "\n"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "pandas output:                                                  sql\n",
            "0  CREATE TABLE \"icu_stays\" (\\n\"row_id\" INTEGER,\\...\n",
            "\n",
            "sqlite3 output: [('CREATE TABLE \"icu_stays\" (\\n\"row_id\" INTEGER,\\n  \"subject_id\" INTEGER,\\n  \"hadm_id\" INTEGER,\\n  \"icustay_id\" INTEGER,\\n  \"dbsource\" TEXT,\\n  \"first_careunit\" TEXT,\\n  \"last_careunit\" TEXT,\\n  \"first_wardid\" INTEGER,\\n  \"last_wardid\" INTEGER,\\n  \"intime\" TEXT,\\n  \"outtime\" TEXT,\\n  \"los\" REAL\\n)',)]\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "61s0AIdK6eLU",
        "colab_type": "text"
      },
      "source": [
        "## Pulling Data from the MIMIC III Demo Database (Single Table)\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "LT-P773-TpIA",
        "colab_type": "text"
      },
      "source": [
        "##### Pull all data from a single table using FROM and SELECT\n",
        "\n",
        "The script below utilizes the sqlite3 library. This library attaches the SQL query to the database cursor with the execute function. The SQL function used is\n",
        "\"SELECT * FROM patients;\".\n",
        "\n",
        "\"*\" indicates that we want all of the records. \"*\" acts as a placeholder for \"all\". \"patients\" is the patient table name that was originally our csv files that was added to the lecture.db database.\n",
        "\n",
        "In order to  read the output of this database call, which is stored in the variable \"result.\" We would need to put result into a for loop. To avoid this extra step and because pandas output is easier to read in these instances, we will use the pandas read.sql function to see the results of the sql queries from here on out.\n",
        "\n",
        "Briefly showing two different libraries that function similarly is a good reminder that not only does Python have libraries that perform the same tasks - but that these libraries think interact with and out put data in differing ways.\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "2Ly8dIpiXksr",
        "colab_type": "code",
        "outputId": "0cda34b5-f707-44dd-8236-b98ddbd91492",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 1000
        }
      },
      "source": [
        "#print(pd.read_sql_query('SELECT * FROM PATIENTS', conn))\n",
        "cursor.execute(\"SELECT * FROM patients;\") #SQL code linked to the cursor\n",
        "result = cursor.fetchall() \n",
        "for r in result:\n",
        "  print(r)\n"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "(9467, 10006, 'F', '2094-03-05 00:00:00', '2165-08-12 00:00:00', '2165-08-12 00:00:00', '2165-08-12 00:00:00', 1)\n",
            "(9472, 10011, 'F', '2090-06-05 00:00:00', '2126-08-28 00:00:00', '2126-08-28 00:00:00', None, 1)\n",
            "(9474, 10013, 'F', '2038-09-03 00:00:00', '2125-10-07 00:00:00', '2125-10-07 00:00:00', '2125-10-07 00:00:00', 1)\n",
            "(9478, 10017, 'F', '2075-09-21 00:00:00', '2152-09-12 00:00:00', None, '2152-09-12 00:00:00', 1)\n",
            "(9479, 10019, 'M', '2114-06-20 00:00:00', '2163-05-15 00:00:00', '2163-05-15 00:00:00', '2163-05-15 00:00:00', 1)\n",
            "(9486, 10026, 'F', '1895-05-17 00:00:00', '2195-11-24 00:00:00', None, '2195-11-24 00:00:00', 1)\n",
            "(9487, 10027, 'F', '2108-01-15 00:00:00', '2190-09-14 00:00:00', None, '2190-09-14 00:00:00', 1)\n",
            "(9489, 10029, 'M', '2061-04-10 00:00:00', '2140-09-21 00:00:00', None, '2140-09-21 00:00:00', 1)\n",
            "(9491, 10032, 'M', '2050-03-29 00:00:00', '2138-05-21 00:00:00', '2138-05-21 00:00:00', '2138-05-21 00:00:00', 1)\n",
            "(9492, 10033, 'F', '2051-04-21 00:00:00', '2133-09-09 00:00:00', None, '2133-09-09 00:00:00', 1)\n",
            "(9494, 10035, 'M', '2053-04-13 00:00:00', '2133-03-30 00:00:00', None, '2133-03-30 00:00:00', 1)\n",
            "(9495, 10036, 'F', '1885-03-24 00:00:00', '2185-03-26 00:00:00', '2185-03-26 00:00:00', '2185-03-26 00:00:00', 1)\n",
            "(9497, 10038, 'F', '2056-01-27 00:00:00', '2147-03-17 00:00:00', '2147-03-17 00:00:00', '2147-03-17 00:00:00', 1)\n",
            "(9499, 10040, 'F', '2061-10-23 00:00:00', '2150-09-05 00:00:00', '2150-09-05 00:00:00', '2150-09-05 00:00:00', 1)\n",
            "(9501, 10042, 'M', '2076-05-06 00:00:00', '2150-12-03 00:00:00', None, '2150-12-03 00:00:00', 1)\n",
            "(9502, 10043, 'M', '2109-04-07 00:00:00', '2191-02-07 00:00:00', None, '2191-02-07 00:00:00', 1)\n",
            "(9503, 10044, 'F', '2071-02-11 00:00:00', '2152-10-20 00:00:00', '2152-10-20 00:00:00', '2152-10-20 00:00:00', 1)\n",
            "(9504, 10045, 'F', '2061-03-25 00:00:00', '2129-12-01 00:00:00', '2129-12-01 00:00:00', '2129-12-01 00:00:00', 1)\n",
            "(9505, 10046, 'F', '2141-03-15 00:00:00', '2195-03-13 00:00:00', None, '2195-03-13 00:00:00', 1)\n",
            "(9514, 10056, 'F', '2046-02-27 00:00:00', '2129-09-16 00:00:00', None, '2129-09-16 00:00:00', 1)\n",
            "(9517, 10059, 'M', '2081-01-03 00:00:00', '2150-08-29 00:00:00', '2150-08-29 00:00:00', '2150-08-29 00:00:00', 1)\n",
            "(9519, 10061, 'F', '2031-05-19 00:00:00', '2108-04-03 00:00:00', None, '2108-04-03 00:00:00', 1)\n",
            "(9522, 10064, 'M', '2058-04-23 00:00:00', '2127-03-19 00:00:00', '2127-03-19 00:00:00', None, 1)\n",
            "(9523, 10065, 'F', '2111-07-18 00:00:00', '2193-11-06 00:00:00', '2193-11-06 00:00:00', '2193-11-06 00:00:00', 1)\n",
            "(9525, 10067, 'M', '2101-06-10 00:00:00', '2130-10-06 00:00:00', '2130-10-06 00:00:00', None, 1)\n",
            "(9527, 10069, 'F', '2146-10-23 00:00:00', '2188-02-27 00:00:00', '2188-02-27 00:00:00', '2188-02-27 00:00:00', 1)\n",
            "(9531, 10074, 'M', '2081-12-26 00:00:00', '2167-04-24 00:00:00', None, '2167-04-24 00:00:00', 1)\n",
            "(9533, 10076, 'M', '2038-05-10 00:00:00', '2107-03-30 00:00:00', '2107-03-30 00:00:00', '2107-03-30 00:00:00', 1)\n",
            "(9539, 10083, 'F', '2110-03-25 00:00:00', '2192-12-12 00:00:00', None, '2192-12-12 00:00:00', 1)\n",
            "(9544, 10088, 'M', '2029-07-09 00:00:00', '2107-07-20 00:00:00', None, '2107-07-20 00:00:00', 1)\n",
            "(9545, 10089, 'M', '2046-04-18 00:00:00', '2132-08-08 00:00:00', '2132-08-08 00:00:00', '2132-08-08 00:00:00', 1)\n",
            "(9546, 10090, 'M', '2096-02-27 00:00:00', '2126-04-06 00:00:00', None, '2126-04-06 00:00:00', 1)\n",
            "(9549, 10093, 'M', '2053-09-08 00:00:00', '2141-01-26 00:00:00', '2141-01-26 00:00:00', '2141-01-26 00:00:00', 1)\n",
            "(9550, 10094, 'M', '1880-02-29 00:00:00', '2180-03-20 00:00:00', '2180-03-20 00:00:00', '2180-03-20 00:00:00', 1)\n",
            "(9553, 10098, 'F', '2150-12-07 00:00:00', '2170-12-03 00:00:00', '2170-12-03 00:00:00', None, 1)\n",
            "(9556, 10101, 'M', '2055-07-18 00:00:00', '2128-03-24 00:00:00', '2128-03-24 00:00:00', None, 1)\n",
            "(9557, 10102, 'M', '2035-04-13 00:00:00', '2105-06-11 00:00:00', '2105-06-11 00:00:00', '2105-06-11 00:00:00', 1)\n",
            "(9559, 10104, 'F', '2050-02-16 00:00:00', '2125-05-09 00:00:00', None, '2125-05-09 00:00:00', 1)\n",
            "(9561, 10106, 'M', '2097-12-16 00:00:00', '2163-09-15 00:00:00', None, '2163-09-15 00:00:00', 1)\n",
            "(9566, 10111, 'F', '2097-01-16 00:00:00', '2180-02-01 00:00:00', '2180-02-01 00:00:00', '2180-02-01 00:00:00', 1)\n",
            "(9567, 10112, 'F', '2069-05-05 00:00:00', '2148-01-19 00:00:00', '2148-01-19 00:00:00', '2148-01-19 00:00:00', 1)\n",
            "(9569, 10114, 'F', '2099-03-17 00:00:00', '2178-05-02 00:00:00', '2178-05-02 00:00:00', '2178-05-02 00:00:00', 1)\n",
            "(9572, 10117, 'F', '2072-05-05 00:00:00', '2138-11-18 00:00:00', '2138-11-18 00:00:00', None, 1)\n",
            "(9574, 10119, 'M', '2036-03-10 00:00:00', '2120-09-18 00:00:00', None, '2120-09-18 00:00:00', 1)\n",
            "(9575, 10120, 'M', '2088-05-05 00:00:00', '2115-05-15 00:00:00', '2115-05-15 00:00:00', None, 1)\n",
            "(9579, 10124, 'F', '2108-12-20 00:00:00', '2192-05-15 00:00:00', '2192-05-15 00:00:00', '2192-05-15 00:00:00', 1)\n",
            "(9581, 10126, 'F', '2127-06-04 00:00:00', '2171-08-16 00:00:00', '2171-08-16 00:00:00', '2171-08-16 00:00:00', 1)\n",
            "(9582, 10127, 'F', '2181-04-19 00:00:00', '2201-11-08 00:00:00', None, '2201-11-08 00:00:00', 1)\n",
            "(9585, 10130, 'M', '2109-07-08 00:00:00', '2161-03-13 00:00:00', '2161-03-13 00:00:00', '2161-03-13 00:00:00', 1)\n",
            "(9587, 10132, 'F', '2058-04-23 00:00:00', '2123-11-06 00:00:00', '2123-11-06 00:00:00', '2123-11-06 00:00:00', 1)\n",
            "(30831, 40124, 'F', '2063-07-05 00:00:00', '2130-11-03 00:00:00', '2130-11-03 00:00:00', '2130-11-03 00:00:00', 1)\n",
            "(30839, 40177, 'M', '2082-06-27 00:00:00', '2169-05-12 00:00:00', '2169-05-12 00:00:00', None, 1)\n",
            "(30848, 40204, 'M', '2079-08-17 00:00:00', '2156-02-08 00:00:00', '2156-02-08 00:00:00', '2156-02-08 00:00:00', 1)\n",
            "(30865, 40277, 'F', '2070-10-11 00:00:00', '2154-01-22 00:00:00', None, '2154-01-22 00:00:00', 1)\n",
            "(30866, 40286, 'F', '2110-04-02 00:00:00', '2196-10-22 00:00:00', None, '2196-10-22 00:00:00', 1)\n",
            "(30873, 40304, 'M', '2086-12-16 00:00:00', '2165-01-17 00:00:00', '2165-01-17 00:00:00', '2165-01-17 00:00:00', 1)\n",
            "(30876, 40310, 'M', '2103-12-05 00:00:00', '2144-12-31 00:00:00', '2144-12-31 00:00:00', None, 1)\n",
            "(30908, 40456, 'F', '2031-08-12 00:00:00', '2120-08-13 00:00:00', None, '2120-08-13 00:00:00', 1)\n",
            "(30924, 40503, 'F', '2097-11-14 00:00:00', '2186-07-07 00:00:00', '2186-07-07 00:00:00', None, 1)\n",
            "(30946, 40595, 'F', '2068-03-04 00:00:00', '2144-10-31 00:00:00', '2144-10-31 00:00:00', '2144-10-31 00:00:00', 1)\n",
            "(30950, 40601, 'F', '2112-01-20 00:00:00', '2184-08-26 00:00:00', None, '2184-08-26 00:00:00', 1)\n",
            "(30954, 40612, 'F', '2073-08-13 00:00:00', '2159-11-25 00:00:00', None, '2159-11-25 00:00:00', 1)\n",
            "(30962, 40655, 'F', '1844-07-18 00:00:00', '2145-03-07 00:00:00', '2145-03-07 00:00:00', None, 1)\n",
            "(30968, 40687, 'F', '2073-06-05 00:00:00', '2155-03-12 00:00:00', '2155-03-12 00:00:00', '2155-03-12 00:00:00', 1)\n",
            "(31277, 41795, 'M', '2096-07-25 00:00:00', '2145-12-06 00:00:00', None, '2145-12-06 00:00:00', 1)\n",
            "(31300, 41914, 'M', '2090-11-16 00:00:00', '2146-01-25 00:00:00', '2146-01-25 00:00:00', '2146-01-25 00:00:00', 1)\n",
            "(31311, 41976, 'M', '2136-07-28 00:00:00', '2202-12-05 00:00:00', None, '2202-12-05 00:00:00', 1)\n",
            "(31314, 41983, 'F', '1851-09-12 00:00:00', '2151-09-15 00:00:00', '2151-09-15 00:00:00', None, 1)\n",
            "(31323, 42033, 'F', '2045-10-07 00:00:00', '2131-07-28 00:00:00', '2131-07-28 00:00:00', None, 1)\n",
            "(31332, 42066, 'M', '2061-06-13 00:00:00', '2112-02-11 00:00:00', '2112-02-11 00:00:00', None, 1)\n",
            "(31337, 42075, 'M', '2086-02-04 00:00:00', '2166-02-26 00:00:00', '2166-02-26 00:00:00', '2166-02-26 00:00:00', 1)\n",
            "(31350, 42135, 'M', '2083-09-20 00:00:00', '2127-10-28 00:00:00', '2127-10-28 00:00:00', '2127-10-28 00:00:00', 1)\n",
            "(31372, 42199, 'F', '2044-06-27 00:00:00', '2117-04-04 00:00:00', '2117-04-04 00:00:00', None, 1)\n",
            "(31379, 42231, 'F', '2016-12-05 00:00:00', '2105-05-18 00:00:00', '2105-05-18 00:00:00', '2103-05-18 00:00:00', 1)\n",
            "(31391, 42275, 'M', '2058-08-04 00:00:00', '2136-03-26 00:00:00', '2136-03-26 00:00:00', None, 1)\n",
            "(31392, 42281, 'F', '2051-03-23 00:00:00', '2119-11-14 00:00:00', '2119-11-14 00:00:00', '2119-11-14 00:00:00', 1)\n",
            "(31395, 42292, 'M', '2098-04-29 00:00:00', '2165-01-16 00:00:00', None, '2165-01-16 00:00:00', 1)\n",
            "(31397, 42302, 'F', '2074-09-29 00:00:00', '2160-06-08 00:00:00', None, '2160-06-08 00:00:00', 1)\n",
            "(31401, 42321, 'F', '2041-05-16 00:00:00', '2122-02-28 00:00:00', '2122-02-28 00:00:00', '2122-02-28 00:00:00', 1)\n",
            "(31406, 42346, 'F', '2072-12-03 00:00:00', '2160-12-27 00:00:00', '2160-12-27 00:00:00', '2160-12-27 00:00:00', 1)\n",
            "(31413, 42367, 'F', '2060-02-12 00:00:00', '2147-11-04 00:00:00', '2147-11-04 00:00:00', '2147-11-04 00:00:00', 1)\n",
            "(31427, 42412, 'M', '2079-01-29 00:00:00', '2166-05-30 00:00:00', '2166-05-30 00:00:00', None, 1)\n",
            "(31429, 42430, 'M', '2061-12-10 00:00:00', '2142-11-30 00:00:00', '2142-11-30 00:00:00', '2142-11-30 00:00:00', 1)\n",
            "(31440, 42458, 'M', '1846-07-21 00:00:00', '2147-09-08 00:00:00', '2147-09-08 00:00:00', None, 1)\n",
            "(31752, 43735, 'M', '2046-07-05 00:00:00', '2128-11-09 00:00:00', '2128-11-09 00:00:00', None, 1)\n",
            "(31758, 43746, 'F', '2029-12-07 00:00:00', '2111-01-14 00:00:00', '2111-01-14 00:00:00', None, 1)\n",
            "(31759, 43748, 'M', '2099-09-02 00:00:00', '2181-02-27 00:00:00', '2181-02-27 00:00:00', '2181-02-27 00:00:00', 1)\n",
            "(31765, 43779, 'M', '2097-01-07 00:00:00', '2173-12-24 00:00:00', None, '2173-12-24 00:00:00', 1)\n",
            "(31771, 43798, 'M', '2136-07-29 00:00:00', '2200-12-31 00:00:00', '2200-12-31 00:00:00', '2200-12-31 00:00:00', 1)\n",
            "(31778, 43827, 'F', '1876-07-14 00:00:00', '2178-12-07 00:00:00', '2178-12-07 00:00:00', '2178-12-07 00:00:00', 1)\n",
            "(31784, 43870, 'F', '2097-05-16 00:00:00', '2186-02-12 00:00:00', '2186-02-12 00:00:00', '2186-02-12 00:00:00', 1)\n",
            "(31789, 43879, 'F', '2051-07-25 00:00:00', '2107-07-13 00:00:00', '2107-07-13 00:00:00', None, 1)\n",
            "(31790, 43881, 'M', '2051-03-24 00:00:00', '2105-02-16 00:00:00', '2105-02-16 00:00:00', '2105-02-16 00:00:00', 1)\n",
            "(31794, 43909, 'F', '2073-11-22 00:00:00', '2152-10-09 00:00:00', '2152-10-09 00:00:00', '2152-10-09 00:00:00', 1)\n",
            "(31800, 43927, 'F', '2104-02-12 00:00:00', '2176-09-09 00:00:00', '2176-09-09 00:00:00', '2176-09-09 00:00:00', 1)\n",
            "(31838, 44083, 'M', '2057-11-15 00:00:00', '2114-02-20 00:00:00', '2114-02-20 00:00:00', '2114-02-20 00:00:00', 1)\n",
            "(31853, 44154, 'M', '1878-05-14 00:00:00', '2178-05-15 00:00:00', '2178-05-15 00:00:00', '2178-05-15 00:00:00', 1)\n",
            "(31867, 44212, 'F', '2078-06-16 00:00:00', '2124-01-29 00:00:00', None, '2124-01-29 00:00:00', 1)\n",
            "(31870, 44222, 'M', '2107-06-27 00:00:00', '2182-08-03 00:00:00', '2182-08-03 00:00:00', None, 1)\n",
            "(31872, 44228, 'F', '2112-10-22 00:00:00', '2171-04-14 00:00:00', '2171-04-14 00:00:00', None, 1)\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "jeLUATW7TpVP",
        "colab_type": "text"
      },
      "source": [
        "##### Pull specific columns from a table using FROM and SELECT\n",
        "\n",
        "This is a pandas based SQL query for comparison. Though in this query, specific columns (subject_id, gender, dob, and dod_hosp) are requested from the patients table, rather than all of the columns from this table. "
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "YiCc169pX1RR",
        "colab_type": "code",
        "outputId": "887a5467-0f8e-40ff-dc36-8b2c3f68fa97",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 419
        }
      },
      "source": [
        "pd.read_sql_query(\n",
        "    '''SELECT subject_id, gender, dob, dod_hosp \n",
        "    FROM patients;''',\n",
        "    conn) # conn, here, connects this query to the database rather than the cursor. \n",
        "    #The cursor is built in to the pandas function, so we do not need the extra specification, as is needed with the sqlite3 library."
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>subject_id</th>\n",
              "      <th>gender</th>\n",
              "      <th>dob</th>\n",
              "      <th>dod_hosp</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>10006</td>\n",
              "      <td>F</td>\n",
              "      <td>2094-03-05 00:00:00</td>\n",
              "      <td>2165-08-12 00:00:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>10011</td>\n",
              "      <td>F</td>\n",
              "      <td>2090-06-05 00:00:00</td>\n",
              "      <td>2126-08-28 00:00:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>10013</td>\n",
              "      <td>F</td>\n",
              "      <td>2038-09-03 00:00:00</td>\n",
              "      <td>2125-10-07 00:00:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>10017</td>\n",
              "      <td>F</td>\n",
              "      <td>2075-09-21 00:00:00</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>10019</td>\n",
              "      <td>M</td>\n",
              "      <td>2114-06-20 00:00:00</td>\n",
              "      <td>2163-05-15 00:00:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>95</th>\n",
              "      <td>44083</td>\n",
              "      <td>M</td>\n",
              "      <td>2057-11-15 00:00:00</td>\n",
              "      <td>2114-02-20 00:00:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>96</th>\n",
              "      <td>44154</td>\n",
              "      <td>M</td>\n",
              "      <td>1878-05-14 00:00:00</td>\n",
              "      <td>2178-05-15 00:00:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>97</th>\n",
              "      <td>44212</td>\n",
              "      <td>F</td>\n",
              "      <td>2078-06-16 00:00:00</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>98</th>\n",
              "      <td>44222</td>\n",
              "      <td>M</td>\n",
              "      <td>2107-06-27 00:00:00</td>\n",
              "      <td>2182-08-03 00:00:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>99</th>\n",
              "      <td>44228</td>\n",
              "      <td>F</td>\n",
              "      <td>2112-10-22 00:00:00</td>\n",
              "      <td>2171-04-14 00:00:00</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>100 rows × 4 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "    subject_id gender                  dob             dod_hosp\n",
              "0        10006      F  2094-03-05 00:00:00  2165-08-12 00:00:00\n",
              "1        10011      F  2090-06-05 00:00:00  2126-08-28 00:00:00\n",
              "2        10013      F  2038-09-03 00:00:00  2125-10-07 00:00:00\n",
              "3        10017      F  2075-09-21 00:00:00                 None\n",
              "4        10019      M  2114-06-20 00:00:00  2163-05-15 00:00:00\n",
              "..         ...    ...                  ...                  ...\n",
              "95       44083      M  2057-11-15 00:00:00  2114-02-20 00:00:00\n",
              "96       44154      M  1878-05-14 00:00:00  2178-05-15 00:00:00\n",
              "97       44212      F  2078-06-16 00:00:00                 None\n",
              "98       44222      M  2107-06-27 00:00:00  2182-08-03 00:00:00\n",
              "99       44228      F  2112-10-22 00:00:00  2171-04-14 00:00:00\n",
              "\n",
              "[100 rows x 4 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 16
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Sa4-bm4uTpcm",
        "colab_type": "text"
      },
      "source": [
        "##### Pull data based on specified parameters from a single table using the WHERE clause\n",
        "\n",
        "WHERE clause operators include:\n",
        "\n",
        "\n",
        "*   = : equal\n",
        "*   \\> : greater than\n",
        "*   < : less than\n",
        "*   \\>= : greater than or equal\n",
        "*   <= : less than or equal\n",
        "*   <> or != : not equal\n",
        "*   BETWEEN : between a certain range\n",
        "*   LIKE : search for a pattern (%pattern string%)\n",
        "*   IN : speficic value pattern\n",
        "\n",
        "These allow you to call data based on a value. Maybe you want a particilar time or age range, or maybe you want to include only a certain insurance type.\n",
        "\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Z1B87KgwbQfh",
        "colab_type": "text"
      },
      "source": [
        "##### This query will return the columns subject_id, insurance, diagnosis, and admission_location from the admissions table, including only rows that have SEPSIS as the value within the diagnosis column."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "x2-kCYMtcuIk",
        "colab_type": "code",
        "outputId": "29ee232a-2c99-4ea7-ec7f-1cd8e7879087",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 359
        }
      },
      "source": [
        "pd.read_sql_query('''SELECT subject_id, insurance, diagnosis, admission_location FROM admissions WHERE diagnosis = \"SEPSIS\";''',conn)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>subject_id</th>\n",
              "      <th>insurance</th>\n",
              "      <th>diagnosis</th>\n",
              "      <th>admission_location</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>10006</td>\n",
              "      <td>Medicare</td>\n",
              "      <td>SEPSIS</td>\n",
              "      <td>EMERGENCY ROOM ADMIT</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>10013</td>\n",
              "      <td>Medicare</td>\n",
              "      <td>SEPSIS</td>\n",
              "      <td>TRANSFER FROM HOSP/EXTRAM</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>10036</td>\n",
              "      <td>Medicare</td>\n",
              "      <td>SEPSIS</td>\n",
              "      <td>EMERGENCY ROOM ADMIT</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>10056</td>\n",
              "      <td>Medicare</td>\n",
              "      <td>SEPSIS</td>\n",
              "      <td>TRANSFER FROM HOSP/EXTRAM</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>40601</td>\n",
              "      <td>Private</td>\n",
              "      <td>SEPSIS</td>\n",
              "      <td>EMERGENCY ROOM ADMIT</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>41976</td>\n",
              "      <td>Medicare</td>\n",
              "      <td>SEPSIS</td>\n",
              "      <td>EMERGENCY ROOM ADMIT</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>41976</td>\n",
              "      <td>Medicare</td>\n",
              "      <td>SEPSIS</td>\n",
              "      <td>CLINIC REFERRAL/PREMATURE</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>41976</td>\n",
              "      <td>Medicare</td>\n",
              "      <td>SEPSIS</td>\n",
              "      <td>CLINIC REFERRAL/PREMATURE</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>41976</td>\n",
              "      <td>Medicare</td>\n",
              "      <td>SEPSIS</td>\n",
              "      <td>CLINIC REFERRAL/PREMATURE</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>41976</td>\n",
              "      <td>Medicare</td>\n",
              "      <td>SEPSIS</td>\n",
              "      <td>CLINIC REFERRAL/PREMATURE</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   subject_id insurance diagnosis         admission_location\n",
              "0       10006  Medicare    SEPSIS       EMERGENCY ROOM ADMIT\n",
              "1       10013  Medicare    SEPSIS  TRANSFER FROM HOSP/EXTRAM\n",
              "2       10036  Medicare    SEPSIS       EMERGENCY ROOM ADMIT\n",
              "3       10056  Medicare    SEPSIS  TRANSFER FROM HOSP/EXTRAM\n",
              "4       40601   Private    SEPSIS       EMERGENCY ROOM ADMIT\n",
              "5       41976  Medicare    SEPSIS       EMERGENCY ROOM ADMIT\n",
              "6       41976  Medicare    SEPSIS  CLINIC REFERRAL/PREMATURE\n",
              "7       41976  Medicare    SEPSIS  CLINIC REFERRAL/PREMATURE\n",
              "8       41976  Medicare    SEPSIS  CLINIC REFERRAL/PREMATURE\n",
              "9       41976  Medicare    SEPSIS  CLINIC REFERRAL/PREMATURE"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 17
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "42nel2EdblmO",
        "colab_type": "text"
      },
      "source": [
        "##### This query will return the same columns as above, but with less exact parameters\n",
        "\n",
        "Using LIKE instead of =, and adding \"%\" around our string SEPSIS, allows us to see SEPSIS + other strings."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "v0oMmVuCPGsw",
        "colab_type": "code",
        "outputId": "1288a725-2b28-4776-e1c1-d3b96c4fa7bc",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 483
        }
      },
      "source": [
        "pd.read_sql_query('SELECT subject_id, insurance, diagnosis, admission_location FROM admissions WHERE diagnosis LIKE \"%SEPSIS%\";',conn)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>subject_id</th>\n",
              "      <th>insurance</th>\n",
              "      <th>diagnosis</th>\n",
              "      <th>admission_location</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>10006</td>\n",
              "      <td>Medicare</td>\n",
              "      <td>SEPSIS</td>\n",
              "      <td>EMERGENCY ROOM ADMIT</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>10013</td>\n",
              "      <td>Medicare</td>\n",
              "      <td>SEPSIS</td>\n",
              "      <td>TRANSFER FROM HOSP/EXTRAM</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>10036</td>\n",
              "      <td>Medicare</td>\n",
              "      <td>SEPSIS</td>\n",
              "      <td>EMERGENCY ROOM ADMIT</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>10056</td>\n",
              "      <td>Medicare</td>\n",
              "      <td>SEPSIS</td>\n",
              "      <td>TRANSFER FROM HOSP/EXTRAM</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>10088</td>\n",
              "      <td>Medicare</td>\n",
              "      <td>SEPSIS;PNEUMONIA;TELEMETRY</td>\n",
              "      <td>EMERGENCY ROOM ADMIT</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>10088</td>\n",
              "      <td>Private</td>\n",
              "      <td>UROSEPSIS</td>\n",
              "      <td>TRANSFER FROM HOSP/EXTRAM</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>10094</td>\n",
              "      <td>Medicare</td>\n",
              "      <td>SEPSIS;TELEMETRY</td>\n",
              "      <td>EMERGENCY ROOM ADMIT</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>40601</td>\n",
              "      <td>Private</td>\n",
              "      <td>SEPSIS</td>\n",
              "      <td>EMERGENCY ROOM ADMIT</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>41976</td>\n",
              "      <td>Medicare</td>\n",
              "      <td>SEPSIS</td>\n",
              "      <td>EMERGENCY ROOM ADMIT</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>41976</td>\n",
              "      <td>Medicare</td>\n",
              "      <td>SEPSIS</td>\n",
              "      <td>CLINIC REFERRAL/PREMATURE</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>41976</td>\n",
              "      <td>Medicare</td>\n",
              "      <td>SEPSIS</td>\n",
              "      <td>CLINIC REFERRAL/PREMATURE</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>41976</td>\n",
              "      <td>Medicare</td>\n",
              "      <td>SEPSIS; UTI</td>\n",
              "      <td>CLINIC REFERRAL/PREMATURE</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>12</th>\n",
              "      <td>41976</td>\n",
              "      <td>Medicare</td>\n",
              "      <td>SEPSIS</td>\n",
              "      <td>CLINIC REFERRAL/PREMATURE</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>13</th>\n",
              "      <td>41976</td>\n",
              "      <td>Medicare</td>\n",
              "      <td>SEPSIS</td>\n",
              "      <td>CLINIC REFERRAL/PREMATURE</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "    subject_id insurance                   diagnosis         admission_location\n",
              "0        10006  Medicare                      SEPSIS       EMERGENCY ROOM ADMIT\n",
              "1        10013  Medicare                      SEPSIS  TRANSFER FROM HOSP/EXTRAM\n",
              "2        10036  Medicare                      SEPSIS       EMERGENCY ROOM ADMIT\n",
              "3        10056  Medicare                      SEPSIS  TRANSFER FROM HOSP/EXTRAM\n",
              "4        10088  Medicare  SEPSIS;PNEUMONIA;TELEMETRY       EMERGENCY ROOM ADMIT\n",
              "5        10088   Private                   UROSEPSIS  TRANSFER FROM HOSP/EXTRAM\n",
              "6        10094  Medicare            SEPSIS;TELEMETRY       EMERGENCY ROOM ADMIT\n",
              "7        40601   Private                      SEPSIS       EMERGENCY ROOM ADMIT\n",
              "8        41976  Medicare                      SEPSIS       EMERGENCY ROOM ADMIT\n",
              "9        41976  Medicare                      SEPSIS  CLINIC REFERRAL/PREMATURE\n",
              "10       41976  Medicare                      SEPSIS  CLINIC REFERRAL/PREMATURE\n",
              "11       41976  Medicare                 SEPSIS; UTI  CLINIC REFERRAL/PREMATURE\n",
              "12       41976  Medicare                      SEPSIS  CLINIC REFERRAL/PREMATURE\n",
              "13       41976  Medicare                      SEPSIS  CLINIC REFERRAL/PREMATURE"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 18
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ycqoCrW4Umr5",
        "colab_type": "text"
      },
      "source": [
        "##### Pull data based on multiple parameters\n",
        "\n",
        "You can multiple conditional parameters by adding AND or OR between them. The query below returns specified columns from the microbiology_events table, where rows contain either BLOOD CULTURE as the spec_type_desc AND a dilution_value greater than or equal to 6."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "f7LRBnMoUltf",
        "colab_type": "code",
        "outputId": "ebbefe6d-08a4-47f3-fa98-ebf6ef0a82e5",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 979
        }
      },
      "source": [
        "pd.read_sql_query('SELECT row_id, spec_itemid, dilution_value, org_name FROM microbiology_events WHERE spec_type_desc = \"BLOOD CULTURE\" AND dilution_value >= 6;', conn)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>row_id</th>\n",
              "      <th>spec_itemid</th>\n",
              "      <th>dilution_value</th>\n",
              "      <th>org_name</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>134700</td>\n",
              "      <td>70012</td>\n",
              "      <td>8.0</td>\n",
              "      <td>STAPHYLOCOCCUS, COAGULASE NEGATIVE</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>134802</td>\n",
              "      <td>70012</td>\n",
              "      <td>8.0</td>\n",
              "      <td>GRAM POSITIVE COCCUS(COCCI)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>134885</td>\n",
              "      <td>70012</td>\n",
              "      <td>32.0</td>\n",
              "      <td>ESCHERICHIA COLI</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>134887</td>\n",
              "      <td>70012</td>\n",
              "      <td>8.0</td>\n",
              "      <td>ESCHERICHIA COLI</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>134888</td>\n",
              "      <td>70012</td>\n",
              "      <td>16.0</td>\n",
              "      <td>ESCHERICHIA COLI</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>134896</td>\n",
              "      <td>70012</td>\n",
              "      <td>8.0</td>\n",
              "      <td>ENTEROCOCCUS SP.</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>134897</td>\n",
              "      <td>70012</td>\n",
              "      <td>16.0</td>\n",
              "      <td>ENTEROCOCCUS SP.</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>135478</td>\n",
              "      <td>70012</td>\n",
              "      <td>8.0</td>\n",
              "      <td>STAPHYLOCOCCUS, COAGULASE NEGATIVE</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>135479</td>\n",
              "      <td>70012</td>\n",
              "      <td>16.0</td>\n",
              "      <td>STAPHYLOCOCCUS, COAGULASE NEGATIVE</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>135484</td>\n",
              "      <td>70012</td>\n",
              "      <td>8.0</td>\n",
              "      <td>STAPHYLOCOCCUS, COAGULASE NEGATIVE</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>135497</td>\n",
              "      <td>70012</td>\n",
              "      <td>32.0</td>\n",
              "      <td>ENTEROCOCCUS FAECIUM</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>135500</td>\n",
              "      <td>70012</td>\n",
              "      <td>32.0</td>\n",
              "      <td>ENTEROCOCCUS FAECIUM</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>12</th>\n",
              "      <td>135959</td>\n",
              "      <td>70012</td>\n",
              "      <td>8.0</td>\n",
              "      <td>STAPH AUREUS COAG +</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>13</th>\n",
              "      <td>135962</td>\n",
              "      <td>70012</td>\n",
              "      <td>8.0</td>\n",
              "      <td>STAPH AUREUS COAG +</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14</th>\n",
              "      <td>135963</td>\n",
              "      <td>70012</td>\n",
              "      <td>8.0</td>\n",
              "      <td>STAPH AUREUS COAG +</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>15</th>\n",
              "      <td>136011</td>\n",
              "      <td>70012</td>\n",
              "      <td>8.0</td>\n",
              "      <td>STAPHYLOCOCCUS, COAGULASE NEGATIVE</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>16</th>\n",
              "      <td>136017</td>\n",
              "      <td>70012</td>\n",
              "      <td>8.0</td>\n",
              "      <td>STAPHYLOCOCCUS, COAGULASE NEGATIVE</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>17</th>\n",
              "      <td>136018</td>\n",
              "      <td>70012</td>\n",
              "      <td>8.0</td>\n",
              "      <td>STAPHYLOCOCCUS, COAGULASE NEGATIVE</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>18</th>\n",
              "      <td>432272</td>\n",
              "      <td>70012</td>\n",
              "      <td>8.0</td>\n",
              "      <td>PROTEUS MIRABILIS</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>19</th>\n",
              "      <td>432273</td>\n",
              "      <td>70012</td>\n",
              "      <td>16.0</td>\n",
              "      <td>PROTEUS MIRABILIS</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>20</th>\n",
              "      <td>432274</td>\n",
              "      <td>70012</td>\n",
              "      <td>8.0</td>\n",
              "      <td>PROTEUS MIRABILIS</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>21</th>\n",
              "      <td>432278</td>\n",
              "      <td>70012</td>\n",
              "      <td>32.0</td>\n",
              "      <td>PROTEUS MIRABILIS</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>22</th>\n",
              "      <td>432279</td>\n",
              "      <td>70012</td>\n",
              "      <td>32.0</td>\n",
              "      <td>PROTEUS MIRABILIS</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>23</th>\n",
              "      <td>433264</td>\n",
              "      <td>70012</td>\n",
              "      <td>8.0</td>\n",
              "      <td>KLEBSIELLA PNEUMONIAE</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>24</th>\n",
              "      <td>439843</td>\n",
              "      <td>70012</td>\n",
              "      <td>128.0</td>\n",
              "      <td>ESCHERICHIA COLI</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>25</th>\n",
              "      <td>439844</td>\n",
              "      <td>70012</td>\n",
              "      <td>16.0</td>\n",
              "      <td>ESCHERICHIA COLI</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>26</th>\n",
              "      <td>439845</td>\n",
              "      <td>70012</td>\n",
              "      <td>16.0</td>\n",
              "      <td>ESCHERICHIA COLI</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>27</th>\n",
              "      <td>439850</td>\n",
              "      <td>70012</td>\n",
              "      <td>16.0</td>\n",
              "      <td>ESCHERICHIA COLI</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>28</th>\n",
              "      <td>439852</td>\n",
              "      <td>70012</td>\n",
              "      <td>32.0</td>\n",
              "      <td>ESCHERICHIA COLI</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>29</th>\n",
              "      <td>439854</td>\n",
              "      <td>70012</td>\n",
              "      <td>16.0</td>\n",
              "      <td>ESCHERICHIA COLI</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "    row_id  spec_itemid  dilution_value                            org_name\n",
              "0   134700        70012             8.0  STAPHYLOCOCCUS, COAGULASE NEGATIVE\n",
              "1   134802        70012             8.0         GRAM POSITIVE COCCUS(COCCI)\n",
              "2   134885        70012            32.0                    ESCHERICHIA COLI\n",
              "3   134887        70012             8.0                    ESCHERICHIA COLI\n",
              "4   134888        70012            16.0                    ESCHERICHIA COLI\n",
              "5   134896        70012             8.0                    ENTEROCOCCUS SP.\n",
              "6   134897        70012            16.0                    ENTEROCOCCUS SP.\n",
              "7   135478        70012             8.0  STAPHYLOCOCCUS, COAGULASE NEGATIVE\n",
              "8   135479        70012            16.0  STAPHYLOCOCCUS, COAGULASE NEGATIVE\n",
              "9   135484        70012             8.0  STAPHYLOCOCCUS, COAGULASE NEGATIVE\n",
              "10  135497        70012            32.0                ENTEROCOCCUS FAECIUM\n",
              "11  135500        70012            32.0                ENTEROCOCCUS FAECIUM\n",
              "12  135959        70012             8.0                 STAPH AUREUS COAG +\n",
              "13  135962        70012             8.0                 STAPH AUREUS COAG +\n",
              "14  135963        70012             8.0                 STAPH AUREUS COAG +\n",
              "15  136011        70012             8.0  STAPHYLOCOCCUS, COAGULASE NEGATIVE\n",
              "16  136017        70012             8.0  STAPHYLOCOCCUS, COAGULASE NEGATIVE\n",
              "17  136018        70012             8.0  STAPHYLOCOCCUS, COAGULASE NEGATIVE\n",
              "18  432272        70012             8.0                   PROTEUS MIRABILIS\n",
              "19  432273        70012            16.0                   PROTEUS MIRABILIS\n",
              "20  432274        70012             8.0                   PROTEUS MIRABILIS\n",
              "21  432278        70012            32.0                   PROTEUS MIRABILIS\n",
              "22  432279        70012            32.0                   PROTEUS MIRABILIS\n",
              "23  433264        70012             8.0               KLEBSIELLA PNEUMONIAE\n",
              "24  439843        70012           128.0                    ESCHERICHIA COLI\n",
              "25  439844        70012            16.0                    ESCHERICHIA COLI\n",
              "26  439845        70012            16.0                    ESCHERICHIA COLI\n",
              "27  439850        70012            16.0                    ESCHERICHIA COLI\n",
              "28  439852        70012            32.0                    ESCHERICHIA COLI\n",
              "29  439854        70012            16.0                    ESCHERICHIA COLI"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 19
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "DK1Z9NBuSWy3",
        "colab_type": "text"
      },
      "source": [
        "##### Find distinct data occurances with DISTINCT\n",
        "\n",
        "This will return unique values from a column. This can be a quick way to see all of the categories represented in a dataset."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "NEkMaSxTROy-",
        "colab_type": "code",
        "outputId": "7e1ce021-7127-4159-e3a4-832a620bcedf",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 221
        }
      },
      "source": [
        "pd.read_sql_query('SELECT DISTINCT admission_type FROM admissions;', conn)\n",
        "pd.read_sql_query('SELECT DISTINCT admission_location FROM admissions;', conn)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>admission_location</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>EMERGENCY ROOM ADMIT</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>TRANSFER FROM HOSP/EXTRAM</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>PHYS REFERRAL/NORMAL DELI</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>CLINIC REFERRAL/PREMATURE</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>TRANSFER FROM SKILLED NUR</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "          admission_location\n",
              "0       EMERGENCY ROOM ADMIT\n",
              "1  TRANSFER FROM HOSP/EXTRAM\n",
              "2  PHYS REFERRAL/NORMAL DELI\n",
              "3  CLINIC REFERRAL/PREMATURE\n",
              "4  TRANSFER FROM SKILLED NUR"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 20
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Tvvmp-Qkd-MP",
        "colab_type": "text"
      },
      "source": [
        "##### Show ordered resulting data\n",
        "\n",
        "Now that the SQL queries are getting longer, I will start breaking them up into multiple lines by enclosing the statement in 3 single quotes (seen below). \n",
        "\n",
        "The query below returns selected columns from the admissions table oreded by admission type (ascending), and admission_location (decending)."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "GSbez1KxeEif",
        "colab_type": "code",
        "outputId": "8cf2d685-0ca8-4632-bf41-317203846624",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 419
        }
      },
      "source": [
        "pd.read_sql_query('''SELECT subject_id, admission_type, admission_location, admittime \n",
        "FROM admissions \n",
        "ORDER BY admission_type ASC, admission_location DESC;''', conn)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>subject_id</th>\n",
              "      <th>admission_type</th>\n",
              "      <th>admission_location</th>\n",
              "      <th>admittime</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>42231</td>\n",
              "      <td>ELECTIVE</td>\n",
              "      <td>PHYS REFERRAL/NORMAL DELI</td>\n",
              "      <td>2102-08-29 07:15:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>44083</td>\n",
              "      <td>ELECTIVE</td>\n",
              "      <td>PHYS REFERRAL/NORMAL DELI</td>\n",
              "      <td>2112-05-04 08:00:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>10035</td>\n",
              "      <td>ELECTIVE</td>\n",
              "      <td>PHYS REFERRAL/NORMAL DELI</td>\n",
              "      <td>2129-03-03 16:06:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>43927</td>\n",
              "      <td>ELECTIVE</td>\n",
              "      <td>PHYS REFERRAL/NORMAL DELI</td>\n",
              "      <td>2175-10-02 12:30:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>10065</td>\n",
              "      <td>ELECTIVE</td>\n",
              "      <td>PHYS REFERRAL/NORMAL DELI</td>\n",
              "      <td>2189-09-08 07:15:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>124</th>\n",
              "      <td>41976</td>\n",
              "      <td>EMERGENCY</td>\n",
              "      <td>CLINIC REFERRAL/PREMATURE</td>\n",
              "      <td>2202-02-15 19:01:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>125</th>\n",
              "      <td>41976</td>\n",
              "      <td>EMERGENCY</td>\n",
              "      <td>CLINIC REFERRAL/PREMATURE</td>\n",
              "      <td>2202-09-16 21:56:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>126</th>\n",
              "      <td>41976</td>\n",
              "      <td>EMERGENCY</td>\n",
              "      <td>CLINIC REFERRAL/PREMATURE</td>\n",
              "      <td>2202-10-03 01:45:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>127</th>\n",
              "      <td>10112</td>\n",
              "      <td>URGENT</td>\n",
              "      <td>TRANSFER FROM HOSP/EXTRAM</td>\n",
              "      <td>2148-01-13 22:32:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>128</th>\n",
              "      <td>10114</td>\n",
              "      <td>URGENT</td>\n",
              "      <td>TRANSFER FROM HOSP/EXTRAM</td>\n",
              "      <td>2171-10-30 19:03:00</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>129 rows × 4 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "     subject_id admission_type         admission_location            admittime\n",
              "0         42231       ELECTIVE  PHYS REFERRAL/NORMAL DELI  2102-08-29 07:15:00\n",
              "1         44083       ELECTIVE  PHYS REFERRAL/NORMAL DELI  2112-05-04 08:00:00\n",
              "2         10035       ELECTIVE  PHYS REFERRAL/NORMAL DELI  2129-03-03 16:06:00\n",
              "3         43927       ELECTIVE  PHYS REFERRAL/NORMAL DELI  2175-10-02 12:30:00\n",
              "4         10065       ELECTIVE  PHYS REFERRAL/NORMAL DELI  2189-09-08 07:15:00\n",
              "..          ...            ...                        ...                  ...\n",
              "124       41976      EMERGENCY  CLINIC REFERRAL/PREMATURE  2202-02-15 19:01:00\n",
              "125       41976      EMERGENCY  CLINIC REFERRAL/PREMATURE  2202-09-16 21:56:00\n",
              "126       41976      EMERGENCY  CLINIC REFERRAL/PREMATURE  2202-10-03 01:45:00\n",
              "127       10112         URGENT  TRANSFER FROM HOSP/EXTRAM  2148-01-13 22:32:00\n",
              "128       10114         URGENT  TRANSFER FROM HOSP/EXTRAM  2171-10-30 19:03:00\n",
              "\n",
              "[129 rows x 4 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 23
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "xqg1Vg_-6kCi",
        "colab_type": "text"
      },
      "source": [
        "## Pulling Data from the MIMIC III Demo Database (Multiple Tables), i.e. Joining\n",
        "\n",
        "Joins in SQL are used to combine columns from two or more tables, based on shared columns containing elements to link (or join) by.\n",
        "\n",
        "View an interactive visualization of join components here: https://joins.spathon.com/"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ROeCfgbXpN4U",
        "colab_type": "text"
      },
      "source": [
        "This next query will return subject_ids from the patients table (patients.subject_id), date of birth, insurance, marital_status FROM the patients table and the admissions table - JOINing these tables together by the subject_id column.\n",
        "\n",
        "Because subject_id appears in both the paitients table and the admissions table, we can JOIN these tables together. Also, because subject_id appears in both tables, we need to specify which column we want SELECTed by indicating tablename.subject_id (patients.subject_id below). \n",
        "\n",
        "The ON specification in the query below establishes this linking value."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "N34SqRW4v1Y6",
        "colab_type": "code",
        "outputId": "ea538f2a-2e6e-486e-ef80-fa3137cec892",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 419
        }
      },
      "source": [
        "#Pull together subject date of birth (from the patients table) with insurance and marital statuses (from admissions table)\n",
        "pd.read_sql_query('''SELECT patients.subject_id, dob, insurance, marital_status \n",
        "FROM patients \n",
        "INNER JOIN admissions \n",
        "ON patients.subject_id = admissions.subject_id;''', conn)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>subject_id</th>\n",
              "      <th>dob</th>\n",
              "      <th>insurance</th>\n",
              "      <th>marital_status</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>10006</td>\n",
              "      <td>2094-03-05 00:00:00</td>\n",
              "      <td>Medicare</td>\n",
              "      <td>SEPARATED</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>10011</td>\n",
              "      <td>2090-06-05 00:00:00</td>\n",
              "      <td>Private</td>\n",
              "      <td>SINGLE</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>10013</td>\n",
              "      <td>2038-09-03 00:00:00</td>\n",
              "      <td>Medicare</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>10017</td>\n",
              "      <td>2075-09-21 00:00:00</td>\n",
              "      <td>Medicare</td>\n",
              "      <td>DIVORCED</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>10019</td>\n",
              "      <td>2114-06-20 00:00:00</td>\n",
              "      <td>Medicare</td>\n",
              "      <td>DIVORCED</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>124</th>\n",
              "      <td>44083</td>\n",
              "      <td>2057-11-15 00:00:00</td>\n",
              "      <td>Private</td>\n",
              "      <td>SINGLE</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>125</th>\n",
              "      <td>44154</td>\n",
              "      <td>1878-05-14 00:00:00</td>\n",
              "      <td>Medicare</td>\n",
              "      <td>MARRIED</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>126</th>\n",
              "      <td>44212</td>\n",
              "      <td>2078-06-16 00:00:00</td>\n",
              "      <td>Medicare</td>\n",
              "      <td>SINGLE</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>127</th>\n",
              "      <td>44222</td>\n",
              "      <td>2107-06-27 00:00:00</td>\n",
              "      <td>Medicare</td>\n",
              "      <td>SINGLE</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>128</th>\n",
              "      <td>44228</td>\n",
              "      <td>2112-10-22 00:00:00</td>\n",
              "      <td>Private</td>\n",
              "      <td>SINGLE</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>129 rows × 4 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "     subject_id                  dob insurance marital_status\n",
              "0         10006  2094-03-05 00:00:00  Medicare      SEPARATED\n",
              "1         10011  2090-06-05 00:00:00   Private         SINGLE\n",
              "2         10013  2038-09-03 00:00:00  Medicare           None\n",
              "3         10017  2075-09-21 00:00:00  Medicare       DIVORCED\n",
              "4         10019  2114-06-20 00:00:00  Medicare       DIVORCED\n",
              "..          ...                  ...       ...            ...\n",
              "124       44083  2057-11-15 00:00:00   Private         SINGLE\n",
              "125       44154  1878-05-14 00:00:00  Medicare        MARRIED\n",
              "126       44212  2078-06-16 00:00:00  Medicare         SINGLE\n",
              "127       44222  2107-06-27 00:00:00  Medicare         SINGLE\n",
              "128       44228  2112-10-22 00:00:00   Private         SINGLE\n",
              "\n",
              "[129 rows x 4 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 24
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "MB34vNQMmTql",
        "colab_type": "text"
      },
      "source": [
        "##### Conditional parameters can still be applied when using JOINed databases\n",
        "\n",
        "The statement below is almost exactly the same as the statement above. The only difference is that only subjects who are denoted as WIDOWED or SINGLE in the marital_status column are included."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_2l4S0Mw-dO2",
        "colab_type": "code",
        "outputId": "f6aca8c0-e52d-41a9-e007-169d8948f17d",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 1000
        }
      },
      "source": [
        "#Refine the query above by only including subjects who are either single or widowed\n",
        "pd.read_sql_query('''SELECT patients.subject_id, marital_status, dob, insurance \n",
        "FROM patients INNER JOIN admissions \n",
        "ON patients.subject_id = admissions.subject_id \n",
        "WHERE marital_status = \"WIDOWED\" OR marital_status = \"SINGLE\";''', conn)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>subject_id</th>\n",
              "      <th>marital_status</th>\n",
              "      <th>dob</th>\n",
              "      <th>insurance</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>10011</td>\n",
              "      <td>SINGLE</td>\n",
              "      <td>2090-06-05 00:00:00</td>\n",
              "      <td>Private</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>10032</td>\n",
              "      <td>WIDOWED</td>\n",
              "      <td>2050-03-29 00:00:00</td>\n",
              "      <td>Medicare</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>10038</td>\n",
              "      <td>WIDOWED</td>\n",
              "      <td>2056-01-27 00:00:00</td>\n",
              "      <td>Medicare</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>10046</td>\n",
              "      <td>SINGLE</td>\n",
              "      <td>2141-03-15 00:00:00</td>\n",
              "      <td>Medicaid</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>10061</td>\n",
              "      <td>WIDOWED</td>\n",
              "      <td>2031-05-19 00:00:00</td>\n",
              "      <td>Medicare</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>10065</td>\n",
              "      <td>SINGLE</td>\n",
              "      <td>2111-07-18 00:00:00</td>\n",
              "      <td>Private</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>10090</td>\n",
              "      <td>SINGLE</td>\n",
              "      <td>2096-02-27 00:00:00</td>\n",
              "      <td>Private</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>10093</td>\n",
              "      <td>WIDOWED</td>\n",
              "      <td>2053-09-08 00:00:00</td>\n",
              "      <td>Medicare</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>10111</td>\n",
              "      <td>WIDOWED</td>\n",
              "      <td>2097-01-16 00:00:00</td>\n",
              "      <td>Medicare</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>10117</td>\n",
              "      <td>SINGLE</td>\n",
              "      <td>2072-05-05 00:00:00</td>\n",
              "      <td>Private</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>10117</td>\n",
              "      <td>SINGLE</td>\n",
              "      <td>2072-05-05 00:00:00</td>\n",
              "      <td>Private</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>10120</td>\n",
              "      <td>SINGLE</td>\n",
              "      <td>2088-05-05 00:00:00</td>\n",
              "      <td>Medicaid</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>12</th>\n",
              "      <td>10124</td>\n",
              "      <td>WIDOWED</td>\n",
              "      <td>2108-12-20 00:00:00</td>\n",
              "      <td>Medicare</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>13</th>\n",
              "      <td>10124</td>\n",
              "      <td>WIDOWED</td>\n",
              "      <td>2108-12-20 00:00:00</td>\n",
              "      <td>Medicare</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14</th>\n",
              "      <td>10126</td>\n",
              "      <td>SINGLE</td>\n",
              "      <td>2127-06-04 00:00:00</td>\n",
              "      <td>Private</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>15</th>\n",
              "      <td>40124</td>\n",
              "      <td>SINGLE</td>\n",
              "      <td>2063-07-05 00:00:00</td>\n",
              "      <td>Medicare</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>16</th>\n",
              "      <td>40124</td>\n",
              "      <td>SINGLE</td>\n",
              "      <td>2063-07-05 00:00:00</td>\n",
              "      <td>Medicare</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>17</th>\n",
              "      <td>40277</td>\n",
              "      <td>WIDOWED</td>\n",
              "      <td>2070-10-11 00:00:00</td>\n",
              "      <td>Medicare</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>18</th>\n",
              "      <td>40310</td>\n",
              "      <td>SINGLE</td>\n",
              "      <td>2103-12-05 00:00:00</td>\n",
              "      <td>Private</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>19</th>\n",
              "      <td>40310</td>\n",
              "      <td>SINGLE</td>\n",
              "      <td>2103-12-05 00:00:00</td>\n",
              "      <td>Medicaid</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>20</th>\n",
              "      <td>40456</td>\n",
              "      <td>WIDOWED</td>\n",
              "      <td>2031-08-12 00:00:00</td>\n",
              "      <td>Medicare</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>21</th>\n",
              "      <td>40612</td>\n",
              "      <td>SINGLE</td>\n",
              "      <td>2073-08-13 00:00:00</td>\n",
              "      <td>Medicare</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>22</th>\n",
              "      <td>40655</td>\n",
              "      <td>WIDOWED</td>\n",
              "      <td>1844-07-18 00:00:00</td>\n",
              "      <td>Medicare</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>23</th>\n",
              "      <td>40687</td>\n",
              "      <td>WIDOWED</td>\n",
              "      <td>2073-06-05 00:00:00</td>\n",
              "      <td>Medicare</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>24</th>\n",
              "      <td>42066</td>\n",
              "      <td>SINGLE</td>\n",
              "      <td>2061-06-13 00:00:00</td>\n",
              "      <td>Private</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>25</th>\n",
              "      <td>42075</td>\n",
              "      <td>WIDOWED</td>\n",
              "      <td>2086-02-04 00:00:00</td>\n",
              "      <td>Medicare</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>26</th>\n",
              "      <td>42281</td>\n",
              "      <td>SINGLE</td>\n",
              "      <td>2051-03-23 00:00:00</td>\n",
              "      <td>Medicare</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>27</th>\n",
              "      <td>42292</td>\n",
              "      <td>SINGLE</td>\n",
              "      <td>2098-04-29 00:00:00</td>\n",
              "      <td>Private</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>28</th>\n",
              "      <td>42302</td>\n",
              "      <td>SINGLE</td>\n",
              "      <td>2074-09-29 00:00:00</td>\n",
              "      <td>Medicare</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>29</th>\n",
              "      <td>42321</td>\n",
              "      <td>WIDOWED</td>\n",
              "      <td>2041-05-16 00:00:00</td>\n",
              "      <td>Medicare</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>30</th>\n",
              "      <td>42346</td>\n",
              "      <td>SINGLE</td>\n",
              "      <td>2072-12-03 00:00:00</td>\n",
              "      <td>Medicare</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>31</th>\n",
              "      <td>42346</td>\n",
              "      <td>SINGLE</td>\n",
              "      <td>2072-12-03 00:00:00</td>\n",
              "      <td>Medicare</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>32</th>\n",
              "      <td>43746</td>\n",
              "      <td>WIDOWED</td>\n",
              "      <td>2029-12-07 00:00:00</td>\n",
              "      <td>Medicare</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>33</th>\n",
              "      <td>43827</td>\n",
              "      <td>SINGLE</td>\n",
              "      <td>1876-07-14 00:00:00</td>\n",
              "      <td>Medicare</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>34</th>\n",
              "      <td>43927</td>\n",
              "      <td>WIDOWED</td>\n",
              "      <td>2104-02-12 00:00:00</td>\n",
              "      <td>Medicare</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>35</th>\n",
              "      <td>44083</td>\n",
              "      <td>SINGLE</td>\n",
              "      <td>2057-11-15 00:00:00</td>\n",
              "      <td>Private</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>36</th>\n",
              "      <td>44083</td>\n",
              "      <td>SINGLE</td>\n",
              "      <td>2057-11-15 00:00:00</td>\n",
              "      <td>Private</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>37</th>\n",
              "      <td>44083</td>\n",
              "      <td>SINGLE</td>\n",
              "      <td>2057-11-15 00:00:00</td>\n",
              "      <td>Private</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>38</th>\n",
              "      <td>44212</td>\n",
              "      <td>SINGLE</td>\n",
              "      <td>2078-06-16 00:00:00</td>\n",
              "      <td>Medicare</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>39</th>\n",
              "      <td>44222</td>\n",
              "      <td>SINGLE</td>\n",
              "      <td>2107-06-27 00:00:00</td>\n",
              "      <td>Medicare</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>40</th>\n",
              "      <td>44228</td>\n",
              "      <td>SINGLE</td>\n",
              "      <td>2112-10-22 00:00:00</td>\n",
              "      <td>Private</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "    subject_id marital_status                  dob insurance\n",
              "0        10011         SINGLE  2090-06-05 00:00:00   Private\n",
              "1        10032        WIDOWED  2050-03-29 00:00:00  Medicare\n",
              "2        10038        WIDOWED  2056-01-27 00:00:00  Medicare\n",
              "3        10046         SINGLE  2141-03-15 00:00:00  Medicaid\n",
              "4        10061        WIDOWED  2031-05-19 00:00:00  Medicare\n",
              "5        10065         SINGLE  2111-07-18 00:00:00   Private\n",
              "6        10090         SINGLE  2096-02-27 00:00:00   Private\n",
              "7        10093        WIDOWED  2053-09-08 00:00:00  Medicare\n",
              "8        10111        WIDOWED  2097-01-16 00:00:00  Medicare\n",
              "9        10117         SINGLE  2072-05-05 00:00:00   Private\n",
              "10       10117         SINGLE  2072-05-05 00:00:00   Private\n",
              "11       10120         SINGLE  2088-05-05 00:00:00  Medicaid\n",
              "12       10124        WIDOWED  2108-12-20 00:00:00  Medicare\n",
              "13       10124        WIDOWED  2108-12-20 00:00:00  Medicare\n",
              "14       10126         SINGLE  2127-06-04 00:00:00   Private\n",
              "15       40124         SINGLE  2063-07-05 00:00:00  Medicare\n",
              "16       40124         SINGLE  2063-07-05 00:00:00  Medicare\n",
              "17       40277        WIDOWED  2070-10-11 00:00:00  Medicare\n",
              "18       40310         SINGLE  2103-12-05 00:00:00   Private\n",
              "19       40310         SINGLE  2103-12-05 00:00:00  Medicaid\n",
              "20       40456        WIDOWED  2031-08-12 00:00:00  Medicare\n",
              "21       40612         SINGLE  2073-08-13 00:00:00  Medicare\n",
              "22       40655        WIDOWED  1844-07-18 00:00:00  Medicare\n",
              "23       40687        WIDOWED  2073-06-05 00:00:00  Medicare\n",
              "24       42066         SINGLE  2061-06-13 00:00:00   Private\n",
              "25       42075        WIDOWED  2086-02-04 00:00:00  Medicare\n",
              "26       42281         SINGLE  2051-03-23 00:00:00  Medicare\n",
              "27       42292         SINGLE  2098-04-29 00:00:00   Private\n",
              "28       42302         SINGLE  2074-09-29 00:00:00  Medicare\n",
              "29       42321        WIDOWED  2041-05-16 00:00:00  Medicare\n",
              "30       42346         SINGLE  2072-12-03 00:00:00  Medicare\n",
              "31       42346         SINGLE  2072-12-03 00:00:00  Medicare\n",
              "32       43746        WIDOWED  2029-12-07 00:00:00  Medicare\n",
              "33       43827         SINGLE  1876-07-14 00:00:00  Medicare\n",
              "34       43927        WIDOWED  2104-02-12 00:00:00  Medicare\n",
              "35       44083         SINGLE  2057-11-15 00:00:00   Private\n",
              "36       44083         SINGLE  2057-11-15 00:00:00   Private\n",
              "37       44083         SINGLE  2057-11-15 00:00:00   Private\n",
              "38       44212         SINGLE  2078-06-16 00:00:00  Medicare\n",
              "39       44222         SINGLE  2107-06-27 00:00:00  Medicare\n",
              "40       44228         SINGLE  2112-10-22 00:00:00   Private"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 26
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ZcDUvTwFqKnA",
        "colab_type": "text"
      },
      "source": [
        "##### Left Joins: Add relevant columns to your primary (left) spreadsheet from secondary spreadsheets, while keeping rows in the primary spreadsheet which have no linked values from the secondary spreadsheets.\n",
        "\n",
        "The example below contains two LEFT JOINs. Since proceedures are listed individually in the proceedure_events table (each subject may have 0, 1 or more proceedures per admission), and actual proceedure titles are listed in the proceedures_icd table, in order to work across all three tables, you will need to perform the two joins shown below.\n",
        "\n",
        "The result below pulls together data from the admissions tables and the proceedures_icd table, using the proceedure_events table as a sort of cross-walk between the tables."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "8dilq1NK-jkD",
        "colab_type": "code",
        "outputId": "c9bd1803-4b9d-4ea7-85bb-042305d9e6c7",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 419
        }
      },
      "source": [
        "#perform a left join to link proceedure names in the proceedure_icds to the icd codes present \n",
        "#in the proceedure_events table, and present these proceedure names with specific admission instances.\n",
        "\n",
        "pd.read_sql_query('''SELECT admissions.subject_id, discharge_location, proceedure_icd.short_title \n",
        "FROM admissions \n",
        "LEFT JOIN proceedure_events ON admissions.subject_id = proceedure_events.subject_id\n",
        "LEFT JOIN proceedure_icd ON proceedure_events.icd9_code = proceedure_icd.icd9_code;''', conn)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>subject_id</th>\n",
              "      <th>discharge_location</th>\n",
              "      <th>short_title</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>10006</td>\n",
              "      <td>HOME HEALTH CARE</td>\n",
              "      <td>Injection oxazolidinone</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>10006</td>\n",
              "      <td>HOME HEALTH CARE</td>\n",
              "      <td>Venous cath NEC</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>10006</td>\n",
              "      <td>HOME HEALTH CARE</td>\n",
              "      <td>Ven cath renal dialysis</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>10006</td>\n",
              "      <td>HOME HEALTH CARE</td>\n",
              "      <td>Hemodialysis</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>10006</td>\n",
              "      <td>HOME HEALTH CARE</td>\n",
              "      <td>Percu abdominal drainage</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1331</th>\n",
              "      <td>44212</td>\n",
              "      <td>REHAB/DISTINCT PART HOSP</td>\n",
              "      <td>Cont inv mec ven 96+ hrs</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1332</th>\n",
              "      <td>44222</td>\n",
              "      <td>HOME</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1333</th>\n",
              "      <td>44228</td>\n",
              "      <td>HOME HEALTH CARE</td>\n",
              "      <td>Venous cath NEC</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1334</th>\n",
              "      <td>44228</td>\n",
              "      <td>HOME HEALTH CARE</td>\n",
              "      <td>Venous cath NEC</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1335</th>\n",
              "      <td>44228</td>\n",
              "      <td>HOME HEALTH CARE</td>\n",
              "      <td>Packed cell transfusion</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>1336 rows × 3 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "      subject_id        discharge_location               short_title\n",
              "0          10006          HOME HEALTH CARE   Injection oxazolidinone\n",
              "1          10006          HOME HEALTH CARE           Venous cath NEC\n",
              "2          10006          HOME HEALTH CARE   Ven cath renal dialysis\n",
              "3          10006          HOME HEALTH CARE              Hemodialysis\n",
              "4          10006          HOME HEALTH CARE  Percu abdominal drainage\n",
              "...          ...                       ...                       ...\n",
              "1331       44212  REHAB/DISTINCT PART HOSP  Cont inv mec ven 96+ hrs\n",
              "1332       44222                      HOME                      None\n",
              "1333       44228          HOME HEALTH CARE           Venous cath NEC\n",
              "1334       44228          HOME HEALTH CARE           Venous cath NEC\n",
              "1335       44228          HOME HEALTH CARE   Packed cell transfusion\n",
              "\n",
              "[1336 rows x 3 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 27
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "0EJctlE7KLNQ",
        "colab_type": "text"
      },
      "source": [
        "##### Let's look at this a bit more closely\n",
        "\n",
        "First, by viewing the proceedure_events table and observing where the icd9_codes, icd short_titles, and discharge appear across the dataframes."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "p_R1uJ8Vzt_j",
        "colab_type": "code",
        "outputId": "84cf8c7f-e4fc-4ac6-dbf6-6b95b203ad4a",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 357
        }
      },
      "source": [
        "print(proceedure_events.head())\n",
        "print(proceedure_icd.head())\n",
        "print(admissions.head())"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "     icd9_code\n",
            "0         3605\n",
            "1         3722\n",
            "2         8856\n",
            "3         9920\n",
            "4         9671\n",
            "..         ...\n",
            "501       3893\n",
            "502       3893\n",
            "503        966\n",
            "504       3893\n",
            "505        966\n",
            "\n",
            "[506 rows x 1 columns]\n",
            "   row_id  subject_id  hadm_id  seq_num  icd9_code\n",
            "0    3994       10114   167957        1       3605\n",
            "1    3995       10114   167957        2       3722\n",
            "2    3996       10114   167957        3       8856\n",
            "3    3997       10114   167957        4       9920\n",
            "4    3998       10114   167957        5       9671\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "vW9ZajXsRCn4",
        "colab_type": "text"
      },
      "source": [
        "##### Summarizing data results using COUNT and GROUP BY\n",
        "\n",
        "You can use a COUNT function to create a summary read out if you also determine what you would like to GROUP BY. Below, you can see how if you GROUP BY subject_id, you are then able to COUNT the number of icd9_codes per each of these subject_ids within the proceedure_events column. This essentially tells us that for each subject indexed in the, how many proceedures the subject has undergone.\n",
        "\n",
        "Since COUNT is now one of our columns in this data pull, we can utilize apply a conditional statement to this column. HAVING allows us to filter on this summary column. Below, only patientes who have had more than 15 proceedures are included in the final results. \n",
        "\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "GBsQXxrByVcA",
        "colab_type": "code",
        "outputId": "a436a9b9-2059-4567-9eff-832d501ff6a7",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        }
      },
      "source": [
        "pd.read_sql_query('''SELECT subject_id, COUNT(icd9_code) \n",
        "FROM proceedure_events\n",
        "GROUP BY subject_id\n",
        "HAVING COUNT(icd9_code) > 15 ''', conn)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>subject_id</th>\n",
              "      <th>COUNT(icd9_code)</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>10126</td>\n",
              "      <td>22</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>10127</td>\n",
              "      <td>19</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>40310</td>\n",
              "      <td>18</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>41976</td>\n",
              "      <td>49</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>42075</td>\n",
              "      <td>16</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   subject_id  COUNT(icd9_code)\n",
              "0       10126                22\n",
              "1       10127                19\n",
              "2       40310                18\n",
              "3       41976                49\n",
              "4       42075                16"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 17
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "j8tFKJrbTUhw",
        "colab_type": "text"
      },
      "source": [
        "In this last example, you can see how you can bring all of the SQL query elements together to built out highly customizable database queries. Below, this query provides the names of proceedure events that have occured in the database more than 10 times by bringing together the proceedure_icd and proceedure_events tables with a left join. "
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "EhljVc_U3xMg",
        "colab_type": "code",
        "outputId": "9d91ba7f-597d-4186-ee45-eb3819927ecf",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 297
        }
      },
      "source": [
        "pd.read_sql_query('''SELECT proceedure_icd.short_title, proceedure_events.icd9_code, COUNT(proceedure_events.icd9_code) \n",
        "FROM proceedure_events\n",
        "LEFT JOIN proceedure_icd on proceedure_events.icd9_code = proceedure_icd.icd9_code\n",
        "GROUP BY proceedure_events.icd9_code\n",
        "HAVING COUNT(proceedure_events.icd9_code) > 10;''', conn)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>short_title</th>\n",
              "      <th>icd9_code</th>\n",
              "      <th>COUNT(proceedure_events.icd9_code)</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Entral infus nutrit sub</td>\n",
              "      <td>966</td>\n",
              "      <td>32</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Venous cath NEC</td>\n",
              "      <td>3893</td>\n",
              "      <td>56</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Percu abdominal drainage</td>\n",
              "      <td>5491</td>\n",
              "      <td>12</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Insert endotracheal tube</td>\n",
              "      <td>9604</td>\n",
              "      <td>22</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Cont inv mec ven &lt;96 hrs</td>\n",
              "      <td>9671</td>\n",
              "      <td>21</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>Cont inv mec ven 96+ hrs</td>\n",
              "      <td>9672</td>\n",
              "      <td>19</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>Packed cell transfusion</td>\n",
              "      <td>9904</td>\n",
              "      <td>30</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>Parent infus nutrit sub</td>\n",
              "      <td>9915</td>\n",
              "      <td>14</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                short_title  icd9_code  COUNT(proceedure_events.icd9_code)\n",
              "0   Entral infus nutrit sub        966                                  32\n",
              "1           Venous cath NEC       3893                                  56\n",
              "2  Percu abdominal drainage       5491                                  12\n",
              "3  Insert endotracheal tube       9604                                  22\n",
              "4  Cont inv mec ven <96 hrs       9671                                  21\n",
              "5  Cont inv mec ven 96+ hrs       9672                                  19\n",
              "6   Packed cell transfusion       9904                                  30\n",
              "7   Parent infus nutrit sub       9915                                  14"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 7
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "zIrEHbrAidMR",
        "colab_type": "text"
      },
      "source": [
        "## Overview: Bringing SQL Query Elements Together"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ARiRcU9Hio0C",
        "colab_type": "text"
      },
      "source": [
        "In order to build out more complex single queries in SQL, you will follow the structure below. To generalize the theme of this structure: SQL queries startby pointing at relevant columns and tables (lines 1 through 4 below), specifying (cell) values of interest (line 5 below), and finally choosing how the data is returned (lines 6 through 7).\n",
        "\n",
        "As we saw in the SQL in previous examples, lines 3 through 7 in the code snippet below are optional and they are included in a query, they are also expandable. \n",
        "\n",
        "```\n",
        "1. SELECT column_name\n",
        "2. FROM table1_name\n",
        "3.     JOIN table2_name\n",
        "4.       ON table1_name.linking_column_name = table2_name.linking_column_name\n",
        "5.     WHERE contraint_expression\n",
        "6.     GROUP BY column_name\n",
        "7.     ORDER BY column_name ASC/DESC\n",
        "```\n",
        "\n",
        "As you work through the exercises for this session, revisit the database documentation links provided in this lecture workbook. Think about other questions you can ask of the mimic iii database and piece together a SQL query that will result in you retreving data that will enabe you to answer your question.\n"
      ]
    }
  ]
}