completed_tutorials/01-DataJoint Basics.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Getting started with DataJoint"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that you have successfully connected to DataJoint (if not, please visit [Connecting to DataBase](00-ConnectingToDatabase.ipynb) first), let's dive into using DataJoint! In this notebook, we will:\n",
    "\n",
    "1. learn what a data pipeline is\n",
    "2. create our first simple data pipeline in DataJoint\n",
    "3. insert some data into the pipeline\n",
    "4. basic queries to flexible explore the data pipeline\n",
    "5. fetch the data from the pipeline\n",
    "6. delete entries from tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As always, let's start by importing the `datajoint` library."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import datajoint as dj"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# So... What is a data pipeline?"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you visit the [documentation for DataJoint](https://docs.datajoint.io/introduction/Data-pipelines.html), we define a data pipeline as follows:\n",
    "> A data pipeline is a sequence of steps (more generally a directed acyclic graph) with integrated storage at each step. These steps may be thought of as nodes in a graph.\n",
    "\n",
    "While this is an accurate description, it may not be the most intuitive definition. Put succinctly, a data pipeline is a listing or a \"map\" of various \"things\" that you work with in a project, with line connecting things to each other to indicate their dependencies. The \"things\" in a data pipeline tends to be the *nouns* you find when describing a project. The \"things\" may include anything from mouse, experimenter, equipment, to experiment session, trial, two-photon scans, electric activities, to receptive fields, neuronal spikes, to figures for a publication! A data pipeline gives you a framework to:\n",
    "\n",
    "1. define these \"things\" as tables in which you can store the information about them\n",
    "2. define the relationships (in particular the dependencies) between the \"things\"\n",
    "\n",
    "A data pipeline can then serve as a map that describes everything that goes on in your experiment, capturing what is collected, what is processed, and what is analyzed/computed. A well designed data pipeline not only let's you organize your data well, but can bring out logical clarity to your experiment, and may even bring about new insights by making how everything in your experiment relates together obvious.\n",
    "\n",
    "Let's go ahead and build together a pipeline from scratch to better understand what a data pipeline is all about."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Building our first pipeline: "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's build a pipeline to collect, store and process data and analysis for our hypothetical single electrode recording or calcium imaging recording in mice. To help us understand the project better, here is a brief description:"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> * Your lab houses many mice, and each mouse is identified by a unique ID. You also want to keep track of information about each mouse such as their date of birth, and gender.\n",
    "> * As a hard working neuroscientist, you perform experiments every day, sometimes working with more than one mouse in a day! However, on an any given day, a mouse undergoes at most one recording session.\n",
    "> * For each experimental session, you would like to record what mouse you worked with and when you performed the experiment. You would also like to keep track of other helpful information such as the experimental setup you worked on.  \n",
    "\n",
    "> * In a session of electrophysiology\n",
    ">> * you record electrical activity from a single neuron. You use recording equipment that produces separate data files for each neuron you recorded.\n",
    ">> * Neuron's activities are recorded as raw traces. Neuron's spikes needs to be detected for further analysis to be performed.\n",
    "> * In a session of calcium imaging\n",
    ">> * you scan a brain region containing a number of neurons. You use recording equipment that produces separate data files for each scan you performed.\n",
    ">> * you would like to segment the frames and get the regions of interest (ROIs), and save a mask for each ROI\n",
    ">> * finally you would like to extract the trace from each segmented ROI"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pipeline design starts by identifying **things** or **entities** in your project. Common entities includes experimental subjects (e.g. mouse), recording sessions, and two-photon scans."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's revisit the project description, this time paying special attention to **what** (e.g. nouns) about your experiment. Here I have highlighted some nouns in particular."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> * Your lab houses many **mice**, and each mouse is identified by a unique ID. You also want to keep track of information about each mouse such as their date of birth, and gender.\n",
    "> * As a hard working neuroscientist, you perform experiments every day, sometimes working with more than one mouse in a day! However, on an any given day, a mouse undergoes at most one recording session.\n",
    "> * For each **experimental session**, you would like to record what mouse you worked with and when you performed the experiment. You would also like to keep track of other helpful information such as the experimental setup you worked on.  \n",
    "\n",
    "> * In a session of electrophysiology\n",
    ">> * you record electrical activity from a **single neuron**. You use recording equipment that produces separate data files for each neuron you recorded.\n",
    ">> * Neuron's activities are recorded as raw traces. **Neuron's spikes** needs to be detected for further analysis to be performed.\n",
    "> * In a session of calcium imaging\n",
    ">> * you scan a brain region containing a number of neurons. You use recording equipment that produces separate data files for each **scan** you performed.\n",
    ">> * you would like to segment the frames and get the **regions of interest (ROIs)**, and save a mask for each ROI\n",
    ">> * finally you would like to extract the **trace** from each segmented ROI"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Just by going though the description, we can start to identify **things** or **entities** that we might want to store and represent in our data pipeline:\n",
    "\n",
    "* mouse\n",
    "* experimental session\n",
    "\n",
    "For ephys:\n",
    "\n",
    ">* neuron\n",
    ">* spikes\n",
    "\n",
    "For calcium imaging:\n",
    "\n",
    ">* scan\n",
    ">* regions of interest\n",
    ">* trace"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the current notebook, we will design the tables for mouse and experimental sessions, the rest of the pipeline will be designed in the subdirectory `electrophysioloy` and `calcium_imaging`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In DataJoint data pipeline, we represent these **entities** as **tables**. Different *kinds* of entities become distinct tables, and each row of the table is a single example (instance) of the category of entity. \n",
    "\n",
    "For example, if we have a `Mouse` table, then each row in the mouse table represents a single mouse!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When constructing such table, we need to figure out what it would take to **uniquely identify** each entry. Let's take the example of the **mouse** and think about what it would take to uniquely identify a mouse."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After some thought, we might conclude that each mouse can be uniquely identified by knowing its **mouse ID** - a unique ID number assigned to each mouse in the lab. The mouse ID is then a column in the table or an **attribute** that can be used to **uniquely identify** each mouse. Such attribute is called the **primary key** of the table.\n",
    "\n",
    "| mouse_id* |\n",
    "|:--------:|\n",
    "| 11234    |\n",
    "| 11432    |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once we have successfully identified the primary key of the table, we can now think about what other columns, or **non-primary key attributes** that we would want to include in the table. These are additional information **about each entry in the table that we want to store**."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For the case of mouse, what other information about the mouse you might want to store? Based on the project description, we would probably want to store information such as the mouse's **date of birth** and **gender**."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "| mouse_id* | dob        | sex |\n",
    "|:--------:|------------|--------|\n",
    "| 11234    | 2017-11-17 | M      |\n",
    "| 11432    | 2018-03-04 | F      |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we have an idea on how to represent information about mouse, let's create the table using **DataJoint**!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create a schema - house for your tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Every table lives inside a schema - a logical collection of one or more tables in your pipeline. Your final pipeline may consists of many tables spread across one or more schemas. Let's go ahead and create the first schema to house our table."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We create the schema using `dj.schema()` function, passing in the name of the schema. For this workshop, you are given the database privilege to use any schema name.  Let's create a schema called `tutorial`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connecting shan@workshop-db.datajoint.io:3306\n"
     ]
    }
   ],
   "source": [
    "schema = dj.schema('tutorial')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have a schema to place our table into, let's go ahead and define our first table!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating your first table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In DataJoint, you define each table as a class, and provide the table definition (e.g. attribute definitions) as the `definition` static string property. The class will inherit from the `dj.Manual` class provided by DataJoint (more on this later)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "@schema\n",
    "class Mouse(dj.Manual):\n",
    "    definition = \"\"\"\n",
    "    # Experimental animals\n",
    "    mouse_id             : int                          # Unique animal ID\n",
    "    ---\n",
    "    dob=null             : date                         # date of birth\n",
    "    sex=\"unknown\"        : enum('M','F','unknown')      # sex\n",
    "    \"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's take a look at our brand new table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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",
       "\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>Experimental animals</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\">mouse_id</p>\n",
       "                                <span class=\"djtooltiptext\">Unique animal ID</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">dob</p>\n",
       "                                <span class=\"djtooltiptext\">date of birth</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">sex</p>\n",
       "                                <span class=\"djtooltiptext\">sex</span>\n",
       "                            </div> </th> </tr> </thead>\n",
       "                <tbody> <tr>  </tr> </tbody>\n",
       "            </table>\n",
       "            \n",
       "            <p>Total: 0</p></div>\n",
       "            "
      ],
      "text/plain": [
       "*mouse_id    dob     sex    \n",
       "+----------+ +-----+ +-----+\n",
       "\n",
       " (Total: 0)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Mouse()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Insert entries with `insert1` and `insert` methods"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The table was successfully defined, but without any content, the table is not too interesting. Let's go ahead and insert some **mouse** into the table, one at a time using the `insert1` method."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's insert a mouse with the following information:\n",
    "* mouse_id: 0\n",
    "* date of birth: 2017-03-01\n",
    "* sex: male"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "Mouse.insert1((0, '2017-03-01', 'M'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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",
       "\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>Experimental animals</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\">mouse_id</p>\n",
       "                                <span class=\"djtooltiptext\">Unique animal ID</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">dob</p>\n",
       "                                <span class=\"djtooltiptext\">date of birth</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">sex</p>\n",
       "                                <span class=\"djtooltiptext\">sex</span>\n",
       "                            </div> </th> </tr> </thead>\n",
       "                <tbody> <tr> <td>0</td>\n",
       "<td>2017-03-01</td>\n",
       "<td>M</td> </tr> </tbody>\n",
       "            </table>\n",
       "            \n",
       "            <p>Total: 1</p></div>\n",
       "            "
      ],
      "text/plain": [
       "*mouse_id    dob            sex    \n",
       "+----------+ +------------+ +-----+\n",
       "0            2017-03-01     M      \n",
       " (Total: 1)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Mouse()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You could also insert1 as a dictionary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = {\n",
    "    'mouse_id': 100,\n",
    "    'dob': '2017-05-12',\n",
    "    'sex': 'F'\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "Mouse.insert1(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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",
       "\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>Experimental animals</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\">mouse_id</p>\n",
       "                                <span class=\"djtooltiptext\">Unique animal ID</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">dob</p>\n",
       "                                <span class=\"djtooltiptext\">date of birth</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">sex</p>\n",
       "                                <span class=\"djtooltiptext\">sex</span>\n",
       "                            </div> </th> </tr> </thead>\n",
       "                <tbody> <tr> <td>0</td>\n",
       "<td>2017-03-01</td>\n",
       "<td>M</td></tr><tr><td>100</td>\n",
       "<td>2017-05-12</td>\n",
       "<td>F</td> </tr> </tbody>\n",
       "            </table>\n",
       "            \n",
       "            <p>Total: 2</p></div>\n",
       "            "
      ],
      "text/plain": [
       "*mouse_id    dob            sex    \n",
       "+----------+ +------------+ +-----+\n",
       "0            2017-03-01     M      \n",
       "100          2017-05-12     F      \n",
       " (Total: 2)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Mouse()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also insert multiple **mice** together using the `insert` method, passing in a list of data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = [\n",
    "  (1, '2016-11-19', 'M'),\n",
    "  (2, '2016-11-20', 'unknown'),\n",
    "  (5, '2016-12-25', 'F')\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "Mouse.insert(data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Of course, you can insert a list of dictionaries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = [\n",
    "    {'mouse_id': 10, 'dob': '2017-01-01', 'sex': 'F'},\n",
    "    {'mouse_id': 11, 'dob': '2017-01-03', 'sex': 'F'},\n",
    "]\n",
    "\n",
    "# insert them all\n",
    "Mouse.insert(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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",
       "\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>Experimental animals</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\">mouse_id</p>\n",
       "                                <span class=\"djtooltiptext\">Unique animal ID</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">dob</p>\n",
       "                                <span class=\"djtooltiptext\">date of birth</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">sex</p>\n",
       "                                <span class=\"djtooltiptext\">sex</span>\n",
       "                            </div> </th> </tr> </thead>\n",
       "                <tbody> <tr> <td>0</td>\n",
       "<td>2017-03-01</td>\n",
       "<td>M</td></tr><tr><td>1</td>\n",
       "<td>2016-11-19</td>\n",
       "<td>M</td></tr><tr><td>2</td>\n",
       "<td>2016-11-20</td>\n",
       "<td>unknown</td></tr><tr><td>5</td>\n",
       "<td>2016-12-25</td>\n",
       "<td>F</td></tr><tr><td>10</td>\n",
       "<td>2017-01-01</td>\n",
       "<td>F</td></tr><tr><td>11</td>\n",
       "<td>2017-01-03</td>\n",
       "<td>F</td></tr><tr><td>100</td>\n",
       "<td>2017-05-12</td>\n",
       "<td>F</td> </tr> </tbody>\n",
       "            </table>\n",
       "            \n",
       "            <p>Total: 7</p></div>\n",
       "            "
      ],
      "text/plain": [
       "*mouse_id    dob            sex        \n",
       "+----------+ +------------+ +---------+\n",
       "0            2017-03-01     M          \n",
       "1            2016-11-19     M          \n",
       "2            2016-11-20     unknown    \n",
       "5            2016-12-25     F          \n",
       "10           2017-01-01     F          \n",
       "11           2017-01-03     F          \n",
       "100          2017-05-12     F          \n",
       " (Total: 7)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Mouse()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data integrity"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "DataJoint checks for data integrity, and ensures that you don't insert a duplicate by mistake. Let's try inserting another mouse with `mouse_id: 0` and see what happens!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "ename": "DuplicateError",
     "evalue": "(\"Duplicate entry '0' for key 'PRIMARY'\", 'To ignore duplicate entries in insert, set skip_duplicates=True')",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mDuplicateError\u001b[0m                            Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-14-304369cd8a83>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      2\u001b[0m {'mouse_id': 0,\n\u001b[1;32m      3\u001b[0m  \u001b[0;34m'dob'\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0;34m'2018-01-01'\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 4\u001b[0;31m  \u001b[0;34m'sex'\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0;34m'M'\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      5\u001b[0m })\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/datajoint/table.py\u001b[0m in \u001b[0;36minsert1\u001b[0;34m(self, row, **kwargs)\u001b[0m\n\u001b[1;32m    173\u001b[0m         \u001b[0mFor\u001b[0m \u001b[0mkwargs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0msee\u001b[0m \u001b[0minsert\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    174\u001b[0m         \"\"\"\n\u001b[0;32m--> 175\u001b[0;31m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0minsert\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mrow\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    176\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    177\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0minsert\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mrows\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mreplace\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mFalse\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mskip_duplicates\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mFalse\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mignore_extra_fields\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mFalse\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mallow_direct_insert\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/datajoint/table.py\u001b[0m in \u001b[0;36minsert\u001b[0;34m(self, rows, replace, skip_duplicates, ignore_extra_fields, allow_direct_insert)\u001b[0m\n\u001b[1;32m    344\u001b[0m                 \u001b[0;32mraise\u001b[0m \u001b[0merr\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msuggest\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'To ignore extra fields in insert, set ignore_extra_fields=True'\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mfrom\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    345\u001b[0m             \u001b[0;32mexcept\u001b[0m \u001b[0mDuplicateError\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0merr\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 346\u001b[0;31m                 \u001b[0;32mraise\u001b[0m \u001b[0merr\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msuggest\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'To ignore duplicate entries in insert, set skip_duplicates=True'\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mfrom\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    347\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    348\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdelete_quick\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mget_count\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mFalse\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mDuplicateError\u001b[0m: (\"Duplicate entry '0' for key 'PRIMARY'\", 'To ignore duplicate entries in insert, set skip_duplicates=True')"
     ]
    }
   ],
   "source": [
    "Mouse.insert1(\n",
    "{'mouse_id': 0,\n",
    " 'dob': '2018-01-01',\n",
    " 'sex': 'M',\n",
    "})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Go ahead and insert a few more mice into your table before moving on."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = [\n",
    "    {'mouse_id': 12, 'dob': '2017-03-21', 'sex': 'F'},\n",
    "    {'mouse_id': 18, 'dob': '2017-05-01', 'sex': 'F'},\n",
    "    {'mouse_id': 19, 'dob': '2018-07-21', 'sex': 'M'},\n",
    "    {'mouse_id': 22, 'dob': '2019-12-15', 'sex': 'F'},\n",
    "    {'mouse_id': 34, 'dob': '2018-09-22', 'sex': 'M'}\n",
    "]\n",
    "\n",
    "# insert them all\n",
    "Mouse.insert(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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",
       "\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>Experimental animals</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\">mouse_id</p>\n",
       "                                <span class=\"djtooltiptext\">Unique animal ID</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">dob</p>\n",
       "                                <span class=\"djtooltiptext\">date of birth</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">sex</p>\n",
       "                                <span class=\"djtooltiptext\">sex</span>\n",
       "                            </div> </th> </tr> </thead>\n",
       "                <tbody> <tr> <td>0</td>\n",
       "<td>2017-03-01</td>\n",
       "<td>M</td></tr><tr><td>1</td>\n",
       "<td>2016-11-19</td>\n",
       "<td>M</td></tr><tr><td>2</td>\n",
       "<td>2016-11-20</td>\n",
       "<td>unknown</td></tr><tr><td>5</td>\n",
       "<td>2016-12-25</td>\n",
       "<td>F</td></tr><tr><td>10</td>\n",
       "<td>2017-01-01</td>\n",
       "<td>F</td></tr><tr><td>11</td>\n",
       "<td>2017-01-03</td>\n",
       "<td>F</td></tr><tr><td>12</td>\n",
       "<td>2017-03-21</td>\n",
       "<td>F</td></tr><tr><td>18</td>\n",
       "<td>2017-05-01</td>\n",
       "<td>F</td></tr><tr><td>19</td>\n",
       "<td>2018-07-21</td>\n",
       "<td>M</td></tr><tr><td>22</td>\n",
       "<td>2019-12-15</td>\n",
       "<td>F</td></tr><tr><td>34</td>\n",
       "<td>2018-09-22</td>\n",
       "<td>M</td></tr><tr><td>100</td>\n",
       "<td>2017-05-12</td>\n",
       "<td>F</td> </tr> </tbody>\n",
       "            </table>\n",
       "            \n",
       "            <p>Total: 12</p></div>\n",
       "            "
      ],
      "text/plain": [
       "*mouse_id    dob            sex        \n",
       "+----------+ +------------+ +---------+\n",
       "0            2017-03-01     M          \n",
       "1            2016-11-19     M          \n",
       "2            2016-11-20     unknown    \n",
       "5            2016-12-25     F          \n",
       "10           2017-01-01     F          \n",
       "11           2017-01-03     F          \n",
       "12           2017-03-21     F          \n",
       "18           2017-05-01     F          \n",
       "19           2018-07-21     M          \n",
       "22           2019-12-15     F          \n",
       "34           2018-09-22     M          \n",
       "100          2017-05-12     F          \n",
       " (Total: 12)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Mouse()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# ENTER YOUR CODE - Insert more mice\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create tables with dependencies"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Congratulations! We have successfully created your first table! We are now ready to tackle and include other **entities** in the project into our data pipeline. \n",
    "\n",
    "Let's now take a look at representing an **experimental session**."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As with mouse, we should think about **what information (i.e. attributes) is needed to uniquely identify an experimental session**. Here is the relevant section of the project description:\n",
    "\n",
    "> * As a hard working neuroscientist, you perform experiments every day, sometimes working with **more than one mouse in a day**! However, on an any given day, **a mouse undergoes at most one recording session**.\n",
    "> * For each experimental session, you would like to record **what mouse you worked with** and **when you performed the experiment**. You would also like to keep track of other helpful information such as the **experimental setup** you worked on."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Based on the above, it appears that you need to know:\n",
    "\n",
    "* the date of the session\n",
    "* the mouse you recorded from in that session\n",
    "\n",
    "to uniquely identify a single experimental session."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that, to uniquely identify an experimental session (or simply a **session**), we need to know the mouse that the session was about. In other words, a session cannot existing without a corresponding mouse! \n",
    "\n",
    "With **mouse** already represented as a table in our pipeline, we say that the session **depends on** the mouse! We could graphically represent this in an **entity relationship diagram (ERD)** by drawing the line between two tables, with the one below (**session**) depending on the one above (**mouse**)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Thus we will need both **mouse** and a new attribute **session_date** to uniquely identify a single session. \n",
    "\n",
    "Remember that a **mouse** is already uniquely identified by its primary key - **mouse_id**. In DataJoint, you can declare that **session** depends on the mouse, and DataJoint will automatically include the mouse's primary key (`mouse_id`) as part of the session's primary key, along side any additional attribute(s) you specify."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "@schema\n",
    "class Session(dj.Manual):\n",
    "    definition = \"\"\"\n",
    "    # Experiment session\n",
    "    -> Mouse\n",
    "    session_date               : date                         # date\n",
    "    ---\n",
    "    experiment_setup           : int                          # experiment setup ID\n",
    "    experimenter               : varchar(100)                 # experimenter name\n",
    "    data_path=''               : varchar(255)                 #\n",
    "    \"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can actually generate something similar to an entity relationship diagram (ERD) on the fly by calling `dj.Diagram` with the schema object. Many of the symbols and features are the same as the ERD standard."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/svg+xml": "<svg height=\"114pt\" viewBox=\"0.00 0.00 66.69 114.00\" width=\"67pt\" xmlns=\"http://www.w3.org/2000/svg\" xmlns:xlink=\"http://www.w3.org/1999/xlink\">\n<g class=\"graph\" id=\"graph0\" transform=\"scale(1 1) rotate(0) translate(4 110)\">\n<title>%3</title>\n<polygon fill=\"#ffffff\" points=\"-4,4 -4,-110 62.6914,-110 62.6914,4 -4,4\" stroke=\"transparent\"/>\n<!-- Mouse -->\n<g class=\"node\" id=\"node1\">\n<title>Mouse</title>\n<g id=\"a_node1\"><a xlink:title=\"mouse_id             \r\n------------------------------\r\ndob=null             \r\nsex=&quot;unknown&quot;        \r\n\">\n<polygon fill=\"#00ff00\" fill-opacity=\"0.188235\" points=\"55.3633,-106 3.3281,-106 3.3281,-71 55.3633,-71 55.3633,-106\" stroke=\"#00ff00\" stroke-opacity=\"0.188235\"/>\n<text fill=\"#006400\" font-family=\"arial\" font-size=\"12.00\" text-anchor=\"start\" text-decoration=\"underline\" x=\"11.3369\" y=\"-86.7994\">Mouse</text>\n</a>\n</g>\n</g>\n<!-- Session -->\n<g class=\"node\" id=\"node2\">\n<title>Session</title>\n<g id=\"a_node2\"><a xlink:title=\"→ Mouse\r\nsession_date         \r\n------------------------------\r\nexperiment_setup     \r\nexperimenter         \r\ndata_path=&quot;&quot;         \r\n\">\n<polygon fill=\"#00ff00\" fill-opacity=\"0.188235\" points=\"58.5379,-35 .1535,-35 .1535,0 58.5379,0 58.5379,-35\" stroke=\"#00ff00\" stroke-opacity=\"0.188235\"/>\n<text fill=\"#006400\" font-family=\"arial\" font-size=\"12.00\" text-anchor=\"start\" text-decoration=\"underline\" x=\"8\" y=\"-15.7994\">Session</text>\n</a>\n</g>\n</g>\n<!-- Mouse&#45;&gt;Session -->\n<g class=\"edge\" id=\"edge1\">\n<title>Mouse-&gt;Session</title>\n<path d=\"M29.3457,-70.9494C29.3457,-60.1083 29.3457,-46.2366 29.3457,-35.3418\" fill=\"none\" stroke=\"#000000\" stroke-opacity=\"0.250980\" stroke-width=\".75\"/>\n</g>\n</g>\n</svg>",
      "text/plain": [
       "<datajoint.diagram.Diagram at 0xa1d699588>"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dj.Diagram(schema)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's try inserting a few sessions manually."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = {\n",
    "  'mouse_id': 0,\n",
    "  'session_date': '2017-05-15',\n",
    "  'experiment_setup': 0,\n",
    "  'experimenter': 'Edgar Y. Walker'\n",
    "}\n",
    "\n",
    "Session.insert1(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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",
       "\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>Experiment session</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\">mouse_id</p>\n",
       "                                <span class=\"djtooltiptext\">Unique animal ID</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"primary\">session_date</p>\n",
       "                                <span class=\"djtooltiptext\">date</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">experiment_setup</p>\n",
       "                                <span class=\"djtooltiptext\">experiment setup ID</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">experimenter</p>\n",
       "                                <span class=\"djtooltiptext\">experimenter name</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">data_path</p>\n",
       "                                <span class=\"djtooltiptext\"></span>\n",
       "                            </div> </th> </tr> </thead>\n",
       "                <tbody> <tr> <td>0</td>\n",
       "<td>2017-05-15</td>\n",
       "<td>0</td>\n",
       "<td>Edgar Y. Walker</td>\n",
       "<td></td> </tr> </tbody>\n",
       "            </table>\n",
       "            \n",
       "            <p>Total: 1</p></div>\n",
       "            "
      ],
      "text/plain": [
       "*mouse_id    *session_date  experiment_set experimenter   data_path    \n",
       "+----------+ +------------+ +------------+ +------------+ +-----------+\n",
       "0            2017-05-15     0              Edgar Y. Walke              \n",
       " (Total: 1)"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Session()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's insert another session for `mouse_id = 0` but on a different date."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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",
       "\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>Experiment session</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\">mouse_id</p>\n",
       "                                <span class=\"djtooltiptext\">Unique animal ID</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"primary\">session_date</p>\n",
       "                                <span class=\"djtooltiptext\">date</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">experiment_setup</p>\n",
       "                                <span class=\"djtooltiptext\">experiment setup ID</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">experimenter</p>\n",
       "                                <span class=\"djtooltiptext\">experimenter name</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">data_path</p>\n",
       "                                <span class=\"djtooltiptext\"></span>\n",
       "                            </div> </th> </tr> </thead>\n",
       "                <tbody> <tr> <td>0</td>\n",
       "<td>2017-05-15</td>\n",
       "<td>0</td>\n",
       "<td>Edgar Y. Walker</td>\n",
       "<td></td></tr><tr><td>0</td>\n",
       "<td>2018-01-15</td>\n",
       "<td>100</td>\n",
       "<td>Jacob Reimer</td>\n",
       "<td></td> </tr> </tbody>\n",
       "            </table>\n",
       "            \n",
       "            <p>Total: 2</p></div>\n",
       "            "
      ],
      "text/plain": [
       "*mouse_id    *session_date  experiment_set experimenter   data_path    \n",
       "+----------+ +------------+ +------------+ +------------+ +-----------+\n",
       "0            2017-05-15     0              Edgar Y. Walke              \n",
       "0            2018-01-15     100            Jacob Reimer                \n",
       " (Total: 2)"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = {\n",
    "  'mouse_id': 0,\n",
    "  'session_date': '2018-01-15',\n",
    "  'experiment_setup': 100,\n",
    "  'experimenter': 'Jacob Reimer'\n",
    "}\n",
    "\n",
    "Session.insert1(data)\n",
    "\n",
    "Session()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And another session done on the same date but on a different mouse"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = {\n",
    "  'mouse_id': 18,\n",
    "  'session_date': '2018-01-15',\n",
    "  'experiment_setup': 101,\n",
    "  'experimenter': 'Jacob Reimer'\n",
    "}\n",
    "\n",
    "# insert them all\n",
    "Session.insert1(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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",
       "\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>Experiment session</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\">mouse_id</p>\n",
       "                                <span class=\"djtooltiptext\">Unique animal ID</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"primary\">session_date</p>\n",
       "                                <span class=\"djtooltiptext\">date</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">experiment_setup</p>\n",
       "                                <span class=\"djtooltiptext\">experiment setup ID</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">experimenter</p>\n",
       "                                <span class=\"djtooltiptext\">experimenter name</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">data_path</p>\n",
       "                                <span class=\"djtooltiptext\"></span>\n",
       "                            </div> </th> </tr> </thead>\n",
       "                <tbody> <tr> <td>0</td>\n",
       "<td>2017-05-15</td>\n",
       "<td>0</td>\n",
       "<td>Edgar Y. Walker</td>\n",
       "<td></td></tr><tr><td>0</td>\n",
       "<td>2018-01-15</td>\n",
       "<td>100</td>\n",
       "<td>Jacob Reimer</td>\n",
       "<td></td></tr><tr><td>18</td>\n",
       "<td>2018-01-15</td>\n",
       "<td>101</td>\n",
       "<td>Jacob Reimer</td>\n",
       "<td></td> </tr> </tbody>\n",
       "            </table>\n",
       "            \n",
       "            <p>Total: 3</p></div>\n",
       "            "
      ],
      "text/plain": [
       "*mouse_id    *session_date  experiment_set experimenter   data_path    \n",
       "+----------+ +------------+ +------------+ +------------+ +-----------+\n",
       "0            2017-05-15     0              Edgar Y. Walke              \n",
       "0            2018-01-15     100            Jacob Reimer                \n",
       "18           2018-01-15     101            Jacob Reimer                \n",
       " (Total: 3)"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Session()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What happens if we try to insert a session for a mouse that doesn't exist?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "bad_data = {\n",
    "    'mouse_id': 9999,  # this mouse doesn't exist!\n",
    "    'session_date': '2017-05-15',\n",
    "    'experiment_setup': 0,\n",
    "    'experimenter': 'Edgar Y. Walker'\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "ename": "IntegrityError",
     "evalue": "Cannot add or update a child row: a foreign key constraint fails (`shan_tutorial`.`session`, CONSTRAINT `session_ibfk_1` FOREIGN KEY (`mouse_id`) REFERENCES `mouse` (`mouse_id`) ON UPDATE CASCADE)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mIntegrityError\u001b[0m                            Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-25-7b1482a07dfd>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mSession\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0minsert1\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mbad_data\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/datajoint/table.py\u001b[0m in \u001b[0;36minsert1\u001b[0;34m(self, row, **kwargs)\u001b[0m\n\u001b[1;32m    173\u001b[0m         \u001b[0mFor\u001b[0m \u001b[0mkwargs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0msee\u001b[0m \u001b[0minsert\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    174\u001b[0m         \"\"\"\n\u001b[0;32m--> 175\u001b[0;31m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0minsert\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mrow\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    176\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    177\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0minsert\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mrows\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mreplace\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mFalse\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mskip_duplicates\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mFalse\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mignore_extra_fields\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mFalse\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mallow_direct_insert\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/datajoint/table.py\u001b[0m in \u001b[0;36minsert\u001b[0;34m(self, rows, replace, skip_duplicates, ignore_extra_fields, allow_direct_insert)\u001b[0m\n\u001b[1;32m    340\u001b[0m                                if skip_duplicates else ''))\n\u001b[1;32m    341\u001b[0m                 self.connection.query(query, args=list(\n\u001b[0;32m--> 342\u001b[0;31m                     itertools.chain.from_iterable((v for v in r['values'] if v is not None) for r in rows)))\n\u001b[0m\u001b[1;32m    343\u001b[0m             \u001b[0;32mexcept\u001b[0m \u001b[0mUnknownAttributeError\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0merr\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    344\u001b[0m                 \u001b[0;32mraise\u001b[0m \u001b[0merr\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msuggest\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'To ignore extra fields in insert, set ignore_extra_fields=True'\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mfrom\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/datajoint/connection.py\u001b[0m in \u001b[0;36mquery\u001b[0;34m(self, query, args, as_dict, suppress_warnings, reconnect)\u001b[0m\n\u001b[1;32m    209\u001b[0m         \u001b[0mcursor\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_conn\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcursor\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcursor\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mcursor_class\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    210\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 211\u001b[0;31m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__execute_query\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mquery\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor_class\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0msuppress_warnings\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    212\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0merrors\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mLostConnectionError\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    213\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mreconnect\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/datajoint/connection.py\u001b[0m in \u001b[0;36m__execute_query\u001b[0;34m(cursor, query, args, cursor_class, suppress_warnings)\u001b[0m\n\u001b[1;32m    191\u001b[0m                 \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mquery\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0margs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    192\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mclient_errors\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0merr\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 193\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mtranslate_query_error\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0merr\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mquery\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mfrom\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    194\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    195\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mquery\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mquery\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0margs\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mas_dict\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mFalse\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0msuppress_warnings\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mTrue\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mreconnect\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mIntegrityError\u001b[0m: Cannot add or update a child row: a foreign key constraint fails (`shan_tutorial`.`session`, CONSTRAINT `session_ibfk_1` FOREIGN KEY (`mouse_id`) REFERENCES `mouse` (`mouse_id`) ON UPDATE CASCADE)"
     ]
    }
   ],
   "source": [
    "Session.insert1(bad_data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Querying data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Often times, you don't want all data but rather work with **a subset of entities** matching specific criteria. Rather than fetching the whole data and writing your own parser, it is far more efficient to narrow your data to the subset before fetching.\n",
    "\n",
    "For this, DataJoint offers very powerful yet intuitive **querying** syntax that let's you select exactly the data you want before you fetch it.\n",
    "\n",
    "It is also critical to note that the result of any DataJoint query represents a valid entity."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will introduce three major types of queries used in DataJoint:\n",
    "* restriction (`&`) and negative restriction (`-`): filter data\n",
    "* join (`*`): bring fields from different tables together\n",
    "* projection (`.proj()`): focus on a subset of attributes\n",
    "* aggregation (`.aggr()`): simple computation of one table against another table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Restrictions (`&`) - filter data with certain conditions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The **restriction** operation, `&`, let's you specify the criteria to narrow down the table on the left."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exact match"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Mouse with id 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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",
       "\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>Experimental animals</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\">mouse_id</p>\n",
       "                                <span class=\"djtooltiptext\">Unique animal ID</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">dob</p>\n",
       "                                <span class=\"djtooltiptext\">date of birth</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">sex</p>\n",
       "                                <span class=\"djtooltiptext\">sex</span>\n",
       "                            </div> </th> </tr> </thead>\n",
       "                <tbody> <tr> <td>0</td>\n",
       "<td>2017-03-01</td>\n",
       "<td>M</td> </tr> </tbody>\n",
       "            </table>\n",
       "            \n",
       "            <p>Total: 1</p></div>\n",
       "            "
      ],
      "text/plain": [
       "*mouse_id    dob            sex    \n",
       "+----------+ +------------+ +-----+\n",
       "0            2017-03-01     M      \n",
       " (Total: 1)"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Mouse & 'mouse_id = 0'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "All male mice (`'sex = \"M\"'`)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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",
       "\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>Experimental animals</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\">mouse_id</p>\n",
       "                                <span class=\"djtooltiptext\">Unique animal ID</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">dob</p>\n",
       "                                <span class=\"djtooltiptext\">date of birth</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">sex</p>\n",
       "                                <span class=\"djtooltiptext\">sex</span>\n",
       "                            </div> </th> </tr> </thead>\n",
       "                <tbody> <tr> <td>0</td>\n",
       "<td>2017-03-01</td>\n",
       "<td>M</td></tr><tr><td>1</td>\n",
       "<td>2016-11-19</td>\n",
       "<td>M</td></tr><tr><td>19</td>\n",
       "<td>2018-07-21</td>\n",
       "<td>M</td></tr><tr><td>34</td>\n",
       "<td>2018-09-22</td>\n",
       "<td>M</td> </tr> </tbody>\n",
       "            </table>\n",
       "            \n",
       "            <p>Total: 4</p></div>\n",
       "            "
      ],
      "text/plain": [
       "*mouse_id    dob            sex    \n",
       "+----------+ +------------+ +-----+\n",
       "0            2017-03-01     M      \n",
       "1            2016-11-19     M      \n",
       "19           2018-07-21     M      \n",
       "34           2018-09-22     M      \n",
       " (Total: 4)"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Mouse & 'sex = \"M\"'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "All female mice (`'sex=\"F\"'`)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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",
       "\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>Experimental animals</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\">mouse_id</p>\n",
       "                                <span class=\"djtooltiptext\">Unique animal ID</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">dob</p>\n",
       "                                <span class=\"djtooltiptext\">date of birth</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">sex</p>\n",
       "                                <span class=\"djtooltiptext\">sex</span>\n",
       "                            </div> </th> </tr> </thead>\n",
       "                <tbody> <tr> <td>5</td>\n",
       "<td>2016-12-25</td>\n",
       "<td>F</td></tr><tr><td>10</td>\n",
       "<td>2017-01-01</td>\n",
       "<td>F</td></tr><tr><td>11</td>\n",
       "<td>2017-01-03</td>\n",
       "<td>F</td></tr><tr><td>12</td>\n",
       "<td>2017-03-21</td>\n",
       "<td>F</td></tr><tr><td>18</td>\n",
       "<td>2017-05-01</td>\n",
       "<td>F</td></tr><tr><td>22</td>\n",
       "<td>2019-12-15</td>\n",
       "<td>F</td></tr><tr><td>100</td>\n",
       "<td>2017-05-12</td>\n",
       "<td>F</td> </tr> </tbody>\n",
       "            </table>\n",
       "            \n",
       "            <p>Total: 7</p></div>\n",
       "            "
      ],
      "text/plain": [
       "*mouse_id    dob            sex    \n",
       "+----------+ +------------+ +-----+\n",
       "5            2016-12-25     F      \n",
       "10           2017-01-01     F      \n",
       "11           2017-01-03     F      \n",
       "12           2017-03-21     F      \n",
       "18           2017-05-01     F      \n",
       "22           2019-12-15     F      \n",
       "100          2017-05-12     F      \n",
       " (Total: 7)"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Mouse & 'sex = \"F\"'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also use as a dictionary as a restrictor, with one field or multiple fields"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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",
       "\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>Experimental animals</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\">mouse_id</p>\n",
       "                                <span class=\"djtooltiptext\">Unique animal ID</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">dob</p>\n",
       "                                <span class=\"djtooltiptext\">date of birth</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">sex</p>\n",
       "                                <span class=\"djtooltiptext\">sex</span>\n",
       "                            </div> </th> </tr> </thead>\n",
       "                <tbody> <tr> <td>5</td>\n",
       "<td>2016-12-25</td>\n",
       "<td>F</td> </tr> </tbody>\n",
       "            </table>\n",
       "            \n",
       "            <p>Total: 1</p></div>\n",
       "            "
      ],
      "text/plain": [
       "*mouse_id    dob            sex    \n",
       "+----------+ +------------+ +-----+\n",
       "5            2016-12-25     F      \n",
       " (Total: 1)"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Mouse & dict(mouse_id=5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Inequality"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also use inequality in your query to match based on numerical values."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Mouse that is born **after 2017-01-01**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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",
       "\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>Experimental animals</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\">mouse_id</p>\n",
       "                                <span class=\"djtooltiptext\">Unique animal ID</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">dob</p>\n",
       "                                <span class=\"djtooltiptext\">date of birth</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">sex</p>\n",
       "                                <span class=\"djtooltiptext\">sex</span>\n",
       "                            </div> </th> </tr> </thead>\n",
       "                <tbody> <tr> <td>0</td>\n",
       "<td>2017-03-01</td>\n",
       "<td>M</td></tr><tr><td>11</td>\n",
       "<td>2017-01-03</td>\n",
       "<td>F</td></tr><tr><td>12</td>\n",
       "<td>2017-03-21</td>\n",
       "<td>F</td></tr><tr><td>18</td>\n",
       "<td>2017-05-01</td>\n",
       "<td>F</td></tr><tr><td>19</td>\n",
       "<td>2018-07-21</td>\n",
       "<td>M</td></tr><tr><td>22</td>\n",
       "<td>2019-12-15</td>\n",
       "<td>F</td></tr><tr><td>34</td>\n",
       "<td>2018-09-22</td>\n",
       "<td>M</td></tr><tr><td>100</td>\n",
       "<td>2017-05-12</td>\n",
       "<td>F</td> </tr> </tbody>\n",
       "            </table>\n",
       "            \n",
       "            <p>Total: 8</p></div>\n",
       "            "
      ],
      "text/plain": [
       "*mouse_id    dob            sex    \n",
       "+----------+ +------------+ +-----+\n",
       "0            2017-03-01     M      \n",
       "11           2017-01-03     F      \n",
       "12           2017-03-21     F      \n",
       "18           2017-05-01     F      \n",
       "19           2018-07-21     M      \n",
       "22           2019-12-15     F      \n",
       "34           2018-09-22     M      \n",
       "100          2017-05-12     F      \n",
       " (Total: 8)"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Mouse & 'dob > \"2017-01-01\"'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Mouse that is born within a range of dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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",
       "\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>Experimental animals</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\">mouse_id</p>\n",
       "                                <span class=\"djtooltiptext\">Unique animal ID</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">dob</p>\n",
       "                                <span class=\"djtooltiptext\">date of birth</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">sex</p>\n",
       "                                <span class=\"djtooltiptext\">sex</span>\n",
       "                            </div> </th> </tr> </thead>\n",
       "                <tbody> <tr> <td>0</td>\n",
       "<td>2017-03-01</td>\n",
       "<td>M</td></tr><tr><td>12</td>\n",
       "<td>2017-03-21</td>\n",
       "<td>F</td></tr><tr><td>18</td>\n",
       "<td>2017-05-01</td>\n",
       "<td>F</td></tr><tr><td>100</td>\n",
       "<td>2017-05-12</td>\n",
       "<td>F</td> </tr> </tbody>\n",
       "            </table>\n",
       "            \n",
       "            <p>Total: 4</p></div>\n",
       "            "
      ],
      "text/plain": [
       "*mouse_id    dob            sex    \n",
       "+----------+ +------------+ +-----+\n",
       "0            2017-03-01     M      \n",
       "12           2017-03-21     F      \n",
       "18           2017-05-01     F      \n",
       "100          2017-05-12     F      \n",
       " (Total: 4)"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Mouse & 'dob between \"2017-03-01\" and \"2017-08-23\"'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Mouse that is **not** male"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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",
       "\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>Experimental animals</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\">mouse_id</p>\n",
       "                                <span class=\"djtooltiptext\">Unique animal ID</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">dob</p>\n",
       "                                <span class=\"djtooltiptext\">date of birth</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">sex</p>\n",
       "                                <span class=\"djtooltiptext\">sex</span>\n",
       "                            </div> </th> </tr> </thead>\n",
       "                <tbody> <tr> <td>2</td>\n",
       "<td>2016-11-20</td>\n",
       "<td>unknown</td></tr><tr><td>5</td>\n",
       "<td>2016-12-25</td>\n",
       "<td>F</td></tr><tr><td>10</td>\n",
       "<td>2017-01-01</td>\n",
       "<td>F</td></tr><tr><td>11</td>\n",
       "<td>2017-01-03</td>\n",
       "<td>F</td></tr><tr><td>12</td>\n",
       "<td>2017-03-21</td>\n",
       "<td>F</td></tr><tr><td>18</td>\n",
       "<td>2017-05-01</td>\n",
       "<td>F</td></tr><tr><td>22</td>\n",
       "<td>2019-12-15</td>\n",
       "<td>F</td></tr><tr><td>100</td>\n",
       "<td>2017-05-12</td>\n",
       "<td>F</td> </tr> </tbody>\n",
       "            </table>\n",
       "            \n",
       "            <p>Total: 8</p></div>\n",
       "            "
      ],
      "text/plain": [
       "*mouse_id    dob            sex        \n",
       "+----------+ +------------+ +---------+\n",
       "2            2016-11-20     unknown    \n",
       "5            2016-12-25     F          \n",
       "10           2017-01-01     F          \n",
       "11           2017-01-03     F          \n",
       "12           2017-03-21     F          \n",
       "18           2017-05-01     F          \n",
       "22           2019-12-15     F          \n",
       "100          2017-05-12     F          \n",
       " (Total: 8)"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Mouse & 'sex != \"M\"'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can easily combine multiple restrictions to narrow down the entities based on multiple attributes."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's find all mouse that **is not male** AND **born after 2017-01-01**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "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",
       "\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>Experimental animals</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\">mouse_id</p>\n",
       "                                <span class=\"djtooltiptext\">Unique animal ID</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">dob</p>\n",
       "                                <span class=\"djtooltiptext\">date of birth</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">sex</p>\n",
       "                                <span class=\"djtooltiptext\">sex</span>\n",
       "                            </div> </th> </tr> </thead>\n",
       "                <tbody> <tr> <td>11</td>\n",
       "<td>2017-01-03</td>\n",
       "<td>F</td></tr><tr><td>12</td>\n",
       "<td>2017-03-21</td>\n",
       "<td>F</td></tr><tr><td>18</td>\n",
       "<td>2017-05-01</td>\n",
       "<td>F</td></tr><tr><td>22</td>\n",
       "<td>2019-12-15</td>\n",
       "<td>F</td></tr><tr><td>100</td>\n",
       "<td>2017-05-12</td>\n",
       "<td>F</td> </tr> </tbody>\n",
       "            </table>\n",
       "            \n",
       "            <p>Total: 5</p></div>\n",
       "            "
      ],
      "text/plain": [
       "*mouse_id    dob            sex    \n",
       "+----------+ +------------+ +-----+\n",
       "11           2017-01-03     F      \n",
       "12           2017-03-21     F      \n",
       "18           2017-05-01     F      \n",
       "22           2019-12-15     F      \n",
       "100          2017-05-12     F      \n",
       " (Total: 5)"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Mouse & 'sex != \"M\"' & 'dob > \"2017-01-01\"'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "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",
       "\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>Experimental animals</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\">mouse_id</p>\n",
       "                                <span class=\"djtooltiptext\">Unique animal ID</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">dob</p>\n",
       "                                <span class=\"djtooltiptext\">date of birth</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">sex</p>\n",
       "                                <span class=\"djtooltiptext\">sex</span>\n",
       "                            </div> </th> </tr> </thead>\n",
       "                <tbody> <tr> <td>11</td>\n",
       "<td>2017-01-03</td>\n",
       "<td>F</td></tr><tr><td>12</td>\n",
       "<td>2017-03-21</td>\n",
       "<td>F</td></tr><tr><td>18</td>\n",
       "<td>2017-05-01</td>\n",
       "<td>F</td></tr><tr><td>22</td>\n",
       "<td>2019-12-15</td>\n",
       "<td>F</td></tr><tr><td>100</td>\n",
       "<td>2017-05-12</td>\n",
       "<td>F</td> </tr> </tbody>\n",
       "            </table>\n",
       "            \n",
       "            <p>Total: 5</p></div>\n",
       "            "
      ],
      "text/plain": [
       "*mouse_id    dob            sex    \n",
       "+----------+ +------------+ +-----+\n",
       "11           2017-01-03     F      \n",
       "12           2017-03-21     F      \n",
       "18           2017-05-01     F      \n",
       "22           2019-12-15     F      \n",
       "100          2017-05-12     F      \n",
       " (Total: 5)"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Mouse & 'sex != \"M\" and dob > \"2017-01-01\"'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Result of one query can be used in another query! Let's first find **all female mice** and store the result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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",
       "\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>Experimental animals</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\">mouse_id</p>\n",
       "                                <span class=\"djtooltiptext\">Unique animal ID</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">dob</p>\n",
       "                                <span class=\"djtooltiptext\">date of birth</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">sex</p>\n",
       "                                <span class=\"djtooltiptext\">sex</span>\n",
       "                            </div> </th> </tr> </thead>\n",
       "                <tbody> <tr> <td>5</td>\n",
       "<td>2016-12-25</td>\n",
       "<td>F</td></tr><tr><td>10</td>\n",
       "<td>2017-01-01</td>\n",
       "<td>F</td></tr><tr><td>11</td>\n",
       "<td>2017-01-03</td>\n",
       "<td>F</td></tr><tr><td>12</td>\n",
       "<td>2017-03-21</td>\n",
       "<td>F</td></tr><tr><td>18</td>\n",
       "<td>2017-05-01</td>\n",
       "<td>F</td></tr><tr><td>22</td>\n",
       "<td>2019-12-15</td>\n",
       "<td>F</td></tr><tr><td>100</td>\n",
       "<td>2017-05-12</td>\n",
       "<td>F</td> </tr> </tbody>\n",
       "            </table>\n",
       "            \n",
       "            <p>Total: 7</p></div>\n",
       "            "
      ],
      "text/plain": [
       "*mouse_id    dob            sex    \n",
       "+----------+ +------------+ +-----+\n",
       "5            2016-12-25     F      \n",
       "10           2017-01-01     F      \n",
       "11           2017-01-03     F      \n",
       "12           2017-03-21     F      \n",
       "18           2017-05-01     F      \n",
       "22           2019-12-15     F      \n",
       "100          2017-05-12     F      \n",
       " (Total: 7)"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "female_mice = Mouse & 'sex = \"F\"'\n",
    "female_mice"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "and among these mice, find ones with **mouse_id > 10**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "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",
       "\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>Experimental animals</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\">mouse_id</p>\n",
       "                                <span class=\"djtooltiptext\">Unique animal ID</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">dob</p>\n",
       "                                <span class=\"djtooltiptext\">date of birth</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">sex</p>\n",
       "                                <span class=\"djtooltiptext\">sex</span>\n",
       "                            </div> </th> </tr> </thead>\n",
       "                <tbody> <tr> <td>11</td>\n",
       "<td>2017-01-03</td>\n",
       "<td>F</td></tr><tr><td>12</td>\n",
       "<td>2017-03-21</td>\n",
       "<td>F</td></tr><tr><td>18</td>\n",
       "<td>2017-05-01</td>\n",
       "<td>F</td></tr><tr><td>22</td>\n",
       "<td>2019-12-15</td>\n",
       "<td>F</td></tr><tr><td>100</td>\n",
       "<td>2017-05-12</td>\n",
       "<td>F</td> </tr> </tbody>\n",
       "            </table>\n",
       "            \n",
       "            <p>Total: 5</p></div>\n",
       "            "
      ],
      "text/plain": [
       "*mouse_id    dob            sex    \n",
       "+----------+ +------------+ +-----+\n",
       "11           2017-01-03     F      \n",
       "12           2017-03-21     F      \n",
       "18           2017-05-01     F      \n",
       "22           2019-12-15     F      \n",
       "100          2017-05-12     F      \n",
       " (Total: 5)"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "female_mice & 'mouse_id > 10'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In computer science/math lingo, DataJoint operations are said to **satisfy closure property**. Practically speaking, this means that the result of a query can immediately be used in another query, allowing you to build more complex queries from simpler ones. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Restriction one table with another"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "All mice that has a session"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "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",
       "\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>Experimental animals</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\">mouse_id</p>\n",
       "                                <span class=\"djtooltiptext\">Unique animal ID</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">dob</p>\n",
       "                                <span class=\"djtooltiptext\">date of birth</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">sex</p>\n",
       "                                <span class=\"djtooltiptext\">sex</span>\n",
       "                            </div> </th> </tr> </thead>\n",
       "                <tbody> <tr> <td>0</td>\n",
       "<td>2017-03-01</td>\n",
       "<td>M</td></tr><tr><td>18</td>\n",
       "<td>2017-05-01</td>\n",
       "<td>F</td> </tr> </tbody>\n",
       "            </table>\n",
       "            \n",
       "            <p>Total: 2</p></div>\n",
       "            "
      ],
      "text/plain": [
       "*mouse_id    dob            sex    \n",
       "+----------+ +------------+ +-----+\n",
       "0            2017-03-01     M      \n",
       "18           2017-05-01     F      \n",
       " (Total: 2)"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Mouse & Session "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Combining restrictions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "All the above queries could be combined "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Male mice that had a session"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "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",
       "\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>Experimental animals</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\">mouse_id</p>\n",
       "                                <span class=\"djtooltiptext\">Unique animal ID</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">dob</p>\n",
       "                                <span class=\"djtooltiptext\">date of birth</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">sex</p>\n",
       "                                <span class=\"djtooltiptext\">sex</span>\n",
       "                            </div> </th> </tr> </thead>\n",
       "                <tbody> <tr> <td>0</td>\n",
       "<td>2017-03-01</td>\n",
       "<td>M</td> </tr> </tbody>\n",
       "            </table>\n",
       "            \n",
       "            <p>Total: 1</p></div>\n",
       "            "
      ],
      "text/plain": [
       "*mouse_id    dob            sex    \n",
       "+----------+ +------------+ +-----+\n",
       "0            2017-03-01     M      \n",
       " (Total: 1)"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Mouse & Session & 'sex = \"M\"'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Give me all mice that have had an experimental session done on or before 2017-05-19"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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",
       "\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>Experimental animals</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\">mouse_id</p>\n",
       "                                <span class=\"djtooltiptext\">Unique animal ID</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">dob</p>\n",
       "                                <span class=\"djtooltiptext\">date of birth</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">sex</p>\n",
       "                                <span class=\"djtooltiptext\">sex</span>\n",
       "                            </div> </th> </tr> </thead>\n",
       "                <tbody> <tr> <td>0</td>\n",
       "<td>2017-03-01</td>\n",
       "<td>M</td> </tr> </tbody>\n",
       "            </table>\n",
       "            \n",
       "            <p>Total: 1</p></div>\n",
       "            "
      ],
      "text/plain": [
       "*mouse_id    dob            sex    \n",
       "+----------+ +------------+ +-----+\n",
       "0            2017-03-01     M      \n",
       " (Total: 1)"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Mouse & (Session & 'session_date <= \"2017-05-19\"')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Negative restriction - with the `-` operator"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "All mice that do not have any session"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "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",
       "\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>Experimental animals</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\">mouse_id</p>\n",
       "                                <span class=\"djtooltiptext\">Unique animal ID</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">dob</p>\n",
       "                                <span class=\"djtooltiptext\">date of birth</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">sex</p>\n",
       "                                <span class=\"djtooltiptext\">sex</span>\n",
       "                            </div> </th> </tr> </thead>\n",
       "                <tbody> <tr> <td>1</td>\n",
       "<td>2016-11-19</td>\n",
       "<td>M</td></tr><tr><td>2</td>\n",
       "<td>2016-11-20</td>\n",
       "<td>unknown</td></tr><tr><td>5</td>\n",
       "<td>2016-12-25</td>\n",
       "<td>F</td></tr><tr><td>10</td>\n",
       "<td>2017-01-01</td>\n",
       "<td>F</td></tr><tr><td>11</td>\n",
       "<td>2017-01-03</td>\n",
       "<td>F</td></tr><tr><td>12</td>\n",
       "<td>2017-03-21</td>\n",
       "<td>F</td></tr><tr><td>19</td>\n",
       "<td>2018-07-21</td>\n",
       "<td>M</td></tr><tr><td>22</td>\n",
       "<td>2019-12-15</td>\n",
       "<td>F</td></tr><tr><td>34</td>\n",
       "<td>2018-09-22</td>\n",
       "<td>M</td></tr><tr><td>100</td>\n",
       "<td>2017-05-12</td>\n",
       "<td>F</td> </tr> </tbody>\n",
       "            </table>\n",
       "            \n",
       "            <p>Total: 10</p></div>\n",
       "            "
      ],
      "text/plain": [
       "*mouse_id    dob            sex        \n",
       "+----------+ +------------+ +---------+\n",
       "1            2016-11-19     M          \n",
       "2            2016-11-20     unknown    \n",
       "5            2016-12-25     F          \n",
       "10           2017-01-01     F          \n",
       "11           2017-01-03     F          \n",
       "12           2017-03-21     F          \n",
       "19           2018-07-21     M          \n",
       "22           2019-12-15     F          \n",
       "34           2018-09-22     M          \n",
       "100          2017-05-12     F          \n",
       " (Total: 10)"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Mouse - Session"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Male mice that have do not have any session"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "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",
       "\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>Experimental animals</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\">mouse_id</p>\n",
       "                                <span class=\"djtooltiptext\">Unique animal ID</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">dob</p>\n",
       "                                <span class=\"djtooltiptext\">date of birth</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">sex</p>\n",
       "                                <span class=\"djtooltiptext\">sex</span>\n",
       "                            </div> </th> </tr> </thead>\n",
       "                <tbody> <tr> <td>1</td>\n",
       "<td>2016-11-19</td>\n",
       "<td>M</td></tr><tr><td>19</td>\n",
       "<td>2018-07-21</td>\n",
       "<td>M</td></tr><tr><td>34</td>\n",
       "<td>2018-09-22</td>\n",
       "<td>M</td> </tr> </tbody>\n",
       "            </table>\n",
       "            \n",
       "            <p>Total: 3</p></div>\n",
       "            "
      ],
      "text/plain": [
       "*mouse_id    dob            sex    \n",
       "+----------+ +------------+ +-----+\n",
       "1            2016-11-19     M      \n",
       "19           2018-07-21     M      \n",
       "34           2018-09-22     M      \n",
       " (Total: 3)"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# ENTER YOUR CODE\n",
    "(Mouse - Session) & 'sex = \"M\"'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Joining (*)  - bring fields from different tables together"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Sometimes you want to see information from multiple tables combined together to be viewed (and queried!) simultaneously. You can do this using the join `*` operator."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Behavior of join:\n",
    "\n",
    "1. match the common field(s) of the primary keys in the two tables\n",
    "2. do a combination of the non-matched part of the primary key\n",
    "3. listing out the secondary attributes for each combination\n",
    "4. if two tables have secondary attributes that share a same name, it will throw an error. To join, we need to rename that attribute for at least one of the tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "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",
       "\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\">mouse_id</p>\n",
       "                                <span class=\"djtooltiptext\">Unique animal ID</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"primary\">session_date</p>\n",
       "                                <span class=\"djtooltiptext\">date</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">dob</p>\n",
       "                                <span class=\"djtooltiptext\">date of birth</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">sex</p>\n",
       "                                <span class=\"djtooltiptext\">sex</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">experiment_setup</p>\n",
       "                                <span class=\"djtooltiptext\">experiment setup ID</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">experimenter</p>\n",
       "                                <span class=\"djtooltiptext\">experimenter name</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">data_path</p>\n",
       "                                <span class=\"djtooltiptext\"></span>\n",
       "                            </div> </th> </tr> </thead>\n",
       "                <tbody> <tr> <td>0</td>\n",
       "<td>2017-05-15</td>\n",
       "<td>2017-03-01</td>\n",
       "<td>M</td>\n",
       "<td>0</td>\n",
       "<td>Edgar Y. Walker</td>\n",
       "<td></td></tr><tr><td>0</td>\n",
       "<td>2018-01-15</td>\n",
       "<td>2017-03-01</td>\n",
       "<td>M</td>\n",
       "<td>100</td>\n",
       "<td>Jacob Reimer</td>\n",
       "<td></td></tr><tr><td>18</td>\n",
       "<td>2018-01-15</td>\n",
       "<td>2017-05-01</td>\n",
       "<td>F</td>\n",
       "<td>101</td>\n",
       "<td>Jacob Reimer</td>\n",
       "<td></td> </tr> </tbody>\n",
       "            </table>\n",
       "            \n",
       "            <p>Total: 3</p></div>\n",
       "            "
      ],
      "text/plain": [
       "*mouse_id    *session_date  dob            sex     experiment_set experimenter   data_path    \n",
       "+----------+ +------------+ +------------+ +-----+ +------------+ +------------+ +-----------+\n",
       "0            2017-05-15     2017-03-01     M       0              Edgar Y. Walke              \n",
       "0            2018-01-15     2017-03-01     M       100            Jacob Reimer                \n",
       "18           2018-01-15     2017-05-01     F       101            Jacob Reimer                \n",
       " (Total: 3)"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# looking at the combination of mouse and session\n",
    "Mouse * Session"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here each row represents a unique (and valid!) combination of a mouse and a session."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The combined table can be queried using any of the attributes (columns) present in the joined tables:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "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",
       "\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\">mouse_id</p>\n",
       "                                <span class=\"djtooltiptext\">Unique animal ID</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"primary\">session_date</p>\n",
       "                                <span class=\"djtooltiptext\">date</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">dob</p>\n",
       "                                <span class=\"djtooltiptext\">date of birth</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">sex</p>\n",
       "                                <span class=\"djtooltiptext\">sex</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">experiment_setup</p>\n",
       "                                <span class=\"djtooltiptext\">experiment setup ID</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">experimenter</p>\n",
       "                                <span class=\"djtooltiptext\">experimenter name</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">data_path</p>\n",
       "                                <span class=\"djtooltiptext\"></span>\n",
       "                            </div> </th> </tr> </thead>\n",
       "                <tbody> <tr> <td>0</td>\n",
       "<td>2018-01-15</td>\n",
       "<td>2017-03-01</td>\n",
       "<td>M</td>\n",
       "<td>100</td>\n",
       "<td>Jacob Reimer</td>\n",
       "<td></td> </tr> </tbody>\n",
       "            </table>\n",
       "            \n",
       "            <p>Total: 1</p></div>\n",
       "            "
      ],
      "text/plain": [
       "*mouse_id    *session_date  dob            sex     experiment_set experimenter   data_path    \n",
       "+----------+ +------------+ +------------+ +-----+ +------------+ +------------+ +-----------+\n",
       "0            2018-01-15     2017-03-01     M       100            Jacob Reimer                \n",
       " (Total: 1)"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Find 'experimenter = \"Jacob Reimer\"' and 'sex = \"M\"'\n",
    "Mouse * Session & 'experimenter = \"Jacob Reimer\"' & 'sex = \"M\"'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "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",
       "\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\">mouse_id</p>\n",
       "                                <span class=\"djtooltiptext\">Unique animal ID</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"primary\">session_date</p>\n",
       "                                <span class=\"djtooltiptext\">date</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">dob</p>\n",
       "                                <span class=\"djtooltiptext\">date of birth</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">sex</p>\n",
       "                                <span class=\"djtooltiptext\">sex</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">experiment_setup</p>\n",
       "                                <span class=\"djtooltiptext\">experiment setup ID</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">experimenter</p>\n",
       "                                <span class=\"djtooltiptext\">experimenter name</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">data_path</p>\n",
       "                                <span class=\"djtooltiptext\"></span>\n",
       "                            </div> </th> </tr> </thead>\n",
       "                <tbody> <tr> <td>0</td>\n",
       "<td>2018-01-15</td>\n",
       "<td>2017-03-01</td>\n",
       "<td>M</td>\n",
       "<td>100</td>\n",
       "<td>Jacob Reimer</td>\n",
       "<td></td></tr><tr><td>18</td>\n",
       "<td>2018-01-15</td>\n",
       "<td>2017-05-01</td>\n",
       "<td>F</td>\n",
       "<td>101</td>\n",
       "<td>Jacob Reimer</td>\n",
       "<td></td> </tr> </tbody>\n",
       "            </table>\n",
       "            \n",
       "            <p>Total: 2</p></div>\n",
       "            "
      ],
      "text/plain": [
       "*mouse_id    *session_date  dob            sex     experiment_set experimenter   data_path    \n",
       "+----------+ +------------+ +------------+ +-----+ +------------+ +------------+ +-----------+\n",
       "0            2018-01-15     2017-03-01     M       100            Jacob Reimer                \n",
       "18           2018-01-15     2017-05-01     F       101            Jacob Reimer                \n",
       " (Total: 2)"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Mouse * Session & 'session_date > \"2017-05-19\"'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Projection .proj(): focus on attributes of interest\n",
    "Beside restriction (`&`) and join (`*`) operations, DataJoint offers another type of operation: projection (`.proj()`). Projection is used to select attributes (columns) from a table, to rename them, or to create new calculated attributes. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "From the ***Mouse*** table, suppose we want to focus only on the `sex` attribute and ignore the others, this can be done as:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "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",
       "\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\">mouse_id</p>\n",
       "                                <span class=\"djtooltiptext\">Unique animal ID</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">sex</p>\n",
       "                                <span class=\"djtooltiptext\">sex</span>\n",
       "                            </div> </th> </tr> </thead>\n",
       "                <tbody> <tr> <td>0</td>\n",
       "<td>M</td></tr><tr><td>1</td>\n",
       "<td>M</td></tr><tr><td>2</td>\n",
       "<td>unknown</td></tr><tr><td>5</td>\n",
       "<td>F</td></tr><tr><td>10</td>\n",
       "<td>F</td></tr><tr><td>11</td>\n",
       "<td>F</td></tr><tr><td>12</td>\n",
       "<td>F</td></tr><tr><td>18</td>\n",
       "<td>F</td></tr><tr><td>19</td>\n",
       "<td>M</td></tr><tr><td>22</td>\n",
       "<td>F</td></tr><tr><td>34</td>\n",
       "<td>M</td> </tr> </tbody>\n",
       "            </table>\n",
       "            \n",
       "            <p>Total: 11</p></div>\n",
       "            "
      ],
      "text/plain": [
       "*mouse_id    sex        \n",
       "+----------+ +---------+\n",
       "0            M          \n",
       "1            M          \n",
       "2            unknown    \n",
       "5            F          \n",
       "10           F          \n",
       "11           F          \n",
       "12           F          \n",
       "18           F          \n",
       "19           M          \n",
       "22           F          \n",
       "34           M          \n",
       " (Total: 11)"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Mouse.proj('sex')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that `.proj()` will always retain all attributes that are part of the primary key"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Rename attribute with proj()\n",
    "Say we want to rename the existing attribute `dob` of the `Mouse` table to `date_of_birth`, this can be done using `.proj()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "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",
       "\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\">mouse_id</p>\n",
       "                                <span class=\"djtooltiptext\">Unique animal ID</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">date_of_birth</p>\n",
       "                                <span class=\"djtooltiptext\">date of birth</span>\n",
       "                            </div> </th> </tr> </thead>\n",
       "                <tbody> <tr> <td>0</td>\n",
       "<td>2017-03-01</td></tr><tr><td>1</td>\n",
       "<td>2016-11-19</td></tr><tr><td>2</td>\n",
       "<td>2016-11-20</td></tr><tr><td>5</td>\n",
       "<td>2016-12-25</td></tr><tr><td>10</td>\n",
       "<td>2017-01-01</td></tr><tr><td>11</td>\n",
       "<td>2017-01-03</td></tr><tr><td>12</td>\n",
       "<td>2017-03-21</td></tr><tr><td>18</td>\n",
       "<td>2017-05-01</td></tr><tr><td>19</td>\n",
       "<td>2018-07-21</td></tr><tr><td>22</td>\n",
       "<td>2019-12-15</td></tr><tr><td>34</td>\n",
       "<td>2018-09-22</td></tr><tr><td>100</td>\n",
       "<td>2017-05-12</td> </tr> </tbody>\n",
       "            </table>\n",
       "            \n",
       "            <p>Total: 12</p></div>\n",
       "            "
      ],
      "text/plain": [
       "*mouse_id    date_of_birth \n",
       "+----------+ +------------+\n",
       "0            2017-03-01    \n",
       "1            2016-11-19    \n",
       "2            2016-11-20    \n",
       "5            2016-12-25    \n",
       "10           2017-01-01    \n",
       "11           2017-01-03    \n",
       "12           2017-03-21    \n",
       "18           2017-05-01    \n",
       "19           2018-07-21    \n",
       "22           2019-12-15    \n",
       "34           2018-09-22    \n",
       "100          2017-05-12    \n",
       " (Total: 12)"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Mouse.proj(date_of_birth='dob')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Perform simple computations with proj()\n",
    "Projection is perhaps most useful to perform simple computations on the attributes, especially on attributes from multiple tables by using in conjunction with the join (`*`) operation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "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",
       "\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\">mouse_id</p>\n",
       "                                <span class=\"djtooltiptext\">Unique animal ID</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"primary\">session_date</p>\n",
       "                                <span class=\"djtooltiptext\">date</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">age</p>\n",
       "                                <span class=\"djtooltiptext\">calculated attribute</span>\n",
       "                            </div> </th> </tr> </thead>\n",
       "                <tbody> <tr> <td>0</td>\n",
       "<td>2017-05-15</td>\n",
       "<td>75</td></tr><tr><td>0</td>\n",
       "<td>2018-01-15</td>\n",
       "<td>320</td></tr><tr><td>18</td>\n",
       "<td>2018-01-15</td>\n",
       "<td>259</td> </tr> </tbody>\n",
       "            </table>\n",
       "            \n",
       "            <p>Total: 3</p></div>\n",
       "            "
      ],
      "text/plain": [
       "*mouse_id    *session_date  age    \n",
       "+----------+ +------------+ +-----+\n",
       "0            2017-05-15     75     \n",
       "0            2018-01-15     320    \n",
       "18           2018-01-15     259    \n",
       " (Total: 3)"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(Mouse * Session).proj(age='datediff(session_date, dob)')"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note: as you can see, the projection results keep the primary attributes from the `Mouse * Session` joining operation, while removing all other non-primary attributes. To Keep all other attributes, you can use the `...` syntax"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "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",
       "\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\">mouse_id</p>\n",
       "                                <span class=\"djtooltiptext\">Unique animal ID</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"primary\">session_date</p>\n",
       "                                <span class=\"djtooltiptext\">date</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">dob</p>\n",
       "                                <span class=\"djtooltiptext\">date of birth</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">sex</p>\n",
       "                                <span class=\"djtooltiptext\">sex</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">experiment_setup</p>\n",
       "                                <span class=\"djtooltiptext\">experiment setup ID</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">experimenter</p>\n",
       "                                <span class=\"djtooltiptext\">experimenter name</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">data_path</p>\n",
       "                                <span class=\"djtooltiptext\"></span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">age</p>\n",
       "                                <span class=\"djtooltiptext\">calculated attribute</span>\n",
       "                            </div> </th> </tr> </thead>\n",
       "                <tbody> <tr> <td>0</td>\n",
       "<td>2017-05-15</td>\n",
       "<td>2017-03-01</td>\n",
       "<td>M</td>\n",
       "<td>0</td>\n",
       "<td>Edgar Y. Walker</td>\n",
       "<td></td>\n",
       "<td>75</td></tr><tr><td>0</td>\n",
       "<td>2018-01-15</td>\n",
       "<td>2017-03-01</td>\n",
       "<td>M</td>\n",
       "<td>100</td>\n",
       "<td>Jacob Reimer</td>\n",
       "<td></td>\n",
       "<td>320</td></tr><tr><td>18</td>\n",
       "<td>2018-01-15</td>\n",
       "<td>2017-05-01</td>\n",
       "<td>F</td>\n",
       "<td>101</td>\n",
       "<td>Jacob Reimer</td>\n",
       "<td></td>\n",
       "<td>259</td> </tr> </tbody>\n",
       "            </table>\n",
       "            \n",
       "            <p>Total: 3</p></div>\n",
       "            "
      ],
      "text/plain": [
       "*mouse_id    *session_date  dob            sex     experiment_set experimenter   data_path     age    \n",
       "+----------+ +------------+ +------------+ +-----+ +------------+ +------------+ +-----------+ +-----+\n",
       "0            2017-05-15     2017-03-01     M       0              Edgar Y. Walke               75     \n",
       "0            2018-01-15     2017-03-01     M       100            Jacob Reimer                 320    \n",
       "18           2018-01-15     2017-05-01     F       101            Jacob Reimer                 259    \n",
       " (Total: 3)"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(Mouse * Session).proj(..., age='datediff(session_date, dob)')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Fetch data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once you have successfully narrowed down to the entities you want, you can fetch the query results just by calling fetch on it!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Fetch one or multiple entries: `fetch()`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "All male mouse"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "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",
       "\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>Experimental animals</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\">mouse_id</p>\n",
       "                                <span class=\"djtooltiptext\">Unique animal ID</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">dob</p>\n",
       "                                <span class=\"djtooltiptext\">date of birth</span>\n",
       "                            </div></th><th><div class=\"djtooltip\">\n",
       "                                <p id=\"nonprimary\">sex</p>\n",
       "                                <span class=\"djtooltiptext\">sex</span>\n",
       "                            </div> </th> </tr> </thead>\n",
       "                <tbody> <tr> <td>0</td>\n",
       "<td>2017-03-01</td>\n",
       "<td>M</td></tr><tr><td>1</td>\n",
       "<td>2016-11-19</td>\n",
       "<td>M</td></tr><tr><td>19</td>\n",
       "<td>2018-07-21</td>\n",
       "<td>M</td></tr><tr><td>34</td>\n",
       "<td>2018-09-22</td>\n",
       "<td>M</td> </tr> </tbody>\n",
       "            </table>\n",
       "            \n",
       "            <p>Total: 4</p></div>\n",
       "            "
      ],
      "text/plain": [
       "*mouse_id    dob            sex    \n",
       "+----------+ +------------+ +-----+\n",
       "0            2017-03-01     M      \n",
       "1            2016-11-19     M      \n",
       "19           2018-07-21     M      \n",
       "34           2018-09-22     M      \n",
       " (Total: 4)"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "male_mouse = Mouse & 'sex = \"M\"'\n",
    "male_mouse"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Fetch it!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([( 0, datetime.date(2017, 3, 1), 'M'),\n",
       "       ( 1, datetime.date(2016, 11, 19), 'M'),\n",
       "       (19, datetime.date(2018, 7, 21), 'M'),\n",
       "       (34, datetime.date(2018, 9, 22), 'M')],\n",
       "      dtype=[('mouse_id', '<i8'), ('dob', 'O'), ('sex', 'O')])"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "male_mouse.fetch()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "or all in one step"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([( 0, datetime.date(2017, 3, 1), 'M'),\n",
       "       ( 1, datetime.date(2016, 11, 19), 'M'),\n",
       "       (19, datetime.date(2018, 7, 21), 'M'),\n",
       "       (34, datetime.date(2018, 9, 22), 'M')],\n",
       "      dtype=[('mouse_id', '<i8'), ('dob', 'O'), ('sex', 'O')])"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(Mouse & 'sex = \"M\"').fetch()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Fetch as a list of dictionaries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'mouse_id': 0, 'dob': datetime.date(2017, 3, 1), 'sex': 'M'},\n",
       " {'mouse_id': 1, 'dob': datetime.date(2016, 11, 19), 'sex': 'M'},\n",
       " {'mouse_id': 19, 'dob': datetime.date(2018, 7, 21), 'sex': 'M'},\n",
       " {'mouse_id': 34, 'dob': datetime.date(2018, 9, 22), 'sex': 'M'}]"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(Mouse & 'sex = \"M\"').fetch(as_dict=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Fetch as a pandas dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>dob</th>\n",
       "      <th>sex</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mouse_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017-03-01</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016-11-19</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2018-07-21</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>2018-09-22</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 dob sex\n",
       "mouse_id                \n",
       "0         2017-03-01   M\n",
       "1         2016-11-19   M\n",
       "19        2018-07-21   M\n",
       "34        2018-09-22   M"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(Mouse & 'sex = \"M\"').fetch(format='frame')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Fetch the primary key"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'mouse_id': 0}, {'mouse_id': 1}, {'mouse_id': 19}, {'mouse_id': 34}]"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(Mouse & 'sex = \"M\"').fetch('KEY')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Fetch specific fields"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "sex, dob = Mouse.fetch('sex', 'dob')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['M', 'M', 'unknown', 'F', 'F', 'F', 'F', 'F', 'M', 'F', 'M', 'F'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sex"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([datetime.date(2017, 3, 1), datetime.date(2016, 11, 19),\n",
       "       datetime.date(2016, 11, 20), datetime.date(2016, 12, 25),\n",
       "       datetime.date(2017, 1, 1), datetime.date(2017, 1, 3),\n",
       "       datetime.date(2017, 3, 21), datetime.date(2017, 5, 1),\n",
       "       datetime.date(2018, 7, 21), datetime.date(2019, 12, 15),\n",
       "       datetime.date(2018, 9, 22), datetime.date(2017, 5, 12)],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dob"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Or fetch them together as a list of dictionaries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'dob': datetime.date(2017, 3, 1), 'sex': 'M'},\n",
       " {'dob': datetime.date(2016, 11, 19), 'sex': 'M'},\n",
       " {'dob': datetime.date(2016, 11, 20), 'sex': 'unknown'},\n",
       " {'dob': datetime.date(2016, 12, 25), 'sex': 'F'},\n",
       " {'dob': datetime.date(2017, 1, 1), 'sex': 'F'},\n",
       " {'dob': datetime.date(2017, 1, 3), 'sex': 'F'},\n",
       " {'dob': datetime.date(2017, 3, 21), 'sex': 'F'},\n",
       " {'dob': datetime.date(2017, 5, 1), 'sex': 'F'},\n",
       " {'dob': datetime.date(2018, 7, 21), 'sex': 'M'},\n",
       " {'dob': datetime.date(2019, 12, 15), 'sex': 'F'},\n",
       " {'dob': datetime.date(2018, 9, 22), 'sex': 'M'},\n",
       " {'dob': datetime.date(2017, 5, 12), 'sex': 'F'}]"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "info = Mouse.fetch('sex', 'dob', as_dict=True)\n",
    "info"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Fetch data from only one entry: `fetch1()`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When knowing there's only 1 result to be fetched back, we can use `.fetch1()`. `fetch1` will always return the fetched result in a dictionary format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'mouse_id': 0, 'dob': datetime.date(2017, 3, 1), 'sex': 'M'}"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mouse_0 = (Mouse & {'mouse_id': 0}).fetch1()  # \"fetch1()\" because we know there's only one\n",
    "mouse_0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`fetch1()` could also fetch the primary key"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'mouse_id': 0}"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(Mouse & {'mouse_id': 0}).fetch1('KEY')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "or fetch specific fields:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "sex, dob = (Mouse & {'mouse_id': 0}).fetch1('sex', 'dob')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'M'"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sex"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.date(2017, 3, 1)"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dob"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Deletion (`.delete()`) - deleting entries and their dependencies"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we have a good idea on how to restrict table entries, this is a good time to introduce how to **delete** entries from a table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To delete a specific entry, you restrict the table down to the target entry, and call `delete` method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "About to delete:\n",
      "`shan_tutorial`.`mouse`: 1 items\n",
      "Proceed? [yes, No]: yes\n",
      "Committed.\n"
     ]
    }
   ],
   "source": [
    "(Mouse & 'mouse_id = 100').delete()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calling `delete` method on an *unrestricted* table will attempt to delete the whole table!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "About to delete:\n",
      "`shan_tutorial`.`mouse`: 11 items\n",
      "Proceed? [yes, No]: No\n",
      "Cancelled deletes.\n"
     ]
    }
   ],
   "source": [
    "Mouse.delete()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Summary"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Congratulations! You have successfully created your first DatJoint pipeline, using dependencies to establish the link between the tables. You have also learned to query and fetch the data.\n",
    "\n",
    "In the next session, we are going to extend our data pipeline with tables to represent **imported data** and define new tables to **compute and hold analysis results**.\n",
    "\n",
    "We will use both ephys and calcium imaging as example pipelines.\n",
    "+ [02-electrophysiology](./02-electrophysiology-%20Impo%20Tables%20-%20Completed.ipynb)\n",
    "+ [02-calcium imaging](./02-calcium_imaging%20-%20Imported%20Tables%20-%20Completed.ipynb)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}