sql-intro-4.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Create the tables for this section."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: @None'"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%load_ext sql       \n",
    "# Connect to an empty SQLite database\n",
    "%sql sqlite://"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Product</th>\n",
       "        <th>Date</th>\n",
       "        <th>Price</th>\n",
       "        <th>Quantity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bagel</td>\n",
       "        <td>2017-10-21</td>\n",
       "        <td>1.0</td>\n",
       "        <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bagel</td>\n",
       "        <td>2017-10-25</td>\n",
       "        <td>1.5</td>\n",
       "        <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Banana</td>\n",
       "        <td>2017-10-03</td>\n",
       "        <td>0.5</td>\n",
       "        <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Banana</td>\n",
       "        <td>2017-10-10</td>\n",
       "        <td>1.0</td>\n",
       "        <td>10</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Bagel', '2017-10-21', 1.0, 20),\n",
       " ('Bagel', '2017-10-25', 1.5, 20),\n",
       " ('Banana', '2017-10-03', 0.5, 10),\n",
       " ('Banana', '2017-10-10', 1.0, 10)]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "DROP TABLE IF EXISTS Purchase;\n",
    "-- Create tables\n",
    "CREATE TABLE Purchase (\n",
    "    Product  VARCHAR(255), \n",
    "    Date     DATE, \n",
    "    Price    FLOAT,\n",
    "    Quantity INT\n",
    ");\n",
    "\n",
    "-- Insert tuples\n",
    "INSERT INTO Purchase VALUES ('Bagel', '2017-10-21', 1, 20);\n",
    "INSERT INTO Purchase VALUES ('Bagel', '2017-10-25', 1.5, 20);\n",
    "INSERT INTO Purchase VALUES ('Banana', '2017-10-03', 0.5, 10);\n",
    "INSERT INTO Purchase VALUES ('Banana', '2017-10-10', 1, 10);\n",
    "\n",
    "SELECT * FROM Purchase;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Aggregation Operations\n",
    "\n",
    "SQL support several __aggregation__ operations\n",
    "* SUM, COUNT, MIN, MAX, AVG\n",
    "* Except COUNT, all aggregations apply to a single attribute"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "source": [
    "### COUNT\n",
    "\n",
    "Syntax\n",
    "```mysql\n",
    "SELECT COUNT(column_name)\n",
    "FROM   table_name\n",
    "WHERE  condition;\n",
    "``` "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "> __Example:__ Find the number of purchases\n",
    "\n",
    "| Product |    Date    | Price | Quantity |\n",
    "|---------|------------|-------|----------|\n",
    "| Bagel   | 2017-10-21 | 1     | 20       |\n",
    "| Bagel   | 2017-10-25 | 1.5   | 20       |\n",
    "| Banana  | 2017-10-03 | 0.5   | 10       |\n",
    "| Banana  | 2017-10-10 | 1     | 10       |"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>COUNT(Product)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(4,)]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT COUNT(Product)\n",
    "FROM   Purchase;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "source": [
    "* Count applies to duplicates, unless otherwise stated\n",
    "  * Same as ```COUNT(*)```. Why?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "> __Example:__ Find the number of __different__ product purchases\n",
    "\n",
    "\n",
    "| Product |    Date    | Price | Quantity |\n",
    "|---------|:----------:|------:|:--------:|\n",
    "| Bagel   | 2017-10-21 | 1.0   | 20       |\n",
    "| Bagel   | 2017-10-25 | 1.5   | 20       |\n",
    "| Banana  | 2017-10-03 | 0.5   | 10       |\n",
    "| Banana  | 2017-10-10 | 1.0   | 10       |\n",
    "\n",
    "* Use DISTINCT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>COUNT(DISTINCT Product)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2,)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT COUNT(DISTINCT Product)\n",
    "FROM   Purchase;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### SUM\n",
    "\n",
    "Syntax\n",
    "```mysql\n",
    "SELECT SUM(column_name)\n",
    "FROM   table_name\n",
    "WHERE  condition;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "> __Example:__ How many units of all products have been purchased?\n",
    "\n",
    "| Product |    Date    | Price | Quantity |\n",
    "|---------|:----------:|------:|:--------:|\n",
    "| Bagel   | 2017-10-21 | 1.0   | 20       |\n",
    "| Bagel   | 2017-10-25 | 1.5   | 20       |\n",
    "| Banana  | 2017-10-03 | 0.5   | 10       |\n",
    "| Banana  | 2017-10-10 | 1.0   | 10       |"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>SUM(Quantity)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>60</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(60,)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT SUM(Quantity)\n",
    "FROM   Purchase;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "> __Example:__ How many Bagels have been purchased?\n",
    "\n",
    "| Product |    Date    | Price | Quantity |\n",
    "|---------|:----------:|------:|:--------:|\n",
    "| Bagel   | 2017-10-21 | 1.0   | 20       |\n",
    "| Bagel   | 2017-10-25 | 1.5   | 20       |\n",
    "| Banana  | 2017-10-03 | 0.5   | 10       |\n",
    "| Banana  | 2017-10-10 | 1.0   | 10       |"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>SUM(Quantity)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>40</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(40,)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT SUM(Quantity)\n",
    "FROM   Purchase\n",
    "WHERE  Product = 'Bagel';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## AVG\n",
    "\n",
    "Syntax\n",
    "```mysql\n",
    "SELECT AVG(column_name)\n",
    "FROM   table_name\n",
    "WHERE  condition;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "> __Example:__ What is the average sell price of Bagels?\n",
    "\n",
    "| Product |    Date    | Price | Quantity |\n",
    "|---------|:----------:|------:|:--------:|\n",
    "| Bagel   | 2017-10-21 | 1.0   | 20       |\n",
    "| Bagel   | 2017-10-25 | 1.5   | 20       |\n",
    "| Banana  | 2017-10-03 | 0.5   | 10       |\n",
    "| Banana  | 2017-10-10 | 1.0   | 10       |"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>AVG(Price)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1.25</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1.25,)]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT AVG(Price)\n",
    "FROM   Purchase\n",
    "WHERE  Product = 'Bagel';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Simple Aggregations\n",
    "\n",
    "> __Example:__ Total earnings from Bagels sold?\n",
    "\n",
    "| Product |    Date    | Price | Quantity |\n",
    "|---------|:----------:|------:|:--------:|\n",
    "| Bagel   | 2017-10-21 | 1.0   | 20       |\n",
    "| Bagel   | 2017-10-25 | 1.5   | 20       |\n",
    "| Banana  | 2017-10-03 | 0.5   | 10       |\n",
    "| Banana  | 2017-10-10 | 1.0   | 10       |"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>SUM(Price * Quantity)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>15.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(15.0,)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT SUM(Price * Quantity)\n",
    "FROM   Purchase\n",
    "WHERE  Product = 'Banana';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## GROUP BY\n",
    "\n",
    "Used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.\n",
    "\n",
    "Syntax\n",
    "```mysql\n",
    "SELECT   column_name(s)\n",
    "FROM     table_name\n",
    "WHERE    condition\n",
    "GROUP BY column_name(s)\n",
    "[ORDER BY column_name(s)];\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "> __Example:__ Find total sales after October 1st. per product\n",
    "\n",
    "| Product |    Date    | Price | Quantity |\n",
    "|---------|:----------:|------:|:--------:|\n",
    "| Bagel   | 2017-10-21 | 1.0   | 20       |\n",
    "| Bagel   | 2017-10-25 | 1.5   | 20       |\n",
    "| Banana  | 2017-10-03 | 0.5   | 10       |\n",
    "| Banana  | 2017-10-10 | 1.0   | 10       |"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Product</th>\n",
       "        <th>TotalSales</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bagel</td>\n",
       "        <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Banana</td>\n",
       "        <td>15.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Bagel', 50.0), ('Banana', 15.0)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT   Product, SUM(price * quantity) AS TotalSales\n",
    "FROM     Purchase\n",
    "WHERE    Date > '2017-10-01'\n",
    "GROUP BY Product;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Grouping and Aggregation: Semantics of the Query\n",
    "\n",
    "__1.__ Compute the FROM and WHERE clauses"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Product</th>\n",
       "        <th>Date</th>\n",
       "        <th>Price</th>\n",
       "        <th>Quantity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bagel</td>\n",
       "        <td>2017-10-21</td>\n",
       "        <td>1.0</td>\n",
       "        <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bagel</td>\n",
       "        <td>2017-10-25</td>\n",
       "        <td>1.5</td>\n",
       "        <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Banana</td>\n",
       "        <td>2017-10-03</td>\n",
       "        <td>0.5</td>\n",
       "        <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Banana</td>\n",
       "        <td>2017-10-10</td>\n",
       "        <td>1.0</td>\n",
       "        <td>10</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Bagel', '2017-10-21', 1.0, 20),\n",
       " ('Bagel', '2017-10-25', 1.5, 20),\n",
       " ('Banana', '2017-10-03', 0.5, 10),\n",
       " ('Banana', '2017-10-10', 1.0, 10)]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "FROM   Purchase\n",
    "WHERE  Date > '2017-10-01'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "__2.__ Group attributes according to GROUP BY\n",
    "\n",
    "|__Product__| __Date__ |__Price__|__Quantity__|\n",
    "|:-------:|:----------:|:-----:|:--------:|\n",
    "|  Bagel  | 2017-10-21 |   1   |    20    |\n",
    "|         | 2017-10-25 |  1.5  |    20    |\n",
    "|  Banana | 2017-10-03 |  0.5  |    10    |\n",
    "|         | 2017-10-10 |   1   |    10    |\n",
    "\n",
    "\n",
    "__Caution:__ SQL _only_ displays one row if no aggregation function is used"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Product</th>\n",
       "        <th>Date</th>\n",
       "        <th>Price</th>\n",
       "        <th>Quantity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bagel</td>\n",
       "        <td>2017-10-25</td>\n",
       "        <td>1.5</td>\n",
       "        <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Banana</td>\n",
       "        <td>2017-10-10</td>\n",
       "        <td>1.0</td>\n",
       "        <td>10</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Bagel', '2017-10-25', 1.5, 20), ('Banana', '2017-10-10', 1.0, 10)]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT   *\n",
    "FROM     Purchase\n",
    "WHERE    Date > '2017-10-01'\n",
    "GROUP BY Product;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Product</th>\n",
       "        <th>Count(Product)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bagel</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Banana</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Bagel', 2), ('Banana', 2)]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT   Product, Count(Product)\n",
    "FROM     Purchase\n",
    "WHERE    Date > '2017-10-01'\n",
    "GROUP BY Product;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "__3.__ Compute the SELECT clause: grouped attributes and aggregates\n",
    "\n",
    "| Product |    Date    | Price | Quantity |\n",
    "|---------|:----------:|------:|:--------:|\n",
    "| Bagel   | 2017-10-21 | 1.0   | 20       |\n",
    "| Bagel   | 2017-10-25 | 1.5   | 20       |\n",
    "| Banana  | 2017-10-03 | 0.5   | 10       |\n",
    "| Banana  | 2017-10-10 | 1.0   | 10       |"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Product</th>\n",
       "        <th>TotalSales</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bagel</td>\n",
       "        <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Banana</td>\n",
       "        <td>15.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Bagel', 50.0), ('Banana', 15.0)]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql  -- Find total sales after '2017-10-01' per product\n",
    "\n",
    "SELECT   Product, SUM(price * quantity) AS TotalSales\n",
    "FROM     Purchase\n",
    "WHERE    Date > '2017-10-01'\n",
    "GROUP BY Product;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### GROUP BY vs Nested Queries\n",
    "\n",
    "```mysql\n",
    "SELECT   Product, SUM(price * quantity) AS TotalSales\n",
    "FROM     Purchase\n",
    "WHERE    Date > '10/1'\n",
    "GROUP BY Product;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Product</th>\n",
       "        <th>TotalSales</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bagel</td>\n",
       "        <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Banana</td>\n",
       "        <td>15.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Bagel', 50.0), ('Banana', 15.0)]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT DISTINCT x.Product, (SELECT Sum(y.price*y.quantity)\n",
    "                                   FROM Purchase y\n",
    "                                   WHERE x.product = y.product\n",
    "                                         AND y.date > '2017-10-01') AS TotalSales\n",
    "FROM  Purchase x\n",
    "WHERE x.date > '2017-10-01';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## HAVING\n",
    "\n",
    "* HAVING clauses contain conditions on __aggregates__\n",
    "* WHERE clauses condition on __individual tuples__\n",
    "\n",
    "Syntax\n",
    "```mysql\n",
    "SELECT   column_name(s)\n",
    "FROM     table_name\n",
    "WHERE    condition\n",
    "GROUP BY column_name(s)\n",
    "HAVING   condition\n",
    "[ORDER BY column_name(s)];\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "> __Example:__ Same query as before, except that we consider only products with more than 30 units sold\n",
    "\n",
    "| Product |    Date    | Price | Quantity |\n",
    "|---------|:----------:|------:|:--------:|\n",
    "| Bagel   | 2017-10-21 | 1.0   | 20       |\n",
    "| Bagel   | 2017-10-25 | 1.5   | 20       |\n",
    "| Banana  | 2017-10-03 | 0.5   | 10       |\n",
    "| Banana  | 2017-10-10 | 1.0   | 10       |"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Product</th>\n",
       "        <th>TotalSales</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bagel</td>\n",
       "        <td>50.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Bagel', 50.0)]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT   Product, SUM(price * quantity) AS TotalSales\n",
    "FROM     Purchase\n",
    "WHERE    Date > '2017-10-01'\n",
    "GROUP BY Product\n",
    "HAVING SUM(Quantity) > 30;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Exercise II\n",
    "\n",
    "An organism that sells tickets for football matches uses a database with the following relational schema:\n",
    "\n",
    "```\n",
    "Match(Match_ID, Date, Hour, Stadium_ID, Team_ID)\n",
    "Team(Team_ID, Name, City)\n",
    "Stadium(Stadium_ID, Name, Address, Capacity, Team_ID)\n",
    "Ticket(Ticket_ID, Match_ID, Place_Number, Category, Price)\n",
    "Sell(Sell_ID, Sell_Date, Ticket_ID, Payment_Method)\n",
    "```\n",
    "\n",
    "Write the following query in SQL:\n",
    "> What are the name(s) of the stadium(s) with **the** largest capacity?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "___"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Advanced\\* Topics\n",
    "\n",
    "In this section\n",
    "* Relational Division is SQL\n",
    "* Nulls (revisited)\n",
    "* Outer Joins"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Relational Division in SQL\n",
    "\n",
    "* Not supported as a primitive operator, but useful for expressing queries like:\n",
    "\n",
    "> _\"Find suppliers who sell the x parts...\"_\n",
    "\n",
    "> _\"Find buyers who bought all products from a given category...\"_\n",
    "\n",
    "* Let $A$ have 2 fields, $x$ and $y$, $B$ have only field $y$\n",
    "```mysql\n",
    "A(x, y)\n",
    "B(y)\n",
    "```\n",
    "  * $A/B$ contains all $x$ tuples such that for every $y$ tuple in $B$, there is an $xy$ tuple in $A$\n",
    "  * Or: If the set of $y$ values associated with an $x$ value in $A$ contains all $y$ values in $B$, the $x$ value is in $A/B$."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "__Classic Option 1__\n",
    "```mysql\n",
    "SELECT T1.x\n",
    "FROM A AS T1\n",
    "WHERE NOT EXISTS( SELECT T2.y\n",
    "                  FROM B AS T2\n",
    "                  EXCEPT\n",
    "                  SELECT T3.y\n",
    "                  FROM A AS T3\n",
    "                  WHERE T3.y=T1.y);\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "__Classic Option 2__ (without EXCEPT)\n",
    "```mysql\n",
    "SELECT DISTINCT T1.x\n",
    "FROM A AS T1\n",
    "WHERE NOT EXISTS(SELECT T2.y\n",
    "                 FROM B AS T2\n",
    "                 WHERE NOT EXISTS (SELECT T3.x\n",
    "                                   FROM A AS T3\n",
    "                                   WHERE T3.x=T1.x\n",
    "                                   AND T3.y=T2.y\n",
    "                                  )\n",
    "                );\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "> __Example:__ Find Establishments which sell all products\n",
    "```\n",
    "Establishment(eid, ename)\n",
    "Sells(eid, pname)\n",
    "Products(pname)\n",
    "```\n",
    "\n",
    "Classic Option 2 (without EXCEPT)\n",
    "```mysql\n",
    "SELECT DISTINCT E.ename\n",
    "FROM Establishment AS E\n",
    "WHERE NOT EXISTS (SELECT p.pname\n",
    "                  FROM Products3 AS P \n",
    "                  WHERE NOT EXISTS (SELECT S.eid\n",
    "                                    FROM Sells AS S\n",
    "                                    WHERE S.pname=P.pname\n",
    "                                    AND S.eid=E.eid\n",
    "                                   )\n",
    "                );\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Classic Option 2 (without EXCEPT)\n",
    "```mysql\n",
    "SELECT DISTINCT E.ename\n",
    "FROM Establishment AS E\n",
    "WHERE NOT EXISTS (SELECT p.pname\n",
    "                  FROM Products3 AS P \n",
    "                  WHERE NOT EXISTS (SELECT S.eid\n",
    "                                    FROM Sells AS S\n",
    "                                    WHERE S.pname=P.pname\n",
    "                                    AND S.eid=E.eid\n",
    "                                   )\n",
    "                );\n",
    "```\n",
    "\n",
    "* Semantics:\n",
    "  * _Establishment_ E such that...\n",
    "    \n",
    "    ... there is no _Product_ P...\n",
    " \n",
    "    ...... without a _Sells_ tuple showing that E sells P "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "> __Example:__ Find Establishments which sell all products\n",
    "![division_example](figures/sql_division_example.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "DROP TABLE IF EXISTS Establishment;\n",
    "-- Create tables\n",
    "CREATE TABLE Establishment (\n",
    "    eid INT,\n",
    "    ename VARCHAR);\n",
    "\n",
    "DROP TABLE IF EXISTS Sells;\n",
    "-- Create tables\n",
    "CREATE TABLE Sells (\n",
    "    eid INT,\n",
    "    pname VARCHAR);\n",
    "\n",
    "DROP TABLE IF EXISTS Products1;\n",
    "-- Create tables\n",
    "CREATE TABLE Products1 (\n",
    "    pname VARCHAR);\n",
    "\n",
    "DROP TABLE IF EXISTS Products2;\n",
    "-- Create tables\n",
    "CREATE TABLE Products2 (\n",
    "    pname VARCHAR);\n",
    "\n",
    "DROP TABLE IF EXISTS Products3;\n",
    "-- Create tables\n",
    "CREATE TABLE Products3 (\n",
    "    pname VARCHAR);\n",
    "\n",
    "-- Insert tuples\n",
    "INSERT INTO Establishment VALUES (1, 'Carrefour');\n",
    "INSERT INTO Establishment VALUES (2, 'Franprix');\n",
    "INSERT INTO Establishment VALUES (3, 'Boulangerie');\n",
    "INSERT INTO Establishment VALUES (4, 'Biocoop');\n",
    "INSERT INTO Sells VALUES (1, 'Wine');\n",
    "INSERT INTO Sells VALUES (1, 'Bread');\n",
    "INSERT INTO Sells VALUES (1, 'Cheese');\n",
    "INSERT INTO Sells VALUES (1, 'Meat');\n",
    "INSERT INTO Sells VALUES (2, 'Wine');\n",
    "INSERT INTO Sells VALUES (2, 'Bread');\n",
    "INSERT INTO Sells VALUES (3, 'Bread');\n",
    "INSERT INTO Sells VALUES (4, 'Bread');\n",
    "INSERT INTO Sells VALUES (4, 'Meat');\n",
    "INSERT INTO Products1 VALUES ('Bread');\n",
    "INSERT INTO Products2 VALUES ('Bread');\n",
    "INSERT INTO Products2 VALUES ('Meat');\n",
    "INSERT INTO Products3 VALUES ('Wine');\n",
    "INSERT INTO Products3 VALUES ('Bread');\n",
    "INSERT INTO Products3 VALUES ('Meat');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>eid</th>\n",
       "        <th>ename</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Carrefour</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Franprix</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Boulangerie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>Biocoop</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'Carrefour'), (2, 'Franprix'), (3, 'Boulangerie'), (4, 'Biocoop')]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM Establishment;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>eid</th>\n",
       "        <th>pname</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Wine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Bread</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Cheese</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Meat</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Wine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Bread</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Bread</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>Bread</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>Meat</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'Wine'),\n",
       " (1, 'Bread'),\n",
       " (1, 'Cheese'),\n",
       " (1, 'Meat'),\n",
       " (2, 'Wine'),\n",
       " (2, 'Bread'),\n",
       " (3, 'Bread'),\n",
       " (4, 'Bread'),\n",
       " (4, 'Meat')]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM Sells;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>pname</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Wine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bread</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Meat</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Wine',), ('Bread',), ('Meat',)]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM Products3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "> __Example:__ Find Establishments which sell all products\n",
    "![division_example](figures/sql_division_example.png)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>ename</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Carrefour</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Carrefour',)]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql  -- Change bellow to query Products[1,2,3]\n",
    "SELECT DISTINCT E.ename\n",
    "FROM Establishment AS E\n",
    "WHERE NOT EXISTS (SELECT P.pname\n",
    "                  FROM Products3 AS P \n",
    "                  WHERE NOT EXISTS (SELECT S.eid\n",
    "                                    FROM Sells AS S\n",
    "                                    WHERE S.pname=P.pname\n",
    "                                    AND S.eid=E.eid\n",
    "                                   )\n",
    "                );"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "> __Exercise:__ Write the same query with EXCEPT (Classic Option 1)\n",
    "```\n",
    "Establishment(eid, ename)\n",
    "Sells(eid, pname)\n",
    "Products(pname)\n",
    "```\n",
    "___\n",
    "```mysql\n",
    "SELECT T1.x\n",
    "FROM A AS T1\n",
    "WHERE NOT EXISTS( SELECT T2.y\n",
    "                  FROM B AS T2\n",
    "                  EXCEPT\n",
    "                  SELECT T3.y\n",
    "                  FROM A AS T3\n",
    "                  WHERE T3.y=T1.y);\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "> __Example:__ Find Establishments which sell all products\n",
    "![division_example](figures/sql_division_example.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "-- Write the same query with EXCEPT (Classic Option 1)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Exercise III\n",
    "\n",
    "An organism that sells tickets for football matches uses a database with the following relational schema:\n",
    "\n",
    "```\n",
    "Match(Match_ID, Date, Hour, Stadium_ID, Team_ID)\n",
    "Team(Team_ID, Name, City)\n",
    "Stadium(Stadium_ID, Name, Address, Capacity, Team_ID)\n",
    "Ticket(Ticket_ID, Match_ID, Place_Number, Category, Price)\n",
    "Sell(Sell_ID, Sell_Date, Ticket_ID, Payment_Method)\n",
    "```\n",
    "\n",
    "Write the following query in SQL:\n",
    "> What are the teams that will play at least once in all the stadiums?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Exercise IV\n",
    "\n",
    "An organism that sells tickets for football matches uses a database with the following relational schema:\n",
    "\n",
    "```\n",
    "Match(Match_ID, Date, Hour, Stadium_ID, Team_ID)\n",
    "Team(Team_ID, Name, City)\n",
    "Stadium(Stadium_ID, Name, Address, Capacity, Team_ID)\n",
    "Ticket(Ticket_ID, Match_ID, Place_Number, Category, Price)\n",
    "Sell(Sell_ID, Sell_Date, Ticket_ID, Payment_Method)\n",
    "```\n",
    "\n",
    "Write the following query in SQL:\n",
    "> What are the dates and identifiers of matches for which there are no more tickets to sell?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Yet another option\n",
    "\n",
    "[\"A Simpler (and Better) SQL Approach to Relational Division\"](https://users.dcc.uchile.cl/~cgutierr/cursos/BD/divisionSQL.pdf)\n",
    "\n",
    "Journal of Information Systems Education, Vol. 13(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Null Values\n",
    "\n",
    "* For _numerical operations_, NULL -> NULL:\n",
    "  * If x is NULL then ```4*(3-x)/7``` is still NULL\n",
    "\n",
    "* For _boolean operations_, in SQL there are three values:\n",
    "\n",
    "```\n",
    "FALSE  = 0\n",
    "UNKNOWN = 0.5\n",
    "TRUE    = 1\n",
    "```\n",
    "\n",
    "* If x is NULL then ```x = 'Joe'``` is UNKNOWN"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "```\n",
    "C1 AND C2   = min(C1, C2)\n",
    "C1 OR C2    = max(C1, C2)\n",
    "NOT C1      = 1 – C1\n",
    "```\n",
    "\n",
    ">  __Example:__\n",
    "```mysql\n",
    "SELECT *\n",
    "FROM   Person\n",
    "WHERE  (age < 25)\n",
    "  AND  (height > 6 AND weight > 190);\n",
    "```\n",
    "Won't return: \n",
    "- age=20\n",
    "- height=NULL  <--\n",
    "- weight=200\n",
    "\n",
    "__Rule in SQL:__ include only tuples that yield TRUE (1.0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "> __Example:__ Unexpected behavior\n",
    "```mysql\n",
    "SELECT *\n",
    "FROM   Person\n",
    "WHERE  age < 25 OR age >= 25;\n",
    "```\n",
    "Some tuples from _Person_ are not included"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "Test for NULL explicitly:\n",
    "* x IS NULL\n",
    "* x IS NOT NULL\n",
    "\n",
    "\n",
    ">```mysql\n",
    "SELECT *\n",
    "FROM   Person\n",
    "WHERE  age < 25 OR age >= 25 OR age IS NULL;\n",
    "```\n",
    "Now it includes all tuples in _Person_"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Inner Joins + NULLS = Lost data?\n",
    "\n",
    "* By default, joins in SQL are __inner joins__\n",
    "\n",
    "> __Example:__ Find Products (Name) and the Stores where they are sold.\n",
    "```\n",
    "Product(name, category)\n",
    "Purchase(prodName, store)\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "> __Example:__ Find Products (Name) and the Stores where they are sold.\n",
    "```\n",
    "Product(name, category)\n",
    "Purchase(prodName, store)\n",
    "```\n",
    "Syntax 1\n",
    "```mysql\n",
    "SELECT Product.name, Purchase.store\n",
    "FROM   Product\n",
    "JOIN   Purchase ON Product.name = Purchase.prodName;\n",
    "```\n",
    "Syntax 2\n",
    "```mysql\n",
    "SELECT Product.name, Purchase.store\n",
    "FROM   Product, Purchase\n",
    "WHERE  Product.name = Purchase.prodName;\n",
    "```\n",
    "* Both equivalent, both _inner joins_\n",
    "\n",
    "* __However:__ Products that never sold (with no Purchase tuple) will be lost!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Outer Joins\n",
    "* An __outer join__ returns tuples from the joined relations that don’t have a corresponding tuple in the other relations\n",
    "  * i.e. If we join relations A and B on A.x = B.x, and there is an entry in A with x=5, but none in B with x=5\n",
    "  LEFT [OUTER] JOIN will return a tuple __(5, NULL)__\n",
    " \n",
    "![example](figures/outer-join.png)\n",
    "\n",
    "Syntax\n",
    "```mysql\n",
    "SELECT column_name(s)\n",
    "FROM   table1\n",
    "LEFT OUTER JOIN table2 ON table1.column_name = table2.column_name;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "-- Create tables\n",
    "DROP TABLE IF EXISTS Product;\n",
    "CREATE TABLE Product (\n",
    "    name VARCHAR(255) PRIMARY KEY,\n",
    "    category VARCHAR(255)\n",
    ");\n",
    "\n",
    "DROP TABLE IF EXISTS Purchase;\n",
    "CREATE TABLE Purchase(\n",
    "    prodName varchar(255),\n",
    "    store varchar(255)\n",
    ");\n",
    "\n",
    "-- Insert tuples\n",
    "INSERT INTO Product VALUES ('Gizmo', 'Gadget');\n",
    "INSERT INTO Product VALUES ('Camera', 'Photo');\n",
    "INSERT INTO Product VALUES ('OneClick', 'Photo');\n",
    "\n",
    "INSERT INTO Purchase VALUES ('Gizmo', 'Wiz');\n",
    "INSERT INTO Purchase VALUES ('Camera', 'Ritz');\n",
    "INSERT INTO Purchase VALUES ('Camera', 'Wiz');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>category</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Gizmo</td>\n",
       "        <td>Gadget</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Camera</td>\n",
       "        <td>Photo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>OneClick</td>\n",
       "        <td>Photo</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Gizmo', 'Gadget'), ('Camera', 'Photo'), ('OneClick', 'Photo')]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "FROM   Product;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>prodName</th>\n",
       "        <th>store</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Gizmo</td>\n",
       "        <td>Wiz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Camera</td>\n",
       "        <td>Ritz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Camera</td>\n",
       "        <td>Wiz</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Gizmo', 'Wiz'), ('Camera', 'Ritz'), ('Camera', 'Wiz')]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "FROM   Purchase;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>store</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Camera</td>\n",
       "        <td>Ritz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Camera</td>\n",
       "        <td>Wiz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Gizmo</td>\n",
       "        <td>Wiz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>OneClick</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Camera', 'Ritz'), ('Camera', 'Wiz'), ('Gizmo', 'Wiz'), ('OneClick', None)]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT Product.name, Purchase.store\n",
    "FROM   Product\n",
    "LEFT OUTER JOIN Purchase\n",
    "ON Product.name = Purchase.prodName;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Outer Joins\n",
    "* __Left outer join__\n",
    "  * Include the left tuple even if there is no match\n",
    "* __Right outer join__\n",
    "  * Include the right tuple even if there is no match\n",
    "* __Full outer join__\n",
    "  * Include both left and right tuples even if there is no match\n",
    "  ![example](figures/joins.png)"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Slideshow",
  "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}