04-calc.ipynb

   1 {
   2  "metadata": {
   3   "name": ""
   4  },
   5  "nbformat": 3,
   6  "nbformat_minor": 0,
   7  "worksheets": [
   8   {
   9    "cells": [
  10     {
  11      "cell_type": "heading",
  12      "level": 2,
  13      "metadata": {},
  14      "source": [
  15       "Calculating New Values"
  16      ]
  17     },
  18     {
  19      "cell_type": "markdown",
  20      "metadata": {
  21       "cell_tags": [
  22        "objectives"
  23       ]
  24      },
  25      "source": [
  26       "#### Objectives\n",
  27       "\n",
  28       "*   Write queries that calculate new values for each selected record."
  29      ]
  30     },
  31     {
  32      "cell_type": "markdown",
  33      "metadata": {},
  34      "source": [
  35       "After carefully re-reading the expedition logs,\n",
  36       "we realize that the radiation measurements they report\n",
  37       "may need to be corrected upward by 5%.\n",
  38       "Rather than modifying the stored data,\n",
  39       "we can do this calculation on the fly\n",
  40       "as part of our query:"
  41      ]
  42     },
  43     {
  44      "cell_type": "code",
  45      "collapsed": false,
  46      "input": [
  47       "%load_ext sqlitemagic"
  48      ],
  49      "language": "python",
  50      "metadata": {},
  51      "outputs": [],
  52      "prompt_number": 1
  53     },
  54     {
  55      "cell_type": "code",
  56      "collapsed": false,
  57      "input": [
  58       "%%sqlite survey.db\n",
  59       "select 1.05 * reading from Survey where quant='rad';"
  60      ],
  61      "language": "python",
  62      "metadata": {},
  63      "outputs": [
  64       {
  65        "html": [
  66         "<table>\n",
  67         "<tr><td>10.311</td></tr>\n",
  68         "<tr><td>8.19</td></tr>\n",
  69         "<tr><td>8.8305</td></tr>\n",
  70         "<tr><td>7.581</td></tr>\n",
  71         "<tr><td>4.5675</td></tr>\n",
  72         "<tr><td>2.2995</td></tr>\n",
  73         "<tr><td>1.533</td></tr>\n",
  74         "<tr><td>11.8125</td></tr>\n",
  75         "</table>"
  76        ],
  77        "metadata": {},
  78        "output_type": "display_data",
  79        "text": [
  80         "<IPython.core.display.HTML at 0x1023c4e50>"
  81        ]
  82       }
  83      ],
  84      "prompt_number": 2
  85     },
  86     {
  87      "cell_type": "markdown",
  88      "metadata": {},
  89      "source": [
  90       "When we run the query,\n",
  91       "the expression `1.05 * reading` is evaluated for each row.\n",
  92       "Expressions can use any of the fields,\n",
  93       "all of usual arithmetic operators,\n",
  94       "and a variety of common functions.\n",
  95       "(Exactly which ones depends on which database manager is being used.)\n",
  96       "For example,\n",
  97       "we can convert temperature readings from Fahrenheit to Celsius\n",
  98       "and round to two decimal places:"
  99      ]
 100     },
 101     {
 102      "cell_type": "code",
 103      "collapsed": false,
 104      "input": [
 105       "%%sqlite survey.db\n",
 106       "select taken, round(5*(reading-32)/9, 2) from Survey where quant='temp';"
 107      ],
 108      "language": "python",
 109      "metadata": {},
 110      "outputs": [
 111       {
 112        "html": [
 113         "<table>\n",
 114         "<tr><td>734</td><td>-29.72</td></tr>\n",
 115         "<tr><td>735</td><td>-32.22</td></tr>\n",
 116         "<tr><td>751</td><td>-28.06</td></tr>\n",
 117         "<tr><td>752</td><td>-26.67</td></tr>\n",
 118         "</table>"
 119        ],
 120        "metadata": {},
 121        "output_type": "display_data",
 122        "text": [
 123         "<IPython.core.display.HTML at 0x1023cbd90>"
 124        ]
 125       }
 126      ],
 127      "prompt_number": 4
 128     },
 129     {
 130      "cell_type": "markdown",
 131      "metadata": {},
 132      "source": [
 133       "We can also combine values from different fields,\n",
 134       "for example by using the string concatenation operator `||`:"
 135      ]
 136     },
 137     {
 138      "cell_type": "code",
 139      "collapsed": false,
 140      "input": [
 141       "%%sqlite survey.db\n",
 142       "select personal || ' ' || family from Person;"
 143      ],
 144      "language": "python",
 145      "metadata": {},
 146      "outputs": [
 147       {
 148        "html": [
 149         "<table>\n",
 150         "<tr><td>William Dyer</td></tr>\n",
 151         "<tr><td>Frank Pabodie</td></tr>\n",
 152         "<tr><td>Anderson Lake</td></tr>\n",
 153         "<tr><td>Valentina Roerich</td></tr>\n",
 154         "<tr><td>Frank Danforth</td></tr>\n",
 155         "</table>"
 156        ],
 157        "metadata": {},
 158        "output_type": "display_data",
 159        "text": [
 160         "<IPython.core.display.HTML at 0x1023c46d0>"
 161        ]
 162       }
 163      ],
 164      "prompt_number": 5
 165     },
 166     {
 167      "cell_type": "markdown",
 168      "metadata": {},
 169      "source": [
 170       "> It may seem strange to use `personal` and `family` as field names\n",
 171       "> instead of `first` and `last`,\n",
 172       "> but it's a necessary first step toward handling cultural differences.\n",
 173       "> For example,\n",
 174       "> consider the following rules:\n",
 175       "\n",
 176       "<table>\n",
 177       "  <tr> <th>Full Name</th> <th>Alphabetized Under</th> <th>Reason</th> </tr>\n",
 178       "  <tr> <td>Liu Xiaobo</td> <td>Liu</td> <td>Chinese family names come first</td> </tr>\n",
 179       "  <tr> <td> Leonardo da Vinci</td> <td>Leonardo</td> <td>\"da Vinci\" just means \"from Vinci\"</td> </tr>\n",
 180       "  <tr> <td> Catherine de Medici</td> <td>Medici</td> <td>family name</td> </tr>\n",
 181       "  <tr> <td> Jean de La Fontaine</td> <td>La Fontaine</td> <td>family name is \"La Fontaine\"</td> </tr>\n",
 182       "  <tr> <td> Juan Ponce de Leon</td> <td>Ponce de Leon</td> <td>full family name is \"Ponce de Leon\"</td> </tr>\n",
 183       "  <tr> <td> Gabriel Garcia Marquez</td> <td>Garcia Marquez</td> <td>double-barrelled Spanish surnames</td> </tr>\n",
 184       "  <tr> <td> Wernher von Braun</td> <td>von <em>or</em> Braun</td> <td>depending on whether he was in Germany or the US</td> </tr>\n",
 185       "  <tr> <td> Elizabeth Alexandra May Windsor</td> <td>Elizabeth</td> <td>monarchs alphabetize by the name under which they reigned</td> </tr>\n",
 186       "  <tr> <td> Thomas a Beckett</td> <td>Thomas</td> <td>and saints according to the names by which they were canonized</td> </tr>\n",
 187       "</table>\n",
 188       "\n",
 189       "> Clearly,\n",
 190       "> even a two-part division into \"personal\" and \"family\"\n",
 191       "> isn't enough..."
 192      ]
 193     },
 194     {
 195      "cell_type": "markdown",
 196      "metadata": {},
 197      "source": [
 198       "#### Challenges\n",
 199       "\n",
 200       "1.  After further reading,\n",
 201       "    we realize that Valentina Roerich\n",
 202       "    was reporting salinity as percentages.\n",
 203       "    Write a query that returns all of her salinity measurements\n",
 204       "    from the `Survey` table\n",
 205       "    with the values divided by 100.\n",
 206       "\n",
 207       "2.  The `union` operator combines the results of two queries:"
 208      ]
 209     },
 210     {
 211      "cell_type": "code",
 212      "collapsed": false,
 213      "input": [
 214       "%%sqlite survey.db\n",
 215       "select * from Person where ident='dyer' union select * from Person where ident='roe';"
 216      ],
 217      "language": "python",
 218      "metadata": {},
 219      "outputs": [
 220       {
 221        "html": [
 222         "<table>\n",
 223         "<tr><td>dyer</td><td>William</td><td>Dyer</td></tr>\n",
 224         "<tr><td>roe</td><td>Valentina</td><td>Roerich</td></tr>\n",
 225         "</table>"
 226        ],
 227        "metadata": {},
 228        "output_type": "display_data",
 229        "text": [
 230         "<IPython.core.display.HTML at 0x1023cbd50>"
 231        ]
 232       }
 233      ],
 234      "prompt_number": 6
 235     },
 236     {
 237      "cell_type": "markdown",
 238      "metadata": {},
 239      "source": [
 240       "Use `union` to create a consolidated list of salinity measurements\n",
 241       "in which Roerich's, and only Roerich's,\n",
 242       "have been corrected as described in the previous challenge.\n",
 243       "The output should be something like:\n",
 244       "\n",
 245       "<table>\n",
 246       "  <tr> <td>619</td> <td>0.13</td> </tr>\n",
 247       "  <tr> <td>622</td> <td>0.09</td> </tr>\n",
 248       "  <tr> <td>734</td> <td>0.05</td> </tr>\n",
 249       "  <tr> <td>751</td> <td>0.1</td> </tr>\n",
 250       "  <tr> <td>752</td> <td>0.09</td> </tr>\n",
 251       "  <tr> <td>752</td> <td>0.416</td> </tr>\n",
 252       "  <tr> <td>837</td> <td>0.21</td> </tr>\n",
 253       "  <tr> <td>837</td> <td>0.225</td> </tr>\n",
 254       "</table>\n"
 255      ]
 256     },
 257     {
 258      "cell_type": "markdown",
 259      "metadata": {},
 260      "source": [
 261       "3.  The site identifiers in the `Visited` table have two parts\n",
 262       "    separated by a '-':"
 263      ]
 264     },
 265     {
 266      "cell_type": "code",
 267      "collapsed": false,
 268      "input": [
 269       "%%sqlite survey.db\n",
 270       "select distinct site from Visited;"
 271      ],
 272      "language": "python",
 273      "metadata": {},
 274      "outputs": [
 275       {
 276        "html": [
 277         "<table>\n",
 278         "<tr><td>DR-1</td></tr>\n",
 279         "<tr><td>DR-3</td></tr>\n",
 280         "<tr><td>MSK-4</td></tr>\n",
 281         "</table>"
 282        ],
 283        "metadata": {},
 284        "output_type": "display_data",
 285        "text": [
 286         "<IPython.core.display.HTML at 0x1023c4e50>"
 287        ]
 288       }
 289      ],
 290      "prompt_number": 7
 291     },
 292     {
 293      "cell_type": "markdown",
 294      "metadata": {},
 295      "source": [
 296       "Some major site identifiers are two letters long and some are three.\n",
 297       "The \"in string\" function `instr(X, Y)`\n",
 298       "returns the 1-based index of the first occurrence of string Y in string X,\n",
 299       "or 0 if Y does not exist in X.\n",
 300       "The substring function `substr(X, I)`\n",
 301       "returns the substring of X starting at index I.\n",
 302       "Use these two functions to produce a list of unique major site identifiers.\n",
 303       "(For this data,\n",
 304       "the list should contain only \"DR\" and \"MSK\")."
 305      ]
 306     },
 307     {
 308      "cell_type": "markdown",
 309      "metadata": {
 310       "cell_tags": [
 311        "keypoints"
 312       ]
 313      },
 314      "source": [
 315       "#### Key Points\n",
 316       "\n",
 317       "*   SQL can perform calculations using the values in a record as part of a query."
 318      ]
 319     }
 320    ],
 321    "metadata": {}
 322   }
 323  ]
 324 }