sql_casjobs_introduction.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Getting Started with SQL \n",
    "\n",
    "#### TAMÁS BUDAVÁRI – AUGUST 2016 \n",
    " \n",
    "Databases are easy: they consist of tables, which in turn have columns – just like the files with which you \n",
    "are probably used to working. The difference is that you don’t have to think about how to read and write \n",
    "the files but can focus on the questions you try to answer. The following code snippets and exercises will \n",
    "teach you the basics of expressing your questions in the Structure Query Language, or SQL for short. SQL \n",
    "is a standard language and most of these commands will work on any relational database but there are \n",
    "minor differences in dialects. \n",
    " \n",
    "The database you will be querying stores a collection of measurements of some (X,Y) quantities. There is \n",
    "a relation between them but the Y measurements are noisy. Our database is synthetic but carries several \n",
    "aspects of real measurements: separate instruments, multiple students and runs of measurements in \n",
    "different observational domains. The relevant tables are Data, Runs, Instruments and Users. The names \n",
    "should be suggestive of their contents.\n",
    "\n",
    "You have tried out some SQL queries using the online <a href=\"https://skyserver.sdss.org/CasJobs/\">CasJobs web application</a>. Here we use many of these same queries as examples for accessing a set of relational databases from within Jupyter notebooks running python inside a \n",
    "<a href=\"https://apps.sciserver.org/compute/\">SciServer/Compute</a> container.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## SciServer.CasJobs\n",
    "From  a python notebook in SciServer/Compute you can submit SQL queries to the same service that runs queries from the CasJobs webpage. The advantage is that you can directly access the result and visualize it or analyze it in any way you want.\n",
    "\n",
    "To do so you must import a library that is available on all the SciServer Compute images."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import SciServer.CasJobs as cj"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The First Queries \n",
    "To get all (X,Y) values from the table Data, you could use the following SQL:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "``` sql\n",
    "-- never run queries like this \n",
    "select X, Y \n",
    "from Data \n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "But don’t do it! First you should always think about what will happen. \n",
    "\n",
    "The table might have hundreds of millions of rows! Do you really want all that data dumped on you? Try \n",
    "the next set of commands to see their effects and understand how they work. Consider them as \n",
    "illustrations accompanying the lecture and ask questions if something is not clear! "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql=\"\"\"\n",
    "-- have a quick peek  \n",
    "select top 5 X, Y \n",
    "from Data \n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To execute this query you will use the SciServer.CasJobs library, which we aliased to 'cj' in the import statement.\n",
    "Its 'executeQuery' function submits the specified sql to the specified context<br/>\n",
    "As written here this query will be executed \"synchronously\" and return the result as a <a href=\"https://pandas.pydata.org/\">pandas</a> <a href=\"http://pandas.pydata.org/pandas-docs/stable/dsintro.html#dataframe\">DataFrame</a> that will be printed at the end of the cell."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>X</th>\n",
       "      <th>Y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.145097</td>\n",
       "      <td>-1.131867</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.200108</td>\n",
       "      <td>-0.337185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.405262</td>\n",
       "      <td>-0.246609</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.338955</td>\n",
       "      <td>-1.156640</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.089844</td>\n",
       "      <td>-1.062386</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          X         Y\n",
       "0  0.145097 -1.131867\n",
       "1  0.200108 -0.337185\n",
       "2  0.405262 -0.246609\n",
       "3  0.338955 -1.156640\n",
       "4  0.089844 -1.062386"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=cj.executeQuery(sql=sql,context=\"IntroSQL\")\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you only want to inspect the result you do not need to store it in a variable. And note that you do not need to be explicit about the variables. See <a href=\"http://www.sciserver.org/docs/sciscript-python/\">docs</a> for documentation on all the SciServer python modules. In particular <a href=\"http://www.sciserver.org/docs/sciscript-python/SciServer.html#module-SciServer.CasJobs\">SciServer.CasJobs</a> for the CasJobs module."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>X</th>\n",
       "      <th>Y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.001319</td>\n",
       "      <td>-1.121337</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.001944</td>\n",
       "      <td>-0.888723</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.002475</td>\n",
       "      <td>-0.770705</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          X         Y\n",
       "0  0.001319 -1.121337\n",
       "1  0.001944 -0.888723\n",
       "2  0.002475 -0.770705"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# sorting to peek at the extremes \n",
    "sql=\"\"\"\n",
    "select top 3 X, Y \n",
    "from Data \n",
    "order by X \n",
    "\"\"\"\n",
    "cj.executeQuery(sql,\"IntroSQL\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>x</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.546278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.434254</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.438969</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.501610</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.430480</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          x\n",
       "0  0.546278\n",
       "1  0.434254\n",
       "2  0.438969\n",
       "3  0.501610\n",
       "4  0.430480"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql=\"\"\"\n",
    "-- filtering with formulas and functions \n",
    "select top 5 x \n",
    "from Data \n",
    "where 2*y between -sin(x) and x \n",
    "order by RunID desc, y desc \n",
    "\"\"\"\n",
    "cj.executeQuery(sql,\"IntroSQL\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The last query showed you that you can comment your SQL code by using the following formats \n",
    " <pre>\n",
    "-- single-line comments go after -- \n",
    " \n",
    "/* multi-line comments are  \n",
    "   like this one \n",
    "*/ \n",
    "</pre> \n",
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# NOT TRUE:\n",
    "### SAVE YOUR WORK! \n",
    "The website you are using will not save your queries. If you would \n",
    "like to keep them for future reference, open a text editor to  cut & \n",
    "paste the relevant lines into a file that you can regularly save. \n",
    " \n",
    "IF you save your notebook in a volume in your private /Storage/&lt;username&gt; area, for example /persistent, your notebook will be backed up and available in other containers as well.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Aggregation\n",
    "Consider getting only the relevant information from the database and not everything. Run the following \n",
    "commands and see what the different constructs achieve: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>N</th>\n",
       "      <th>Column1</th>\n",
       "      <th>Column2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>546</td>\n",
       "      <td>546</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     N  Column1  Column2\n",
       "0  546      546       10"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql=\"\"\"\n",
    "-- counting \n",
    "select COUNT(ID) as N, COUNT(RunID), COUNT(distinct RunID) \n",
    "from Data \n",
    "\"\"\"\n",
    "cj.executeQuery(sql,\"IntroSQL\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Column1</th>\n",
       "      <th>Column2</th>\n",
       "      <th>Column3</th>\n",
       "      <th>Column4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>230</td>\n",
       "      <td>127.140058</td>\n",
       "      <td>1.509137</td>\n",
       "      <td>1.403175</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Column1     Column2   Column3   Column4\n",
       "0      230  127.140058  1.509137  1.403175"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql=\"\"\"\n",
    "-- aggregates in general \n",
    "select COUNT(id), SUM(x), AVG(y), STDEV(x-y) \n",
    "from Data \n",
    "where Y>0 \n",
    "\"\"\"\n",
    "cj.executeQuery(sql,\"IntroSQL\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>RunID</th>\n",
       "      <th>Column1</th>\n",
       "      <th>Column2</th>\n",
       "      <th>Column3</th>\n",
       "      <th>Column4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>0.505333</td>\n",
       "      <td>0.994198</td>\n",
       "      <td>-0.086970</td>\n",
       "      <td>2.975272</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>109</td>\n",
       "      <td>0.356064</td>\n",
       "      <td>0.879783</td>\n",
       "      <td>-1.207412</td>\n",
       "      <td>1.644311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>104</td>\n",
       "      <td>0.255726</td>\n",
       "      <td>0.789319</td>\n",
       "      <td>-2.374107</td>\n",
       "      <td>1.774286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>102</td>\n",
       "      <td>0.242926</td>\n",
       "      <td>0.773327</td>\n",
       "      <td>-0.935795</td>\n",
       "      <td>1.074530</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>105</td>\n",
       "      <td>0.202550</td>\n",
       "      <td>0.797702</td>\n",
       "      <td>-8.014773</td>\n",
       "      <td>6.073653</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>108</td>\n",
       "      <td>0.302871</td>\n",
       "      <td>0.599753</td>\n",
       "      <td>-0.700361</td>\n",
       "      <td>0.266618</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>106</td>\n",
       "      <td>0.202057</td>\n",
       "      <td>0.799781</td>\n",
       "      <td>-5.970850</td>\n",
       "      <td>4.230965</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>103</td>\n",
       "      <td>0.004561</td>\n",
       "      <td>0.881169</td>\n",
       "      <td>-7.480040</td>\n",
       "      <td>5.935146</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>100</td>\n",
       "      <td>0.017204</td>\n",
       "      <td>0.492729</td>\n",
       "      <td>-1.619121</td>\n",
       "      <td>0.487096</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>107</td>\n",
       "      <td>0.001319</td>\n",
       "      <td>0.097952</td>\n",
       "      <td>-1.163017</td>\n",
       "      <td>-0.693459</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   RunID   Column1   Column2   Column3   Column4\n",
       "0    101  0.505333  0.994198 -0.086970  2.975272\n",
       "1    109  0.356064  0.879783 -1.207412  1.644311\n",
       "2    104  0.255726  0.789319 -2.374107  1.774286\n",
       "3    102  0.242926  0.773327 -0.935795  1.074530\n",
       "4    105  0.202550  0.797702 -8.014773  6.073653\n",
       "5    108  0.302871  0.599753 -0.700361  0.266618\n",
       "6    106  0.202057  0.799781 -5.970850  4.230965\n",
       "7    103  0.004561  0.881169 -7.480040  5.935146\n",
       "8    100  0.017204  0.492729 -1.619121  0.487096\n",
       "9    107  0.001319  0.097952 -1.163017 -0.693459"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql=\"\"\"\n",
    "-- grouping data \n",
    "select RunID, MIN(x), MAX(x), MIN(y), MAX(Y) \n",
    "from Data \n",
    "group by RunID \n",
    "order by AVG(X) desc \n",
    "\"\"\"\n",
    "cj.executeQuery(sql,\"IntroSQL\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>RunID</th>\n",
       "      <th>Column1</th>\n",
       "      <th>Column2</th>\n",
       "      <th>Column3</th>\n",
       "      <th>Column4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [RunID, Column1, Column2, Column3, Column4]\n",
       "Index: []"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql=\"\"\"\n",
    "-- having: contraints on aggregates \n",
    "select RunID, MIN(x), MAX(x), MIN(y), MAX(Y) \n",
    "from Data \n",
    "where X>0.2         -- filtering on the input \n",
    "group by RunID \n",
    "having MAX(Y) < 0   -- filtering on aggregate \n",
    "order by AVG(X) desc \n",
    "\"\"\"\n",
    "cj.executeQuery(sql,\"AstroinformIntroSQLatics2018\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>RunID</th>\n",
       "      <th>Column1</th>\n",
       "      <th>Column2</th>\n",
       "      <th>Column3</th>\n",
       "      <th>Column4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>103</td>\n",
       "      <td>0.207009</td>\n",
       "      <td>0.881169</td>\n",
       "      <td>-5.723222</td>\n",
       "      <td>5.935146</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>105</td>\n",
       "      <td>0.202550</td>\n",
       "      <td>0.797702</td>\n",
       "      <td>-8.014773</td>\n",
       "      <td>6.073653</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>108</td>\n",
       "      <td>0.302871</td>\n",
       "      <td>0.599753</td>\n",
       "      <td>-0.700361</td>\n",
       "      <td>0.266618</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>106</td>\n",
       "      <td>0.202057</td>\n",
       "      <td>0.799781</td>\n",
       "      <td>-5.970850</td>\n",
       "      <td>4.230965</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   RunID   Column1   Column2   Column3   Column4\n",
       "0    103  0.207009  0.881169 -5.723222  5.935146\n",
       "1    105  0.202550  0.797702 -8.014773  6.073653\n",
       "2    108  0.302871  0.599753 -0.700361  0.266618\n",
       "3    106  0.202057  0.799781 -5.970850  4.230965"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql=\"\"\"\n",
    "-- or  \n",
    "select RunID, MIN(x), MAX(x), MIN(y), MAX(Y) \n",
    "from Data \n",
    "where X>0.2 \n",
    "group by RunID \n",
    "having COUNT(*) > 30 \n",
    "order by AVG(X) desc \n",
    "\"\"\"\n",
    "cj.executeQuery(sql,\"IntroSQL\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>RunID</th>\n",
       "      <th>Column1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>103</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>105</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>106</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>108</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>107</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>102</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>109</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>100</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>101</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>104</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   RunID  Column1\n",
       "0    103       99\n",
       "1    105       99\n",
       "2    106       99\n",
       "3    108       73\n",
       "4    107       49\n",
       "5    102       30\n",
       "6    109       27\n",
       "7    100       25\n",
       "8    101       25\n",
       "9    104       20"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql=\"\"\"\n",
    "-- number of measurements in each run \n",
    "select RunID, COUNT(*) \n",
    "from Data \n",
    "group by RunID \n",
    "order by 2 desc \n",
    "\"\"\"\n",
    "cj.executeQuery(sql,\"IntroSQL\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>X</th>\n",
       "      <th>Column1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.145097</td>\n",
       "      <td>0.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.200108</td>\n",
       "      <td>0.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.405262</td>\n",
       "      <td>0.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.338955</td>\n",
       "      <td>0.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.089844</td>\n",
       "      <td>0.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0.131127</td>\n",
       "      <td>0.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0.465117</td>\n",
       "      <td>0.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0.049429</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0.391001</td>\n",
       "      <td>0.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0.357276</td>\n",
       "      <td>0.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>0.129908</td>\n",
       "      <td>0.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>0.348302</td>\n",
       "      <td>0.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>0.330393</td>\n",
       "      <td>0.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>0.178024</td>\n",
       "      <td>0.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>0.404610</td>\n",
       "      <td>0.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>0.061659</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>0.492729</td>\n",
       "      <td>0.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>0.017204</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>0.065357</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>0.355085</td>\n",
       "      <td>0.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>0.369850</td>\n",
       "      <td>0.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>0.364192</td>\n",
       "      <td>0.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>0.068344</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>0.396223</td>\n",
       "      <td>0.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>0.104986</td>\n",
       "      <td>0.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>0.981793</td>\n",
       "      <td>0.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>0.613945</td>\n",
       "      <td>0.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>0.662763</td>\n",
       "      <td>0.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>0.767558</td>\n",
       "      <td>0.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>0.994198</td>\n",
       "      <td>0.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>0.435793</td>\n",
       "      <td>0.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>0.511613</td>\n",
       "      <td>0.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>0.738097</td>\n",
       "      <td>0.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>0.305846</td>\n",
       "      <td>0.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>0.724111</td>\n",
       "      <td>0.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>0.593763</td>\n",
       "      <td>0.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>0.371776</td>\n",
       "      <td>0.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>0.462956</td>\n",
       "      <td>0.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>0.431236</td>\n",
       "      <td>0.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>0.242926</td>\n",
       "      <td>0.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>0.839875</td>\n",
       "      <td>0.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>0.783815</td>\n",
       "      <td>0.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>0.601005</td>\n",
       "      <td>0.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>0.780251</td>\n",
       "      <td>0.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>0.652454</td>\n",
       "      <td>0.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>0.384627</td>\n",
       "      <td>0.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>0.186540</td>\n",
       "      <td>0.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>0.871552</td>\n",
       "      <td>0.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>0.740827</td>\n",
       "      <td>0.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>0.437515</td>\n",
       "      <td>0.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>0.472008</td>\n",
       "      <td>0.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>0.716693</td>\n",
       "      <td>0.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>0.157553</td>\n",
       "      <td>0.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>0.309754</td>\n",
       "      <td>0.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>0.043183</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>0.386268</td>\n",
       "      <td>0.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>0.737191</td>\n",
       "      <td>0.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>0.585478</td>\n",
       "      <td>0.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>0.526038</td>\n",
       "      <td>0.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>0.193036</td>\n",
       "      <td>0.04</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           X  Column1\n",
       "0   0.145097     0.02\n",
       "1   0.200108     0.04\n",
       "2   0.405262     0.16\n",
       "3   0.338955     0.11\n",
       "4   0.089844     0.01\n",
       "5   0.131127     0.02\n",
       "6   0.465117     0.22\n",
       "7   0.049429     0.00\n",
       "8   0.391001     0.15\n",
       "9   0.357276     0.13\n",
       "10  0.129908     0.02\n",
       "11  0.348302     0.12\n",
       "12  0.330393     0.11\n",
       "13  0.178024     0.03\n",
       "14  0.404610     0.16\n",
       "15  0.061659     0.00\n",
       "16  0.492729     0.24\n",
       "17  0.017204     0.00\n",
       "18  0.065357     0.00\n",
       "19  0.355085     0.13\n",
       "20  0.369850     0.14\n",
       "21  0.364192     0.13\n",
       "22  0.068344     0.00\n",
       "23  0.396223     0.16\n",
       "24  0.104986     0.01\n",
       "25  0.981793     0.96\n",
       "26  0.613945     0.38\n",
       "27  0.662763     0.44\n",
       "28  0.767558     0.59\n",
       "29  0.994198     0.99\n",
       "..       ...      ...\n",
       "70  0.435793     0.19\n",
       "71  0.511613     0.26\n",
       "72  0.738097     0.54\n",
       "73  0.305846     0.09\n",
       "74  0.724111     0.52\n",
       "75  0.593763     0.35\n",
       "76  0.371776     0.14\n",
       "77  0.462956     0.21\n",
       "78  0.431236     0.19\n",
       "79  0.242926     0.06\n",
       "80  0.839875     0.71\n",
       "81  0.783815     0.61\n",
       "82  0.601005     0.36\n",
       "83  0.780251     0.61\n",
       "84  0.652454     0.43\n",
       "85  0.384627     0.15\n",
       "86  0.186540     0.03\n",
       "87  0.871552     0.76\n",
       "88  0.740827     0.55\n",
       "89  0.437515     0.19\n",
       "90  0.472008     0.22\n",
       "91  0.716693     0.51\n",
       "92  0.157553     0.02\n",
       "93  0.309754     0.10\n",
       "94  0.043183     0.00\n",
       "95  0.386268     0.15\n",
       "96  0.737191     0.54\n",
       "97  0.585478     0.34\n",
       "98  0.526038     0.28\n",
       "99  0.193036     0.04\n",
       "\n",
       "[100 rows x 2 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql=\"\"\"\n",
    "-- rounding is easy \n",
    "select top 100 X, ROUND(X*X,2) from Data \n",
    "\"\"\"\n",
    "cj.executeQuery(sql,\"IntroSQL\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>X</th>\n",
       "      <th>N</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.00</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.01</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.02</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.03</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.04</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0.05</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0.06</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0.07</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0.08</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0.09</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>0.10</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>0.11</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>0.12</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>0.13</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>0.15</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>0.16</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>0.17</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>0.18</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>0.19</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>0.20</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>0.21</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>0.22</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>0.23</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>0.24</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>0.25</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>0.26</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>0.27</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>0.28</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>0.29</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>0.30</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>0.65</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>0.66</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>0.67</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>0.68</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>0.69</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>0.70</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>0.71</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>0.72</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>0.73</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>0.74</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>0.75</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>0.76</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>0.77</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>0.78</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>0.79</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>0.80</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>0.81</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>0.83</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>0.84</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>0.85</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>0.86</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>0.87</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>0.88</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>0.90</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>0.91</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>0.92</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>0.93</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>0.97</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>0.98</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>0.99</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>94 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       X   N\n",
       "0   0.00   4\n",
       "1   0.01   8\n",
       "2   0.02   6\n",
       "3   0.03   6\n",
       "4   0.04   6\n",
       "5   0.05   8\n",
       "6   0.06   8\n",
       "7   0.07   5\n",
       "8   0.08   5\n",
       "9   0.09  11\n",
       "10  0.10   5\n",
       "11  0.11   3\n",
       "12  0.12   1\n",
       "13  0.13   2\n",
       "14  0.15   2\n",
       "15  0.16   1\n",
       "16  0.17   1\n",
       "17  0.18   1\n",
       "18  0.19   3\n",
       "19  0.20   4\n",
       "20  0.21  12\n",
       "21  0.22   4\n",
       "22  0.23   3\n",
       "23  0.24   5\n",
       "24  0.25   3\n",
       "25  0.26   5\n",
       "26  0.27   5\n",
       "27  0.28   8\n",
       "28  0.29   7\n",
       "29  0.30  10\n",
       "..   ...  ..\n",
       "64  0.65   6\n",
       "65  0.66   8\n",
       "66  0.67   2\n",
       "67  0.68   6\n",
       "68  0.69   7\n",
       "69  0.70   6\n",
       "70  0.71   5\n",
       "71  0.72   8\n",
       "72  0.73   6\n",
       "73  0.74   4\n",
       "74  0.75   4\n",
       "75  0.76   3\n",
       "76  0.77  11\n",
       "77  0.78   5\n",
       "78  0.79   4\n",
       "79  0.80   5\n",
       "80  0.81   1\n",
       "81  0.83   2\n",
       "82  0.84   2\n",
       "83  0.85   1\n",
       "84  0.86   5\n",
       "85  0.87   3\n",
       "86  0.88   3\n",
       "87  0.90   1\n",
       "88  0.91   1\n",
       "89  0.92   1\n",
       "90  0.93   1\n",
       "91  0.97   1\n",
       "92  0.98   2\n",
       "93  0.99   1\n",
       "\n",
       "[94 rows x 2 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql=\"\"\"\n",
    "-- building a histogram \n",
    "select ROUND(x,2) as X, COUNT(*) as N \n",
    "from Data \n",
    "group by ROUND(x,2) \n",
    "order by 1 \n",
    "\"\"\"\n",
    "cj.executeQuery(sql,\"IntroSQL\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can use the same <pre> SELECT ... INTO ...</pre> pattern to save results in your MyDB. Make sure though that a table with this name does not already exist!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Rows Affected</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>61</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Rows Affected\n",
       "0             61"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql=\"\"\"\n",
    "-- custom bin size using a variable and save the results \n",
    "declare @bin float = 0.016 \n",
    "select ROUND(x/@bin,0)*@bin as X, COUNT(*) as Cts \n",
    "into AHistogram  -- name of new table \n",
    "from Data \n",
    "group by ROUND(x/@bin,0)*@bin \n",
    "order by ROUND(x/@bin,0)*@bin \n",
    "\"\"\"\n",
    "cj.executeQuery(sql,\"IntroSQL\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Check that the table was indeed created in your MyDB in the CasJobs UI at https://skyserver.sdss.org/CasJobs/MyDB.aspx"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Asking Questions using SQL \n",
    "\n",
    "You’ll see that every question you have about the data will nicely translate to commands. For example, \n",
    "let’s consider the following question: Who ran the first measurement? The following 3 select statements \n",
    "will get you an answer. Note, you can run all three queries using a single execution. When more than 1 result is returned from the database, the result is a list of data frames: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql=\"\"\"\n",
    "select top 1 RunID from Data order by ID -- 100 \n",
    "select UserID from Runs where RunID=100 -- 12 \n",
    "select * from Users where UserID=12 \n",
    "\"\"\"\n",
    "dfs=cj.executeQuery(sql,\"IntroSQL\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Loop over the elements in the result, each of which is a DataFrame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0 :\n",
      "   RunID\n",
      "0    100\n",
      "1 :\n",
      "   UserID\n",
      "0      12\n",
      "2 :\n",
      "   UserID        Name  AdvisorID\n",
      "0      12  Hugo First         10\n"
     ]
    }
   ],
   "source": [
    "for idx, df in enumerate(dfs):\n",
    "    print(idx,\":\")\n",
    "    print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What did we really mean by “first” measurement? Any other definition to use? \n",
    " \n",
    "Nested queries can combine multiple searches into one request. Run and analyze the following queries \n",
    "and their results: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql=\"\"\"\n",
    "-- nested queries \n",
    "select UserID \n",
    "from Runs \n",
    "where RunID = (select top 1 RunID from Data order by ID) \n",
    " \n",
    "-- doubly so \n",
    "select * from Users \n",
    "where UserID = ( \n",
    "  select UserID from Runs \n",
    "  where RunID = (select top 1 RunID from Data order by ID) \n",
    ") \n",
    " \n",
    "-- whole set of runs using the 'in' keyword \n",
    "select UserID \n",
    "from Runs \n",
    "where RunID in (select top 1 RunID  \n",
    "                from Data order by ID) \n",
    "\"\"\"\n",
    "dfs=cj.executeQuery(sql,\"IntroSQL\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0 :\n",
      "   UserID\n",
      "0      12\n",
      "1 :\n",
      "   UserID        Name  AdvisorID\n",
      "0      12  Hugo First         10\n",
      "2 :\n",
      "   UserID\n",
      "0      12\n"
     ]
    }
   ],
   "source": [
    "for idx, df in enumerate(dfs):\n",
    "    print(idx,\":\")\n",
    "    print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Combining tables is where relational database engines really shine. The terminology is “joining tables”. \n",
    "Here  are  different  implementations  of  similar  questions.  Make  sure  you  understand  these  queries \n",
    "because they will be important: \n",
    " "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>RunID</th>\n",
       "      <th>Column1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Hugo First</td>\n",
       "      <td>100</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Hugo First</td>\n",
       "      <td>101</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Hammond Eggs</td>\n",
       "      <td>102</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Gil T. Azell</td>\n",
       "      <td>103</td>\n",
       "      <td>0.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Holly Wood</td>\n",
       "      <td>104</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Gil T. Azell</td>\n",
       "      <td>105</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Gil T. Azell</td>\n",
       "      <td>106</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Holly Wood</td>\n",
       "      <td>107</td>\n",
       "      <td>0.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Holly Wood</td>\n",
       "      <td>108</td>\n",
       "      <td>0.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Levy Tate</td>\n",
       "      <td>109</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Name  RunID  Column1\n",
       "0    Hugo First    100      0.5\n",
       "1    Hugo First    101      0.5\n",
       "2  Hammond Eggs    102      0.6\n",
       "3  Gil T. Azell    103      0.9\n",
       "4    Holly Wood    104      0.6\n",
       "5  Gil T. Azell    105      0.6\n",
       "6  Gil T. Azell    106      0.6\n",
       "7    Holly Wood    107      0.1\n",
       "8    Holly Wood    108      0.3\n",
       "9     Levy Tate    109      0.6"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql=\"\"\"\n",
    "-- inner join (old style)  \n",
    "select u.Name, r.RunID, r.Xmax - r.Xmin \n",
    "from Runs r, Users u -- aliases are convenient \n",
    "where r.UserID=u.UserID  \n",
    "-- risk of forgetting a constraint when many tables \n",
    "\"\"\"\n",
    "cj.executeQuery(sql,\"IntroSQL\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>RunID</th>\n",
       "      <th>Column1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Hugo First</td>\n",
       "      <td>100</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Hugo First</td>\n",
       "      <td>101</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Hammond Eggs</td>\n",
       "      <td>102</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Gil T. Azell</td>\n",
       "      <td>103</td>\n",
       "      <td>0.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Holly Wood</td>\n",
       "      <td>104</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Gil T. Azell</td>\n",
       "      <td>105</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Gil T. Azell</td>\n",
       "      <td>106</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Holly Wood</td>\n",
       "      <td>107</td>\n",
       "      <td>0.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Holly Wood</td>\n",
       "      <td>108</td>\n",
       "      <td>0.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Levy Tate</td>\n",
       "      <td>109</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Name  RunID  Column1\n",
       "0    Hugo First    100      0.5\n",
       "1    Hugo First    101      0.5\n",
       "2  Hammond Eggs    102      0.6\n",
       "3  Gil T. Azell    103      0.9\n",
       "4    Holly Wood    104      0.6\n",
       "5  Gil T. Azell    105      0.6\n",
       "6  Gil T. Azell    106      0.6\n",
       "7    Holly Wood    107      0.1\n",
       "8    Holly Wood    108      0.3\n",
       "9     Levy Tate    109      0.6"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql=\"\"\"\n",
    "-- inner join (preferred) \n",
    "select u.Name, r.RunID, r.Xmax - r.Xmin \n",
    "from Runs r \n",
    "     join Users u on u.UserID=r.UserID \n",
    "\"\"\"\n",
    "cj.executeQuery(sql,\"IntroSQL\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>RunID</th>\n",
       "      <th>Column1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Hugo First</td>\n",
       "      <td>100</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Hugo First</td>\n",
       "      <td>101</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Hammond Eggs</td>\n",
       "      <td>102</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Gil T. Azell</td>\n",
       "      <td>103</td>\n",
       "      <td>0.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Holly Wood</td>\n",
       "      <td>104</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Gil T. Azell</td>\n",
       "      <td>105</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Gil T. Azell</td>\n",
       "      <td>106</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Holly Wood</td>\n",
       "      <td>107</td>\n",
       "      <td>0.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Holly Wood</td>\n",
       "      <td>108</td>\n",
       "      <td>0.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Levy Tate</td>\n",
       "      <td>109</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Name  RunID  Column1\n",
       "0    Hugo First    100      0.5\n",
       "1    Hugo First    101      0.5\n",
       "2  Hammond Eggs    102      0.6\n",
       "3  Gil T. Azell    103      0.9\n",
       "4    Holly Wood    104      0.6\n",
       "5  Gil T. Azell    105      0.6\n",
       "6  Gil T. Azell    106      0.6\n",
       "7    Holly Wood    107      0.1\n",
       "8    Holly Wood    108      0.3\n",
       "9     Levy Tate    109      0.6"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql=\"\"\"-- or explicitly  \n",
    "select u.Name, r.RunID, r.Xmax - r.Xmin \n",
    "from Runs r \n",
    "     inner join Users u on u.UserID=r.UserID \n",
    "\"\"\"\n",
    "cj.executeQuery(sql,\"IntroSQL\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Gil T. Azell</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Hammond Eggs</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Holly Wood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Hugo First</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Levy Tate</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Name\n",
       "0  Gil T. Azell\n",
       "1  Hammond Eggs\n",
       "2    Holly Wood\n",
       "3    Hugo First\n",
       "4     Levy Tate"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql=\"\"\" \n",
    "-- list of users with measurements \n",
    "select distinct u.Name \n",
    "from Runs r \n",
    "     join Users u on u.UserID=r.UserID      \n",
    "\"\"\"\n",
    "cj.executeQuery(sql,\"IntroSQL\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A different kind of join is used less frequently to look at all combinations of rows in the specified tables. \n",
    "Compare the following queries to the previous ones and run them to see the differences in the results: \n",
    " "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>RunID</th>\n",
       "      <th>Column1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Andy Structible</td>\n",
       "      <td>100</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Andy Structible</td>\n",
       "      <td>101</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Andy Structible</td>\n",
       "      <td>102</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Andy Structible</td>\n",
       "      <td>103</td>\n",
       "      <td>0.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Andy Structible</td>\n",
       "      <td>104</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Andy Structible</td>\n",
       "      <td>105</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Andy Structible</td>\n",
       "      <td>106</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Andy Structible</td>\n",
       "      <td>107</td>\n",
       "      <td>0.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Andy Structible</td>\n",
       "      <td>108</td>\n",
       "      <td>0.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Andy Structible</td>\n",
       "      <td>109</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Jack Pot</td>\n",
       "      <td>100</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Jack Pot</td>\n",
       "      <td>101</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Jack Pot</td>\n",
       "      <td>102</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Jack Pot</td>\n",
       "      <td>103</td>\n",
       "      <td>0.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Jack Pot</td>\n",
       "      <td>104</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Jack Pot</td>\n",
       "      <td>105</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Jack Pot</td>\n",
       "      <td>106</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Jack Pot</td>\n",
       "      <td>107</td>\n",
       "      <td>0.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Jack Pot</td>\n",
       "      <td>108</td>\n",
       "      <td>0.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Jack Pot</td>\n",
       "      <td>109</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Hugo First</td>\n",
       "      <td>100</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Hugo First</td>\n",
       "      <td>101</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Hugo First</td>\n",
       "      <td>102</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Hugo First</td>\n",
       "      <td>103</td>\n",
       "      <td>0.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Hugo First</td>\n",
       "      <td>104</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Hugo First</td>\n",
       "      <td>105</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Hugo First</td>\n",
       "      <td>106</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Hugo First</td>\n",
       "      <td>107</td>\n",
       "      <td>0.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Hugo First</td>\n",
       "      <td>108</td>\n",
       "      <td>0.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Hugo First</td>\n",
       "      <td>109</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Gil T. Azell</td>\n",
       "      <td>100</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>Gil T. Azell</td>\n",
       "      <td>101</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>Gil T. Azell</td>\n",
       "      <td>102</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>Gil T. Azell</td>\n",
       "      <td>103</td>\n",
       "      <td>0.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>Gil T. Azell</td>\n",
       "      <td>104</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>Gil T. Azell</td>\n",
       "      <td>105</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>Gil T. Azell</td>\n",
       "      <td>106</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>Gil T. Azell</td>\n",
       "      <td>107</td>\n",
       "      <td>0.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>Gil T. Azell</td>\n",
       "      <td>108</td>\n",
       "      <td>0.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>Gil T. Azell</td>\n",
       "      <td>109</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>Holly Wood</td>\n",
       "      <td>100</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>Holly Wood</td>\n",
       "      <td>101</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>Holly Wood</td>\n",
       "      <td>102</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>Holly Wood</td>\n",
       "      <td>103</td>\n",
       "      <td>0.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>Holly Wood</td>\n",
       "      <td>104</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>Holly Wood</td>\n",
       "      <td>105</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>Holly Wood</td>\n",
       "      <td>106</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>Holly Wood</td>\n",
       "      <td>107</td>\n",
       "      <td>0.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>Holly Wood</td>\n",
       "      <td>108</td>\n",
       "      <td>0.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>Holly Wood</td>\n",
       "      <td>109</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>Levy Tate</td>\n",
       "      <td>100</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>Levy Tate</td>\n",
       "      <td>101</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>Levy Tate</td>\n",
       "      <td>102</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>Levy Tate</td>\n",
       "      <td>103</td>\n",
       "      <td>0.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>Levy Tate</td>\n",
       "      <td>104</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>Levy Tate</td>\n",
       "      <td>105</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>Levy Tate</td>\n",
       "      <td>106</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>Levy Tate</td>\n",
       "      <td>107</td>\n",
       "      <td>0.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>Levy Tate</td>\n",
       "      <td>108</td>\n",
       "      <td>0.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>Levy Tate</td>\n",
       "      <td>109</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>70 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               Name  RunID  Column1\n",
       "0   Andy Structible    100      0.5\n",
       "1   Andy Structible    101      0.5\n",
       "2   Andy Structible    102      0.6\n",
       "3   Andy Structible    103      0.9\n",
       "4   Andy Structible    104      0.6\n",
       "5   Andy Structible    105      0.6\n",
       "6   Andy Structible    106      0.6\n",
       "7   Andy Structible    107      0.1\n",
       "8   Andy Structible    108      0.3\n",
       "9   Andy Structible    109      0.6\n",
       "10         Jack Pot    100      0.5\n",
       "11         Jack Pot    101      0.5\n",
       "12         Jack Pot    102      0.6\n",
       "13         Jack Pot    103      0.9\n",
       "14         Jack Pot    104      0.6\n",
       "15         Jack Pot    105      0.6\n",
       "16         Jack Pot    106      0.6\n",
       "17         Jack Pot    107      0.1\n",
       "18         Jack Pot    108      0.3\n",
       "19         Jack Pot    109      0.6\n",
       "20       Hugo First    100      0.5\n",
       "21       Hugo First    101      0.5\n",
       "22       Hugo First    102      0.6\n",
       "23       Hugo First    103      0.9\n",
       "24       Hugo First    104      0.6\n",
       "25       Hugo First    105      0.6\n",
       "26       Hugo First    106      0.6\n",
       "27       Hugo First    107      0.1\n",
       "28       Hugo First    108      0.3\n",
       "29       Hugo First    109      0.6\n",
       "..              ...    ...      ...\n",
       "40     Gil T. Azell    100      0.5\n",
       "41     Gil T. Azell    101      0.5\n",
       "42     Gil T. Azell    102      0.6\n",
       "43     Gil T. Azell    103      0.9\n",
       "44     Gil T. Azell    104      0.6\n",
       "45     Gil T. Azell    105      0.6\n",
       "46     Gil T. Azell    106      0.6\n",
       "47     Gil T. Azell    107      0.1\n",
       "48     Gil T. Azell    108      0.3\n",
       "49     Gil T. Azell    109      0.6\n",
       "50       Holly Wood    100      0.5\n",
       "51       Holly Wood    101      0.5\n",
       "52       Holly Wood    102      0.6\n",
       "53       Holly Wood    103      0.9\n",
       "54       Holly Wood    104      0.6\n",
       "55       Holly Wood    105      0.6\n",
       "56       Holly Wood    106      0.6\n",
       "57       Holly Wood    107      0.1\n",
       "58       Holly Wood    108      0.3\n",
       "59       Holly Wood    109      0.6\n",
       "60        Levy Tate    100      0.5\n",
       "61        Levy Tate    101      0.5\n",
       "62        Levy Tate    102      0.6\n",
       "63        Levy Tate    103      0.9\n",
       "64        Levy Tate    104      0.6\n",
       "65        Levy Tate    105      0.6\n",
       "66        Levy Tate    106      0.6\n",
       "67        Levy Tate    107      0.1\n",
       "68        Levy Tate    108      0.3\n",
       "69        Levy Tate    109      0.6\n",
       "\n",
       "[70 rows x 3 columns]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql=\"\"\"\n",
    "-- cross join (old style)  \n",
    "select u.Name, r.RunID, r.Xmax - r.Xmin \n",
    "from Runs r, Users u \n",
    "-- same as old-style inner join w/o contraint \n",
    "\"\"\"\n",
    "cj.executeQuery(sql,\"IntroSQL\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>RunID</th>\n",
       "      <th>Column1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Andy Structible</td>\n",
       "      <td>100</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Andy Structible</td>\n",
       "      <td>101</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Andy Structible</td>\n",
       "      <td>102</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Andy Structible</td>\n",
       "      <td>103</td>\n",
       "      <td>0.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Andy Structible</td>\n",
       "      <td>104</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Andy Structible</td>\n",
       "      <td>105</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Andy Structible</td>\n",
       "      <td>106</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Andy Structible</td>\n",
       "      <td>107</td>\n",
       "      <td>0.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Andy Structible</td>\n",
       "      <td>108</td>\n",
       "      <td>0.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Andy Structible</td>\n",
       "      <td>109</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Jack Pot</td>\n",
       "      <td>100</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Jack Pot</td>\n",
       "      <td>101</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Jack Pot</td>\n",
       "      <td>102</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Jack Pot</td>\n",
       "      <td>103</td>\n",
       "      <td>0.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Jack Pot</td>\n",
       "      <td>104</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Jack Pot</td>\n",
       "      <td>105</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Jack Pot</td>\n",
       "      <td>106</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Jack Pot</td>\n",
       "      <td>107</td>\n",
       "      <td>0.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Jack Pot</td>\n",
       "      <td>108</td>\n",
       "      <td>0.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Jack Pot</td>\n",
       "      <td>109</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Hugo First</td>\n",
       "      <td>100</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Hugo First</td>\n",
       "      <td>101</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Hugo First</td>\n",
       "      <td>102</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Hugo First</td>\n",
       "      <td>103</td>\n",
       "      <td>0.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Hugo First</td>\n",
       "      <td>104</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Hugo First</td>\n",
       "      <td>105</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Hugo First</td>\n",
       "      <td>106</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Hugo First</td>\n",
       "      <td>107</td>\n",
       "      <td>0.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Hugo First</td>\n",
       "      <td>108</td>\n",
       "      <td>0.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Hugo First</td>\n",
       "      <td>109</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Gil T. Azell</td>\n",
       "      <td>100</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>Gil T. Azell</td>\n",
       "      <td>101</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>Gil T. Azell</td>\n",
       "      <td>102</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>Gil T. Azell</td>\n",
       "      <td>103</td>\n",
       "      <td>0.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>Gil T. Azell</td>\n",
       "      <td>104</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>Gil T. Azell</td>\n",
       "      <td>105</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>Gil T. Azell</td>\n",
       "      <td>106</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>Gil T. Azell</td>\n",
       "      <td>107</td>\n",
       "      <td>0.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>Gil T. Azell</td>\n",
       "      <td>108</td>\n",
       "      <td>0.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>Gil T. Azell</td>\n",
       "      <td>109</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>Holly Wood</td>\n",
       "      <td>100</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>Holly Wood</td>\n",
       "      <td>101</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>Holly Wood</td>\n",
       "      <td>102</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>Holly Wood</td>\n",
       "      <td>103</td>\n",
       "      <td>0.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>Holly Wood</td>\n",
       "      <td>104</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>Holly Wood</td>\n",
       "      <td>105</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>Holly Wood</td>\n",
       "      <td>106</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>Holly Wood</td>\n",
       "      <td>107</td>\n",
       "      <td>0.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>Holly Wood</td>\n",
       "      <td>108</td>\n",
       "      <td>0.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>Holly Wood</td>\n",
       "      <td>109</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>Levy Tate</td>\n",
       "      <td>100</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>Levy Tate</td>\n",
       "      <td>101</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>Levy Tate</td>\n",
       "      <td>102</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>Levy Tate</td>\n",
       "      <td>103</td>\n",
       "      <td>0.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>Levy Tate</td>\n",
       "      <td>104</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>Levy Tate</td>\n",
       "      <td>105</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>Levy Tate</td>\n",
       "      <td>106</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>Levy Tate</td>\n",
       "      <td>107</td>\n",
       "      <td>0.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>Levy Tate</td>\n",
       "      <td>108</td>\n",
       "      <td>0.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>Levy Tate</td>\n",
       "      <td>109</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>70 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               Name  RunID  Column1\n",
       "0   Andy Structible    100      0.5\n",
       "1   Andy Structible    101      0.5\n",
       "2   Andy Structible    102      0.6\n",
       "3   Andy Structible    103      0.9\n",
       "4   Andy Structible    104      0.6\n",
       "5   Andy Structible    105      0.6\n",
       "6   Andy Structible    106      0.6\n",
       "7   Andy Structible    107      0.1\n",
       "8   Andy Structible    108      0.3\n",
       "9   Andy Structible    109      0.6\n",
       "10         Jack Pot    100      0.5\n",
       "11         Jack Pot    101      0.5\n",
       "12         Jack Pot    102      0.6\n",
       "13         Jack Pot    103      0.9\n",
       "14         Jack Pot    104      0.6\n",
       "15         Jack Pot    105      0.6\n",
       "16         Jack Pot    106      0.6\n",
       "17         Jack Pot    107      0.1\n",
       "18         Jack Pot    108      0.3\n",
       "19         Jack Pot    109      0.6\n",
       "20       Hugo First    100      0.5\n",
       "21       Hugo First    101      0.5\n",
       "22       Hugo First    102      0.6\n",
       "23       Hugo First    103      0.9\n",
       "24       Hugo First    104      0.6\n",
       "25       Hugo First    105      0.6\n",
       "26       Hugo First    106      0.6\n",
       "27       Hugo First    107      0.1\n",
       "28       Hugo First    108      0.3\n",
       "29       Hugo First    109      0.6\n",
       "..              ...    ...      ...\n",
       "40     Gil T. Azell    100      0.5\n",
       "41     Gil T. Azell    101      0.5\n",
       "42     Gil T. Azell    102      0.6\n",
       "43     Gil T. Azell    103      0.9\n",
       "44     Gil T. Azell    104      0.6\n",
       "45     Gil T. Azell    105      0.6\n",
       "46     Gil T. Azell    106      0.6\n",
       "47     Gil T. Azell    107      0.1\n",
       "48     Gil T. Azell    108      0.3\n",
       "49     Gil T. Azell    109      0.6\n",
       "50       Holly Wood    100      0.5\n",
       "51       Holly Wood    101      0.5\n",
       "52       Holly Wood    102      0.6\n",
       "53       Holly Wood    103      0.9\n",
       "54       Holly Wood    104      0.6\n",
       "55       Holly Wood    105      0.6\n",
       "56       Holly Wood    106      0.6\n",
       "57       Holly Wood    107      0.1\n",
       "58       Holly Wood    108      0.3\n",
       "59       Holly Wood    109      0.6\n",
       "60        Levy Tate    100      0.5\n",
       "61        Levy Tate    101      0.5\n",
       "62        Levy Tate    102      0.6\n",
       "63        Levy Tate    103      0.9\n",
       "64        Levy Tate    104      0.6\n",
       "65        Levy Tate    105      0.6\n",
       "66        Levy Tate    106      0.6\n",
       "67        Levy Tate    107      0.1\n",
       "68        Levy Tate    108      0.3\n",
       "69        Levy Tate    109      0.6\n",
       "\n",
       "[70 rows x 3 columns]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql=\"\"\" \n",
    "-- cross join: explicitly \n",
    "select u.Name, r.RunID, r.Xmax - r.Xmin \n",
    "from Runs r cross join Users u \n",
    "\"\"\"\n",
    "cj.executeQuery(sql,\"IntroSQL\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Column1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Column1\n",
       "0        7"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql=\"\"\" \n",
    "-- compare the size of the result set with these \n",
    "select COUNT(*) from users \n",
    "\"\"\"\n",
    "cj.executeQuery(sql,\"IntroSQL\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Column1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Column1\n",
       "0       10"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql=\"\"\"\n",
    "select COUNT(*) from runs \n",
    "-- all combinations \n",
    "\"\"\"\n",
    "cj.executeQuery(sql,\"IntroSQL\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.7 (py37)",
   "language": "python",
   "name": "py37"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}