Spring 2017 Introduction.to.SQL.with.Python.-.Instructor.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### In this notebook we'll go over a brief introduction to the structure of the Sakila Database and setting up SQL in your Python Environment.\n",
    "#### DISCLAIMER:\n",
    "______________________________________________________________________________________________________\n",
    "There are many ways to browse through a SQL database, throughout this workshop we are going to be focusing on learning about SQL queries using a combination of Python, SQLite, and pandas. Please note that this is a pretty specific way of operating with a SQL Database, and may or may not fit other general needs. The primary goal of this section is to teach you how to use SQL queries to grab information and set it as a pandas DataFrame. We will not be going over more general topics of relational databases, MySQL, or using a SQL console directly.\n",
    "\n",
    "______________________________________________________________________________________________________\n",
    "\n",
    "#### Step 1: Download SQL Alchemy\n",
    "To start this appendix, download SQLAlchemy. You can do this by either downloading it\n",
    "by typing conda install sqlalchemy if you are using the Anaconda installation of Python.\n",
    "\n",
    "#### Step 2: Download SQLite Broswer\n",
    "Next up we will download a sql browser. We will be using SQLite Browser because it is lightweight and free to use.\n",
    "\n",
    "Download SQLite Browser here: http://sqlitebrowser.org/\n",
    "\n",
    "#### Step 3: Download the sakila Database\n",
    "You can download the fully constructed database here\n",
    "\n",
    "https://www.dropbox.com/s/6pz7vkl5c32xujt/sakila.db?dl=0\n",
    "\n",
    "Make sure to save it in the same directory as your iPython notebooks, or remember the file path for later so we can tell pandas exactly where to look for it.\n",
    "\n",
    "#### All done! Now let's look at the database before diving into how to work with it in Python.\n",
    "Check out the database either by opening it up using SQLite Browser or by checking out the diagram at this link: https://vladlight.files.wordpress.com/2012/12/sakilaerm.png\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<img src=\"https://vladlight.files.wordpress.com/2012/12/sakilaerm.png\"/>"
      ],
      "text/plain": [
       "<IPython.core.display.Image object>"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from IPython.display import Image\n",
    "Image(url='https://vladlight.files.wordpress.com/2012/12/sakilaerm.png')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have seen an overview of what the database looks like, let's go ahead and learn how to communicate with it with Python and pandas.\n",
    "\n",
    "Python comes with SQLite3, which provides a lightweight disk-based database that doesn't require a seperate server process. It's useful to prototype with SQLite and then port the code to a larger database system, like MySQL. Python comes with a module to connect to a SQL database with SQLite. The module is SQLite3, let's go ahead and import it (and pandas as well)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To use the module, you must first create a Connection object that represents the database. If the database name already exists SQLite3 will automatically connect to it, if it does not exsist, SQLite3 will automatically create.\n",
    "\n",
    "Let's make the connection!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Connect to the database (again, downloaded from here: https://www.dropbox.com/s/6pz7vkl5c32xujt/sakila.db?dl=0)\n",
    "con = sqlite3.connect(\"sakila.db\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customer_id</th>\n",
       "      <th>store_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>email</th>\n",
       "      <th>address_id</th>\n",
       "      <th>active</th>\n",
       "      <th>create_date</th>\n",
       "      <th>last_update</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>MARY</td>\n",
       "      <td>SMITH</td>\n",
       "      <td>MARY.SMITH@sakilacustomer.org</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>PATRICIA</td>\n",
       "      <td>JOHNSON</td>\n",
       "      <td>PATRICIA.JOHNSON@sakilacustomer.org</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>LINDA</td>\n",
       "      <td>WILLIAMS</td>\n",
       "      <td>LINDA.WILLIAMS@sakilacustomer.org</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>BARBARA</td>\n",
       "      <td>JONES</td>\n",
       "      <td>BARBARA.JONES@sakilacustomer.org</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>ELIZABETH</td>\n",
       "      <td>BROWN</td>\n",
       "      <td>ELIZABETH.BROWN@sakilacustomer.org</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>JENNIFER</td>\n",
       "      <td>DAVIS</td>\n",
       "      <td>JENNIFER.DAVIS@sakilacustomer.org</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>MARIA</td>\n",
       "      <td>MILLER</td>\n",
       "      <td>MARIA.MILLER@sakilacustomer.org</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>SUSAN</td>\n",
       "      <td>WILSON</td>\n",
       "      <td>SUSAN.WILSON@sakilacustomer.org</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "      <td>MARGARET</td>\n",
       "      <td>MOORE</td>\n",
       "      <td>MARGARET.MOORE@sakilacustomer.org</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>DOROTHY</td>\n",
       "      <td>TAYLOR</td>\n",
       "      <td>DOROTHY.TAYLOR@sakilacustomer.org</td>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>11</td>\n",
       "      <td>2</td>\n",
       "      <td>LISA</td>\n",
       "      <td>ANDERSON</td>\n",
       "      <td>LISA.ANDERSON@sakilacustomer.org</td>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>NANCY</td>\n",
       "      <td>THOMAS</td>\n",
       "      <td>NANCY.THOMAS@sakilacustomer.org</td>\n",
       "      <td>16</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>13</td>\n",
       "      <td>2</td>\n",
       "      <td>KAREN</td>\n",
       "      <td>JACKSON</td>\n",
       "      <td>KAREN.JACKSON@sakilacustomer.org</td>\n",
       "      <td>17</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>14</td>\n",
       "      <td>2</td>\n",
       "      <td>BETTY</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>BETTY.WHITE@sakilacustomer.org</td>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "      <td>HELEN</td>\n",
       "      <td>HARRIS</td>\n",
       "      <td>HELEN.HARRIS@sakilacustomer.org</td>\n",
       "      <td>19</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>16</td>\n",
       "      <td>2</td>\n",
       "      <td>SANDRA</td>\n",
       "      <td>MARTIN</td>\n",
       "      <td>SANDRA.MARTIN@sakilacustomer.org</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>17</td>\n",
       "      <td>1</td>\n",
       "      <td>DONNA</td>\n",
       "      <td>THOMPSON</td>\n",
       "      <td>DONNA.THOMPSON@sakilacustomer.org</td>\n",
       "      <td>21</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>CAROL</td>\n",
       "      <td>GARCIA</td>\n",
       "      <td>CAROL.GARCIA@sakilacustomer.org</td>\n",
       "      <td>22</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>19</td>\n",
       "      <td>1</td>\n",
       "      <td>RUTH</td>\n",
       "      <td>MARTINEZ</td>\n",
       "      <td>RUTH.MARTINEZ@sakilacustomer.org</td>\n",
       "      <td>23</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>20</td>\n",
       "      <td>2</td>\n",
       "      <td>SHARON</td>\n",
       "      <td>ROBINSON</td>\n",
       "      <td>SHARON.ROBINSON@sakilacustomer.org</td>\n",
       "      <td>24</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>21</td>\n",
       "      <td>1</td>\n",
       "      <td>MICHELLE</td>\n",
       "      <td>CLARK</td>\n",
       "      <td>MICHELLE.CLARK@sakilacustomer.org</td>\n",
       "      <td>25</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>22</td>\n",
       "      <td>1</td>\n",
       "      <td>LAURA</td>\n",
       "      <td>RODRIGUEZ</td>\n",
       "      <td>LAURA.RODRIGUEZ@sakilacustomer.org</td>\n",
       "      <td>26</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>23</td>\n",
       "      <td>2</td>\n",
       "      <td>SARAH</td>\n",
       "      <td>LEWIS</td>\n",
       "      <td>SARAH.LEWIS@sakilacustomer.org</td>\n",
       "      <td>27</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>KIMBERLY</td>\n",
       "      <td>LEE</td>\n",
       "      <td>KIMBERLY.LEE@sakilacustomer.org</td>\n",
       "      <td>28</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>25</td>\n",
       "      <td>1</td>\n",
       "      <td>DEBORAH</td>\n",
       "      <td>WALKER</td>\n",
       "      <td>DEBORAH.WALKER@sakilacustomer.org</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>26</td>\n",
       "      <td>2</td>\n",
       "      <td>JESSICA</td>\n",
       "      <td>HALL</td>\n",
       "      <td>JESSICA.HALL@sakilacustomer.org</td>\n",
       "      <td>30</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>27</td>\n",
       "      <td>2</td>\n",
       "      <td>SHIRLEY</td>\n",
       "      <td>ALLEN</td>\n",
       "      <td>SHIRLEY.ALLEN@sakilacustomer.org</td>\n",
       "      <td>31</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>28</td>\n",
       "      <td>1</td>\n",
       "      <td>CYNTHIA</td>\n",
       "      <td>YOUNG</td>\n",
       "      <td>CYNTHIA.YOUNG@sakilacustomer.org</td>\n",
       "      <td>32</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>29</td>\n",
       "      <td>2</td>\n",
       "      <td>ANGELA</td>\n",
       "      <td>HERNANDEZ</td>\n",
       "      <td>ANGELA.HERNANDEZ@sakilacustomer.org</td>\n",
       "      <td>33</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>30</td>\n",
       "      <td>1</td>\n",
       "      <td>MELISSA</td>\n",
       "      <td>KING</td>\n",
       "      <td>MELISSA.KING@sakilacustomer.org</td>\n",
       "      <td>34</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>569</th>\n",
       "      <td>570</td>\n",
       "      <td>2</td>\n",
       "      <td>IVAN</td>\n",
       "      <td>CROMWELL</td>\n",
       "      <td>IVAN.CROMWELL@sakilacustomer.org</td>\n",
       "      <td>576</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:37.000</td>\n",
       "      <td>2011-09-14 18:10:44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>570</th>\n",
       "      <td>571</td>\n",
       "      <td>2</td>\n",
       "      <td>JOHNNIE</td>\n",
       "      <td>CHISHOLM</td>\n",
       "      <td>JOHNNIE.CHISHOLM@sakilacustomer.org</td>\n",
       "      <td>577</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:37.000</td>\n",
       "      <td>2011-09-14 18:10:44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>571</th>\n",
       "      <td>572</td>\n",
       "      <td>1</td>\n",
       "      <td>SIDNEY</td>\n",
       "      <td>BURLESON</td>\n",
       "      <td>SIDNEY.BURLESON@sakilacustomer.org</td>\n",
       "      <td>578</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:37.000</td>\n",
       "      <td>2011-09-14 18:10:44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>572</th>\n",
       "      <td>573</td>\n",
       "      <td>1</td>\n",
       "      <td>BYRON</td>\n",
       "      <td>BOX</td>\n",
       "      <td>BYRON.BOX@sakilacustomer.org</td>\n",
       "      <td>579</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:37.000</td>\n",
       "      <td>2011-09-14 18:10:44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>573</th>\n",
       "      <td>574</td>\n",
       "      <td>2</td>\n",
       "      <td>JULIAN</td>\n",
       "      <td>VEST</td>\n",
       "      <td>JULIAN.VEST@sakilacustomer.org</td>\n",
       "      <td>580</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:37.000</td>\n",
       "      <td>2011-09-14 18:10:44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>574</th>\n",
       "      <td>575</td>\n",
       "      <td>2</td>\n",
       "      <td>ISAAC</td>\n",
       "      <td>OGLESBY</td>\n",
       "      <td>ISAAC.OGLESBY@sakilacustomer.org</td>\n",
       "      <td>581</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:37.000</td>\n",
       "      <td>2011-09-14 18:10:44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>575</th>\n",
       "      <td>576</td>\n",
       "      <td>2</td>\n",
       "      <td>MORRIS</td>\n",
       "      <td>MCCARTER</td>\n",
       "      <td>MORRIS.MCCARTER@sakilacustomer.org</td>\n",
       "      <td>582</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:37.000</td>\n",
       "      <td>2011-09-14 18:10:44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>576</th>\n",
       "      <td>577</td>\n",
       "      <td>2</td>\n",
       "      <td>CLIFTON</td>\n",
       "      <td>MALCOLM</td>\n",
       "      <td>CLIFTON.MALCOLM@sakilacustomer.org</td>\n",
       "      <td>583</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:37.000</td>\n",
       "      <td>2011-09-14 18:10:44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>577</th>\n",
       "      <td>578</td>\n",
       "      <td>2</td>\n",
       "      <td>WILLARD</td>\n",
       "      <td>LUMPKIN</td>\n",
       "      <td>WILLARD.LUMPKIN@sakilacustomer.org</td>\n",
       "      <td>584</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:37.000</td>\n",
       "      <td>2011-09-14 18:10:44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>578</th>\n",
       "      <td>579</td>\n",
       "      <td>2</td>\n",
       "      <td>DARYL</td>\n",
       "      <td>LARUE</td>\n",
       "      <td>DARYL.LARUE@sakilacustomer.org</td>\n",
       "      <td>585</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:37.000</td>\n",
       "      <td>2011-09-14 18:10:44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>579</th>\n",
       "      <td>580</td>\n",
       "      <td>1</td>\n",
       "      <td>ROSS</td>\n",
       "      <td>GREY</td>\n",
       "      <td>ROSS.GREY@sakilacustomer.org</td>\n",
       "      <td>586</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:37.000</td>\n",
       "      <td>2011-09-14 18:10:44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>580</th>\n",
       "      <td>581</td>\n",
       "      <td>1</td>\n",
       "      <td>VIRGIL</td>\n",
       "      <td>WOFFORD</td>\n",
       "      <td>VIRGIL.WOFFORD@sakilacustomer.org</td>\n",
       "      <td>587</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:37.000</td>\n",
       "      <td>2011-09-14 18:10:44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>581</th>\n",
       "      <td>582</td>\n",
       "      <td>2</td>\n",
       "      <td>ANDY</td>\n",
       "      <td>VANHORN</td>\n",
       "      <td>ANDY.VANHORN@sakilacustomer.org</td>\n",
       "      <td>588</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:37.000</td>\n",
       "      <td>2011-09-14 18:10:44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>582</th>\n",
       "      <td>583</td>\n",
       "      <td>1</td>\n",
       "      <td>MARSHALL</td>\n",
       "      <td>THORN</td>\n",
       "      <td>MARSHALL.THORN@sakilacustomer.org</td>\n",
       "      <td>589</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:37.000</td>\n",
       "      <td>2011-09-14 18:10:44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>583</th>\n",
       "      <td>584</td>\n",
       "      <td>2</td>\n",
       "      <td>SALVADOR</td>\n",
       "      <td>TEEL</td>\n",
       "      <td>SALVADOR.TEEL@sakilacustomer.org</td>\n",
       "      <td>590</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:37.000</td>\n",
       "      <td>2011-09-14 18:10:44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>584</th>\n",
       "      <td>585</td>\n",
       "      <td>1</td>\n",
       "      <td>PERRY</td>\n",
       "      <td>SWAFFORD</td>\n",
       "      <td>PERRY.SWAFFORD@sakilacustomer.org</td>\n",
       "      <td>591</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:37.000</td>\n",
       "      <td>2011-09-14 18:10:44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>585</th>\n",
       "      <td>586</td>\n",
       "      <td>1</td>\n",
       "      <td>KIRK</td>\n",
       "      <td>STCLAIR</td>\n",
       "      <td>KIRK.STCLAIR@sakilacustomer.org</td>\n",
       "      <td>592</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:37.000</td>\n",
       "      <td>2011-09-14 18:10:44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>586</th>\n",
       "      <td>587</td>\n",
       "      <td>1</td>\n",
       "      <td>SERGIO</td>\n",
       "      <td>STANFIELD</td>\n",
       "      <td>SERGIO.STANFIELD@sakilacustomer.org</td>\n",
       "      <td>593</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:37.000</td>\n",
       "      <td>2011-09-14 18:10:44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>587</th>\n",
       "      <td>588</td>\n",
       "      <td>1</td>\n",
       "      <td>MARION</td>\n",
       "      <td>OCAMPO</td>\n",
       "      <td>MARION.OCAMPO@sakilacustomer.org</td>\n",
       "      <td>594</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:37.000</td>\n",
       "      <td>2011-09-14 18:10:44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>588</th>\n",
       "      <td>589</td>\n",
       "      <td>1</td>\n",
       "      <td>TRACY</td>\n",
       "      <td>HERRMANN</td>\n",
       "      <td>TRACY.HERRMANN@sakilacustomer.org</td>\n",
       "      <td>595</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:37.000</td>\n",
       "      <td>2011-09-14 18:10:44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>589</th>\n",
       "      <td>590</td>\n",
       "      <td>2</td>\n",
       "      <td>SETH</td>\n",
       "      <td>HANNON</td>\n",
       "      <td>SETH.HANNON@sakilacustomer.org</td>\n",
       "      <td>596</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:37.000</td>\n",
       "      <td>2011-09-14 18:10:44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>590</th>\n",
       "      <td>591</td>\n",
       "      <td>1</td>\n",
       "      <td>KENT</td>\n",
       "      <td>ARSENAULT</td>\n",
       "      <td>KENT.ARSENAULT@sakilacustomer.org</td>\n",
       "      <td>597</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:37.000</td>\n",
       "      <td>2011-09-14 18:10:44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>591</th>\n",
       "      <td>592</td>\n",
       "      <td>1</td>\n",
       "      <td>TERRANCE</td>\n",
       "      <td>ROUSH</td>\n",
       "      <td>TERRANCE.ROUSH@sakilacustomer.org</td>\n",
       "      <td>598</td>\n",
       "      <td>0</td>\n",
       "      <td>2006-02-14 22:04:37.000</td>\n",
       "      <td>2011-09-14 18:10:44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>592</th>\n",
       "      <td>593</td>\n",
       "      <td>2</td>\n",
       "      <td>RENE</td>\n",
       "      <td>MCALISTER</td>\n",
       "      <td>RENE.MCALISTER@sakilacustomer.org</td>\n",
       "      <td>599</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:37.000</td>\n",
       "      <td>2011-09-14 18:10:44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>593</th>\n",
       "      <td>594</td>\n",
       "      <td>1</td>\n",
       "      <td>EDUARDO</td>\n",
       "      <td>HIATT</td>\n",
       "      <td>EDUARDO.HIATT@sakilacustomer.org</td>\n",
       "      <td>600</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:37.000</td>\n",
       "      <td>2011-09-14 18:10:44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>594</th>\n",
       "      <td>595</td>\n",
       "      <td>1</td>\n",
       "      <td>TERRENCE</td>\n",
       "      <td>GUNDERSON</td>\n",
       "      <td>TERRENCE.GUNDERSON@sakilacustomer.org</td>\n",
       "      <td>601</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:37.000</td>\n",
       "      <td>2011-09-14 18:10:44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>595</th>\n",
       "      <td>596</td>\n",
       "      <td>1</td>\n",
       "      <td>ENRIQUE</td>\n",
       "      <td>FORSYTHE</td>\n",
       "      <td>ENRIQUE.FORSYTHE@sakilacustomer.org</td>\n",
       "      <td>602</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:37.000</td>\n",
       "      <td>2011-09-14 18:10:44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>596</th>\n",
       "      <td>597</td>\n",
       "      <td>1</td>\n",
       "      <td>FREDDIE</td>\n",
       "      <td>DUGGAN</td>\n",
       "      <td>FREDDIE.DUGGAN@sakilacustomer.org</td>\n",
       "      <td>603</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:37.000</td>\n",
       "      <td>2011-09-14 18:10:44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>597</th>\n",
       "      <td>598</td>\n",
       "      <td>1</td>\n",
       "      <td>WADE</td>\n",
       "      <td>DELVALLE</td>\n",
       "      <td>WADE.DELVALLE@sakilacustomer.org</td>\n",
       "      <td>604</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:37.000</td>\n",
       "      <td>2011-09-14 18:10:44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>598</th>\n",
       "      <td>599</td>\n",
       "      <td>2</td>\n",
       "      <td>AUSTIN</td>\n",
       "      <td>CINTRON</td>\n",
       "      <td>AUSTIN.CINTRON@sakilacustomer.org</td>\n",
       "      <td>605</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:37.000</td>\n",
       "      <td>2011-09-14 18:10:44</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>599 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     customer_id  store_id first_name  last_name  \\\n",
       "0              1         1       MARY      SMITH   \n",
       "1              2         1   PATRICIA    JOHNSON   \n",
       "2              3         1      LINDA   WILLIAMS   \n",
       "3              4         2    BARBARA      JONES   \n",
       "4              5         1  ELIZABETH      BROWN   \n",
       "5              6         2   JENNIFER      DAVIS   \n",
       "6              7         1      MARIA     MILLER   \n",
       "7              8         2      SUSAN     WILSON   \n",
       "8              9         2   MARGARET      MOORE   \n",
       "9             10         1    DOROTHY     TAYLOR   \n",
       "10            11         2       LISA   ANDERSON   \n",
       "11            12         1      NANCY     THOMAS   \n",
       "12            13         2      KAREN    JACKSON   \n",
       "13            14         2      BETTY      WHITE   \n",
       "14            15         1      HELEN     HARRIS   \n",
       "15            16         2     SANDRA     MARTIN   \n",
       "16            17         1      DONNA   THOMPSON   \n",
       "17            18         2      CAROL     GARCIA   \n",
       "18            19         1       RUTH   MARTINEZ   \n",
       "19            20         2     SHARON   ROBINSON   \n",
       "20            21         1   MICHELLE      CLARK   \n",
       "21            22         1      LAURA  RODRIGUEZ   \n",
       "22            23         2      SARAH      LEWIS   \n",
       "23            24         2   KIMBERLY        LEE   \n",
       "24            25         1    DEBORAH     WALKER   \n",
       "25            26         2    JESSICA       HALL   \n",
       "26            27         2    SHIRLEY      ALLEN   \n",
       "27            28         1    CYNTHIA      YOUNG   \n",
       "28            29         2     ANGELA  HERNANDEZ   \n",
       "29            30         1    MELISSA       KING   \n",
       "..           ...       ...        ...        ...   \n",
       "569          570         2       IVAN   CROMWELL   \n",
       "570          571         2    JOHNNIE   CHISHOLM   \n",
       "571          572         1     SIDNEY   BURLESON   \n",
       "572          573         1      BYRON        BOX   \n",
       "573          574         2     JULIAN       VEST   \n",
       "574          575         2      ISAAC    OGLESBY   \n",
       "575          576         2     MORRIS   MCCARTER   \n",
       "576          577         2    CLIFTON    MALCOLM   \n",
       "577          578         2    WILLARD    LUMPKIN   \n",
       "578          579         2      DARYL      LARUE   \n",
       "579          580         1       ROSS       GREY   \n",
       "580          581         1     VIRGIL    WOFFORD   \n",
       "581          582         2       ANDY    VANHORN   \n",
       "582          583         1   MARSHALL      THORN   \n",
       "583          584         2   SALVADOR       TEEL   \n",
       "584          585         1      PERRY   SWAFFORD   \n",
       "585          586         1       KIRK    STCLAIR   \n",
       "586          587         1     SERGIO  STANFIELD   \n",
       "587          588         1     MARION     OCAMPO   \n",
       "588          589         1      TRACY   HERRMANN   \n",
       "589          590         2       SETH     HANNON   \n",
       "590          591         1       KENT  ARSENAULT   \n",
       "591          592         1   TERRANCE      ROUSH   \n",
       "592          593         2       RENE  MCALISTER   \n",
       "593          594         1    EDUARDO      HIATT   \n",
       "594          595         1   TERRENCE  GUNDERSON   \n",
       "595          596         1    ENRIQUE   FORSYTHE   \n",
       "596          597         1    FREDDIE     DUGGAN   \n",
       "597          598         1       WADE   DELVALLE   \n",
       "598          599         2     AUSTIN    CINTRON   \n",
       "\n",
       "                                     email  address_id active  \\\n",
       "0            MARY.SMITH@sakilacustomer.org           5      1   \n",
       "1      PATRICIA.JOHNSON@sakilacustomer.org           6      1   \n",
       "2        LINDA.WILLIAMS@sakilacustomer.org           7      1   \n",
       "3         BARBARA.JONES@sakilacustomer.org           8      1   \n",
       "4       ELIZABETH.BROWN@sakilacustomer.org           9      1   \n",
       "5        JENNIFER.DAVIS@sakilacustomer.org          10      1   \n",
       "6          MARIA.MILLER@sakilacustomer.org          11      1   \n",
       "7          SUSAN.WILSON@sakilacustomer.org          12      1   \n",
       "8        MARGARET.MOORE@sakilacustomer.org          13      1   \n",
       "9        DOROTHY.TAYLOR@sakilacustomer.org          14      1   \n",
       "10        LISA.ANDERSON@sakilacustomer.org          15      1   \n",
       "11         NANCY.THOMAS@sakilacustomer.org          16      1   \n",
       "12        KAREN.JACKSON@sakilacustomer.org          17      1   \n",
       "13          BETTY.WHITE@sakilacustomer.org          18      1   \n",
       "14         HELEN.HARRIS@sakilacustomer.org          19      1   \n",
       "15        SANDRA.MARTIN@sakilacustomer.org          20      0   \n",
       "16       DONNA.THOMPSON@sakilacustomer.org          21      1   \n",
       "17         CAROL.GARCIA@sakilacustomer.org          22      1   \n",
       "18        RUTH.MARTINEZ@sakilacustomer.org          23      1   \n",
       "19      SHARON.ROBINSON@sakilacustomer.org          24      1   \n",
       "20       MICHELLE.CLARK@sakilacustomer.org          25      1   \n",
       "21      LAURA.RODRIGUEZ@sakilacustomer.org          26      1   \n",
       "22          SARAH.LEWIS@sakilacustomer.org          27      1   \n",
       "23         KIMBERLY.LEE@sakilacustomer.org          28      1   \n",
       "24       DEBORAH.WALKER@sakilacustomer.org          29      1   \n",
       "25         JESSICA.HALL@sakilacustomer.org          30      1   \n",
       "26        SHIRLEY.ALLEN@sakilacustomer.org          31      1   \n",
       "27        CYNTHIA.YOUNG@sakilacustomer.org          32      1   \n",
       "28     ANGELA.HERNANDEZ@sakilacustomer.org          33      1   \n",
       "29         MELISSA.KING@sakilacustomer.org          34      1   \n",
       "..                                     ...         ...    ...   \n",
       "569       IVAN.CROMWELL@sakilacustomer.org         576      1   \n",
       "570    JOHNNIE.CHISHOLM@sakilacustomer.org         577      1   \n",
       "571     SIDNEY.BURLESON@sakilacustomer.org         578      1   \n",
       "572           BYRON.BOX@sakilacustomer.org         579      1   \n",
       "573         JULIAN.VEST@sakilacustomer.org         580      1   \n",
       "574       ISAAC.OGLESBY@sakilacustomer.org         581      1   \n",
       "575     MORRIS.MCCARTER@sakilacustomer.org         582      1   \n",
       "576     CLIFTON.MALCOLM@sakilacustomer.org         583      1   \n",
       "577     WILLARD.LUMPKIN@sakilacustomer.org         584      1   \n",
       "578         DARYL.LARUE@sakilacustomer.org         585      1   \n",
       "579           ROSS.GREY@sakilacustomer.org         586      1   \n",
       "580      VIRGIL.WOFFORD@sakilacustomer.org         587      1   \n",
       "581        ANDY.VANHORN@sakilacustomer.org         588      1   \n",
       "582      MARSHALL.THORN@sakilacustomer.org         589      1   \n",
       "583       SALVADOR.TEEL@sakilacustomer.org         590      1   \n",
       "584      PERRY.SWAFFORD@sakilacustomer.org         591      1   \n",
       "585        KIRK.STCLAIR@sakilacustomer.org         592      1   \n",
       "586    SERGIO.STANFIELD@sakilacustomer.org         593      1   \n",
       "587       MARION.OCAMPO@sakilacustomer.org         594      1   \n",
       "588      TRACY.HERRMANN@sakilacustomer.org         595      1   \n",
       "589         SETH.HANNON@sakilacustomer.org         596      1   \n",
       "590      KENT.ARSENAULT@sakilacustomer.org         597      1   \n",
       "591      TERRANCE.ROUSH@sakilacustomer.org         598      0   \n",
       "592      RENE.MCALISTER@sakilacustomer.org         599      1   \n",
       "593       EDUARDO.HIATT@sakilacustomer.org         600      1   \n",
       "594  TERRENCE.GUNDERSON@sakilacustomer.org         601      1   \n",
       "595    ENRIQUE.FORSYTHE@sakilacustomer.org         602      1   \n",
       "596      FREDDIE.DUGGAN@sakilacustomer.org         603      1   \n",
       "597       WADE.DELVALLE@sakilacustomer.org         604      1   \n",
       "598      AUSTIN.CINTRON@sakilacustomer.org         605      1   \n",
       "\n",
       "                 create_date          last_update  \n",
       "0    2006-02-14 22:04:36.000  2011-09-14 18:10:28  \n",
       "1    2006-02-14 22:04:36.000  2011-09-14 18:10:28  \n",
       "2    2006-02-14 22:04:36.000  2011-09-14 18:10:28  \n",
       "3    2006-02-14 22:04:36.000  2011-09-14 18:10:28  \n",
       "4    2006-02-14 22:04:36.000  2011-09-14 18:10:28  \n",
       "5    2006-02-14 22:04:36.000  2011-09-14 18:10:28  \n",
       "6    2006-02-14 22:04:36.000  2011-09-14 18:10:28  \n",
       "7    2006-02-14 22:04:36.000  2011-09-14 18:10:28  \n",
       "8    2006-02-14 22:04:36.000  2011-09-14 18:10:28  \n",
       "9    2006-02-14 22:04:36.000  2011-09-14 18:10:28  \n",
       "10   2006-02-14 22:04:36.000  2011-09-14 18:10:28  \n",
       "11   2006-02-14 22:04:36.000  2011-09-14 18:10:28  \n",
       "12   2006-02-14 22:04:36.000  2011-09-14 18:10:28  \n",
       "13   2006-02-14 22:04:36.000  2011-09-14 18:10:28  \n",
       "14   2006-02-14 22:04:36.000  2011-09-14 18:10:28  \n",
       "15   2006-02-14 22:04:36.000  2011-09-14 18:10:28  \n",
       "16   2006-02-14 22:04:36.000  2011-09-14 18:10:28  \n",
       "17   2006-02-14 22:04:36.000  2011-09-14 18:10:28  \n",
       "18   2006-02-14 22:04:36.000  2011-09-14 18:10:28  \n",
       "19   2006-02-14 22:04:36.000  2011-09-14 18:10:28  \n",
       "20   2006-02-14 22:04:36.000  2011-09-14 18:10:28  \n",
       "21   2006-02-14 22:04:36.000  2011-09-14 18:10:28  \n",
       "22   2006-02-14 22:04:36.000  2011-09-14 18:10:28  \n",
       "23   2006-02-14 22:04:36.000  2011-09-14 18:10:28  \n",
       "24   2006-02-14 22:04:36.000  2011-09-14 18:10:29  \n",
       "25   2006-02-14 22:04:36.000  2011-09-14 18:10:29  \n",
       "26   2006-02-14 22:04:36.000  2011-09-14 18:10:29  \n",
       "27   2006-02-14 22:04:36.000  2011-09-14 18:10:29  \n",
       "28   2006-02-14 22:04:36.000  2011-09-14 18:10:29  \n",
       "29   2006-02-14 22:04:36.000  2011-09-14 18:10:29  \n",
       "..                       ...                  ...  \n",
       "569  2006-02-14 22:04:37.000  2011-09-14 18:10:44  \n",
       "570  2006-02-14 22:04:37.000  2011-09-14 18:10:44  \n",
       "571  2006-02-14 22:04:37.000  2011-09-14 18:10:44  \n",
       "572  2006-02-14 22:04:37.000  2011-09-14 18:10:44  \n",
       "573  2006-02-14 22:04:37.000  2011-09-14 18:10:44  \n",
       "574  2006-02-14 22:04:37.000  2011-09-14 18:10:44  \n",
       "575  2006-02-14 22:04:37.000  2011-09-14 18:10:44  \n",
       "576  2006-02-14 22:04:37.000  2011-09-14 18:10:44  \n",
       "577  2006-02-14 22:04:37.000  2011-09-14 18:10:44  \n",
       "578  2006-02-14 22:04:37.000  2011-09-14 18:10:44  \n",
       "579  2006-02-14 22:04:37.000  2011-09-14 18:10:44  \n",
       "580  2006-02-14 22:04:37.000  2011-09-14 18:10:44  \n",
       "581  2006-02-14 22:04:37.000  2011-09-14 18:10:44  \n",
       "582  2006-02-14 22:04:37.000  2011-09-14 18:10:44  \n",
       "583  2006-02-14 22:04:37.000  2011-09-14 18:10:44  \n",
       "584  2006-02-14 22:04:37.000  2011-09-14 18:10:44  \n",
       "585  2006-02-14 22:04:37.000  2011-09-14 18:10:44  \n",
       "586  2006-02-14 22:04:37.000  2011-09-14 18:10:44  \n",
       "587  2006-02-14 22:04:37.000  2011-09-14 18:10:44  \n",
       "588  2006-02-14 22:04:37.000  2011-09-14 18:10:44  \n",
       "589  2006-02-14 22:04:37.000  2011-09-14 18:10:44  \n",
       "590  2006-02-14 22:04:37.000  2011-09-14 18:10:44  \n",
       "591  2006-02-14 22:04:37.000  2011-09-14 18:10:44  \n",
       "592  2006-02-14 22:04:37.000  2011-09-14 18:10:44  \n",
       "593  2006-02-14 22:04:37.000  2011-09-14 18:10:44  \n",
       "594  2006-02-14 22:04:37.000  2011-09-14 18:10:44  \n",
       "595  2006-02-14 22:04:37.000  2011-09-14 18:10:44  \n",
       "596  2006-02-14 22:04:37.000  2011-09-14 18:10:44  \n",
       "597  2006-02-14 22:04:37.000  2011-09-14 18:10:44  \n",
       "598  2006-02-14 22:04:37.000  2011-09-14 18:10:44  \n",
       "\n",
       "[599 rows x 9 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Set SQL query as a comment\n",
    "sql_query = ''' SELECT * FROM customer '''\n",
    "\n",
    "# Use pandas to pass sql query using connection form SQLite3\n",
    "df = pd.read_sql(sql_query, con)\n",
    "\n",
    "# Show the resulting DataFrame\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### SQL SELECT Statement\n",
    "The SELECT statement is used to select data from a database. The result is then stored in a result table, sometimes called the result-set.\n",
    "\n",
    "### Syntax for SQL SELECT\n",
    "SELECT column_name FROM table_name\n",
    "\n",
    "We could also select multiple columns:\n",
    "\n",
    "SELECT column_name1,column_name2 \n",
    "FROM table_name\n",
    "\n",
    "Or we could select everything in a table using *\n",
    "\n",
    "SELECT * FROM table_name\n",
    "\n",
    "To see how this and multiple other queries work, we'll connect to the database and make a function that automatically takes in our query and returns a DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Set function as our sql_to_pandas\n",
    "\n",
    "def sql_to_df(sql_query):\n",
    "\n",
    "    # Use pandas to pass sql query using connection form SQLite3\n",
    "    df = pd.read_sql(sql_query, con)\n",
    "\n",
    "    # Show the resulting DataFrame\n",
    "    return df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Selecting Multiple Columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>MARY</td>\n",
       "      <td>SMITH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>PATRICIA</td>\n",
       "      <td>JOHNSON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>LINDA</td>\n",
       "      <td>WILLIAMS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BARBARA</td>\n",
       "      <td>JONES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ELIZABETH</td>\n",
       "      <td>BROWN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  first_name last_name\n",
       "0       MARY     SMITH\n",
       "1   PATRICIA   JOHNSON\n",
       "2      LINDA  WILLIAMS\n",
       "3    BARBARA     JONES\n",
       "4  ELIZABETH     BROWN"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Select multiple columns example\n",
    "query = ''' SELECT first_name,last_name\n",
    "            FROM customer; '''\n",
    "\n",
    "# Grab from first two columns\n",
    "sql_to_df(query).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Selecting Everything from table with *"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customer_id</th>\n",
       "      <th>store_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>email</th>\n",
       "      <th>address_id</th>\n",
       "      <th>active</th>\n",
       "      <th>create_date</th>\n",
       "      <th>last_update</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>MARY</td>\n",
       "      <td>SMITH</td>\n",
       "      <td>MARY.SMITH@sakilacustomer.org</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>PATRICIA</td>\n",
       "      <td>JOHNSON</td>\n",
       "      <td>PATRICIA.JOHNSON@sakilacustomer.org</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>LINDA</td>\n",
       "      <td>WILLIAMS</td>\n",
       "      <td>LINDA.WILLIAMS@sakilacustomer.org</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>BARBARA</td>\n",
       "      <td>JONES</td>\n",
       "      <td>BARBARA.JONES@sakilacustomer.org</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>ELIZABETH</td>\n",
       "      <td>BROWN</td>\n",
       "      <td>ELIZABETH.BROWN@sakilacustomer.org</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:28</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customer_id  store_id first_name last_name  \\\n",
       "0            1         1       MARY     SMITH   \n",
       "1            2         1   PATRICIA   JOHNSON   \n",
       "2            3         1      LINDA  WILLIAMS   \n",
       "3            4         2    BARBARA     JONES   \n",
       "4            5         1  ELIZABETH     BROWN   \n",
       "\n",
       "                                 email  address_id active  \\\n",
       "0        MARY.SMITH@sakilacustomer.org           5      1   \n",
       "1  PATRICIA.JOHNSON@sakilacustomer.org           6      1   \n",
       "2    LINDA.WILLIAMS@sakilacustomer.org           7      1   \n",
       "3     BARBARA.JONES@sakilacustomer.org           8      1   \n",
       "4   ELIZABETH.BROWN@sakilacustomer.org           9      1   \n",
       "\n",
       "               create_date          last_update  \n",
       "0  2006-02-14 22:04:36.000  2011-09-14 18:10:28  \n",
       "1  2006-02-14 22:04:36.000  2011-09-14 18:10:28  \n",
       "2  2006-02-14 22:04:36.000  2011-09-14 18:10:28  \n",
       "3  2006-02-14 22:04:36.000  2011-09-14 18:10:28  \n",
       "4  2006-02-14 22:04:36.000  2011-09-14 18:10:28  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Select multiple colums example\n",
    "query = '''SELECT * \n",
    "           FROM customer;'''\n",
    "\n",
    "# Grab\n",
    "sql_to_df(query).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Syntax for the SQL DISTINCT Statement\n",
    "\n",
    "In a table, a column may contain duplicate values; and sometimes you only want to list the distinct (unique) values. The DISTINCT keyword can be used to return only distinct (unique) values.\n",
    "\n",
    "SELECT DISTINCT column_name\n",
    "FROM table_name;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   country_id\n",
       "0           1\n",
       "1           2\n",
       "2           3\n",
       "3           4\n",
       "4           5"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Select distinct country_ids from the city table.\n",
    "\n",
    "query = '''SELECT DISTINCT(country_id)\n",
    "           FROM city'''\n",
    "\n",
    "sql_to_df(query).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Syntax for the SQL WHERE\n",
    "\n",
    "The WHERE clause is used to filter records, and is used to extract only the records that fulfill the specific parameter.\n",
    "\n",
    "SELECT column_name\n",
    "FROM table_name\n",
    "WHERE column_name (math operator) desired_value;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customer_id</th>\n",
       "      <th>store_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>email</th>\n",
       "      <th>address_id</th>\n",
       "      <th>active</th>\n",
       "      <th>create_date</th>\n",
       "      <th>last_update</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>MARY</td>\n",
       "      <td>SMITH</td>\n",
       "      <td>MARY.SMITH@sakilacustomer.org</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>PATRICIA</td>\n",
       "      <td>JOHNSON</td>\n",
       "      <td>PATRICIA.JOHNSON@sakilacustomer.org</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>LINDA</td>\n",
       "      <td>WILLIAMS</td>\n",
       "      <td>LINDA.WILLIAMS@sakilacustomer.org</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>ELIZABETH</td>\n",
       "      <td>BROWN</td>\n",
       "      <td>ELIZABETH.BROWN@sakilacustomer.org</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>MARIA</td>\n",
       "      <td>MILLER</td>\n",
       "      <td>MARIA.MILLER@sakilacustomer.org</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:28</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customer_id  store_id first_name last_name  \\\n",
       "0            1         1       MARY     SMITH   \n",
       "1            2         1   PATRICIA   JOHNSON   \n",
       "2            3         1      LINDA  WILLIAMS   \n",
       "3            5         1  ELIZABETH     BROWN   \n",
       "4            7         1      MARIA    MILLER   \n",
       "\n",
       "                                 email  address_id active  \\\n",
       "0        MARY.SMITH@sakilacustomer.org           5      1   \n",
       "1  PATRICIA.JOHNSON@sakilacustomer.org           6      1   \n",
       "2    LINDA.WILLIAMS@sakilacustomer.org           7      1   \n",
       "3   ELIZABETH.BROWN@sakilacustomer.org           9      1   \n",
       "4      MARIA.MILLER@sakilacustomer.org          11      1   \n",
       "\n",
       "               create_date          last_update  \n",
       "0  2006-02-14 22:04:36.000  2011-09-14 18:10:28  \n",
       "1  2006-02-14 22:04:36.000  2011-09-14 18:10:28  \n",
       "2  2006-02-14 22:04:36.000  2011-09-14 18:10:28  \n",
       "3  2006-02-14 22:04:36.000  2011-09-14 18:10:28  \n",
       "4  2006-02-14 22:04:36.000  2011-09-14 18:10:28  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Select all customer info from the 1st store.\n",
    "\n",
    "query = '''SELECT *\n",
    "           FROM customer\n",
    "           WHERE store_id = 1'''\n",
    "\n",
    "sql_to_df(query).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note, there are a variety of logical operators you can use for a SQL request.\n",
    "\n",
    "\n",
    "\n",
    "<table>\n",
    "<tr>\n",
    "<th>Operator</th>\n",
    "<th>Description</th>\n",
    "</tr>\n",
    "<tr>\n",
    "<td>%</td>\n",
    "<td> Equal</td>\n",
    "</tr>\n",
    "<tr>\n",
    "<td><></td>\n",
    "<td>Not equal. Note: In some versions of SQL this operator may be written  !=</td>\n",
    "</tr>\n",
    "<tr>\n",
    "<td>></td>\n",
    "<td> Greater than</td>\n",
    "</tr>\n",
    "<tr>\n",
    "<td><</td>\n",
    "<td> Less than\n",
    "</td>\n",
    "</tr>\n",
    "<tr>\n",
    "<td>>=</td>\n",
    "<td> Greater than or equal</td>\n",
    "</tr>\n",
    "<tr>\n",
    "<td><=</td>\n",
    "<td> Less than or equal</td>\n",
    "</tr>\n",
    "</table>\n",
    "\n",
    "SQL requires single quotes around text values, while numeric fields are not enclosed in quotes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customer_id</th>\n",
       "      <th>store_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>email</th>\n",
       "      <th>address_id</th>\n",
       "      <th>active</th>\n",
       "      <th>create_date</th>\n",
       "      <th>last_update</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>MARY</td>\n",
       "      <td>SMITH</td>\n",
       "      <td>MARY.SMITH@sakilacustomer.org</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:28</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customer_id  store_id first_name last_name                          email  \\\n",
       "0            1         1       MARY     SMITH  MARY.SMITH@sakilacustomer.org   \n",
       "\n",
       "   address_id active              create_date          last_update  \n",
       "0           5      1  2006-02-14 22:04:36.000  2011-09-14 18:10:28  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = '''SELECT *\n",
    "           FROM customer\n",
    "           WHERE first_name = 'MARY' '''\n",
    "\n",
    "sql_to_df(query).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Syntax for AND\n",
    "\n",
    "The AND operator is used to filter records based on more than one condition.\n",
    "\n",
    "The AND operator displays a record if both the first condition AND the second condition are true."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>film_id</th>\n",
       "      <th>title</th>\n",
       "      <th>description</th>\n",
       "      <th>release_year</th>\n",
       "      <th>language_id</th>\n",
       "      <th>original_language_id</th>\n",
       "      <th>rental_duration</th>\n",
       "      <th>rental_rate</th>\n",
       "      <th>length</th>\n",
       "      <th>replacement_cost</th>\n",
       "      <th>rating</th>\n",
       "      <th>special_features</th>\n",
       "      <th>last_update</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8</td>\n",
       "      <td>AIRPORT POLLOCK</td>\n",
       "      <td>A Epic Tale of a Moose And a Girl who must Con...</td>\n",
       "      <td>2006</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>6</td>\n",
       "      <td>4.99</td>\n",
       "      <td>54</td>\n",
       "      <td>15.99</td>\n",
       "      <td>R</td>\n",
       "      <td>Trailers</td>\n",
       "      <td>2011-09-14 18:05:33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>17</td>\n",
       "      <td>ALONE TRIP</td>\n",
       "      <td>A Fast-Paced Character Study of a Composer And...</td>\n",
       "      <td>2006</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>3</td>\n",
       "      <td>0.99</td>\n",
       "      <td>82</td>\n",
       "      <td>14.99</td>\n",
       "      <td>R</td>\n",
       "      <td>Trailers,Behind the Scenes</td>\n",
       "      <td>2011-09-14 18:05:33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20</td>\n",
       "      <td>AMELIE HELLFIGHTERS</td>\n",
       "      <td>A Boring Drama of a Woman And a Squirrel who m...</td>\n",
       "      <td>2006</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>4</td>\n",
       "      <td>4.99</td>\n",
       "      <td>79</td>\n",
       "      <td>23.99</td>\n",
       "      <td>R</td>\n",
       "      <td>Commentaries,Deleted Scenes,Behind the Scenes</td>\n",
       "      <td>2011-09-14 18:05:33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>21</td>\n",
       "      <td>AMERICAN CIRCUS</td>\n",
       "      <td>A Insightful Drama of a Girl And a Astronaut w...</td>\n",
       "      <td>2006</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>3</td>\n",
       "      <td>4.99</td>\n",
       "      <td>129</td>\n",
       "      <td>17.99</td>\n",
       "      <td>R</td>\n",
       "      <td>Commentaries,Behind the Scenes</td>\n",
       "      <td>2011-09-14 18:05:33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>23</td>\n",
       "      <td>ANACONDA CONFESSIONS</td>\n",
       "      <td>A Lacklusture Display of a Dentist And a Denti...</td>\n",
       "      <td>2006</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>3</td>\n",
       "      <td>0.99</td>\n",
       "      <td>92</td>\n",
       "      <td>9.99</td>\n",
       "      <td>R</td>\n",
       "      <td>Trailers,Deleted Scenes</td>\n",
       "      <td>2011-09-14 18:05:33</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   film_id                 title  \\\n",
       "0        8       AIRPORT POLLOCK   \n",
       "1       17            ALONE TRIP   \n",
       "2       20   AMELIE HELLFIGHTERS   \n",
       "3       21       AMERICAN CIRCUS   \n",
       "4       23  ANACONDA CONFESSIONS   \n",
       "\n",
       "                                         description release_year  \\\n",
       "0  A Epic Tale of a Moose And a Girl who must Con...         2006   \n",
       "1  A Fast-Paced Character Study of a Composer And...         2006   \n",
       "2  A Boring Drama of a Woman And a Squirrel who m...         2006   \n",
       "3  A Insightful Drama of a Girl And a Astronaut w...         2006   \n",
       "4  A Lacklusture Display of a Dentist And a Denti...         2006   \n",
       "\n",
       "   language_id original_language_id  rental_duration  rental_rate  length  \\\n",
       "0            1                 None                6         4.99      54   \n",
       "1            1                 None                3         0.99      82   \n",
       "2            1                 None                4         4.99      79   \n",
       "3            1                 None                3         4.99     129   \n",
       "4            1                 None                3         0.99      92   \n",
       "\n",
       "   replacement_cost rating                               special_features  \\\n",
       "0             15.99      R                                       Trailers   \n",
       "1             14.99      R                     Trailers,Behind the Scenes   \n",
       "2             23.99      R  Commentaries,Deleted Scenes,Behind the Scenes   \n",
       "3             17.99      R                 Commentaries,Behind the Scenes   \n",
       "4              9.99      R                        Trailers,Deleted Scenes   \n",
       "\n",
       "           last_update  \n",
       "0  2011-09-14 18:05:33  \n",
       "1  2011-09-14 18:05:33  \n",
       "2  2011-09-14 18:05:33  \n",
       "3  2011-09-14 18:05:33  \n",
       "4  2011-09-14 18:05:33  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Select all films from 2006 that are rated R.\n",
    "\n",
    "query = '''SELECT *\n",
    "           FROM film\n",
    "           WHERE release_year = 2006\n",
    "           AND rating = 'R' '''\n",
    "\n",
    "sql_to_df(query).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Syntax for OR\n",
    "\n",
    "The OR operator displays a record if either the first condition OR the second condition is true."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>film_id</th>\n",
       "      <th>title</th>\n",
       "      <th>description</th>\n",
       "      <th>release_year</th>\n",
       "      <th>language_id</th>\n",
       "      <th>original_language_id</th>\n",
       "      <th>rental_duration</th>\n",
       "      <th>rental_rate</th>\n",
       "      <th>length</th>\n",
       "      <th>replacement_cost</th>\n",
       "      <th>rating</th>\n",
       "      <th>special_features</th>\n",
       "      <th>last_update</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>ACADEMY DINOSAUR</td>\n",
       "      <td>A Epic Drama of a Feminist And a Mad Scientist...</td>\n",
       "      <td>2006</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>6</td>\n",
       "      <td>0.99</td>\n",
       "      <td>86</td>\n",
       "      <td>20.99</td>\n",
       "      <td>PG</td>\n",
       "      <td>Deleted Scenes,Behind the Scenes</td>\n",
       "      <td>2011-09-14 18:05:32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6</td>\n",
       "      <td>AGENT TRUMAN</td>\n",
       "      <td>A Intrepid Panorama of a Robot And a Boy who m...</td>\n",
       "      <td>2006</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>3</td>\n",
       "      <td>2.99</td>\n",
       "      <td>169</td>\n",
       "      <td>17.99</td>\n",
       "      <td>PG</td>\n",
       "      <td>Deleted Scenes</td>\n",
       "      <td>2011-09-14 18:05:33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8</td>\n",
       "      <td>AIRPORT POLLOCK</td>\n",
       "      <td>A Epic Tale of a Moose And a Girl who must Con...</td>\n",
       "      <td>2006</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>6</td>\n",
       "      <td>4.99</td>\n",
       "      <td>54</td>\n",
       "      <td>15.99</td>\n",
       "      <td>R</td>\n",
       "      <td>Trailers</td>\n",
       "      <td>2011-09-14 18:05:33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12</td>\n",
       "      <td>ALASKA PHANTOM</td>\n",
       "      <td>A Fanciful Saga of a Hunter And a Pastry Chef ...</td>\n",
       "      <td>2006</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>6</td>\n",
       "      <td>0.99</td>\n",
       "      <td>136</td>\n",
       "      <td>22.99</td>\n",
       "      <td>PG</td>\n",
       "      <td>Commentaries,Deleted Scenes</td>\n",
       "      <td>2011-09-14 18:05:33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>13</td>\n",
       "      <td>ALI FOREVER</td>\n",
       "      <td>A Action-Packed Drama of a Dentist And a Croco...</td>\n",
       "      <td>2006</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>4</td>\n",
       "      <td>4.99</td>\n",
       "      <td>150</td>\n",
       "      <td>21.99</td>\n",
       "      <td>PG</td>\n",
       "      <td>Deleted Scenes,Behind the Scenes</td>\n",
       "      <td>2011-09-14 18:05:33</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   film_id             title  \\\n",
       "0        1  ACADEMY DINOSAUR   \n",
       "1        6      AGENT TRUMAN   \n",
       "2        8   AIRPORT POLLOCK   \n",
       "3       12    ALASKA PHANTOM   \n",
       "4       13       ALI FOREVER   \n",
       "\n",
       "                                         description release_year  \\\n",
       "0  A Epic Drama of a Feminist And a Mad Scientist...         2006   \n",
       "1  A Intrepid Panorama of a Robot And a Boy who m...         2006   \n",
       "2  A Epic Tale of a Moose And a Girl who must Con...         2006   \n",
       "3  A Fanciful Saga of a Hunter And a Pastry Chef ...         2006   \n",
       "4  A Action-Packed Drama of a Dentist And a Croco...         2006   \n",
       "\n",
       "   language_id original_language_id  rental_duration  rental_rate  length  \\\n",
       "0            1                 None                6         0.99      86   \n",
       "1            1                 None                3         2.99     169   \n",
       "2            1                 None                6         4.99      54   \n",
       "3            1                 None                6         0.99     136   \n",
       "4            1                 None                4         4.99     150   \n",
       "\n",
       "   replacement_cost rating                  special_features  \\\n",
       "0             20.99     PG  Deleted Scenes,Behind the Scenes   \n",
       "1             17.99     PG                    Deleted Scenes   \n",
       "2             15.99      R                          Trailers   \n",
       "3             22.99     PG       Commentaries,Deleted Scenes   \n",
       "4             21.99     PG  Deleted Scenes,Behind the Scenes   \n",
       "\n",
       "           last_update  \n",
       "0  2011-09-14 18:05:32  \n",
       "1  2011-09-14 18:05:33  \n",
       "2  2011-09-14 18:05:33  \n",
       "3  2011-09-14 18:05:33  \n",
       "4  2011-09-14 18:05:33  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Select all films rated R or PG\n",
    "\n",
    "query = '''SELECT *\n",
    "           FROM film\n",
    "           WHERE rating = 'PG'\n",
    "           OR rating = 'R' '''\n",
    "\n",
    "sql_to_df(query).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Before we begin with Wildcards, ORDER BY, and GROUP BY. Let's take a look at aggregate functions.\n",
    "\n",
    "* AVG() - Returns the average value.\n",
    "* COUNT() - Returns the number of rows.\n",
    "* FIRST() - Returns the first value.\n",
    "* LAST() - Returns the last value.\n",
    "* MAX() - Returns the largest value.\n",
    "* MIN() - Returns the smallest value.\n",
    "* SUM() - Returns the sum.\n",
    "\n",
    "You can call any of these aggregate functions on a column to get the resulting values back. For example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>COUNT(customer_id)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>599</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   COUNT(customer_id)\n",
       "0                 599"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Count the number of customers\n",
    "query = ''' SELECT COUNT(customer_id)\n",
    "            FROM customer; '''\n",
    "\n",
    "# Grab \n",
    "sql_to_df(query).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The usual syntax is:\n",
    "\n",
    "SELECT column_name, aggregate_function(column_name) <br/>\n",
    "FROM table_name <br/>\n",
    "WHERE column_name"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## SQL Wildcards\n",
    "\n",
    "A wildcard character can be used to substitute for any other characters in a string. In SQL, wildcard characters are used with the SQL LIKE operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.\n",
    "\n",
    "There are several wildcard operators:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<table>\n",
    "<tr>\n",
    "<th>Wildcard</th>\n",
    "<th>Description</th>\n",
    "</tr>\n",
    "<tr>\n",
    "<td>%</td>\n",
    "<td>A substitute for zero or more characters</td>\n",
    "</tr>\n",
    "<tr>\n",
    "<td>_</td>\n",
    "<td>A substitute for a single character</td>\n",
    "</tr>\n",
    "<tr>\n",
    "<td>[character_list]</td>\n",
    "<td>Sets and ranges of characters to match</td>\n",
    "</tr>\n",
    "</table>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customer_id</th>\n",
       "      <th>store_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>email</th>\n",
       "      <th>address_id</th>\n",
       "      <th>active</th>\n",
       "      <th>create_date</th>\n",
       "      <th>last_update</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>MARY</td>\n",
       "      <td>SMITH</td>\n",
       "      <td>MARY.SMITH@sakilacustomer.org</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>MARIA</td>\n",
       "      <td>MILLER</td>\n",
       "      <td>MARIA.MILLER@sakilacustomer.org</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "      <td>MARGARET</td>\n",
       "      <td>MOORE</td>\n",
       "      <td>MARGARET.MOORE@sakilacustomer.org</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>21</td>\n",
       "      <td>1</td>\n",
       "      <td>MICHELLE</td>\n",
       "      <td>CLARK</td>\n",
       "      <td>MICHELLE.CLARK@sakilacustomer.org</td>\n",
       "      <td>25</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>30</td>\n",
       "      <td>1</td>\n",
       "      <td>MELISSA</td>\n",
       "      <td>KING</td>\n",
       "      <td>MELISSA.KING@sakilacustomer.org</td>\n",
       "      <td>34</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:29</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customer_id  store_id first_name last_name  \\\n",
       "0            1         1       MARY     SMITH   \n",
       "1            7         1      MARIA    MILLER   \n",
       "2            9         2   MARGARET     MOORE   \n",
       "3           21         1   MICHELLE     CLARK   \n",
       "4           30         1    MELISSA      KING   \n",
       "\n",
       "                               email  address_id active  \\\n",
       "0      MARY.SMITH@sakilacustomer.org           5      1   \n",
       "1    MARIA.MILLER@sakilacustomer.org          11      1   \n",
       "2  MARGARET.MOORE@sakilacustomer.org          13      1   \n",
       "3  MICHELLE.CLARK@sakilacustomer.org          25      1   \n",
       "4    MELISSA.KING@sakilacustomer.org          34      1   \n",
       "\n",
       "               create_date          last_update  \n",
       "0  2006-02-14 22:04:36.000  2011-09-14 18:10:28  \n",
       "1  2006-02-14 22:04:36.000  2011-09-14 18:10:28  \n",
       "2  2006-02-14 22:04:36.000  2011-09-14 18:10:28  \n",
       "3  2006-02-14 22:04:36.000  2011-09-14 18:10:28  \n",
       "4  2006-02-14 22:04:36.000  2011-09-14 18:10:29  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# First the % wildcard\n",
    "\n",
    "# Select any customers whose name start with an M\n",
    "query = ''' SELECT *\n",
    "            FROM customer\n",
    "            WHERE first_name LIKE 'M%' ; '''\n",
    "\n",
    "# Grab \n",
    "sql_to_df(query).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customer_id</th>\n",
       "      <th>store_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>email</th>\n",
       "      <th>address_id</th>\n",
       "      <th>active</th>\n",
       "      <th>create_date</th>\n",
       "      <th>last_update</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>30</td>\n",
       "      <td>1</td>\n",
       "      <td>MELISSA</td>\n",
       "      <td>KING</td>\n",
       "      <td>MELISSA.KING@sakilacustomer.org</td>\n",
       "      <td>34</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:29</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customer_id  store_id first_name last_name  \\\n",
       "0           30         1    MELISSA      KING   \n",
       "\n",
       "                             email  address_id active  \\\n",
       "0  MELISSA.KING@sakilacustomer.org          34      1   \n",
       "\n",
       "               create_date          last_update  \n",
       "0  2006-02-14 22:04:36.000  2011-09-14 18:10:29  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Next the _ wildcard\n",
    "\n",
    "# Select any customers whose last name ends with ing\n",
    "query = ''' SELECT *\n",
    "            FROM customer\n",
    "            WHERE last_name LIKE '_ING' ; '''\n",
    "\n",
    "# Grab \n",
    "sql_to_df(query).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we will move on to the [Character_list] wildcard. \n",
    "### IMPORTANT NOTE!\n",
    "Using [charlist] with SQLite is a little different than with other SQL formats, such as MySQL. \n",
    "\n",
    "In MySQL you would use: \n",
    "\n",
    "WHERE value LIKE '[charlist]%'\n",
    "\n",
    "In SQLite you use: \n",
    "\n",
    "WHERE value GLOB '[charlist]*'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customer_id</th>\n",
       "      <th>store_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>email</th>\n",
       "      <th>address_id</th>\n",
       "      <th>active</th>\n",
       "      <th>create_date</th>\n",
       "      <th>last_update</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>BARBARA</td>\n",
       "      <td>JONES</td>\n",
       "      <td>BARBARA.JONES@sakilacustomer.org</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>14</td>\n",
       "      <td>2</td>\n",
       "      <td>BETTY</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>BETTY.WHITE@sakilacustomer.org</td>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>29</td>\n",
       "      <td>2</td>\n",
       "      <td>ANGELA</td>\n",
       "      <td>HERNANDEZ</td>\n",
       "      <td>ANGELA.HERNANDEZ@sakilacustomer.org</td>\n",
       "      <td>33</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>31</td>\n",
       "      <td>2</td>\n",
       "      <td>BRENDA</td>\n",
       "      <td>WRIGHT</td>\n",
       "      <td>BRENDA.WRIGHT@sakilacustomer.org</td>\n",
       "      <td>35</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>32</td>\n",
       "      <td>1</td>\n",
       "      <td>AMY</td>\n",
       "      <td>LOPEZ</td>\n",
       "      <td>AMY.LOPEZ@sakilacustomer.org</td>\n",
       "      <td>36</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:29</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customer_id  store_id first_name  last_name  \\\n",
       "0            4         2    BARBARA      JONES   \n",
       "1           14         2      BETTY      WHITE   \n",
       "2           29         2     ANGELA  HERNANDEZ   \n",
       "3           31         2     BRENDA     WRIGHT   \n",
       "4           32         1        AMY      LOPEZ   \n",
       "\n",
       "                                 email  address_id active  \\\n",
       "0     BARBARA.JONES@sakilacustomer.org           8      1   \n",
       "1       BETTY.WHITE@sakilacustomer.org          18      1   \n",
       "2  ANGELA.HERNANDEZ@sakilacustomer.org          33      1   \n",
       "3     BRENDA.WRIGHT@sakilacustomer.org          35      1   \n",
       "4         AMY.LOPEZ@sakilacustomer.org          36      1   \n",
       "\n",
       "               create_date          last_update  \n",
       "0  2006-02-14 22:04:36.000  2011-09-14 18:10:28  \n",
       "1  2006-02-14 22:04:36.000  2011-09-14 18:10:28  \n",
       "2  2006-02-14 22:04:36.000  2011-09-14 18:10:29  \n",
       "3  2006-02-14 22:04:36.000  2011-09-14 18:10:29  \n",
       "4  2006-02-14 22:04:36.000  2011-09-14 18:10:29  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Finally the [character_list] wildcard\n",
    "\n",
    "# Select any customers whose first name begins with an A or a B\n",
    "query = ''' SELECT *\n",
    "            FROM customer\n",
    "            WHERE first_name GLOB '[AB]*' ; '''\n",
    "\n",
    "# Grab \n",
    "sql_to_df(query).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SQL ORDER BY\n",
    "The ORDER BY keyword is used to sort the result-set by one or more columns. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in a descending order, you can use the DESC keyword. The syntax is:\n",
    "SELECT column_name \n",
    "FROM table_name\n",
    "ORDER BY column_name ASC|DESC\n",
    "Let's see it in action:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customer_id</th>\n",
       "      <th>store_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>email</th>\n",
       "      <th>address_id</th>\n",
       "      <th>active</th>\n",
       "      <th>create_date</th>\n",
       "      <th>last_update</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>505</td>\n",
       "      <td>1</td>\n",
       "      <td>RAFAEL</td>\n",
       "      <td>ABNEY</td>\n",
       "      <td>RAFAEL.ABNEY@sakilacustomer.org</td>\n",
       "      <td>510</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:37.000</td>\n",
       "      <td>2011-09-14 18:10:42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>504</td>\n",
       "      <td>1</td>\n",
       "      <td>NATHANIEL</td>\n",
       "      <td>ADAM</td>\n",
       "      <td>NATHANIEL.ADAM@sakilacustomer.org</td>\n",
       "      <td>509</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:37.000</td>\n",
       "      <td>2011-09-14 18:10:42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>36</td>\n",
       "      <td>2</td>\n",
       "      <td>KATHLEEN</td>\n",
       "      <td>ADAMS</td>\n",
       "      <td>KATHLEEN.ADAMS@sakilacustomer.org</td>\n",
       "      <td>40</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>96</td>\n",
       "      <td>1</td>\n",
       "      <td>DIANA</td>\n",
       "      <td>ALEXANDER</td>\n",
       "      <td>DIANA.ALEXANDER@sakilacustomer.org</td>\n",
       "      <td>100</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>470</td>\n",
       "      <td>1</td>\n",
       "      <td>GORDON</td>\n",
       "      <td>ALLARD</td>\n",
       "      <td>GORDON.ALLARD@sakilacustomer.org</td>\n",
       "      <td>475</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:37.000</td>\n",
       "      <td>2011-09-14 18:10:41</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customer_id  store_id first_name  last_name  \\\n",
       "0          505         1     RAFAEL      ABNEY   \n",
       "1          504         1  NATHANIEL       ADAM   \n",
       "2           36         2   KATHLEEN      ADAMS   \n",
       "3           96         1      DIANA  ALEXANDER   \n",
       "4          470         1     GORDON     ALLARD   \n",
       "\n",
       "                                email  address_id active  \\\n",
       "0     RAFAEL.ABNEY@sakilacustomer.org         510      1   \n",
       "1   NATHANIEL.ADAM@sakilacustomer.org         509      1   \n",
       "2   KATHLEEN.ADAMS@sakilacustomer.org          40      1   \n",
       "3  DIANA.ALEXANDER@sakilacustomer.org         100      1   \n",
       "4    GORDON.ALLARD@sakilacustomer.org         475      1   \n",
       "\n",
       "               create_date          last_update  \n",
       "0  2006-02-14 22:04:37.000  2011-09-14 18:10:42  \n",
       "1  2006-02-14 22:04:37.000  2011-09-14 18:10:42  \n",
       "2  2006-02-14 22:04:36.000  2011-09-14 18:10:29  \n",
       "3  2006-02-14 22:04:36.000  2011-09-14 18:10:30  \n",
       "4  2006-02-14 22:04:37.000  2011-09-14 18:10:41  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Select all customers and order results by last name\n",
    "query = ''' SELECT *\n",
    "            FROM customer\n",
    "            ORDER BY last_name ; '''\n",
    "\n",
    "# Grab \n",
    "sql_to_df(query).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customer_id</th>\n",
       "      <th>store_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>email</th>\n",
       "      <th>address_id</th>\n",
       "      <th>active</th>\n",
       "      <th>create_date</th>\n",
       "      <th>last_update</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>28</td>\n",
       "      <td>1</td>\n",
       "      <td>CYNTHIA</td>\n",
       "      <td>YOUNG</td>\n",
       "      <td>CYNTHIA.YOUNG@sakilacustomer.org</td>\n",
       "      <td>32</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>413</td>\n",
       "      <td>2</td>\n",
       "      <td>MARVIN</td>\n",
       "      <td>YEE</td>\n",
       "      <td>MARVIN.YEE@sakilacustomer.org</td>\n",
       "      <td>418</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:37.000</td>\n",
       "      <td>2011-09-14 18:10:40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>402</td>\n",
       "      <td>1</td>\n",
       "      <td>LUIS</td>\n",
       "      <td>YANEZ</td>\n",
       "      <td>LUIS.YANEZ@sakilacustomer.org</td>\n",
       "      <td>407</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:37.000</td>\n",
       "      <td>2011-09-14 18:10:39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>318</td>\n",
       "      <td>1</td>\n",
       "      <td>BRIAN</td>\n",
       "      <td>WYMAN</td>\n",
       "      <td>BRIAN.WYMAN@sakilacustomer.org</td>\n",
       "      <td>323</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:37.000</td>\n",
       "      <td>2011-09-14 18:10:37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>31</td>\n",
       "      <td>2</td>\n",
       "      <td>BRENDA</td>\n",
       "      <td>WRIGHT</td>\n",
       "      <td>BRENDA.WRIGHT@sakilacustomer.org</td>\n",
       "      <td>35</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14 22:04:36.000</td>\n",
       "      <td>2011-09-14 18:10:29</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customer_id  store_id first_name last_name  \\\n",
       "0           28         1    CYNTHIA     YOUNG   \n",
       "1          413         2     MARVIN       YEE   \n",
       "2          402         1       LUIS     YANEZ   \n",
       "3          318         1      BRIAN     WYMAN   \n",
       "4           31         2     BRENDA    WRIGHT   \n",
       "\n",
       "                              email  address_id active  \\\n",
       "0  CYNTHIA.YOUNG@sakilacustomer.org          32      1   \n",
       "1     MARVIN.YEE@sakilacustomer.org         418      1   \n",
       "2     LUIS.YANEZ@sakilacustomer.org         407      1   \n",
       "3    BRIAN.WYMAN@sakilacustomer.org         323      1   \n",
       "4  BRENDA.WRIGHT@sakilacustomer.org          35      1   \n",
       "\n",
       "               create_date          last_update  \n",
       "0  2006-02-14 22:04:36.000  2011-09-14 18:10:29  \n",
       "1  2006-02-14 22:04:37.000  2011-09-14 18:10:40  \n",
       "2  2006-02-14 22:04:37.000  2011-09-14 18:10:39  \n",
       "3  2006-02-14 22:04:37.000  2011-09-14 18:10:37  \n",
       "4  2006-02-14 22:04:36.000  2011-09-14 18:10:29  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Select all customers and order results by last name, DESCENDING\n",
    "query = ''' SELECT *\n",
    "            FROM customer\n",
    "            ORDER BY last_name DESC; '''\n",
    "\n",
    "# Grab \n",
    "sql_to_df(query).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## SQL GROUP BY \n",
    "\n",
    "The GROUP BY statement is used with the aggregate functions to group the results by one or more columns. The syntax is:\n",
    "\n",
    "SELECT column_name, aggregate_function(column_name) <br/>\n",
    "FROM table_name <br/>\n",
    "WHERE column_name operator value <br/>\n",
    "GROUP BY column_name; \n",
    "\n",
    "Let's see how it works."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>store_id</th>\n",
       "      <th>COUNT(customer_id)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>326</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>273</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   store_id  COUNT(customer_id)\n",
       "0         1                 326\n",
       "1         2                 273"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Count the number of customers per store\n",
    "\n",
    "query = ''' SELECT store_id , COUNT(customer_id)\n",
    "            FROM customer\n",
    "            GROUP BY store_id; '''\n",
    "\n",
    "# Grab \n",
    "sql_to_df(query).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SQL Joins"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The JOIN statement is used to merge columns between two different tables in a relational database. \n",
    "\n",
    "We will look at four different types of JOIN statements: http://stackoverflow.com/questions/17946221/sql-join-and-different-types-of-joins"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customer_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>address_id</th>\n",
       "      <th>address</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>MARY</td>\n",
       "      <td>5</td>\n",
       "      <td>1913 Hanoi Way</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>PATRICIA</td>\n",
       "      <td>6</td>\n",
       "      <td>1121 Loja Avenue</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>LINDA</td>\n",
       "      <td>7</td>\n",
       "      <td>692 Joliet Street</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>BARBARA</td>\n",
       "      <td>8</td>\n",
       "      <td>1566 Inegl Manor</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>ELIZABETH</td>\n",
       "      <td>9</td>\n",
       "      <td>53 Idfu Parkway</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customer_id first_name  address_id            address\n",
       "0            1       MARY           5     1913 Hanoi Way\n",
       "1            2   PATRICIA           6   1121 Loja Avenue\n",
       "2            3      LINDA           7  692 Joliet Street\n",
       "3            4    BARBARA           8   1566 Inegl Manor\n",
       "4            5  ELIZABETH           9    53 Idfu Parkway"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = '''SELECT customer.customer_id, customer.first_name, address.address_id, address.address\n",
    "FROM customer\n",
    "INNER JOIN address\n",
    "ON customer.address_id=address.address_id;'''\n",
    "\n",
    "sql_to_df(query).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>store_id</th>\n",
       "      <th>address_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Mike</td>\n",
       "      <td>Hillyer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>Jon</td>\n",
       "      <td>Stephens</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   store_id  address_id first_name last_name\n",
       "0         1           1       Mike   Hillyer\n",
       "1         2           2        Jon  Stephens"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = '''SELECT store.store_id, store.address_id, staff.first_name, staff.last_name\n",
    "FROM store\n",
    "LEFT JOIN staff\n",
    "ON store.store_id = staff.store_id\n",
    "ORDER BY store.store_id;'''\n",
    "\n",
    "sql_to_df(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>address</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AARON</td>\n",
       "      <td>SELBY</td>\n",
       "      <td>1519 Santiago de los Caballeros Loop</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ADAM</td>\n",
       "      <td>GOOCH</td>\n",
       "      <td>230 Urawa Drive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ADRIAN</td>\n",
       "      <td>CLARY</td>\n",
       "      <td>1986 Sivas Place</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AGNES</td>\n",
       "      <td>BISHOP</td>\n",
       "      <td>866 Shivapuri Manor</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ALAN</td>\n",
       "      <td>KAHN</td>\n",
       "      <td>753 Ilorin Avenue</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>ALBERT</td>\n",
       "      <td>CROUSE</td>\n",
       "      <td>1641 Changhwa Place</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>ALBERTO</td>\n",
       "      <td>HENNING</td>\n",
       "      <td>502 Mandi Bahauddin Parkway</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>ALEX</td>\n",
       "      <td>GRESHAM</td>\n",
       "      <td>251 Florencia Drive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>ALEXANDER</td>\n",
       "      <td>FENNELL</td>\n",
       "      <td>231 Kaliningrad Place</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>ALFRED</td>\n",
       "      <td>CASILLAS</td>\n",
       "      <td>1727 Matamoros Place</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>ALFREDO</td>\n",
       "      <td>MCADAMS</td>\n",
       "      <td>1407 Surakarta Manor</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>ALICE</td>\n",
       "      <td>STEWART</td>\n",
       "      <td>1135 Izumisano Parkway</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>ALICIA</td>\n",
       "      <td>MILLS</td>\n",
       "      <td>1963 Moscow Place</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>ALLAN</td>\n",
       "      <td>CORNISH</td>\n",
       "      <td>947 Trshavn Place</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>ALLEN</td>\n",
       "      <td>BUTTERFIELD</td>\n",
       "      <td>791 Salinas Street</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>ALLISON</td>\n",
       "      <td>STANLEY</td>\n",
       "      <td>754 Valencia Place</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>ALMA</td>\n",
       "      <td>AUSTIN</td>\n",
       "      <td>1074 Binzhou Manor</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>ALVIN</td>\n",
       "      <td>DELOACH</td>\n",
       "      <td>1447 Chatsworth Place</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>AMANDA</td>\n",
       "      <td>CARTER</td>\n",
       "      <td>671 Graz Street</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>AMBER</td>\n",
       "      <td>DIXON</td>\n",
       "      <td>1029 Dzerzinsk Manor</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   first_name    last_name                               address\n",
       "0       AARON        SELBY  1519 Santiago de los Caballeros Loop\n",
       "1        ADAM        GOOCH                       230 Urawa Drive\n",
       "2      ADRIAN        CLARY                      1986 Sivas Place\n",
       "3       AGNES       BISHOP                   866 Shivapuri Manor\n",
       "4        ALAN         KAHN                     753 Ilorin Avenue\n",
       "5      ALBERT       CROUSE                   1641 Changhwa Place\n",
       "6     ALBERTO      HENNING           502 Mandi Bahauddin Parkway\n",
       "7        ALEX      GRESHAM                   251 Florencia Drive\n",
       "8   ALEXANDER      FENNELL                 231 Kaliningrad Place\n",
       "9      ALFRED     CASILLAS                  1727 Matamoros Place\n",
       "10    ALFREDO      MCADAMS                  1407 Surakarta Manor\n",
       "11      ALICE      STEWART                1135 Izumisano Parkway\n",
       "12     ALICIA        MILLS                     1963 Moscow Place\n",
       "13      ALLAN      CORNISH                     947 Trshavn Place\n",
       "14      ALLEN  BUTTERFIELD                    791 Salinas Street\n",
       "15    ALLISON      STANLEY                    754 Valencia Place\n",
       "16       ALMA       AUSTIN                    1074 Binzhou Manor\n",
       "17      ALVIN      DELOACH                 1447 Chatsworth Place\n",
       "18     AMANDA       CARTER                       671 Graz Street\n",
       "19      AMBER        DIXON                  1029 Dzerzinsk Manor"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = '''SELECT customer.first_name, customer.last_name, address.address\n",
    "FROM customer\n",
    "LEFT JOIN address\n",
    "ON customer.address_id = address.address_id\n",
    "ORDER BY customer.first_name;'''\n",
    "sql_to_df(query).head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "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.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}