MySQL-JupyterNotebook.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "import pandas as pd\n",
    "from warnings import filterwarnings\n",
    "import pymysql\n",
    "filterwarnings('ignore', category=pymysql.Warning)\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "engine = create_engine('mysql+pymysql://root:Washka_10@localhost/sakila')  # connect to server"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>actor_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>last_update</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>PENELOPE</td>\n",
       "      <td>GUINESS</td>\n",
       "      <td>2006-02-15 04:34:33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>NICK</td>\n",
       "      <td>WAHLBERG</td>\n",
       "      <td>2006-02-15 04:34:33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>ED</td>\n",
       "      <td>CHASE</td>\n",
       "      <td>2006-02-15 04:34:33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>JENNIFER</td>\n",
       "      <td>DAVIS</td>\n",
       "      <td>2006-02-15 04:34:33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>JOHNNY</td>\n",
       "      <td>LOLLOBRIGIDA</td>\n",
       "      <td>2006-02-15 04:34:33</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   actor_id first_name     last_name         last_update\n",
       "0         1   PENELOPE       GUINESS 2006-02-15 04:34:33\n",
       "1         2       NICK      WAHLBERG 2006-02-15 04:34:33\n",
       "2         3         ED         CHASE 2006-02-15 04:34:33\n",
       "3         4   JENNIFER         DAVIS 2006-02-15 04:34:33\n",
       "4         5     JOHNNY  LOLLOBRIGIDA 2006-02-15 04:34:33"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"\n",
    "select * from actor\n",
    "\"\"\"\n",
    "query_return = pd.read_sql_query(sql_query, engine)\n",
    "query_return.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Actor_Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>PENELOPE GUINESS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NICK WAHLBERG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ED CHASE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>JENNIFER DAVIS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>JOHNNY LOLLOBRIGIDA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Actor_Name\n",
       "0     PENELOPE GUINESS\n",
       "1        NICK WAHLBERG\n",
       "2             ED CHASE\n",
       "3       JENNIFER DAVIS\n",
       "4  JOHNNY LOLLOBRIGIDA"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"\n",
    "select concat(first_name, \" \", last_name) as Actor_Name\n",
    "from actor\n",
    "\n",
    "\"\"\"\n",
    "query_return = pd.read_sql_query(sql_query, engine)\n",
    "query_return.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>actor_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>9</td>\n",
       "      <td>JOE</td>\n",
       "      <td>SWANK</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   actor_id first_name last_name\n",
       "0         9        JOE     SWANK"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"\n",
    "select actor_id, first_name, last_name from actor\n",
    "where first_name = \"Joe\"\n",
    "\"\"\"\n",
    "query_return = pd.read_sql_query(sql_query, engine)\n",
    "query_return.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>actor_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>last_update</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>14</td>\n",
       "      <td>VIVIEN</td>\n",
       "      <td>BERGEN</td>\n",
       "      <td>2006-02-15 04:34:33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>41</td>\n",
       "      <td>JODIE</td>\n",
       "      <td>DEGENERES</td>\n",
       "      <td>2006-02-15 04:34:33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>107</td>\n",
       "      <td>GINA</td>\n",
       "      <td>DEGENERES</td>\n",
       "      <td>2006-02-15 04:34:33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>166</td>\n",
       "      <td>NICK</td>\n",
       "      <td>DEGENERES</td>\n",
       "      <td>2006-02-15 04:34:33</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   actor_id first_name  last_name         last_update\n",
       "0        14     VIVIEN     BERGEN 2006-02-15 04:34:33\n",
       "1        41      JODIE  DEGENERES 2006-02-15 04:34:33\n",
       "2       107       GINA  DEGENERES 2006-02-15 04:34:33\n",
       "3       166       NICK  DEGENERES 2006-02-15 04:34:33"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"\n",
    "select * from actor\n",
    "where last_name like \"%%GEN%%\"\n",
    "\"\"\"\n",
    "query_return = pd.read_sql_query(sql_query, engine)\n",
    "query_return.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>last_name</th>\n",
       "      <th>first_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CHAPLIN</td>\n",
       "      <td>GREG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>JOLIE</td>\n",
       "      <td>WOODY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>OLIVIER</td>\n",
       "      <td>AUDREY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>OLIVIER</td>\n",
       "      <td>CUBA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>WILLIAMS</td>\n",
       "      <td>MORGAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>WILLIAMS</td>\n",
       "      <td>MUCHO GROUCHO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>WILLIAMS</td>\n",
       "      <td>SEAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>WILLIS</td>\n",
       "      <td>BEN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>WILLIS</td>\n",
       "      <td>GENE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>WILLIS</td>\n",
       "      <td>HUMPHREY</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  last_name     first_name\n",
       "0   CHAPLIN           GREG\n",
       "1     JOLIE          WOODY\n",
       "2   OLIVIER         AUDREY\n",
       "3   OLIVIER           CUBA\n",
       "4  WILLIAMS         MORGAN\n",
       "5  WILLIAMS  MUCHO GROUCHO\n",
       "6  WILLIAMS           SEAN\n",
       "7    WILLIS            BEN\n",
       "8    WILLIS           GENE\n",
       "9    WILLIS       HUMPHREY"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"\n",
    "select last_name, first_name from actor\n",
    "where last_name like \"%%LI%%\"\n",
    "order by last_name, first_name\n",
    "\n",
    "\"\"\"\n",
    "query_return = pd.read_sql_query(sql_query, engine)\n",
    "query_return"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country_id</th>\n",
       "      <th>country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Afghanistan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12</td>\n",
       "      <td>Bangladesh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>23</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   country_id      country\n",
       "0           1  Afghanistan\n",
       "1          12   Bangladesh\n",
       "2          23        China"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"\n",
    "select country_id, country from country\n",
    "where country IN (\"China\", \"Afghanistan\", \"Bangladesh\");\n",
    "\n",
    "\"\"\"\n",
    "query_return = pd.read_sql_query(sql_query, engine)\n",
    "query_return"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# done in MySQLHW (add middle_name column between first_name and last_name columns)\n",
    "# alter table actor\n",
    "# add column middle_name varchar(30) after first_name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>actor_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>last_update</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>PENELOPE</td>\n",
       "      <td>GUINESS</td>\n",
       "      <td>2006-02-15 04:34:33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>NICK</td>\n",
       "      <td>WAHLBERG</td>\n",
       "      <td>2006-02-15 04:34:33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>ED</td>\n",
       "      <td>CHASE</td>\n",
       "      <td>2006-02-15 04:34:33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>JENNIFER</td>\n",
       "      <td>DAVIS</td>\n",
       "      <td>2006-02-15 04:34:33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>JOHNNY</td>\n",
       "      <td>LOLLOBRIGIDA</td>\n",
       "      <td>2006-02-15 04:34:33</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   actor_id first_name     last_name         last_update\n",
       "0         1   PENELOPE       GUINESS 2006-02-15 04:34:33\n",
       "1         2       NICK      WAHLBERG 2006-02-15 04:34:33\n",
       "2         3         ED         CHASE 2006-02-15 04:34:33\n",
       "3         4   JENNIFER         DAVIS 2006-02-15 04:34:33\n",
       "4         5     JOHNNY  LOLLOBRIGIDA 2006-02-15 04:34:33"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"\n",
    "select * \n",
    "from actor\n",
    "\"\"\"\n",
    "query_return = pd.read_sql_query(sql_query, engine)\n",
    "query_return.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# done in MySQLHW (changed datatype of middle_name column to blob - A BLOB is a binary large object that can hold a variable amount of data.)\n",
    "# alter table actor\n",
    "# modify middle_name blob"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# done in MySQLHW (deleted middle_name column)\n",
    "# alter table actor\n",
    "# drop column middle_name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>actor_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>last_update</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>PENELOPE</td>\n",
       "      <td>GUINESS</td>\n",
       "      <td>2006-02-15 04:34:33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>NICK</td>\n",
       "      <td>WAHLBERG</td>\n",
       "      <td>2006-02-15 04:34:33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>ED</td>\n",
       "      <td>CHASE</td>\n",
       "      <td>2006-02-15 04:34:33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>JENNIFER</td>\n",
       "      <td>DAVIS</td>\n",
       "      <td>2006-02-15 04:34:33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>JOHNNY</td>\n",
       "      <td>LOLLOBRIGIDA</td>\n",
       "      <td>2006-02-15 04:34:33</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   actor_id first_name     last_name         last_update\n",
       "0         1   PENELOPE       GUINESS 2006-02-15 04:34:33\n",
       "1         2       NICK      WAHLBERG 2006-02-15 04:34:33\n",
       "2         3         ED         CHASE 2006-02-15 04:34:33\n",
       "3         4   JENNIFER         DAVIS 2006-02-15 04:34:33\n",
       "4         5     JOHNNY  LOLLOBRIGIDA 2006-02-15 04:34:33"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"\n",
    "select * \n",
    "from actor\n",
    "\"\"\"\n",
    "query_return = pd.read_sql_query(sql_query, engine)\n",
    "query_return.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>last_name</th>\n",
       "      <th>Name_Count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AKROYD</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ALLEN</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ASTAIRE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BACALL</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BAILEY</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  last_name  Name_Count\n",
       "0    AKROYD           3\n",
       "1     ALLEN           3\n",
       "2   ASTAIRE           1\n",
       "3    BACALL           1\n",
       "4    BAILEY           2"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"\n",
    "select last_name,\n",
    "count(last_name) as Name_Count\n",
    "from actor\n",
    "group by last_name\n",
    "order by last_name asc\n",
    "\"\"\"\n",
    "query_return = pd.read_sql_query(sql_query, engine)\n",
    "query_return.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>last_name</th>\n",
       "      <th>Name_Count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AKROYD</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ALLEN</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BAILEY</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BENING</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BERRY</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  last_name  Name_Count\n",
       "0    AKROYD           3\n",
       "1     ALLEN           3\n",
       "2    BAILEY           2\n",
       "3    BENING           2\n",
       "4     BERRY           3"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"\n",
    "select last_name,\n",
    "count(last_name) as Name_Count\n",
    "from actor\n",
    "group by last_name\n",
    "Having Name_Count >= 2\n",
    "order by last_name asc\n",
    "\"\"\"\n",
    "query_return = pd.read_sql_query(sql_query, engine)\n",
    "query_return.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# done in MySQLHW\n",
    "# UPDATE actor\n",
    "# SET first_name =\n",
    "# CASE\n",
    "# WHEN first_name = 'HARPO'\n",
    "# THEN 'GROUCHO'\n",
    "# ELSE 'MUCHO GROUCHO'\n",
    "# END\n",
    "# WHERE actor_id = 172;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>actor_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>last_update</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>PENELOPE</td>\n",
       "      <td>GUINESS</td>\n",
       "      <td>2006-02-15 04:34:33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>NICK</td>\n",
       "      <td>WAHLBERG</td>\n",
       "      <td>2006-02-15 04:34:33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>ED</td>\n",
       "      <td>CHASE</td>\n",
       "      <td>2006-02-15 04:34:33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>JENNIFER</td>\n",
       "      <td>DAVIS</td>\n",
       "      <td>2006-02-15 04:34:33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>JOHNNY</td>\n",
       "      <td>LOLLOBRIGIDA</td>\n",
       "      <td>2006-02-15 04:34:33</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   actor_id first_name     last_name         last_update\n",
       "0         1   PENELOPE       GUINESS 2006-02-15 04:34:33\n",
       "1         2       NICK      WAHLBERG 2006-02-15 04:34:33\n",
       "2         3         ED         CHASE 2006-02-15 04:34:33\n",
       "3         4   JENNIFER         DAVIS 2006-02-15 04:34:33\n",
       "4         5     JOHNNY  LOLLOBRIGIDA 2006-02-15 04:34:33"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"\n",
    "select * from actor\n",
    "\n",
    "\"\"\"\n",
    "query_return = pd.read_sql_query(sql_query, engine)\n",
    "query_return.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# done in MySQLHW\n",
    "# SHOW CREATE TABLE address"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>address_id</th>\n",
       "      <th>address</th>\n",
       "      <th>address2</th>\n",
       "      <th>district</th>\n",
       "      <th>city_id</th>\n",
       "      <th>postal_code</th>\n",
       "      <th>phone</th>\n",
       "      <th>location</th>\n",
       "      <th>last_update</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>47 MySakila Drive</td>\n",
       "      <td>None</td>\n",
       "      <td>Alberta</td>\n",
       "      <td>300</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>b'\\x00\\x00\\x00\\x00\\x01\\x01\\x00\\x00\\x00&gt;\\n2]c4\\...</td>\n",
       "      <td>2014-09-25 22:30:27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>28 MySQL Boulevard</td>\n",
       "      <td>None</td>\n",
       "      <td>QLD</td>\n",
       "      <td>576</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>b'\\x00\\x00\\x00\\x00\\x01\\x01\\x00\\x00\\x00\\x8e\\x10...</td>\n",
       "      <td>2014-09-25 22:30:09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>23 Workhaven Lane</td>\n",
       "      <td>None</td>\n",
       "      <td>Alberta</td>\n",
       "      <td>300</td>\n",
       "      <td></td>\n",
       "      <td>14033335568</td>\n",
       "      <td>b'\\x00\\x00\\x00\\x00\\x01\\x01\\x00\\x00\\x00\\xcd\\xc4...</td>\n",
       "      <td>2014-09-25 22:30:27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>1411 Lillydale Drive</td>\n",
       "      <td>None</td>\n",
       "      <td>QLD</td>\n",
       "      <td>576</td>\n",
       "      <td></td>\n",
       "      <td>6172235589</td>\n",
       "      <td>b'\\x00\\x00\\x00\\x00\\x01\\x01\\x00\\x00\\x00[\\r\\xe44...</td>\n",
       "      <td>2014-09-25 22:30:09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>1913 Hanoi Way</td>\n",
       "      <td></td>\n",
       "      <td>Nagasaki</td>\n",
       "      <td>463</td>\n",
       "      <td>35200</td>\n",
       "      <td>28303384290</td>\n",
       "      <td>b'\\x00\\x00\\x00\\x00\\x01\\x01\\x00\\x00\\x00(\\xd17\\x...</td>\n",
       "      <td>2014-09-25 22:31:53</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   address_id               address address2  district  city_id postal_code  \\\n",
       "0           1     47 MySakila Drive     None   Alberta      300               \n",
       "1           2    28 MySQL Boulevard     None       QLD      576               \n",
       "2           3     23 Workhaven Lane     None   Alberta      300               \n",
       "3           4  1411 Lillydale Drive     None       QLD      576               \n",
       "4           5        1913 Hanoi Way           Nagasaki      463       35200   \n",
       "\n",
       "         phone                                           location  \\\n",
       "0               b'\\x00\\x00\\x00\\x00\\x01\\x01\\x00\\x00\\x00>\\n2]c4\\...   \n",
       "1               b'\\x00\\x00\\x00\\x00\\x01\\x01\\x00\\x00\\x00\\x8e\\x10...   \n",
       "2  14033335568  b'\\x00\\x00\\x00\\x00\\x01\\x01\\x00\\x00\\x00\\xcd\\xc4...   \n",
       "3   6172235589  b'\\x00\\x00\\x00\\x00\\x01\\x01\\x00\\x00\\x00[\\r\\xe44...   \n",
       "4  28303384290  b'\\x00\\x00\\x00\\x00\\x01\\x01\\x00\\x00\\x00(\\xd17\\x...   \n",
       "\n",
       "          last_update  \n",
       "0 2014-09-25 22:30:27  \n",
       "1 2014-09-25 22:30:09  \n",
       "2 2014-09-25 22:30:27  \n",
       "3 2014-09-25 22:30:09  \n",
       "4 2014-09-25 22:31:53  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"\n",
    "select * from address\n",
    "\n",
    "\"\"\"\n",
    "query_return = pd.read_sql_query(sql_query, engine)\n",
    "query_return.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\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>Mike</td>\n",
       "      <td>Hillyer</td>\n",
       "      <td>23 Workhaven Lane</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jon</td>\n",
       "      <td>Stephens</td>\n",
       "      <td>1411 Lillydale Drive</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  first_name last_name               address\n",
       "0       Mike   Hillyer     23 Workhaven Lane\n",
       "1        Jon  Stephens  1411 Lillydale Drive"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"\n",
    "select staff.first_name, staff.last_name, address.address\n",
    "from address\n",
    "inner join staff on\n",
    "staff.address_id = address.address_id\n",
    "\"\"\"\n",
    "finished_query = pd.read_sql_query(sql_query, engine)\n",
    "finished_query.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\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>amount</th>\n",
       "      <th>payment_date</th>\n",
       "      <th>Total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mike</td>\n",
       "      <td>Hillyer</td>\n",
       "      <td>0.99</td>\n",
       "      <td>2005-08-02 18:01:38</td>\n",
       "      <td>11853.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jon</td>\n",
       "      <td>Stephens</td>\n",
       "      <td>4.99</td>\n",
       "      <td>2005-08-01 08:51:04</td>\n",
       "      <td>12218.48</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  first_name last_name  amount        payment_date     Total\n",
       "0       Mike   Hillyer    0.99 2005-08-02 18:01:38  11853.65\n",
       "1        Jon  Stephens    4.99 2005-08-01 08:51:04  12218.48"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"\n",
    "select staff.first_name, staff.last_name, payment.amount, payment.payment_date, sum(payment.amount) as 'Total'\n",
    "from staff\n",
    "inner join payment on\n",
    "staff.staff_id = payment.staff_id\n",
    "where month(payment.payment_date) = 08 and year(payment.payment_date) = 2005\n",
    "group by staff.staff_id\n",
    "\n",
    "\"\"\"\n",
    "finished_query = pd.read_sql_query(sql_query, engine)\n",
    "finished_query.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>film_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>439</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   film_id\n",
       "0      439"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"\n",
    "select film_id from film\n",
    "where title = \"Hunchback Impossible\"\n",
    "\"\"\"\n",
    "finished_query = pd.read_sql_query(sql_query, engine)\n",
    "finished_query.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>film_id</th>\n",
       "      <th>Total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>439</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   film_id  Total\n",
       "0      439      6"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"\n",
    "select film_id, \n",
    "count(film_id) as 'Total'\n",
    "from inventory\n",
    "where film_id = \"439\"\n",
    "\"\"\"\n",
    "finished_query = pd.read_sql_query(sql_query, engine)\n",
    "finished_query.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>KANE EXORCIST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>KARATE MOON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>KENTUCKIAN GIANT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>KICK SAVANNAH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>KILL BROTHERHOOD</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              title\n",
       "0     KANE EXORCIST\n",
       "1       KARATE MOON\n",
       "2  KENTUCKIAN GIANT\n",
       "3     KICK SAVANNAH\n",
       "4  KILL BROTHERHOOD"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"\n",
    "select title\n",
    "from film\n",
    "where (title LIKE 'K%%' OR title LIKE 'Q%%') \n",
    "and language_id=(SELECT language_id FROM language where name='English')\n",
    "\"\"\"\n",
    "finished_query = pd.read_sql_query(sql_query, engine)\n",
    "finished_query.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\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>ED</td>\n",
       "      <td>CHASE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>KARL</td>\n",
       "      <td>BERRY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>UMA</td>\n",
       "      <td>WOOD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>WOODY</td>\n",
       "      <td>JOLIE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SPENCER</td>\n",
       "      <td>DEPP</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  first_name last_name\n",
       "0         ED     CHASE\n",
       "1       KARL     BERRY\n",
       "2        UMA      WOOD\n",
       "3      WOODY     JOLIE\n",
       "4    SPENCER      DEPP"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"\n",
    "select first_name, last_name\n",
    "from actor\n",
    "where actor_id\n",
    "IN (select actor_id from film_actor where film_id \n",
    "IN (select film_id from film where title='ALONE TRIP'));\n",
    "\"\"\"\n",
    "finished_query = pd.read_sql_query(sql_query, engine)\n",
    "finished_query.head()\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\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>email</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>VERA</td>\n",
       "      <td>MCCOY</td>\n",
       "      <td>VERA.MCCOY@sakilacustomer.org</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MARIO</td>\n",
       "      <td>CHEATHAM</td>\n",
       "      <td>MARIO.CHEATHAM@sakilacustomer.org</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>JUDY</td>\n",
       "      <td>GRAY</td>\n",
       "      <td>JUDY.GRAY@sakilacustomer.org</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>JUNE</td>\n",
       "      <td>CARROLL</td>\n",
       "      <td>JUNE.CARROLL@sakilacustomer.org</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ANTHONY</td>\n",
       "      <td>SCHWAB</td>\n",
       "      <td>ANTHONY.SCHWAB@sakilacustomer.org</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  first_name last_name                              email\n",
       "0       VERA     MCCOY      VERA.MCCOY@sakilacustomer.org\n",
       "1      MARIO  CHEATHAM  MARIO.CHEATHAM@sakilacustomer.org\n",
       "2       JUDY      GRAY       JUDY.GRAY@sakilacustomer.org\n",
       "3       JUNE   CARROLL    JUNE.CARROLL@sakilacustomer.org\n",
       "4    ANTHONY    SCHWAB  ANTHONY.SCHWAB@sakilacustomer.org"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"\n",
    "select first_name, last_name, email \n",
    "from customer cu\n",
    "join address a ON (cu.address_id = a.address_id)\n",
    "join city cit ON (a.city_id=cit.city_id)\n",
    "join country cntry ON (cit.country_id=cntry.country_id)\n",
    "\"\"\"\n",
    "finished_query = pd.read_sql_query(sql_query, engine)\n",
    "finished_query.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AMADEUS HOLY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AMERICAN CIRCUS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ANTITRUST TOMATOES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ARK RIDGEMONT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BAREFOOT MANCHURIAN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 title\n",
       "0         AMADEUS HOLY\n",
       "1      AMERICAN CIRCUS\n",
       "2   ANTITRUST TOMATOES\n",
       "3        ARK RIDGEMONT\n",
       "4  BAREFOOT MANCHURIAN"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"\n",
    "select title from film f\n",
    "join film_category fcat on (f.film_id=fcat.film_id)\n",
    "join category c on (fcat.category_id=c.category_id);\n",
    "\"\"\"\n",
    "finished_query = pd.read_sql_query(sql_query, engine)\n",
    "finished_query.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>Count_of_Rented_Movies</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BUCKET BROTHERHOOD</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ROCKETEER MOTHER</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>GRIT CLOCKWORK</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>JUGGLER HARDLY</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>RIDGEMONT SUBMARINE</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 title  Count_of_Rented_Movies\n",
       "0   BUCKET BROTHERHOOD                      34\n",
       "1     ROCKETEER MOTHER                      33\n",
       "2       GRIT CLOCKWORK                      32\n",
       "3       JUGGLER HARDLY                      32\n",
       "4  RIDGEMONT SUBMARINE                      32"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"\n",
    "select title, COUNT(f.film_id) AS 'Count_of_Rented_Movies'\n",
    "from  film f\n",
    "join inventory i ON (f.film_id= i.film_id)\n",
    "join rental r ON (i.inventory_id=r.inventory_id)\n",
    "group by title ORDER BY Count_of_Rented_Movies DESC;\n",
    "\"\"\"\n",
    "finished_query = pd.read_sql_query(sql_query, engine)\n",
    "finished_query.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>store_id</th>\n",
       "      <th>SUM(p.amount)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>33489.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>33927.04</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   store_id  SUM(p.amount)\n",
       "0         1       33489.47\n",
       "1         2       33927.04"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"\n",
    "select s.store_id, SUM(p.amount) \n",
    "from payment p\n",
    "join staff s ON (p.staff_id=s.staff_id)\n",
    "group by store_id;\n",
    "\"\"\"\n",
    "finished_query = pd.read_sql_query(sql_query, engine)\n",
    "finished_query.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>store_id</th>\n",
       "      <th>city</th>\n",
       "      <th>country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Lethbridge</td>\n",
       "      <td>Canada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Woodridge</td>\n",
       "      <td>Australia</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   store_id        city    country\n",
       "0         1  Lethbridge     Canada\n",
       "1         2   Woodridge  Australia"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"\n",
    "select store_id, city, country FROM store s\n",
    "join address a on (s.address_id=a.address_id)\n",
    "join city c ON (a.city_id=c.city_id)\n",
    "join country cntry on (c.country_id=cntry.country_id);\n",
    "\"\"\"\n",
    "finished_query = pd.read_sql_query(sql_query, engine)\n",
    "finished_query.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Top Five</th>\n",
       "      <th>Gross</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Music</td>\n",
       "      <td>3417.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Travel</td>\n",
       "      <td>3549.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Classics</td>\n",
       "      <td>3639.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Children</td>\n",
       "      <td>3655.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Horror</td>\n",
       "      <td>3722.54</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Top Five    Gross\n",
       "0     Music  3417.72\n",
       "1    Travel  3549.64\n",
       "2  Classics  3639.59\n",
       "3  Children  3655.55\n",
       "4    Horror  3722.54"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"\n",
    "select c.name AS \"Top Five\", SUM(p.amount) AS \"Gross\" \n",
    "from category c\n",
    "join film_category fc on (c.category_id=fc.category_id)\n",
    "join inventory i on (fc.film_id=i.film_id)\n",
    "join rental r on (i.inventory_id=r.inventory_id)\n",
    "join payment p on (r.rental_id=p.rental_id)\n",
    "group by c.name order by Gross  limit 5;\n",
    "\"\"\"\n",
    "finished_query = pd.read_sql_query(sql_query, engine)\n",
    "finished_query.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# done in MySQLHW\n",
    "# create view Top_Five_Genres as\n",
    "# SELECT c.name AS \"Top Five\", SUM(p.amount) AS \"Gross\" \n",
    "# FROM category c\n",
    "# JOIN film_category fc ON (c.category_id=fc.category_id)\n",
    "# JOIN inventory i ON (fc.film_id=i.film_id)\n",
    "# JOIN rental r ON (i.inventory_id=r.inventory_id)\n",
    "# JOIN payment p ON (r.rental_id=p.rental_id)\n",
    "# GROUP BY c.name ORDER BY Gross  LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Top Five</th>\n",
       "      <th>Gross</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Music</td>\n",
       "      <td>3417.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Travel</td>\n",
       "      <td>3549.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Classics</td>\n",
       "      <td>3639.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Children</td>\n",
       "      <td>3655.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Horror</td>\n",
       "      <td>3722.54</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Top Five    Gross\n",
       "0     Music  3417.72\n",
       "1    Travel  3549.64\n",
       "2  Classics  3639.59\n",
       "3  Children  3655.55\n",
       "4    Horror  3722.54"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"\n",
    "select * from Top_Five_Genres\n",
    "\"\"\"\n",
    "finished_query = pd.read_sql_query(sql_query, engine)\n",
    "finished_query.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# done in MySQLHW\n",
    "# drop view Top_Five_Genres"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}