notebooks/09. Relational CRUD/09.1 Relational CRUD.ipynb

   1 {
   2  "metadata": {
   3   "name": "",
   4   "signature": "sha256:cd3d8a5219bdc5ea9da82fdbfe3b9e6ff8b67beb40d74ed4ace058f529e815fe"
   5  },
   6  "nbformat": 3,
   7  "nbformat_minor": 0,
   8  "worksheets": [
   9   {
  10    "cells": [
  11     {
  12      "cell_type": "heading",
  13      "level": 1,
  14      "metadata": {},
  15      "source": [
  16       "Section 9 Relational CRUD Part 1"
  17      ]
  18     },
  19     {
  20      "cell_type": "markdown",
  21      "metadata": {},
  22      "source": [
  23       "The aim of this practical activity is to introduce you to the environment used to execute PostgreSQL SQL statements so that you can create database tables, populate them with data, and run queries against the data in order to ask questions about the data.\n",
  24       "\n",
  25       "In this activity you will create, populate and query the patient table as used in the study materials for Section 9.\n"
  26      ]
  27     },
  28     {
  29      "cell_type": "markdown",
  30      "metadata": {},
  31      "source": [
  32       " "
  33      ]
  34     },
  35     {
  36      "cell_type": "markdown",
  37      "metadata": {},
  38      "source": [
  39       "Setup the environment to run PostgreSQL."
  40      ]
  41     },
  42     {
  43      "cell_type": "code",
  44      "collapsed": false,
  45      "input": [
  46       "import pandas as pd"
  47      ],
  48      "language": "python",
  49      "metadata": {},
  50      "outputs": [],
  51      "prompt_number": 1
  52     },
  53     {
  54      "cell_type": "code",
  55      "collapsed": false,
  56      "input": [
  57       "import pandas.io.sql as psql"
  58      ],
  59      "language": "python",
  60      "metadata": {},
  61      "outputs": [],
  62      "prompt_number": 2
  63     },
  64     {
  65      "cell_type": "code",
  66      "collapsed": false,
  67      "input": [
  68       "import psycopg2 as pg"
  69      ],
  70      "language": "python",
  71      "metadata": {},
  72      "outputs": [],
  73      "prompt_number": 3
  74     },
  75     {
  76      "cell_type": "code",
  77      "collapsed": false,
  78      "input": [
  79       "conn = pg.connect(\"dbname='postgres'  host='localhost' user='test' password='test' \")"
  80      ],
  81      "language": "python",
  82      "metadata": {},
  83      "outputs": [],
  84      "prompt_number": 4
  85     },
  86     {
  87      "cell_type": "code",
  88      "collapsed": false,
  89      "input": [
  90       "c = conn.cursor()"
  91      ],
  92      "language": "python",
  93      "metadata": {},
  94      "outputs": [],
  95      "prompt_number": 5
  96     },
  97     {
  98      "cell_type": "markdown",
  99      "metadata": {},
 100      "source": [
 101       "Create the patient table and populate it with the same data as used in the study materials for Section 9."
 102      ]
 103     },
 104     {
 105      "cell_type": "code",
 106      "collapsed": false,
 107      "input": [
 108       "c.execute(\"DROP TABLE IF EXISTS patient\")\n",
 109       "c.execute(\"CREATE TABLE patient (\\\n",
 110       "            patient_id CHAR(4) NOT NULL CHECK (patient_id SIMILAR TO 'p[0-9][0-9][0-9]'),\\\n",
 111       "            patient_name VARCHAR(20) NOT NULL,\\\n",
 112       "            date_of_birth DATE NOT NULL,\\\n",
 113       "            gender CHAR(1) NOT NULL CHECK (gender = 'F' OR gender = 'M'),\\\n",
 114       "            height DECIMAL(4,1) CHECK (height > 0),\\\n",
 115       "            weight DECIMAL(4,1) CHECK (weight > 0),\\\n",
 116       "            PRIMARY KEY (patient_id)\\\n",
 117       "           )\")\n",
 118       "c.execute(\"INSERT INTO patient (patient_id, patient_name, date_of_birth, gender, height, weight)\\\n",
 119       "            VALUES ( 'p001', 'Thornton', '1980-01-22', 'F', 162.3, 71.6)\")\n",
 120       "c.execute(\"INSERT INTO patient (patient_id, patient_name, date_of_birth, gender, height, weight)\\\n",
 121       "            VALUES ( 'p007', 'Tennent', '1980-04-01', 'M', 176.8, 70.9)\")\n",
 122       "c.execute(\"INSERT INTO patient (patient_id, patient_name, date_of_birth, gender, height, weight)\\\n",
 123       "            VALUES ( 'p008', 'James', '1980-07-08', 'M', 167.9, 70.5)\")\n",
 124       "c.execute(\"INSERT INTO patient (patient_id, patient_name, date_of_birth, gender, height, weight)\\\n",
 125       "            VALUES ( 'p009', 'Kay', '1980-09-25', 'F', 164.7, 53.2)\")\n",
 126       "c.execute(\"INSERT INTO patient (patient_id, patient_name, date_of_birth, gender, height, weight)\\\n",
 127       "            VALUES ( 'p015', 'Harris', '1980-12-04', 'M', 180.6, 64.3)\")\n",
 128       "c.execute(\"INSERT INTO patient (patient_id, patient_name, date_of_birth, gender)\\\n",
 129       "            VALUES ( 'p031', 'Rubinstein', '1980-12-23', 'F')\")\n",
 130       "c.execute(\"INSERT INTO patient (patient_id, patient_name, date_of_birth, gender)\\\n",
 131       "            VALUES ( 'p037', 'Boswell', '1981-06-11', 'F')\")\n",
 132       "c.execute(\"INSERT INTO patient (patient_id, patient_name, date_of_birth, gender, height, weight)\\\n",
 133       "            VALUES ( 'p038', 'Ming', '1981-09-23', 'M', 186.3, 85.4)\")\n",
 134       "c.execute(\"INSERT INTO patient (patient_id, patient_name, date_of_birth, gender, height, weight)\\\n",
 135       "            VALUES ( 'p039', 'Maher', '1981-10-09', 'F', 161.9, 73.0)\")\n",
 136       "c.execute(\"INSERT INTO patient (patient_id, patient_name, date_of_birth, gender, height, weight)\\\n",
 137       "            VALUES ( 'p068', 'Monroe', '1981-10-21', 'F', 165.0, 62.6)\")\n",
 138       "c.execute(\"INSERT INTO patient (patient_id, patient_name, date_of_birth, gender, height, weight)\\\n",
 139       "            VALUES ( 'p071', 'Harris', '1981-12-12', 'M', 186.3, 76.7)\")\n",
 140       "c.execute(\"INSERT INTO patient (patient_id, patient_name, date_of_birth, gender, height, weight)\\\n",
 141       "            VALUES ( 'p078', 'Hunt', '1982-02-25', 'M', 179.9, 74.3)\")\n",
 142       "c.execute(\"INSERT INTO patient (patient_id, patient_name, date_of_birth, gender, height, weight)\\\n",
 143       "            VALUES ( 'p079', 'Dixon', '1982-05-05', 'F', 163.9, 56.5)\")\n",
 144       "c.execute(\"INSERT INTO patient (patient_id, patient_name, date_of_birth, gender, height, weight)\\\n",
 145       "            VALUES ( 'p080', 'Bell', '1982-06-11', 'F', 171.3, 49.2)\")\n",
 146       "c.execute(\"INSERT INTO patient (patient_id, patient_name, date_of_birth, gender, height, weight)\\\n",
 147       "            VALUES ( 'p087', 'Reed', '1982-06-14', 'F', 160.0, 59.1)\")\n",
 148       "c.execute(\"INSERT INTO patient (patient_id, patient_name, date_of_birth, gender, height, weight)\\\n",
 149       "            VALUES ( 'p088', 'Boswell', '1982-08-23', 'M', 168.4, 91.4)\")\n",
 150       "c.execute(\"INSERT INTO patient (patient_id, patient_name, date_of_birth, gender, height, weight)\\\n",
 151       "           VALUES ( 'p089', 'Jarvis', '1982-11-09', 'F', 172.9, 53.4)\")\n",
 152       "c.execute(\"COMMIT\")"
 153      ],
 154      "language": "python",
 155      "metadata": {},
 156      "outputs": [],
 157      "prompt_number": 6
 158     },
 159     {
 160      "cell_type": "markdown",
 161      "metadata": {},
 162      "source": [
 163       "Display the contents of the patient table."
 164      ]
 165     },
 166     {
 167      "cell_type": "code",
 168      "collapsed": false,
 169      "input": [
 170       "df = pd.read_sql(\"SELECT * FROM patient \\\n",
 171       "                  ORDER BY patient_id\", conn)\n",
 172       "df"
 173      ],
 174      "language": "python",
 175      "metadata": {},
 176      "outputs": [
 177       {
 178        "html": [
 179         "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
 180         "<table border=\"1\" class=\"dataframe\">\n",
 181         "  <thead>\n",
 182         "    <tr style=\"text-align: right;\">\n",
 183         "      <th></th>\n",
 184         "      <th>patient_id</th>\n",
 185         "      <th>patient_name</th>\n",
 186         "      <th>date_of_birth</th>\n",
 187         "      <th>gender</th>\n",
 188         "      <th>height</th>\n",
 189         "      <th>weight</th>\n",
 190         "    </tr>\n",
 191         "  </thead>\n",
 192         "  <tbody>\n",
 193         "    <tr>\n",
 194         "      <th>0 </th>\n",
 195         "      <td> p001</td>\n",
 196         "      <td>   Thornton</td>\n",
 197         "      <td> 1980-01-22</td>\n",
 198         "      <td> F</td>\n",
 199         "      <td> 162.3</td>\n",
 200         "      <td> 71.6</td>\n",
 201         "    </tr>\n",
 202         "    <tr>\n",
 203         "      <th>1 </th>\n",
 204         "      <td> p007</td>\n",
 205         "      <td>    Tennent</td>\n",
 206         "      <td> 1980-04-01</td>\n",
 207         "      <td> M</td>\n",
 208         "      <td> 176.8</td>\n",
 209         "      <td> 70.9</td>\n",
 210         "    </tr>\n",
 211         "    <tr>\n",
 212         "      <th>2 </th>\n",
 213         "      <td> p008</td>\n",
 214         "      <td>      James</td>\n",
 215         "      <td> 1980-07-08</td>\n",
 216         "      <td> M</td>\n",
 217         "      <td> 167.9</td>\n",
 218         "      <td> 70.5</td>\n",
 219         "    </tr>\n",
 220         "    <tr>\n",
 221         "      <th>3 </th>\n",
 222         "      <td> p009</td>\n",
 223         "      <td>        Kay</td>\n",
 224         "      <td> 1980-09-25</td>\n",
 225         "      <td> F</td>\n",
 226         "      <td> 164.7</td>\n",
 227         "      <td> 53.2</td>\n",
 228         "    </tr>\n",
 229         "    <tr>\n",
 230         "      <th>4 </th>\n",
 231         "      <td> p015</td>\n",
 232         "      <td>     Harris</td>\n",
 233         "      <td> 1980-12-04</td>\n",
 234         "      <td> M</td>\n",
 235         "      <td> 180.6</td>\n",
 236         "      <td> 64.3</td>\n",
 237         "    </tr>\n",
 238         "    <tr>\n",
 239         "      <th>5 </th>\n",
 240         "      <td> p031</td>\n",
 241         "      <td> Rubinstein</td>\n",
 242         "      <td> 1980-12-23</td>\n",
 243         "      <td> F</td>\n",
 244         "      <td>   NaN</td>\n",
 245         "      <td>  NaN</td>\n",
 246         "    </tr>\n",
 247         "    <tr>\n",
 248         "      <th>6 </th>\n",
 249         "      <td> p037</td>\n",
 250         "      <td>    Boswell</td>\n",
 251         "      <td> 1981-06-11</td>\n",
 252         "      <td> F</td>\n",
 253         "      <td>   NaN</td>\n",
 254         "      <td>  NaN</td>\n",
 255         "    </tr>\n",
 256         "    <tr>\n",
 257         "      <th>7 </th>\n",
 258         "      <td> p038</td>\n",
 259         "      <td>       Ming</td>\n",
 260         "      <td> 1981-09-23</td>\n",
 261         "      <td> M</td>\n",
 262         "      <td> 186.3</td>\n",
 263         "      <td> 85.4</td>\n",
 264         "    </tr>\n",
 265         "    <tr>\n",
 266         "      <th>8 </th>\n",
 267         "      <td> p039</td>\n",
 268         "      <td>      Maher</td>\n",
 269         "      <td> 1981-10-09</td>\n",
 270         "      <td> F</td>\n",
 271         "      <td> 161.9</td>\n",
 272         "      <td> 73.0</td>\n",
 273         "    </tr>\n",
 274         "    <tr>\n",
 275         "      <th>9 </th>\n",
 276         "      <td> p068</td>\n",
 277         "      <td>     Monroe</td>\n",
 278         "      <td> 1981-10-21</td>\n",
 279         "      <td> F</td>\n",
 280         "      <td> 165.0</td>\n",
 281         "      <td> 62.6</td>\n",
 282         "    </tr>\n",
 283         "    <tr>\n",
 284         "      <th>10</th>\n",
 285         "      <td> p071</td>\n",
 286         "      <td>     Harris</td>\n",
 287         "      <td> 1981-12-12</td>\n",
 288         "      <td> M</td>\n",
 289         "      <td> 186.3</td>\n",
 290         "      <td> 76.7</td>\n",
 291         "    </tr>\n",
 292         "    <tr>\n",
 293         "      <th>11</th>\n",
 294         "      <td> p078</td>\n",
 295         "      <td>       Hunt</td>\n",
 296         "      <td> 1982-02-25</td>\n",
 297         "      <td> M</td>\n",
 298         "      <td> 179.9</td>\n",
 299         "      <td> 74.3</td>\n",
 300         "    </tr>\n",
 301         "    <tr>\n",
 302         "      <th>12</th>\n",
 303         "      <td> p079</td>\n",
 304         "      <td>      Dixon</td>\n",
 305         "      <td> 1982-05-05</td>\n",
 306         "      <td> F</td>\n",
 307         "      <td> 163.9</td>\n",
 308         "      <td> 56.5</td>\n",
 309         "    </tr>\n",
 310         "    <tr>\n",
 311         "      <th>13</th>\n",
 312         "      <td> p080</td>\n",
 313         "      <td>       Bell</td>\n",
 314         "      <td> 1982-06-11</td>\n",
 315         "      <td> F</td>\n",
 316         "      <td> 171.3</td>\n",
 317         "      <td> 49.2</td>\n",
 318         "    </tr>\n",
 319         "    <tr>\n",
 320         "      <th>14</th>\n",
 321         "      <td> p087</td>\n",
 322         "      <td>       Reed</td>\n",
 323         "      <td> 1982-06-14</td>\n",
 324         "      <td> F</td>\n",
 325         "      <td> 160.0</td>\n",
 326         "      <td> 59.1</td>\n",
 327         "    </tr>\n",
 328         "    <tr>\n",
 329         "      <th>15</th>\n",
 330         "      <td> p088</td>\n",
 331         "      <td>    Boswell</td>\n",
 332         "      <td> 1982-08-23</td>\n",
 333         "      <td> M</td>\n",
 334         "      <td> 168.4</td>\n",
 335         "      <td> 91.4</td>\n",
 336         "    </tr>\n",
 337         "    <tr>\n",
 338         "      <th>16</th>\n",
 339         "      <td> p089</td>\n",
 340         "      <td>     Jarvis</td>\n",
 341         "      <td> 1982-11-09</td>\n",
 342         "      <td> F</td>\n",
 343         "      <td> 172.9</td>\n",
 344         "      <td> 53.4</td>\n",
 345         "    </tr>\n",
 346         "  </tbody>\n",
 347         "</table>\n",
 348         "</div>"
 349        ],
 350        "metadata": {},
 351        "output_type": "pyout",
 352        "prompt_number": 7,
 353        "text": [
 354         "   patient_id patient_name date_of_birth gender  height  weight\n",
 355         "0        p001     Thornton    1980-01-22      F   162.3    71.6\n",
 356         "1        p007      Tennent    1980-04-01      M   176.8    70.9\n",
 357         "2        p008        James    1980-07-08      M   167.9    70.5\n",
 358         "3        p009          Kay    1980-09-25      F   164.7    53.2\n",
 359         "4        p015       Harris    1980-12-04      M   180.6    64.3\n",
 360         "5        p031   Rubinstein    1980-12-23      F     NaN     NaN\n",
 361         "6        p037      Boswell    1981-06-11      F     NaN     NaN\n",
 362         "7        p038         Ming    1981-09-23      M   186.3    85.4\n",
 363         "8        p039        Maher    1981-10-09      F   161.9    73.0\n",
 364         "9        p068       Monroe    1981-10-21      F   165.0    62.6\n",
 365         "10       p071       Harris    1981-12-12      M   186.3    76.7\n",
 366         "11       p078         Hunt    1982-02-25      M   179.9    74.3\n",
 367         "12       p079        Dixon    1982-05-05      F   163.9    56.5\n",
 368         "13       p080         Bell    1982-06-11      F   171.3    49.2\n",
 369         "14       p087         Reed    1982-06-14      F   160.0    59.1\n",
 370         "15       p088      Boswell    1982-08-23      M   168.4    91.4\n",
 371         "16       p089       Jarvis    1982-11-09      F   172.9    53.4"
 372        ]
 373       }
 374      ],
 375      "prompt_number": 7
 376     },
 377     {
 378      "cell_type": "markdown",
 379      "metadata": {},
 380      "source": [
 381       " "
 382      ]
 383     },
 384     {
 385      "cell_type": "heading",
 386      "level": 6,
 387      "metadata": {},
 388      "source": [
 389       "Projection"
 390      ]
 391     },
 392     {
 393      "cell_type": "markdown",
 394      "metadata": {},
 395      "source": [
 396       "The relational algebra operator project \u2018slices\u2019 a relation vertically, selecting specified attributes (columns) to appear in the resultant relation. The equivalent SQL SELECT statement is:\n",
 397       " \n",
 398       "SELECT {column list}\n",
 399       "FROM {table}\n",
 400       " \n",
 401       "For example, \u201cDisplay the names of patients and their gender\u201d:\n"
 402      ]
 403     },
 404     {
 405      "cell_type": "code",
 406      "collapsed": false,
 407      "input": [
 408       "df = pd.read_sql(\"SELECT patient_name, gender \\\n",
 409       "                  FROM patient \\\n",
 410       "                  ORDER BY patient_name, gender\", conn)\n",
 411       "df"
 412      ],
 413      "language": "python",
 414      "metadata": {},
 415      "outputs": [
 416       {
 417        "html": [
 418         "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
 419         "<table border=\"1\" class=\"dataframe\">\n",
 420         "  <thead>\n",
 421         "    <tr style=\"text-align: right;\">\n",
 422         "      <th></th>\n",
 423         "      <th>patient_name</th>\n",
 424         "      <th>gender</th>\n",
 425         "    </tr>\n",
 426         "  </thead>\n",
 427         "  <tbody>\n",
 428         "    <tr>\n",
 429         "      <th>0 </th>\n",
 430         "      <td>       Bell</td>\n",
 431         "      <td> F</td>\n",
 432         "    </tr>\n",
 433         "    <tr>\n",
 434         "      <th>1 </th>\n",
 435         "      <td>    Boswell</td>\n",
 436         "      <td> F</td>\n",
 437         "    </tr>\n",
 438         "    <tr>\n",
 439         "      <th>2 </th>\n",
 440         "      <td>    Boswell</td>\n",
 441         "      <td> M</td>\n",
 442         "    </tr>\n",
 443         "    <tr>\n",
 444         "      <th>3 </th>\n",
 445         "      <td>      Dixon</td>\n",
 446         "      <td> F</td>\n",
 447         "    </tr>\n",
 448         "    <tr>\n",
 449         "      <th>4 </th>\n",
 450         "      <td>     Harris</td>\n",
 451         "      <td> M</td>\n",
 452         "    </tr>\n",
 453         "    <tr>\n",
 454         "      <th>5 </th>\n",
 455         "      <td>     Harris</td>\n",
 456         "      <td> M</td>\n",
 457         "    </tr>\n",
 458         "    <tr>\n",
 459         "      <th>6 </th>\n",
 460         "      <td>       Hunt</td>\n",
 461         "      <td> M</td>\n",
 462         "    </tr>\n",
 463         "    <tr>\n",
 464         "      <th>7 </th>\n",
 465         "      <td>      James</td>\n",
 466         "      <td> M</td>\n",
 467         "    </tr>\n",
 468         "    <tr>\n",
 469         "      <th>8 </th>\n",
 470         "      <td>     Jarvis</td>\n",
 471         "      <td> F</td>\n",
 472         "    </tr>\n",
 473         "    <tr>\n",
 474         "      <th>9 </th>\n",
 475         "      <td>        Kay</td>\n",
 476         "      <td> F</td>\n",
 477         "    </tr>\n",
 478         "    <tr>\n",
 479         "      <th>10</th>\n",
 480         "      <td>      Maher</td>\n",
 481         "      <td> F</td>\n",
 482         "    </tr>\n",
 483         "    <tr>\n",
 484         "      <th>11</th>\n",
 485         "      <td>       Ming</td>\n",
 486         "      <td> M</td>\n",
 487         "    </tr>\n",
 488         "    <tr>\n",
 489         "      <th>12</th>\n",
 490         "      <td>     Monroe</td>\n",
 491         "      <td> F</td>\n",
 492         "    </tr>\n",
 493         "    <tr>\n",
 494         "      <th>13</th>\n",
 495         "      <td>       Reed</td>\n",
 496         "      <td> F</td>\n",
 497         "    </tr>\n",
 498         "    <tr>\n",
 499         "      <th>14</th>\n",
 500         "      <td> Rubinstein</td>\n",
 501         "      <td> F</td>\n",
 502         "    </tr>\n",
 503         "    <tr>\n",
 504         "      <th>15</th>\n",
 505         "      <td>    Tennent</td>\n",
 506         "      <td> M</td>\n",
 507         "    </tr>\n",
 508         "    <tr>\n",
 509         "      <th>16</th>\n",
 510         "      <td>   Thornton</td>\n",
 511         "      <td> F</td>\n",
 512         "    </tr>\n",
 513         "  </tbody>\n",
 514         "</table>\n",
 515         "</div>"
 516        ],
 517        "metadata": {},
 518        "output_type": "pyout",
 519        "prompt_number": 8,
 520        "text": [
 521         "   patient_name gender\n",
 522         "0          Bell      F\n",
 523         "1       Boswell      F\n",
 524         "2       Boswell      M\n",
 525         "3         Dixon      F\n",
 526         "4        Harris      M\n",
 527         "5        Harris      M\n",
 528         "6          Hunt      M\n",
 529         "7         James      M\n",
 530         "8        Jarvis      F\n",
 531         "9           Kay      F\n",
 532         "10        Maher      F\n",
 533         "11         Ming      M\n",
 534         "12       Monroe      F\n",
 535         "13         Reed      F\n",
 536         "14   Rubinstein      F\n",
 537         "15      Tennent      M\n",
 538         "16     Thornton      F"
 539        ]
 540       }
 541      ],
 542      "prompt_number": 8
 543     },
 544     {
 545      "cell_type": "markdown",
 546      "metadata": {},
 547      "source": [
 548       "\u201cDisplay the names of patients and their gender, eliminating duplicate rows\u201d:"
 549      ]
 550     },
 551     {
 552      "cell_type": "code",
 553      "collapsed": false,
 554      "input": [
 555       "df = pd.read_sql(\"SELECT DISTINCT patient_name, gender \\\n",
 556       "                  FROM patient \\\n",
 557       "                  ORDER BY patient_name, gender\", conn)\n",
 558       "df"
 559      ],
 560      "language": "python",
 561      "metadata": {},
 562      "outputs": [
 563       {
 564        "html": [
 565         "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
 566         "<table border=\"1\" class=\"dataframe\">\n",
 567         "  <thead>\n",
 568         "    <tr style=\"text-align: right;\">\n",
 569         "      <th></th>\n",
 570         "      <th>patient_name</th>\n",
 571         "      <th>gender</th>\n",
 572         "    </tr>\n",
 573         "  </thead>\n",
 574         "  <tbody>\n",
 575         "    <tr>\n",
 576         "      <th>0 </th>\n",
 577         "      <td>       Bell</td>\n",
 578         "      <td> F</td>\n",
 579         "    </tr>\n",
 580         "    <tr>\n",
 581         "      <th>1 </th>\n",
 582         "      <td>    Boswell</td>\n",
 583         "      <td> F</td>\n",
 584         "    </tr>\n",
 585         "    <tr>\n",
 586         "      <th>2 </th>\n",
 587         "      <td>    Boswell</td>\n",
 588         "      <td> M</td>\n",
 589         "    </tr>\n",
 590         "    <tr>\n",
 591         "      <th>3 </th>\n",
 592         "      <td>      Dixon</td>\n",
 593         "      <td> F</td>\n",
 594         "    </tr>\n",
 595         "    <tr>\n",
 596         "      <th>4 </th>\n",
 597         "      <td>     Harris</td>\n",
 598         "      <td> M</td>\n",
 599         "    </tr>\n",
 600         "    <tr>\n",
 601         "      <th>5 </th>\n",
 602         "      <td>       Hunt</td>\n",
 603         "      <td> M</td>\n",
 604         "    </tr>\n",
 605         "    <tr>\n",
 606         "      <th>6 </th>\n",
 607         "      <td>      James</td>\n",
 608         "      <td> M</td>\n",
 609         "    </tr>\n",
 610         "    <tr>\n",
 611         "      <th>7 </th>\n",
 612         "      <td>     Jarvis</td>\n",
 613         "      <td> F</td>\n",
 614         "    </tr>\n",
 615         "    <tr>\n",
 616         "      <th>8 </th>\n",
 617         "      <td>        Kay</td>\n",
 618         "      <td> F</td>\n",
 619         "    </tr>\n",
 620         "    <tr>\n",
 621         "      <th>9 </th>\n",
 622         "      <td>      Maher</td>\n",
 623         "      <td> F</td>\n",
 624         "    </tr>\n",
 625         "    <tr>\n",
 626         "      <th>10</th>\n",
 627         "      <td>       Ming</td>\n",
 628         "      <td> M</td>\n",
 629         "    </tr>\n",
 630         "    <tr>\n",
 631         "      <th>11</th>\n",
 632         "      <td>     Monroe</td>\n",
 633         "      <td> F</td>\n",
 634         "    </tr>\n",
 635         "    <tr>\n",
 636         "      <th>12</th>\n",
 637         "      <td>       Reed</td>\n",
 638         "      <td> F</td>\n",
 639         "    </tr>\n",
 640         "    <tr>\n",
 641         "      <th>13</th>\n",
 642         "      <td> Rubinstein</td>\n",
 643         "      <td> F</td>\n",
 644         "    </tr>\n",
 645         "    <tr>\n",
 646         "      <th>14</th>\n",
 647         "      <td>    Tennent</td>\n",
 648         "      <td> M</td>\n",
 649         "    </tr>\n",
 650         "    <tr>\n",
 651         "      <th>15</th>\n",
 652         "      <td>   Thornton</td>\n",
 653         "      <td> F</td>\n",
 654         "    </tr>\n",
 655         "  </tbody>\n",
 656         "</table>\n",
 657         "</div>"
 658        ],
 659        "metadata": {},
 660        "output_type": "pyout",
 661        "prompt_number": 9,
 662        "text": [
 663         "   patient_name gender\n",
 664         "0          Bell      F\n",
 665         "1       Boswell      F\n",
 666         "2       Boswell      M\n",
 667         "3         Dixon      F\n",
 668         "4        Harris      M\n",
 669         "5          Hunt      M\n",
 670         "6         James      M\n",
 671         "7        Jarvis      F\n",
 672         "8           Kay      F\n",
 673         "9         Maher      F\n",
 674         "10         Ming      M\n",
 675         "11       Monroe      F\n",
 676         "12         Reed      F\n",
 677         "13   Rubinstein      F\n",
 678         "14      Tennent      M\n",
 679         "15     Thornton      F"
 680        ]
 681       }
 682      ],
 683      "prompt_number": 9
 684     },
 685     {
 686      "cell_type": "markdown",
 687      "metadata": {},
 688      "source": [
 689       " "
 690      ]
 691     },
 692     {
 693      "cell_type": "heading",
 694      "level": 6,
 695      "metadata": {},
 696      "source": [
 697       "Selection"
 698      ]
 699     },
 700     {
 701      "cell_type": "markdown",
 702      "metadata": {},
 703      "source": [
 704       "The relational algebra operator select \u2018slices\u2019 a relation horizontally, selecting specified tuple (rows) subject to some condition to appear in the resultant relation. The equivalent SQL SELECT statement is:\n",
 705       " \n",
 706       "SELECT *\n",
 707       "FROM {table}\n",
 708       "WHERE {condition}\n",
 709       " \n",
 710       "For example, \u201cDisplay the details of all the female patients\u201d:\n"
 711      ]
 712     },
 713     {
 714      "cell_type": "code",
 715      "collapsed": false,
 716      "input": [
 717       "df = pd.read_sql(\"SELECT * \\\n",
 718       "                  FROM patient \\\n",
 719       "                  WHERE gender = 'F' \\\n",
 720       "                  ORDER BY patient_id\", conn)\n",
 721       "df"
 722      ],
 723      "language": "python",
 724      "metadata": {},
 725      "outputs": [
 726       {
 727        "html": [
 728         "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
 729         "<table border=\"1\" class=\"dataframe\">\n",
 730         "  <thead>\n",
 731         "    <tr style=\"text-align: right;\">\n",
 732         "      <th></th>\n",
 733         "      <th>patient_id</th>\n",
 734         "      <th>patient_name</th>\n",
 735         "      <th>date_of_birth</th>\n",
 736         "      <th>gender</th>\n",
 737         "      <th>height</th>\n",
 738         "      <th>weight</th>\n",
 739         "    </tr>\n",
 740         "  </thead>\n",
 741         "  <tbody>\n",
 742         "    <tr>\n",
 743         "      <th>0</th>\n",
 744         "      <td> p001</td>\n",
 745         "      <td>   Thornton</td>\n",
 746         "      <td> 1980-01-22</td>\n",
 747         "      <td> F</td>\n",
 748         "      <td> 162.3</td>\n",
 749         "      <td> 71.6</td>\n",
 750         "    </tr>\n",
 751         "    <tr>\n",
 752         "      <th>1</th>\n",
 753         "      <td> p009</td>\n",
 754         "      <td>        Kay</td>\n",
 755         "      <td> 1980-09-25</td>\n",
 756         "      <td> F</td>\n",
 757         "      <td> 164.7</td>\n",
 758         "      <td> 53.2</td>\n",
 759         "    </tr>\n",
 760         "    <tr>\n",
 761         "      <th>2</th>\n",
 762         "      <td> p031</td>\n",
 763         "      <td> Rubinstein</td>\n",
 764         "      <td> 1980-12-23</td>\n",
 765         "      <td> F</td>\n",
 766         "      <td>   NaN</td>\n",
 767         "      <td>  NaN</td>\n",
 768         "    </tr>\n",
 769         "    <tr>\n",
 770         "      <th>3</th>\n",
 771         "      <td> p037</td>\n",
 772         "      <td>    Boswell</td>\n",
 773         "      <td> 1981-06-11</td>\n",
 774         "      <td> F</td>\n",
 775         "      <td>   NaN</td>\n",
 776         "      <td>  NaN</td>\n",
 777         "    </tr>\n",
 778         "    <tr>\n",
 779         "      <th>4</th>\n",
 780         "      <td> p039</td>\n",
 781         "      <td>      Maher</td>\n",
 782         "      <td> 1981-10-09</td>\n",
 783         "      <td> F</td>\n",
 784         "      <td> 161.9</td>\n",
 785         "      <td> 73.0</td>\n",
 786         "    </tr>\n",
 787         "    <tr>\n",
 788         "      <th>5</th>\n",
 789         "      <td> p068</td>\n",
 790         "      <td>     Monroe</td>\n",
 791         "      <td> 1981-10-21</td>\n",
 792         "      <td> F</td>\n",
 793         "      <td> 165.0</td>\n",
 794         "      <td> 62.6</td>\n",
 795         "    </tr>\n",
 796         "    <tr>\n",
 797         "      <th>6</th>\n",
 798         "      <td> p079</td>\n",
 799         "      <td>      Dixon</td>\n",
 800         "      <td> 1982-05-05</td>\n",
 801         "      <td> F</td>\n",
 802         "      <td> 163.9</td>\n",
 803         "      <td> 56.5</td>\n",
 804         "    </tr>\n",
 805         "    <tr>\n",
 806         "      <th>7</th>\n",
 807         "      <td> p080</td>\n",
 808         "      <td>       Bell</td>\n",
 809         "      <td> 1982-06-11</td>\n",
 810         "      <td> F</td>\n",
 811         "      <td> 171.3</td>\n",
 812         "      <td> 49.2</td>\n",
 813         "    </tr>\n",
 814         "    <tr>\n",
 815         "      <th>8</th>\n",
 816         "      <td> p087</td>\n",
 817         "      <td>       Reed</td>\n",
 818         "      <td> 1982-06-14</td>\n",
 819         "      <td> F</td>\n",
 820         "      <td> 160.0</td>\n",
 821         "      <td> 59.1</td>\n",
 822         "    </tr>\n",
 823         "    <tr>\n",
 824         "      <th>9</th>\n",
 825         "      <td> p089</td>\n",
 826         "      <td>     Jarvis</td>\n",
 827         "      <td> 1982-11-09</td>\n",
 828         "      <td> F</td>\n",
 829         "      <td> 172.9</td>\n",
 830         "      <td> 53.4</td>\n",
 831         "    </tr>\n",
 832         "  </tbody>\n",
 833         "</table>\n",
 834         "</div>"
 835        ],
 836        "metadata": {},
 837        "output_type": "pyout",
 838        "prompt_number": 10,
 839        "text": [
 840         "  patient_id patient_name date_of_birth gender  height  weight\n",
 841         "0       p001     Thornton    1980-01-22      F   162.3    71.6\n",
 842         "1       p009          Kay    1980-09-25      F   164.7    53.2\n",
 843         "2       p031   Rubinstein    1980-12-23      F     NaN     NaN\n",
 844         "3       p037      Boswell    1981-06-11      F     NaN     NaN\n",
 845         "4       p039        Maher    1981-10-09      F   161.9    73.0\n",
 846         "5       p068       Monroe    1981-10-21      F   165.0    62.6\n",
 847         "6       p079        Dixon    1982-05-05      F   163.9    56.5\n",
 848         "7       p080         Bell    1982-06-11      F   171.3    49.2\n",
 849         "8       p087         Reed    1982-06-14      F   160.0    59.1\n",
 850         "9       p089       Jarvis    1982-11-09      F   172.9    53.4"
 851        ]
 852       }
 853      ],
 854      "prompt_number": 10
 855     },
 856     {
 857      "cell_type": "markdown",
 858      "metadata": {},
 859      "source": [
 860       " "
 861      ]
 862     },
 863     {
 864      "cell_type": "markdown",
 865      "metadata": {},
 866      "source": [
 867       "\u201cDisplay the details of patients whose weight has not been recorded\u201d:"
 868      ]
 869     },
 870     {
 871      "cell_type": "code",
 872      "collapsed": false,
 873      "input": [
 874       "df = pd.read_sql(\"SELECT * \\\n",
 875       "                  FROM patient \\\n",
 876       "                  WHERE weight IS NULL \\\n",
 877       "                  ORDER BY patient_id\", conn)\n",
 878       "df"
 879      ],
 880      "language": "python",
 881      "metadata": {},
 882      "outputs": [
 883       {
 884        "html": [
 885         "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
 886         "<table border=\"1\" class=\"dataframe\">\n",
 887         "  <thead>\n",
 888         "    <tr style=\"text-align: right;\">\n",
 889         "      <th></th>\n",
 890         "      <th>patient_id</th>\n",
 891         "      <th>patient_name</th>\n",
 892         "      <th>date_of_birth</th>\n",
 893         "      <th>gender</th>\n",
 894         "      <th>height</th>\n",
 895         "      <th>weight</th>\n",
 896         "    </tr>\n",
 897         "  </thead>\n",
 898         "  <tbody>\n",
 899         "    <tr>\n",
 900         "      <th>0</th>\n",
 901         "      <td> p031</td>\n",
 902         "      <td> Rubinstein</td>\n",
 903         "      <td> 1980-12-23</td>\n",
 904         "      <td> F</td>\n",
 905         "      <td> None</td>\n",
 906         "      <td> None</td>\n",
 907         "    </tr>\n",
 908         "    <tr>\n",
 909         "      <th>1</th>\n",
 910         "      <td> p037</td>\n",
 911         "      <td>    Boswell</td>\n",
 912         "      <td> 1981-06-11</td>\n",
 913         "      <td> F</td>\n",
 914         "      <td> None</td>\n",
 915         "      <td> None</td>\n",
 916         "    </tr>\n",
 917         "  </tbody>\n",
 918         "</table>\n",
 919         "</div>"
 920        ],
 921        "metadata": {},
 922        "output_type": "pyout",
 923        "prompt_number": 11,
 924        "text": [
 925         "  patient_id patient_name date_of_birth gender height weight\n",
 926         "0       p031   Rubinstein    1980-12-23      F   None   None\n",
 927         "1       p037      Boswell    1981-06-11      F   None   None"
 928        ]
 929       }
 930      ],
 931      "prompt_number": 11
 932     },
 933     {
 934      "cell_type": "markdown",
 935      "metadata": {},
 936      "source": [
 937       " "
 938      ]
 939     },
 940     {
 941      "cell_type": "heading",
 942      "level": 6,
 943      "metadata": {},
 944      "source": [
 945       "Aggregation"
 946      ]
 947     },
 948     {
 949      "cell_type": "markdown",
 950      "metadata": {},
 951      "source": [
 952       "Summarising data by subset groupings \u2013 aggregation, is effected by including aggregate functions in the SELECT clause and using the GROUP BY clause:\n",
 953       "    \n",
 954       "SELECT {aggregate function(s)} FROM {table(s)} [WHERE {condition} ] [GROUP BY {column list} [HAVING {condition}]]\n",
 955       "\n",
 956       "For example, \"Display some summary statistics about patients\":"
 957      ]
 958     },
 959     {
 960      "cell_type": "code",
 961      "collapsed": false,
 962      "input": [
 963       "df = pd.read_sql(\"SELECT COUNT(*) AS number_of_patients, \\\n",
 964       "                         COUNT(DISTINCT patient_name) AS number_of_distinct_names, \\\n",
 965       "                         COUNT(weight) AS number_weighed, \\\n",
 966       "                         MIN(weight) AS minimum_weight, \\\n",
 967       "                         MAX(weight) AS maximum_weight, \\\n",
 968       "                         CAST(AVG(weight) AS DECIMAL(4,1)) AS average_weight \\\n",
 969       "                  FROM patient\", conn)\n",
 970       "df"
 971      ],
 972      "language": "python",
 973      "metadata": {},
 974      "outputs": [
 975       {
 976        "html": [
 977         "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
 978         "<table border=\"1\" class=\"dataframe\">\n",
 979         "  <thead>\n",
 980         "    <tr style=\"text-align: right;\">\n",
 981         "      <th></th>\n",
 982         "      <th>number_of_patients</th>\n",
 983         "      <th>number_of_distinct_names</th>\n",
 984         "      <th>number_weighed</th>\n",
 985         "      <th>minimum_weight</th>\n",
 986         "      <th>maximum_weight</th>\n",
 987         "      <th>average_weight</th>\n",
 988         "    </tr>\n",
 989         "  </thead>\n",
 990         "  <tbody>\n",
 991         "    <tr>\n",
 992         "      <th>0</th>\n",
 993         "      <td> 17</td>\n",
 994         "      <td> 15</td>\n",
 995         "      <td> 15</td>\n",
 996         "      <td> 49.2</td>\n",
 997         "      <td> 91.4</td>\n",
 998         "      <td> 67.5</td>\n",
 999         "    </tr>\n",
1000         "  </tbody>\n",
1001         "</table>\n",
1002         "</div>"
1003        ],
1004        "metadata": {},
1005        "output_type": "pyout",
1006        "prompt_number": 12,
1007        "text": [
1008         "   number_of_patients  number_of_distinct_names  number_weighed  \\\n",
1009         "0                  17                        15              15   \n",
1010         "\n",
1011         "   minimum_weight  maximum_weight  average_weight  \n",
1012         "0            49.2            91.4            67.5  "
1013        ]
1014       }
1015      ],
1016      "prompt_number": 12
1017     },
1018     {
1019      "cell_type": "markdown",
1020      "metadata": {},
1021      "source": [
1022       " "
1023      ]
1024     },
1025     {
1026      "cell_type": "markdown",
1027      "metadata": {},
1028      "source": [
1029       "\"Display separate summary statistics about female and male patients\":"
1030      ]
1031     },
1032     {
1033      "cell_type": "code",
1034      "collapsed": false,
1035      "input": [
1036       "df = pd.read_sql(\"SELECT gender, \\\n",
1037       "                         COUNT(*) AS number_of_patients, \\\n",
1038       "                         COUNT(DISTINCT patient_name) AS number_of_distinct_names, \\\n",
1039       "                         COUNT(weight) AS number_weighed, \\\n",
1040       "                         MIN(weight) AS minimum_weight, \\\n",
1041       "                         MAX(weight) AS maximum_weight, \\\n",
1042       "                         CAST(AVG(weight) AS DECIMAL(4,1)) AS average_weight \\\n",
1043       "                  FROM patient \\\n",
1044       "                  GROUP BY gender\", conn)\n",
1045       "df"
1046      ],
1047      "language": "python",
1048      "metadata": {},
1049      "outputs": [
1050       {
1051        "html": [
1052         "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
1053         "<table border=\"1\" class=\"dataframe\">\n",
1054         "  <thead>\n",
1055         "    <tr style=\"text-align: right;\">\n",
1056         "      <th></th>\n",
1057         "      <th>gender</th>\n",
1058         "      <th>number_of_patients</th>\n",
1059         "      <th>number_of_distinct_names</th>\n",
1060         "      <th>number_weighed</th>\n",
1061         "      <th>minimum_weight</th>\n",
1062         "      <th>maximum_weight</th>\n",
1063         "      <th>average_weight</th>\n",
1064         "    </tr>\n",
1065         "  </thead>\n",
1066         "  <tbody>\n",
1067         "    <tr>\n",
1068         "      <th>0</th>\n",
1069         "      <td> F</td>\n",
1070         "      <td> 10</td>\n",
1071         "      <td> 10</td>\n",
1072         "      <td> 8</td>\n",
1073         "      <td> 49.2</td>\n",
1074         "      <td> 73.0</td>\n",
1075         "      <td> 59.8</td>\n",
1076         "    </tr>\n",
1077         "    <tr>\n",
1078         "      <th>1</th>\n",
1079         "      <td> M</td>\n",
1080         "      <td>  7</td>\n",
1081         "      <td>  6</td>\n",
1082         "      <td> 7</td>\n",
1083         "      <td> 64.3</td>\n",
1084         "      <td> 91.4</td>\n",
1085         "      <td> 76.2</td>\n",
1086         "    </tr>\n",
1087         "  </tbody>\n",
1088         "</table>\n",
1089         "</div>"
1090        ],
1091        "metadata": {},
1092        "output_type": "pyout",
1093        "prompt_number": 13,
1094        "text": [
1095         "  gender  number_of_patients  number_of_distinct_names  number_weighed  \\\n",
1096         "0      F                  10                        10               8   \n",
1097         "1      M                   7                         6               7   \n",
1098         "\n",
1099         "   minimum_weight  maximum_weight  average_weight  \n",
1100         "0            49.2            73.0            59.8  \n",
1101         "1            64.3            91.4            76.2  "
1102        ]
1103       }
1104      ],
1105      "prompt_number": 13
1106     },
1107     {
1108      "cell_type": "markdown",
1109      "metadata": {},
1110      "source": [
1111       " "
1112      ]
1113     },
1114     {
1115      "cell_type": "markdown",
1116      "metadata": {},
1117      "source": [
1118       "\"Display separate summary statistics about patients by their year of birth\":"
1119      ]
1120     },
1121     {
1122      "cell_type": "code",
1123      "collapsed": false,
1124      "input": [
1125       "df = pd.read_sql(\"SELECT EXTRACT(YEAR FROM date_of_birth) AS birth_year, \\\n",
1126       "                         COUNT(*) AS number_of_patients, \\\n",
1127       "                         COUNT(DISTINCT patient_name) AS number_of_distinct_names, \\\n",
1128       "                         COUNT(weight) AS number_weighed, \\\n",
1129       "                         MIN(weight) AS minimum_weight, \\\n",
1130       "                         MAX(weight) AS maximum_weight, \\\n",
1131       "                         CAST(AVG(weight) AS DECIMAL(4,1)) AS average_weight \\\n",
1132       "                  FROM patient \\\n",
1133       "                  GROUP BY EXTRACT(YEAR FROM date_of_birth) \\\n",
1134       "                  ORDER BY EXTRACT(YEAR FROM date_of_birth)\", conn)\n",
1135       "df"
1136      ],
1137      "language": "python",
1138      "metadata": {},
1139      "outputs": [
1140       {
1141        "html": [
1142         "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
1143         "<table border=\"1\" class=\"dataframe\">\n",
1144         "  <thead>\n",
1145         "    <tr style=\"text-align: right;\">\n",
1146         "      <th></th>\n",
1147         "      <th>birth_year</th>\n",
1148         "      <th>number_of_patients</th>\n",
1149         "      <th>number_of_distinct_names</th>\n",
1150         "      <th>number_weighed</th>\n",
1151         "      <th>minimum_weight</th>\n",
1152         "      <th>maximum_weight</th>\n",
1153         "      <th>average_weight</th>\n",
1154         "    </tr>\n",
1155         "  </thead>\n",
1156         "  <tbody>\n",
1157         "    <tr>\n",
1158         "      <th>0</th>\n",
1159         "      <td> 1980</td>\n",
1160         "      <td> 6</td>\n",
1161         "      <td> 6</td>\n",
1162         "      <td> 5</td>\n",
1163         "      <td> 53.2</td>\n",
1164         "      <td> 71.6</td>\n",
1165         "      <td> 66.1</td>\n",
1166         "    </tr>\n",
1167         "    <tr>\n",
1168         "      <th>1</th>\n",
1169         "      <td> 1981</td>\n",
1170         "      <td> 5</td>\n",
1171         "      <td> 5</td>\n",
1172         "      <td> 4</td>\n",
1173         "      <td> 62.6</td>\n",
1174         "      <td> 85.4</td>\n",
1175         "      <td> 74.4</td>\n",
1176         "    </tr>\n",
1177         "    <tr>\n",
1178         "      <th>2</th>\n",
1179         "      <td> 1982</td>\n",
1180         "      <td> 6</td>\n",
1181         "      <td> 6</td>\n",
1182         "      <td> 6</td>\n",
1183         "      <td> 49.2</td>\n",
1184         "      <td> 91.4</td>\n",
1185         "      <td> 64.0</td>\n",
1186         "    </tr>\n",
1187         "  </tbody>\n",
1188         "</table>\n",
1189         "</div>"
1190        ],
1191        "metadata": {},
1192        "output_type": "pyout",
1193        "prompt_number": 14,
1194        "text": [
1195         "   birth_year  number_of_patients  number_of_distinct_names  number_weighed  \\\n",
1196         "0        1980                   6                         6               5   \n",
1197         "1        1981                   5                         5               4   \n",
1198         "2        1982                   6                         6               6   \n",
1199         "\n",
1200         "   minimum_weight  maximum_weight  average_weight  \n",
1201         "0            53.2            71.6            66.1  \n",
1202         "1            62.6            85.4            74.4  \n",
1203         "2            49.2            91.4            64.0  "
1204        ]
1205       }
1206      ],
1207      "prompt_number": 14
1208     },
1209     {
1210      "cell_type": "markdown",
1211      "metadata": {},
1212      "source": [
1213       " "
1214      ]
1215     },
1216     {
1217      "cell_type": "markdown",
1218      "metadata": {},
1219      "source": [
1220       "\"Display separate summary statistics about patients by their year of birth where at least 5 patients have had their weight recorded\":"
1221      ]
1222     },
1223     {
1224      "cell_type": "code",
1225      "collapsed": false,
1226      "input": [
1227       "df = pd.read_sql(\"SELECT EXTRACT(YEAR FROM date_of_birth) AS birth_year, \\\n",
1228       "                         COUNT(*) AS number_of_patients, \\\n",
1229       "                         COUNT(DISTINCT patient_name) AS number_of_distinct_names, \\\n",
1230       "                         COUNT(weight) AS number_weighed, \\\n",
1231       "                         MIN(weight) AS minimum_weight, \\\n",
1232       "                         MAX(weight) AS maximum_weight, \\\n",
1233       "                         CAST(AVG(weight) AS DECIMAL(4,1)) AS average_weight \\\n",
1234       "                  FROM patient \\\n",
1235       "                  GROUP BY EXTRACT(YEAR FROM date_of_birth) \\\n",
1236       "                  HAVING COUNT(weight) >= 5 \\\n",
1237       "                  ORDER BY EXTRACT(YEAR FROM date_of_birth)\", conn)\n",
1238       "df"
1239      ],
1240      "language": "python",
1241      "metadata": {},
1242      "outputs": [
1243       {
1244        "html": [
1245         "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
1246         "<table border=\"1\" class=\"dataframe\">\n",
1247         "  <thead>\n",
1248         "    <tr style=\"text-align: right;\">\n",
1249         "      <th></th>\n",
1250         "      <th>birth_year</th>\n",
1251         "      <th>number_of_patients</th>\n",
1252         "      <th>number_of_distinct_names</th>\n",
1253         "      <th>number_weighed</th>\n",
1254         "      <th>minimum_weight</th>\n",
1255         "      <th>maximum_weight</th>\n",
1256         "      <th>average_weight</th>\n",
1257         "    </tr>\n",
1258         "  </thead>\n",
1259         "  <tbody>\n",
1260         "    <tr>\n",
1261         "      <th>0</th>\n",
1262         "      <td> 1980</td>\n",
1263         "      <td> 6</td>\n",
1264         "      <td> 6</td>\n",
1265         "      <td> 5</td>\n",
1266         "      <td> 53.2</td>\n",
1267         "      <td> 71.6</td>\n",
1268         "      <td> 66.1</td>\n",
1269         "    </tr>\n",
1270         "    <tr>\n",
1271         "      <th>1</th>\n",
1272         "      <td> 1982</td>\n",
1273         "      <td> 6</td>\n",
1274         "      <td> 6</td>\n",
1275         "      <td> 6</td>\n",
1276         "      <td> 49.2</td>\n",
1277         "      <td> 91.4</td>\n",
1278         "      <td> 64.0</td>\n",
1279         "    </tr>\n",
1280         "  </tbody>\n",
1281         "</table>\n",
1282         "</div>"
1283        ],
1284        "metadata": {},
1285        "output_type": "pyout",
1286        "prompt_number": 15,
1287        "text": [
1288         "   birth_year  number_of_patients  number_of_distinct_names  number_weighed  \\\n",
1289         "0        1980                   6                         6               5   \n",
1290         "1        1982                   6                         6               6   \n",
1291         "\n",
1292         "   minimum_weight  maximum_weight  average_weight  \n",
1293         "0            53.2            71.6            66.1  \n",
1294         "1            49.2            91.4            64.0  "
1295        ]
1296       }
1297      ],
1298      "prompt_number": 15
1299     },
1300     {
1301      "cell_type": "markdown",
1302      "metadata": {},
1303      "source": [
1304       " \n",
1305       "    "
1306      ]
1307     },
1308     {
1309      "cell_type": "markdown",
1310      "metadata": {},
1311      "source": [
1312       " "
1313      ]
1314     },
1315     {
1316      "cell_type": "markdown",
1317      "metadata": {},
1318      "source": [
1319       " "
1320      ]
1321     },
1322     {
1323      "cell_type": "heading",
1324      "level": 3,
1325      "metadata": {},
1326      "source": [
1327       "Exercises"
1328      ]
1329     },
1330     {
1331      "cell_type": "markdown",
1332      "metadata": {},
1333      "source": [
1334       "1. Execute the SQL statements given in the solution to Exercise 7 to register the details of the new female patient and then record her height and weight. Write and execute an SQL SELECT statement to check that the data has been added correctly (don\u2019t just simply list the contents of the whole table). Write and execute an SQL statement to delete this patient from the database.\n",
1335       "\n",
1336       "2. Try adding or updating patient data that will violate the constraints defined on the *patient* table - primary key, NOT NULL, domain and CHECK constraints (see Exercise 6).\n",
1337       "\n",
1338       "3. Write and execute SQL SELECT statements to answer the following questions about the patient data:\n",
1339       "    \n",
1340       "    (i) Display the details of female patients who were born before 1981.\n",
1341       "    \n",
1342       "    (ii) For patients who have the same name, display those names.\n",
1343       "    \n",
1344       "    (iii) Display the minimum and maximum Body Mass Index (see http://en.wikipedia.org/wiki/Body_mass_index). Write a query to determine how many patients are overweight?"
1345      ]
1346     }
1347    ],
1348    "metadata": {}
1349   }
1350  ]
1351 }