{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "9090334b-1a86-4d41-8861-fb398f0ccd02",
   "metadata": {},
   "source": [
    "## Lecture 14- Advanced SQL\n",
    "\n",
    "**Announcements**\n",
    "\n",
    "  - Hw#4 is out due next monday\n",
    "  - Lecture exercise 13 out today, due on thursday at 2pm.\n",
    "\n",
    "### Advanced Constructs\n",
    "\n",
    "\n",
    "- Scalar queries: queries that return a single value \n",
    "\n",
    "- Queries as a set of tuples\n",
    "\n",
    "```\n",
    "value in/not in (set)\n",
    "value >=ALL (set)\n",
    "value <=ALL (set)\n",
    "value >ALL (set)\n",
    "value <ALL (set)\n",
    "value =ALL (set)\n",
    "value <>ALL (set)  \n",
    "```\n",
    "\n",
    "- Anonymous and non-anonymous queries as new relations in FROM or using WITH\n",
    "  - A query is anonymous if it only exists for a single query (regardless of\n",
    "    where it is located)                                                                \n",
    "- Correlated and uncorrelated subqueries\n",
    "\n",
    "- NOT EXISTS (set) : if the set is empty, then not exists is true! \n",
    "\n",
    "- For all queries"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d7509b5b-10ce-4f5a-bdf4-ca9fed62cf16",
   "metadata": {},
   "source": [
    "### Baker database\n",
    "\n",
    "Bakers(<u>baker</u>, fullname, age, occupation, hometown)  \n",
    "Episodes(<u>id</u>, title, firstaired, viewers7day, signature, technical, showstopper)  \n",
    "Favorites(<u>episodeid, baker</u>)  \n",
    "Results(<u>episodeid, baker</u>, result)  \n",
    "Showstoppers(<u>episodeid, baker</u>, make)  \n",
    "Signatures(<u>episodeid, baker</u>, make)  \n",
    "Technicals(<u>episodeid, baker</u>, rank)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "c7e310fb-68d7-4b28-a7d8-5b7467be076d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The sql extension is already loaded. To reload it, use:\n",
      "  %reload_ext sql\n"
     ]
    }
   ],
   "source": [
    "load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "bf5d02fb-ce74-4f72-b714-dc85df9a2b84",
   "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": 19,
   "id": "38020bdc-dbb4-4e66-bed1-30cc5647d8e3",
   "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\">4 rows affected.</span>"
      ],
      "text/plain": [
       "4 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>Jon</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Briony</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Rahul</td>\n",
       "            <td>5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ruby</td>\n",
       "            <td>7</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+--------+----------+\n",
       "| baker  | numtimes |\n",
       "+--------+----------+\n",
       "|  Jon   |    4     |\n",
       "| Briony |    4     |\n",
       "| Rahul  |    5     |\n",
       "|  Ruby  |    7     |\n",
       "+--------+----------+"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "    \n",
    "-- Find all bakers who were in top 3 of technicals more times than 'Manon';\n",
    "\n",
    "-- Within the subquery, local technicals overwrites the global one!\n",
    "\n",
    "select\n",
    "   baker\n",
    "   , count(*) as numtimes\n",
    "from\n",
    "   technicals  --- <- global technicals\n",
    "where\n",
    "   rank <= 3\n",
    "group by\n",
    "   baker\n",
    "having\n",
    "   count(*) > (select count(*) from technicals   --<- local technicals\n",
    "               where rank <=3 and baker = 'Manon') ;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "6ca53565-ef8d-4440-852d-0396667bff65",
   "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\">4 rows affected.</span>"
      ],
      "text/plain": [
       "4 rows affected."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>baker</th>\n",
       "            <th>numtimes</th>\n",
       "            <th>nummanon</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Jon</td>\n",
       "            <td>4</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Briony</td>\n",
       "            <td>4</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Rahul</td>\n",
       "            <td>5</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ruby</td>\n",
       "            <td>7</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+--------+----------+----------+\n",
       "| baker  | numtimes | nummanon |\n",
       "+--------+----------+----------+\n",
       "|  Jon   |    4     |    3     |\n",
       "| Briony |    4     |    3     |\n",
       "| Rahul  |    5     |    3     |\n",
       "|  Ruby  |    7     |    3     |\n",
       "+--------+----------+----------+"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "-- You can use a scalar query anywhere in the query that uses a constant\n",
    "\n",
    "select\n",
    "   baker\n",
    "   , count(*) as numtimes\n",
    "   , (select count(*) from technicals t1\n",
    "               where t1.rank <=3 and t1.baker = 'Manon') as nummanon\n",
    "from\n",
    "   technicals \n",
    "where\n",
    "   rank <= 3\n",
    "group by\n",
    "   baker\n",
    "having\n",
    "   count(*) > (select count(*) from technicals t1\n",
    "               where t1.rank <=3 and t1.baker = 'Manon') ;\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "c97986c0-995b-4f49-b1ff-b00f6b93a8fb",
   "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\">4 rows affected.</span>"
      ],
      "text/plain": [
       "4 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>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Jon</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Rahul</td>\n",
       "            <td>5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ruby</td>\n",
       "            <td>7</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+--------+----------+\n",
       "| baker  | numtimes |\n",
       "+--------+----------+\n",
       "| Briony |    4     |\n",
       "|  Jon   |    4     |\n",
       "| Rahul  |    5     |\n",
       "|  Ruby  |    7     |\n",
       "+--------+----------+"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "\n",
    "-- In this case, the subquery is not needed but for a larger relation\n",
    "-- it is likely that the subquery solution is much more efficient for this query.\n",
    "\n",
    "\n",
    "select\n",
    "   t.baker\n",
    "   , count(distinct t.episodeid) as numtimes\n",
    "from\n",
    "   technicals t\n",
    "   , technicals t2\n",
    "where\n",
    "   t.rank <= 3\n",
    "   and t2.rank <= 3\n",
    "   and t2.baker = 'Manon'\n",
    "   and t.baker <> 'Manon'\n",
    "group by\n",
    "   t.baker\n",
    "having\n",
    "   count(distinct t.episodeid) > count(distinct t2.episodeid);\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "d8df5a93-6d67-42b4-bc9e-d2b8ce472b7a",
   "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",
       "            <th>numtimes</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Manon</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Jon</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Briony</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Rahul</td>\n",
       "            <td>5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ruby</td>\n",
       "            <td>7</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Dan</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Terry</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Kim-Joy</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+---------+----------+\n",
       "|  baker  | numtimes |\n",
       "+---------+----------+\n",
       "|  Manon  |    3     |\n",
       "|   Jon   |    4     |\n",
       "|  Briony |    4     |\n",
       "|  Rahul  |    5     |\n",
       "|   Ruby  |    7     |\n",
       "|   Dan   |    3     |\n",
       "|  Terry  |    1     |\n",
       "| Kim-Joy |    3     |\n",
       "+---------+----------+"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "-- value in (set of values)\n",
    "-- value in (result of a query)\n",
    "\n",
    "select\n",
    "   baker\n",
    "   , count(*) as numtimes\n",
    "from\n",
    "   technicals \n",
    "where\n",
    "   rank IN (1,2,3)\n",
    "group by \n",
    "   baker;\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "9ebe96d4-41d5-4629-aae0-6a9e47995ad4",
   "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>id</th>\n",
       "            <th>title</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>4</td>\n",
       "            <td>Desserts</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>10</td>\n",
       "            <td>Final</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+----+----------+\n",
       "| id |  title   |\n",
       "+----+----------+\n",
       "| 4  | Desserts |\n",
       "| 10 |  Final   |\n",
       "+----+----------+"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "--- Find all episodes in which no one was eliminated.\n",
    "\n",
    "-- Solution 1\n",
    "-- select *\n",
    "-- from episodes\n",
    "-- where id not in (episodes in which someone was eliminated);\n",
    "    \n",
    "select id, title\n",
    "from episodes\n",
    "where id not in (select distinct episodeid from results\n",
    "                 where result = 'eliminated') ;\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "0b60be61-bbcc-4d16-85d0-55d3d1fa92a1",
   "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>id</th>\n",
       "            <th>title</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>4</td>\n",
       "            <td>Desserts</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>10</td>\n",
       "            <td>Final</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+----+----------+\n",
       "| id |  title   |\n",
       "+----+----------+\n",
       "| 4  | Desserts |\n",
       "| 10 |  Final   |\n",
       "+----+----------+"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "\n",
    "-- Solution 2\n",
    "\n",
    "select id, title\n",
    "from episodes\n",
    "except\n",
    "select e.id, e.title\n",
    "from\n",
    "   episodes e, results r\n",
    "where\n",
    "   e.id = r.episodeid and result = 'eliminated' ;\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "f7307a86-3d96-42ee-ae35-8f6731de7502",
   "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>id</th>\n",
       "            <th>title</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>4</td>\n",
       "            <td>Desserts</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>10</td>\n",
       "            <td>Final</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+----+----------+\n",
       "| id |  title   |\n",
       "+----+----------+\n",
       "| 4  | Desserts |\n",
       "| 10 |  Final   |\n",
       "+----+----------+"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "-- Solution 3\n",
    "select e.id, e.title\n",
    "from episodes e left join results r\n",
    "     on e.id = r.episodeid and r.result = 'eliminated'\n",
    "where\n",
    "     r.episodeid is null ;\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "f0081161-ee6e-46c2-9ae8-1d30f5962aaa",
   "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": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "\n",
    "-- Find bakers who were star baker at least twice, but never in back to\n",
    "--  back episodes.\n",
    "\n",
    "\n",
    "select\n",
    "   r.baker\n",
    "from\n",
    "   results r\n",
    "where\n",
    "   r.result = 'star baker'\n",
    "   and r.baker not in (select r2.baker\n",
    "                       from results r1, results r2\n",
    "                       where r1.baker = r2.baker\n",
    "                       and r1.episodeid +1 = r2.episodeid\n",
    "                       and r1.result = 'star baker'\n",
    "                       and r2.result = 'star baker')\n",
    "group by\n",
    "   r.baker\n",
    "having\n",
    "   count(*) >= 2;\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "504cb06a-0ce9-4994-a827-944cb1e5fc7a",
   "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": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "-- Alternate solution with except\n",
    "\n",
    "select\n",
    "   r.baker\n",
    "from\n",
    "   results r\n",
    "where\n",
    "   r.result = 'star baker'\n",
    "group by\n",
    "   r.baker\n",
    "having\n",
    "   count(*) >= 2\n",
    "except\n",
    "(select r2.baker\n",
    "from results r1, results r2\n",
    "where r1.baker = r2.baker\n",
    "and r1.episodeid +1 = r2.episodeid\n",
    "and r1.result = 'star baker'\n",
    "and r2.result = 'star baker'\n",
    ")\n",
    "\n",
    ";\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "f40a7583-b4c6-46ba-8b97-0f62c577b438",
   "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>id</th>\n",
       "            <th>numtimes</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>4</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>10</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>6</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>9</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>7</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>3</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>5</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>8</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",
       "| id | numtimes |\n",
       "+----+----------+\n",
       "| 4  |    2     |\n",
       "| 10 |    0     |\n",
       "| 6  |    2     |\n",
       "| 2  |    2     |\n",
       "| 9  |    1     |\n",
       "| 7  |    1     |\n",
       "| 3  |    1     |\n",
       "| 1  |    1     |\n",
       "| 5  |    1     |\n",
       "| 8  |    1     |\n",
       "+----+----------+\n",
       "Truncated to displaylimit of 10."
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "\n",
    "-- Find the episodes with more than the lowest number of favorites, return their\n",
    "--  title and airdate.\n",
    "\n",
    "-- First find counts\n",
    "\n",
    "\n",
    "\n",
    "select\n",
    "   e.id\n",
    "   , count(f.episodeid) as numtimes\n",
    "from\n",
    "   episodes e left join favorites f\n",
    "   on e.id = f.episodeid\n",
    "group by\n",
    "   e.id ;\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "c1cae8b8-4416-44d0-b0ae-ca80c44b33be",
   "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>numtimes</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+----------+\n",
       "| numtimes |\n",
       "+----------+\n",
       "|    1     |\n",
       "+----------+"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "-- Find the lowest number, excluding the last episode \n",
    "    \n",
    "select distinct\n",
    "   count(f.episodeid) as numtimes\n",
    "from\n",
    "   episodes e left join favorites f\n",
    "   on e.id = f.episodeid\n",
    "where\n",
    "   e.id < 10\n",
    "group by\n",
    "   e.id\n",
    "order by numtimes asc\n",
    "limit 1\n",
    ";\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "494bfa95-69ff-4ebd-8489-9f000d763582",
   "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\">9 rows affected.</span>"
      ],
      "text/plain": [
       "9 rows affected."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>id</th>\n",
       "            <th>title</th>\n",
       "            <th>numfavs</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "            <td>Biscuits</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>4</td>\n",
       "            <td>Desserts</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>3</td>\n",
       "            <td>Bread</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2</td>\n",
       "            <td>Cakes</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>8</td>\n",
       "            <td>Danish</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>5</td>\n",
       "            <td>Spice</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>6</td>\n",
       "            <td>Pastry</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>7</td>\n",
       "            <td>Vegan</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>9</td>\n",
       "            <td>Pâtisserie (Semi-final)</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+----+-------------------------+---------+\n",
       "| id |          title          | numfavs |\n",
       "+----+-------------------------+---------+\n",
       "| 1  |         Biscuits        |    1    |\n",
       "| 4  |         Desserts        |    2    |\n",
       "| 3  |          Bread          |    1    |\n",
       "| 2  |          Cakes          |    2    |\n",
       "| 8  |          Danish         |    1    |\n",
       "| 5  |          Spice          |    1    |\n",
       "| 6  |          Pastry         |    2    |\n",
       "| 7  |          Vegan          |    1    |\n",
       "| 9  | Pâtisserie (Semi-final) |    1    |\n",
       "+----+-------------------------+---------+"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "-- Find the episodes with more than the lowest number of favorites, return their\n",
    "--  title and airdate.\n",
    "\n",
    "\n",
    "select distinct\n",
    "   e.id, e.title\n",
    "   , count(f.episodeid) as numfavs\n",
    "from\n",
    "   episodes e left join favorites f\n",
    "   on e.id = f.episodeid\n",
    "group by\n",
    "    e.id\n",
    "having\n",
    "   count(f.episodeid) > (select distinct\n",
    "                          count(f.episodeid) as numtimes\n",
    "                          from\n",
    "                         episodes e left join favorites f\n",
    "                         on e.id = f.episodeid\n",
    "                        group by e.id\n",
    "                        order by numtimes asc\n",
    "                        limit 1);\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "7b1074fb-40c4-4c40-b680-de869aad9881",
   "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>id</th>\n",
       "            <th>title</th>\n",
       "            <th>numfavs</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>4</td>\n",
       "            <td>Desserts</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>6</td>\n",
       "            <td>Pastry</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2</td>\n",
       "            <td>Cakes</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+----+----------+---------+\n",
       "| id |  title   | numfavs |\n",
       "+----+----------+---------+\n",
       "| 4  | Desserts |    2    |\n",
       "| 6  |  Pastry  |    2    |\n",
       "| 2  |  Cakes   |    2    |\n",
       "+----+----------+---------+"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "\n",
    "-- Find the episodes with more than the lowest two number of favorites,\n",
    "--  return their title and airdate.\n",
    "\n",
    "\n",
    "select distinct\n",
    "   e.id, e.title\n",
    "   , count(f.episodeid) as numfavs\n",
    "from\n",
    "   episodes e left join favorites f\n",
    "   on e.id = f.episodeid\n",
    "group by\n",
    "    e.id\n",
    "having\n",
    "   count(f.episodeid) >ALL (select distinct\n",
    "                          count(f.episodeid) as numtimes\n",
    "                          from\n",
    "                         episodes e left join favorites f\n",
    "                         on e.id = f.episodeid\n",
    "                        group by e.id\n",
    "                        order by numtimes asc\n",
    "                        limit 2);\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3e32b53a-528f-4aa5-b2a6-8d3992cba23c",
   "metadata": {},
   "source": [
    "You can write queries more succinctly using WITH ()\n",
    "\n",
    "```\n",
    "WITH r1 as ( query1 ) \n",
    "     , r2 as (query 2 )\n",
    "\n",
    "select *\n",
    "from r1, r2\n",
    "where r1.a = r2.b ;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "3e86ec52-68ce-46c0-964d-620d1a8181ee",
   "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\">4 rows affected.</span>"
      ],
      "text/plain": [
       "4 rows affected."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>baker</th>\n",
       "            <th>numtimes</th>\n",
       "            <th>num</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Rahul</td>\n",
       "            <td>5</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ruby</td>\n",
       "            <td>7</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Briony</td>\n",
       "            <td>4</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Jon</td>\n",
       "            <td>4</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+--------+----------+-----+\n",
       "| baker  | numtimes | num |\n",
       "+--------+----------+-----+\n",
       "| Rahul  |    5     |  3  |\n",
       "|  Ruby  |    7     |  3  |\n",
       "| Briony |    4     |  3  |\n",
       "|  Jon   |    4     |  3  |\n",
       "+--------+----------+-----+"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "--- Rewriting an earlier query\n",
    "-- be very careful how to refer to a WITH query like any other relation\n",
    "\n",
    "with\n",
    "    manonq as (select count(*) as num\n",
    "     from technicals t1\n",
    "     where t1.rank <=3 and t1.baker = 'Manon') \n",
    "select\n",
    "   t.baker\n",
    "   , count(*) as numtimes\n",
    "   , mq.num\n",
    "from\n",
    "   technicals t, manonq mq\n",
    "where\n",
    "   t.rank <= 3\n",
    "group by\n",
    "   t.baker, mq.num\n",
    "having\n",
    "   count(*) > (select num from manonq) ;\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "b07a6f64-25b0-423e-bc27-3ba90fd83580",
   "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>id</th>\n",
       "            <th>title</th>\n",
       "            <th>numfavs</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>4</td>\n",
       "            <td>Desserts</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>6</td>\n",
       "            <td>Pastry</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2</td>\n",
       "            <td>Cakes</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+----+----------+---------+\n",
       "| id |  title   | numfavs |\n",
       "+----+----------+---------+\n",
       "| 4  | Desserts |    2    |\n",
       "| 6  |  Pastry  |    2    |\n",
       "| 2  |  Cakes   |    2    |\n",
       "+----+----------+---------+"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "with counts as (select distinct\n",
    "                      count(f.episodeid) as numtimes\n",
    "                from\n",
    "                     episodes e left join favorites f\n",
    "                      on e.id = f.episodeid\n",
    "                group by e.id\n",
    "                order by numtimes asc\n",
    "                limit 2)\n",
    "select distinct\n",
    "   e.id, e.title\n",
    "   , count(f.episodeid) as numfavs\n",
    "from\n",
    "   episodes e left join favorites f\n",
    "   on e.id = f.episodeid\n",
    "group by\n",
    "    e.id\n",
    "having\n",
    "   count(f.episodeid) >ALL (select numtimes from counts) ;\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "a238dec8-3e7e-4b05-97ce-81e660d3632b",
   "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",
       "            <th>fullname</th>\n",
       "            <th>age</th>\n",
       "            <th>occupation</th>\n",
       "            <th>hometown</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Kim-Joy</td>\n",
       "            <td>Kim-Joy Hewlett</td>\n",
       "            <td>27</td>\n",
       "            <td>Mental health specialist</td>\n",
       "            <td>Leeds</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Rahul</td>\n",
       "            <td>Rahul Mandal</td>\n",
       "            <td>30</td>\n",
       "            <td>Research scientist</td>\n",
       "            <td>Rotherham</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ruby</td>\n",
       "            <td>Ruby Bhogal</td>\n",
       "            <td>29</td>\n",
       "            <td>Project manager</td>\n",
       "            <td>London</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+---------+-----------------+-----+--------------------------+-----------+\n",
       "|  baker  |     fullname    | age |        occupation        |  hometown |\n",
       "+---------+-----------------+-----+--------------------------+-----------+\n",
       "| Kim-Joy | Kim-Joy Hewlett |  27 | Mental health specialist |   Leeds   |\n",
       "|  Rahul  |   Rahul Mandal  |  30 |    Research scientist    | Rotherham |\n",
       "|   Ruby  |   Ruby Bhogal   |  29 |     Project manager      |   London  |\n",
       "+---------+-----------------+-----+--------------------------+-----------+"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "-- not exists (set) -- return true if the set is empty.\n",
    "\n",
    "-- Find bakers who were never eliminated\n",
    "\n",
    "-- select * from bakers where not exists (a tuple for this baker\n",
    "--                                        in results where they are eliminated)\n",
    "\n",
    "select\n",
    "     *\n",
    "from\n",
    "     bakers b\n",
    "where\n",
    "     not exists (select * from results r\n",
    "                 where r.baker = b.baker\n",
    "\t\t       and r.result = 'eliminated') ;\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "a8dda868-8fc9-433f-bcbd-7131f8b9adfd",
   "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>baker</th>\n",
       "            <th>fullname</th>\n",
       "            <th>age</th>\n",
       "            <th>occupation</th>\n",
       "            <th>hometown</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Imelda</td>\n",
       "            <td>Imelda McCarron</td>\n",
       "            <td>33</td>\n",
       "            <td>Countryside recreation officer</td>\n",
       "            <td>County Tyrone</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Luke</td>\n",
       "            <td>Luke Thompson</td>\n",
       "            <td>30</td>\n",
       "            <td>Civil servant/house and techno DJ</td>\n",
       "            <td>Sheffield</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+--------+-----------------+-----+-----------------------------------+---------------+\n",
       "| baker  |     fullname    | age |             occupation            |    hometown   |\n",
       "+--------+-----------------+-----+-----------------------------------+---------------+\n",
       "| Imelda | Imelda McCarron |  33 |   Countryside recreation officer  | County Tyrone |\n",
       "|  Luke  |  Luke Thompson  |  30 | Civil servant/house and techno DJ |   Sheffield   |\n",
       "+--------+-----------------+-----+-----------------------------------+---------------+"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "-- Find bakers who never improved in the technicals by more than a single\n",
    "-- position in a subsequent episode\n",
    "\n",
    "-- select\n",
    "--     *\n",
    "-- from\n",
    "--     bakers b\n",
    "-- where\n",
    "--     not exists (two technical tuples showing improvement for this baker )\n",
    "\n",
    "select\n",
    "    *\n",
    "from\n",
    "    bakers b\n",
    "where\n",
    "    not exists (select *\n",
    "                from technicals t1, technicals t2\n",
    "\t\twhere t1.baker = t2.baker and t1.baker = b.baker\n",
    "\t\t      and t2.rank - t1.rank > 1\n",
    "\t\t      and t1.episodeid > t2.episodeid) ;\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "47ff3fe9-d726-42ee-acf5-b0641e47665b",
   "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>baker</th>\n",
       "            <th>fullname</th>\n",
       "            <th>age</th>\n",
       "            <th>occupation</th>\n",
       "            <th>hometown</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Imelda</td>\n",
       "            <td>Imelda McCarron</td>\n",
       "            <td>33</td>\n",
       "            <td>Countryside recreation officer</td>\n",
       "            <td>County Tyrone</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Luke</td>\n",
       "            <td>Luke Thompson</td>\n",
       "            <td>30</td>\n",
       "            <td>Civil servant/house and techno DJ</td>\n",
       "            <td>Sheffield</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+--------+-----------------+-----+-----------------------------------+---------------+\n",
       "| baker  |     fullname    | age |             occupation            |    hometown   |\n",
       "+--------+-----------------+-----+-----------------------------------+---------------+\n",
       "| Imelda | Imelda McCarron |  33 |   Countryside recreation officer  | County Tyrone |\n",
       "|  Luke  |  Luke Thompson  |  30 | Civil servant/house and techno DJ |   Sheffield   |\n",
       "+--------+-----------------+-----+-----------------------------------+---------------+"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "-- Can also use not in with a non-correlated query\n",
    "select\n",
    "    *\n",
    "from\n",
    "    bakers b\n",
    "where\n",
    "    b.baker not in (select t1.baker\n",
    "                from technicals t1, technicals t2\n",
    "\t\twhere t1.baker = t2.baker\n",
    "\t\t      and t2.rank - t1.rank > 1\n",
    "\t\t      and t1.episodeid > t2.episodeid) ;\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "3997f9b9-00ac-43a4-afe0-4fb48d75e471",
   "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",
       "            <th>fullname</th>\n",
       "            <th>age</th>\n",
       "            <th>occupation</th>\n",
       "            <th>hometown</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Kim-Joy</td>\n",
       "            <td>Kim-Joy Hewlett</td>\n",
       "            <td>27</td>\n",
       "            <td>Mental health specialist</td>\n",
       "            <td>Leeds</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Rahul</td>\n",
       "            <td>Rahul Mandal</td>\n",
       "            <td>30</td>\n",
       "            <td>Research scientist</td>\n",
       "            <td>Rotherham</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ruby</td>\n",
       "            <td>Ruby Bhogal</td>\n",
       "            <td>29</td>\n",
       "            <td>Project manager</td>\n",
       "            <td>London</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+---------+-----------------+-----+--------------------------+-----------+\n",
       "|  baker  |     fullname    | age |        occupation        |  hometown |\n",
       "+---------+-----------------+-----+--------------------------+-----------+\n",
       "| Kim-Joy | Kim-Joy Hewlett |  27 | Mental health specialist |   Leeds   |\n",
       "|  Rahul  |   Rahul Mandal  |  30 |    Research scientist    | Rotherham |\n",
       "|   Ruby  |   Ruby Bhogal   |  29 |     Project manager      |   London  |\n",
       "+---------+-----------------+-----+--------------------------+-----------+"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "-- FOR ALL QUERIES OFTEN requires two set subtractions\n",
    "\n",
    "-- Find bakers who competed in all the episodes of the season\n",
    "\n",
    "-- select * from bakers b\n",
    "--      where not exists ( an episode in the seasons such that\n",
    "--          there does not exist a tuple in technicals for this\n",
    "-- \t        baker and this episode)\n",
    "\n",
    "\n",
    "select * from bakers b\n",
    "where not exists\n",
    "     (select * from episodes e\n",
    "      where not exists (select * from technicals t\n",
    "                        where t.baker = b.baker\n",
    "\t\t\t      and t.episodeid = e.id)) ;\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "2e60cc38-e9ba-448b-b43b-429a63030314",
   "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",
       "            <th>fullname</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Rahul</td>\n",
       "            <td>Rahul Mandal</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ruby</td>\n",
       "            <td>Ruby Bhogal</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Kim-Joy</td>\n",
       "            <td>Kim-Joy Hewlett</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+---------+-----------------+\n",
       "|  baker  |     fullname    |\n",
       "+---------+-----------------+\n",
       "|  Rahul  |   Rahul Mandal  |\n",
       "|   Ruby  |   Ruby Bhogal   |\n",
       "| Kim-Joy | Kim-Joy Hewlett |\n",
       "+---------+-----------------+"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "-- Do not forget that much cheaper ways to write the same query may exist\n",
    "\n",
    "select\n",
    "    b.baker\n",
    "    , b.fullname\n",
    "from\n",
    "    bakers b\n",
    "    , technicals t\n",
    "where\n",
    "    b.baker = t.baker\n",
    "group by\n",
    "    b.baker\n",
    "having\n",
    "    count(*) = (select count(*) from episodes) ;\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "af3b8910-cc9a-4673-9e5c-2c04d51f6f24",
   "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",
       "            <th>numwins</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Ruby</td>\n",
       "            <td>5</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+-------+---------+\n",
       "| baker | numwins |\n",
       "+-------+---------+\n",
       "|  Ruby |    5    |\n",
       "+-------+---------+"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "-- Find the bakers with the highest total number of wins and technical\n",
    "--  wins combined.\n",
    "\n",
    "\n",
    "WITH counts as\n",
    "     (select b.baker\n",
    "          , count(distinct r.episodeid)+count(distinct t.episodeid) as numwins\n",
    "     from bakers b left join results r\n",
    "          on b.baker = r.baker and r.result = 'star baker'\n",
    "\t  left join technicals t \n",
    "              on b.baker = t.baker and t.rank = 1\n",
    "      group by b.baker\t      \n",
    ")\n",
    "select baker, numwins\n",
    "from counts\n",
    "where  numwins = (select max(numwins) from counts) ;\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b60acf55-8b8a-4f8e-bada-45dca3e62a9d",
   "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
}
