SQL_2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Workbook 2: What are the Distributions of Jobs by County and by Metropolitan/Micropolitan Area?\n",
    "\n",
    "So far in Workbook 1, we've just looked at the data by census block, with each row representing one census block. We also have a geography crosswalk table, containing information about the census blocks, including information about county and zip codes. What if we wanted to look at the jobs dataset within the context of the county they were in? We'd need to somehow combine the information from these two datasets together. We do that using `JOIN`s. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Motivating Question\n",
    "\n",
    "In the previous workbook, we explored a little bit of the Workplace Area Characteristic dataset as well as the Geography Crosswalk dataset. We were able to look at how jobs were in each census block, as well as how many census blocks there were in each county or metropolitan/micropolitan area. In this notebook, we're going to explore deeper into the data available to us to try to answer the question:\n",
    "\n",
    "**What are the characteristics of the distribution of jobs by county and by metropolitan/micropolitan area? **\n",
    "\n",
    "To answer this, we'll have to use data from multiple tables."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## <span style=\"color:green\">Joins (VIDEO)</span>\n",
    "\n",
    "One of the nice things about relational databases is organization using multiple tables that are linked together in some way. For example, suppose we have one table with 6 rows called **Table A**:\n",
    "\n",
    "| blockid | C000|\n",
    "|---|---|\n",
    "|1|5|\n",
    "|2|10|\n",
    "|3|2|\n",
    "|4|6|\n",
    "|5|22|\n",
    "|6|9|\n",
    "\n",
    "And another table with 5 rows called **Table B**:\n",
    "\n",
    "| blockid | CA01|\n",
    "|---|---|\n",
    "|2|2|\n",
    "|5|4|\n",
    "|6|1|\n",
    "|7|2|\n",
    "|8|0|\n",
    "\n",
    "Let's say we want to combine Table A and Table B so that we have one table that contains information about `blockid`, `C000`, and `CA01`. We want to do this by matching the two tables by what they have in common, `blockid`. That is, we want a table that looks like this (let's call this **Table C**):\n",
    "\n",
    "| blockid | C000 | CA01 |\n",
    "|---|---|---|\n",
    "|2|10|2|\n",
    "|5|22|4|\n",
    "|6|9|1|\n",
    "\n",
    "Table C has each `blockid` that was in both Table A and Table B. It also contains the appropriate values for `C000` and `CA01` corresponding to each `blockid`. This kind of matching can be quite tricky to figure out manually, since there are different numbers of rows in each table, not all of the `blockid` values match for the two tables, and there are some `blockid` values that aren't in both. Fortunately for us, SQL is well-equipped to handle this task using the `JOIN` statement."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### SQL Code and how it works\n",
    "\n",
    "Before we begin joining, let's first connect to the database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%defaultDatasource jdbc:sqlite:lodes.db"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we're connected and have established a plan for how we're joining two tables together, let's take a look at the SQL code that performs this join and break it down.\n",
    "\n",
    "    SELECT * FROM ca_wac_2015\n",
    "    JOIN ca_xwalk\n",
    "    ON ca_wac_2015.w_geocode = ca_xwalk.tabblk2010 \n",
    "    LIMIT 1000;\n",
    "\n",
    "Let's look at the first two lines.\n",
    "\n",
    "    SELECT * FROM ca_wac_2015\n",
    "    JOIN ca_xwalk\n",
    "\n",
    "Here, we want to `SELECT` each column from a data table that we get from joining the tables `ca_wac_2015` and `ca_xwalk`. The second line takes the `ca_wac_2015` table and joins the `ca_xwalk` table to it. \n",
    "\n",
    "We can't just mash two tables together though -- we need some way of making sure that the appropriate rows match. We do this with the third line:\n",
    "\n",
    "    ON ca_wac_2015.w_geocode = ca_xwalk.tabblk2010\n",
    "\n",
    "This part specifies what we're joining on. That is, what is the ID variable that is in both tables that we want to match. Notice that they don't need to be named the same in both tables, though you do need to specify what they are in each table, even if they are the same, as well as which table they are from.\n",
    "\n",
    "If you run the full code below, you should see the first 1000 rows (because of the `LIMIT 1000`) of the joined table. You should be able to scroll through all of the variables and see that we've managed to merge the `ca_wac_2015` and `ca_xwalk` tables together according to their census block IDs.\n",
    "\n",
    "> **Side note:** We're only going to be displaying a few of the columns instead of using `SELECT *` like we showed above. This is because we aren't able to display more than 50 columns here in this notebook format. Joining to get tables with greater than 50 columns is perfectly fine, but we'll only look at a few at a time to make it easier to follow in these exercises."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT ca_wac_2015.w_geocode, ca_xwalk.tabblk2010, ca_wac_2015.c000, ca_wac_2015.ca01, ca_wac_2015.ca02, ca_wac_2015.ca03, \n",
    "ca_xwalk.ctyname, ca_xwalk.cbsaname \n",
    "FROM ca_wac_2015\n",
    "JOIN ca_xwalk\n",
    "ON ca_wac_2015.w_geocode = ca_xwalk.tabblk2010 \n",
    "LIMIT 1000;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here, we've chosen to display the two census block ID variables we're joining on so that you can see the matching, as well as a few characteristics from each table. Notice that we've specified the table before each variable. That's generally only necessary when both tables have a column with the same name, but we've done it here for clarity. The following will do the exact same thing and run just fine since the two tables don't share any of the column names."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT w_geocode, tabblk2010, c000, ca01, ca02, ca03, ctyname, cbsaname \n",
    "FROM ca_wac_2015\n",
    "JOIN ca_xwalk\n",
    "ON ca_wac_2015.w_geocode = ca_xwalk.tabblk2010 \n",
    "LIMIT 1000;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## <span style=\"color:red\">Checkpoint: Get Census Data and Join with your Tables\n",
    "\n",
    "Try joining the Residence Area Characteristics table with the Crosswalk table in a similar manner, as well as changing some of the columns to display. As you construct your query, make sure you answer the following question regarding the join:\n",
    "\n",
    "- What are they being joined on? That is, what is the \"ID\" variable you're matching on in each table?\n",
    "- What information does the resulting table give you? For example, after we join the WAC table to the geography crosswalk table, we are now able to determine counties or metropolitan/micropolitan areas for census block containing workplaces.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## <span style=\"color:green\">Different Types of Joins (VIDEO)</span>\n",
    "\n",
    "We've so far done only one type of join, an inner join. This is the default join (which is why we didn't need to specify anything more in the code). However, there are different types of joins.\n",
    "\n",
    "### Left and Right Joins in SQL\n",
    "\n",
    "\n",
    "Suppose we want to look at every single census block in one table, only filling in information from the second table if it exists. We'll illustrate this using Table A and Table B from before. Recall that our `JOIN` created Table C:\n",
    "\n",
    "| blockid | C000 | CA01 |\n",
    "|---|---|---|\n",
    "|2|10|2|\n",
    "|5|22|4|\n",
    "|6|9|1|\n",
    "\n",
    "Instead, we want to create the following table:\n",
    "\n",
    "| blockid | C000 | CA01 |\n",
    "|---|---|---|\n",
    "|1|5|*null*|\n",
    "|2|10|2|\n",
    "|3|2|*null*|\n",
    "|4|6|*null*|\n",
    "|5|22|4|\n",
    "|6|9|1|\n",
    "\n",
    "Here, we've kept every single row in Table A, and simply filled in the information from Table B if it existed for that `blockid`. This is called a **LEFT JOIN**, since we're taking the table on the left (that is, Table A) and adding the information from Table B onto that. We could have also done a **RIGHT JOIN**, which does the same thing, except flipping the tables, giving us something that looks like:\n",
    "\n",
    "| blockid | C000 | CA01|\n",
    "|---|---|---|\n",
    "|2|10|2|\n",
    "|5|22|4|\n",
    "|6|9|1|\n",
    "|7|*null*|2|\n",
    "|8|*null*|0|"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Applying Left Joins\n",
    "When might you use left or right joins? Suppose you want to know which census blocks don't have any jobs. Then, we'd want to make sure that we keep all of the census blocks in the geography crosswalk, even if they aren't present in the workplace area characteristics tables, and add in the workplace area characteristics. This would mean that any blocks with a `null` value in the `C000` column would be blocks without any jobs. \n",
    "\n",
    "To do this `JOIN`, we can use the `LEFT JOIN` statement."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT w_geocode, tabblk2010, c000, ca01, ca02, ca03, ctyname, cbsaname  FROM ca_wac_2015\n",
    "LEFT JOIN ca_xwalk \n",
    "ON ca_wac_2015.w_geocode = ca_xwalk.tabblk2010\n",
    "LIMIT 1000;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is very similar to what we've done already with `JOIN`, except we add the word `LEFT` to it. When doing `LEFT` and `RIGHT JOIN`s, make sure to keep track of which one is first. Here, `lodes.ca_wac` comes first, so that's the table on the \"left\" side."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Outer Join\n",
    "\n",
    "An outer join keeps all unique ids, then puts `NULL` if it isn't part of that table. This is similar to a `LEFT` or `RIGHT JOIN`, except instead of only keeping all IDs from one table, it keeps them from both tables. Consider our example with Table A and Table B. We want to join them such that we get a table that looks like:\n",
    "\n",
    "| blockid | C000 | CA01 |\n",
    "|---|---|---|\n",
    "|1|5|*null*|\n",
    "|2|10|2|\n",
    "|3|2|*null*|\n",
    "|4|6|*null*|\n",
    "|5|22|4|\n",
    "|6|9|1|\n",
    "|7|*null*|2|\n",
    "|8|*null*|0|\n",
    "\n",
    "In a way, it's like combining the `LEFT` and `RIGHT JOIN`s so that we have all information from both tables.\n",
    "\n",
    "### Applying Outer Joins\n",
    "\n",
    "Suppose we want to know which census blocks that contain either the residences of people with jobs in the state or the census blocks of the location of the workplace, but not both. We use `OUTER JOIN` for that. Unfortunately, we aren't able to show the outer join here, as it isn't supported by SQLite. We've provided the code here, but it won't run, so just make sure to keep it in mind for the future."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT * FROM ca_wac_2015\n",
    "OUTER JOIN ca_rac_2015 \n",
    "ON ca_wac_2015.w_geocode = ca_rac_2015.h_geocode \n",
    "LIMIT 1000;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This will let us see which census blocks contain values for both workplace characteristics and residence characteristics. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## <span style=\"color:red\">Checkpoint: Types of Joins</span>\n",
    "\n",
    "Consider the following situations. How would you answer the question posed? What type of join should you use for each one? Which tables do you need to join? Try doing the join.\n",
    "\n",
    "- How many census blocks in the state contain a workplace and how many don't? Recall that the geography crosswalk table contains information about the census blocks in the state.\n",
    "- Which county contains the most census blocks containing a workplace?\n",
    "- Which metropolitan/micropolitan area had the most census blocks containing a residence of a worker in the state?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## <span style=\"color:green\">Using Joins With Aggregation Functions (VIDEO)</span>\n",
    "\n",
    "Suppose we wanted to know the distribution of the total number of jobs in California by county. The employment information is in one table, and county information is in another table. We need to join them, then aggregate the jobs, by county. Even though this seems like a complicated multi-step process, we can actually do it all in one query.  Let's break it down into two parts: the join and the aggregation.\n",
    "\n",
    "### The Join\n",
    "\n",
    "We need to join the `ca_wac_2015` table and the `ca_xwalk` table. Since we aren't worried about counties that have no jobs, we can do an inner join. Consider the following `JOIN` (if you want to run it, I would suggest adding a `LIMIT` at the end):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT a.w_geocode, a.c000, b.ctyname\n",
    "FROM ca_wac_2015 a\n",
    "JOIN ca_xwalk b\n",
    "ON a.w_geocode = b.tabblk2010;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This should provide the `JOIN` that we want, as well as the relevant columns. We need to take the table we get from the join and apply the aggregation to it. \n",
    "\n",
    "### The Aggregation\n",
    "\n",
    "From our joined table, we need `GROUP BY` county, then find the `SUM()`. For now, let's call our joined table \"`joinedtable`\" and write the query based on this table. Since we're also interested in what counties have the most jobs, we'll order by the sum in descending order. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "    SELECT ctyname, SUM(c000)\n",
    "    FROM joinedtable\n",
    "    GROUP BY ctyname\n",
    "    ORDER BY SUM(c000) DESC\n",
    "    LIMIT 1000;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "But we've actually already figured out how to get table \"`joinedtable`\" using the `JOIN`s above. All we need to do is put the `JOIN` in."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT b.ctyname, SUM(a.c000)\n",
    "FROM ca_wac_2015 a\n",
    "JOIN ca_xwalk b\n",
    "ON a.w_geocode = b.tabblk2010\n",
    "GROUP BY ctyname\n",
    "ORDER BY SUM(c000) DESC\n",
    "LIMIT 1000;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This gives us one long query that performs the `JOIN`, then aggregates in the way we want. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## <span style=\"color:green\">Creating New Tables for Future Use (VIDEO)</span>\n",
    "\n",
    "So far, we've mostly just been exploring the data without making any changes to the database. However, there might be times when we might want to create new tables. We can do this using `CREATE TABLE`. Let's use a previous example to create a new table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "CREATE TABLE joinedtable AS \n",
    "SELECT * FROM ca_wac_2015\n",
    "LEFT JOIN ca_xwalk \n",
    "ON ca_wac_2015.w_geocode = ca_xwalk.tabblk2010\n",
    "LIMIT 1000;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This should look mostly familiar, since everything after the first line is stuff we've already done. The first line creates a new table called `joinedtable` from the output.\n",
    "\n",
    "This is a bit of a mess, though. We usually don't need everything from the tables that we do join, so we can choose what we keep. Let's create a new table that has just the information we need."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "CREATE TABLE joinedtable2 AS \n",
    "SELECT a.w_geocode AS blockid, a.c000 AS total_jobs, b.cty AS county \n",
    "FROM ca_wac_2015 a\n",
    "LEFT JOIN ca_xwalk b\n",
    "ON a.w_geocode = b.tabblk2010\n",
    "LIMIT 1000;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, notice that we use aliasing to help make refering to tables easier. That is, in the third and fourth lines, we put \"`a`\" and \"`b`\" after each table to give it that alias. We can then use \"`a`\" and \"`b`\" whenever we refer to either table, which makes the `SELECT` statement easier. \n",
    "\n",
    "Along those lines, notice that we specify which table each variable was from. If the column name is unique between the two tables (i.e. both tables don't have a column with the same name), then you don't need to specify the table as we've done. However, if they aren't unique and both tables have a variable with that name, you need to specify which one you want.\n",
    "\n",
    "Lastly, we've made the table easier to read by changing the name of the variable in the new table, using `AS` in the `SELECT` part of the query. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dropping Tables\n",
    "\n",
    "Conversely, you can also drop, or delete, tables. We created a table in the previous section that we won't need, so let's drop it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "DROP TABLE joinedtable;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You might be tempted to avoid dropping tables since it seems relatively harmless to simply not use the table anymore without dropping them. However, it is important to keep databases clean and consider the amount of space each table takes up. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## <span style=\"color:red\">Checkpoint: Putting It All Together</span>\n",
    "\n",
    "Look back to the motivating question: **What are the characteristics of the distribution of jobs by county and by metropolitan/micropolitan area?**\n",
    "\n",
    "Using what you know about joins and aggregation functions, try to answer the following questions:\n",
    "\n",
    "- Which counties have the most jobs?\n",
    "- Which counties do the workers live in the most?\n",
    "- Which metropolitan/micropolitan areas have the most jobs?\n",
    "- What are some summary statistics of jobs at the county or metropolitan/micropolitan level (e.g. average number of jobs per county)?\n",
    "\n",
    "Then, try creating a new table containing a joined table. You may use this newly created table to answer some of the questions above. After you're done, drop the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "\n"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "SQL",
   "language": "SQL",
   "name": "sql"
  },
  "language_info": {
   "codemirror_mode": "sql",
   "file_extension": ".sql",
   "mimetype": "",
   "name": "SQL",
   "nbconverter_exporter": "",
   "version": ""
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": false,
   "sideBar": false,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": false,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}