02-2-students_hobbies_exercises.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Working with Two Tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: isdb16@postgres'"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%load_ext sql\n",
    "\n",
    "# Windows users need to specify password\n",
    "%sql postgres://isdb16@localhost/postgres"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "Done.\n",
      "3 rows affected.\n",
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>sid</th>\n",
       "        <th>name</th>\n",
       "        <th>gpa</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>s1</td>\n",
       "        <td>Mary</td>\n",
       "        <td>4.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>s2</td>\n",
       "        <td>Jack</td>\n",
       "        <td>3.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>s3</td>\n",
       "        <td>Pat</td>\n",
       "        <td>2.00</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('s1', 'Mary', Decimal('4.00')),\n",
       " ('s2', 'Jack', Decimal('3.00')),\n",
       " ('s3', 'Pat', Decimal('2.00'))]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "\n",
    "DROP TABLE if EXISTS Students CASCADE ;\n",
    "\n",
    "CREATE TABLE Students (\n",
    "    PRIMARY KEY(sid),\n",
    "    sid   text,\n",
    "    name  text,\n",
    "    gpa   NUMERIC(3,2)\n",
    ");\n",
    "\n",
    "INSERT INTO Students (sid, name, gpa)\n",
    "     VALUES\n",
    "     ('s1', 'Mary', 4),\n",
    "     ('s2', 'Jack', 3),\n",
    "     ('s3', 'Pat', 2);\n",
    "        \n",
    "  SELECT * \n",
    "    FROM Students\n",
    "ORDER BY sid;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "Done.\n",
      "5 rows affected.\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>sid</th>\n",
       "        <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>s1</td>\n",
       "        <td>Running</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>s1</td>\n",
       "        <td>Swimming</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>s2</td>\n",
       "        <td>Swimming</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>s2</td>\n",
       "        <td>Squash</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>s2</td>\n",
       "        <td>Tennis</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('s1', 'Running'),\n",
       " ('s1', 'Swimming'),\n",
       " ('s2', 'Swimming'),\n",
       " ('s2', 'Squash'),\n",
       " ('s2', 'Tennis')]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "DROP TABLE if EXISTS Hobbies CASCADE ;\n",
    "\n",
    "CREATE TABLE Hobbies(\n",
    "  sid  text,\n",
    "  name text\n",
    ");\n",
    "\n",
    "INSERT INTO Hobbies\n",
    "     VALUES\n",
    "     ('s1', 'Running'),\n",
    "     ('s1', 'Swimming'),\n",
    "     ('s2', 'Swimming'),\n",
    "     ('s2', 'Squash'),\n",
    "     ('s2', 'Tennis') ;\n",
    "        \n",
    "  SELECT * \n",
    "    FROM Hobbies\n",
    "ORDER BY sid;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**`SELECT` all fields from TWO tables at the same time**"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "%%sql\n",
    "--Solution\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is termed the **cross product** of two tables (relations) which each entry in one table is matched (joined) with **ALL** entries of the other table.  Note that if the two tables being joined have the same attribute name then Postgres renames them for us for differentiation."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The cross product of two tables is often not that useful.  Rather we want to align corresponding rows i.e., join rows together that have information about the same entity.\n",
    "\n",
    "One way of doing this is:"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "%%sql\n",
    "--Solution\n",
    "  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The above way of joining two tables using a `WHERE` clause is **NOT** the recommended way.  A `WHERE` clause should _only_ be used for _filtering_ rows, based on a condition, as they are read in.\n",
    "\n",
    "To join two tables together, use the recommended `JOIN` clause:"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "%%sql\n",
    "--Solution\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**How many hobbies is each student doing?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "-- Solution\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Hmmm ... Pat is not doing any hobbies and we would like to know that \n",
    "information. How can we get that?**\n",
    "\n",
    "We need to use a particular form of nested query termed a _correlated nested query_. This essentially amount to a nested loop.\n",
    "\n",
    "The outer most `SELECT` statement iterates over each row of `Students`.  For each row of `Students` the inner `SELECT` iterates over `Hobbies`.   "
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "%%sql \n",
    "-- Solution\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### More details on how correlated nested queries work\n",
    "\n",
    "SQL is a declarative language (you say **WHAT** to do) whereas most languages you may have seen already (Java, Python etc) are procedural languages (you say **HOW** to do it).\n",
    "\n",
    "The following analogy may help.\n",
    "\n",
    "Think of a row in a SQL table as a Python _dictionary_ and column names as _keys_ to that dictionary.  So if `s` is a single row of the `Students` table then we can access the individual cells by using the column names as keys e.g., `s['sid']`, `s['name']`, and `s['gpa']`.\n",
    "\n",
    "A SQL table can be thought of as a list of rows and hence a list of dictionaries.  Hence a SQL table in Python terms could be viewed as having the following structure:\n",
    "\n",
    "```\n",
    "[ {...        },\n",
    "  {...        },\n",
    "  {...        },\n",
    "  ... ]\n",
    " ```\n",
    " \n",
    "As we discussed in class, a `FROM` statement is equivalent to a `FOR` statement in a procedural language. So,\n",
    "\n",
    "```\n",
    "SELECT s.sid, s.name\n",
    "  FROM Students as s \n",
    " ```\n",
    " \n",
    "is equivalent to:\n",
    "\n",
    "```\n",
    "for s in Students:\n",
    "    print( s['sid'], s['name'] )\n",
    "```\n",
    "\n",
    "What we want is: for a given `sid`, count the number of times it occurs in the `Hobbies` table. Expanding this further we get:\n",
    " \n",
    "   1. Given a Student, `s`, count the number of times `s['sid']` occurs in       the `Hobbies` table \n",
    "   2. Do this for all `Students`\n",
    "\n",
    "Step 1, procedurally is:\n",
    "\n",
    "```\n",
    "def count(Hobbies, s):\n",
    "    for h in Hobbies:\n",
    "       cnt = 0\n",
    "       if h['sid'] == s['sid']:\n",
    "          cnt += 1\n",
    "    return cnt\n",
    "```\n",
    "\n",
    "The declarative version in SQL is:\n",
    " \n",
    "``` \n",
    "SELECT count(h.sid) \n",
    "  FROM Hobbies AS h \n",
    " WHERE h.sid = s.sid\n",
    "```\n",
    "\n",
    "Step 2, procedually in Python is\n",
    "\n",
    "```\n",
    "for s in Students:\n",
    "   cnt = count( Hobbies, s)\n",
    "   print( s['sid'], s['name'], cnt)\n",
    "```\n",
    "\n",
    "declaratively in SQL we have\n",
    "\n",
    "```\n",
    "SELECT s.sid, s.name, <step-1>\n",
    "  FROM Students AS s;\n",
    "```\n",
    "\n",
    "The above `s` is accessed non-locally in step-1, similar to how non-local variables are accessed in Python.\n",
    " \n",
    "_What takes many lines of code in Java, can be said in a few lines of Python.  The joy of Python!  What can be said in many lines of (procedural) Python, can be said in a few lines of SQL.  But, of course, Python is general purpose whereas SQL has been crafted for working specifically with tables._"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}