notebooks/m269_sql_notebook/.ipynb_checkpoints/M269 - Python - Blue-checkpoint.ipynb

   1 {
   2  "metadata": {
   3   "name": "",
   4   "signature": "sha256:590cccce2df09fbafb9a1cc2f48b964a448a3e04c81dcff0b057029077ed77c3"
   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       "M269 Recap - Python"
  17      ]
  18     },
  19     {
  20      "cell_type": "markdown",
  21      "metadata": {},
  22      "source": [
  23       "If you have completed M269 (a prerequisite course for TM351), you should already be familiar with the idea of SQL, the widely used query language for querying relational databases.\n",
  24       "\n",
  25       "You will have an opportunity to learn about SQL in far more depth throughout TM351, but for now, let's quickly recap on the exercises from M269."
  26      ]
  27     },
  28     {
  29      "cell_type": "code",
  30      "collapsed": false,
  31      "input": [
  32       "from m269_db import *"
  33      ],
  34      "language": "python",
  35      "metadata": {
  36       "activity": false
  37      },
  38      "outputs": [],
  39      "prompt_number": 11
  40     },
  41     {
  42      "cell_type": "markdown",
  43      "metadata": {},
  44      "source": [
  45       "One of the tables in the M269 database was called `student`."
  46      ]
  47     },
  48     {
  49      "cell_type": "code",
  50      "collapsed": false,
  51      "input": [
  52       "#We can examine the contents of the student table \n",
  53       "#by selecting all the data contained in that table in the database\n",
  54       "q='''SELECT * FROM student;'''\n",
  55       "\n",
  56       "show(q)"
  57      ],
  58      "language": "python",
  59      "metadata": {
  60       "activity": false
  61      },
  62      "outputs": [
  63       {
  64        "ename": "NameError",
  65        "evalue": "global name 'm269_database' is not defined",
  66        "output_type": "pyerr",
  67        "traceback": [
  68         "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m\n\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
  69         "\u001b[0;32m<ipython-input-12-fbbf4f69b75b>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      3\u001b[0m \u001b[0mq\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m'''SELECT * FROM student;'''\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      4\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 5\u001b[0;31m \u001b[0mshow\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mq\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
  70         "\u001b[0;32m/Users/ajh59/tmp/m269_sql_notebook/m269_db.py\u001b[0m in \u001b[0;36mshow\u001b[0;34m(q)\u001b[0m\n\u001b[1;32m     21\u001b[0m     \u001b[0;34m\"\"\"Applies an sql query to the M269 database and prints the result\"\"\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     22\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 23\u001b[0;31m     \u001b[0mconnection\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0msqlite3\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mconnect\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mm269_database\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     24\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     25\u001b[0m     \u001b[0mcursor\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mconnection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcursor\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
  71         "\u001b[0;31mNameError\u001b[0m: global name 'm269_database' is not defined"
  72        ]
  73       }
  74      ],
  75      "prompt_number": 12
  76     },
  77     {
  78      "cell_type": "code",
  79      "collapsed": false,
  80      "input": [
  81       "#We can use the column names to restrict which columns of the table we want to look at.\n",
  82       "q='''\n",
  83       "SELECT student_id, location\n",
  84       "FROM student;\n",
  85       "'''\n",
  86       "show(q)"
  87      ],
  88      "language": "python",
  89      "metadata": {
  90       "activity": false
  91      },
  92      "outputs": [
  93       {
  94        "output_type": "stream",
  95        "stream": "stdout",
  96        "text": [
  97         " student_id | location   \n",
  98         "------------|------------\n",
  99         " X042       | Manchester \n",
 100         " X019       | Bristol    \n",
 101         " X020       | Manchester \n",
 102         " X048       | Bath       \n",
 103         " X018       | London     \n",
 104         " X043       | London     \n",
 105         " X025       | York       \n",
 106         " X002       | Manchester \n",
 107         " X023       | Bristol    \n",
 108         " X008       | London     \n"
 109        ]
 110       }
 111      ],
 112      "prompt_number": 23
 113     },
 114     {
 115      "cell_type": "code",
 116      "collapsed": false,
 117      "input": [
 118       "#Write an SQL query which will return the columns student_name and module from the student table.\n",
 119       "q='''\n",
 120       "\n",
 121       "'''\n",
 122       "show(q)"
 123      ],
 124      "language": "python",
 125      "metadata": {
 126       "activity": true
 127      },
 128      "outputs": []
 129     },
 130     {
 131      "cell_type": "code",
 132      "collapsed": false,
 133      "input": [
 134       "#Choose particular rows of a table using conditions.\n",
 135       "#The returned table should contain all the columns in the student table if we use SELECT *.\n",
 136       "q='''\n",
 137       "SELECT * FROM student WHERE location='Manchester';\n",
 138       "'''\n",
 139       "\n",
 140       "show(q)"
 141      ],
 142      "language": "python",
 143      "metadata": {
 144       "activity": false
 145      },
 146      "outputs": [
 147       {
 148        "output_type": "stream",
 149        "stream": "stdout",
 150        "text": [
 151         " student_id | student_name | location   | module \n",
 152         "------------|--------------|------------|--------\n",
 153         " X042       | Yves         | Manchester | M381   \n",
 154         " X020       | Rory         | Manchester | T304   \n",
 155         " X002       | Mike         | Manchester | M218   \n"
 156        ]
 157       }
 158      ],
 159      "prompt_number": 21
 160     },
 161     {
 162      "cell_type": "code",
 163      "collapsed": false,
 164      "input": [
 165       "#Conditions in the WHERE clause can also be combined with the logical operators AND, OR and NOT.\n",
 166       "#So if we wanted to list those rows which contained those students who live\n",
 167       "# in Manchester or who are studying module T304, we could use the query:\n",
 168       "q='''\n",
 169       "SELECT *\n",
 170       "FROM student\n",
 171       "WHERE location='Manchester' OR module='T304';\n",
 172       "'''\n",
 173       "\n",
 174       "show(q)"
 175      ],
 176      "language": "python",
 177      "metadata": {
 178       "activity": false
 179      },
 180      "outputs": [
 181       {
 182        "output_type": "stream",
 183        "stream": "stdout",
 184        "text": [
 185         " student_id | student_name | location   | module \n",
 186         "------------|--------------|------------|--------\n",
 187         " X042       | Yves         | Manchester | M381   \n",
 188         " X019       | Sara         | Bristol    | T304   \n",
 189         " X020       | Rory         | Manchester | T304   \n",
 190         " X043       | Willow       | London     | T304   \n",
 191         " X002       | Mike         | Manchester | M218   \n"
 192        ]
 193       }
 194      ],
 195      "prompt_number": 22
 196     },
 197     {
 198      "cell_type": "code",
 199      "collapsed": false,
 200      "input": [
 201       "#Restrict the returned columns by making the required columns explicit in the SELECT clause\n",
 202       "\n",
 203       "q='''\n",
 204       "SELECT student_id, student_name\n",
 205       "FROM student\n",
 206       "WHERE location='Manchester' OR module='T304';\n",
 207       "'''\n",
 208       "\n",
 209       "show(q)"
 210      ],
 211      "language": "python",
 212      "metadata": {
 213       "activity": false
 214      },
 215      "outputs": [
 216       {
 217        "output_type": "stream",
 218        "stream": "stdout",
 219        "text": [
 220         " student_id | student_name \n",
 221         "------------|--------------\n",
 222         " X042       | Yves         \n",
 223         " X019       | Sara         \n",
 224         " X020       | Rory         \n",
 225         " X043       | Willow       \n",
 226         " X002       | Mike         \n"
 227        ]
 228       }
 229      ],
 230      "prompt_number": 25
 231     },
 232     {
 233      "cell_type": "code",
 234      "collapsed": false,
 235      "input": [
 236       "#What are the student identification codes and the names of those students who live in Manchester?\n",
 237       "\n",
 238       "q='''\n",
 239       "\n",
 240       "'''\n",
 241       "show(q)"
 242      ],
 243      "language": "python",
 244      "metadata": {
 245       "activity": true
 246      },
 247      "outputs": []
 248     },
 249     {
 250      "cell_type": "code",
 251      "collapsed": false,
 252      "input": [
 253       "#What are the student identifiers of all the students named Mike?\n",
 254       "q='''\n",
 255       "SELECT student_id\n",
 256       "FROM student\n",
 257       "WHERE student_name='Mike';\n",
 258       "'''\n",
 259       "show(q)"
 260      ],
 261      "language": "python",
 262      "metadata": {
 263       "activity": true
 264      },
 265      "outputs": [
 266       {
 267        "ename": "NameError",
 268        "evalue": "name 'show' is not defined",
 269        "output_type": "pyerr",
 270        "traceback": [
 271         "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m\n\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
 272         "\u001b[0;32m<ipython-input-4-dd13548a0686>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      5\u001b[0m \u001b[0mWHERE\u001b[0m \u001b[0mstudent_name\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m'Mike'\u001b[0m\u001b[0;34m;\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      6\u001b[0m '''\n\u001b[0;32m----> 7\u001b[0;31m \u001b[0mshow\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mq\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
 273         "\u001b[0;31mNameError\u001b[0m: name 'show' is not defined"
 274        ]
 275       }
 276      ],
 277      "prompt_number": 4
 278     },
 279     {
 280      "cell_type": "code",
 281      "collapsed": false,
 282      "input": [
 283       "#In which towns are there students studying the module T353 or module T238?\n",
 284       "q='''\n",
 285       "\n",
 286       "'''\n",
 287       "show(q)"
 288      ],
 289      "language": "python",
 290      "metadata": {
 291       "activity": true
 292      },
 293      "outputs": []
 294     },
 295     {
 296      "cell_type": "markdown",
 297      "metadata": {},
 298      "source": [
 299       "The M269 database, m269_db, contains two further tables  - `country` and `language` - which contain much more information than the student table we have looked at so far."
 300      ]
 301     },
 302     {
 303      "cell_type": "code",
 304      "collapsed": false,
 305      "input": [
 306       "#Examine the contents of the country table\n",
 307       "q='''\n",
 308       "\n",
 309       "'''\n",
 310       "show(q)"
 311      ],
 312      "language": "python",
 313      "metadata": {
 314       "activity": true
 315      },
 316      "outputs": []
 317     },
 318     {
 319      "cell_type": "markdown",
 320      "metadata": {
 321       "activity": false,
 322       "activityM": true
 323      },
 324      "source": [
 325       "sadas"
 326      ]
 327     },
 328     {
 329      "cell_type": "code",
 330      "collapsed": false,
 331      "input": [
 332       "#Examine the contents of the language table\n",
 333       "q='''\n",
 334       "\n",
 335       "'''\n",
 336       "show(q)"
 337      ],
 338      "language": "python",
 339      "metadata": {
 340       "activity": true
 341      },
 342      "outputs": []
 343     },
 344     {
 345      "cell_type": "markdown",
 346      "metadata": {},
 347      "source": [
 348       "When we define SQL queries, we typically do so in order to answer a particular question. In many cases, the question to be asked will initially be formulated in natural language terms before being refined and developed as an SQL query. We can also go the other way, expressing SQL queries in more naturalistic terms."
 349      ]
 350     },
 351     {
 352      "cell_type": "markdown",
 353      "metadata": {},
 354      "source": [
 355       "<div class=\"activity\">Activity - Interpreting SQL Queries as Natural Language Questions</div>\n",
 356       "\n",
 357       "Give an equivalent English question that each of the following queries might have been used to answer:\n",
 358       "\n",
 359       "&nbsp;&nbsp;SELECT *\n",
 360       "FROM country\n",
 361       "WHERE capital_city='Helsinki';"
 362      ]
 363     },
 364     {
 365      "cell_type": "markdown",
 366      "metadata": {
 367       "activity": "activityAns"
 368      },
 369      "source": [
 370       "YOUR ANSWER HERE"
 371      ]
 372     },
 373     {
 374      "cell_type": "markdown",
 375      "metadata": {
 376       "activity": "activity"
 377      },
 378      "source": [
 379       "SELECT country_iso\n",
 380       "FROM language\n",
 381       "WHERE language='Portuguese';"
 382      ]
 383     },
 384     {
 385      "cell_type": "markdown",
 386      "metadata": {
 387       "activity": "activityAns"
 388      },
 389      "source": [
 390       "YOUR ANSWER HERE"
 391      ]
 392     },
 393     {
 394      "cell_type": "markdown",
 395      "metadata": {
 396       "activity": "activity"
 397      },
 398      "source": [
 399       "SELECT country_name\n",
 400       "FROM country\n",
 401       "WHERE has_capital=0 AND continent='Asia';"
 402      ]
 403     },
 404     {
 405      "cell_type": "markdown",
 406      "metadata": {
 407       "activity": "activityAns"
 408      },
 409      "source": [
 410       "YOUR ANSWER HERE"
 411      ]
 412     },
 413     {
 414      "cell_type": "markdown",
 415      "metadata": {},
 416      "source": [
 417       "<div class=\"answer\" id=\"ans1\" style=\"display: none\">\n",
 418       "Some possible ways of expressing the queries as English questions are:\n",
 419       "\n",
 420       "<ol type='a'><li>List all the information about the countries whose capital is Helsinki.</li>\n",
 421       "<li>List the ISO codes of every country which has Portuguese as an official language.</li>\n",
 422       "<li>List the name of every country in Asia which does not have a capital city.</li></ol></div>"
 423      ]
 424     },
 425     {
 426      "cell_type": "markdown",
 427      "metadata": {},
 428      "source": [
 429       "Now try answering a few more questions by making your own queries on to the database:"
 430      ]
 431     },
 432     {
 433      "cell_type": "code",
 434      "collapsed": false,
 435      "input": [
 436       "#What is the capital of Chad?\n"
 437      ],
 438      "language": "python",
 439      "metadata": {
 440       "activity": true
 441      },
 442      "outputs": []
 443     },
 444     {
 445      "cell_type": "markdown",
 446      "metadata": {},
 447      "source": [
 448       "<div class=\"answer\"  id='ans2' style=\"display: none\">\n",
 449       "SELECT capital_city\n",
 450       "FROM country\n",
 451       "WHERE country_name='Chad';\n",
 452       "</div>"
 453      ]
 454     },
 455     {
 456      "cell_type": "code",
 457      "collapsed": false,
 458      "input": [
 459       "#List every official language used in the country with the ISO code BOL (Bolivia).\n"
 460      ],
 461      "language": "python",
 462      "metadata": {
 463       "activity": true
 464      },
 465      "outputs": [],
 466      "prompt_number": 41
 467     },
 468     {
 469      "cell_type": "markdown",
 470      "metadata": {},
 471      "source": [
 472       "<div class=\"answer\" id='ans3' style=\"display: none\">\n",
 473       "SELECT language\n",
 474       "FROM language\n",
 475       "WHERE country_iso='BOL';\n",
 476       "</div>"
 477      ]
 478     },
 479     {
 480      "cell_type": "code",
 481      "collapsed": false,
 482      "input": [
 483       "#List the name of every country which is in Europe or North America.\n"
 484      ],
 485      "language": "python",
 486      "metadata": {
 487       "activity": true
 488      },
 489      "outputs": []
 490     },
 491     {
 492      "cell_type": "markdown",
 493      "metadata": {
 494       "activity": "activity"
 495      },
 496      "source": [
 497       "<div class=\"answer\" id='ans4' style=\"display: none\">\n",
 498       "SELECT country_name\n",
 499       "FROM country\n",
 500       "WHERE continent='Europe' OR continent='North America';\n",
 501       "</div>"
 502      ]
 503     },
 504     {
 505      "cell_type": "heading",
 506      "level": 2,
 507      "metadata": {},
 508      "source": [
 509       "Exploring another dataset..."
 510      ]
 511     },
 512     {
 513      "cell_type": "markdown",
 514      "metadata": {
 515       "activity": "clear"
 516      },
 517      "source": [
 518       "The m269_db database contains another dataset made up from the tables `man`, `woman`, `married` and `parent`."
 519      ]
 520     },
 521     {
 522      "cell_type": "code",
 523      "collapsed": false,
 524      "input": [
 525       "#Explore each of the tables to familiarise yourself with the columns and typical row values\n",
 526       "\n",
 527       "\n",
 528       "\n"
 529      ],
 530      "language": "python",
 531      "metadata": {
 532       "activity": true
 533      },
 534      "outputs": [],
 535      "prompt_number": 28
 536     },
 537     {
 538      "cell_type": "code",
 539      "collapsed": false,
 540      "input": [
 541       "q='''\n",
 542       "SELECT *\n",
 543       "FROM person;\n",
 544       "'''\n",
 545       "show(q)"
 546      ],
 547      "language": "python",
 548      "metadata": {
 549       "activity": false
 550      },
 551      "outputs": [
 552       {
 553        "output_type": "stream",
 554        "stream": "stdout",
 555        "text": [
 556         " person_name \n",
 557         "-------------\n",
 558         " Gail        \n",
 559         " Brian       \n",
 560         " Hassan      \n",
 561         " Eve         \n",
 562         " Freddie     \n",
 563         " Jenny       \n",
 564         " Indira      \n",
 565         " Adam        \n",
 566         " Claire      \n",
 567         " Dmitri      \n"
 568        ]
 569       }
 570      ],
 571      "prompt_number": 31
 572     },
 573     {
 574      "cell_type": "code",
 575      "collapsed": false,
 576      "input": [
 577       "q='''\n",
 578       "SELECT *\n",
 579       "FROM town;\n",
 580       "'''\n",
 581       "show(q)"
 582      ],
 583      "language": "python",
 584      "metadata": {
 585       "activity": false
 586      },
 587      "outputs": [
 588       {
 589        "output_type": "stream",
 590        "stream": "stdout",
 591        "text": [
 592         " town_name  \n",
 593         "------------\n",
 594         " Sunderland \n",
 595         " Newcastle  \n",
 596         " Bath       \n",
 597         " York       \n",
 598         " London     \n",
 599         " Manchester \n",
 600         " Norwich    \n",
 601         " Bristol    \n"
 602        ]
 603       }
 604      ],
 605      "prompt_number": 30
 606     },
 607     {
 608      "cell_type": "code",
 609      "collapsed": false,
 610      "input": [
 611       "q='''\n",
 612       "SELECT *\n",
 613       "FROM residence;\n",
 614       "'''\n",
 615       "show(q)"
 616      ],
 617      "language": "python",
 618      "metadata": {
 619       "activity": false
 620      },
 621      "outputs": [
 622       {
 623        "output_type": "stream",
 624        "stream": "stdout",
 625        "text": [
 626         " person_name | lives_in   \n",
 627         "-------------|------------\n",
 628         " Gail        | Manchester \n",
 629         " Freddie     | Bath       \n",
 630         " Indira      | London     \n",
 631         " Jenny       | Sunderland \n",
 632         " Claire      | Bristol    \n",
 633         " Eve         | London     \n",
 634         " Brian       | Manchester \n",
 635         " Adam        | London     \n",
 636         " Hassan      | York       \n",
 637         " Dmitri      | Bristol    \n"
 638        ]
 639       }
 640      ],
 641      "prompt_number": 32
 642     },
 643     {
 644      "cell_type": "markdown",
 645      "metadata": {},
 646      "source": [
 647       "Samples of two of the tables have been gathered into separate tables:"
 648      ]
 649     },
 650     {
 651      "cell_type": "code",
 652      "collapsed": false,
 653      "input": [
 654       "q='''\n",
 655       "SELECT *\n",
 656       "FROM small_student;\n",
 657       "'''\n",
 658       "show(q)"
 659      ],
 660      "language": "python",
 661      "metadata": {
 662       "activity": false
 663      },
 664      "outputs": [
 665       {
 666        "output_type": "stream",
 667        "stream": "stdout",
 668        "text": [
 669         " student_id | student_name \n",
 670         "------------|--------------\n",
 671         " X008       | Mike         \n",
 672         " X018       | Rachel       \n",
 673         " X043       | Simon        \n"
 674        ]
 675       }
 676      ],
 677      "prompt_number": 33
 678     },
 679     {
 680      "cell_type": "code",
 681      "collapsed": false,
 682      "input": [
 683       "q='''\n",
 684       "SELECT *\n",
 685       "FROM small_parent;\n",
 686       "'''\n",
 687       "show(q)"
 688      ],
 689      "language": "python",
 690      "metadata": {
 691       "activity": false
 692      },
 693      "outputs": [
 694       {
 695        "output_type": "stream",
 696        "stream": "stdout",
 697        "text": [
 698         " mother | father | child  \n",
 699         "--------|--------|--------\n",
 700         " Olivia | Mike   | Victor \n",
 701         " Rachel | John   | Willow \n"
 702        ]
 703       }
 704      ],
 705      "prompt_number": 35
 706     },
 707     {
 708      "cell_type": "markdown",
 709      "metadata": {},
 710      "source": [
 711       "Do you remember how we can join tables?"
 712      ]
 713     },
 714     {
 715      "cell_type": "code",
 716      "collapsed": false,
 717      "input": [
 718       "q='''\n",
 719       "SELECT *\n",
 720       "FROM small_student CROSS JOIN small_parent;\n",
 721       "'''\n",
 722       "show(q)"
 723      ],
 724      "language": "python",
 725      "metadata": {
 726       "activity": false
 727      },
 728      "outputs": [
 729       {
 730        "output_type": "stream",
 731        "stream": "stdout",
 732        "text": [
 733         " student_id | student_name | mother | father | child  \n",
 734         "------------|--------------|--------|--------|--------\n",
 735         " X008       | Mike         | Olivia | Mike   | Victor \n",
 736         " X008       | Mike         | Rachel | John   | Willow \n",
 737         " X018       | Rachel       | Olivia | Mike   | Victor \n",
 738         " X018       | Rachel       | Rachel | John   | Willow \n",
 739         " X043       | Simon        | Olivia | Mike   | Victor \n",
 740         " X043       | Simon        | Rachel | John   | Willow \n"
 741        ]
 742       }
 743      ],
 744      "prompt_number": 36
 745     },
 746     {
 747      "cell_type": "code",
 748      "collapsed": false,
 749      "input": [
 750       "#Which students are also fathers?\n",
 751       "#We are only interested in those rows where the value in the student_name column is equal to \n",
 752       "# the value in the father column:\n",
 753       "q='''\n",
 754       "SELECT *\n",
 755       "FROM small_student CROSS JOIN small_parent \n",
 756       "WHERE student_name=father;\n",
 757       "'''\n",
 758       "show(q)"
 759      ],
 760      "language": "python",
 761      "metadata": {
 762       "activity": false
 763      },
 764      "outputs": [
 765       {
 766        "output_type": "stream",
 767        "stream": "stdout",
 768        "text": [
 769         " student_id | student_name | mother | father | child  \n",
 770         "------------|--------------|--------|--------|--------\n",
 771         " X008       | Mike         | Olivia | Mike   | Victor \n"
 772        ]
 773       }
 774      ],
 775      "prompt_number": 38
 776     },
 777     {
 778      "cell_type": "code",
 779      "collapsed": false,
 780      "input": [
 781       "#to answer the question that was actually asked, we might prefer to return only \n",
 782       "# the student\u2019s personal identifier and name\n",
 783       "q='''\n",
 784       "SELECT student_id, student_name\n",
 785       "FROM small_student CROSS JOIN small_parent\n",
 786       "WHERE student_name=father;\n",
 787       "'''\n",
 788       "show(q)"
 789      ],
 790      "language": "python",
 791      "metadata": {
 792       "activity": false
 793      },
 794      "outputs": [
 795       {
 796        "output_type": "stream",
 797        "stream": "stdout",
 798        "text": [
 799         " student_id | student_name \n",
 800         "------------|--------------\n",
 801         " X008       | Mike         \n"
 802        ]
 803       }
 804      ],
 805      "prompt_number": 39
 806     },
 807     {
 808      "cell_type": "code",
 809      "collapsed": false,
 810      "input": [
 811       "#Examine the tables residence and administration\n",
 812       "\n"
 813      ],
 814      "language": "python",
 815      "metadata": {
 816       "activity": true
 817      },
 818      "outputs": []
 819     },
 820     {
 821      "cell_type": "code",
 822      "collapsed": false,
 823      "input": [
 824       "#Examine the effect of CROSS JOINing them under the condition that lives_in=town\n",
 825       "\n"
 826      ],
 827      "language": "python",
 828      "metadata": {
 829       "activity": true
 830      },
 831      "outputs": []
 832     },
 833     {
 834      "cell_type": "code",
 835      "collapsed": false,
 836      "input": [
 837       "#List the names of those people who live in the county of Somerset.\n",
 838       "q='''\n",
 839       "SELECT person_name\n",
 840       "FROM residence CROSS JOIN administration\n",
 841       "WHERE lives_in=town AND county='Somerset';\n",
 842       "'''\n",
 843       "show(q)"
 844      ],
 845      "language": "python",
 846      "metadata": {
 847       "activity": false
 848      },
 849      "outputs": [
 850       {
 851        "output_type": "stream",
 852        "stream": "stdout",
 853        "text": [
 854         " person_name \n",
 855         "-------------\n",
 856         " Freddie     \n",
 857         " Claire      \n",
 858         " Dmitri      \n"
 859        ]
 860       }
 861      ],
 862      "prompt_number": 40
 863     },
 864     {
 865      "cell_type": "code",
 866      "collapsed": false,
 867      "input": [
 868       "#What column or columns are defined over the same objects in the two tables country and language?\n",
 869       "\n"
 870      ],
 871      "language": "python",
 872      "metadata": {
 873       "activity": true
 874      },
 875      "outputs": []
 876     },
 877     {
 878      "cell_type": "markdown",
 879      "metadata": {
 880       "activity": "activity"
 881      },
 882      "source": [
 883       "If you executed the query:\n",
 884       "\n",
 885       "SELECT *\n",
 886       "FROM country CROSS JOIN language\n",
 887       "WHERE iso3166=country_iso;\n",
 888       "\n",
 889       "\n",
 890       "what would the names of the columns in the returned table be?"
 891      ]
 892     },
 893     {
 894      "cell_type": "markdown",
 895      "metadata": {
 896       "activity": "activityAns"
 897      },
 898      "source": [
 899       "*YOUR ANSWER HERE*"
 900      ]
 901     },
 902     {
 903      "cell_type": "markdown",
 904      "metadata": {
 905       "activity": "activity"
 906      },
 907      "source": [
 908       "What would each row tell you?"
 909      ]
 910     },
 911     {
 912      "cell_type": "markdown",
 913      "metadata": {
 914       "activity": "activityAns"
 915      },
 916      "source": [
 917       "YOUR ANSWER HERE"
 918      ]
 919     },
 920     {
 921      "cell_type": "heading",
 922      "level": 3,
 923      "metadata": {},
 924      "source": [
 925       "Where Next?"
 926      ]
 927     },
 928     {
 929      "cell_type": "markdown",
 930      "metadata": {},
 931      "source": [
 932       "This completes our quick recap of the SQL commands you should recall from M269. SQL is actually a far richer language than we have seen and you will have an opportunity to learn more about it throught the course.\n",
 933       "\n",
 934       "You will also see how the sorts of operation that can be applied using SQL can also be applied using other data manipulation languages. Developing a good understanding of how data is represented, and the operations that can then be applied to it, in abstract terms is essential if you are to be able to work with data effectively."
 935      ]
 936     }
 937    ],
 938    "metadata": {}
 939   }
 940  ]
 941 }