sqlite_workshop.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Welcome to our Jupyter notebook!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Jupyter notebook is an amazing open-source tool that allows you to share live code, notes, equations, etc. in an interactive fashion. \"Jupyter\" stands for Julia, Python, and R, but today supports even more programming languages than just these. For each cell, you can specify whether you want it to be code or Markdown. The code is written in Python. You can tell that we are now in a Python evironment because it says `Python 3` in the upper left-hand corner of the page. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Insert a cell below in Markdown:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Insert cell below in code and run `print(\"Hello world\")`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Important note:** Jupyter notebooks are sequential, which means that cells that contain code need to be run in order to work! So remember to hit \"Run\" for each cell as we are working through. For example, if we try to print the value of x before defining it, we will get an error:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "x = 100 + 3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(x)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Okay let's start learning about pandas!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Import modules that we'll need"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To start, we'll load up a few modules with the `import` statement. When you load a module using import, all of the functions available are now accessible to you. Modules and import statements help programmers avoid naming conflicts because you can use short, straightforward names for functions and variables without worrying that they're already taken. Matlab does not have anything equivalent to Python's module system and therefore can be harder to read. add your own notes!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Getting and cleaning Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load Data into pandas Dataframe"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's load the data up using the `read_csv` function from the Pandas package, which we've abbreviated as `pd`. By using `pd.read_csv`, we return what is called a pandas DataFrame. A DataFrame can be thought of as a 2D table, but the values within each of the columns must be the same datatype. For example, any entry in the Year column must be an integer, while an entry in the Cause column must be a string."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use a # to \"comment out\" anything in a code block - this is a nice way to take notes and document your code!\n",
    "\n",
    "# Data sets available at https://catalog.data.gov/dataset\n",
    "data = pd.read_csv('NCHS_-_Leading_Causes_of_Death__United_States.csv',',')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The first thing to do with our DataFrame is to look at the first few rows of the function using `head()`. We often do this just to confirm that we loaded the data correctly (that it has the correct column names)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Look at data\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**It looks just like our csv file!**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Explore the data using pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Instead of looking at the data in Excel, we can use methods properties within the pandas framework to describe basic features of our dataset (feel free to look at the pandas documentation on DataFrames if you are curious about the difference between a method and a property)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Describe returns summary statistics on numerical variables\n",
    "data.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# What are the column names?\n",
    "data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# How many rows and columns are there?\n",
    "data.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that the convention here is (rows, columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Clean Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Data typically needs to be \"cleaned\" before it's ready to use. For example, for pandas and other frameworks, spaces in names and names that start with numbers will cause problems. We've used a custom function `clean_names` to clean up the names of the data columns of the DataFrame for use in the database.  The details of how this function works are not important, but you're welcome to look at the file `make_database.py` in the `Github` repo to get an idea of how this function works."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Querying the database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Background"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### About Relational Databases"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Unlike an excel spreadsheet or a `pandas DataFrame`, data is typically spread across multiple tables in a relational\n",
    "database. The process of spreading data across multiple tables is called `normalization`. Normalization reduces\n",
    "redundancies in the database (making the normalized database more compact in terms of disk space), makes it easier\n",
    "(and safer) to change the value of a cell in a database, and can optimize the queries (or searches)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### About SQLite"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Most relational databaes require a separate server process. This means you have to access the server in order to interact with the relational database. Here we're using `SQLite`, which is a relational database which has similar features to relational databases requiring servers (such as `MySQL`, `Postgre SQL`, or `SQL Server`), but doesn't require a server and is essentially plug-and-play. Most importantly, SQLite uses similar query language to the other databases. The language used to query all of these databases is based upon `SQL`, or `Structured Query Language`. We will sometimes point out equivalent commands for MySQL."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Using Python to Interact With Relational Databases"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`sqlite3` is a module within python that allows you to interact with `SQLite` within the comfort of python. Alternatively, you could interact with SQLite through a command line interface, but python makes it easier to run, store, and alter your queries. The `sqlite3` module with establish a connection with the sqlite3 database and assign this connection to an object, which we are calling `connection`:\n",
    "```python\n",
    "connection = sqlite3.connect(\"my_database\")\n",
    "```\n",
    "We will use a nice feature of `pandas` which allows us to run a query and load it as a dataframe after we've opened up a connection with the database:\n",
    "```python\n",
    "result_df = pd.read_sql_query(\"My SQLite Query\", connection)\n",
    "```\n",
    "The `pd` is how we tell the `pandas` module that we are talking to it, and the `read_sql_query` is the command we're giving the `pandas` module, which tells `pandas` to use the connection we've given it (inside of the paretheses) to run the query we've also given it, and ultimately to load the results of the query into a dataframe. Notice that I have to type the name of the dataframe at the end of the code block to see the results of the query. Finally, it is important to remember to close the database when you're done querying it, which you do by telling the connection to close:\n",
    "```python\n",
    "connection.close()\n",
    "```\n",
    "Lastly, do not worry if everything you just read sounded like gobble-dee-muck.  It's sufficient to just think of the commands as a series of spells that will work if you say them in the proper order."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Getting to Know Your Database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "One of the first things you'll want to do with a database with which you're unfamiliar is find out the names of the\n",
    "`tables` within the database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = sqlite3.connect(\"leading_causes_of_death.sqlite\")\n",
    "result_df = pd.read_sql_query(\"SELECT name FROM sqlite_master WHERE type='table'\", connection)\n",
    "connection.close()\n",
    "result_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So our column names are `Year`, `Cause`, `State`, and `Deaths`. Notice that I have to type the name of the dataframe at the end of the code block to see the results of the query."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "_As an aside:_ Although hopefully the command we used might make a little more sense as you continue with this notebook, it is fairly esoteric. In MySQL the equivalent command is\n",
    "```SQL\n",
    "SHOW tables;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next you'll want to find out what are the `column` names in each table. We will do that table-by-table below, starting with the `Year` table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "_As an aside_: In MySQL you can use the command\n",
    "```SQL\n",
    "DESCRIBE my_table;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Year Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = sqlite3.connect(\"leading_causes_of_death.sqlite\")\n",
    "result_df = pd.read_sql_query(\"SELECT * FROM Year LIMIT 5\", connection)\n",
    "connection.close()\n",
    "result_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Cause Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = sqlite3.connect(\"leading_causes_of_death.sqlite\")\n",
    "result_df = pd.read_sql_query(\"SELECT * FROM Cause LIMIT 5\", connection)\n",
    "connection.close()\n",
    "result_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### State Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = sqlite3.connect(\"leading_causes_of_death.sqlite\")\n",
    "result_df = pd.read_sql_query(\"SELECT * FROM State LIMIT 5\", connection)\n",
    "connection.close()\n",
    "result_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Deaths Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = sqlite3.connect(\"leading_causes_of_death.sqlite\")\n",
    "result_df = pd.read_sql_query(\"SELECT * FROM Deaths LIMIT 5\", connection)\n",
    "connection.close()\n",
    "result_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Comparison to Original DataFrame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lets take a look at our original dataframe, which we can do by typing its name `data`. We're going to once again use the `head` method to not overwhelm ourselves with the full table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Comparing the original dataframe (which has a format identical to the `csv` file you earlier opened in `Excel` to the `Deaths` table, we see that the year, cause name, and state are recoded as numbers. This prevents Vermont from being stored in the database 1000's of times and instead a single number (47) is stored in the `Deaths` table. This is exactly what we meant by `normalization`: we've broken up the original table into several tables in order to store the data in a more efficient manner. We can figure out which state the number 47 refers to by looking in the `State` table. We can figure out which year the number 14 refers to by looking in the `Year` table. We can also figure out what cause of death the number 1 refers to by looking in the `Cause` table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Query Language Basics"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we're going to introduce you to the SQLite query language. This will make some of the queries we ran in the section **Getting To Know Your Database** a little easier to understand, and we invite you to review that section after completing this one."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Basic structure for all of the queries we will make today:\n",
    "\n",
    "~~~~\n",
    "connection = sqlite3.connect(\"leading_causes_of_death.sqlite\")\n",
    "result_df = pd.read_sql_query(\"PUT YOUR SQL QUERY STATEMENT HERE\", connection)\n",
    "connection.close()\n",
    "result_df\n",
    "~~~~"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The Select Statement"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A very common SQL statement is the `SELECT` statement which allows you to select columns from a particular table:\n",
    "```SQL\n",
    "SELECT <ColumnName>\n",
    "FROM <TableName>\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Select and view the Cause_Name column from the Cause table:\n",
    "\n",
    "connection = sqlite3.connect(\"leading_causes_of_death.sqlite\")\n",
    "result_df = pd.read_sql_query(\"SELECT Cause_Name FROM Cause\", connection)\n",
    "connection.close()\n",
    "result_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The Limit Statement"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you don't want to see all of the output you can add a `LIMIT` expression:\n",
    "```SQL\n",
    "SELECT <ColumnName>\n",
    "FROM <TableName>\n",
    "LIMIT <n>\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Limit the number of rows that display to 3:\n",
    "\n",
    "connection = sqlite3.connect(\"leading_causes_of_death.sqlite\")\n",
    "result_df = pd.read_sql_query(\"SELECT Cause_Name FROM Cause LIMIT 3\", connection)\n",
    "connection.close()\n",
    "result_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Selecting Multiple or All Columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "One approach to selecting all columns is to type them out as a list:\n",
    "```SQL\n",
    "SELECT <ColumnName, ColumnName> \n",
    "FROM <TableName>\n",
    "LIMIT <n>\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Select and display all columns from the Cause table:\n",
    "\n",
    "connection = sqlite3.connect(\"leading_causes_of_death.sqlite\")\n",
    "result_df = pd.read_sql_query(\"SELECT Cause_Name, Cause_Description FROM Cause LIMIT 3\", connection)\n",
    "connection.close()\n",
    "result_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "However, an easier way to simply select all column names is with `*`, which is a wildcard character in multiple programming languages:\n",
    "\n",
    "```SQL\n",
    "SELECT * \n",
    "FROM <TableName>\n",
    "LIMIT <n>\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise**: Modify the query above so that you get the same result using `*` instead of the column names."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Answer exercise here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The WHERE Statement"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also specify a condition using a `WHERE` statement:\n",
    "```SQL\n",
    "SELECT <ColumnName>\n",
    "FROM <TableName>\n",
    "WHERE <condition>\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# In the Cause table, display the Cause_Description where Cause_Name is CLRD:\n",
    "\n",
    "connection = sqlite3.connect(\"leading_causes_of_death.sqlite\")\n",
    "result_df = pd.read_sql_query(\"SELECT Cause_Description FROM Cause WHERE Cause_Name = 'CLRD'\", connection)\n",
    "connection.close()\n",
    "result_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise**: What is the description of 'Unintentional injuries'?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Answer exercise here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Single Joins"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we preview at our Deaths table again: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = sqlite3.connect(\"leading_causes_of_death.sqlite\")\n",
    "result_df = pd.read_sql_query(\"SELECT * FROM Deaths LIMIT 5\", connection)\n",
    "connection.close()\n",
    "result_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What if we wanted to identify the year, cause, and state in the first row? We COULD use a series of `SELECT` and `WHERE` statements. For example, to identify what year the YearID 14 corresponds to:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = sqlite3.connect(\"leading_causes_of_death.sqlite\")\n",
    "result_df = pd.read_sql_query(\"SELECT Year FROM Year WHERE YearID = 14\", connection)\n",
    "connection.close()\n",
    "result_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "However, that was a lot of work, and you had to remember the primary key number of the YearID from the first row. That's not very efficient and prone to errors. Computers to the rescue! If you give your computer the right command, it will do all of this work for you. This is where can use `JOIN` statements!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Now let's head back to our powerpoint for a quick intro on how to make more complex queries using database joins!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `JOIN` statement begins with a `SELECT` statement:\n",
    "```SQL\n",
    "SELECT <Table_x.Column_x, Table_y.Column_y>\n",
    "```\n",
    "`Table_x.Column_x` means `Column_x` from `Table_x`, so we are telling the database to show us `Column_x` from `Table_x` and `Column_y` from `Table_y`. Now we will add the `JOIN` part:\n",
    "```SQL\n",
    "SELECT <Table_x.Column_x, Table_y.Column_y>\n",
    "FROM <Table_x> \n",
    "INNER JOIN <Table_y>\n",
    "ON <Table_x.Column_x> = <Table_y.Column_y>\n",
    "```\n",
    "The `FROM Table_x` is code for saying, starting from `Table_x`, and the `INNER JOIN Table_y ON Table_x.Column_x = Table_y.Column_y` means make a table that has all of the rows from `Table_x` and `Table_y` where `Column_x` and `Column_y` have identical values."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "_Aside_: There are many types of `JOINS`, but `INNER JOIN` is one of the most common."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Join the Deaths and State tables on StateID:\n",
    "\n",
    "connection = sqlite3.connect(\"leading_causes_of_death.sqlite\")\n",
    "\n",
    "query = '''SELECT *\n",
    "      FROM Deaths \n",
    "      INNER JOIN State\n",
    "      ON Deaths.StateID = State.StateID\n",
    "      LIMIT 5'''\n",
    "\n",
    "result_df = pd.read_sql_query(query, connection)\n",
    "connection.close()\n",
    "result_df\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `INNER JOIN` combined the two tables.  It's now really easy to see that `StateID` number 47 refers to Vermont. Notice too that we assigned the query to a variable and gave that variable (`query`) as a a parameter to `pd.read_sql_query`. We also put `'''` around the query, which is a way of telling Python that we are writing out a query that we want to put on multiple lines. `SQLite` doesn't care if you break your query up into lines or not, but it sure makes things a lot easier to read! We could have used the command below and gotten exactly the same result:\n",
    "```python\n",
    "result_df = pd.read_sql_query(\"SELECT * FROM Deaths INNER JOIN State ON Deaths.StateID = State.StateID LIMIT 5\", connection)\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's repeat that query but this time only select the columns `State`, `Deaths`, and `Age_Adjusted_Death_Rate`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = sqlite3.connect(\"leading_causes_of_death.sqlite\")\n",
    "\n",
    "query = '''SELECT State.State, Deaths.Deaths, Deaths.Age_Adjusted_Death_Rate\n",
    "      FROM Deaths \n",
    "      INNER JOIN State\n",
    "      ON Deaths.StateID = State.StateID\n",
    "      LIMIT 5'''\n",
    "\n",
    "result_df = pd.read_sql_query(query, connection)\n",
    "connection.close()\n",
    "result_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise:** Repeat the above query, but this time create a table that contains `Year`, `Deaths`, and `Age_Adjusted_Death_Rate`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Answer exercise here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Multiple Joins"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Single Joins are great, but we need `Year`, `State`, and `Cause` joined with `Deaths` to be able to turn `Deaths` into something that we can understand with just a glance."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = sqlite3.connect(\"leading_causes_of_death.sqlite\")\n",
    "\n",
    "query = '''SELECT Year.Year, State.State, Deaths.Deaths, Deaths.Age_Adjusted_Death_Rate\n",
    "      FROM Deaths \n",
    "      INNER JOIN State\n",
    "      ON Deaths.StateID = State.StateID\n",
    "      INNER JOIN Year\n",
    "      ON Deaths.YearID = Year.YearID\n",
    "      LIMIT 5'''\n",
    "\n",
    "result_df = pd.read_sql_query(query, connection)\n",
    "connection.close()\n",
    "result_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise:** Repeat the above query, but this time create a table that contains `Year`, `State`, `Cause`, `Deaths`, and `Age_Adjusted_Death_Rate`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Answer exercise here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Joins with the WHERE Statement"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can add in a `WHERE` statement to select rows from the joined table that meet some condition."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Join tables for the year 2014:\n",
    "\n",
    "connection = sqlite3.connect(\"leading_causes_of_death.sqlite\")\n",
    "\n",
    "query = '''SELECT Year.Year, State.State, Cause.Cause_Name, Deaths.Deaths, Deaths.Age_Adjusted_Death_Rate\n",
    "      FROM Deaths \n",
    "      INNER JOIN State\n",
    "      ON Deaths.StateID = State.StateID\n",
    "      INNER JOIN Year\n",
    "      ON Deaths.YearID = Year.YearID\n",
    "      INNER JOIN Cause\n",
    "      ON Deaths.CauseID = Cause.CauseID\n",
    "      WHERE YEAR = 2014\n",
    "      LIMIT 5'''\n",
    "\n",
    "result_df = pd.read_sql_query(query, connection)\n",
    "connection.close()\n",
    "result_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The AND Statement"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also add in an `AND` statment in the `WHERE` statement to select rows that meet two conditions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Join tables for \"All causes\" of death in 2014:\n",
    "\n",
    "connection = sqlite3.connect(\"leading_causes_of_death.sqlite\")\n",
    "\n",
    "query = '''SELECT Year.Year, State.State, Cause.Cause_Name, Deaths.Deaths, Deaths.Age_Adjusted_Death_Rate\n",
    "      FROM Deaths \n",
    "      INNER JOIN State\n",
    "      ON Deaths.StateID = State.StateID\n",
    "      INNER JOIN Year\n",
    "      ON Deaths.YearID = Year.YearID\n",
    "      INNER JOIN Cause\n",
    "      ON Deaths.CauseID = Cause.CauseID\n",
    "      WHERE Year = 2014 AND Cause_Name = 'All causes'\n",
    "      LIMIT 5'''\n",
    "\n",
    "result_df = pd.read_sql_query(query, connection)\n",
    "connection.close()\n",
    "result_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The ORDER BY Statement"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `ORDER BY` statement allows us to get results ordered by some column.  This makes it really easy to see the row with the highest of some value (or the lowest). The `ORDER BY` statement works like this:\n",
    "```SQL\n",
    "ORDER BY <ColumnA> ASC\n",
    "```\n",
    "This will give us a table that is sorted by ColumnA in ascending order. The statement below gives us the table sorted by ColumnA sorted in descending order.\n",
    "```SQL\n",
    "ORDER BY <ColumnA> DESC\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Order State in ascending order:\n",
    "\n",
    "connection = sqlite3.connect(\"leading_causes_of_death.sqlite\")\n",
    "\n",
    "query = '''SELECT Year.Year, State.State, Cause.Cause_Name, Deaths.Deaths, Deaths.Age_Adjusted_Death_Rate\n",
    "      FROM Deaths \n",
    "      INNER JOIN State\n",
    "      ON Deaths.StateID = State.StateID\n",
    "      INNER JOIN Year\n",
    "      ON Deaths.YearID = Year.YearID\n",
    "      INNER JOIN Cause\n",
    "      ON Deaths.CauseID = Cause.CauseID\n",
    "      WHERE YEAR = 2014 AND Cause_Name = 'All causes'\n",
    "      ORDER BY State ASC\n",
    "      LIMIT 5'''\n",
    "\n",
    "result_df = pd.read_sql_query(query, connection)\n",
    "connection.close()\n",
    "result_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We've gotten the table in alphabetical order by State! Not very interesting! But you get the idea. You can use `ORDER BY` with both numerical and alphabetical variables. For a much more interesting example of the `ORDER BY` statement, check out the exercise below."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise**: Which state had the highest Age-Adjusted Death Rate in 2014? Which state had the lowest?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Answer exercise here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Wrapping Up"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We hope you enjoyed this introduction to using `python`, `pandas` and `SQLite`! Our intention was to give you an introduction to these powerful software tools and to instill you with the confidence that _you_ can learn to use them! If you'd like to get some more information, we've provided you with some links with helpful tutorials for you to continue your learning.\n",
    "\n",
    "[SQLite Tutorial](https://www.tutorialspoint.com/sqlite)\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}