{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "e24cf73a-3ec5-46f6-9e54-09d5a02623e7",
   "metadata": {},
   "source": [
    "## Lecture 12 - More on SQL\n",
    "\n",
    "**Announcements**\n",
    "\n",
    "  - Class this week on Thursday and Friday\n",
    "  - A new lecture exercise today, due on monday at midnight. \n",
    "  - No lecture exercises tomorrow.\n",
    "  - Expect a new homework this friday with more complex SQL queries.\n",
    "\n",
    "\n",
    "## SQL \n",
    "\n",
    "- Industry standard\n",
    "- Bag oriented - not set\n",
    "- Learned about \n",
    "  - string comparisons using LIKE\n",
    "  - joins\n",
    "  - DISTINCT for removal of duplicates\n",
    "  - null values\n",
    "  - date/time/timestamp data types.\n",
    "- Basic execution of operations\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "63b023be-3afb-4799-a39b-898287220590",
   "metadata": {},
   "outputs": [],
   "source": [
    "load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "88282b1e-5947-4884-b13c-46a7427fd31d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Connecting to &#x27;baking&#x27;</span>"
      ],
      "text/plain": [
       "Connecting to 'baking'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%sql --section baking"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "3a646945-4230-4fde-9f7e-6bd44c61738e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;baking&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'baking'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">6 rows affected.</span>"
      ],
      "text/plain": [
       "6 rows affected."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>bakername</th>\n",
       "            <th>age</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Briony Williams</td>\n",
       "            <td>33</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Dan Beasley-Harling</td>\n",
       "            <td>36</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Kim-Joy Hewlett</td>\n",
       "            <td>27</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Manon Lagrève</td>\n",
       "            <td>26</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Rahul Mandal</td>\n",
       "            <td>30</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ruby Bhogal</td>\n",
       "            <td>29</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+---------------------+-----+\n",
       "|      bakername      | age |\n",
       "+---------------------+-----+\n",
       "|   Briony Williams   |  33 |\n",
       "| Dan Beasley-Harling |  36 |\n",
       "|   Kim-Joy Hewlett   |  27 |\n",
       "|    Manon Lagrève    |  26 |\n",
       "|     Rahul Mandal    |  30 |\n",
       "|     Ruby Bhogal     |  29 |\n",
       "+---------------------+-----+"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "-- Find the name and age of all bakers who won star baker.\n",
    "\n",
    "SELECT DISTINCT\n",
    "   b.fullname as bakername\n",
    "   , b.age \n",
    "FROM\n",
    "   bakers b\n",
    "   , results r\n",
    "WHERE\n",
    "   b.baker = r.baker\n",
    "   and r.result = 'star baker'\n",
    "ORDER BY\n",
    "   bakername ASC ;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "24eb5325-1b5d-473e-87a7-fb1c5be23a66",
   "metadata": {},
   "source": [
    "\n",
    "### SET OPERATIONS\n",
    "\n",
    "- UNION (set union) -- tuples from R1 or R2\n",
    "\n",
    "- INTERSECT (set intersection) -- tuples from R1 and R2\n",
    "\n",
    "- EXCEPT (set subtraction) -- tuples from R1 and not from R2\n",
    "\n",
    "\n",
    "Set/bag operation pointers:\n",
    "- Set compatibility is needed, so the SELECT operations must return the same schema. \n",
    "- Note the ordering of operations. The resulting schema will have the attribute names of the first relation.\n",
    "- Note when certain set/bag operations are needed, especially union, they will impact the performance signicantly.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "bb44ddec-4afb-4d4f-b3bf-d87f35a0dc13",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;baking&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'baking'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">7 rows affected.</span>"
      ],
      "text/plain": [
       "7 rows affected."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>toprbaker</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Briony</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Dan</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Jon</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Kim-Joy</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Manon</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Rahul</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ruby</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+-----------+\n",
       "| toprbaker |\n",
       "+-----------+\n",
       "|   Briony  |\n",
       "|    Dan    |\n",
       "|    Jon    |\n",
       "|  Kim-Joy  |\n",
       "|   Manon   |\n",
       "|   Rahul   |\n",
       "|    Ruby   |\n",
       "+-----------+"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "\n",
    "-- Find bakers who were star baker or a favorite\n",
    "\n",
    "SELECT \n",
    "   baker  as toprbaker\n",
    "FROM\n",
    "   results\n",
    "WHERE\n",
    "   result = 'star baker'\n",
    "UNION\n",
    "SELECT \n",
    "   baker as lowerrbaker\n",
    "FROM\n",
    "   favorites\n",
    "order by toprbaker\n",
    ";\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "fe5677ee-407c-4e8e-b30a-3909906ebeea",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;baking&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'baking'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">3 rows affected.</span>"
      ],
      "text/plain": [
       "3 rows affected."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>baker</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Imelda</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Luke</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Antony</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+--------+\n",
       "| baker  |\n",
       "+--------+\n",
       "| Imelda |\n",
       "|  Luke  |\n",
       "| Antony |\n",
       "+--------+"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "-- Find bakers who were never in the top 5 of a technical episode.\n",
    "\n",
    "-- (all bakers) - (all bakers who were in top 5)\n",
    "\n",
    "SELECT\n",
    "   baker \n",
    "FROM\n",
    "   bakers\n",
    "EXCEPT\n",
    "SELECT\n",
    "   baker\n",
    "FROM\n",
    "   technicals\n",
    "WHERE\n",
    "   rank <= 5;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6936e44e-6747-4b8b-8e5f-fc542f8a9de5",
   "metadata": {},
   "source": [
    "### BAG OPERATIONS (which retain copies of tuples!)\n",
    "\n",
    "Suppose t occurs m times in R and n times in S\n",
    "\n",
    "R UNION ALL S = {Return t:  m+n times}\n",
    "\n",
    "R INTERSECT ALL S = {Return t:  min(m,n) times}\n",
    "\n",
    "R EXCEPT ALL S = {Return t:  max(0, (m-n)) times}\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "c9a9ea99-b6e4-4fdd-a93f-abf7440b4868",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;baking&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'baking'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">6 rows affected.</span>"
      ],
      "text/plain": [
       "6 rows affected."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>baker</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Rahul</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Rahul</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Dan</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Briony</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Jon</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Jon</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+--------+\n",
       "| baker  |\n",
       "+--------+\n",
       "| Rahul  |\n",
       "| Rahul  |\n",
       "|  Dan   |\n",
       "| Briony |\n",
       "|  Jon   |\n",
       "|  Jon   |\n",
       "+--------+"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "    \n",
    "-- Find bakers who were favorite baker more times than\n",
    "-- star baker;\n",
    "\n",
    "\n",
    "SELECT \n",
    "   baker\n",
    "FROM\n",
    "   favorites\n",
    "EXCEPT ALL\n",
    "SELECT \n",
    "   baker \n",
    "FROM\n",
    "   results\n",
    "WHERE\n",
    "   result = 'star baker';\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "2afdc54d-a1c1-440d-9b16-41ab4aa956f5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;baking&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'baking'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">21 rows affected.</span>"
      ],
      "text/plain": [
       "21 rows affected."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>baker</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Briony</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Jon</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Dan</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Dan</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Rahul</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Jon</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Rahul</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Rahul</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ruby</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Rahul</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">Truncated to <a href=\"https://jupysql.ploomber.io/en/latest/api/configuration.html#displaylimit\">displaylimit</a> of 10.</span>"
      ],
      "text/plain": [
       "+--------+\n",
       "| baker  |\n",
       "+--------+\n",
       "| Briony |\n",
       "|  Jon   |\n",
       "|  Dan   |\n",
       "|  Dan   |\n",
       "| Rahul  |\n",
       "|  Jon   |\n",
       "| Rahul  |\n",
       "| Rahul  |\n",
       "|  Ruby  |\n",
       "| Rahul  |\n",
       "+--------+\n",
       "Truncated to displaylimit of 10."
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "\n",
    "-- Find all episodes they were a star baker or a favorite.\n",
    "\n",
    "SELECT \n",
    "   baker\n",
    "FROM\n",
    "   favorites\n",
    "UNION ALL\n",
    "SELECT \n",
    "   baker\n",
    "FROM\n",
    "   results\n",
    "WHERE\n",
    "   result = 'star baker'\n",
    ";\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "859dfaaf-3e07-4920-b8a1-7297e8334586",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;baking&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'baking'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">1 rows affected.</span>"
      ],
      "text/plain": [
       "1 rows affected."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>fullname</th>\n",
       "            <th>age</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Jon Jenkins</td>\n",
       "            <td>47</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+-------------+-----+\n",
       "|   fullname  | age |\n",
       "+-------------+-----+\n",
       "| Jon Jenkins |  47 |\n",
       "+-------------+-----+"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "-- Return the full name and age of bakers....\n",
    "\n",
    "SELECT \n",
    "   b.fullname\n",
    "   , b.age\n",
    "FROM\n",
    "   favorites f\n",
    "   , bakers b\n",
    "WHERE\n",
    "   f.baker = b.baker\n",
    "EXCEPT\n",
    "SELECT \n",
    "   b.fullname\n",
    "   , b.age\n",
    "FROM\n",
    "   results r\n",
    "   , bakers b\n",
    "WHERE\n",
    "   b.baker = r.baker\n",
    "   and result = 'star baker'\n",
    "ORDER BY\n",
    "   fullname, age\n",
    ";\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "ee9443e2-0584-4956-bbcb-24f67b2914c8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;baking&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'baking'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">7 rows affected.</span>"
      ],
      "text/plain": [
       "7 rows affected."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>toprbaker</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Briony</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Dan</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Jon</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Kim-Joy</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Manon</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Rahul</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ruby</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+-----------+\n",
       "| toprbaker |\n",
       "+-----------+\n",
       "|   Briony  |\n",
       "|    Dan    |\n",
       "|    Jon    |\n",
       "|  Kim-Joy  |\n",
       "|   Manon   |\n",
       "|   Rahul   |\n",
       "|    Ruby   |\n",
       "+-----------+"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "    \n",
    "-- Find bakers who were star baker or a favorite\n",
    "\n",
    "SELECT \n",
    "   baker  as toprbaker\n",
    "FROM\n",
    "   results\n",
    "WHERE\n",
    "   result = 'star baker'\n",
    "UNION\n",
    "SELECT \n",
    "   baker as lowerrbaker\n",
    "FROM\n",
    "   favorites\n",
    "order by toprbaker\n",
    ";\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d8a65cc7-70d9-44b2-8888-94572713619e",
   "metadata": {},
   "source": [
    "\n",
    "-- Don't do this, very inefficient!!!!\n",
    "```\n",
    "SELECT DISTINCT  \n",
    "   b.baker  \n",
    "FROM  \n",
    "   results r, favorites f, bakers b  \n",
    "WHERE  \n",
    "   (b.baker = r.baker and r.result = 'star baker')  \n",
    "   or (b.baker = f.baker)  \n",
    "order by baker;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "27c84496-3d87-424e-9d2e-159d1b65dd6a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;baking&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'baking'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">8 rows affected.</span>"
      ],
      "text/plain": [
       "8 rows affected."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>baker</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Karen</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Antony</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Manon</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ruby</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Luke</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Terry</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Imelda</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Kim-Joy</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+---------+\n",
       "|  baker  |\n",
       "+---------+\n",
       "|  Karen  |\n",
       "|  Antony |\n",
       "|  Manon  |\n",
       "|   Ruby  |\n",
       "|   Luke  |\n",
       "|  Terry  |\n",
       "|  Imelda |\n",
       "| Kim-Joy |\n",
       "+---------+"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "-- 1. Find all bakers who were eliminated before 5th episode or have been\n",
    "-- in rank 6 or higher in at least 2 episodes before the 5th episode.\n",
    "\n",
    "-- (eliminated before 5th episode) union (have been\n",
    "-- in rank 6 or higher in at least 2 episodes before the 5th episode.)\n",
    "\n",
    "select\n",
    "   baker\n",
    "from\n",
    "   results \n",
    "where\n",
    "   result = 'eliminated'\n",
    "   and episodeid < 5\n",
    "union\n",
    "select\n",
    "   t1.baker \n",
    "from\n",
    "   technicals t1,\n",
    "   technicals t2\n",
    "where\n",
    "   t1.baker = t2.baker\n",
    "   and t1.rank >= 6\n",
    "   and t2.rank >= 6\n",
    "   and t1.episodeid < 5\n",
    "   and t2.episodeid < 5\n",
    "   and t1.episodeid <> t2.episodeid ;\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "514ccd78-09c0-40c4-afca-3ad18bd9b59d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;baking&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'baking'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">1 rows affected.</span>"
      ],
      "text/plain": [
       "1 rows affected."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>baker</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Kim-Joy</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+---------+\n",
       "|  baker  |\n",
       "+---------+\n",
       "| Kim-Joy |\n",
       "+---------+"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "-- Find bakers who have only started being a favorite after the 6th\n",
    "-- episode\n",
    "\n",
    "-- (all favorites) - (all favorites before the 7th episode)\n",
    "\n",
    "SELECT baker FROM favorites\n",
    "EXCEPT\n",
    "SELECT baker FROM favorites WHERE episodeid < 7;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "17d4ad84-f757-4151-8a74-2bca92bfb7da",
   "metadata": {},
   "source": [
    "![Query Execution Ordering](lecture12_figures/SQL_Execution_Order_Simple.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "21964705-63f5-4678-a074-d652ee74a611",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;baking&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'baking'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">1 rows affected.</span>"
      ],
      "text/plain": [
       "1 rows affected."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>numepisodes</th>\n",
       "            <th>numbakers</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>9</td>\n",
       "            <td>6</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+-------------+-----------+\n",
       "| numepisodes | numbakers |\n",
       "+-------------+-----------+\n",
       "|      9      |     6     |\n",
       "+-------------+-----------+"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "    \n",
    "-- Counting\n",
    "SELECT \n",
    "   count(baker) as numepisodes\n",
    "   , count(distinct baker) as numbakers\n",
    "FROM\n",
    "   results\n",
    "WHERE\n",
    "   result = 'star baker';\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "d35a3ae1-088e-4f77-abd6-4e891b6a3a06",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;baking&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'baking'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">1 rows affected.</span>"
      ],
      "text/plain": [
       "1 rows affected."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>bestrank</th>\n",
       "            <th>worstrank</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "            <td>6</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+----------+-----------+\n",
       "| bestrank | worstrank |\n",
       "+----------+-----------+\n",
       "|    1     |     6     |\n",
       "+----------+-----------+"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "    \n",
    "-- What is the highest rank 'Dan' achieved in technicals?\n",
    "\n",
    "SELECT\n",
    "   min(rank) as bestrank\n",
    "   , max(rank) as worstrank\n",
    "FROM\n",
    "   technicals\n",
    "WHERE\n",
    "   baker = 'Dan'\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "643732c1-2539-4909-91ed-c4bcf46cf185",
   "metadata": {},
   "source": [
    "**Aggregates:** count, min, max, sum, avg, stdev\n",
    "\n",
    "\n",
    "- count(*) - number of tuples (includes null values too)\n",
    "- count(a) - number of tuples in which A has a value\n",
    "- count(distinct a) - number of different values for A\n",
    "\n",
    "```\n",
    "create table numbers (id int) ;\n",
    "insert into numbers values(1);\n",
    "insert into numbers values(1);\n",
    "insert into numbers values(3);\n",
    "insert into numbers values(null);\n",
    "\n",
    "select min(id), max(id), avg(id) from numbers ;\n",
    "\n",
    "-- average of the not-null values\n",
    "\n",
    "select count(*), count(id), count(distinct id) from numbers ;\n",
    "\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "76cfdb9f-51de-4c8c-b4f8-6af4f0a62cd4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;baking&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'baking'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">7 rows affected.</span>"
      ],
      "text/plain": [
       "7 rows affected."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>baker</th>\n",
       "            <th>numtop1</th>\n",
       "            <th>firstep</th>\n",
       "            <th>lastep</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Dan</td>\n",
       "            <td>1</td>\n",
       "            <td>4</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Briony</td>\n",
       "            <td>2</td>\n",
       "            <td>6</td>\n",
       "            <td>8</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Terry</td>\n",
       "            <td>1</td>\n",
       "            <td>3</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Jon</td>\n",
       "            <td>1</td>\n",
       "            <td>2</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Rahul</td>\n",
       "            <td>1</td>\n",
       "            <td>7</td>\n",
       "            <td>7</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Kim-Joy</td>\n",
       "            <td>1</td>\n",
       "            <td>10</td>\n",
       "            <td>10</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ruby</td>\n",
       "            <td>3</td>\n",
       "            <td>1</td>\n",
       "            <td>9</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+---------+---------+---------+--------+\n",
       "|  baker  | numtop1 | firstep | lastep |\n",
       "+---------+---------+---------+--------+\n",
       "|   Dan   |    1    |    4    |   4    |\n",
       "|  Briony |    2    |    6    |   8    |\n",
       "|  Terry  |    1    |    3    |   3    |\n",
       "|   Jon   |    1    |    2    |   2    |\n",
       "|  Rahul  |    1    |    7    |   7    |\n",
       "| Kim-Joy |    1    |    10   |   10   |\n",
       "|   Ruby  |    3    |    1    |   9    |\n",
       "+---------+---------+---------+--------+"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "    \n",
    "-- Find for each baker, the number of times they were in rank 1\n",
    "-- of technicals\n",
    "\n",
    "select\n",
    "   t.baker\n",
    "   , count(*) as numtop1\n",
    "   , min(t.episodeid) as firstep\n",
    "   , max(t.episodeid) as lastep\n",
    "from\n",
    "   technicals t\n",
    "where\n",
    "   t.rank = 1\n",
    "group by\n",
    "   t.baker ;\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "39f37357-09e7-43e5-bbf7-8802909f730e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;baking&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'baking'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">10 rows affected.</span>"
      ],
      "text/plain": [
       "10 rows affected."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>baker</th>\n",
       "            <th>numtimes</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Briony</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Rahul</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ruby</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Karen</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Luke</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Terry</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Imelda</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Antony</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Kim-Joy</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Manon</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">Truncated to <a href=\"https://jupysql.ploomber.io/en/latest/api/configuration.html#displaylimit\">displaylimit</a> of 10.</span>"
      ],
      "text/plain": [
       "+---------+----------+\n",
       "|  baker  | numtimes |\n",
       "+---------+----------+\n",
       "|  Briony |    1     |\n",
       "|  Rahul  |    1     |\n",
       "|   Ruby  |    2     |\n",
       "|  Karen  |    2     |\n",
       "|   Luke  |    2     |\n",
       "|  Terry  |    2     |\n",
       "|  Imelda |    1     |\n",
       "|  Antony |    3     |\n",
       "| Kim-Joy |    2     |\n",
       "|  Manon  |    1     |\n",
       "+---------+----------+\n",
       "Truncated to displaylimit of 10."
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "--- For bakers, find the number of times they have been\n",
    "-- in rank 6 or higher  before the 5th episode\n",
    "\n",
    "select\n",
    "   baker\n",
    "   , count(*) as numtimes\n",
    "from\n",
    "   technicals\n",
    "where\n",
    "   rank > 6\n",
    "   and episodeid <5\n",
    "group by\n",
    "   baker  ;\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "6f6016c5-3f99-4def-9c39-7aaa71d6ad01",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;baking&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'baking'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">6 rows affected.</span>"
      ],
      "text/plain": [
       "6 rows affected."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>baker</th>\n",
       "            <th>numtimes</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Ruby</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Karen</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Luke</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Terry</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Antony</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Kim-Joy</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+---------+----------+\n",
       "|  baker  | numtimes |\n",
       "+---------+----------+\n",
       "|   Ruby  |    2     |\n",
       "|  Karen  |    2     |\n",
       "|   Luke  |    2     |\n",
       "|  Terry  |    2     |\n",
       "|  Antony |    3     |\n",
       "| Kim-Joy |    2     |\n",
       "+---------+----------+"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "    \n",
    "--- Find bakers who have been\n",
    "-- in rank 6 or higher in at least 2 episodes before the 5th episode\n",
    "\n",
    "\n",
    "select\n",
    "   baker\n",
    "   , count(*) as numtimes\n",
    "from\n",
    "   technicals\n",
    "where\n",
    "   rank > 6\n",
    "   and episodeid <5\n",
    "group by\n",
    "   baker\n",
    "having\n",
    "   count(*) >= 2\n",
    ";"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "132b1f27-885d-446a-817f-d9834b499f49",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;baking&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'baking'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">6 rows affected.</span>"
      ],
      "text/plain": [
       "6 rows affected."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>fullname</th>\n",
       "            <th>age</th>\n",
       "            <th>numtimes</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Ruby Bhogal</td>\n",
       "            <td>29</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Karen Wright</td>\n",
       "            <td>60</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Luke Thompson</td>\n",
       "            <td>30</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Terry Hartill</td>\n",
       "            <td>56</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Antony Amourdoux</td>\n",
       "            <td>30</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Kim-Joy Hewlett</td>\n",
       "            <td>27</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+------------------+-----+----------+\n",
       "|     fullname     | age | numtimes |\n",
       "+------------------+-----+----------+\n",
       "|   Ruby Bhogal    |  29 |    2     |\n",
       "|   Karen Wright   |  60 |    2     |\n",
       "|  Luke Thompson   |  30 |    2     |\n",
       "|  Terry Hartill   |  56 |    2     |\n",
       "| Antony Amourdoux |  30 |    3     |\n",
       "| Kim-Joy Hewlett  |  27 |    2     |\n",
       "+------------------+-----+----------+"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "    \n",
    "--- Return the fullname and age of bakers who have been\n",
    "-- in rank 6 or higher in at least 2 episodes before the 5th episode\n",
    "\n",
    "\n",
    "select\n",
    "   b.fullname\n",
    "   , b.age\n",
    "   , count(*) as numtimes\n",
    "from\n",
    "   technicals t\n",
    "   , bakers b\n",
    "where\n",
    "   t.baker = b.baker\n",
    "   and t.rank > 6\n",
    "   and t.episodeid <5\n",
    "group by\n",
    "   b.baker\n",
    "having\n",
    "   count(*) >= 2\n",
    ";"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a29bfc5a-17ac-4456-b7f7-d356c94321cf",
   "metadata": {},
   "source": [
    "Note: you can also group by b.baker, b.fullname, b.age\n",
    "The result would be the same\n",
    "However, if you grouped by t.baker, you cannot\n",
    "return b.fullname (because it does not know that \n",
    "t.baker implies b.fullname and b.age."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "057d7935-604c-460d-9c72-909bc24b5063",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;baking&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'baking'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">2 rows affected.</span>"
      ],
      "text/plain": [
       "2 rows affected."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>fullname</th>\n",
       "            <th>age</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Kim-Joy Hewlett</td>\n",
       "            <td>27</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Rahul Mandal</td>\n",
       "            <td>30</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+-----------------+-----+\n",
       "|     fullname    | age |\n",
       "+-----------------+-----+\n",
       "| Kim-Joy Hewlett |  27 |\n",
       "|   Rahul Mandal  |  30 |\n",
       "+-----------------+-----+"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "    \n",
    "-- Find bakers who were in top 3 rank in technicals at least 3 times and\n",
    "-- were star baker at least 2 times and made an 'orange' thing in their\n",
    "-- signature bake in an episode. Return their fullname and age.\n",
    "\n",
    "SELECT\n",
    "   b.fullname\n",
    "   , b.age\n",
    "FROM\n",
    "   technicals t\n",
    "   , bakers b\n",
    "   , signatures s\n",
    "   , results r\n",
    "WHERE\n",
    "   t.baker = b.baker\n",
    "   and b.baker = s.baker\n",
    "   and r.baker = b.baker\n",
    "   and t.rank <= 3\n",
    "   and r.result = 'star baker'\n",
    "   and lower(s.make) like '%orange%'\n",
    "GROUP BY\n",
    "   b.baker\n",
    "HAVING\n",
    "   count(distinct t.episodeid) >= 3\n",
    "   and count(distinct r.episodeid) >= 2\n",
    "ORDER BY\n",
    "   b.fullname\n",
    "   , b.age\n",
    ";\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "76cebb9a-f388-4403-8276-fe1330c90895",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;baking&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'baking'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">2 rows affected.</span>"
      ],
      "text/plain": [
       "2 rows affected."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>fullname</th>\n",
       "            <th>age</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Kim-Joy Hewlett</td>\n",
       "            <td>27</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Rahul Mandal</td>\n",
       "            <td>30</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+-----------------+-----+\n",
       "|     fullname    | age |\n",
       "+-----------------+-----+\n",
       "| Kim-Joy Hewlett |  27 |\n",
       "|   Rahul Mandal  |  30 |\n",
       "+-----------------+-----+"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "-- The same query can also be solved using INTERSECT\n",
    "\n",
    "SELECT\n",
    "   b.fullname\n",
    "   , b.age\n",
    "FROM\n",
    "   technicals t\n",
    "   , bakers b   \n",
    "WHERE\n",
    "   b.baker = t.baker\n",
    "   and t.rank <= 3\n",
    "GROUP BY b.baker\n",
    "HAVING count(*)>=3\n",
    "\n",
    "INTERSECT\n",
    "\n",
    "SELECT\n",
    "   b.fullname\n",
    "   , b.age\n",
    "FROM\n",
    "   results r\n",
    "   , bakers b   \n",
    "WHERE\n",
    "   b.baker = r.baker\n",
    "   and r.result = 'star baker'\n",
    "GROUP BY b.baker\n",
    "HAVING count(*)>=2\n",
    "\n",
    "INTERSECT\n",
    "\n",
    "SELECT\n",
    "   b.fullname\n",
    "   , b.age\n",
    "FROM\n",
    "   signatures s\n",
    "   , bakers b   \n",
    "WHERE\n",
    "   b.baker = s.baker\n",
    "   and lower(s.make) like '%orange%'\n",
    "\n",
    "ORDER BY\n",
    "   fullname\n",
    "   , age\n",
    ";\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "81bd27b2-fc3b-446c-9411-8fa504d97835",
   "metadata": {},
   "source": [
    "![Query Execution Ordering](lecture12_figures/SQL_Execution_Order_Full.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "11bd1107-0845-4b59-a2ed-d9b27494a62e",
   "metadata": {},
   "source": [
    "Note the images are generated by ChatGPT based on my prompt."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5d505334-d32b-45d6-9e56-8ca82215bfaa",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.13.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
