Lectures/Lecture_7/w4111-L7-Relational_ER_SQL_Final.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<style  type=\"text/css\"> \n",
    "cool {\n",
    "  width: 100px;\n",
    "  height: 100px;\n",
    "  background-color: red;\n",
    "  position: relative;\n",
    "  -webkit-animation-name: example; /* Safari 4.0 - 8.0 */\n",
    "  -webkit-animation-duration: 4s; /* Safari 4.0 - 8.0 */\n",
    "  -webkit-animation-iteration-count: infinite; /* Safari 4.0 - 8.0 */\n",
    "  animation-name: example;\n",
    "  animation-duration: 4s;\n",
    "  animation-iteration-count: infinite;\n",
    "}\n",
    "\n",
    "/* Safari 4.0 - 8.0 */\n",
    "@-webkit-keyframes example {\n",
    "  0%   {background-color:red; left:0px; top:0px;}\n",
    "  25%  {background-color:yellow; left:200px; top:0px;}\n",
    "  50%  {background-color:blue; left:200px; top:200px;}\n",
    "  75%  {background-color:green; left:0px; top:200px;}\n",
    "  100% {background-color:red; left:0px; top:0px;}\n",
    "}\n",
    "\n",
    "/* Standard syntax */\n",
    "@keyframes example {\n",
    "  0%   {background-color:red; left:0px; top:0px;}\n",
    "  25%  {background-color:yellow; left:200px; top:0px;}\n",
    "  50%  {background-color:blue; left:200px; top:200px;}\n",
    "  75%  {background-color:green; left:0px; top:200px;}\n",
    "  100% {background-color:red; left:0px; top:0px;}\n",
    "}\n",
    "</style>\n",
    "\n",
    "<b><center>\n",
    "<span style=\"font-size: 24pt; line-height: 1.2\">\n",
    "COMS W4111: Introduction to Databases\n",
    "</span>\n",
    "</center></b>\n",
    "</span><br>\n",
    "<p>\n",
    "<i><center>\n",
    "<span style=\"font-size: 20pt; line-height: 1.2\">\n",
    "Lecture : <br>ER, Relational, SQL Final Lecture (for now)<br>Examples\n",
    "</span>\n",
    "</center></i>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<u><span style=\"font-size: 24px;\">Initialize Environment</span></u>\n",
    "<br><br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The sql extension is already loaded. To reload it, use:\n",
      "  %reload_ext sql\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'Connected: dbuser@W4111Examples'"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%load_ext sql\n",
    "%sql mysql+pymysql://dbuser:dbuserdbuser@localhost/W4111Examples"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data and Time\n",
    "\n",
    "## Let's Create a Person Table\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://dbuser:***@localhost/W4111Examples\n",
      "0 rows affected.\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "drop table if exists Person;\n",
    "\n",
    "CREATE TABLE `W4111Examples`.`Person` (\n",
    "  `uni` VARCHAR(16) NOT NULL,\n",
    "  `auto_id` INT NOT NULL AUTO_INCREMENT,\n",
    "  `last_name` VARCHAR(64) NOT NULL,\n",
    "  `first_name` VARCHAR(64) NOT NULL,\n",
    "  `middle_initial` CHAR(1) NOT NULL,\n",
    "  `DOB` DATE NOT NULL,\n",
    "  UNIQUE INDEX `uni_UNIQUE` (`uni` ASC) VISIBLE,\n",
    "  PRIMARY KEY (`auto_id`),\n",
    "  INDEX `name_idx` (`last_name` ASC, `first_name` ASC, `middle_initial` ASC) VISIBLE);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Notes:__\n",
    "1. ```uni``` and ```auto_id``` are _candidate keys._ I chose ```auto_id``` for the primary key, and we will see why later in the lecture.<br><br>\n",
    "2. The index on ```last_name, first_name``` may significantly improve performance for SQL operations.\n",
    "    - The index is an index on both ```last_name``` alone, and ```last_name``` and ```first_name``` together.\n",
    "    - It is __not__ an index on ```first_name``` alone.\n",
    "    - Thus, it helps with\n",
    "        - ```where last_name='Ferguson' ```\n",
    "        - ```where last_name='Ferguson' and first_name='Donald' ```\n",
    "    - But does not help with,\n",
    "        - ```where first_name='Donald' ```\n",
    "        - ```where last_name='Ferguson' OR first_name='Donald' ```\n",
    "    - We will understand in the next module."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Date\n",
    "\n",
    "- A common problem in databases is that people like \"January 1, 2018\" and databases like ```TIMESTAMP``` or ```DATE``` types.\n",
    "\n",
    "\n",
    "- Let's make a test table and add some values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://dbuser:***@localhost/W4111Examples\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "CREATE TABLE `W4111Examples`.`test_date_time` (\n",
    "  `date_string` VARCHAR(64) NULL,\n",
    "  `time_string` VARCHAR(64) NULL,\n",
    "  `date_type` DATE NULL,\n",
    "  `time_stamp_type` TIMESTAMP NULL);\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://dbuser:***@localhost/W4111Examples\n",
      "2 rows affected.\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "insert into test_date_time(date_string)\n",
    "    values\n",
    "        ('January 1st, 1960'),\n",
    "        ('April 1st, 2019');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Why is this bad? One example is that it sorts that date as if they were strings, like names."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://dbuser:***@localhost/W4111Examples\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>date_string</th>\n",
       "        <th>time_string</th>\n",
       "        <th>date_type</th>\n",
       "        <th>time_stamp_type</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>April 1st, 2019</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>January 1st, 1960</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('April 1st, 2019', None, None, None),\n",
       " ('January 1st, 1960', None, None, None)]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select * from test_date_time order by date_string asc;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Most languages have functions and libraries to handle string/date and time conversion."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://dbuser:***@localhost/W4111Examples\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>date_string</th>\n",
       "        <th>parsed_string</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>January 1st, 1960</td>\n",
       "        <td>1960-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>April 1st, 2019</td>\n",
       "        <td>2019-04-01</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('January 1st, 1960', datetime.date(1960, 1, 1)),\n",
       " ('April 1st, 2019', datetime.date(2019, 4, 1))]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "select date_string, str_to_date(date_string, \"%M %D, %Y\") as parsed_string from\n",
    "    test_date_time;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- You can use in insert, update, etc."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://dbuser:***@localhost/W4111Examples\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "update test_date_time\n",
    "    set date_type = str_to_date(date_string, \"%M %D, %Y\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- And you can examine the information, and display in friendlier formats."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://dbuser:***@localhost/W4111Examples\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>date_type</th>\n",
       "        <th>formated_date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1960-01-01</td>\n",
       "        <td>(Fri) Jan-01-60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2019-04-01</td>\n",
       "        <td>(Mon) Apr-01-19</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.date(1960, 1, 1), '(Fri) Jan-01-60'),\n",
       " (datetime.date(2019, 4, 1), '(Mon) Apr-01-19')]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "    select date_type,\n",
    "        date_format(date_type, \"(%a) %b-%d-%y\") as formated_date\n",
    "    from\n",
    "        test_date_time;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Formatting and parsing is based on pattern string.\n",
    "\n",
    "<hr style=\"height:2px;\">\n",
    "<img src=\"./Lecture_7_Relational_ER_SQL_Final_18.jpg\">\n",
    "<hr style=\"height:2px;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Treating the value as a ```DATE``` type enables functions on the domain:\n",
    "    - Ordering\n",
    "    - ```BETWEEN```\n",
    "    - Operators"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://dbuser:***@localhost/W4111Examples\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>date_string</th>\n",
       "        <th>time_string</th>\n",
       "        <th>date_type</th>\n",
       "        <th>time_stamp_type</th>\n",
       "        <th>day_of_week</th>\n",
       "        <th>day_of_month</th>\n",
       "        <th>day_of_year</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>January 1st, 1960</td>\n",
       "        <td>None</td>\n",
       "        <td>1960-01-01</td>\n",
       "        <td>None</td>\n",
       "        <td>6</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>April 1st, 2019</td>\n",
       "        <td>None</td>\n",
       "        <td>2019-04-01</td>\n",
       "        <td>None</td>\n",
       "        <td>2</td>\n",
       "        <td>1</td>\n",
       "        <td>91</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('January 1st, 1960', None, datetime.date(1960, 1, 1), None, 6, 1, 1),\n",
       " ('April 1st, 2019', None, datetime.date(2019, 4, 1), None, 2, 1, 91)]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "    select\n",
    "        *,\n",
    "        dayofweek(date_type) as day_of_week,\n",
    "        dayofmonth(date_type) as day_of_month,\n",
    "        dayofyear(date_type) as day_of_year\n",
    "    from\n",
    "        test_date_time;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://dbuser:***@localhost/W4111Examples\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>days_between</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>-21640</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(-21640,)]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "    SELECT DATEDIFF(\"1960-01-01\", \"2019-04-01\") as days_between"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Computed (Generated) Field\n",
    "\n",
    "- You can create a view.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://dbuser:***@localhost/W4111Examples\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "create view personv as select\n",
    "\t*,\n",
    "    timestampdiff(year, dob, now()) as age\n",
    "    from\n",
    "    Person;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- We can use the view to automatically produce the person's age."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://dbuser:***@localhost/W4111Examples\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>uni</th>\n",
       "        <th>auto_id</th>\n",
       "        <th>last_name</th>\n",
       "        <th>first_name</th>\n",
       "        <th>middle_initial</th>\n",
       "        <th>DOB</th>\n",
       "        <th>age</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>mm1</td>\n",
       "        <td>1</td>\n",
       "        <td>Mouse</td>\n",
       "        <td>Mickey</td>\n",
       "        <td>None</td>\n",
       "        <td>1928-11-18</td>\n",
       "        <td>91</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('mm1', 1, 'Mouse', 'Mickey', None, datetime.date(1928, 11, 18), 91)]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select * from personv;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- The view is relatively simple, can we insert through the view?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://dbuser:***@localhost/W4111Examples\n",
      "Insert got exception e =  (pymysql.err.InternalError) (1471, 'The target table personv of the INSERT is not insertable-into')\n",
      "[SQL: insert into personv (uni, last_name, first_name,  DOB) values('dd2', \"Duck\", \"Donald\", '000-01-01')]\n",
      "(Background on this error at: http://sqlalche.me/e/2j85)\n"
     ]
    }
   ],
   "source": [
    "try:\n",
    "    %sql insert into personv (uni, last_name, first_name,  DOB) values('dd2', \"Duck\", \"Donald\", '000-01-01')\n",
    "    print(\"Insert worked!\")\n",
    "except Exception as e:\n",
    "    print(\"Insert got exception e = \", e)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Oh Well. We can insert into the base table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://dbuser:***@localhost/W4111Examples\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql insert into person (uni, last_name, first_name,  DOB) values('dd2', \"Duck\", \"Donald\", '000-01-01')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- And see through the view."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://dbuser:***@localhost/W4111Examples\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>uni</th>\n",
       "        <th>auto_id</th>\n",
       "        <th>last_name</th>\n",
       "        <th>first_name</th>\n",
       "        <th>middle_initial</th>\n",
       "        <th>DOB</th>\n",
       "        <th>age</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>mm1</td>\n",
       "        <td>1</td>\n",
       "        <td>Mouse</td>\n",
       "        <td>Mickey</td>\n",
       "        <td>None</td>\n",
       "        <td>1928-11-18</td>\n",
       "        <td>91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>dd2</td>\n",
       "        <td>5</td>\n",
       "        <td>Duck</td>\n",
       "        <td>Donald</td>\n",
       "        <td>None</td>\n",
       "        <td>0000-01-01</td>\n",
       "        <td>2020</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('mm1', 1, 'Mouse', 'Mickey', None, datetime.date(1928, 11, 18), 91),\n",
       " ('dd2', 5, 'Duck', 'Donald', None, '0000-01-01', 2020)]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select * from personv;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Maybe we can update through the view, because the data and age are incorrect."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://dbuser:***@localhost/W4111Examples\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "update personv set dob='1934-06-09' where uni='dd2'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- OK. That worked. What can we see?\n",
    "\n",
    "\n",
    "- The view."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://dbuser:***@localhost/W4111Examples\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>uni</th>\n",
       "        <th>auto_id</th>\n",
       "        <th>last_name</th>\n",
       "        <th>first_name</th>\n",
       "        <th>middle_initial</th>\n",
       "        <th>DOB</th>\n",
       "        <th>age</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>mm1</td>\n",
       "        <td>1</td>\n",
       "        <td>Mouse</td>\n",
       "        <td>Mickey</td>\n",
       "        <td>None</td>\n",
       "        <td>1928-11-18</td>\n",
       "        <td>91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>dd2</td>\n",
       "        <td>5</td>\n",
       "        <td>Duck</td>\n",
       "        <td>Donald</td>\n",
       "        <td>None</td>\n",
       "        <td>1934-06-09</td>\n",
       "        <td>85</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('mm1', 1, 'Mouse', 'Mickey', None, datetime.date(1928, 11, 18), 91),\n",
       " ('dd2', 5, 'Duck', 'Donald', None, datetime.date(1934, 6, 9), 85)]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select * from personv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- The base table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://dbuser:***@localhost/W4111Examples\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>uni</th>\n",
       "        <th>auto_id</th>\n",
       "        <th>last_name</th>\n",
       "        <th>first_name</th>\n",
       "        <th>middle_initial</th>\n",
       "        <th>DOB</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>mm1</td>\n",
       "        <td>1</td>\n",
       "        <td>Mouse</td>\n",
       "        <td>Mickey</td>\n",
       "        <td>None</td>\n",
       "        <td>1928-11-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>dd2</td>\n",
       "        <td>5</td>\n",
       "        <td>Duck</td>\n",
       "        <td>Donald</td>\n",
       "        <td>None</td>\n",
       "        <td>1934-06-09</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('mm1', 1, 'Mouse', 'Mickey', None, datetime.date(1928, 11, 18)),\n",
       " ('dd2', 5, 'Duck', 'Donald', None, datetime.date(1934, 6, 9))]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select * from person"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  },
  "toc": {
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "286px"
   },
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}