notebooks/Relational-Division.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "4f2d3834",
   "metadata": {},
   "source": [
    "# Review: Complex Queries"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "31ebffab",
   "metadata": {},
   "source": [
    "* Restriction by a query (Subquery in the `WHERE` clause)\n",
    "* Join\n",
    "    - Cross join\n",
    "    - Inner join\n",
    "    - Equijoin\n",
    "    - Natural join\n",
    "* Aggregation  \n",
    "    * `LEFT JOIN` followed by `GROUP BY`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9ff1a177",
   "metadata": {},
   "source": [
    "# Relational Division\n",
    "\n",
    "Relational division is a query of the type: \n",
    "> \"Find all entries in A that have a mathcing entry in B for each entry in C.\"\n",
    "\n",
    "For example, imagine you have a database for managing the hiring pipeline for a company. You have Tables `Candidate` and `CandidateSkill` listing all employees' skills. Then you have a table `Job` and `RequiredSkills`. \n",
    "\n",
    "You are now asked to\n",
    "> \"Show all the candidates who qualify for a given job.\"\n",
    "\n",
    "Examples from other databases could be:\n",
    "> \"Find all students who have completed all the required courses for their major.\"\n",
    "> \"Find all students who have completed all the required courses for their major.\"\n",
    "> \"Show all airlins that every \n",
    "\n",
    "This is not a trivial query with no direct support in DataJoint and SQL. There are several ways to solve it. I will illustrate just one. You can search for other articles on relational division to review other approaches."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "7e101f04",
   "metadata": {},
   "outputs": [],
   "source": [
    "import datajoint as dj\n",
    "from faker import Faker\n",
    "fake = Faker()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "7b6f7a79",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[2022-10-25 17:15:30,182][INFO]: Connecting dimitri@db.ust-data-sci.net:3306\n",
      "[2022-10-25 17:15:30,790][INFO]: Connected dimitri@db.ust-data-sci.net:3306\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Proceed to delete entire schema `dimitri_hiring`? [yes, No]: yes\n"
     ]
    }
   ],
   "source": [
    "schema = dj.Schema('dimitri_hiring')\n",
    "schema.drop()\n",
    "schema = dj.Schema('dimitri_hiring')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "f844857a",
   "metadata": {},
   "outputs": [],
   "source": [
    "@schema\n",
    "class Skill(dj.Lookup):\n",
    "    definition = \"\"\"\n",
    "    skill : varchar(24)\n",
    "    \"\"\"\n",
    "    contents = zip(\n",
    "        (\"SQL\", \"Java\", \"Python\", \"C++\", \n",
    "         \"JavaScript\", \"R\", \"Rust\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "a20278c3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "    \n",
       "    <style type=\"text/css\">\n",
       "        .Relation{\n",
       "            border-collapse:collapse;\n",
       "        }\n",
       "        .Relation th{\n",
       "            background: #A0A0A0; color: #ffffff; padding:4px; border:#f0e0e0 1px solid;\n",
       "            font-weight: normal; font-family: monospace; font-size: 100%;\n",
       "        }\n",
       "        .Relation td{\n",
       "            padding:4px; border:#f0e0e0 1px solid; font-size:100%;\n",
       "        }\n",
       "        .Relation tr:nth-child(odd){\n",
       "            background: #ffffff;\n",
       "        }\n",
       "        .Relation tr:nth-child(even){\n",
       "            background: #f3f1ff;\n",
       "        }\n",
       "        /* Tooltip container */\n",
       "        .djtooltip {\n",
       "        }\n",
       "        /* Tooltip text */\n",
       "        .djtooltip .djtooltiptext {\n",
       "            visibility: hidden;\n",
       "            width: 120px;\n",
       "            background-color: black;\n",
       "            color: #fff;\n",
       "            text-align: center;\n",
       "            padding: 5px 0;\n",
       "            border-radius: 6px;\n",
       "            /* Position the tooltip text - see examples below! */\n",
       "            position: absolute;\n",
       "            z-index: 1;\n",
       "        }\n",
       "        #primary {\n",
       "            font-weight: bold;\n",
       "            color: black;\n",
       "        }\n",
       "        #nonprimary {\n",
       "            font-weight: normal;\n",
       "            color: white;\n",
       "        }\n",
       "\n",
       "        /* Show the tooltip text when you mouse over the tooltip container */\n",
       "        .djtooltip:hover .djtooltiptext {\n",
       "            visibility: visible;\n",
       "        }\n",
       "    </style>\n",
       "    \n",
       "    <b></b>\n",
       "        <div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "        <table border=\"1\" class=\"Relation\">\n",
       "            <thead> <tr style=\"text-align: right;\"> <th> <div class=\"djtooltip\">\n",
       "                            <p id=\"primary\">skill</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div> </th> </tr> </thead>\n",
       "            <tbody> <tr> <td>C++</td></tr><tr><td>Java</td></tr><tr><td>JavaScript</td></tr><tr><td>Python</td></tr><tr><td>R</td></tr><tr><td>Rust</td></tr><tr><td>SQL</td> </tr> </tbody>\n",
       "        </table>\n",
       "        \n",
       "        <p>Total: 7</p></div>\n",
       "        "
      ],
      "text/plain": [
       "*skill        \n",
       "+------------+\n",
       "C++           \n",
       "Java          \n",
       "JavaScript    \n",
       "Python        \n",
       "R             \n",
       "Rust          \n",
       "SQL           \n",
       " (Total: 7)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Skill()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "141b82c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "@schema\n",
    "class Candidate(dj.Manual):\n",
    "    definition = \"\"\"\n",
    "    candidate_id : int\n",
    "    ---\n",
    "    name  : varchar(60)\n",
    "    \"\"\"\n",
    "    \n",
    "    class Skill(dj.Part):\n",
    "        definition = \"\"\"\n",
    "        -> master\n",
    "        -> Skill\n",
    "        \"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "4e234582",
   "metadata": {},
   "outputs": [],
   "source": [
    "Candidate.insert(((fake.random_int(), fake.name()) for _ in range(300)), \n",
    "                 skip_duplicates=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "8e168932",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "    \n",
       "    <style type=\"text/css\">\n",
       "        .Relation{\n",
       "            border-collapse:collapse;\n",
       "        }\n",
       "        .Relation th{\n",
       "            background: #A0A0A0; color: #ffffff; padding:4px; border:#f0e0e0 1px solid;\n",
       "            font-weight: normal; font-family: monospace; font-size: 100%;\n",
       "        }\n",
       "        .Relation td{\n",
       "            padding:4px; border:#f0e0e0 1px solid; font-size:100%;\n",
       "        }\n",
       "        .Relation tr:nth-child(odd){\n",
       "            background: #ffffff;\n",
       "        }\n",
       "        .Relation tr:nth-child(even){\n",
       "            background: #f3f1ff;\n",
       "        }\n",
       "        /* Tooltip container */\n",
       "        .djtooltip {\n",
       "        }\n",
       "        /* Tooltip text */\n",
       "        .djtooltip .djtooltiptext {\n",
       "            visibility: hidden;\n",
       "            width: 120px;\n",
       "            background-color: black;\n",
       "            color: #fff;\n",
       "            text-align: center;\n",
       "            padding: 5px 0;\n",
       "            border-radius: 6px;\n",
       "            /* Position the tooltip text - see examples below! */\n",
       "            position: absolute;\n",
       "            z-index: 1;\n",
       "        }\n",
       "        #primary {\n",
       "            font-weight: bold;\n",
       "            color: black;\n",
       "        }\n",
       "        #nonprimary {\n",
       "            font-weight: normal;\n",
       "            color: white;\n",
       "        }\n",
       "\n",
       "        /* Show the tooltip text when you mouse over the tooltip container */\n",
       "        .djtooltip:hover .djtooltiptext {\n",
       "            visibility: visible;\n",
       "        }\n",
       "    </style>\n",
       "    \n",
       "    <b></b>\n",
       "        <div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "        <table border=\"1\" class=\"Relation\">\n",
       "            <thead> <tr style=\"text-align: right;\"> <th> <div class=\"djtooltip\">\n",
       "                            <p id=\"primary\">candidate_id</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div></th><th><div class=\"djtooltip\">\n",
       "                            <p id=\"nonprimary\">name</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div> </th> </tr> </thead>\n",
       "            <tbody> <tr> <td>23</td>\n",
       "<td>Courtney Oneill</td></tr><tr><td>41</td>\n",
       "<td>Tina Moss</td></tr><tr><td>42</td>\n",
       "<td>Kenneth Terry</td></tr><tr><td>58</td>\n",
       "<td>Ronald Roberts</td></tr><tr><td>143</td>\n",
       "<td>Dennis Robertson</td></tr><tr><td>145</td>\n",
       "<td>Jose Anderson</td></tr><tr><td>153</td>\n",
       "<td>Lisa Atkins</td></tr><tr><td>180</td>\n",
       "<td>Jade Clark</td></tr><tr><td>196</td>\n",
       "<td>Jose Reynolds</td></tr><tr><td>200</td>\n",
       "<td>Thomas Bolton</td></tr><tr><td>218</td>\n",
       "<td>Bryce Smith</td></tr><tr><td>234</td>\n",
       "<td>Jimmy Turner</td> </tr> </tbody>\n",
       "        </table>\n",
       "        <p>...</p>\n",
       "        <p>Total: 297</p></div>\n",
       "        "
      ],
      "text/plain": [
       "*candidate_id  name          \n",
       "+------------+ +------------+\n",
       "23             Courtney Oneil\n",
       "41             Tina Moss     \n",
       "42             Kenneth Terry \n",
       "58             Ronald Roberts\n",
       "143            Dennis Roberts\n",
       "145            Jose Anderson \n",
       "153            Lisa Atkins   \n",
       "180            Jade Clark    \n",
       "196            Jose Reynolds \n",
       "200            Thomas Bolton \n",
       "218            Bryce Smith   \n",
       "234            Jimmy Turner  \n",
       "   ...\n",
       " (Total: 297)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Candidate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "c66542ae",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "    \n",
       "    <style type=\"text/css\">\n",
       "        .Relation{\n",
       "            border-collapse:collapse;\n",
       "        }\n",
       "        .Relation th{\n",
       "            background: #A0A0A0; color: #ffffff; padding:4px; border:#f0e0e0 1px solid;\n",
       "            font-weight: normal; font-family: monospace; font-size: 100%;\n",
       "        }\n",
       "        .Relation td{\n",
       "            padding:4px; border:#f0e0e0 1px solid; font-size:100%;\n",
       "        }\n",
       "        .Relation tr:nth-child(odd){\n",
       "            background: #ffffff;\n",
       "        }\n",
       "        .Relation tr:nth-child(even){\n",
       "            background: #f3f1ff;\n",
       "        }\n",
       "        /* Tooltip container */\n",
       "        .djtooltip {\n",
       "        }\n",
       "        /* Tooltip text */\n",
       "        .djtooltip .djtooltiptext {\n",
       "            visibility: hidden;\n",
       "            width: 120px;\n",
       "            background-color: black;\n",
       "            color: #fff;\n",
       "            text-align: center;\n",
       "            padding: 5px 0;\n",
       "            border-radius: 6px;\n",
       "            /* Position the tooltip text - see examples below! */\n",
       "            position: absolute;\n",
       "            z-index: 1;\n",
       "        }\n",
       "        #primary {\n",
       "            font-weight: bold;\n",
       "            color: black;\n",
       "        }\n",
       "        #nonprimary {\n",
       "            font-weight: normal;\n",
       "            color: white;\n",
       "        }\n",
       "\n",
       "        /* Show the tooltip text when you mouse over the tooltip container */\n",
       "        .djtooltip:hover .djtooltiptext {\n",
       "            visibility: visible;\n",
       "        }\n",
       "    </style>\n",
       "    \n",
       "    \n",
       "        <div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "        <table border=\"1\" class=\"Relation\">\n",
       "            <thead> <tr style=\"text-align: right;\"> <th> <div class=\"djtooltip\">\n",
       "                            <p id=\"primary\">candidate_id</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div></th><th><div class=\"djtooltip\">\n",
       "                            <p id=\"primary\">skill</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div></th><th><div class=\"djtooltip\">\n",
       "                            <p id=\"nonprimary\">name</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div> </th> </tr> </thead>\n",
       "            <tbody> <tr> <td>23</td>\n",
       "<td>C++</td>\n",
       "<td>Courtney Oneill</td></tr><tr><td>23</td>\n",
       "<td>Java</td>\n",
       "<td>Courtney Oneill</td></tr><tr><td>41</td>\n",
       "<td>JavaScript</td>\n",
       "<td>Tina Moss</td></tr><tr><td>41</td>\n",
       "<td>SQL</td>\n",
       "<td>Tina Moss</td></tr><tr><td>42</td>\n",
       "<td>Java</td>\n",
       "<td>Kenneth Terry</td></tr><tr><td>58</td>\n",
       "<td>Java</td>\n",
       "<td>Ronald Roberts</td></tr><tr><td>58</td>\n",
       "<td>Python</td>\n",
       "<td>Ronald Roberts</td></tr><tr><td>58</td>\n",
       "<td>R</td>\n",
       "<td>Ronald Roberts</td></tr><tr><td>143</td>\n",
       "<td>Java</td>\n",
       "<td>Dennis Robertson</td></tr><tr><td>143</td>\n",
       "<td>JavaScript</td>\n",
       "<td>Dennis Robertson</td></tr><tr><td>143</td>\n",
       "<td>SQL</td>\n",
       "<td>Dennis Robertson</td></tr><tr><td>145</td>\n",
       "<td>C++</td>\n",
       "<td>Jose Anderson</td> </tr> </tbody>\n",
       "        </table>\n",
       "        <p>...</p>\n",
       "        <p>Total: 846</p></div>\n",
       "        "
      ],
      "text/plain": [
       "*candidate_id  *skill         name          \n",
       "+------------+ +------------+ +------------+\n",
       "23             Java           Courtney Oneil\n",
       "23             R              Courtney Oneil\n",
       "41             Python         Tina Moss     \n",
       "41             SQL            Tina Moss     \n",
       "42             R              Kenneth Terry \n",
       "58             Java           Ronald Roberts\n",
       "58             R              Ronald Roberts\n",
       "58             SQL            Ronald Roberts\n",
       "143            JavaScript     Dennis Roberts\n",
       "143            Rust           Dennis Roberts\n",
       "145            JavaScript     Jose Anderson \n",
       "145            SQL            Jose Anderson \n",
       "   ...\n",
       " (Total: 844)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Candidate * Skill & 'rand() < 0.4'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "0ab04879",
   "metadata": {},
   "outputs": [],
   "source": [
    "Candidate.Skill.insert(\n",
    "    Candidate.proj() * Skill & 'RAND() < 0.4')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "9a634fd3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "    \n",
       "    <style type=\"text/css\">\n",
       "        .Relation{\n",
       "            border-collapse:collapse;\n",
       "        }\n",
       "        .Relation th{\n",
       "            background: #A0A0A0; color: #ffffff; padding:4px; border:#f0e0e0 1px solid;\n",
       "            font-weight: normal; font-family: monospace; font-size: 100%;\n",
       "        }\n",
       "        .Relation td{\n",
       "            padding:4px; border:#f0e0e0 1px solid; font-size:100%;\n",
       "        }\n",
       "        .Relation tr:nth-child(odd){\n",
       "            background: #ffffff;\n",
       "        }\n",
       "        .Relation tr:nth-child(even){\n",
       "            background: #f3f1ff;\n",
       "        }\n",
       "        /* Tooltip container */\n",
       "        .djtooltip {\n",
       "        }\n",
       "        /* Tooltip text */\n",
       "        .djtooltip .djtooltiptext {\n",
       "            visibility: hidden;\n",
       "            width: 120px;\n",
       "            background-color: black;\n",
       "            color: #fff;\n",
       "            text-align: center;\n",
       "            padding: 5px 0;\n",
       "            border-radius: 6px;\n",
       "            /* Position the tooltip text - see examples below! */\n",
       "            position: absolute;\n",
       "            z-index: 1;\n",
       "        }\n",
       "        #primary {\n",
       "            font-weight: bold;\n",
       "            color: black;\n",
       "        }\n",
       "        #nonprimary {\n",
       "            font-weight: normal;\n",
       "            color: white;\n",
       "        }\n",
       "\n",
       "        /* Show the tooltip text when you mouse over the tooltip container */\n",
       "        .djtooltip:hover .djtooltiptext {\n",
       "            visibility: visible;\n",
       "        }\n",
       "    </style>\n",
       "    \n",
       "    <b></b>\n",
       "        <div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "        <table border=\"1\" class=\"Relation\">\n",
       "            <thead> <tr style=\"text-align: right;\"> <th> <div class=\"djtooltip\">\n",
       "                            <p id=\"primary\">candidate_id</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div></th><th><div class=\"djtooltip\">\n",
       "                            <p id=\"primary\">skill</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div> </th> </tr> </thead>\n",
       "            <tbody> <tr> <td>23</td>\n",
       "<td>C++</td></tr><tr><td>23</td>\n",
       "<td>JavaScript</td></tr><tr><td>23</td>\n",
       "<td>Python</td></tr><tr><td>23</td>\n",
       "<td>SQL</td></tr><tr><td>41</td>\n",
       "<td>C++</td></tr><tr><td>41</td>\n",
       "<td>Java</td></tr><tr><td>41</td>\n",
       "<td>JavaScript</td></tr><tr><td>41</td>\n",
       "<td>R</td></tr><tr><td>41</td>\n",
       "<td>Rust</td></tr><tr><td>42</td>\n",
       "<td>C++</td></tr><tr><td>42</td>\n",
       "<td>Java</td></tr><tr><td>42</td>\n",
       "<td>R</td> </tr> </tbody>\n",
       "        </table>\n",
       "        <p>...</p>\n",
       "        <p>Total: 823</p></div>\n",
       "        "
      ],
      "text/plain": [
       "*candidate_id  *skill        \n",
       "+------------+ +------------+\n",
       "23             C++           \n",
       "23             JavaScript    \n",
       "23             Python        \n",
       "23             SQL           \n",
       "41             C++           \n",
       "41             Java          \n",
       "41             JavaScript    \n",
       "41             R             \n",
       "41             Rust          \n",
       "42             C++           \n",
       "42             Java          \n",
       "42             R             \n",
       "   ...\n",
       " (Total: 823)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Candidate.Skill()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "d9a51bff",
   "metadata": {},
   "outputs": [],
   "source": [
    "@schema\n",
    "class Position(dj.Manual):\n",
    "    definition = \"\"\"\n",
    "    position : varchar(12)   \n",
    "    ---\n",
    "    position_description : varchar(60)\n",
    "    \"\"\"\n",
    "\n",
    "    class Skill(dj.Part):\n",
    "        definition = \"\"\"\n",
    "        -> master\n",
    "        -> Skill\n",
    "        \"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "0bd86c89",
   "metadata": {},
   "outputs": [],
   "source": [
    "Position.insert1((\"SWE1\", \"Software Engineer I\"))\n",
    "Position.Skill.insert((\n",
    "    (\"SWE1\", \"Rust\"),\n",
    "    (\"SWE1\", \"JavaScript\"),\n",
    "    (\"SWE1\", \"Java\")))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "5b4ec1be",
   "metadata": {},
   "outputs": [],
   "source": [
    "Position.insert1((\"DS2\", \"Data Scientist II\"))\n",
    "Position.Skill.insert((\n",
    "    (\"DS2\", \"SQL\"),\n",
    "    (\"DS2\", \"Python\")))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "e68a0159",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "    \n",
       "    <style type=\"text/css\">\n",
       "        .Relation{\n",
       "            border-collapse:collapse;\n",
       "        }\n",
       "        .Relation th{\n",
       "            background: #A0A0A0; color: #ffffff; padding:4px; border:#f0e0e0 1px solid;\n",
       "            font-weight: normal; font-family: monospace; font-size: 100%;\n",
       "        }\n",
       "        .Relation td{\n",
       "            padding:4px; border:#f0e0e0 1px solid; font-size:100%;\n",
       "        }\n",
       "        .Relation tr:nth-child(odd){\n",
       "            background: #ffffff;\n",
       "        }\n",
       "        .Relation tr:nth-child(even){\n",
       "            background: #f3f1ff;\n",
       "        }\n",
       "        /* Tooltip container */\n",
       "        .djtooltip {\n",
       "        }\n",
       "        /* Tooltip text */\n",
       "        .djtooltip .djtooltiptext {\n",
       "            visibility: hidden;\n",
       "            width: 120px;\n",
       "            background-color: black;\n",
       "            color: #fff;\n",
       "            text-align: center;\n",
       "            padding: 5px 0;\n",
       "            border-radius: 6px;\n",
       "            /* Position the tooltip text - see examples below! */\n",
       "            position: absolute;\n",
       "            z-index: 1;\n",
       "        }\n",
       "        #primary {\n",
       "            font-weight: bold;\n",
       "            color: black;\n",
       "        }\n",
       "        #nonprimary {\n",
       "            font-weight: normal;\n",
       "            color: white;\n",
       "        }\n",
       "\n",
       "        /* Show the tooltip text when you mouse over the tooltip container */\n",
       "        .djtooltip:hover .djtooltiptext {\n",
       "            visibility: visible;\n",
       "        }\n",
       "    </style>\n",
       "    \n",
       "    <b></b>\n",
       "        <div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "        <table border=\"1\" class=\"Relation\">\n",
       "            <thead> <tr style=\"text-align: right;\"> <th> <div class=\"djtooltip\">\n",
       "                            <p id=\"primary\">position</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div></th><th><div class=\"djtooltip\">\n",
       "                            <p id=\"primary\">skill</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div> </th> </tr> </thead>\n",
       "            <tbody> <tr> <td>DS2</td>\n",
       "<td>Python</td></tr><tr><td>DS2</td>\n",
       "<td>SQL</td></tr><tr><td>SWE1</td>\n",
       "<td>Java</td></tr><tr><td>SWE1</td>\n",
       "<td>JavaScript</td></tr><tr><td>SWE1</td>\n",
       "<td>Rust</td> </tr> </tbody>\n",
       "        </table>\n",
       "        \n",
       "        <p>Total: 5</p></div>\n",
       "        "
      ],
      "text/plain": [
       "*position    *skill        \n",
       "+----------+ +------------+\n",
       "DS2          Python        \n",
       "DS2          SQL           \n",
       "SWE1         Java          \n",
       "SWE1         JavaScript    \n",
       "SWE1         Rust          \n",
       " (Total: 5)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Position.Skill()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "5412c315",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/svg+xml": [
       "<svg xmlns=\"http://www.w3.org/2000/svg\" xmlns:xlink=\"http://www.w3.org/1999/xlink\" width=\"225pt\" height=\"98pt\" viewBox=\"0.00 0.00 224.50 98.00\">\n",
       "<g id=\"graph0\" class=\"graph\" transform=\"scale(1 1) rotate(0) translate(4 94)\">\n",
       "<polygon fill=\"white\" stroke=\"none\" points=\"-4,4 -4,-94 220.5,-94 220.5,4 -4,4\"/>\n",
       "<!-- Candidate -->\n",
       "<g id=\"node1\" class=\"node\">\n",
       "<title>Candidate</title>\n",
       "<g id=\"a_node1\"><a xlink:title=\"candidate_id         \r",
       "------------------------------\r",
       "name                 \r",
       "\">\n",
       "<polygon fill=\"#00ff00\" fill-opacity=\"0.188235\" stroke=\"#00ff00\" stroke-opacity=\"0.188235\" points=\"77.5,-90 5.5,-90 5.5,-55 77.5,-55 77.5,-90\"/>\n",
       "<text text-anchor=\"start\" x=\"13.5\" y=\"-70.4\" font-family=\"arial\" text-decoration=\"underline\" font-size=\"12.00\" fill=\"darkgreen\">Candidate</text>\n",
       "</a>\n",
       "</g>\n",
       "</g>\n",
       "<!-- Candidate.Skill -->\n",
       "<g id=\"node3\" class=\"node\">\n",
       "<title>Candidate.Skill</title>\n",
       "<g id=\"a_node3\"><a xlink:title=\"→ Candidate\r",
       "→ Skill\r",
       "\">\n",
       "<polygon fill=\"none\" stroke=\"none\" points=\"83,-19 0,-19 0,0 83,0 83,-19\"/>\n",
       "<text text-anchor=\"middle\" x=\"41.5\" y=\"-7\" font-family=\"arial\" font-size=\"10.00\">Candidate.Skill</text>\n",
       "</a>\n",
       "</g>\n",
       "</g>\n",
       "<!-- Candidate&#45;&gt;Candidate.Skill -->\n",
       "<g id=\"edge1\" class=\"edge\">\n",
       "<title>Candidate-&gt;Candidate.Skill</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" stroke-width=\"0.75\" stroke-opacity=\"0.250980\" d=\"M41.5,-54.83C41.5,-43.26 41.5,-28.32 41.5,-19.08\"/>\n",
       "</g>\n",
       "<!-- Skill -->\n",
       "<g id=\"node2\" class=\"node\">\n",
       "<title>Skill</title>\n",
       "<g id=\"a_node2\"><a xlink:title=\"skill                \r",
       "\">\n",
       "<polygon fill=\"#000000\" fill-opacity=\"0.125490\" stroke=\"none\" points=\"131,-90 96,-90 96,-55 131,-55 131,-90\"/>\n",
       "<text text-anchor=\"start\" x=\"104\" y=\"-71\" font-family=\"arial\" text-decoration=\"underline\" font-size=\"10.00\">Skill</text>\n",
       "</a>\n",
       "</g>\n",
       "</g>\n",
       "<!-- Skill&#45;&gt;Candidate.Skill -->\n",
       "<g id=\"edge2\" class=\"edge\">\n",
       "<title>Skill-&gt;Candidate.Skill</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" stroke-width=\"0.75\" stroke-opacity=\"0.250980\" d=\"M95.7,-56.42C81.88,-44.71 63.19,-28.88 51.76,-19.19\"/>\n",
       "</g>\n",
       "<!-- Position.Skill -->\n",
       "<g id=\"node4\" class=\"node\">\n",
       "<title>Position.Skill</title>\n",
       "<g id=\"a_node4\"><a xlink:title=\"→ Position\r",
       "→ Skill\r",
       "\">\n",
       "<polygon fill=\"none\" stroke=\"none\" points=\"216.5,-19 142.5,-19 142.5,0 216.5,0 216.5,-19\"/>\n",
       "<text text-anchor=\"middle\" x=\"179.5\" y=\"-7\" font-family=\"arial\" font-size=\"10.00\">Position.Skill</text>\n",
       "</a>\n",
       "</g>\n",
       "</g>\n",
       "<!-- Skill&#45;&gt;Position.Skill -->\n",
       "<g id=\"edge3\" class=\"edge\">\n",
       "<title>Skill-&gt;Position.Skill</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" stroke-width=\"0.75\" stroke-opacity=\"0.250980\" d=\"M131.19,-55.15C143.71,-43.58 160.02,-28.51 170.12,-19.17\"/>\n",
       "</g>\n",
       "<!-- Position -->\n",
       "<g id=\"node5\" class=\"node\">\n",
       "<title>Position</title>\n",
       "<g id=\"a_node5\"><a xlink:title=\"position             \r",
       "------------------------------\r",
       "position_description \r",
       "\">\n",
       "<polygon fill=\"#00ff00\" fill-opacity=\"0.188235\" stroke=\"#00ff00\" stroke-opacity=\"0.188235\" points=\"209.5,-90 149.5,-90 149.5,-55 209.5,-55 209.5,-90\"/>\n",
       "<text text-anchor=\"start\" x=\"157.5\" y=\"-70.4\" font-family=\"arial\" text-decoration=\"underline\" font-size=\"12.00\" fill=\"darkgreen\">Position</text>\n",
       "</a>\n",
       "</g>\n",
       "</g>\n",
       "<!-- Position&#45;&gt;Position.Skill -->\n",
       "<g id=\"edge4\" class=\"edge\">\n",
       "<title>Position-&gt;Position.Skill</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" stroke-width=\"0.75\" stroke-opacity=\"0.250980\" d=\"M179.5,-54.83C179.5,-43.26 179.5,-28.32 179.5,-19.08\"/>\n",
       "</g>\n",
       "</g>\n",
       "</svg>"
      ],
      "text/plain": [
       "<datajoint.diagram.Diagram at 0x7fb9310552d0>"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dj.Diagram(schema)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3b48e0c7",
   "metadata": {},
   "source": [
    "# Query example"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "adf12884",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "    \n",
       "    <style type=\"text/css\">\n",
       "        .Relation{\n",
       "            border-collapse:collapse;\n",
       "        }\n",
       "        .Relation th{\n",
       "            background: #A0A0A0; color: #ffffff; padding:4px; border:#f0e0e0 1px solid;\n",
       "            font-weight: normal; font-family: monospace; font-size: 100%;\n",
       "        }\n",
       "        .Relation td{\n",
       "            padding:4px; border:#f0e0e0 1px solid; font-size:100%;\n",
       "        }\n",
       "        .Relation tr:nth-child(odd){\n",
       "            background: #ffffff;\n",
       "        }\n",
       "        .Relation tr:nth-child(even){\n",
       "            background: #f3f1ff;\n",
       "        }\n",
       "        /* Tooltip container */\n",
       "        .djtooltip {\n",
       "        }\n",
       "        /* Tooltip text */\n",
       "        .djtooltip .djtooltiptext {\n",
       "            visibility: hidden;\n",
       "            width: 120px;\n",
       "            background-color: black;\n",
       "            color: #fff;\n",
       "            text-align: center;\n",
       "            padding: 5px 0;\n",
       "            border-radius: 6px;\n",
       "            /* Position the tooltip text - see examples below! */\n",
       "            position: absolute;\n",
       "            z-index: 1;\n",
       "        }\n",
       "        #primary {\n",
       "            font-weight: bold;\n",
       "            color: black;\n",
       "        }\n",
       "        #nonprimary {\n",
       "            font-weight: normal;\n",
       "            color: white;\n",
       "        }\n",
       "\n",
       "        /* Show the tooltip text when you mouse over the tooltip container */\n",
       "        .djtooltip:hover .djtooltiptext {\n",
       "            visibility: visible;\n",
       "        }\n",
       "    </style>\n",
       "    \n",
       "    <b></b>\n",
       "        <div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "        <table border=\"1\" class=\"Relation\">\n",
       "            <thead> <tr style=\"text-align: right;\"> <th> <div class=\"djtooltip\">\n",
       "                            <p id=\"primary\">candidate_id</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div></th><th><div class=\"djtooltip\">\n",
       "                            <p id=\"nonprimary\">name</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div> </th> </tr> </thead>\n",
       "            <tbody> <tr> <td>145</td>\n",
       "<td>Jose Anderson</td></tr><tr><td>656</td>\n",
       "<td>Jonathan Peterson</td></tr><tr><td>830</td>\n",
       "<td>Tony Thompson</td></tr><tr><td>1239</td>\n",
       "<td>Rebecca Hatfield</td></tr><tr><td>1567</td>\n",
       "<td>Joseph Peters</td></tr><tr><td>1611</td>\n",
       "<td>Joshua Swanson</td></tr><tr><td>2300</td>\n",
       "<td>Glenda White</td></tr><tr><td>2462</td>\n",
       "<td>Tamara Thomas MD</td></tr><tr><td>2692</td>\n",
       "<td>Edward Johnston</td></tr><tr><td>3329</td>\n",
       "<td>Max Williams</td></tr><tr><td>3877</td>\n",
       "<td>Robert Ballard</td></tr><tr><td>4609</td>\n",
       "<td>Michael Haas</td> </tr> </tbody>\n",
       "        </table>\n",
       "        <p>...</p>\n",
       "        <p>Total: 39</p></div>\n",
       "        "
      ],
      "text/plain": [
       "*candidate_id  name          \n",
       "+------------+ +------------+\n",
       "145            Jose Anderson \n",
       "656            Jonathan Peter\n",
       "830            Tony Thompson \n",
       "1239           Rebecca Hatfie\n",
       "1567           Joseph Peters \n",
       "1611           Joshua Swanson\n",
       "2300           Glenda White  \n",
       "2462           Tamara Thomas \n",
       "2692           Edward Johnsto\n",
       "3329           Max Williams  \n",
       "3877           Robert Ballard\n",
       "4609           Michael Haas  \n",
       "   ...\n",
       " (Total: 39)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Candidate & (Candidate.Skill() & 'skill = \"Java\"') \\\n",
    "& (Candidate.Skill() & 'skill = \"Python\"')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "a3f7525a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "    \n",
       "    <style type=\"text/css\">\n",
       "        .Relation{\n",
       "            border-collapse:collapse;\n",
       "        }\n",
       "        .Relation th{\n",
       "            background: #A0A0A0; color: #ffffff; padding:4px; border:#f0e0e0 1px solid;\n",
       "            font-weight: normal; font-family: monospace; font-size: 100%;\n",
       "        }\n",
       "        .Relation td{\n",
       "            padding:4px; border:#f0e0e0 1px solid; font-size:100%;\n",
       "        }\n",
       "        .Relation tr:nth-child(odd){\n",
       "            background: #ffffff;\n",
       "        }\n",
       "        .Relation tr:nth-child(even){\n",
       "            background: #f3f1ff;\n",
       "        }\n",
       "        /* Tooltip container */\n",
       "        .djtooltip {\n",
       "        }\n",
       "        /* Tooltip text */\n",
       "        .djtooltip .djtooltiptext {\n",
       "            visibility: hidden;\n",
       "            width: 120px;\n",
       "            background-color: black;\n",
       "            color: #fff;\n",
       "            text-align: center;\n",
       "            padding: 5px 0;\n",
       "            border-radius: 6px;\n",
       "            /* Position the tooltip text - see examples below! */\n",
       "            position: absolute;\n",
       "            z-index: 1;\n",
       "        }\n",
       "        #primary {\n",
       "            font-weight: bold;\n",
       "            color: black;\n",
       "        }\n",
       "        #nonprimary {\n",
       "            font-weight: normal;\n",
       "            color: white;\n",
       "        }\n",
       "\n",
       "        /* Show the tooltip text when you mouse over the tooltip container */\n",
       "        .djtooltip:hover .djtooltiptext {\n",
       "            visibility: visible;\n",
       "        }\n",
       "    </style>\n",
       "    \n",
       "    <b></b>\n",
       "        <div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "        <table border=\"1\" class=\"Relation\">\n",
       "            <thead> <tr style=\"text-align: right;\"> <th> <div class=\"djtooltip\">\n",
       "                            <p id=\"primary\">candidate_id</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div></th><th><div class=\"djtooltip\">\n",
       "                            <p id=\"nonprimary\">name</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div> </th> </tr> </thead>\n",
       "            <tbody> <tr> <td>23</td>\n",
       "<td>Courtney Oneill</td></tr><tr><td>41</td>\n",
       "<td>Tina Moss</td></tr><tr><td>42</td>\n",
       "<td>Kenneth Terry</td></tr><tr><td>143</td>\n",
       "<td>Dennis Robertson</td></tr><tr><td>145</td>\n",
       "<td>Jose Anderson</td></tr><tr><td>200</td>\n",
       "<td>Thomas Bolton</td></tr><tr><td>218</td>\n",
       "<td>Bryce Smith</td></tr><tr><td>234</td>\n",
       "<td>Jimmy Turner</td></tr><tr><td>281</td>\n",
       "<td>Jordan Mccarthy</td></tr><tr><td>464</td>\n",
       "<td>Dustin Thompson</td></tr><tr><td>526</td>\n",
       "<td>Mary Cook</td></tr><tr><td>541</td>\n",
       "<td>Linda Marshall</td> </tr> </tbody>\n",
       "        </table>\n",
       "        <p>...</p>\n",
       "        <p>Total: 122</p></div>\n",
       "        "
      ],
      "text/plain": [
       "*candidate_id  name          \n",
       "+------------+ +------------+\n",
       "23             Courtney Oneil\n",
       "41             Tina Moss     \n",
       "42             Kenneth Terry \n",
       "143            Dennis Roberts\n",
       "145            Jose Anderson \n",
       "200            Thomas Bolton \n",
       "218            Bryce Smith   \n",
       "234            Jimmy Turner  \n",
       "281            Jordan Mccarth\n",
       "464            Dustin Thompso\n",
       "526            Mary Cook     \n",
       "541            Linda Marshall\n",
       "   ...\n",
       " (Total: 122)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# All candidates who have the required skill\n",
    "\n",
    "cpp_experts = Candidate & (Candidate.Skill & {\"skill\": 'C++'})\n",
    "cpp_experts"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e2a4d0cf",
   "metadata": {},
   "source": [
    "### Show candidates who qualify for a given job\n",
    "\n",
    "Rephrase (position)\n",
    "\n",
    "> Show candidates who have *all* the skills required for job \"SWE1\"\n",
    "\n",
    "Rephrasing (a double negative)\n",
    "\n",
    "> Show candidates for whom there is no skill required for job \"SWE1\" that she does not possess.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "8b6a306b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "    \n",
       "    <style type=\"text/css\">\n",
       "        .Relation{\n",
       "            border-collapse:collapse;\n",
       "        }\n",
       "        .Relation th{\n",
       "            background: #A0A0A0; color: #ffffff; padding:4px; border:#f0e0e0 1px solid;\n",
       "            font-weight: normal; font-family: monospace; font-size: 100%;\n",
       "        }\n",
       "        .Relation td{\n",
       "            padding:4px; border:#f0e0e0 1px solid; font-size:100%;\n",
       "        }\n",
       "        .Relation tr:nth-child(odd){\n",
       "            background: #ffffff;\n",
       "        }\n",
       "        .Relation tr:nth-child(even){\n",
       "            background: #f3f1ff;\n",
       "        }\n",
       "        /* Tooltip container */\n",
       "        .djtooltip {\n",
       "        }\n",
       "        /* Tooltip text */\n",
       "        .djtooltip .djtooltiptext {\n",
       "            visibility: hidden;\n",
       "            width: 120px;\n",
       "            background-color: black;\n",
       "            color: #fff;\n",
       "            text-align: center;\n",
       "            padding: 5px 0;\n",
       "            border-radius: 6px;\n",
       "            /* Position the tooltip text - see examples below! */\n",
       "            position: absolute;\n",
       "            z-index: 1;\n",
       "        }\n",
       "        #primary {\n",
       "            font-weight: bold;\n",
       "            color: black;\n",
       "        }\n",
       "        #nonprimary {\n",
       "            font-weight: normal;\n",
       "            color: white;\n",
       "        }\n",
       "\n",
       "        /* Show the tooltip text when you mouse over the tooltip container */\n",
       "        .djtooltip:hover .djtooltiptext {\n",
       "            visibility: visible;\n",
       "        }\n",
       "    </style>\n",
       "    \n",
       "    <b></b>\n",
       "        <div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "        <table border=\"1\" class=\"Relation\">\n",
       "            <thead> <tr style=\"text-align: right;\"> <th> <div class=\"djtooltip\">\n",
       "                            <p id=\"primary\">position</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div></th><th><div class=\"djtooltip\">\n",
       "                            <p id=\"primary\">skill</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div> </th> </tr> </thead>\n",
       "            <tbody> <tr> <td>SWE1</td>\n",
       "<td>Java</td></tr><tr><td>SWE1</td>\n",
       "<td>JavaScript</td></tr><tr><td>SWE1</td>\n",
       "<td>Rust</td> </tr> </tbody>\n",
       "        </table>\n",
       "        \n",
       "        <p>Total: 3</p></div>\n",
       "        "
      ],
      "text/plain": [
       "*position    *skill        \n",
       "+----------+ +------------+\n",
       "SWE1         Java          \n",
       "SWE1         JavaScript    \n",
       "SWE1         Rust          \n",
       " (Total: 3)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# skills for SWE1\n",
    "required_skill = Position.Skill() & {'position': \"SWE1\"}\n",
    "required_skill"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "baf14393",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "    \n",
       "    <style type=\"text/css\">\n",
       "        .Relation{\n",
       "            border-collapse:collapse;\n",
       "        }\n",
       "        .Relation th{\n",
       "            background: #A0A0A0; color: #ffffff; padding:4px; border:#f0e0e0 1px solid;\n",
       "            font-weight: normal; font-family: monospace; font-size: 100%;\n",
       "        }\n",
       "        .Relation td{\n",
       "            padding:4px; border:#f0e0e0 1px solid; font-size:100%;\n",
       "        }\n",
       "        .Relation tr:nth-child(odd){\n",
       "            background: #ffffff;\n",
       "        }\n",
       "        .Relation tr:nth-child(even){\n",
       "            background: #f3f1ff;\n",
       "        }\n",
       "        /* Tooltip container */\n",
       "        .djtooltip {\n",
       "        }\n",
       "        /* Tooltip text */\n",
       "        .djtooltip .djtooltiptext {\n",
       "            visibility: hidden;\n",
       "            width: 120px;\n",
       "            background-color: black;\n",
       "            color: #fff;\n",
       "            text-align: center;\n",
       "            padding: 5px 0;\n",
       "            border-radius: 6px;\n",
       "            /* Position the tooltip text - see examples below! */\n",
       "            position: absolute;\n",
       "            z-index: 1;\n",
       "        }\n",
       "        #primary {\n",
       "            font-weight: bold;\n",
       "            color: black;\n",
       "        }\n",
       "        #nonprimary {\n",
       "            font-weight: normal;\n",
       "            color: white;\n",
       "        }\n",
       "\n",
       "        /* Show the tooltip text when you mouse over the tooltip container */\n",
       "        .djtooltip:hover .djtooltiptext {\n",
       "            visibility: visible;\n",
       "        }\n",
       "    </style>\n",
       "    \n",
       "    <b></b>\n",
       "        <div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "        <table border=\"1\" class=\"Relation\">\n",
       "            <thead> <tr style=\"text-align: right;\"> <th> <div class=\"djtooltip\">\n",
       "                            <p id=\"primary\">candidate_id</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div></th><th><div class=\"djtooltip\">\n",
       "                            <p id=\"nonprimary\">name</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div> </th> </tr> </thead>\n",
       "            <tbody> <tr> <td>41</td>\n",
       "<td>Tina Moss</td></tr><tr><td>143</td>\n",
       "<td>Dennis Robertson</td></tr><tr><td>1910</td>\n",
       "<td>Peter Martinez</td></tr><tr><td>1918</td>\n",
       "<td>Amanda Cox MD</td></tr><tr><td>2816</td>\n",
       "<td>Bryan Bishop</td></tr><tr><td>2934</td>\n",
       "<td>Tony May</td></tr><tr><td>3205</td>\n",
       "<td>Troy Bailey</td></tr><tr><td>4034</td>\n",
       "<td>Stephen Trujillo</td></tr><tr><td>4699</td>\n",
       "<td>Andrew Cole</td></tr><tr><td>5000</td>\n",
       "<td>Diana Day</td></tr><tr><td>5253</td>\n",
       "<td>William Lee</td></tr><tr><td>5532</td>\n",
       "<td>Cheryl Mccoy</td> </tr> </tbody>\n",
       "        </table>\n",
       "        <p>...</p>\n",
       "        <p>Total: 21</p></div>\n",
       "        "
      ],
      "text/plain": [
       "*candidate_id  name          \n",
       "+------------+ +------------+\n",
       "41             Tina Moss     \n",
       "143            Dennis Roberts\n",
       "1910           Peter Martinez\n",
       "1918           Amanda Cox MD \n",
       "2816           Bryan Bishop  \n",
       "2934           Tony May      \n",
       "3205           Troy Bailey   \n",
       "4034           Stephen Trujil\n",
       "4699           Andrew Cole   \n",
       "5000           Diana Day     \n",
       "5253           William Lee   \n",
       "5532           Cheryl Mccoy  \n",
       "   ...\n",
       " (Total: 21)"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Candidate - (Candidate().proj() * required_skill - Candidate.Skill())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "b9b7b37b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "    \n",
       "    <style type=\"text/css\">\n",
       "        .Relation{\n",
       "            border-collapse:collapse;\n",
       "        }\n",
       "        .Relation th{\n",
       "            background: #A0A0A0; color: #ffffff; padding:4px; border:#f0e0e0 1px solid;\n",
       "            font-weight: normal; font-family: monospace; font-size: 100%;\n",
       "        }\n",
       "        .Relation td{\n",
       "            padding:4px; border:#f0e0e0 1px solid; font-size:100%;\n",
       "        }\n",
       "        .Relation tr:nth-child(odd){\n",
       "            background: #ffffff;\n",
       "        }\n",
       "        .Relation tr:nth-child(even){\n",
       "            background: #f3f1ff;\n",
       "        }\n",
       "        /* Tooltip container */\n",
       "        .djtooltip {\n",
       "        }\n",
       "        /* Tooltip text */\n",
       "        .djtooltip .djtooltiptext {\n",
       "            visibility: hidden;\n",
       "            width: 120px;\n",
       "            background-color: black;\n",
       "            color: #fff;\n",
       "            text-align: center;\n",
       "            padding: 5px 0;\n",
       "            border-radius: 6px;\n",
       "            /* Position the tooltip text - see examples below! */\n",
       "            position: absolute;\n",
       "            z-index: 1;\n",
       "        }\n",
       "        #primary {\n",
       "            font-weight: bold;\n",
       "            color: black;\n",
       "        }\n",
       "        #nonprimary {\n",
       "            font-weight: normal;\n",
       "            color: white;\n",
       "        }\n",
       "\n",
       "        /* Show the tooltip text when you mouse over the tooltip container */\n",
       "        .djtooltip:hover .djtooltiptext {\n",
       "            visibility: visible;\n",
       "        }\n",
       "    </style>\n",
       "    \n",
       "    <b></b>\n",
       "        <div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "        <table border=\"1\" class=\"Relation\">\n",
       "            <thead> <tr style=\"text-align: right;\"> <th> <div class=\"djtooltip\">\n",
       "                            <p id=\"primary\">candidate_id</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div></th><th><div class=\"djtooltip\">\n",
       "                            <p id=\"primary\">skill</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div> </th> </tr> </thead>\n",
       "            <tbody> <tr> <td>23</td>\n",
       "<td>C++</td></tr><tr><td>23</td>\n",
       "<td>JavaScript</td></tr><tr><td>23</td>\n",
       "<td>Python</td></tr><tr><td>23</td>\n",
       "<td>SQL</td></tr><tr><td>41</td>\n",
       "<td>C++</td></tr><tr><td>41</td>\n",
       "<td>Java</td></tr><tr><td>41</td>\n",
       "<td>JavaScript</td></tr><tr><td>41</td>\n",
       "<td>R</td></tr><tr><td>41</td>\n",
       "<td>Rust</td></tr><tr><td>42</td>\n",
       "<td>C++</td></tr><tr><td>42</td>\n",
       "<td>Java</td></tr><tr><td>42</td>\n",
       "<td>R</td> </tr> </tbody>\n",
       "        </table>\n",
       "        <p>...</p>\n",
       "        <p>Total: 823</p></div>\n",
       "        "
      ],
      "text/plain": [
       "*candidate_id  *skill        \n",
       "+------------+ +------------+\n",
       "23             C++           \n",
       "23             JavaScript    \n",
       "23             Python        \n",
       "23             SQL           \n",
       "41             C++           \n",
       "41             Java          \n",
       "41             JavaScript    \n",
       "41             R             \n",
       "41             Rust          \n",
       "42             C++           \n",
       "42             Java          \n",
       "42             R             \n",
       "   ...\n",
       " (Total: 823)"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Candidate.Skill()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "61c1972b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "    \n",
       "    <style type=\"text/css\">\n",
       "        .Relation{\n",
       "            border-collapse:collapse;\n",
       "        }\n",
       "        .Relation th{\n",
       "            background: #A0A0A0; color: #ffffff; padding:4px; border:#f0e0e0 1px solid;\n",
       "            font-weight: normal; font-family: monospace; font-size: 100%;\n",
       "        }\n",
       "        .Relation td{\n",
       "            padding:4px; border:#f0e0e0 1px solid; font-size:100%;\n",
       "        }\n",
       "        .Relation tr:nth-child(odd){\n",
       "            background: #ffffff;\n",
       "        }\n",
       "        .Relation tr:nth-child(even){\n",
       "            background: #f3f1ff;\n",
       "        }\n",
       "        /* Tooltip container */\n",
       "        .djtooltip {\n",
       "        }\n",
       "        /* Tooltip text */\n",
       "        .djtooltip .djtooltiptext {\n",
       "            visibility: hidden;\n",
       "            width: 120px;\n",
       "            background-color: black;\n",
       "            color: #fff;\n",
       "            text-align: center;\n",
       "            padding: 5px 0;\n",
       "            border-radius: 6px;\n",
       "            /* Position the tooltip text - see examples below! */\n",
       "            position: absolute;\n",
       "            z-index: 1;\n",
       "        }\n",
       "        #primary {\n",
       "            font-weight: bold;\n",
       "            color: black;\n",
       "        }\n",
       "        #nonprimary {\n",
       "            font-weight: normal;\n",
       "            color: white;\n",
       "        }\n",
       "\n",
       "        /* Show the tooltip text when you mouse over the tooltip container */\n",
       "        .djtooltip:hover .djtooltiptext {\n",
       "            visibility: visible;\n",
       "        }\n",
       "    </style>\n",
       "    \n",
       "    \n",
       "        <div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "        <table border=\"1\" class=\"Relation\">\n",
       "            <thead> <tr style=\"text-align: right;\"> <th> <div class=\"djtooltip\">\n",
       "                            <p id=\"primary\">candidate_id</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div></th><th><div class=\"djtooltip\">\n",
       "                            <p id=\"primary\">position</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div></th><th><div class=\"djtooltip\">\n",
       "                            <p id=\"primary\">skill</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div> </th> </tr> </thead>\n",
       "            <tbody> <tr> <td>23</td>\n",
       "<td>SWE1</td>\n",
       "<td>Java</td></tr><tr><td>23</td>\n",
       "<td>SWE1</td>\n",
       "<td>Rust</td></tr><tr><td>42</td>\n",
       "<td>SWE1</td>\n",
       "<td>JavaScript</td></tr><tr><td>42</td>\n",
       "<td>SWE1</td>\n",
       "<td>Rust</td></tr><tr><td>58</td>\n",
       "<td>SWE1</td>\n",
       "<td>Java</td></tr><tr><td>145</td>\n",
       "<td>SWE1</td>\n",
       "<td>JavaScript</td></tr><tr><td>145</td>\n",
       "<td>SWE1</td>\n",
       "<td>Rust</td></tr><tr><td>153</td>\n",
       "<td>SWE1</td>\n",
       "<td>Java</td></tr><tr><td>153</td>\n",
       "<td>SWE1</td>\n",
       "<td>Rust</td></tr><tr><td>180</td>\n",
       "<td>SWE1</td>\n",
       "<td>JavaScript</td></tr><tr><td>180</td>\n",
       "<td>SWE1</td>\n",
       "<td>Rust</td></tr><tr><td>196</td>\n",
       "<td>SWE1</td>\n",
       "<td>Java</td> </tr> </tbody>\n",
       "        </table>\n",
       "        <p>...</p>\n",
       "        <p>Total: 539</p></div>\n",
       "        "
      ],
      "text/plain": [
       "*candidate_id  *position    *skill        \n",
       "+------------+ +----------+ +------------+\n",
       "23             SWE1         Java          \n",
       "23             SWE1         Rust          \n",
       "42             SWE1         JavaScript    \n",
       "42             SWE1         Rust          \n",
       "58             SWE1         Java          \n",
       "145            SWE1         JavaScript    \n",
       "145            SWE1         Rust          \n",
       "153            SWE1         Java          \n",
       "153            SWE1         Rust          \n",
       "180            SWE1         JavaScript    \n",
       "180            SWE1         Rust          \n",
       "196            SWE1         Java          \n",
       "   ...\n",
       " (Total: 539)"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# candidates' missing skills\n",
    "missing_skill = Candidate.proj() * required_skill - Candidate.Skill\n",
    "missing_skill"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "ed91aaeb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "    \n",
       "    <style type=\"text/css\">\n",
       "        .Relation{\n",
       "            border-collapse:collapse;\n",
       "        }\n",
       "        .Relation th{\n",
       "            background: #A0A0A0; color: #ffffff; padding:4px; border:#f0e0e0 1px solid;\n",
       "            font-weight: normal; font-family: monospace; font-size: 100%;\n",
       "        }\n",
       "        .Relation td{\n",
       "            padding:4px; border:#f0e0e0 1px solid; font-size:100%;\n",
       "        }\n",
       "        .Relation tr:nth-child(odd){\n",
       "            background: #ffffff;\n",
       "        }\n",
       "        .Relation tr:nth-child(even){\n",
       "            background: #f3f1ff;\n",
       "        }\n",
       "        /* Tooltip container */\n",
       "        .djtooltip {\n",
       "        }\n",
       "        /* Tooltip text */\n",
       "        .djtooltip .djtooltiptext {\n",
       "            visibility: hidden;\n",
       "            width: 120px;\n",
       "            background-color: black;\n",
       "            color: #fff;\n",
       "            text-align: center;\n",
       "            padding: 5px 0;\n",
       "            border-radius: 6px;\n",
       "            /* Position the tooltip text - see examples below! */\n",
       "            position: absolute;\n",
       "            z-index: 1;\n",
       "        }\n",
       "        #primary {\n",
       "            font-weight: bold;\n",
       "            color: black;\n",
       "        }\n",
       "        #nonprimary {\n",
       "            font-weight: normal;\n",
       "            color: white;\n",
       "        }\n",
       "\n",
       "        /* Show the tooltip text when you mouse over the tooltip container */\n",
       "        .djtooltip:hover .djtooltiptext {\n",
       "            visibility: visible;\n",
       "        }\n",
       "    </style>\n",
       "    \n",
       "    <b></b>\n",
       "        <div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "        <table border=\"1\" class=\"Relation\">\n",
       "            <thead> <tr style=\"text-align: right;\"> <th> <div class=\"djtooltip\">\n",
       "                            <p id=\"primary\">candidate_id</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div></th><th><div class=\"djtooltip\">\n",
       "                            <p id=\"nonprimary\">name</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div> </th> </tr> </thead>\n",
       "            <tbody> <tr> <td>41</td>\n",
       "<td>Tina Moss</td></tr><tr><td>143</td>\n",
       "<td>Dennis Robertson</td></tr><tr><td>1910</td>\n",
       "<td>Peter Martinez</td></tr><tr><td>1918</td>\n",
       "<td>Amanda Cox MD</td></tr><tr><td>2816</td>\n",
       "<td>Bryan Bishop</td></tr><tr><td>2934</td>\n",
       "<td>Tony May</td></tr><tr><td>3205</td>\n",
       "<td>Troy Bailey</td></tr><tr><td>4034</td>\n",
       "<td>Stephen Trujillo</td></tr><tr><td>4699</td>\n",
       "<td>Andrew Cole</td></tr><tr><td>5000</td>\n",
       "<td>Diana Day</td></tr><tr><td>5253</td>\n",
       "<td>William Lee</td></tr><tr><td>5532</td>\n",
       "<td>Cheryl Mccoy</td> </tr> </tbody>\n",
       "        </table>\n",
       "        <p>...</p>\n",
       "        <p>Total: 21</p></div>\n",
       "        "
      ],
      "text/plain": [
       "*candidate_id  name          \n",
       "+------------+ +------------+\n",
       "41             Tina Moss     \n",
       "143            Dennis Roberts\n",
       "1910           Peter Martinez\n",
       "1918           Amanda Cox MD \n",
       "2816           Bryan Bishop  \n",
       "2934           Tony May      \n",
       "3205           Troy Bailey   \n",
       "4034           Stephen Trujil\n",
       "4699           Andrew Cole   \n",
       "5000           Diana Day     \n",
       "5253           William Lee   \n",
       "5532           Cheryl Mccoy  \n",
       "   ...\n",
       " (Total: 21)"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# qualified candidates\n",
    "qualified_candidate = Candidate - missing_skill\n",
    "qualified_candidate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "71348a24",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "    \n",
       "    <style type=\"text/css\">\n",
       "        .Relation{\n",
       "            border-collapse:collapse;\n",
       "        }\n",
       "        .Relation th{\n",
       "            background: #A0A0A0; color: #ffffff; padding:4px; border:#f0e0e0 1px solid;\n",
       "            font-weight: normal; font-family: monospace; font-size: 100%;\n",
       "        }\n",
       "        .Relation td{\n",
       "            padding:4px; border:#f0e0e0 1px solid; font-size:100%;\n",
       "        }\n",
       "        .Relation tr:nth-child(odd){\n",
       "            background: #ffffff;\n",
       "        }\n",
       "        .Relation tr:nth-child(even){\n",
       "            background: #f3f1ff;\n",
       "        }\n",
       "        /* Tooltip container */\n",
       "        .djtooltip {\n",
       "        }\n",
       "        /* Tooltip text */\n",
       "        .djtooltip .djtooltiptext {\n",
       "            visibility: hidden;\n",
       "            width: 120px;\n",
       "            background-color: black;\n",
       "            color: #fff;\n",
       "            text-align: center;\n",
       "            padding: 5px 0;\n",
       "            border-radius: 6px;\n",
       "            /* Position the tooltip text - see examples below! */\n",
       "            position: absolute;\n",
       "            z-index: 1;\n",
       "        }\n",
       "        #primary {\n",
       "            font-weight: bold;\n",
       "            color: black;\n",
       "        }\n",
       "        #nonprimary {\n",
       "            font-weight: normal;\n",
       "            color: white;\n",
       "        }\n",
       "\n",
       "        /* Show the tooltip text when you mouse over the tooltip container */\n",
       "        .djtooltip:hover .djtooltiptext {\n",
       "            visibility: visible;\n",
       "        }\n",
       "    </style>\n",
       "    \n",
       "    <b></b>\n",
       "        <div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "        <table border=\"1\" class=\"Relation\">\n",
       "            <thead> <tr style=\"text-align: right;\"> <th> <div class=\"djtooltip\">\n",
       "                            <p id=\"primary\">candidate_id</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div></th><th><div class=\"djtooltip\">\n",
       "                            <p id=\"nonprimary\">name</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div> </th> </tr> </thead>\n",
       "            <tbody> <tr> <td>41</td>\n",
       "<td>Tina Moss</td></tr><tr><td>143</td>\n",
       "<td>Dennis Robertson</td></tr><tr><td>1910</td>\n",
       "<td>Peter Martinez</td></tr><tr><td>1918</td>\n",
       "<td>Amanda Cox MD</td></tr><tr><td>2816</td>\n",
       "<td>Bryan Bishop</td></tr><tr><td>2934</td>\n",
       "<td>Tony May</td></tr><tr><td>3205</td>\n",
       "<td>Troy Bailey</td></tr><tr><td>4034</td>\n",
       "<td>Stephen Trujillo</td></tr><tr><td>4699</td>\n",
       "<td>Andrew Cole</td></tr><tr><td>5000</td>\n",
       "<td>Diana Day</td></tr><tr><td>5253</td>\n",
       "<td>William Lee</td></tr><tr><td>5532</td>\n",
       "<td>Cheryl Mccoy</td> </tr> </tbody>\n",
       "        </table>\n",
       "        <p>...</p>\n",
       "        <p>Total: 21</p></div>\n",
       "        "
      ],
      "text/plain": [
       "*candidate_id  name          \n",
       "+------------+ +------------+\n",
       "41             Tina Moss     \n",
       "143            Dennis Roberts\n",
       "1910           Peter Martinez\n",
       "1918           Amanda Cox MD \n",
       "2816           Bryan Bishop  \n",
       "2934           Tony May      \n",
       "3205           Troy Bailey   \n",
       "4034           Stephen Trujil\n",
       "4699           Andrew Cole   \n",
       "5000           Diana Day     \n",
       "5253           William Lee   \n",
       "5532           Cheryl Mccoy  \n",
       "   ...\n",
       " (Total: 21)"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Candidates who qualify for job SWE1\n",
    "\n",
    "Candidate - (\n",
    "    (Candidate.proj() * Position.Skill & {'position': \"SWE1\"})\n",
    "    - Candidate.Skill)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "310fd6ff",
   "metadata": {},
   "source": [
    "## SQL equlvalent\n",
    "\n",
    "Literal translation of the above DataJoint query\n",
    "```sql\n",
    "USE dimitri_hiring;\n",
    "\n",
    "SELECT candidate_id, name \n",
    "FROM candidate \n",
    "WHERE candidate_id NOT IN (\n",
    "    SELECT candidate_id\n",
    "    FROM candidate NATURAL JOIN position__skill\n",
    "    WHERE position = \"SWE1\" AND (candidate_id, skill) NOT IN (\n",
    "        SELECT candidate_id, skill FROM candidate__skill))\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "021ea3d3",
   "metadata": {},
   "source": [
    "## Using Aggregation\n",
    "\n",
    "Another method is to simply compare the number of matching skils for each candidate to the total number of matching skills, using aggregation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "8db64f01",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "    \n",
       "    <style type=\"text/css\">\n",
       "        .Relation{\n",
       "            border-collapse:collapse;\n",
       "        }\n",
       "        .Relation th{\n",
       "            background: #A0A0A0; color: #ffffff; padding:4px; border:#f0e0e0 1px solid;\n",
       "            font-weight: normal; font-family: monospace; font-size: 100%;\n",
       "        }\n",
       "        .Relation td{\n",
       "            padding:4px; border:#f0e0e0 1px solid; font-size:100%;\n",
       "        }\n",
       "        .Relation tr:nth-child(odd){\n",
       "            background: #ffffff;\n",
       "        }\n",
       "        .Relation tr:nth-child(even){\n",
       "            background: #f3f1ff;\n",
       "        }\n",
       "        /* Tooltip container */\n",
       "        .djtooltip {\n",
       "        }\n",
       "        /* Tooltip text */\n",
       "        .djtooltip .djtooltiptext {\n",
       "            visibility: hidden;\n",
       "            width: 120px;\n",
       "            background-color: black;\n",
       "            color: #fff;\n",
       "            text-align: center;\n",
       "            padding: 5px 0;\n",
       "            border-radius: 6px;\n",
       "            /* Position the tooltip text - see examples below! */\n",
       "            position: absolute;\n",
       "            z-index: 1;\n",
       "        }\n",
       "        #primary {\n",
       "            font-weight: bold;\n",
       "            color: black;\n",
       "        }\n",
       "        #nonprimary {\n",
       "            font-weight: normal;\n",
       "            color: white;\n",
       "        }\n",
       "\n",
       "        /* Show the tooltip text when you mouse over the tooltip container */\n",
       "        .djtooltip:hover .djtooltiptext {\n",
       "            visibility: visible;\n",
       "        }\n",
       "    </style>\n",
       "    \n",
       "    <b></b>\n",
       "        <div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "        <table border=\"1\" class=\"Relation\">\n",
       "            <thead> <tr style=\"text-align: right;\"> <th> <div class=\"djtooltip\">\n",
       "                            <p id=\"primary\">candidate_id</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div></th><th><div class=\"djtooltip\">\n",
       "                            <p id=\"nonprimary\">name</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div> </th> </tr> </thead>\n",
       "            <tbody> <tr> <td>23</td>\n",
       "<td>Courtney Oneill</td></tr><tr><td>41</td>\n",
       "<td>Tina Moss</td></tr><tr><td>42</td>\n",
       "<td>Kenneth Terry</td></tr><tr><td>58</td>\n",
       "<td>Ronald Roberts</td></tr><tr><td>143</td>\n",
       "<td>Dennis Robertson</td></tr><tr><td>145</td>\n",
       "<td>Jose Anderson</td></tr><tr><td>153</td>\n",
       "<td>Lisa Atkins</td></tr><tr><td>180</td>\n",
       "<td>Jade Clark</td></tr><tr><td>196</td>\n",
       "<td>Jose Reynolds</td></tr><tr><td>200</td>\n",
       "<td>Thomas Bolton</td></tr><tr><td>218</td>\n",
       "<td>Bryce Smith</td></tr><tr><td>234</td>\n",
       "<td>Jimmy Turner</td> </tr> </tbody>\n",
       "        </table>\n",
       "        <p>...</p>\n",
       "        <p>Total: 297</p></div>\n",
       "        "
      ],
      "text/plain": [
       "*candidate_id  name          \n",
       "+------------+ +------------+\n",
       "23             Courtney Oneil\n",
       "41             Tina Moss     \n",
       "42             Kenneth Terry \n",
       "58             Ronald Roberts\n",
       "143            Dennis Roberts\n",
       "145            Jose Anderson \n",
       "153            Lisa Atkins   \n",
       "180            Jade Clark    \n",
       "196            Jose Reynolds \n",
       "200            Thomas Bolton \n",
       "218            Bryce Smith   \n",
       "234            Jimmy Turner  \n",
       "   ...\n",
       " (Total: 297)"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Candidate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "bf2164f4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "    \n",
       "    <style type=\"text/css\">\n",
       "        .Relation{\n",
       "            border-collapse:collapse;\n",
       "        }\n",
       "        .Relation th{\n",
       "            background: #A0A0A0; color: #ffffff; padding:4px; border:#f0e0e0 1px solid;\n",
       "            font-weight: normal; font-family: monospace; font-size: 100%;\n",
       "        }\n",
       "        .Relation td{\n",
       "            padding:4px; border:#f0e0e0 1px solid; font-size:100%;\n",
       "        }\n",
       "        .Relation tr:nth-child(odd){\n",
       "            background: #ffffff;\n",
       "        }\n",
       "        .Relation tr:nth-child(even){\n",
       "            background: #f3f1ff;\n",
       "        }\n",
       "        /* Tooltip container */\n",
       "        .djtooltip {\n",
       "        }\n",
       "        /* Tooltip text */\n",
       "        .djtooltip .djtooltiptext {\n",
       "            visibility: hidden;\n",
       "            width: 120px;\n",
       "            background-color: black;\n",
       "            color: #fff;\n",
       "            text-align: center;\n",
       "            padding: 5px 0;\n",
       "            border-radius: 6px;\n",
       "            /* Position the tooltip text - see examples below! */\n",
       "            position: absolute;\n",
       "            z-index: 1;\n",
       "        }\n",
       "        #primary {\n",
       "            font-weight: bold;\n",
       "            color: black;\n",
       "        }\n",
       "        #nonprimary {\n",
       "            font-weight: normal;\n",
       "            color: white;\n",
       "        }\n",
       "\n",
       "        /* Show the tooltip text when you mouse over the tooltip container */\n",
       "        .djtooltip:hover .djtooltiptext {\n",
       "            visibility: visible;\n",
       "        }\n",
       "    </style>\n",
       "    \n",
       "    \n",
       "        <div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "        <table border=\"1\" class=\"Relation\">\n",
       "            <thead> <tr style=\"text-align: right;\"> <th> <div class=\"djtooltip\">\n",
       "                            <p id=\"primary\">candidate_id</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div></th><th><div class=\"djtooltip\">\n",
       "                            <p id=\"primary\">skill</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div></th><th><div class=\"djtooltip\">\n",
       "                            <p id=\"primary\">position</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div> </th> </tr> </thead>\n",
       "            <tbody> <tr> <td>41</td>\n",
       "<td>Java</td>\n",
       "<td>SWE1</td></tr><tr><td>42</td>\n",
       "<td>Java</td>\n",
       "<td>SWE1</td></tr><tr><td>143</td>\n",
       "<td>Java</td>\n",
       "<td>SWE1</td></tr><tr><td>145</td>\n",
       "<td>Java</td>\n",
       "<td>SWE1</td></tr><tr><td>180</td>\n",
       "<td>Java</td>\n",
       "<td>SWE1</td></tr><tr><td>464</td>\n",
       "<td>Java</td>\n",
       "<td>SWE1</td></tr><tr><td>502</td>\n",
       "<td>Java</td>\n",
       "<td>SWE1</td></tr><tr><td>641</td>\n",
       "<td>Java</td>\n",
       "<td>SWE1</td></tr><tr><td>656</td>\n",
       "<td>Java</td>\n",
       "<td>SWE1</td></tr><tr><td>749</td>\n",
       "<td>Java</td>\n",
       "<td>SWE1</td></tr><tr><td>771</td>\n",
       "<td>Java</td>\n",
       "<td>SWE1</td></tr><tr><td>830</td>\n",
       "<td>Java</td>\n",
       "<td>SWE1</td> </tr> </tbody>\n",
       "        </table>\n",
       "        <p>...</p>\n",
       "        <p>Total: 352</p></div>\n",
       "        "
      ],
      "text/plain": [
       "*candidate_id  *skill    *position   \n",
       "+------------+ +-------+ +----------+\n",
       "41             Java      SWE1        \n",
       "42             Java      SWE1        \n",
       "143            Java      SWE1        \n",
       "145            Java      SWE1        \n",
       "180            Java      SWE1        \n",
       "464            Java      SWE1        \n",
       "502            Java      SWE1        \n",
       "641            Java      SWE1        \n",
       "656            Java      SWE1        \n",
       "749            Java      SWE1        \n",
       "771            Java      SWE1        \n",
       "830            Java      SWE1        \n",
       "   ...\n",
       " (Total: 352)"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Candidate.Skill() * Position.Skill() & {'position': 'SWE1'}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "000a574b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "    \n",
       "    <style type=\"text/css\">\n",
       "        .Relation{\n",
       "            border-collapse:collapse;\n",
       "        }\n",
       "        .Relation th{\n",
       "            background: #A0A0A0; color: #ffffff; padding:4px; border:#f0e0e0 1px solid;\n",
       "            font-weight: normal; font-family: monospace; font-size: 100%;\n",
       "        }\n",
       "        .Relation td{\n",
       "            padding:4px; border:#f0e0e0 1px solid; font-size:100%;\n",
       "        }\n",
       "        .Relation tr:nth-child(odd){\n",
       "            background: #ffffff;\n",
       "        }\n",
       "        .Relation tr:nth-child(even){\n",
       "            background: #f3f1ff;\n",
       "        }\n",
       "        /* Tooltip container */\n",
       "        .djtooltip {\n",
       "        }\n",
       "        /* Tooltip text */\n",
       "        .djtooltip .djtooltiptext {\n",
       "            visibility: hidden;\n",
       "            width: 120px;\n",
       "            background-color: black;\n",
       "            color: #fff;\n",
       "            text-align: center;\n",
       "            padding: 5px 0;\n",
       "            border-radius: 6px;\n",
       "            /* Position the tooltip text - see examples below! */\n",
       "            position: absolute;\n",
       "            z-index: 1;\n",
       "        }\n",
       "        #primary {\n",
       "            font-weight: bold;\n",
       "            color: black;\n",
       "        }\n",
       "        #nonprimary {\n",
       "            font-weight: normal;\n",
       "            color: white;\n",
       "        }\n",
       "\n",
       "        /* Show the tooltip text when you mouse over the tooltip container */\n",
       "        .djtooltip:hover .djtooltiptext {\n",
       "            visibility: visible;\n",
       "        }\n",
       "    </style>\n",
       "    \n",
       "    \n",
       "        <div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "        <table border=\"1\" class=\"Relation\">\n",
       "            <thead> <tr style=\"text-align: right;\"> <th> <div class=\"djtooltip\">\n",
       "                            <p id=\"primary\">candidate_id</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div></th><th><div class=\"djtooltip\">\n",
       "                            <p id=\"nonprimary\">n</p>\n",
       "                            <span class=\"djtooltiptext\">calculated attribute</span>\n",
       "                        </div> </th> </tr> </thead>\n",
       "            <tbody> <tr> <td>41</td>\n",
       "<td>3</td></tr><tr><td>143</td>\n",
       "<td>3</td></tr><tr><td>1910</td>\n",
       "<td>3</td></tr><tr><td>1918</td>\n",
       "<td>3</td></tr><tr><td>2816</td>\n",
       "<td>3</td></tr><tr><td>2934</td>\n",
       "<td>3</td></tr><tr><td>3205</td>\n",
       "<td>3</td></tr><tr><td>4034</td>\n",
       "<td>3</td></tr><tr><td>4699</td>\n",
       "<td>3</td></tr><tr><td>5000</td>\n",
       "<td>3</td></tr><tr><td>5253</td>\n",
       "<td>3</td></tr><tr><td>5532</td>\n",
       "<td>3</td> </tr> </tbody>\n",
       "        </table>\n",
       "        <p>...</p>\n",
       "        <p>Total: 21</p></div>\n",
       "        "
      ],
      "text/plain": [
       "*candidate_id  n    \n",
       "+------------+ +---+\n",
       "41             3    \n",
       "143            3    \n",
       "1910           3    \n",
       "1918           3    \n",
       "2816           3    \n",
       "2934           3    \n",
       "3205           3    \n",
       "4034           3    \n",
       "4699           3    \n",
       "5000           3    \n",
       "5253           3    \n",
       "5532           3    \n",
       "   ...\n",
       " (Total: 21)"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "qualified = Candidate.aggr(\n",
    "    Candidate.Skill * Position.Skill & {'position': \"SWE1\"}, \n",
    "     n='count(skill)') & 'n=3'\n",
    "\n",
    "qualified"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0079447e",
   "metadata": {},
   "source": [
    "## SQL equivalent\n",
    "\n",
    "```sql\n",
    "SELECT candidate_id, name \n",
    "FROM candidate NATURAL JOIN \n",
    "     candidate__skill NATURAL JOIN position__skill\n",
    "WHERE position=\"SWE1\" \n",
    "GROUP BY candidate_id \n",
    "HAVING n=3\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4497c714",
   "metadata": {},
   "source": [
    "# Example from `sales`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "101ca681",
   "metadata": {},
   "outputs": [],
   "source": [
    "sales = dj.create_virtual_module('sales', 'shared_sales')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "284df349",
   "metadata": {},
   "source": [
    "## Show all Orders that contain the Products (1, 6, 11, 16, 36)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "d3fd8afd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/svg+xml": [
       "<svg xmlns=\"http://www.w3.org/2000/svg\" xmlns:xlink=\"http://www.w3.org/1999/xlink\" width=\"436pt\" height=\"185pt\" viewBox=\"0.00 0.00 436.00 185.00\">\n",
       "<g id=\"graph0\" class=\"graph\" transform=\"scale(1 1) rotate(0) translate(4 181)\">\n",
       "<polygon fill=\"white\" stroke=\"none\" points=\"-4,4 -4,-181 432,-181 432,4 -4,4\"/>\n",
       "<!-- sales.Category -->\n",
       "<g id=\"node1\" class=\"node\">\n",
       "<title>sales.Category</title>\n",
       "<g id=\"a_node1\"><a xlink:title=\"category_id=0        \r",
       "------------------------------\r",
       "category_description=&quot;&quot; \r",
       "\">\n",
       "<polygon fill=\"#00ff00\" fill-opacity=\"0.188235\" stroke=\"#00ff00\" stroke-opacity=\"0.188235\" points=\"335.5,-177 238.5,-177 238.5,-142 335.5,-142 335.5,-177\"/>\n",
       "<text text-anchor=\"start\" x=\"246.5\" y=\"-157.4\" font-family=\"arial\" text-decoration=\"underline\" font-size=\"12.00\" fill=\"darkgreen\">sales.Category</text>\n",
       "</a>\n",
       "</g>\n",
       "</g>\n",
       "<!-- sales.Product -->\n",
       "<g id=\"node5\" class=\"node\">\n",
       "<title>sales.Product</title>\n",
       "<g id=\"a_node5\"><a xlink:title=\"product_number       \r",
       "------------------------------\r",
       "product_name         \r",
       "product_description=&quot;&quot; \r",
       "retail_price         \r",
       "quantity_on_hand     \r",
       "→ sales.Category\r",
       "\">\n",
       "<polygon fill=\"#00ff00\" fill-opacity=\"0.188235\" stroke=\"#00ff00\" stroke-opacity=\"0.188235\" points=\"323.5,-106 234.5,-106 234.5,-71 323.5,-71 323.5,-106\"/>\n",
       "<text text-anchor=\"start\" x=\"242.5\" y=\"-86.4\" font-family=\"arial\" text-decoration=\"underline\" font-size=\"12.00\" fill=\"darkgreen\">sales.Product</text>\n",
       "</a>\n",
       "</g>\n",
       "</g>\n",
       "<!-- sales.Category&#45;&gt;sales.Product -->\n",
       "<g id=\"edge1\" class=\"edge\">\n",
       "<title>sales.Category-&gt;sales.Product</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" stroke-width=\"0.75\" stroke-dasharray=\"5,2\" stroke-opacity=\"0.250980\" d=\"M285.06,-141.8C283.81,-130.95 282.17,-116.87 280.92,-106.05\"/>\n",
       "</g>\n",
       "<!-- sales.Employee -->\n",
       "<g id=\"node2\" class=\"node\">\n",
       "<title>sales.Employee</title>\n",
       "<g id=\"a_node2\"><a xlink:title=\"employee_id          \r",
       "------------------------------\r",
       "employee_first_name  \r",
       "employee_last_name   \r",
       "employee_street_address=&quot;&quot; \r",
       "employee_city=&quot;&quot;     \r",
       "employee_state=&quot;&quot;    \r",
       "employee_zipcode=&quot;&quot;  \r",
       "employee_area_code=null \r",
       "employee_phone=&quot;&quot;    \r",
       "employee_birthdate=null \r",
       "INDEX (employee_area_code)\r",
       "INDEX (employee_zipcode)\r",
       "\">\n",
       "<polygon fill=\"#00ff00\" fill-opacity=\"0.188235\" stroke=\"#00ff00\" stroke-opacity=\"0.188235\" points=\"102,-177 0,-177 0,-142 102,-142 102,-177\"/>\n",
       "<text text-anchor=\"start\" x=\"8\" y=\"-157.4\" font-family=\"arial\" text-decoration=\"underline\" font-size=\"12.00\" fill=\"darkgreen\">sales.Employee</text>\n",
       "</a>\n",
       "</g>\n",
       "</g>\n",
       "<!-- sales.Order -->\n",
       "<g id=\"node3\" class=\"node\">\n",
       "<title>sales.Order</title>\n",
       "<g id=\"a_node3\"><a xlink:title=\"order_number         \r",
       "------------------------------\r",
       "order_date           \r",
       "ship_date            \r",
       "→ sales.Customer\r",
       "→ sales.Employee\r",
       "\">\n",
       "<polygon fill=\"#00ff00\" fill-opacity=\"0.188235\" stroke=\"#00ff00\" stroke-opacity=\"0.188235\" points=\"209,-106 131,-106 131,-71 209,-71 209,-106\"/>\n",
       "<text text-anchor=\"start\" x=\"139\" y=\"-86.4\" font-family=\"arial\" text-decoration=\"underline\" font-size=\"12.00\" fill=\"darkgreen\">sales.Order</text>\n",
       "</a>\n",
       "</g>\n",
       "</g>\n",
       "<!-- sales.Employee&#45;&gt;sales.Order -->\n",
       "<g id=\"edge2\" class=\"edge\">\n",
       "<title>sales.Employee-&gt;sales.Order</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" stroke-width=\"0.75\" stroke-dasharray=\"5,2\" stroke-opacity=\"0.250980\" d=\"M79.5,-141.97C98.21,-131.13 122.58,-117 141.32,-106.13\"/>\n",
       "</g>\n",
       "<!-- sales.Order.Item -->\n",
       "<g id=\"node4\" class=\"node\">\n",
       "<title>sales.Order.Item</title>\n",
       "<g id=\"a_node4\"><a xlink:title=\"→ sales.Order\r",
       "→ sales.Product\r",
       "------------------------------\r",
       "quoted_price         \r",
       "quantity_ordered=0   \r",
       "\">\n",
       "<polygon fill=\"none\" stroke=\"none\" points=\"214.5,-27 125.5,-27 125.5,-8 214.5,-8 214.5,-27\"/>\n",
       "<text text-anchor=\"middle\" x=\"170\" y=\"-15\" font-family=\"arial\" font-size=\"10.00\">sales.Order.Item</text>\n",
       "</a>\n",
       "</g>\n",
       "</g>\n",
       "<!-- sales.Order&#45;&gt;sales.Order.Item -->\n",
       "<g id=\"edge3\" class=\"edge\">\n",
       "<title>sales.Order-&gt;sales.Order.Item</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" stroke-width=\"0.75\" stroke-opacity=\"0.250980\" d=\"M170,-70.8C170,-57.08 170,-38.19 170,-27.27\"/>\n",
       "</g>\n",
       "<!-- sales.Product&#45;&gt;sales.Order.Item -->\n",
       "<g id=\"edge4\" class=\"edge\">\n",
       "<title>sales.Product-&gt;sales.Order.Item</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" stroke-width=\"0.75\" stroke-opacity=\"0.250980\" d=\"M252.89,-70.97C231.1,-57.18 200.91,-38.06 183.62,-27.12\"/>\n",
       "</g>\n",
       "<!-- sales.ProductVendor -->\n",
       "<g id=\"node8\" class=\"node\">\n",
       "<title>sales.ProductVendor</title>\n",
       "<g id=\"a_node8\"><a xlink:title=\"→ sales.Product\r",
       "→ sales.Vendor\r",
       "------------------------------\r",
       "wholesale_price      \r",
       "days_to_deliver      \r",
       "\">\n",
       "<polygon fill=\"#00ff00\" fill-opacity=\"0.188235\" stroke=\"#00ff00\" stroke-opacity=\"0.188235\" points=\"401,-35 273,-35 273,0 401,0 401,-35\"/>\n",
       "<text text-anchor=\"middle\" x=\"337\" y=\"-14.4\" font-family=\"arial\" font-size=\"12.00\" fill=\"darkgreen\">sales.ProductVendor</text>\n",
       "</a>\n",
       "</g>\n",
       "</g>\n",
       "<!-- sales.Product&#45;&gt;sales.ProductVendor -->\n",
       "<g id=\"edge5\" class=\"edge\">\n",
       "<title>sales.Product-&gt;sales.ProductVendor</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" stroke-width=\"0.75\" stroke-opacity=\"0.250980\" d=\"M293.04,-70.8C302.16,-59.95 314,-45.87 323.09,-35.05\"/>\n",
       "</g>\n",
       "<!-- sales.Vendor -->\n",
       "<g id=\"node6\" class=\"node\">\n",
       "<title>sales.Vendor</title>\n",
       "<g id=\"a_node6\"><a xlink:title=\"vendor_id            \r",
       "------------------------------\r",
       "vendor_name          \r",
       "vendor_street_address \r",
       "vendor_city          \r",
       "vendor_state         \r",
       "vendor_zipcode       \r",
       "vendor_phone         \r",
       "vendor_fax=&quot;&quot;        \r",
       "vendor_webpage=''    \r",
       "vendor_email=&quot;&quot;      \r",
       "\">\n",
       "<polygon fill=\"#00ff00\" fill-opacity=\"0.188235\" stroke=\"#00ff00\" stroke-opacity=\"0.188235\" points=\"428,-106 342,-106 342,-71 428,-71 428,-106\"/>\n",
       "<text text-anchor=\"start\" x=\"350\" y=\"-86.4\" font-family=\"arial\" text-decoration=\"underline\" font-size=\"12.00\" fill=\"darkgreen\">sales.Vendor</text>\n",
       "</a>\n",
       "</g>\n",
       "</g>\n",
       "<!-- sales.Vendor&#45;&gt;sales.ProductVendor -->\n",
       "<g id=\"edge6\" class=\"edge\">\n",
       "<title>sales.Vendor-&gt;sales.ProductVendor</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" stroke-width=\"0.75\" stroke-opacity=\"0.250980\" d=\"M373.38,-70.8C365.83,-59.95 356.04,-45.87 348.51,-35.05\"/>\n",
       "</g>\n",
       "<!-- sales.Customer -->\n",
       "<g id=\"node7\" class=\"node\">\n",
       "<title>sales.Customer</title>\n",
       "<g id=\"a_node7\"><a xlink:title=\"customer_id          \r",
       "------------------------------\r",
       "customer_first_name  \r",
       "customer_last_name   \r",
       "customer_street_address=&quot;&quot; \r",
       "customer_city=&quot;&quot;     \r",
       "customer_state=&quot;&quot;    \r",
       "customer_zipcode=&quot;&quot;  \r",
       "customer_area_code=null \r",
       "customer_phone=null  \r",
       "INDEX (customer_area_code)\r",
       "INDEX (customer_zipcode)\r",
       "\">\n",
       "<polygon fill=\"#00ff00\" fill-opacity=\"0.188235\" stroke=\"#00ff00\" stroke-opacity=\"0.188235\" points=\"220,-177 120,-177 120,-142 220,-142 220,-177\"/>\n",
       "<text text-anchor=\"start\" x=\"128\" y=\"-157.4\" font-family=\"arial\" text-decoration=\"underline\" font-size=\"12.00\" fill=\"darkgreen\">sales.Customer</text>\n",
       "</a>\n",
       "</g>\n",
       "</g>\n",
       "<!-- sales.Customer&#45;&gt;sales.Order -->\n",
       "<g id=\"edge7\" class=\"edge\">\n",
       "<title>sales.Customer-&gt;sales.Order</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" stroke-width=\"0.75\" stroke-dasharray=\"5,2\" stroke-opacity=\"0.250980\" d=\"M170,-141.8C170,-130.95 170,-116.87 170,-106.05\"/>\n",
       "</g>\n",
       "</g>\n",
       "</svg>"
      ],
      "text/plain": [
       "<datajoint.diagram.Diagram at 0x7fb9310e79a0>"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dj.Diagram(sales)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "8681e5ef",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "    \n",
       "    <style type=\"text/css\">\n",
       "        .Relation{\n",
       "            border-collapse:collapse;\n",
       "        }\n",
       "        .Relation th{\n",
       "            background: #A0A0A0; color: #ffffff; padding:4px; border:#f0e0e0 1px solid;\n",
       "            font-weight: normal; font-family: monospace; font-size: 100%;\n",
       "        }\n",
       "        .Relation td{\n",
       "            padding:4px; border:#f0e0e0 1px solid; font-size:100%;\n",
       "        }\n",
       "        .Relation tr:nth-child(odd){\n",
       "            background: #ffffff;\n",
       "        }\n",
       "        .Relation tr:nth-child(even){\n",
       "            background: #f3f1ff;\n",
       "        }\n",
       "        /* Tooltip container */\n",
       "        .djtooltip {\n",
       "        }\n",
       "        /* Tooltip text */\n",
       "        .djtooltip .djtooltiptext {\n",
       "            visibility: hidden;\n",
       "            width: 120px;\n",
       "            background-color: black;\n",
       "            color: #fff;\n",
       "            text-align: center;\n",
       "            padding: 5px 0;\n",
       "            border-radius: 6px;\n",
       "            /* Position the tooltip text - see examples below! */\n",
       "            position: absolute;\n",
       "            z-index: 1;\n",
       "        }\n",
       "        #primary {\n",
       "            font-weight: bold;\n",
       "            color: black;\n",
       "        }\n",
       "        #nonprimary {\n",
       "            font-weight: normal;\n",
       "            color: white;\n",
       "        }\n",
       "\n",
       "        /* Show the tooltip text when you mouse over the tooltip container */\n",
       "        .djtooltip:hover .djtooltiptext {\n",
       "            visibility: visible;\n",
       "        }\n",
       "    </style>\n",
       "    \n",
       "    <b></b>\n",
       "        <div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "        <table border=\"1\" class=\"Relation\">\n",
       "            <thead> <tr style=\"text-align: right;\"> <th> <div class=\"djtooltip\">\n",
       "                            <p id=\"primary\">order_number</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div></th><th><div class=\"djtooltip\">\n",
       "                            <p id=\"nonprimary\">order_date</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div></th><th><div class=\"djtooltip\">\n",
       "                            <p id=\"nonprimary\">ship_date</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div></th><th><div class=\"djtooltip\">\n",
       "                            <p id=\"nonprimary\">customer_id</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div></th><th><div class=\"djtooltip\">\n",
       "                            <p id=\"nonprimary\">employee_id</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div> </th> </tr> </thead>\n",
       "            <tbody> <tr> <td>1</td>\n",
       "<td>2017-09-02</td>\n",
       "<td>2017-09-05</td>\n",
       "<td>1018</td>\n",
       "<td>707</td></tr><tr><td>3</td>\n",
       "<td>2017-09-02</td>\n",
       "<td>2017-09-05</td>\n",
       "<td>1002</td>\n",
       "<td>707</td></tr><tr><td>4</td>\n",
       "<td>2017-09-02</td>\n",
       "<td>2017-09-04</td>\n",
       "<td>1009</td>\n",
       "<td>703</td></tr><tr><td>5</td>\n",
       "<td>2017-09-02</td>\n",
       "<td>2017-09-02</td>\n",
       "<td>1024</td>\n",
       "<td>708</td></tr><tr><td>8</td>\n",
       "<td>2017-09-02</td>\n",
       "<td>2017-09-02</td>\n",
       "<td>1003</td>\n",
       "<td>703</td></tr><tr><td>10</td>\n",
       "<td>2017-09-02</td>\n",
       "<td>2017-09-05</td>\n",
       "<td>1012</td>\n",
       "<td>701</td></tr><tr><td>11</td>\n",
       "<td>2017-09-03</td>\n",
       "<td>2017-09-05</td>\n",
       "<td>1020</td>\n",
       "<td>706</td></tr><tr><td>13</td>\n",
       "<td>2017-09-03</td>\n",
       "<td>2017-09-03</td>\n",
       "<td>1024</td>\n",
       "<td>704</td></tr><tr><td>14</td>\n",
       "<td>2017-09-03</td>\n",
       "<td>2017-09-04</td>\n",
       "<td>1013</td>\n",
       "<td>704</td></tr><tr><td>15</td>\n",
       "<td>2017-09-03</td>\n",
       "<td>2017-09-07</td>\n",
       "<td>1004</td>\n",
       "<td>701</td></tr><tr><td>17</td>\n",
       "<td>2017-09-03</td>\n",
       "<td>2017-09-04</td>\n",
       "<td>1014</td>\n",
       "<td>702</td></tr><tr><td>18</td>\n",
       "<td>2017-09-03</td>\n",
       "<td>2017-09-04</td>\n",
       "<td>1016</td>\n",
       "<td>708</td> </tr> </tbody>\n",
       "        </table>\n",
       "        <p>...</p>\n",
       "        <p>Total: 628</p></div>\n",
       "        "
      ],
      "text/plain": [
       "*order_number  order_date     ship_date      customer_id    employee_id   \n",
       "+------------+ +------------+ +------------+ +------------+ +------------+\n",
       "1              2017-09-02     2017-09-05     1018           707           \n",
       "3              2017-09-02     2017-09-05     1002           707           \n",
       "4              2017-09-02     2017-09-04     1009           703           \n",
       "5              2017-09-02     2017-09-02     1024           708           \n",
       "8              2017-09-02     2017-09-02     1003           703           \n",
       "10             2017-09-02     2017-09-05     1012           701           \n",
       "11             2017-09-03     2017-09-05     1020           706           \n",
       "13             2017-09-03     2017-09-03     1024           704           \n",
       "14             2017-09-03     2017-09-04     1013           704           \n",
       "15             2017-09-03     2017-09-07     1004           701           \n",
       "17             2017-09-03     2017-09-04     1014           702           \n",
       "18             2017-09-03     2017-09-04     1016           708           \n",
       "   ...\n",
       " (Total: 628)"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sales.Order & (sales.Order.Item & 'product_number in (1, 6, 11, 16, 36)')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "b0da02b4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "    \n",
       "    <style type=\"text/css\">\n",
       "        .Relation{\n",
       "            border-collapse:collapse;\n",
       "        }\n",
       "        .Relation th{\n",
       "            background: #A0A0A0; color: #ffffff; padding:4px; border:#f0e0e0 1px solid;\n",
       "            font-weight: normal; font-family: monospace; font-size: 100%;\n",
       "        }\n",
       "        .Relation td{\n",
       "            padding:4px; border:#f0e0e0 1px solid; font-size:100%;\n",
       "        }\n",
       "        .Relation tr:nth-child(odd){\n",
       "            background: #ffffff;\n",
       "        }\n",
       "        .Relation tr:nth-child(even){\n",
       "            background: #f3f1ff;\n",
       "        }\n",
       "        /* Tooltip container */\n",
       "        .djtooltip {\n",
       "        }\n",
       "        /* Tooltip text */\n",
       "        .djtooltip .djtooltiptext {\n",
       "            visibility: hidden;\n",
       "            width: 120px;\n",
       "            background-color: black;\n",
       "            color: #fff;\n",
       "            text-align: center;\n",
       "            padding: 5px 0;\n",
       "            border-radius: 6px;\n",
       "            /* Position the tooltip text - see examples below! */\n",
       "            position: absolute;\n",
       "            z-index: 1;\n",
       "        }\n",
       "        #primary {\n",
       "            font-weight: bold;\n",
       "            color: black;\n",
       "        }\n",
       "        #nonprimary {\n",
       "            font-weight: normal;\n",
       "            color: white;\n",
       "        }\n",
       "\n",
       "        /* Show the tooltip text when you mouse over the tooltip container */\n",
       "        .djtooltip:hover .djtooltiptext {\n",
       "            visibility: visible;\n",
       "        }\n",
       "    </style>\n",
       "    \n",
       "    \n",
       "        <div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "        <table border=\"1\" class=\"Relation\">\n",
       "            <thead> <tr style=\"text-align: right;\"> <th> <div class=\"djtooltip\">\n",
       "                            <p id=\"primary\">order_number</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div></th><th><div class=\"djtooltip\">\n",
       "                            <p id=\"nonprimary\">n</p>\n",
       "                            <span class=\"djtooltiptext\">calculated attribute</span>\n",
       "                        </div> </th> </tr> </thead>\n",
       "            <tbody> <tr> <td>3</td>\n",
       "<td>5</td></tr><tr><td>11</td>\n",
       "<td>5</td></tr><tr><td>39</td>\n",
       "<td>5</td></tr><tr><td>91</td>\n",
       "<td>5</td></tr><tr><td>119</td>\n",
       "<td>5</td></tr><tr><td>148</td>\n",
       "<td>5</td></tr><tr><td>160</td>\n",
       "<td>5</td></tr><tr><td>175</td>\n",
       "<td>5</td></tr><tr><td>242</td>\n",
       "<td>5</td></tr><tr><td>329</td>\n",
       "<td>5</td></tr><tr><td>339</td>\n",
       "<td>5</td></tr><tr><td>373</td>\n",
       "<td>5</td> </tr> </tbody>\n",
       "        </table>\n",
       "        <p>...</p>\n",
       "        <p>Total: 29</p></div>\n",
       "        "
      ],
      "text/plain": [
       "*order_number  n    \n",
       "+------------+ +---+\n",
       "3              5    \n",
       "11             5    \n",
       "39             5    \n",
       "91             5    \n",
       "119            5    \n",
       "148            5    \n",
       "160            5    \n",
       "175            5    \n",
       "242            5    \n",
       "329            5    \n",
       "339            5    \n",
       "373            5    \n",
       "   ...\n",
       " (Total: 29)"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sales.Order.aggr(\n",
    "    sales.Order.Item & 'product_number in (1, 6, 11, 16, 36)', n='count(*)') & 'n=5'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "ad2e8874",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "    \n",
       "    <style type=\"text/css\">\n",
       "        .Relation{\n",
       "            border-collapse:collapse;\n",
       "        }\n",
       "        .Relation th{\n",
       "            background: #A0A0A0; color: #ffffff; padding:4px; border:#f0e0e0 1px solid;\n",
       "            font-weight: normal; font-family: monospace; font-size: 100%;\n",
       "        }\n",
       "        .Relation td{\n",
       "            padding:4px; border:#f0e0e0 1px solid; font-size:100%;\n",
       "        }\n",
       "        .Relation tr:nth-child(odd){\n",
       "            background: #ffffff;\n",
       "        }\n",
       "        .Relation tr:nth-child(even){\n",
       "            background: #f3f1ff;\n",
       "        }\n",
       "        /* Tooltip container */\n",
       "        .djtooltip {\n",
       "        }\n",
       "        /* Tooltip text */\n",
       "        .djtooltip .djtooltiptext {\n",
       "            visibility: hidden;\n",
       "            width: 120px;\n",
       "            background-color: black;\n",
       "            color: #fff;\n",
       "            text-align: center;\n",
       "            padding: 5px 0;\n",
       "            border-radius: 6px;\n",
       "            /* Position the tooltip text - see examples below! */\n",
       "            position: absolute;\n",
       "            z-index: 1;\n",
       "        }\n",
       "        #primary {\n",
       "            font-weight: bold;\n",
       "            color: black;\n",
       "        }\n",
       "        #nonprimary {\n",
       "            font-weight: normal;\n",
       "            color: white;\n",
       "        }\n",
       "\n",
       "        /* Show the tooltip text when you mouse over the tooltip container */\n",
       "        .djtooltip:hover .djtooltiptext {\n",
       "            visibility: visible;\n",
       "        }\n",
       "    </style>\n",
       "    \n",
       "    <b></b>\n",
       "        <div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "        <table border=\"1\" class=\"Relation\">\n",
       "            <thead> <tr style=\"text-align: right;\"> <th> <div class=\"djtooltip\">\n",
       "                            <p id=\"primary\">order_number</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div></th><th><div class=\"djtooltip\">\n",
       "                            <p id=\"nonprimary\">order_date</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div></th><th><div class=\"djtooltip\">\n",
       "                            <p id=\"nonprimary\">ship_date</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div></th><th><div class=\"djtooltip\">\n",
       "                            <p id=\"nonprimary\">customer_id</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div></th><th><div class=\"djtooltip\">\n",
       "                            <p id=\"nonprimary\">employee_id</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div> </th> </tr> </thead>\n",
       "            <tbody> <tr> <td>3</td>\n",
       "<td>2017-09-02</td>\n",
       "<td>2017-09-05</td>\n",
       "<td>1002</td>\n",
       "<td>707</td></tr><tr><td>11</td>\n",
       "<td>2017-09-03</td>\n",
       "<td>2017-09-05</td>\n",
       "<td>1020</td>\n",
       "<td>706</td></tr><tr><td>39</td>\n",
       "<td>2017-09-08</td>\n",
       "<td>2017-09-11</td>\n",
       "<td>1004</td>\n",
       "<td>704</td></tr><tr><td>91</td>\n",
       "<td>2017-09-15</td>\n",
       "<td>2017-09-19</td>\n",
       "<td>1010</td>\n",
       "<td>703</td></tr><tr><td>119</td>\n",
       "<td>2017-09-21</td>\n",
       "<td>2017-09-21</td>\n",
       "<td>1013</td>\n",
       "<td>701</td></tr><tr><td>148</td>\n",
       "<td>2017-09-27</td>\n",
       "<td>2017-10-01</td>\n",
       "<td>1027</td>\n",
       "<td>704</td></tr><tr><td>160</td>\n",
       "<td>2017-10-01</td>\n",
       "<td>2017-10-01</td>\n",
       "<td>1021</td>\n",
       "<td>706</td></tr><tr><td>175</td>\n",
       "<td>2017-10-05</td>\n",
       "<td>2017-10-08</td>\n",
       "<td>1013</td>\n",
       "<td>706</td></tr><tr><td>242</td>\n",
       "<td>2017-10-19</td>\n",
       "<td>2017-10-21</td>\n",
       "<td>1010</td>\n",
       "<td>705</td></tr><tr><td>329</td>\n",
       "<td>2017-11-04</td>\n",
       "<td>2017-11-06</td>\n",
       "<td>1004</td>\n",
       "<td>708</td></tr><tr><td>339</td>\n",
       "<td>2017-11-06</td>\n",
       "<td>2017-11-10</td>\n",
       "<td>1005</td>\n",
       "<td>705</td></tr><tr><td>373</td>\n",
       "<td>2017-11-13</td>\n",
       "<td>2017-11-16</td>\n",
       "<td>1014</td>\n",
       "<td>701</td> </tr> </tbody>\n",
       "        </table>\n",
       "        <p>...</p>\n",
       "        <p>Total: 29</p></div>\n",
       "        "
      ],
      "text/plain": [
       "*order_number  order_date     ship_date      customer_id    employee_id   \n",
       "+------------+ +------------+ +------------+ +------------+ +------------+\n",
       "3              2017-09-02     2017-09-05     1002           707           \n",
       "11             2017-09-03     2017-09-05     1020           706           \n",
       "39             2017-09-08     2017-09-11     1004           704           \n",
       "91             2017-09-15     2017-09-19     1010           703           \n",
       "119            2017-09-21     2017-09-21     1013           701           \n",
       "148            2017-09-27     2017-10-01     1027           704           \n",
       "160            2017-10-01     2017-10-01     1021           706           \n",
       "175            2017-10-05     2017-10-08     1013           706           \n",
       "242            2017-10-19     2017-10-21     1010           705           \n",
       "329            2017-11-04     2017-11-06     1004           708           \n",
       "339            2017-11-06     2017-11-10     1005           705           \n",
       "373            2017-11-13     2017-11-16     1014           701           \n",
       "   ...\n",
       " (Total: 29)"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Use the relational division pattern\n",
    "\n",
    "desired_items = sales.Order.proj() * (sales.Product & 'product_number in (1, 6, 11, 16, 36)')\n",
    "\n",
    "missing_items = desired_items - sales.Order.Item()\n",
    "\n",
    "# orders that don't omit any of the desired items\n",
    "sales.Order - missing_items"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1b4cb4a6",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "benv",
   "language": "python",
   "name": "benv"
  },
  "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.10.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}