jl/demo/citydb-with-rabbit.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Querying the database of Chicago employees"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The database\n",
    "We start with loading a sample database.  Our sample database is derived from the dataset of all employees of the city of Chicago ([source](https://data.cityofchicago.org/Administration-Finance/Current-Employee-Names-Salaries-and-Position-Title/xzkq-xp2w))."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Department:\n",
       "  name :: UTF8String # unique\n",
       "  employee (inverse of Employee.department) :: Array{Employee,1} # unique, covering\n",
       "Employee:\n",
       "  name :: UTF8String\n",
       "  surname :: UTF8String\n",
       "  position :: UTF8String\n",
       "  salary :: Int64\n",
       "  department :: Department\n",
       "  managed_by :: Nullable{Employee}\n",
       "  manages (inverse of Employee.managed_by) :: Array{Employee,1} # unique"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "include(\"../citydb.jl\")\n",
    "\n",
    "using RBT\n",
    "setdb(citydb)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can execute a query using `@query()` command:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "42"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(6*(3+4))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Traversing the database structure\n",
    "*Find the names of all departments.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "35-element Array{UTF8String,1}:\n",
       " \"WATER MGMNT\"      \n",
       " \"POLICE\"           \n",
       " \"GENERAL SERVICES\" \n",
       " \"CITY COUNCIL\"     \n",
       " \"STREETS & SAN\"    \n",
       " ⋮                  \n",
       " \"BOARD OF ETHICS\"  \n",
       " \"POLICE BOARD\"     \n",
       " \"BUDGET & MGMT\"    \n",
       " \"ADMIN HEARNG\"     \n",
       " \"LICENSE APPL COMM\""
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(department.name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Find the names of all employees.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "32181-element Array{UTF8String,1}:\n",
       " \"ELVIA\"     \n",
       " \"VICENTE\"   \n",
       " \"MUHAMMAD\"  \n",
       " \"GIRLEY\"    \n",
       " \"DILAN\"     \n",
       " ⋮           \n",
       " \"NANCY\"     \n",
       " \"DARCI\"     \n",
       " \"THADDEUS\"  \n",
       " \"RACHENETTE\"\n",
       " \"MICHELLE\"  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(department.employee.name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We are not longer restricted by the hierarchical structure of the database, so we can query employees directly."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "32181-element Array{UTF8String,1}:\n",
       " \"ELVIA\"    \n",
       " \"JEFFERY\"  \n",
       " \"KARINA\"   \n",
       " \"KIMBERLEI\"\n",
       " \"VICENTE\"  \n",
       " ⋮          \n",
       " \"MICHAEL\"  \n",
       " \"PETER\"    \n",
       " \"MARK\"     \n",
       " \"CARLO\"    \n",
       " \"DARIUSZ\"  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(employee.name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can traverse the schema in any direction, for instance, from employees to their departments."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "32181-element Array{UTF8String,1}:\n",
       " \"WATER MGMNT\"     \n",
       " \"POLICE\"          \n",
       " \"POLICE\"          \n",
       " \"GENERAL SERVICES\"\n",
       " \"WATER MGMNT\"     \n",
       " ⋮                 \n",
       " \"GENERAL SERVICES\"\n",
       " \"POLICE\"          \n",
       " \"POLICE\"          \n",
       " \"POLICE\"          \n",
       " \"DoIT\"            "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(employee.department.name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Show the list of all salaries.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "32181-element Array{Int64,1}:\n",
       "  88968\n",
       "  80778\n",
       "  80778\n",
       "  84780\n",
       " 104736\n",
       "      ⋮\n",
       "  97448\n",
       "  86520\n",
       "  83616\n",
       "  86520\n",
       " 110352"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(employee.salary)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If the traversal ends at an entity class, an array of records is generated."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "32181-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}:\n",
       " (\"ELVIA\",\"A\",\"WATER MGMNT\",\"WATER RATE TAKER\",88968)                     \n",
       " (\"JEFFERY\",\"A\",\"POLICE\",\"POLICE OFFICER\",80778)                          \n",
       " (\"KARINA\",\"A\",\"POLICE\",\"POLICE OFFICER\",80778)                           \n",
       " (\"KIMBERLEI\",\"A\",\"GENERAL SERVICES\",\"CHIEF CONTRACT EXPEDITER\",84780)    \n",
       " (\"VICENTE\",\"A\",\"WATER MGMNT\",\"CIVIL ENGINEER IV\",104736)                 \n",
       " ⋮                                                                        \n",
       " (\"MICHAEL\",\"Z\",\"GENERAL SERVICES\",\"FRM OF MACHINISTS - AUTOMOTIVE\",97448)\n",
       " (\"PETER\",\"Z\",\"POLICE\",\"POLICE OFFICER\",86520)                            \n",
       " (\"MARK\",\"Z\",\"POLICE\",\"POLICE OFFICER\",83616)                             \n",
       " (\"CARLO\",\"Z\",\"POLICE\",\"POLICE OFFICER\",86520)                            \n",
       " (\"DARIUSZ\",\"Z\",\"DoIT\",\"CHIEF DATA BASE ANALYST\",110352)                  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(employee)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Which fields are selected depends on the path to the class."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "32181-element Array{Tuple{UTF8String,UTF8String},1}:\n",
       " (\"ELVIA\",\"A\")     \n",
       " (\"VICENTE\",\"A\")   \n",
       " (\"MUHAMMAD\",\"A\")  \n",
       " (\"GIRLEY\",\"A\")    \n",
       " (\"DILAN\",\"A\")     \n",
       " ⋮                 \n",
       " (\"NANCY\",\"T\")     \n",
       " (\"DARCI\",\"W\")     \n",
       " (\"THADDEUS\",\"W\")  \n",
       " (\"RACHENETTE\",\"W\")\n",
       " (\"MICHELLE\",\"G\")  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(department.employee)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Summarizing data\n",
    "*Find the number of departments.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "35"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(count(department))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Find the number of employees for each department.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "35-element Array{Int64,1}:\n",
       "  1848\n",
       " 13570\n",
       "   924\n",
       "   397\n",
       "  2090\n",
       "     ⋮\n",
       "     9\n",
       "     2\n",
       "    43\n",
       "    39\n",
       "     1"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(department.count(employee))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Find the total number of employees.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "32181"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(count(department.employee))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Again, we can query `employee` directly."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "32181"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(count(employee))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Find the top salary among all employees.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Nullable(260004)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(max(employee.salary))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Find the maximum number of employees per department.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Nullable(13570)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(max(department.count(employee)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Selecting output columns\n",
    "*For each department, find the number of employees.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "35-element Array{Tuple{UTF8String,Int64},1}:\n",
       " (\"WATER MGMNT\",1848)    \n",
       " (\"POLICE\",13570)        \n",
       " (\"GENERAL SERVICES\",924)\n",
       " (\"CITY COUNCIL\",397)    \n",
       " (\"STREETS & SAN\",2090)  \n",
       " ⋮                       \n",
       " (\"BOARD OF ETHICS\",9)   \n",
       " (\"POLICE BOARD\",2)      \n",
       " (\"BUDGET & MGMT\",43)    \n",
       " (\"ADMIN HEARNG\",39)     \n",
       " (\"LICENSE APPL COMM\",1) "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(department:select(name,count(employee)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `:select` notation is a syntax sugar for regular function call where the first argument is placed before the function name (postfix notation)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "35-element Array{Tuple{UTF8String,Int64},1}:\n",
       " (\"WATER MGMNT\",1848)    \n",
       " (\"POLICE\",13570)        \n",
       " (\"GENERAL SERVICES\",924)\n",
       " (\"CITY COUNCIL\",397)    \n",
       " (\"STREETS & SAN\",2090)  \n",
       " ⋮                       \n",
       " (\"BOARD OF ETHICS\",9)   \n",
       " (\"POLICE BOARD\",2)      \n",
       " (\"BUDGET & MGMT\",43)    \n",
       " (\"ADMIN HEARNG\",39)     \n",
       " (\"LICENSE APPL COMM\",1) "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(select(department,name,count(employee)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is easy to add new columns to the output.  Let us add *the top salary per department.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "35-element Array{Tuple{UTF8String,Int64,Nullable{Int64}},1}:\n",
       " (\"WATER MGMNT\",1848,Nullable(169512))    \n",
       " (\"POLICE\",13570,Nullable(260004))        \n",
       " (\"GENERAL SERVICES\",924,Nullable(157092))\n",
       " (\"CITY COUNCIL\",397,Nullable(160248))    \n",
       " (\"STREETS & SAN\",2090,Nullable(157092))  \n",
       " ⋮                                        \n",
       " (\"BOARD OF ETHICS\",9,Nullable(131688))   \n",
       " (\"POLICE BOARD\",2,Nullable(97728))       \n",
       " (\"BUDGET & MGMT\",43,Nullable(169992))    \n",
       " (\"ADMIN HEARNG\",39,Nullable(156420))     \n",
       " (\"LICENSE APPL COMM\",1,Nullable(69888))  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    department\n",
    "    :select(\n",
    "        name,\n",
    "        count(employee),\n",
    "        max(employee.salary)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Filtering data\n",
    "*Find the employees with salary greater than $200k.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3-element Array{Tuple{UTF8String,UTF8String,UTF8String,Int64},1}:\n",
       " (\"RAHM\",\"E\",\"MAYOR\",216210)                    \n",
       " (\"GARRY\",\"M\",\"SUPERINTENDENT OF POLICE\",260004)\n",
       " (\"JOSE\",\"S\",\"FIRE COMMISSIONER\",202728)        "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    employee\n",
    "    :filter(salary>200000)\n",
    "    :select(name,surname,position,salary))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can apply `:filter()` on any selected column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3-element Array{Tuple{UTF8String,UTF8String,UTF8String,Int64},1}:\n",
       " (\"RAHM\",\"E\",\"MAYOR\",216210)                    \n",
       " (\"GARRY\",\"M\",\"SUPERINTENDENT OF POLICE\",260004)\n",
       " (\"JOSE\",\"S\",\"FIRE COMMISSIONER\",202728)        "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    employee\n",
    "    :select(name,surname,position,salary)\n",
    "    :filter(salary>200000))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Find the number of employees with salary in the range from \\$100k to \\$200k.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3916"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    employee\n",
    "    :filter((salary>100000)&(salary<=200000))\n",
    "    :count)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Find the departments with more than 1000 employees.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n",
      "WARNING: deprecated syntax \"filter(count(employee)>1000) .\" at In[22]:4.\n",
      "Use \"filter(count(employee)>1000).\" instead.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "7-element Array{UTF8String,1}:\n",
       " \"WATER MGMNT\"  \n",
       " \"POLICE\"       \n",
       " \"STREETS & SAN\"\n",
       " \"AVIATION\"     \n",
       " \"FIRE\"         \n",
       " \"OEMC\"         \n",
       " \"TRANSPORTN\"   "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    department\n",
    "    :filter(count(employee)>1000)\n",
    "    .name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Find the number of departments with more than 1000 employees.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    count(\n",
    "        department\n",
    "        :filter(count(employee)>1000)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*For each department, find the number of employees with salary higher than $100k.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "35-element Array{Tuple{UTF8String,Int64},1}:\n",
       " (\"WATER MGMNT\",179)    \n",
       " (\"POLICE\",1493)        \n",
       " (\"GENERAL SERVICES\",79)\n",
       " (\"CITY COUNCIL\",54)    \n",
       " (\"STREETS & SAN\",39)   \n",
       " ⋮                      \n",
       " (\"BOARD OF ETHICS\",2)  \n",
       " (\"POLICE BOARD\",0)     \n",
       " (\"BUDGET & MGMT\",12)   \n",
       " (\"ADMIN HEARNG\",3)     \n",
       " (\"LICENSE APPL COMM\",0)"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    department\n",
    "    :select(\n",
    "        name,\n",
    "        count(employee:filter(salary>100000))))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*For each department with the number of employees less than 1000, find the employees with salary higher than $125k.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "28-element Array{Tuple{UTF8String,Array{Tuple{UTF8String,UTF8String,UTF8String},1}},1}:\n",
       " (\"GENERAL SERVICES\",[(\"DAVID\",\"R\",\"COMMISSIONER OF FLEET & FACILITY MANAGEMENT\"),(\"PHILLIP\",\"S\",\"EQUIPMENT SERVICES COORD\")])                                                            \n",
       " (\"CITY COUNCIL\",[(\"JAMES\",\"C\",\"DEPUTY CHIEF ADMINISTRATIVE OFFICER\"),(\"MARLA\",\"K\",\"CHIEF ADMINISTRATIVE OFFICER\")])                                                                      \n",
       " (\"FAMILY & SUPPORT\",[(\"EVELYN\",\"D\",\"COMMISSIONER OF FAMILY AND SUPPORT SERVICES\"),(\"MARY\",\"G\",\"DEPUTY COMMISSIONER\"),(\"JENNIFER\",\"W\",\"FIRST DEPUTY COMMISSIONER\")])                      \n",
       " (\"IPRA\",[(\"SCOTT\",\"A\",\"CHIEF ADMINISTRATOR\"),(\"STEVEN\",\"H\",\"DEPUTY CHIEF ADMINISTRATOR\"),(\"STEVEN\",\"M\",\"FIRST DEPUTY CHIEF ADMINISTRATOR\"),(\"WILLIAM\",\"W\",\"DEPUTY CHIEF ADMINISTRATOR\")])\n",
       " (\"PUBLIC LIBRARY\",[(\"BRIAN\",\"B\",\"COMMISSIONER OF CHICAGO PUBLIC LIBRARY\"),(\"MICHELLE\",\"F\",\"DIRECTOR OF LIBRARY TECHNOLOGY\"),(\"ANDREA\",\"S\",\"FIRST DEPUTY COMMISSIONER\")])                 \n",
       " ⋮                                                                                                                                                                                        \n",
       " (\"BOARD OF ETHICS\",[(\"STEVEN\",\"B\",\"EXECUTIVE DIR - BOARD OF ETHICS\")])                                                                                                                   \n",
       " (\"POLICE BOARD\",Tuple{UTF8String,UTF8String,UTF8String}[])                                                                                                                               \n",
       " (\"BUDGET & MGMT\",[(\"ALEXANDRA\",\"H\",\"BUDGET DIR\"),(\"RESHMA\",\"S\",\"FIRST DEPUTY BUDGET DIR\")])                                                                                              \n",
       " (\"ADMIN HEARNG\",[(\"PATRICIA\",\"J\",\"DIR OF ADMINISTRATIVE HEARINGS\"),(\"STEVEN\",\"S\",\"DEPUTY DIR OF ADMINISTRATIVE ADJUDICATION\")])                                                          \n",
       " (\"LICENSE APPL COMM\",Tuple{UTF8String,UTF8String,UTF8String}[])                                                                                                                          "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    department\n",
    "    :filter(count(employee)<1000)\n",
    "    :select(\n",
    "        name,\n",
    "        employee\n",
    "            :filter(salary>125000)\n",
    "            :select(name,surname,position)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Sorting\n",
    "\n",
    "We use the `:sort` combinator to sort an array of values.\n",
    "\n",
    "*List the names of departments in alphabetical order.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "35-element Array{UTF8String,1}:\n",
       " \"ADMIN HEARNG\"     \n",
       " \"ANIMAL CONTRL\"    \n",
       " \"AVIATION\"         \n",
       " \"BOARD OF ELECTION\"\n",
       " \"BOARD OF ETHICS\"  \n",
       " ⋮                  \n",
       " \"PUBLIC LIBRARY\"   \n",
       " \"STREETS & SAN\"    \n",
       " \"TRANSPORTN\"       \n",
       " \"TREASURER\"        \n",
       " \"WATER MGMNT\"      "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(department.name:sort)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also specify the attribute by which the elements of the array are to be sorted.\n",
    "\n",
    "*Show the employees sorted by salary.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "32181-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}:\n",
       " (\"STEVEN\",\"K\",\"MAYOR'S OFFICE\",\"ADMINISTRATIVE SECRETARY\",1)  \n",
       " (\"BETTY\",\"A\",\"FAMILY & SUPPORT\",\"FOSTER GRANDPARENT\",2756)    \n",
       " (\"VICTOR\",\"A\",\"FAMILY & SUPPORT\",\"SENIOR COMPANION\",2756)     \n",
       " (\"RASHEEDAH\",\"A\",\"FAMILY & SUPPORT\",\"SENIOR COMPANION\",2756)  \n",
       " (\"MARY\",\"A\",\"FAMILY & SUPPORT\",\"FOSTER GRANDPARENT\",2756)     \n",
       " ⋮                                                             \n",
       " (\"CHARLES\",\"S\",\"FIRE\",\"FIRST DEPUTY FIRE COMMISSIONER\",197736)\n",
       " (\"ALFONZA\",\"W\",\"POLICE\",\"FIRST DEPUTY SUPERINTENDENT\",197736) \n",
       " (\"JOSE\",\"S\",\"FIRE\",\"FIRE COMMISSIONER\",202728)                \n",
       " (\"RAHM\",\"E\",\"MAYOR'S OFFICE\",\"MAYOR\",216210)                  \n",
       " (\"GARRY\",\"M\",\"POLICE\",\"SUPERINTENDENT OF POLICE\",260004)      "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(employee:sort(salary))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Use `:desc` indicator to reverse the order."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "32181-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}:\n",
       " (\"GARRY\",\"M\",\"POLICE\",\"SUPERINTENDENT OF POLICE\",260004)      \n",
       " (\"RAHM\",\"E\",\"MAYOR'S OFFICE\",\"MAYOR\",216210)                  \n",
       " (\"JOSE\",\"S\",\"FIRE\",\"FIRE COMMISSIONER\",202728)                \n",
       " (\"CHARLES\",\"S\",\"FIRE\",\"FIRST DEPUTY FIRE COMMISSIONER\",197736)\n",
       " (\"ALFONZA\",\"W\",\"POLICE\",\"FIRST DEPUTY SUPERINTENDENT\",197736) \n",
       " ⋮                                                             \n",
       " (\"SONG\",\"Y\",\"FAMILY & SUPPORT\",\"SENIOR COMPANION\",2756)       \n",
       " (\"AMANDA\",\"Y\",\"FAMILY & SUPPORT\",\"FOSTER GRANDPARENT\",2756)   \n",
       " (\"EUNICE\",\"Y\",\"FAMILY & SUPPORT\",\"SENIOR COMPANION\",2756)     \n",
       " (\"MING\",\"Y\",\"FAMILY & SUPPORT\",\"SENIOR COMPANION\",2756)       \n",
       " (\"STEVEN\",\"K\",\"MAYOR'S OFFICE\",\"ADMINISTRATIVE SECRETARY\",1)  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(employee:sort(salary:desc))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is possible to specify several sorting keys."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "32181-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}:\n",
       " (\"GARRY\",\"M\",\"POLICE\",\"SUPERINTENDENT OF POLICE\",260004)      \n",
       " (\"RAHM\",\"E\",\"MAYOR'S OFFICE\",\"MAYOR\",216210)                  \n",
       " (\"JOSE\",\"S\",\"FIRE\",\"FIRE COMMISSIONER\",202728)                \n",
       " (\"CHARLES\",\"S\",\"FIRE\",\"FIRST DEPUTY FIRE COMMISSIONER\",197736)\n",
       " (\"ALFONZA\",\"W\",\"POLICE\",\"FIRST DEPUTY SUPERINTENDENT\",197736) \n",
       " ⋮                                                             \n",
       " (\"MING\",\"Y\",\"FAMILY & SUPPORT\",\"SENIOR COMPANION\",2756)       \n",
       " (\"SONG\",\"Y\",\"FAMILY & SUPPORT\",\"SENIOR COMPANION\",2756)       \n",
       " (\"SONG\",\"Y\",\"FAMILY & SUPPORT\",\"SENIOR COMPANION\",2756)       \n",
       " (\"YELENA\",\"Y\",\"FAMILY & SUPPORT\",\"SENIOR COMPANION\",2756)     \n",
       " (\"STEVEN\",\"K\",\"MAYOR'S OFFICE\",\"ADMINISTRATIVE SECRETARY\",1)  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    employee\n",
    "    :sort(\n",
    "        salary:desc,\n",
    "        surname:asc,\n",
    "        name:asc))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`:sort` can be used together with `:select` and `:filter`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7-element Array{Tuple{UTF8String,Int64},1}:\n",
       " (\"POLICE\",13570)      \n",
       " (\"FIRE\",4875)         \n",
       " (\"STREETS & SAN\",2090)\n",
       " (\"WATER MGMNT\",1848)  \n",
       " (\"AVIATION\",1344)     \n",
       " (\"TRANSPORTN\",1200)   \n",
       " (\"OEMC\",1135)         "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    department\n",
    "    :select(name, size => count(employee))\n",
    "    :filter(size>1000)\n",
    "    :sort(size:desc))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Use `:define` to name a commonly used expression."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7-element Array{Tuple{UTF8String,Int64},1}:\n",
       " (\"POLICE\",13570)      \n",
       " (\"FIRE\",4875)         \n",
       " (\"STREETS & SAN\",2090)\n",
       " (\"WATER MGMNT\",1848)  \n",
       " (\"AVIATION\",1344)     \n",
       " (\"TRANSPORTN\",1200)   \n",
       " (\"OEMC\",1135)         "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    department\n",
    "    :define(size => count(employee))\n",
    "    :select(name, size)\n",
    "    :filter(size>1000)\n",
    "    :sort(size:desc))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Limiting\n",
    "\n",
    "Use combinators `:first`, `:last`, `:take` to limit the size of the output array.  Use `:reverse` to reverse the output array.\n",
    "\n",
    "*The first employee.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Nullable((\"ELVIA\",\"A\",\"WATER MGMNT\",\"WATER RATE TAKER\",88968))"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(first(employee))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*The name of the first employee.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Nullable(\"ELVIA\")"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(first(employee).name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*The department with the largest number of employees.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Nullable((\"POLICE\",13570))"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    department\n",
    "    :select(name, size => count(employee))\n",
    "    :sort(size:desc)\n",
    "    :first)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Same query without `:sort`.\n",
    "\n",
    "*The department with the largest number of employees.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Nullable((\"POLICE\",13570))"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    department\n",
    "    :select(name, size => count(employee))\n",
    "    :first(size))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Last employee.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Nullable((\"DARIUSZ\",\"Z\",\"DoIT\",\"CHIEF DATA BASE ANALYST\",110352))"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(employee:last)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*The department with the largest number of employees.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Nullable((\"POLICE\",13570))"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    department\n",
    "    :select(name, size => count(employee))\n",
    "    :sort(size)\n",
    "    :last)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Same query could be written without `:sort`.\n",
    "\n",
    "*The department with the largest number of employees.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Nullable((\"POLICE\",13570))"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    department\n",
    "    :select(name, size => count(employee))\n",
    "    :last(size:desc))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Show first 5 employees.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}:\n",
       " (\"ELVIA\",\"A\",\"WATER MGMNT\",\"WATER RATE TAKER\",88968)                 \n",
       " (\"JEFFERY\",\"A\",\"POLICE\",\"POLICE OFFICER\",80778)                      \n",
       " (\"KARINA\",\"A\",\"POLICE\",\"POLICE OFFICER\",80778)                       \n",
       " (\"KIMBERLEI\",\"A\",\"GENERAL SERVICES\",\"CHIEF CONTRACT EXPEDITER\",84780)\n",
       " (\"VICENTE\",\"A\",\"WATER MGMNT\",\"CIVIL ENGINEER IV\",104736)             "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(employee:take(5))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Skip first 10 employees, show next 5.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}:\n",
       " (\"BETTY\",\"A\",\"FAMILY & SUPPORT\",\"FOSTER GRANDPARENT\",2756)\n",
       " (\"LYNISE\",\"A\",\"POLICE\",\"CLERK III\",43920)                 \n",
       " (\"WILLIAM\",\"A\",\"IPRA\",\"INVESTIGATOR - IPRA II\",72468)     \n",
       " (\"ZAID\",\"A\",\"POLICE\",\"POLICE OFFICER\",69684)              \n",
       " (\"ABDALMAHD\",\"A\",\"POLICE\",\"POLICE OFFICER\",80778)         "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(employee:skip(10):take(5))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Show last ten employees.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}:\n",
       " (\"MATTHEW\",\"Z\",\"AVIATION\",\"AIRPORT OPERATIONS SUPVSR I\",69840)           \n",
       " (\"JEFFREY\",\"Z\",\"POLICE\",\"POLICE OFFICER\",83616)                          \n",
       " (\"MATTHEW\",\"Z\",\"FIRE\",\"FIREFIGHTER-EMT\",91764)                           \n",
       " (\"BRYAN\",\"Z\",\"POLICE\",\"POLICE OFFICER\",80778)                            \n",
       " (\"JOHN\",\"Z\",\"GENERAL SERVICES\",\"MACHINIST (AUTOMOTIVE)\",92248)           \n",
       " (\"MICHAEL\",\"Z\",\"GENERAL SERVICES\",\"FRM OF MACHINISTS - AUTOMOTIVE\",97448)\n",
       " (\"PETER\",\"Z\",\"POLICE\",\"POLICE OFFICER\",86520)                            \n",
       " (\"MARK\",\"Z\",\"POLICE\",\"POLICE OFFICER\",83616)                             \n",
       " (\"CARLO\",\"Z\",\"POLICE\",\"POLICE OFFICER\",86520)                            \n",
       " (\"DARIUSZ\",\"Z\",\"DoIT\",\"CHIEF DATA BASE ANALYST\",110352)                  "
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(employee:skip(-10))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Show approximately half of employees.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "16090-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}:\n",
       " (\"ELVIA\",\"A\",\"WATER MGMNT\",\"WATER RATE TAKER\",88968)                 \n",
       " (\"JEFFERY\",\"A\",\"POLICE\",\"POLICE OFFICER\",80778)                      \n",
       " (\"KARINA\",\"A\",\"POLICE\",\"POLICE OFFICER\",80778)                       \n",
       " (\"KIMBERLEI\",\"A\",\"GENERAL SERVICES\",\"CHIEF CONTRACT EXPEDITER\",84780)\n",
       " (\"VICENTE\",\"A\",\"WATER MGMNT\",\"CIVIL ENGINEER IV\",104736)             \n",
       " ⋮                                                                    \n",
       " (\"BROOKE\",\"L\",\"LAW\",\"ASST CORPORATION COUNSEL\",63720)                \n",
       " (\"JOSEPH\",\"L\",\"WATER MGMNT\",\"ELECTRICAL MECHANIC\",91520)             \n",
       " (\"PETER\",\"L\",\"POLICE\",\"POLICE OFFICER\",83616)                        \n",
       " (\"ROBERT\",\"L\",\"STREETS & SAN\",\"SANITATION LABORER\",72384)            \n",
       " (\"NICHOLAS\",\"L\",\"OEMC\",\"FIRE COMMUNICATIONS OPERATOR II\",78180)      "
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(employee:take(count(employee)/2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Reverse the order of departments.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "35-element Array{Tuple{UTF8String},1}:\n",
       " (\"LICENSE APPL COMM\",)\n",
       " (\"ADMIN HEARNG\",)     \n",
       " (\"BUDGET & MGMT\",)    \n",
       " (\"POLICE BOARD\",)     \n",
       " (\"BOARD OF ETHICS\",)  \n",
       " ⋮                     \n",
       " (\"STREETS & SAN\",)    \n",
       " (\"CITY COUNCIL\",)     \n",
       " (\"GENERAL SERVICES\",) \n",
       " (\"POLICE\",)           \n",
       " (\"WATER MGMNT\",)      "
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(department:reverse)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Identity\n",
    "\n",
    "An identity of a database record is a value that identifies the record among all the entities of the same class.  Use `id` attribute to find the identity of the input record."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "35-element Array{Tuple{Int64,UTF8String},1}:\n",
       " (1,\"WATER MGMNT\")       \n",
       " (2,\"POLICE\")            \n",
       " (3,\"GENERAL SERVICES\")  \n",
       " (4,\"CITY COUNCIL\")      \n",
       " (5,\"STREETS & SAN\")     \n",
       " ⋮                       \n",
       " (31,\"BOARD OF ETHICS\")  \n",
       " (32,\"POLICE BOARD\")     \n",
       " (33,\"BUDGET & MGMT\")    \n",
       " (34,\"ADMIN HEARNG\")     \n",
       " (35,\"LICENSE APPL COMM\")"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(department:select(id,name))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Use `:get` combinator to find the record by its identity."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Nullable((\"STREETS & SAN\",))"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(department:get(5))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If a record is not found, `null` value is returned."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Nullable{Tuple{UTF8String}}()"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(department:get(-1))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can use brackets instead of `:get`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Nullable((5,\"STREETS & SAN\",2090))"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    department[5]\n",
    "    :select(id, name, count(employee)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Show all employees of a selected department.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2090-element Array{Tuple{UTF8String,UTF8String},1}:\n",
       " (\"EMMANUEL\",\"A\")\n",
       " (\"TAHIR\",\"A\")   \n",
       " (\"TIMOTHY\",\"A\") \n",
       " (\"DANIELLE\",\"A\")\n",
       " (\"SAMUEL\",\"A\")  \n",
       " ⋮               \n",
       " (\"LUIS\",\"Z\")    \n",
       " (\"ROBERT\",\"Z\")  \n",
       " (\"JONATHAN\",\"Z\")\n",
       " (\"THOMAS\",\"Z\")  \n",
       " (\"MAC\",\"Z\")     "
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(department[5].employee)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Hierarchical queries\n",
    "(Note: the data on organizational structure is not available, so the output is largely meaningless).\n",
    "\n",
    "*Find the employees who earn more than their manager.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1-element Array{Tuple{UTF8String,UTF8String,UTF8String,Nullable{Tuple{UTF8String,UTF8String,UTF8String}},Nullable{Int64}},1}:\n",
       " (\"BRIAN\",\"L\",\"AUDITOR IV\",Nullable((\"ROBERT\",\"L\",\"DIR OF ACCOUNTING\")),Nullable(4140))"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    employee\n",
    "    :filter(salary>managed_by.salary)\n",
    "    :select(name, surname, position, managed_by, salary-managed_by.salary))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*For all employees in a certain department, list their seniors and the number of their subordinates.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "24-element Array{Tuple{UTF8String,UTF8String,UTF8String,Array{Tuple{UTF8String,UTF8String,UTF8String},1},Int64},1}:\n",
       " (\"SAEED\",\"A\",\"ASST CITY TREASURER\",[(\"KURT\",\"S\",\"CITY TREASURER\")],0)                                \n",
       " (\"ELIZABETH\",\"A\",\"ACCOUNTANT I\",[(\"ROBERT\",\"L\",\"DIR OF ACCOUNTING\"),(\"KURT\",\"S\",\"CITY TREASURER\")],0)\n",
       " (\"KONSTANTINES\",\"A\",\"ASSISTANT DIRECTOR OF FINANCE\",[(\"KURT\",\"S\",\"CITY TREASURER\")],0)               \n",
       " (\"SARA\",\"A\",\"STUDENT INTERN\",[(\"KURT\",\"S\",\"CITY TREASURER\")],0)                                      \n",
       " (\"NANCY\",\"C\",\"EXECUTIVE SECRETARY I\",[(\"KURT\",\"S\",\"CITY TREASURER\")],0)                              \n",
       " ⋮                                                                                                    \n",
       " (\"DANIELLE\",\"M\",\"ASST CITY TREASURER\",[(\"KURT\",\"S\",\"CITY TREASURER\")],0)                             \n",
       " (\"MARK\",\"M\",\"PORTFOLIO MANAGER\",[(\"KURT\",\"S\",\"CITY TREASURER\")],0)                                   \n",
       " (\"KENNETH\",\"S\",\"ASST CITY TREASURER\",[(\"KURT\",\"S\",\"CITY TREASURER\")],0)                              \n",
       " (\"ALEXANDRA\",\"S\",\"DEPUTY CITY TREASURER\",[(\"KURT\",\"S\",\"CITY TREASURER\")],0)                          \n",
       " (\"KURT\",\"S\",\"CITY TREASURER\",Tuple{UTF8String,UTF8String,UTF8String}[],23)                           "
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    department[26].employee\n",
    "    :select(\n",
    "        name,\n",
    "        surname,\n",
    "        position,\n",
    "        connect(managed_by),\n",
    "        count(connect(manages))))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*List employees of a certain department in hierarchical order.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "24-element Array{Tuple{Int64,UTF8String,UTF8String,UTF8String},1}:\n",
       " (0,\"KURT\",\"S\",\"CITY TREASURER\")                       \n",
       " (1,\"SAEED\",\"A\",\"ASST CITY TREASURER\")                 \n",
       " (1,\"KONSTANTINES\",\"A\",\"ASSISTANT DIRECTOR OF FINANCE\")\n",
       " (1,\"SARA\",\"A\",\"STUDENT INTERN\")                       \n",
       " (1,\"NANCY\",\"C\",\"EXECUTIVE SECRETARY I\")               \n",
       " ⋮                                                     \n",
       " (2,\"TERRANCE\",\"M\",\"ACCOUNTANT IV\")                    \n",
       " (1,\"DANIELLE\",\"M\",\"ASST CITY TREASURER\")              \n",
       " (1,\"MARK\",\"M\",\"PORTFOLIO MANAGER\")                    \n",
       " (1,\"KENNETH\",\"S\",\"ASST CITY TREASURER\")               \n",
       " (1,\"ALEXANDRA\",\"S\",\"DEPUTY CITY TREASURER\")           "
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    department[26].employee\n",
    "    :sort_connect(managed_by)\n",
    "    :select(\n",
    "        depth(managed_by),\n",
    "        name,\n",
    "        surname,\n",
    "        position))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Grouping\n",
    "Use `:unique` combinator to generate all unique values that appear in a sequence.\n",
    "\n",
    "*List all distinct positions.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1094-element Array{UTF8String,1}:\n",
       " \"1ST DEPUTY INSPECTOR GENERAL\"\n",
       " \"A/MGR COM SVC-ELECTIONS\"     \n",
       " \"A/MGR OF MIS-ELECTIONS\"      \n",
       " \"A/MGR WAREHOUSE-ELECTIONS\"   \n",
       " \"A/SUPRV REDISTRICTING\"       \n",
       " ⋮                             \n",
       " \"WINDOW WASHER\"               \n",
       " \"YOUTH SERVICES COORD\"        \n",
       " \"ZONING ADMINISTRATOR\"        \n",
       " \"ZONING INVESTIGATOR\"         \n",
       " \"ZONING PLAN EXAMINER\"        "
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(employee.position:unique)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Find the number of distinct positions for each department.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "35-element Array{Tuple{UTF8String,Int64,Int64},1}:\n",
       " (\"WATER MGMNT\",154,1848)    \n",
       " (\"POLICE\",129,13570)        \n",
       " (\"GENERAL SERVICES\",119,924)\n",
       " (\"CITY COUNCIL\",28,397)     \n",
       " (\"STREETS & SAN\",70,2090)   \n",
       " ⋮                           \n",
       " (\"BOARD OF ETHICS\",9,9)     \n",
       " (\"POLICE BOARD\",2,2)        \n",
       " (\"BUDGET & MGMT\",24,43)     \n",
       " (\"ADMIN HEARNG\",15,39)      \n",
       " (\"LICENSE APPL COMM\",1,1)   "
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    department\n",
    "    :select(\n",
    "        name,\n",
    "        count(unique(employee.position)),\n",
    "        count(employee)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also list distinct positions using `:group` combinator.  With each position, we get a list of employees having this position."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1094-element Array{Tuple{UTF8String,Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}},1}:\n",
       " (\"1ST DEPUTY INSPECTOR GENERAL\",[(\"SHARON\",\"F\",\"INSPECTOR GEN\",\"1ST DEPUTY INSPECTOR GENERAL\",137052)])                                                                                                                                                                                                                                                                                                                                                                                                                        \n",
       " (\"A/MGR COM SVC-ELECTIONS\",[(\"LAURA\",\"G\",\"BOARD OF ELECTION\",\"A/MGR COM SVC-ELECTIONS\",99816)])                                                                                                                                                                                                                                                                                                                                                                                                                                \n",
       " (\"A/MGR OF MIS-ELECTIONS\",[(\"TIEN\",\"T\",\"BOARD OF ELECTION\",\"A/MGR OF MIS-ELECTIONS\",94932)])                                                                                                                                                                                                                                                                                                                                                                                                                                   \n",
       " (\"A/MGR WAREHOUSE-ELECTIONS\",[(\"DERRICK\",\"H\",\"BOARD OF ELECTION\",\"A/MGR WAREHOUSE-ELECTIONS\",71364)])                                                                                                                                                                                                                                                                                                                                                                                                                          \n",
       " (\"A/SUPRV REDISTRICTING\",[(\"THOMAS\",\"M\",\"BOARD OF ELECTION\",\"A/SUPRV REDISTRICTING\",69564),(\"CHRISTOPHER\",\"T\",\"BOARD OF ELECTION\",\"A/SUPRV REDISTRICTING\",45720)])                                                                                                                                                                                                                                                                                                                                                             \n",
       " ⋮                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              \n",
       " (\"WINDOW WASHER\",[(\"GARRY\",\"S\",\"AVIATION\",\"WINDOW WASHER\",47320),(\"MARK\",\"T\",\"AVIATION\",\"WINDOW WASHER\",47320)])                                                                                                                                                                                                                                                                                                                                                                                                               \n",
       " (\"YOUTH SERVICES COORD\",[(\"EVELYN\",\"B\",\"FAMILY & SUPPORT\",\"YOUTH SERVICES COORD\",67224),(\"MONICA\",\"D\",\"FAMILY & SUPPORT\",\"YOUTH SERVICES COORD\",67224),(\"CESAR\",\"G\",\"FAMILY & SUPPORT\",\"YOUTH SERVICES COORD\",73752),(\"KAREN\",\"N\",\"FAMILY & SUPPORT\",\"YOUTH SERVICES COORD\",67224),(\"RASAUNA\",\"R\",\"FAMILY & SUPPORT\",\"YOUTH SERVICES COORD\",67224),(\"RICCADONNA\",\"R\",\"FAMILY & SUPPORT\",\"YOUTH SERVICES COORD\",67224),(\"LA\",\"T\",\"POLICE\",\"YOUTH SERVICES COORD\",80916),(\"TERRENCE\",\"W\",\"POLICE\",\"YOUTH SERVICES COORD\",73752)])\n",
       " (\"ZONING ADMINISTRATOR\",[(\"PATRICIA\",\"S\",\"COMMUNITY DEVELOPMENT\",\"ZONING ADMINISTRATOR\",139800)])                                                                                                                                                                                                                                                                                                                                                                                                                              \n",
       " (\"ZONING INVESTIGATOR\",[(\"CARLOS\",\"R\",\"COMMUNITY DEVELOPMENT\",\"ZONING INVESTIGATOR\",97596)])                                                                                                                                                                                                                                                                                                                                                                                                                                   \n",
       " (\"ZONING PLAN EXAMINER\",[(\"KYLE\",\"B\",\"COMMUNITY DEVELOPMENT\",\"ZONING PLAN EXAMINER\",50004),(\"PETER\",\"B\",\"COMMUNITY DEVELOPMENT\",\"ZONING PLAN EXAMINER\",50004),(\"SHOSHA\",\"C\",\"COMMUNITY DEVELOPMENT\",\"ZONING PLAN EXAMINER\",50004),(\"EDGAR\",\"D\",\"COMMUNITY DEVELOPMENT\",\"ZONING PLAN EXAMINER\",60780),(\"ALICIA\",\"D\",\"COMMUNITY DEVELOPMENT\",\"ZONING PLAN EXAMINER\",66684),(\"JOSE\",\"G\",\"COMMUNITY DEVELOPMENT\",\"ZONING PLAN EXAMINER\",69888),(\"JANICE\",\"H\",\"COMMUNITY DEVELOPMENT\",\"ZONING PLAN EXAMINER\",69888)])               "
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(employee:group(position))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For each row generated by `employee:group(position)`, combinator `employee` will give you employees that have this position.\n",
    "\n",
    "*For each position, find the number of employees.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1094-element Array{Tuple{UTF8String,Int64},1}:\n",
       " (\"POLICE OFFICER\",9489)                       \n",
       " (\"FIREFIGHTER-EMT\",1191)                      \n",
       " (\"SERGEANT\",1138)                             \n",
       " (\"FIREFIGHTER\",970)                           \n",
       " (\"POLICE OFFICER (ASSIGNED AS DETECTIVE)\",808)\n",
       " ⋮                                             \n",
       " (\"WEB AUTHOR\",1)                              \n",
       " (\"WEB DEVELOPER\",1)                           \n",
       " (\"WEB DEVELOPER/ADMINTR-CPL\",1)               \n",
       " (\"ZONING ADMINISTRATOR\",1)                    \n",
       " (\"ZONING INVESTIGATOR\",1)                     "
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    employee\n",
    "    :group(position)\n",
    "    :select(position, size => count(employee))\n",
    "    :sort(size:desc))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Find positions provided by no less than 5 departments.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "78-element Array{Tuple{UTF8String,Array{UTF8String,1}},1}:\n",
       " (\"STAFF ASST\",UTF8String[\"WATER MGMNT\",\"POLICE\",\"GENERAL SERVICES\",\"STREETS & SAN\",\"AVIATION\",\"FIRE\",\"FAMILY & SUPPORT\",\"PUBLIC LIBRARY\",\"DoIT\",\"BUSINESS AFFAIRS\"  …  \"BUILDINGS\",\"ANIMAL CONTRL\",\"CITY CLERK\",\"INSPECTOR GEN\",\"TREASURER\",\"DISABILITIES\",\"PROCUREMENT\",\"BOARD OF ETHICS\",\"ADMIN HEARNG\",\"LICENSE APPL COMM\"])                    \n",
       " (\"ADMINISTRATIVE SERVICES OFFICER II\",UTF8String[\"WATER MGMNT\",\"POLICE\",\"STREETS & SAN\",\"AVIATION\",\"FAMILY & SUPPORT\",\"PUBLIC LIBRARY\",\"DoIT\",\"BUSINESS AFFAIRS\",\"OEMC\",\"TRANSPORTN\"  …  \"LAW\",\"COMMUNITY DEVELOPMENT\",\"BUILDINGS\",\"ANIMAL CONTRL\",\"CITY CLERK\",\"DISABILITIES\",\"HUMAN RESOURCES\",\"HUMAN RELATIONS\",\"BUDGET & MGMT\",\"ADMIN HEARNG\"])\n",
       " (\"ADMINISTRATIVE ASST II\",UTF8String[\"WATER MGMNT\",\"POLICE\",\"GENERAL SERVICES\",\"STREETS & SAN\",\"AVIATION\",\"FIRE\",\"FAMILY & SUPPORT\",\"PUBLIC LIBRARY\",\"BUSINESS AFFAIRS\",\"OEMC\",\"TRANSPORTN\",\"HEALTH\",\"LAW\",\"FINANCE\",\"BUILDINGS\",\"CITY CLERK\",\"PROCUREMENT\",\"ADMIN HEARNG\"])                                                                       \n",
       " (\"ADMINISTRATIVE ASST III\",UTF8String[\"WATER MGMNT\",\"POLICE\",\"GENERAL SERVICES\",\"STREETS & SAN\",\"AVIATION\",\"FIRE\",\"PUBLIC LIBRARY\",\"BUSINESS AFFAIRS\",\"OEMC\",\"TRANSPORTN\",\"HEALTH\",\"FINANCE\",\"COMMUNITY DEVELOPMENT\",\"BUILDINGS\",\"CITY CLERK\",\"HUMAN RELATIONS\",\"ADMIN HEARNG\"])                                                                   \n",
       " (\"ASST TO THE COMMISSIONER\",UTF8String[\"WATER MGMNT\",\"POLICE\",\"GENERAL SERVICES\",\"STREETS & SAN\",\"AVIATION\",\"FIRE\",\"FAMILY & SUPPORT\",\"PUBLIC LIBRARY\",\"DoIT\",\"BUSINESS AFFAIRS\",\"TRANSPORTN\",\"FINANCE\",\"CULTURAL AFFAIRS\",\"COMMUNITY DEVELOPMENT\",\"PROCUREMENT\",\"HUMAN RELATIONS\"])                                                               \n",
       " ⋮                                                                                                                                                                                                                                                                                                                                                  \n",
       " (\"MANAGER OF FINANCE\",UTF8String[\"STREETS & SAN\",\"AVIATION\",\"FAMILY & SUPPORT\",\"TRANSPORTN\",\"HEALTH\"])                                                                                                                                                                                                                                             \n",
       " (\"PAINTER\",UTF8String[\"WATER MGMNT\",\"GENERAL SERVICES\",\"STREETS & SAN\",\"AVIATION\",\"TRANSPORTN\"])                                                                                                                                                                                                                                                   \n",
       " (\"POOL MOTOR TRUCK DRIVER\",UTF8String[\"WATER MGMNT\",\"STREETS & SAN\",\"AVIATION\",\"PUBLIC LIBRARY\",\"TRANSPORTN\"])                                                                                                                                                                                                                                     \n",
       " (\"SPECIAL ASST\",UTF8String[\"FAMILY & SUPPORT\",\"BUSINESS AFFAIRS\",\"OEMC\",\"TRANSPORTN\",\"CULTURAL AFFAIRS\"])                                                                                                                                                                                                                                          \n",
       " (\"STORES LABORER\",UTF8String[\"GENERAL SERVICES\",\"AVIATION\",\"FIRE\",\"TRANSPORTN\",\"PROCUREMENT\"])                                                                                                                                                                                                                                                     "
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    employee\n",
    "    :group(position)\n",
    "    :define(department => unique(employee.department))\n",
    "    :filter(count(department)>=5)\n",
    "    :select(position, department)\n",
    "    :sort(count(department):desc))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Find the popular names of Chicago employees.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5038-element Array{Tuple{UTF8String,Int64},1}:\n",
       " (\"MICHAEL\",1170)\n",
       " (\"JOHN\",914)    \n",
       " (\"JAMES\",691)   \n",
       " (\"ROBERT\",649)  \n",
       " (\"JOSEPH\",543)  \n",
       " ⋮               \n",
       " (\"ZORAN\",1)     \n",
       " (\"ZORRAYDA\",1)  \n",
       " (\"ZULEMA\",1)    \n",
       " (\"ZUZANNA\",1)   \n",
       " (\"ZYNETTA\",1)   "
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    employee\n",
    "    :group(name)\n",
    "    :select(name, size => count(employee))\n",
    "    :sort(size:desc))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Find the top salary by the first name, but only if there are at least 10 employees having this name.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "483-element Array{Tuple{UTF8String,Int64},1}:\n",
       " (\"JOSE\",202728)   \n",
       " (\"CHARLES\",197736)\n",
       " (\"ROBERT\",194256) \n",
       " (\"ANTHONY\",187680)\n",
       " (\"JOHN\",187680)   \n",
       " ⋮                 \n",
       " (\"SARA\",86520)    \n",
       " (\"TERRI\",86520)   \n",
       " (\"DARIUS\",85020)  \n",
       " (\"ANNIE\",83616)   \n",
       " (\"BERTHA\",83616)  "
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    employee\n",
    "    :group(name)\n",
    "    :filter(count(employee)>=10)\n",
    "    :select(name, max_salary => max(employee.salary))\n",
    "    :sort(max_salary:desc))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Find the number of employees for each department and salary bracket.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "405-element Array{Tuple{UTF8String,Int64,Int64,Int64},1}:\n",
       " (\"WATER MGMNT\",160000,169999,1)    \n",
       " (\"WATER MGMNT\",150000,159999,1)    \n",
       " (\"WATER MGMNT\",130000,139999,2)    \n",
       " (\"WATER MGMNT\",120000,129999,5)    \n",
       " (\"WATER MGMNT\",110000,119999,32)   \n",
       " ⋮                                  \n",
       " (\"ADMIN HEARNG\",80000,89999,2)     \n",
       " (\"ADMIN HEARNG\",70000,79999,3)     \n",
       " (\"ADMIN HEARNG\",60000,69999,19)    \n",
       " (\"ADMIN HEARNG\",50000,59999,7)     \n",
       " (\"LICENSE APPL COMM\",60000,69999,1)"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    employee\n",
    "    :group(department, salary_bracket => salary/10000*10000 :desc)\n",
    "    :select(department, salary_bracket, salary_bracket+9999, count(employee)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To generate totals on each dimension, use `:group_cube`.\n",
    "\n",
    "*Find the number of employees for each department and salary bracket, including totals.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "464-element Array{Tuple{Nullable{UTF8String},Nullable{Int64},Nullable{Int64},Int64},1}:\n",
       " (Nullable(\"WATER MGMNT\"),Nullable(160000),Nullable(169999),1)     \n",
       " (Nullable(\"WATER MGMNT\"),Nullable(150000),Nullable(159999),1)     \n",
       " (Nullable(\"WATER MGMNT\"),Nullable(130000),Nullable(139999),2)     \n",
       " (Nullable(\"WATER MGMNT\"),Nullable(120000),Nullable(129999),5)     \n",
       " (Nullable(\"WATER MGMNT\"),Nullable(110000),Nullable(119999),32)    \n",
       " ⋮                                                                 \n",
       " (Nullable{UTF8String}(),Nullable(30000),Nullable(39999),215)      \n",
       " (Nullable{UTF8String}(),Nullable(20000),Nullable(29999),420)      \n",
       " (Nullable{UTF8String}(),Nullable(10000),Nullable(19999),1209)     \n",
       " (Nullable{UTF8String}(),Nullable(0),Nullable(9999),376)           \n",
       " (Nullable{UTF8String}(),Nullable{Int64}(),Nullable{Int64}(),32181)"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    employee\n",
    "    :group_cube(department, salary_bracket => salary/10000*10000 :desc)\n",
    "    :select(department, salary_bracket, salary_bracket+9999, count(employee)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Add `:dataframe` to present this data in tabular form."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>department</th><th>low</th><th>high</th><th>size</th></tr><tr><th>1</th><td>WATER MGMNT</td><td>160000</td><td>169999</td><td>1</td></tr><tr><th>2</th><td>WATER MGMNT</td><td>150000</td><td>159999</td><td>1</td></tr><tr><th>3</th><td>WATER MGMNT</td><td>130000</td><td>139999</td><td>2</td></tr><tr><th>4</th><td>WATER MGMNT</td><td>120000</td><td>129999</td><td>5</td></tr><tr><th>5</th><td>WATER MGMNT</td><td>110000</td><td>119999</td><td>32</td></tr><tr><th>6</th><td>WATER MGMNT</td><td>100000</td><td>109999</td><td>138</td></tr><tr><th>7</th><td>WATER MGMNT</td><td>90000</td><td>99999</td><td>607</td></tr><tr><th>8</th><td>WATER MGMNT</td><td>80000</td><td>89999</td><td>187</td></tr><tr><th>9</th><td>WATER MGMNT</td><td>70000</td><td>79999</td><td>617</td></tr><tr><th>10</th><td>WATER MGMNT</td><td>60000</td><td>69999</td><td>95</td></tr><tr><th>11</th><td>WATER MGMNT</td><td>50000</td><td>59999</td><td>76</td></tr><tr><th>12</th><td>WATER MGMNT</td><td>40000</td><td>49999</td><td>87</td></tr><tr><th>13</th><td>WATER MGMNT</td><td>NA</td><td>NA</td><td>1848</td></tr><tr><th>14</th><td>POLICE</td><td>260000</td><td>269999</td><td>1</td></tr><tr><th>15</th><td>POLICE</td><td>190000</td><td>199999</td><td>2</td></tr><tr><th>&vellip;</th><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td></tr></table>"
      ],
      "text/plain": [
       "464x4 DataFrames.DataFrame\n",
       "| Row | department    | low    | high   | size  |\n",
       "|-----|---------------|--------|--------|-------|\n",
       "| 1   | \"WATER MGMNT\" | 160000 | 169999 | 1     |\n",
       "| 2   | \"WATER MGMNT\" | 150000 | 159999 | 1     |\n",
       "| 3   | \"WATER MGMNT\" | 130000 | 139999 | 2     |\n",
       "| 4   | \"WATER MGMNT\" | 120000 | 129999 | 5     |\n",
       "⋮\n",
       "| 460 | NA            | 30000  | 39999  | 215   |\n",
       "| 461 | NA            | 20000  | 29999  | 420   |\n",
       "| 462 | NA            | 10000  | 19999  | 1209  |\n",
       "| 463 | NA            | 0      | 9999   | 376   |\n",
       "| 464 | NA            | NA     | NA     | 32181 |"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    employee\n",
    "    :group_cube(\n",
    "        department,\n",
    "        salary_bracket => salary/10000*10000 :desc)\n",
    "    :select(\n",
    "        department,\n",
    "        low => salary_bracket,\n",
    "        high => salary_bracket+9999,\n",
    "        size => count(employee))\n",
    "    :dataframe)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can specify dimensions separately using `:partition`.\n",
    "\n",
    "*Find the number of positions, the number of employees and the highest salary for the first 3 departments.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3-element Array{Tuple{UTF8String,Int64,Int64,Nullable{Int64}},1}:\n",
       " (\"WATER MGMNT\",154,1848,Nullable(169512))    \n",
       " (\"POLICE\",129,13570,Nullable(260004))        \n",
       " (\"GENERAL SERVICES\",119,924,Nullable(157092))"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    employee\n",
    "    :partition(department:take(3))\n",
    "    :select(department.name, count(unique(employee.position)), count(employee), max(employee.salary)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Similar to `:group_cube`, `:partition_cube` adds totals.\n",
    "\n",
    "*Find the numbers of positions and employees, the highest salary and the most popular position for the first 3 departments, and include the totals.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>name</th><th>num_pos</th><th>num_empl</th><th>max_salary</th><th>pop_position</th></tr><tr><th>1</th><td>WATER MGMNT</td><td>154</td><td>1848</td><td>169512</td><td>CONSTRUCTION LABORER</td></tr><tr><th>2</th><td>POLICE</td><td>129</td><td>13570</td><td>260004</td><td>POLICE OFFICER</td></tr><tr><th>3</th><td>GENERAL SERVICES</td><td>119</td><td>924</td><td>157092</td><td>MACHINIST (AUTOMOTIVE)</td></tr><tr><th>4</th><td>NA</td><td>334</td><td>16342</td><td>260004</td><td>POLICE OFFICER</td></tr></table>"
      ],
      "text/plain": [
       "4x5 DataFrames.DataFrame\n",
       "| Row | name               | num_pos | num_empl | max_salary |\n",
       "|-----|--------------------|---------|----------|------------|\n",
       "| 1   | \"WATER MGMNT\"      | 154     | 1848     | 169512     |\n",
       "| 2   | \"POLICE\"           | 129     | 13570    | 260004     |\n",
       "| 3   | \"GENERAL SERVICES\" | 119     | 924      | 157092     |\n",
       "| 4   | NA                 | 334     | 16342    | 260004     |\n",
       "\n",
       "| Row | pop_position             |\n",
       "|-----|--------------------------|\n",
       "| 1   | \"CONSTRUCTION LABORER\"   |\n",
       "| 2   | \"POLICE OFFICER\"         |\n",
       "| 3   | \"MACHINIST (AUTOMOTIVE)\" |\n",
       "| 4   | \"POLICE OFFICER\"         |"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    employee\n",
    "    :partition_cube(department:take(3))\n",
    "    :select(\n",
    "        department.name,\n",
    "        num_pos => count(unique(employee.position)),\n",
    "        num_empl => count(employee),\n",
    "        max_salary => max(employee.salary),\n",
    "        pop_position => employee:group(position):first(count(employee)).position)\n",
    "    :dataframe)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can use an array constructor or `range()` combinator to specify the dimensions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5-element Array{Int64,1}:\n",
       "      0\n",
       "  60000\n",
       " 120000\n",
       " 180000\n",
       " 240000"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(range(0, 60000, max(employee.salary)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*For the given departments, employee's names and salary brackets, find the number of employees, the number of different positions and the most popular position.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>dept</th><th>name</th><th>low</th><th>high</th><th>pop_position</th><th>num_pos</th><th>num_empl</th></tr><tr><th>1</th><td>WATER MGMNT</td><td>ANTHONY</td><td>0</td><td>59999</td><td>LABORER - APPRENTICE</td><td>1</td><td>1</td></tr><tr><th>2</th><td>WATER MGMNT</td><td>ANTHONY</td><td>60000</td><td>119999</td><td>CONSTRUCTION LABORER</td><td>15</td><td>32</td></tr><tr><th>3</th><td>WATER MGMNT</td><td>ANTHONY</td><td>120000</td><td>179999</td><td>NA</td><td>0</td><td>0</td></tr><tr><th>4</th><td>WATER MGMNT</td><td>ANTHONY</td><td>180000</td><td>239999</td><td>NA</td><td>0</td><td>0</td></tr><tr><th>5</th><td>WATER MGMNT</td><td>ANTHONY</td><td>240000</td><td>299999</td><td>NA</td><td>0</td><td>0</td></tr><tr><th>6</th><td>WATER MGMNT</td><td>BRIAN</td><td>0</td><td>59999</td><td>LABORER - APPRENTICE</td><td>2</td><td>2</td></tr><tr><th>7</th><td>WATER MGMNT</td><td>BRIAN</td><td>60000</td><td>119999</td><td>HOISTING ENGINEER</td><td>11</td><td>18</td></tr><tr><th>8</th><td>WATER MGMNT</td><td>BRIAN</td><td>120000</td><td>179999</td><td>NA</td><td>0</td><td>0</td></tr><tr><th>9</th><td>WATER MGMNT</td><td>BRIAN</td><td>180000</td><td>239999</td><td>NA</td><td>0</td><td>0</td></tr><tr><th>10</th><td>WATER MGMNT</td><td>BRIAN</td><td>240000</td><td>299999</td><td>NA</td><td>0</td><td>0</td></tr><tr><th>11</th><td>POLICE</td><td>ANTHONY</td><td>0</td><td>59999</td><td>POLICE OFFICER</td><td>2</td><td>7</td></tr><tr><th>12</th><td>POLICE</td><td>ANTHONY</td><td>60000</td><td>119999</td><td>POLICE OFFICER</td><td>6</td><td>145</td></tr><tr><th>13</th><td>POLICE</td><td>ANTHONY</td><td>120000</td><td>179999</td><td>LIEUTENANT</td><td>1</td><td>4</td></tr><tr><th>14</th><td>POLICE</td><td>ANTHONY</td><td>180000</td><td>239999</td><td>CHIEF</td><td>1</td><td>1</td></tr><tr><th>15</th><td>POLICE</td><td>ANTHONY</td><td>240000</td><td>299999</td><td>NA</td><td>0</td><td>0</td></tr><tr><th>&vellip;</th><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td></tr></table>"
      ],
      "text/plain": [
       "30x7 DataFrames.DataFrame\n",
       "| Row | dept               | name      | low    | high   |\n",
       "|-----|--------------------|-----------|--------|--------|\n",
       "| 1   | \"WATER MGMNT\"      | \"ANTHONY\" | 0      | 59999  |\n",
       "| 2   | \"WATER MGMNT\"      | \"ANTHONY\" | 60000  | 119999 |\n",
       "| 3   | \"WATER MGMNT\"      | \"ANTHONY\" | 120000 | 179999 |\n",
       "| 4   | \"WATER MGMNT\"      | \"ANTHONY\" | 180000 | 239999 |\n",
       "⋮\n",
       "| 26  | \"GENERAL SERVICES\" | \"BRIAN\"   | 0      | 59999  |\n",
       "| 27  | \"GENERAL SERVICES\" | \"BRIAN\"   | 60000  | 119999 |\n",
       "| 28  | \"GENERAL SERVICES\" | \"BRIAN\"   | 120000 | 179999 |\n",
       "| 29  | \"GENERAL SERVICES\" | \"BRIAN\"   | 180000 | 239999 |\n",
       "| 30  | \"GENERAL SERVICES\" | \"BRIAN\"   | 240000 | 299999 |\n",
       "\n",
       "| Row | pop_position             | num_pos | num_empl |\n",
       "|-----|--------------------------|---------|----------|\n",
       "| 1   | \"LABORER - APPRENTICE\"   | 1       | 1        |\n",
       "| 2   | \"CONSTRUCTION LABORER\"   | 15      | 32       |\n",
       "| 3   | NA                       | 0       | 0        |\n",
       "| 4   | NA                       | 0       | 0        |\n",
       "⋮\n",
       "| 26  | \"GARAGE ATTENDANT\"       | 1       | 1        |\n",
       "| 27  | \"MACHINIST (AUTOMOTIVE)\" | 6       | 10       |\n",
       "| 28  | NA                       | 0       | 0        |\n",
       "| 29  | NA                       | 0       | 0        |\n",
       "| 30  | NA                       | 0       | 0        |"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    employee\n",
    "    :define(salary_bracket => salary/60000*60000)\n",
    "    :partition(\n",
    "        department:take(3),\n",
    "        name => [\"ANTHONY\", \"BRIAN\"],\n",
    "        salary_bracket => range(0, 60000, max(employee.salary)))\n",
    "    :select(\n",
    "        dept => department.name,\n",
    "        name,\n",
    "        low => salary_bracket,\n",
    "        high => salary_bracket+59999,\n",
    "        pop_position => employee:group(position):first(count(employee)).position,\n",
    "        num_pos => count(unique(employee.position)),\n",
    "        num_empl => count(employee))\n",
    "    :dataframe)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Output formatting\n",
    "\n",
    "The output can be produced in the form of a JSON value or a `DataFrame` object."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "35-element Array{Dict{Any,Any},1}:\n",
       " Dict{Any,Any}(:name=>\"WATER MGMNT\")      \n",
       " Dict{Any,Any}(:name=>\"POLICE\")           \n",
       " Dict{Any,Any}(:name=>\"GENERAL SERVICES\") \n",
       " Dict{Any,Any}(:name=>\"CITY COUNCIL\")     \n",
       " Dict{Any,Any}(:name=>\"STREETS & SAN\")    \n",
       " ⋮                                        \n",
       " Dict{Any,Any}(:name=>\"BOARD OF ETHICS\")  \n",
       " Dict{Any,Any}(:name=>\"POLICE BOARD\")     \n",
       " Dict{Any,Any}(:name=>\"BUDGET & MGMT\")    \n",
       " Dict{Any,Any}(:name=>\"ADMIN HEARNG\")     \n",
       " Dict{Any,Any}(:name=>\"LICENSE APPL COMM\")"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(department:json)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Selector items become fields of the JSON dictionary."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "35-element Array{Dict{Any,Any},1}:\n",
       " Dict{Any,Any}(:name=>\"WATER MGMNT\",:size=>1848,:head=>Dict{Any,Any}(:surname=>\"P\",:name=>\"THOMAS\"))     \n",
       " Dict{Any,Any}(:name=>\"POLICE\",:size=>13570,:head=>Dict{Any,Any}(:surname=>\"M\",:name=>\"GARRY\"))          \n",
       " Dict{Any,Any}(:name=>\"GENERAL SERVICES\",:size=>924,:head=>Dict{Any,Any}(:surname=>\"R\",:name=>\"DAVID\"))  \n",
       " Dict{Any,Any}(:name=>\"CITY COUNCIL\",:size=>397,:head=>Dict{Any,Any}(:surname=>\"K\",:name=>\"MARLA\"))      \n",
       " Dict{Any,Any}(:name=>\"STREETS & SAN\",:size=>2090,:head=>Dict{Any,Any}(:surname=>\"W\",:name=>\"CHARLES\"))  \n",
       " ⋮                                                                                                       \n",
       " Dict{Any,Any}(:name=>\"BOARD OF ETHICS\",:size=>9,:head=>Dict{Any,Any}(:surname=>\"B\",:name=>\"STEVEN\"))    \n",
       " Dict{Any,Any}(:name=>\"POLICE BOARD\",:size=>2,:head=>Dict{Any,Any}(:surname=>\"C\",:name=>\"MAX\"))          \n",
       " Dict{Any,Any}(:name=>\"BUDGET & MGMT\",:size=>43,:head=>Dict{Any,Any}(:surname=>\"H\",:name=>\"ALEXANDRA\"))  \n",
       " Dict{Any,Any}(:name=>\"ADMIN HEARNG\",:size=>39,:head=>Dict{Any,Any}(:surname=>\"J\",:name=>\"PATRICIA\"))    \n",
       " Dict{Any,Any}(:name=>\"LICENSE APPL COMM\",:size=>1,:head=>Dict{Any,Any}(:surname=>\"G\",:name=>\"MICHELLE\"))"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    department\n",
    "    :select(\n",
    "        name,\n",
    "        size => count(employee),\n",
    "        head => employee:first(salary))\n",
    "    :json)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can pass a list of output fields to the `json` combinator."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "35-element Array{Dict{Any,Any},1}:\n",
       " Dict{Any,Any}(:name=>\"WATER MGMNT\",:size=>1848,:head=>Dict{Any,Any}(:surname=>\"P\",:name=>\"THOMAS\"))     \n",
       " Dict{Any,Any}(:name=>\"POLICE\",:size=>13570,:head=>Dict{Any,Any}(:surname=>\"M\",:name=>\"GARRY\"))          \n",
       " Dict{Any,Any}(:name=>\"GENERAL SERVICES\",:size=>924,:head=>Dict{Any,Any}(:surname=>\"R\",:name=>\"DAVID\"))  \n",
       " Dict{Any,Any}(:name=>\"CITY COUNCIL\",:size=>397,:head=>Dict{Any,Any}(:surname=>\"K\",:name=>\"MARLA\"))      \n",
       " Dict{Any,Any}(:name=>\"STREETS & SAN\",:size=>2090,:head=>Dict{Any,Any}(:surname=>\"W\",:name=>\"CHARLES\"))  \n",
       " ⋮                                                                                                       \n",
       " Dict{Any,Any}(:name=>\"BOARD OF ETHICS\",:size=>9,:head=>Dict{Any,Any}(:surname=>\"B\",:name=>\"STEVEN\"))    \n",
       " Dict{Any,Any}(:name=>\"POLICE BOARD\",:size=>2,:head=>Dict{Any,Any}(:surname=>\"C\",:name=>\"MAX\"))          \n",
       " Dict{Any,Any}(:name=>\"BUDGET & MGMT\",:size=>43,:head=>Dict{Any,Any}(:surname=>\"H\",:name=>\"ALEXANDRA\"))  \n",
       " Dict{Any,Any}(:name=>\"ADMIN HEARNG\",:size=>39,:head=>Dict{Any,Any}(:surname=>\"J\",:name=>\"PATRICIA\"))    \n",
       " Dict{Any,Any}(:name=>\"LICENSE APPL COMM\",:size=>1,:head=>Dict{Any,Any}(:surname=>\"G\",:name=>\"MICHELLE\"))"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    department\n",
    "    :json(\n",
    "        name,\n",
    "        size => count(employee),\n",
    "        head => employee:first(salary)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Use `:dataframe` combinator to generate `DataFrame` output."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>name</th><th>surname</th><th>department</th><th>position</th><th>salary</th></tr><tr><th>1</th><td>ELVIA</td><td>A</td><td>WATER MGMNT</td><td>WATER RATE TAKER</td><td>88968</td></tr><tr><th>2</th><td>JEFFERY</td><td>A</td><td>POLICE</td><td>POLICE OFFICER</td><td>80778</td></tr><tr><th>3</th><td>KARINA</td><td>A</td><td>POLICE</td><td>POLICE OFFICER</td><td>80778</td></tr><tr><th>4</th><td>KIMBERLEI</td><td>A</td><td>GENERAL SERVICES</td><td>CHIEF CONTRACT EXPEDITER</td><td>84780</td></tr><tr><th>5</th><td>VICENTE</td><td>A</td><td>WATER MGMNT</td><td>CIVIL ENGINEER IV</td><td>104736</td></tr><tr><th>6</th><td>ANABEL</td><td>A</td><td>CITY COUNCIL</td><td>ASST TO THE ALDERMAN</td><td>70764</td></tr><tr><th>7</th><td>EMMANUEL</td><td>A</td><td>STREETS &amp; SAN</td><td>GENERAL LABORER - DSS</td><td>40560</td></tr><tr><th>8</th><td>ROBERT</td><td>A</td><td>AVIATION</td><td>ELECTRICAL MECHANIC</td><td>91520</td></tr><tr><th>9</th><td>JAMES</td><td>A</td><td>FIRE</td><td>FIRE ENGINEER</td><td>90456</td></tr><tr><th>10</th><td>TERRY</td><td>A</td><td>POLICE</td><td>POLICE OFFICER</td><td>86520</td></tr><tr><th>11</th><td>BETTY</td><td>A</td><td>FAMILY &amp; SUPPORT</td><td>FOSTER GRANDPARENT</td><td>2756</td></tr><tr><th>12</th><td>LYNISE</td><td>A</td><td>POLICE</td><td>CLERK III</td><td>43920</td></tr><tr><th>13</th><td>WILLIAM</td><td>A</td><td>IPRA</td><td>INVESTIGATOR - IPRA II</td><td>72468</td></tr><tr><th>14</th><td>ZAID</td><td>A</td><td>POLICE</td><td>POLICE OFFICER</td><td>69684</td></tr><tr><th>15</th><td>ABDALMAHD</td><td>A</td><td>POLICE</td><td>POLICE OFFICER</td><td>80778</td></tr><tr><th>&vellip;</th><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td></tr></table>"
      ],
      "text/plain": [
       "32181x5 DataFrames.DataFrame\n",
       "| Row   | name        | surname | department         |\n",
       "|-------|-------------|---------|--------------------|\n",
       "| 1     | \"ELVIA\"     | \"A\"     | \"WATER MGMNT\"      |\n",
       "| 2     | \"JEFFERY\"   | \"A\"     | \"POLICE\"           |\n",
       "| 3     | \"KARINA\"    | \"A\"     | \"POLICE\"           |\n",
       "| 4     | \"KIMBERLEI\" | \"A\"     | \"GENERAL SERVICES\" |\n",
       "⋮\n",
       "| 32177 | \"MICHAEL\"   | \"Z\"     | \"GENERAL SERVICES\" |\n",
       "| 32178 | \"PETER\"     | \"Z\"     | \"POLICE\"           |\n",
       "| 32179 | \"MARK\"      | \"Z\"     | \"POLICE\"           |\n",
       "| 32180 | \"CARLO\"     | \"Z\"     | \"POLICE\"           |\n",
       "| 32181 | \"DARIUSZ\"   | \"Z\"     | \"DoIT\"             |\n",
       "\n",
       "| Row   | position                         | salary |\n",
       "|-------|----------------------------------|--------|\n",
       "| 1     | \"WATER RATE TAKER\"               | 88968  |\n",
       "| 2     | \"POLICE OFFICER\"                 | 80778  |\n",
       "| 3     | \"POLICE OFFICER\"                 | 80778  |\n",
       "| 4     | \"CHIEF CONTRACT EXPEDITER\"       | 84780  |\n",
       "⋮\n",
       "| 32177 | \"FRM OF MACHINISTS - AUTOMOTIVE\" | 97448  |\n",
       "| 32178 | \"POLICE OFFICER\"                 | 86520  |\n",
       "| 32179 | \"POLICE OFFICER\"                 | 83616  |\n",
       "| 32180 | \"POLICE OFFICER\"                 | 86520  |\n",
       "| 32181 | \"CHIEF DATA BASE ANALYST\"        | 110352 |"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(employee:dataframe)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>name</th><th>size</th><th>max_salary</th></tr><tr><th>1</th><td>WATER MGMNT</td><td>1848</td><td>169512</td></tr><tr><th>2</th><td>POLICE</td><td>13570</td><td>260004</td></tr><tr><th>3</th><td>GENERAL SERVICES</td><td>924</td><td>157092</td></tr><tr><th>4</th><td>CITY COUNCIL</td><td>397</td><td>160248</td></tr><tr><th>5</th><td>STREETS &amp; SAN</td><td>2090</td><td>157092</td></tr><tr><th>6</th><td>AVIATION</td><td>1344</td><td>161652</td></tr><tr><th>7</th><td>FIRE</td><td>4875</td><td>202728</td></tr><tr><th>8</th><td>FAMILY &amp; SUPPORT</td><td>679</td><td>157092</td></tr><tr><th>9</th><td>IPRA</td><td>83</td><td>161856</td></tr><tr><th>10</th><td>PUBLIC LIBRARY</td><td>951</td><td>167004</td></tr><tr><th>11</th><td>DoIT</td><td>106</td><td>154992</td></tr><tr><th>12</th><td>BUSINESS AFFAIRS</td><td>173</td><td>157092</td></tr><tr><th>13</th><td>OEMC</td><td>1135</td><td>167796</td></tr><tr><th>14</th><td>TRANSPORTN</td><td>1200</td><td>169500</td></tr><tr><th>15</th><td>HEALTH</td><td>555</td><td>177000</td></tr><tr><th>&vellip;</th><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td></tr></table>"
      ],
      "text/plain": [
       "35x3 DataFrames.DataFrame\n",
       "| Row | name                | size  | max_salary |\n",
       "|-----|---------------------|-------|------------|\n",
       "| 1   | \"WATER MGMNT\"       | 1848  | 169512     |\n",
       "| 2   | \"POLICE\"            | 13570 | 260004     |\n",
       "| 3   | \"GENERAL SERVICES\"  | 924   | 157092     |\n",
       "| 4   | \"CITY COUNCIL\"      | 397   | 160248     |\n",
       "⋮\n",
       "| 31  | \"BOARD OF ETHICS\"   | 9     | 131688     |\n",
       "| 32  | \"POLICE BOARD\"      | 2     | 97728      |\n",
       "| 33  | \"BUDGET & MGMT\"     | 43    | 169992     |\n",
       "| 34  | \"ADMIN HEARNG\"      | 39    | 156420     |\n",
       "| 35  | \"LICENSE APPL COMM\" | 1     | 69888      |"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    department\n",
    "    :select(\n",
    "        name,\n",
    "        size => count(employee),\n",
    "        max_salary => max(employee.salary))\n",
    "    :dataframe)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can pass a list of output fields to the `dataframe` combinator."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>name</th><th>size</th><th>max_salary</th></tr><tr><th>1</th><td>WATER MGMNT</td><td>1848</td><td>169512</td></tr><tr><th>2</th><td>POLICE</td><td>13570</td><td>260004</td></tr><tr><th>3</th><td>GENERAL SERVICES</td><td>924</td><td>157092</td></tr><tr><th>4</th><td>CITY COUNCIL</td><td>397</td><td>160248</td></tr><tr><th>5</th><td>STREETS &amp; SAN</td><td>2090</td><td>157092</td></tr><tr><th>6</th><td>AVIATION</td><td>1344</td><td>161652</td></tr><tr><th>7</th><td>FIRE</td><td>4875</td><td>202728</td></tr><tr><th>8</th><td>FAMILY &amp; SUPPORT</td><td>679</td><td>157092</td></tr><tr><th>9</th><td>IPRA</td><td>83</td><td>161856</td></tr><tr><th>10</th><td>PUBLIC LIBRARY</td><td>951</td><td>167004</td></tr><tr><th>11</th><td>DoIT</td><td>106</td><td>154992</td></tr><tr><th>12</th><td>BUSINESS AFFAIRS</td><td>173</td><td>157092</td></tr><tr><th>13</th><td>OEMC</td><td>1135</td><td>167796</td></tr><tr><th>14</th><td>TRANSPORTN</td><td>1200</td><td>169500</td></tr><tr><th>15</th><td>HEALTH</td><td>555</td><td>177000</td></tr><tr><th>&vellip;</th><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td></tr></table>"
      ],
      "text/plain": [
       "35x3 DataFrames.DataFrame\n",
       "| Row | name                | size  | max_salary |\n",
       "|-----|---------------------|-------|------------|\n",
       "| 1   | \"WATER MGMNT\"       | 1848  | 169512     |\n",
       "| 2   | \"POLICE\"            | 13570 | 260004     |\n",
       "| 3   | \"GENERAL SERVICES\"  | 924   | 157092     |\n",
       "| 4   | \"CITY COUNCIL\"      | 397   | 160248     |\n",
       "⋮\n",
       "| 31  | \"BOARD OF ETHICS\"   | 9     | 131688     |\n",
       "| 32  | \"POLICE BOARD\"      | 2     | 97728      |\n",
       "| 33  | \"BUDGET & MGMT\"     | 43    | 169992     |\n",
       "| 34  | \"ADMIN HEARNG\"      | 39    | 156420     |\n",
       "| 35  | \"LICENSE APPL COMM\" | 1     | 69888      |"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    department\n",
    "    :dataframe(\n",
    "        name,\n",
    "        size => count(employee),\n",
    "        max_salary => max(employee.salary)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Cartesian product and tagged union\n",
    "\n",
    "Use `:mix` to generate a Cartesian product.\n",
    "\n",
    "*Multiplication table.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "165-element Array{Tuple{Int64,Int64,Int64,Int64},1}:\n",
       " (2,2,2,8)      \n",
       " (2,2,3,12)     \n",
       " (2,2,4,16)     \n",
       " (2,2,5,20)     \n",
       " (2,2,6,24)     \n",
       " ⋮              \n",
       " (8,10,10,800)  \n",
       " (9,9,9,729)    \n",
       " (9,9,10,810)   \n",
       " (9,10,10,900)  \n",
       " (10,10,10,1000)"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    mix(a => range(2,1,10),\n",
    "        b => range(2,1,10),\n",
    "        c => range(2,1,10))\n",
    "    :filter((a <= b) & (b <= c))\n",
    "    :select(a, b, c, (a*b)*c))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*All pairs of departments with approximately equal number of employees.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "16-element Array{Tuple{UTF8String,Int64,UTF8String,Int64},1}:\n",
       " (\"IPRA\",83,\"CITY CLERK\",82)                \n",
       " (\"IPRA\",83,\"PROCUREMENT\",81)               \n",
       " (\"ANIMAL CONTRL\",67,\"HUMAN RESOURCES\",68)  \n",
       " (\"CITY CLERK\",82,\"IPRA\",83)                \n",
       " (\"CITY CLERK\",82,\"PROCUREMENT\",81)         \n",
       " ⋮                                          \n",
       " (\"BOARD OF ETHICS\",9,\"LICENSE APPL COMM\",1)\n",
       " (\"POLICE BOARD\",2,\"BOARD OF ETHICS\",9)     \n",
       " (\"POLICE BOARD\",2,\"LICENSE APPL COMM\",1)   \n",
       " (\"LICENSE APPL COMM\",1,\"BOARD OF ETHICS\",9)\n",
       " (\"LICENSE APPL COMM\",1,\"POLICE BOARD\",2)   "
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    mix(department, department)\n",
    "    :filter((left.id != right.id) & (left.count(employee)/10 == right.count(employee)/10))\n",
    "    :select(left.name, left.count(employee), right.name, right.count(employee)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Use `pack()` combinator to generate a tagged union."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10-element Array{Int64,1}:\n",
       "  1\n",
       "  2\n",
       "  3\n",
       "  4\n",
       "  5\n",
       " 95\n",
       " 96\n",
       " 97\n",
       " 98\n",
       " 99"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    pack(\n",
    "        a => range(1,1,5),\n",
    "        z => range(95,1,99)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Values generated by `pack()` don't have to have the same type."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "32216-element Array{Tuple{UTF8String,Vararg{Any}},1}:\n",
       " (\"ELVIA\",\"A\",\"WATER MGMNT\",\"WATER RATE TAKER\",88968)                 \n",
       " (\"JEFFERY\",\"A\",\"POLICE\",\"POLICE OFFICER\",80778)                      \n",
       " (\"KARINA\",\"A\",\"POLICE\",\"POLICE OFFICER\",80778)                       \n",
       " (\"KIMBERLEI\",\"A\",\"GENERAL SERVICES\",\"CHIEF CONTRACT EXPEDITER\",84780)\n",
       " (\"VICENTE\",\"A\",\"WATER MGMNT\",\"CIVIL ENGINEER IV\",104736)             \n",
       " ⋮                                                                    \n",
       " (\"BOARD OF ETHICS\",)                                                 \n",
       " (\"POLICE BOARD\",)                                                    \n",
       " (\"BUDGET & MGMT\",)                                                   \n",
       " (\"ADMIN HEARNG\",)                                                    \n",
       " (\"LICENSE APPL COMM\",)                                               "
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(pack(employee, department))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can extract tagged values using combinators named after the tags."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "32181-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}:\n",
       " (\"ELVIA\",\"A\",\"WATER MGMNT\",\"WATER RATE TAKER\",88968)                     \n",
       " (\"JEFFERY\",\"A\",\"POLICE\",\"POLICE OFFICER\",80778)                          \n",
       " (\"KARINA\",\"A\",\"POLICE\",\"POLICE OFFICER\",80778)                           \n",
       " (\"KIMBERLEI\",\"A\",\"GENERAL SERVICES\",\"CHIEF CONTRACT EXPEDITER\",84780)    \n",
       " (\"VICENTE\",\"A\",\"WATER MGMNT\",\"CIVIL ENGINEER IV\",104736)                 \n",
       " ⋮                                                                        \n",
       " (\"MICHAEL\",\"Z\",\"GENERAL SERVICES\",\"FRM OF MACHINISTS - AUTOMOTIVE\",97448)\n",
       " (\"PETER\",\"Z\",\"POLICE\",\"POLICE OFFICER\",86520)                            \n",
       " (\"MARK\",\"Z\",\"POLICE\",\"POLICE OFFICER\",83616)                             \n",
       " (\"CARLO\",\"Z\",\"POLICE\",\"POLICE OFFICER\",86520)                            \n",
       " (\"DARIUSZ\",\"Z\",\"DoIT\",\"CHIEF DATA BASE ANALYST\",110352)                  "
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(pack(employee, department).employee)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "35-element Array{Tuple{UTF8String},1}:\n",
       " (\"WATER MGMNT\",)      \n",
       " (\"POLICE\",)           \n",
       " (\"GENERAL SERVICES\",) \n",
       " (\"CITY COUNCIL\",)     \n",
       " (\"STREETS & SAN\",)    \n",
       " ⋮                     \n",
       " (\"BOARD OF ETHICS\",)  \n",
       " (\"POLICE BOARD\",)     \n",
       " (\"BUDGET & MGMT\",)    \n",
       " (\"ADMIN HEARNG\",)     \n",
       " (\"LICENSE APPL COMM\",)"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(pack(employee, department).department)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "32216-element Array{Tuple{Nullable{UTF8String},Nullable{UTF8String}},1}:\n",
       " (Nullable(\"WATER RATE TAKER\"),Nullable{UTF8String}())        \n",
       " (Nullable(\"POLICE OFFICER\"),Nullable{UTF8String}())          \n",
       " (Nullable(\"POLICE OFFICER\"),Nullable{UTF8String}())          \n",
       " (Nullable(\"CHIEF CONTRACT EXPEDITER\"),Nullable{UTF8String}())\n",
       " (Nullable(\"CIVIL ENGINEER IV\"),Nullable{UTF8String}())       \n",
       " ⋮                                                            \n",
       " (Nullable{UTF8String}(),Nullable(\"BOARD OF ETHICS\"))         \n",
       " (Nullable{UTF8String}(),Nullable(\"POLICE BOARD\"))            \n",
       " (Nullable{UTF8String}(),Nullable(\"BUDGET & MGMT\"))           \n",
       " (Nullable{UTF8String}(),Nullable(\"ADMIN HEARNG\"))            \n",
       " (Nullable{UTF8String}(),Nullable(\"LICENSE APPL COMM\"))       "
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(pack(employee, department):select(employee.position, department.name))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Use `unlink` to create an unconditional link to an entity class.\n",
    "\n",
    "*Find the employees with salary within 50% of the top salary.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}:\n",
       " (\"BRADLEY\",\"A\",\"FIRE\",\"CAPTAIN-EMT\",131466)                                     \n",
       " (\"DANA\",\"A\",\"POLICE\",\"DEPUTY CHIEF\",170112)                                     \n",
       " (\"MICHAEL\",\"A\",\"POLICE\",\"CAPTAIN\",134820)                                       \n",
       " (\"SANDRA\",\"A\",\"ANIMAL CONTRL\",\"EXECUTIVE DIR OF ANIMAL CARE AND CONTROL\",138420)\n",
       " (\"VERDIE\",\"A\",\"FIRE\",\"ASST DEPUTY CHIEF PARAMEDIC\",156360)                      "
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    employee\n",
    "    :take(500)\n",
    "    :filter(salary > max(unlink(employee).salary)/2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Use `link` to create a link on an arbitrary condition.\n",
    "\n",
    "*For a given employee, find all his namesakes.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Nullable((\"TERRY\",36,[(\"TERRY\",\"A\",\"TRANSPORTN\",\"FOREMAN OF CONSTRUCTION LABORERS\",81328),(\"TERRY\",\"B\",\"POLICE\",\"POLICE OFFICER\",83616),(\"TERRY\",\"B\",\"POLICE\",\"POLICE OFFICER\",86520)]))"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    employee[10]\n",
    "    :define(namesake => link((left.id!=right.id)&(left.name==right.name), employee))\n",
    "    :select(name, count(namesake), namesake:take(3)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Queries with parameteres\n",
    "\n",
    "*Find all employees with the given name and position.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "36-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}:\n",
       " (\"CHARLES\",\"A\",\"POLICE\",\"POLICE OFFICER\",46206)\n",
       " (\"CHARLES\",\"A\",\"POLICE\",\"POLICE OFFICER\",89718)\n",
       " (\"CHARLES\",\"A\",\"POLICE\",\"POLICE OFFICER\",89718)\n",
       " (\"CHARLES\",\"B\",\"POLICE\",\"POLICE OFFICER\",83616)\n",
       " (\"CHARLES\",\"B\",\"POLICE\",\"POLICE OFFICER\",89718)\n",
       " ⋮                                              \n",
       " (\"CHARLES\",\"T\",\"POLICE\",\"POLICE OFFICER\",46206)\n",
       " (\"CHARLES\",\"W\",\"POLICE\",\"POLICE OFFICER\",92316)\n",
       " (\"CHARLES\",\"W\",\"POLICE\",\"POLICE OFFICER\",86520)\n",
       " (\"CHARLES\",\"W\",\"POLICE\",\"POLICE OFFICER\",86520)\n",
       " (\"CHARLES\",\"W\",\"POLICE\",\"POLICE OFFICER\",83616)"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    employee:filter((position==POSITION) & (name==NAME)),\n",
    "    POSITION=\"POLICE OFFICER\",\n",
    "    NAME=\"CHARLES\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Find all departments bigger than the given size.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7-element Array{Tuple{UTF8String,Int64},1}:\n",
       " (\"WATER MGMNT\",848)   \n",
       " (\"POLICE\",12570)      \n",
       " (\"STREETS & SAN\",1090)\n",
       " (\"AVIATION\",344)      \n",
       " (\"FIRE\",3875)         \n",
       " (\"OEMC\",135)          \n",
       " (\"TRANSPORTN\",200)    "
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    department\n",
    "    :filter(count(employee)>SIZE)\n",
    "    :select(name, count(employee)-SIZE),\n",
    "    SIZE=1000)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Find all employees in the given departments.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "18445-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}:\n",
       " (\"JEFFERY\",\"A\",\"POLICE\",\"POLICE OFFICER\",80778)\n",
       " (\"KARINA\",\"A\",\"POLICE\",\"POLICE OFFICER\",80778) \n",
       " (\"JAMES\",\"A\",\"FIRE\",\"FIRE ENGINEER\",90456)     \n",
       " (\"TERRY\",\"A\",\"POLICE\",\"POLICE OFFICER\",86520)  \n",
       " (\"LYNISE\",\"A\",\"POLICE\",\"CLERK III\",43920)      \n",
       " ⋮                                              \n",
       " (\"MATTHEW\",\"Z\",\"FIRE\",\"FIREFIGHTER-EMT\",91764) \n",
       " (\"BRYAN\",\"Z\",\"POLICE\",\"POLICE OFFICER\",80778)  \n",
       " (\"PETER\",\"Z\",\"POLICE\",\"POLICE OFFICER\",86520)  \n",
       " (\"MARK\",\"Z\",\"POLICE\",\"POLICE OFFICER\",83616)   \n",
       " (\"CARLO\",\"Z\",\"POLICE\",\"POLICE OFFICER\",86520)  "
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    employee:filter(department.name in DEPTS),\n",
    "    DEPTS=[\"POLICE\", \"FIRE\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Parameters could also be calculated dynamically using combinator `given`.\n",
    "\n",
    "*Find the highest paid employee.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}:\n",
       " (\"GARRY\",\"M\",\"POLICE\",\"SUPERINTENDENT OF POLICE\",260004)"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    employee\n",
    "    :filter(salary==MAX_SALARY)\n",
    "    :given(MAX_SALARY => max(employee.salary)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Find the highest paid employee in each department.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "35-element Array{Tuple{UTF8String,Array{Tuple{UTF8String,UTF8String},1}},1}:\n",
       " (\"WATER MGMNT\",[(\"THOMAS\",\"P\")])        \n",
       " (\"POLICE\",[(\"GARRY\",\"M\")])              \n",
       " (\"GENERAL SERVICES\",[(\"DAVID\",\"R\")])    \n",
       " (\"CITY COUNCIL\",[(\"MARLA\",\"K\")])        \n",
       " (\"STREETS & SAN\",[(\"CHARLES\",\"W\")])     \n",
       " ⋮                                       \n",
       " (\"BOARD OF ETHICS\",[(\"STEVEN\",\"B\")])    \n",
       " (\"POLICE BOARD\",[(\"MAX\",\"C\")])          \n",
       " (\"BUDGET & MGMT\",[(\"ALEXANDRA\",\"H\")])   \n",
       " (\"ADMIN HEARNG\",[(\"PATRICIA\",\"J\")])     \n",
       " (\"LICENSE APPL COMM\",[(\"MICHELLE\",\"G\")])"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    department\n",
    "    :select(\n",
    "        name,\n",
    "        employee\n",
    "            :filter(salary==MAX_SALARY)\n",
    "            :given(MAX_SALARY => max(employee.salary))))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Navigating the context\n",
    "\n",
    "Sometimes you may want to refer to other values in the output.  Combinators `before`, `after` and `around` allow you to establish a link between output values.\n",
    "\n",
    "Combinator `before` refers to the set of previous values in the output."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>name</th><th>past_names</th></tr><tr><th>1</th><td>WATER MGMNT</td><td>UTF8String[]</td></tr><tr><th>2</th><td>POLICE</td><td>UTF8String[\"WATER MGMNT\"]</td></tr><tr><th>3</th><td>GENERAL SERVICES</td><td>UTF8String[\"POLICE\",\"WATER MGMNT\"]</td></tr><tr><th>4</th><td>CITY COUNCIL</td><td>UTF8String[\"GENERAL SERVICES\",\"POLICE\",\"WATER MGMNT\"]</td></tr><tr><th>5</th><td>STREETS &amp; SAN</td><td>UTF8String[\"CITY COUNCIL\",\"GENERAL SERVICES\",\"POLICE\",\"WATER MGMNT\"]</td></tr><tr><th>6</th><td>AVIATION</td><td>UTF8String[\"STREETS &amp; SAN\",\"CITY COUNCIL\",\"GENERAL SERVICES\",\"POLICE\",\"WATER MGMNT\"]</td></tr><tr><th>7</th><td>FIRE</td><td>UTF8String[\"AVIATION\",\"STREETS &amp; SAN\",\"CITY COUNCIL\",\"GENERAL SERVICES\",\"POLICE\",\"WATER MGMNT\"]</td></tr><tr><th>8</th><td>FAMILY &amp; SUPPORT</td><td>UTF8String[\"FIRE\",\"AVIATION\",\"STREETS &amp; SAN\",\"CITY COUNCIL\",\"GENERAL SERVICES\",\"POLICE\",\"WATER MGMNT\"]</td></tr><tr><th>9</th><td>IPRA</td><td>UTF8String[\"FAMILY &amp; SUPPORT\",\"FIRE\",\"AVIATION\",\"STREETS &amp; SAN\",\"CITY COUNCIL\",\"GENERAL SERVICES\",\"POLICE\",\"WATER MGMNT\"]</td></tr><tr><th>10</th><td>PUBLIC LIBRARY</td><td>UTF8String[\"IPRA\",\"FAMILY &amp; SUPPORT\",\"FIRE\",\"AVIATION\",\"STREETS &amp; SAN\",\"CITY COUNCIL\",\"GENERAL SERVICES\",\"POLICE\",\"WATER MGMNT\"]</td></tr><tr><th>11</th><td>DoIT</td><td>UTF8String[\"PUBLIC LIBRARY\",\"IPRA\",\"FAMILY &amp; SUPPORT\",\"FIRE\",\"AVIATION\",\"STREETS &amp; SAN\",\"CITY COUNCIL\",\"GENERAL SERVICES\",\"POLICE\",\"WATER MGMNT\"]</td></tr><tr><th>12</th><td>BUSINESS AFFAIRS</td><td>UTF8String[\"DoIT\",\"PUBLIC LIBRARY\",\"IPRA\",\"FAMILY &amp; SUPPORT\",\"FIRE\",\"AVIATION\",\"STREETS &amp; SAN\",\"CITY COUNCIL\",\"GENERAL SERVICES\",\"POLICE\",\"WATER MGMNT\"]</td></tr><tr><th>13</th><td>OEMC</td><td>UTF8String[\"BUSINESS AFFAIRS\",\"DoIT\",\"PUBLIC LIBRARY\",\"IPRA\",\"FAMILY &amp; SUPPORT\",\"FIRE\",\"AVIATION\",\"STREETS &amp; SAN\",\"CITY COUNCIL\",\"GENERAL SERVICES\",\"POLICE\",\"WATER MGMNT\"]</td></tr><tr><th>14</th><td>TRANSPORTN</td><td>UTF8String[\"OEMC\",\"BUSINESS AFFAIRS\",\"DoIT\",\"PUBLIC LIBRARY\",\"IPRA\",\"FAMILY &amp; SUPPORT\",\"FIRE\",\"AVIATION\",\"STREETS &amp; SAN\",\"CITY COUNCIL\",\"GENERAL SERVICES\",\"POLICE\",\"WATER MGMNT\"]</td></tr><tr><th>15</th><td>HEALTH</td><td>UTF8String[\"TRANSPORTN\",\"OEMC\",\"BUSINESS AFFAIRS\",\"DoIT\",\"PUBLIC LIBRARY\",\"IPRA\",\"FAMILY &amp; SUPPORT\",\"FIRE\",\"AVIATION\",\"STREETS &amp; SAN\",\"CITY COUNCIL\",\"GENERAL SERVICES\",\"POLICE\",\"WATER MGMNT\"]</td></tr><tr><th>&vellip;</th><td>&vellip;</td><td>&vellip;</td></tr></table>"
      ],
      "text/plain": [
       "35x2 DataFrames.DataFrame\n",
       "| Row | name                |\n",
       "|-----|---------------------|\n",
       "| 1   | \"WATER MGMNT\"       |\n",
       "| 2   | \"POLICE\"            |\n",
       "| 3   | \"GENERAL SERVICES\"  |\n",
       "| 4   | \"CITY COUNCIL\"      |\n",
       "⋮\n",
       "| 31  | \"BOARD OF ETHICS\"   |\n",
       "| 32  | \"POLICE BOARD\"      |\n",
       "| 33  | \"BUDGET & MGMT\"     |\n",
       "| 34  | \"ADMIN HEARNG\"      |\n",
       "| 35  | \"LICENSE APPL COMM\" |\n",
       "\n",
       "| Row | past_names                                                                                                                                                                                                                                                                                                                |\n",
       "|-----|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|\n",
       "| 1   | UTF8String[]                                                                                                                                                                                                                                                                                                              |\n",
       "| 2   | UTF8String[\"WATER MGMNT\"]                                                                                                                                                                                                                                                                                                 |\n",
       "| 3   | UTF8String[\"POLICE\",\"WATER MGMNT\"]                                                                                                                                                                                                                                                                                        |\n",
       "| 4   | UTF8String[\"GENERAL SERVICES\",\"POLICE\",\"WATER MGMNT\"]                                                                                                                                                                                                                                                                     |\n",
       "⋮\n",
       "| 31  | UTF8String[\"HUMAN RELATIONS\",\"PROCUREMENT\",\"HUMAN RESOURCES\",\"DISABILITIES\",\"TREASURER\",\"INSPECTOR GEN\",\"BOARD OF ELECTION\",\"CITY CLERK\",\"ANIMAL CONTRL\",\"BUILDINGS\"  …  \"PUBLIC LIBRARY\",\"IPRA\",\"FAMILY & SUPPORT\",\"FIRE\",\"AVIATION\",\"STREETS & SAN\",\"CITY COUNCIL\",\"GENERAL SERVICES\",\"POLICE\",\"WATER MGMNT\"]         |\n",
       "| 32  | UTF8String[\"BOARD OF ETHICS\",\"HUMAN RELATIONS\",\"PROCUREMENT\",\"HUMAN RESOURCES\",\"DISABILITIES\",\"TREASURER\",\"INSPECTOR GEN\",\"BOARD OF ELECTION\",\"CITY CLERK\",\"ANIMAL CONTRL\"  …  \"PUBLIC LIBRARY\",\"IPRA\",\"FAMILY & SUPPORT\",\"FIRE\",\"AVIATION\",\"STREETS & SAN\",\"CITY COUNCIL\",\"GENERAL SERVICES\",\"POLICE\",\"WATER MGMNT\"]   |\n",
       "| 33  | UTF8String[\"POLICE BOARD\",\"BOARD OF ETHICS\",\"HUMAN RELATIONS\",\"PROCUREMENT\",\"HUMAN RESOURCES\",\"DISABILITIES\",\"TREASURER\",\"INSPECTOR GEN\",\"BOARD OF ELECTION\",\"CITY CLERK\"  …  \"PUBLIC LIBRARY\",\"IPRA\",\"FAMILY & SUPPORT\",\"FIRE\",\"AVIATION\",\"STREETS & SAN\",\"CITY COUNCIL\",\"GENERAL SERVICES\",\"POLICE\",\"WATER MGMNT\"]    |\n",
       "| 34  | UTF8String[\"BUDGET & MGMT\",\"POLICE BOARD\",\"BOARD OF ETHICS\",\"HUMAN RELATIONS\",\"PROCUREMENT\",\"HUMAN RESOURCES\",\"DISABILITIES\",\"TREASURER\",\"INSPECTOR GEN\",\"BOARD OF ELECTION\"  …  \"PUBLIC LIBRARY\",\"IPRA\",\"FAMILY & SUPPORT\",\"FIRE\",\"AVIATION\",\"STREETS & SAN\",\"CITY COUNCIL\",\"GENERAL SERVICES\",\"POLICE\",\"WATER MGMNT\"] |\n",
       "| 35  | UTF8String[\"ADMIN HEARNG\",\"BUDGET & MGMT\",\"POLICE BOARD\",\"BOARD OF ETHICS\",\"HUMAN RELATIONS\",\"PROCUREMENT\",\"HUMAN RESOURCES\",\"DISABILITIES\",\"TREASURER\",\"INSPECTOR GEN\"  …  \"PUBLIC LIBRARY\",\"IPRA\",\"FAMILY & SUPPORT\",\"FIRE\",\"AVIATION\",\"STREETS & SAN\",\"CITY COUNCIL\",\"GENERAL SERVICES\",\"POLICE\",\"WATER MGMNT\"]      |"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    department\n",
    "    :dataframe(\n",
    "        name,\n",
    "        past_names => before.name))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Similarly, combinator `after` refers to all the subsequent values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>name</th><th>next_name</th></tr><tr><th>1</th><td>WATER MGMNT</td><td>POLICE</td></tr><tr><th>2</th><td>POLICE</td><td>GENERAL SERVICES</td></tr><tr><th>3</th><td>GENERAL SERVICES</td><td>CITY COUNCIL</td></tr><tr><th>4</th><td>CITY COUNCIL</td><td>STREETS &amp; SAN</td></tr><tr><th>5</th><td>STREETS &amp; SAN</td><td>AVIATION</td></tr><tr><th>6</th><td>AVIATION</td><td>FIRE</td></tr><tr><th>7</th><td>FIRE</td><td>FAMILY &amp; SUPPORT</td></tr><tr><th>8</th><td>FAMILY &amp; SUPPORT</td><td>IPRA</td></tr><tr><th>9</th><td>IPRA</td><td>PUBLIC LIBRARY</td></tr><tr><th>10</th><td>PUBLIC LIBRARY</td><td>DoIT</td></tr><tr><th>11</th><td>DoIT</td><td>BUSINESS AFFAIRS</td></tr><tr><th>12</th><td>BUSINESS AFFAIRS</td><td>OEMC</td></tr><tr><th>13</th><td>OEMC</td><td>TRANSPORTN</td></tr><tr><th>14</th><td>TRANSPORTN</td><td>HEALTH</td></tr><tr><th>15</th><td>HEALTH</td><td>MAYOR'S OFFICE</td></tr><tr><th>&vellip;</th><td>&vellip;</td><td>&vellip;</td></tr></table>"
      ],
      "text/plain": [
       "35x2 DataFrames.DataFrame\n",
       "| Row | name                | next_name           |\n",
       "|-----|---------------------|---------------------|\n",
       "| 1   | \"WATER MGMNT\"       | \"POLICE\"            |\n",
       "| 2   | \"POLICE\"            | \"GENERAL SERVICES\"  |\n",
       "| 3   | \"GENERAL SERVICES\"  | \"CITY COUNCIL\"      |\n",
       "| 4   | \"CITY COUNCIL\"      | \"STREETS & SAN\"     |\n",
       "⋮\n",
       "| 31  | \"BOARD OF ETHICS\"   | \"POLICE BOARD\"      |\n",
       "| 32  | \"POLICE BOARD\"      | \"BUDGET & MGMT\"     |\n",
       "| 33  | \"BUDGET & MGMT\"     | \"ADMIN HEARNG\"      |\n",
       "| 34  | \"ADMIN HEARNG\"      | \"LICENSE APPL COMM\" |\n",
       "| 35  | \"LICENSE APPL COMM\" | NA                  |"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    department\n",
    "    :dataframe(\n",
    "        name,\n",
    "        next_name => first(after).name))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can use the `before` combinator to number output records."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "35-element Array{Tuple{Int64,UTF8String},1}:\n",
       " (1,\"WATER MGMNT\")       \n",
       " (2,\"POLICE\")            \n",
       " (3,\"GENERAL SERVICES\")  \n",
       " (4,\"CITY COUNCIL\")      \n",
       " (5,\"STREETS & SAN\")     \n",
       " ⋮                       \n",
       " (31,\"BOARD OF ETHICS\")  \n",
       " (32,\"POLICE BOARD\")     \n",
       " (33,\"BUDGET & MGMT\")    \n",
       " (34,\"ADMIN HEARNG\")     \n",
       " (35,\"LICENSE APPL COMM\")"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    department\n",
    "    :select(1+count(before), name))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A variant of `before` called `and_before` includes the current record in the set.  You can also use `and_before` to calculate running totals."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>name</th><th>size</th><th>total</th></tr><tr><th>1</th><td>WATER MGMNT</td><td>1848</td><td>1848</td></tr><tr><th>2</th><td>POLICE</td><td>13570</td><td>15418</td></tr><tr><th>3</th><td>GENERAL SERVICES</td><td>924</td><td>16342</td></tr><tr><th>4</th><td>CITY COUNCIL</td><td>397</td><td>16739</td></tr><tr><th>5</th><td>STREETS &amp; SAN</td><td>2090</td><td>18829</td></tr><tr><th>6</th><td>AVIATION</td><td>1344</td><td>20173</td></tr><tr><th>7</th><td>FIRE</td><td>4875</td><td>25048</td></tr><tr><th>8</th><td>FAMILY &amp; SUPPORT</td><td>679</td><td>25727</td></tr><tr><th>9</th><td>IPRA</td><td>83</td><td>25810</td></tr><tr><th>10</th><td>PUBLIC LIBRARY</td><td>951</td><td>26761</td></tr><tr><th>11</th><td>DoIT</td><td>106</td><td>26867</td></tr><tr><th>12</th><td>BUSINESS AFFAIRS</td><td>173</td><td>27040</td></tr><tr><th>13</th><td>OEMC</td><td>1135</td><td>28175</td></tr><tr><th>14</th><td>TRANSPORTN</td><td>1200</td><td>29375</td></tr><tr><th>15</th><td>HEALTH</td><td>555</td><td>29930</td></tr><tr><th>&vellip;</th><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td></tr></table>"
      ],
      "text/plain": [
       "35x3 DataFrames.DataFrame\n",
       "| Row | name                | size  | total |\n",
       "|-----|---------------------|-------|-------|\n",
       "| 1   | \"WATER MGMNT\"       | 1848  | 1848  |\n",
       "| 2   | \"POLICE\"            | 13570 | 15418 |\n",
       "| 3   | \"GENERAL SERVICES\"  | 924   | 16342 |\n",
       "| 4   | \"CITY COUNCIL\"      | 397   | 16739 |\n",
       "⋮\n",
       "| 31  | \"BOARD OF ETHICS\"   | 9     | 32096 |\n",
       "| 32  | \"POLICE BOARD\"      | 2     | 32098 |\n",
       "| 33  | \"BUDGET & MGMT\"     | 43    | 32141 |\n",
       "| 34  | \"ADMIN HEARNG\"      | 39    | 32180 |\n",
       "| 35  | \"LICENSE APPL COMM\" | 1     | 32181 |"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    department\n",
    "    :define(size => count(employee))\n",
    "    :dataframe(\n",
    "        name,\n",
    "        size,\n",
    "        total => sum(and_before.size)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Combinator `and_around` let you refer to the full set of the output values.\n",
    "\n",
    "*Find the departments with the largest number of employees.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1-element Array{Tuple{UTF8String,Int64},1}:\n",
       " (\"POLICE\",13570)"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    department\n",
    "    :define(size => count(employee))\n",
    "    :filter(size == max(and_around.size))\n",
    "    :select(name, size))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Combinator `around` can also give you output values that have the same property as the current value.\n",
    "\n",
    "*For each employee in a certain department, find how much does their salary differ from the top salary for their position.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>name</th><th>surname</th><th>position</th><th>salary</th><th>salary_diff</th></tr><tr><th>1</th><td>SAEED</td><td>A</td><td>ASST CITY TREASURER</td><td>85020</td><td>0</td></tr><tr><th>2</th><td>ELIZABETH</td><td>A</td><td>ACCOUNTANT I</td><td>72840</td><td>0</td></tr><tr><th>3</th><td>KONSTANTINES</td><td>A</td><td>ASSISTANT DIRECTOR OF FINANCE</td><td>73080</td><td>0</td></tr><tr><th>4</th><td>SARA</td><td>A</td><td>STUDENT INTERN</td><td>15600</td><td>0</td></tr><tr><th>5</th><td>NANCY</td><td>C</td><td>EXECUTIVE SECRETARY I</td><td>45528</td><td>0</td></tr><tr><th>6</th><td>JULIA</td><td>E</td><td>DEPUTY CITY TREASURER</td><td>113898</td><td>0</td></tr><tr><th>7</th><td>JACQUELINE</td><td>F</td><td>ASST TO THE CITY TREASURER</td><td>76512</td><td>0</td></tr><tr><th>8</th><td>MICHAEL</td><td>F</td><td>PORTFOLIO MANAGER</td><td>64152</td><td>12060</td></tr><tr><th>9</th><td>JAMES</td><td>G</td><td>STAFF ASST</td><td>80328</td><td>0</td></tr><tr><th>10</th><td>CHARU</td><td>G</td><td>ASST TO THE CITY TREASURER</td><td>63516</td><td>12996</td></tr><tr><th>11</th><td>ALEXANDRA</td><td>H</td><td>STUDENT INTERN</td><td>15600</td><td>0</td></tr><tr><th>12</th><td>KAREN</td><td>H</td><td>ACCOUNTANT III</td><td>87912</td><td>0</td></tr><tr><th>13</th><td>GEORGE</td><td>H</td><td>POLICY ANALYST</td><td>45000</td><td>0</td></tr><tr><th>14</th><td>KATHRYN</td><td>H</td><td>DIR OF PUBLIC AFFAIRS</td><td>90252</td><td>0</td></tr><tr><th>15</th><td>NASREEN</td><td>K</td><td>ACCOUNTANT IV</td><td>95880</td><td>0</td></tr><tr><th>&vellip;</th><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td></tr></table>"
      ],
      "text/plain": [
       "24x5 DataFrames.DataFrame\n",
       "| Row | name           | surname | position                        | salary |\n",
       "|-----|----------------|---------|---------------------------------|--------|\n",
       "| 1   | \"SAEED\"        | \"A\"     | \"ASST CITY TREASURER\"           | 85020  |\n",
       "| 2   | \"ELIZABETH\"    | \"A\"     | \"ACCOUNTANT I\"                  | 72840  |\n",
       "| 3   | \"KONSTANTINES\" | \"A\"     | \"ASSISTANT DIRECTOR OF FINANCE\" | 73080  |\n",
       "| 4   | \"SARA\"         | \"A\"     | \"STUDENT INTERN\"                | 15600  |\n",
       "⋮\n",
       "| 20  | \"DANIELLE\"     | \"M\"     | \"ASST CITY TREASURER\"           | 85020  |\n",
       "| 21  | \"MARK\"         | \"M\"     | \"PORTFOLIO MANAGER\"             | 76212  |\n",
       "| 22  | \"KENNETH\"      | \"S\"     | \"ASST CITY TREASURER\"           | 75000  |\n",
       "| 23  | \"ALEXANDRA\"    | \"S\"     | \"DEPUTY CITY TREASURER\"         | 90000  |\n",
       "| 24  | \"KURT\"         | \"S\"     | \"CITY TREASURER\"                | 133545 |\n",
       "\n",
       "| Row | salary_diff |\n",
       "|-----|-------------|\n",
       "| 1   | 0           |\n",
       "| 2   | 0           |\n",
       "| 3   | 0           |\n",
       "| 4   | 0           |\n",
       "⋮\n",
       "| 20  | 0           |\n",
       "| 21  | 0           |\n",
       "| 22  | 10020       |\n",
       "| 23  | 23898       |\n",
       "| 24  | 0           |"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    employee\n",
    "    :filter(department.name == DEPT)\n",
    "    :dataframe(\n",
    "        name,\n",
    "        surname,\n",
    "        position,\n",
    "        salary,\n",
    "        salary_diff => max(and_around(position).salary)-salary),\n",
    "    DEPT=\"TREASURER\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By default, context extends to all values produced by the combinator while executing the query.  You can limit the scope of the context using the `frame` combinator.\n",
    "\n",
    "*Find the highest paid employee in each department.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "35-element Array{Tuple{UTF8String,Array{Tuple{UTF8String,UTF8String},1}},1}:\n",
       " (\"WATER MGMNT\",[(\"THOMAS\",\"P\")])        \n",
       " (\"POLICE\",[(\"GARRY\",\"M\")])              \n",
       " (\"GENERAL SERVICES\",[(\"DAVID\",\"R\")])    \n",
       " (\"CITY COUNCIL\",[(\"MARLA\",\"K\")])        \n",
       " (\"STREETS & SAN\",[(\"CHARLES\",\"W\")])     \n",
       " ⋮                                       \n",
       " (\"BOARD OF ETHICS\",[(\"STEVEN\",\"B\")])    \n",
       " (\"POLICE BOARD\",[(\"MAX\",\"C\")])          \n",
       " (\"BUDGET & MGMT\",[(\"ALEXANDRA\",\"H\")])   \n",
       " (\"ADMIN HEARNG\",[(\"PATRICIA\",\"J\")])     \n",
       " (\"LICENSE APPL COMM\",[(\"MICHELLE\",\"G\")])"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    department\n",
    "    :select(\n",
    "        name,\n",
    "        employee\n",
    "            :filter(salary==max(and_around.salary))\n",
    "            :frame))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Compiling and executing queries\n",
    "You can compile and execute queries separately.  To compile a query, use the `RBT.prepare()` function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "department.employee.name :: Array{UTF8String,1}"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "q1 = RBT.@prepare(department.employee.name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count(department) :: Int64"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "q2 = RBT.@prepare(count(department))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "department:select(name,count(employee)) :: Array{Tuple{UTF8String,Int64},1}"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "q3 = RBT.@prepare(department:select(name,count(employee)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count(employee:filter(&(>(salary,100000),<(salary,200000)))) :: Int64"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "q4 = RBT.@prepare(count(employee:filter((salary>100000)&(salary<200000))))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also prepare a query with parameters."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "*(X,*(Y,Z)) :: {Void, X => Int64, Y => Nullable{Int64}, Z => Array{Int64,1}} -> Array{Int64,1}"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "q5 = RBT.@prepare(X*(Y*Z), X=Int, Y=Nullable{Int}, Z=Vector{Int})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "employee:filter(&(==(name,NAME),>(salary,MIN_SALARY))) :: {Void, MIN_SALARY => Int64, NAME => UTF8String} -> Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "q6 = RBT.@prepare(employee:filter((name == NAME) & (salary > MIN_SALARY)), NAME=UTF8String, MIN_SALARY=Int)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Queries know their parameters."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Pair{Tuple{Vararg{Symbol}},Tuple{Vararg{Type{T}}}}((),())"
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "RBT.params(q1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Pair{Tuple{Vararg{Symbol}},Tuple{Vararg{Type{T}}}}((:X,:Y,:Z),(Int64,Nullable{Int64},Array{Int64,1}))"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "RBT.params(q5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Pair{Tuple{Vararg{Symbol}},Tuple{Vararg{Type{T}}}}((:MIN_SALARY,:NAME),(Int64,UTF8String))"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "RBT.params(q6)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To execute a query, call the compiled query as a function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "32181-element Array{UTF8String,1}:\n",
       " \"ELVIA\"     \n",
       " \"VICENTE\"   \n",
       " \"MUHAMMAD\"  \n",
       " \"GIRLEY\"    \n",
       " \"DILAN\"     \n",
       " ⋮           \n",
       " \"NANCY\"     \n",
       " \"DARCI\"     \n",
       " \"THADDEUS\"  \n",
       " \"RACHENETTE\"\n",
       " \"MICHELLE\"  "
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "q1()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "35"
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "q2()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "35-element Array{Tuple{UTF8String,Int64},1}:\n",
       " (\"WATER MGMNT\",1848)    \n",
       " (\"POLICE\",13570)        \n",
       " (\"GENERAL SERVICES\",924)\n",
       " (\"CITY COUNCIL\",397)    \n",
       " (\"STREETS & SAN\",2090)  \n",
       " ⋮                       \n",
       " (\"BOARD OF ETHICS\",9)   \n",
       " (\"POLICE BOARD\",2)      \n",
       " (\"BUDGET & MGMT\",43)    \n",
       " (\"ADMIN HEARNG\",39)     \n",
       " (\"LICENSE APPL COMM\",1) "
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "q3()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3916"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "q4()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3-element Array{Int64,1}:\n",
       " 60\n",
       " 40\n",
       " 20"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "q5(X=5, Y=Nullable(4), Z=[3,2,1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "28-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}:\n",
       " (\"CHARLES\",\"A\",\"POLICE\",\"SERGEANT\",103590)                                     \n",
       " (\"CHARLES\",\"B\",\"POLICE\",\"SERGEANT\",106920)                                     \n",
       " (\"CHARLES\",\"B\",\"POLICE\",\"SERGEANT\",103590)                                     \n",
       " (\"CHARLES\",\"B\",\"FINANCE\",\"AUDITOR IV\",114492)                                  \n",
       " (\"CHARLES\",\"D\",\"POLICE\",\"SERGEANT\",106920)                                     \n",
       " ⋮                                                                              \n",
       " (\"CHARLES\",\"S\",\"FIRE\",\"FIRST DEPUTY FIRE COMMISSIONER\",197736)                 \n",
       " (\"CHARLES\",\"W\",\"STREETS & SAN\",\"GENERAL SUPT OF STREETS AND SANITATION\",110112)\n",
       " (\"CHARLES\",\"W\",\"FIRE\",\"LIEUTENANT-EMT\",106524)                                 \n",
       " (\"CHARLES\",\"W\",\"FIRE\",\"BATTALION CHIEF - EMT\",143682)                          \n",
       " (\"CHARLES\",\"W\",\"STREETS & SAN\",\"COMMISSIONER OF STREETS AND SANITATION\",157092)"
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "q6(NAME=\"CHARLES\", MIN_SALARY=100000)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Julia 0.5.0-dev",
   "language": "julia",
   "name": "julia-0.5"
  },
  "language_info": {
   "file_extension": ".jl",
   "mimetype": "application/julia",
   "name": "julia",
   "version": "0.5.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}