--- /dev/null
+{
+ "metadata": {
+ "name": "",
+ "signature": "sha256:cd3d8a5219bdc5ea9da82fdbfe3b9e6ff8b67beb40d74ed4ace058f529e815fe"
+ },
+ "nbformat": 3,
+ "nbformat_minor": 0,
+ "worksheets": [
+ {
+ "cells": [
+ {
+ "cell_type": "heading",
+ "level": 1,
+ "metadata": {},
+ "source": [
+ "Section 9 Relational CRUD Part 1"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "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",
+ "\n",
+ "In this activity you will create, populate and query the patient table as used in the study materials for Section 9.\n"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ " "
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "Setup the environment to run PostgreSQL."
+ ]
+ },
+ {
+ "cell_type": "code",
+ "collapsed": false,
+ "input": [
+ "import pandas as pd"
+ ],
+ "language": "python",
+ "metadata": {},
+ "outputs": [],
+ "prompt_number": 1
+ },
+ {
+ "cell_type": "code",
+ "collapsed": false,
+ "input": [
+ "import pandas.io.sql as psql"
+ ],
+ "language": "python",
+ "metadata": {},
+ "outputs": [],
+ "prompt_number": 2
+ },
+ {
+ "cell_type": "code",
+ "collapsed": false,
+ "input": [
+ "import psycopg2 as pg"
+ ],
+ "language": "python",
+ "metadata": {},
+ "outputs": [],
+ "prompt_number": 3
+ },
+ {
+ "cell_type": "code",
+ "collapsed": false,
+ "input": [
+ "conn = pg.connect(\"dbname='postgres' host='localhost' user='test' password='test' \")"
+ ],
+ "language": "python",
+ "metadata": {},
+ "outputs": [],
+ "prompt_number": 4
+ },
+ {
+ "cell_type": "code",
+ "collapsed": false,
+ "input": [
+ "c = conn.cursor()"
+ ],
+ "language": "python",
+ "metadata": {},
+ "outputs": [],
+ "prompt_number": 5
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "Create the patient table and populate it with the same data as used in the study materials for Section 9."
+ ]
+ },
+ {
+ "cell_type": "code",
+ "collapsed": false,
+ "input": [
+ "c.execute(\"DROP TABLE IF EXISTS patient\")\n",
+ "c.execute(\"CREATE TABLE patient (\\\n",
+ " patient_id CHAR(4) NOT NULL CHECK (patient_id SIMILAR TO 'p[0-9][0-9][0-9]'),\\\n",
+ " patient_name VARCHAR(20) NOT NULL,\\\n",
+ " date_of_birth DATE NOT NULL,\\\n",
+ " gender CHAR(1) NOT NULL CHECK (gender = 'F' OR gender = 'M'),\\\n",
+ " height DECIMAL(4,1) CHECK (height > 0),\\\n",
+ " weight DECIMAL(4,1) CHECK (weight > 0),\\\n",
+ " PRIMARY KEY (patient_id)\\\n",
+ " )\")\n",
+ "c.execute(\"INSERT INTO patient (patient_id, patient_name, date_of_birth, gender, height, weight)\\\n",
+ " VALUES ( 'p001', 'Thornton', '1980-01-22', 'F', 162.3, 71.6)\")\n",
+ "c.execute(\"INSERT INTO patient (patient_id, patient_name, date_of_birth, gender, height, weight)\\\n",
+ " VALUES ( 'p007', 'Tennent', '1980-04-01', 'M', 176.8, 70.9)\")\n",
+ "c.execute(\"INSERT INTO patient (patient_id, patient_name, date_of_birth, gender, height, weight)\\\n",
+ " VALUES ( 'p008', 'James', '1980-07-08', 'M', 167.9, 70.5)\")\n",
+ "c.execute(\"INSERT INTO patient (patient_id, patient_name, date_of_birth, gender, height, weight)\\\n",
+ " VALUES ( 'p009', 'Kay', '1980-09-25', 'F', 164.7, 53.2)\")\n",
+ "c.execute(\"INSERT INTO patient (patient_id, patient_name, date_of_birth, gender, height, weight)\\\n",
+ " VALUES ( 'p015', 'Harris', '1980-12-04', 'M', 180.6, 64.3)\")\n",
+ "c.execute(\"INSERT INTO patient (patient_id, patient_name, date_of_birth, gender)\\\n",
+ " VALUES ( 'p031', 'Rubinstein', '1980-12-23', 'F')\")\n",
+ "c.execute(\"INSERT INTO patient (patient_id, patient_name, date_of_birth, gender)\\\n",
+ " VALUES ( 'p037', 'Boswell', '1981-06-11', 'F')\")\n",
+ "c.execute(\"INSERT INTO patient (patient_id, patient_name, date_of_birth, gender, height, weight)\\\n",
+ " VALUES ( 'p038', 'Ming', '1981-09-23', 'M', 186.3, 85.4)\")\n",
+ "c.execute(\"INSERT INTO patient (patient_id, patient_name, date_of_birth, gender, height, weight)\\\n",
+ " VALUES ( 'p039', 'Maher', '1981-10-09', 'F', 161.9, 73.0)\")\n",
+ "c.execute(\"INSERT INTO patient (patient_id, patient_name, date_of_birth, gender, height, weight)\\\n",
+ " VALUES ( 'p068', 'Monroe', '1981-10-21', 'F', 165.0, 62.6)\")\n",
+ "c.execute(\"INSERT INTO patient (patient_id, patient_name, date_of_birth, gender, height, weight)\\\n",
+ " VALUES ( 'p071', 'Harris', '1981-12-12', 'M', 186.3, 76.7)\")\n",
+ "c.execute(\"INSERT INTO patient (patient_id, patient_name, date_of_birth, gender, height, weight)\\\n",
+ " VALUES ( 'p078', 'Hunt', '1982-02-25', 'M', 179.9, 74.3)\")\n",
+ "c.execute(\"INSERT INTO patient (patient_id, patient_name, date_of_birth, gender, height, weight)\\\n",
+ " VALUES ( 'p079', 'Dixon', '1982-05-05', 'F', 163.9, 56.5)\")\n",
+ "c.execute(\"INSERT INTO patient (patient_id, patient_name, date_of_birth, gender, height, weight)\\\n",
+ " VALUES ( 'p080', 'Bell', '1982-06-11', 'F', 171.3, 49.2)\")\n",
+ "c.execute(\"INSERT INTO patient (patient_id, patient_name, date_of_birth, gender, height, weight)\\\n",
+ " VALUES ( 'p087', 'Reed', '1982-06-14', 'F', 160.0, 59.1)\")\n",
+ "c.execute(\"INSERT INTO patient (patient_id, patient_name, date_of_birth, gender, height, weight)\\\n",
+ " VALUES ( 'p088', 'Boswell', '1982-08-23', 'M', 168.4, 91.4)\")\n",
+ "c.execute(\"INSERT INTO patient (patient_id, patient_name, date_of_birth, gender, height, weight)\\\n",
+ " VALUES ( 'p089', 'Jarvis', '1982-11-09', 'F', 172.9, 53.4)\")\n",
+ "c.execute(\"COMMIT\")"
+ ],
+ "language": "python",
+ "metadata": {},
+ "outputs": [],
+ "prompt_number": 6
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "Display the contents of the patient table."
+ ]
+ },
+ {
+ "cell_type": "code",
+ "collapsed": false,
+ "input": [
+ "df = pd.read_sql(\"SELECT * FROM patient \\\n",
+ " ORDER BY patient_id\", conn)\n",
+ "df"
+ ],
+ "language": "python",
+ "metadata": {},
+ "outputs": [
+ {
+ "html": [
+ "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
+ "<table border=\"1\" class=\"dataframe\">\n",
+ " <thead>\n",
+ " <tr style=\"text-align: right;\">\n",
+ " <th></th>\n",
+ " <th>patient_id</th>\n",
+ " <th>patient_name</th>\n",
+ " <th>date_of_birth</th>\n",
+ " <th>gender</th>\n",
+ " <th>height</th>\n",
+ " <th>weight</th>\n",
+ " </tr>\n",
+ " </thead>\n",
+ " <tbody>\n",
+ " <tr>\n",
+ " <th>0 </th>\n",
+ " <td> p001</td>\n",
+ " <td> Thornton</td>\n",
+ " <td> 1980-01-22</td>\n",
+ " <td> F</td>\n",
+ " <td> 162.3</td>\n",
+ " <td> 71.6</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>1 </th>\n",
+ " <td> p007</td>\n",
+ " <td> Tennent</td>\n",
+ " <td> 1980-04-01</td>\n",
+ " <td> M</td>\n",
+ " <td> 176.8</td>\n",
+ " <td> 70.9</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>2 </th>\n",
+ " <td> p008</td>\n",
+ " <td> James</td>\n",
+ " <td> 1980-07-08</td>\n",
+ " <td> M</td>\n",
+ " <td> 167.9</td>\n",
+ " <td> 70.5</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>3 </th>\n",
+ " <td> p009</td>\n",
+ " <td> Kay</td>\n",
+ " <td> 1980-09-25</td>\n",
+ " <td> F</td>\n",
+ " <td> 164.7</td>\n",
+ " <td> 53.2</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>4 </th>\n",
+ " <td> p015</td>\n",
+ " <td> Harris</td>\n",
+ " <td> 1980-12-04</td>\n",
+ " <td> M</td>\n",
+ " <td> 180.6</td>\n",
+ " <td> 64.3</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>5 </th>\n",
+ " <td> p031</td>\n",
+ " <td> Rubinstein</td>\n",
+ " <td> 1980-12-23</td>\n",
+ " <td> F</td>\n",
+ " <td> NaN</td>\n",
+ " <td> NaN</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>6 </th>\n",
+ " <td> p037</td>\n",
+ " <td> Boswell</td>\n",
+ " <td> 1981-06-11</td>\n",
+ " <td> F</td>\n",
+ " <td> NaN</td>\n",
+ " <td> NaN</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>7 </th>\n",
+ " <td> p038</td>\n",
+ " <td> Ming</td>\n",
+ " <td> 1981-09-23</td>\n",
+ " <td> M</td>\n",
+ " <td> 186.3</td>\n",
+ " <td> 85.4</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>8 </th>\n",
+ " <td> p039</td>\n",
+ " <td> Maher</td>\n",
+ " <td> 1981-10-09</td>\n",
+ " <td> F</td>\n",
+ " <td> 161.9</td>\n",
+ " <td> 73.0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>9 </th>\n",
+ " <td> p068</td>\n",
+ " <td> Monroe</td>\n",
+ " <td> 1981-10-21</td>\n",
+ " <td> F</td>\n",
+ " <td> 165.0</td>\n",
+ " <td> 62.6</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>10</th>\n",
+ " <td> p071</td>\n",
+ " <td> Harris</td>\n",
+ " <td> 1981-12-12</td>\n",
+ " <td> M</td>\n",
+ " <td> 186.3</td>\n",
+ " <td> 76.7</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>11</th>\n",
+ " <td> p078</td>\n",
+ " <td> Hunt</td>\n",
+ " <td> 1982-02-25</td>\n",
+ " <td> M</td>\n",
+ " <td> 179.9</td>\n",
+ " <td> 74.3</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>12</th>\n",
+ " <td> p079</td>\n",
+ " <td> Dixon</td>\n",
+ " <td> 1982-05-05</td>\n",
+ " <td> F</td>\n",
+ " <td> 163.9</td>\n",
+ " <td> 56.5</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>13</th>\n",
+ " <td> p080</td>\n",
+ " <td> Bell</td>\n",
+ " <td> 1982-06-11</td>\n",
+ " <td> F</td>\n",
+ " <td> 171.3</td>\n",
+ " <td> 49.2</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>14</th>\n",
+ " <td> p087</td>\n",
+ " <td> Reed</td>\n",
+ " <td> 1982-06-14</td>\n",
+ " <td> F</td>\n",
+ " <td> 160.0</td>\n",
+ " <td> 59.1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>15</th>\n",
+ " <td> p088</td>\n",
+ " <td> Boswell</td>\n",
+ " <td> 1982-08-23</td>\n",
+ " <td> M</td>\n",
+ " <td> 168.4</td>\n",
+ " <td> 91.4</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>16</th>\n",
+ " <td> p089</td>\n",
+ " <td> Jarvis</td>\n",
+ " <td> 1982-11-09</td>\n",
+ " <td> F</td>\n",
+ " <td> 172.9</td>\n",
+ " <td> 53.4</td>\n",
+ " </tr>\n",
+ " </tbody>\n",
+ "</table>\n",
+ "</div>"
+ ],
+ "metadata": {},
+ "output_type": "pyout",
+ "prompt_number": 7,
+ "text": [
+ " patient_id patient_name date_of_birth gender height weight\n",
+ "0 p001 Thornton 1980-01-22 F 162.3 71.6\n",
+ "1 p007 Tennent 1980-04-01 M 176.8 70.9\n",
+ "2 p008 James 1980-07-08 M 167.9 70.5\n",
+ "3 p009 Kay 1980-09-25 F 164.7 53.2\n",
+ "4 p015 Harris 1980-12-04 M 180.6 64.3\n",
+ "5 p031 Rubinstein 1980-12-23 F NaN NaN\n",
+ "6 p037 Boswell 1981-06-11 F NaN NaN\n",
+ "7 p038 Ming 1981-09-23 M 186.3 85.4\n",
+ "8 p039 Maher 1981-10-09 F 161.9 73.0\n",
+ "9 p068 Monroe 1981-10-21 F 165.0 62.6\n",
+ "10 p071 Harris 1981-12-12 M 186.3 76.7\n",
+ "11 p078 Hunt 1982-02-25 M 179.9 74.3\n",
+ "12 p079 Dixon 1982-05-05 F 163.9 56.5\n",
+ "13 p080 Bell 1982-06-11 F 171.3 49.2\n",
+ "14 p087 Reed 1982-06-14 F 160.0 59.1\n",
+ "15 p088 Boswell 1982-08-23 M 168.4 91.4\n",
+ "16 p089 Jarvis 1982-11-09 F 172.9 53.4"
+ ]
+ }
+ ],
+ "prompt_number": 7
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ " "
+ ]
+ },
+ {
+ "cell_type": "heading",
+ "level": 6,
+ "metadata": {},
+ "source": [
+ "Projection"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "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",
+ " \n",
+ "SELECT {column list}\n",
+ "FROM {table}\n",
+ " \n",
+ "For example, \u201cDisplay the names of patients and their gender\u201d:\n"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "collapsed": false,
+ "input": [
+ "df = pd.read_sql(\"SELECT patient_name, gender \\\n",
+ " FROM patient \\\n",
+ " ORDER BY patient_name, gender\", conn)\n",
+ "df"
+ ],
+ "language": "python",
+ "metadata": {},
+ "outputs": [
+ {
+ "html": [
+ "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
+ "<table border=\"1\" class=\"dataframe\">\n",
+ " <thead>\n",
+ " <tr style=\"text-align: right;\">\n",
+ " <th></th>\n",
+ " <th>patient_name</th>\n",
+ " <th>gender</th>\n",
+ " </tr>\n",
+ " </thead>\n",
+ " <tbody>\n",
+ " <tr>\n",
+ " <th>0 </th>\n",
+ " <td> Bell</td>\n",
+ " <td> F</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>1 </th>\n",
+ " <td> Boswell</td>\n",
+ " <td> F</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>2 </th>\n",
+ " <td> Boswell</td>\n",
+ " <td> M</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>3 </th>\n",
+ " <td> Dixon</td>\n",
+ " <td> F</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>4 </th>\n",
+ " <td> Harris</td>\n",
+ " <td> M</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>5 </th>\n",
+ " <td> Harris</td>\n",
+ " <td> M</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>6 </th>\n",
+ " <td> Hunt</td>\n",
+ " <td> M</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>7 </th>\n",
+ " <td> James</td>\n",
+ " <td> M</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>8 </th>\n",
+ " <td> Jarvis</td>\n",
+ " <td> F</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>9 </th>\n",
+ " <td> Kay</td>\n",
+ " <td> F</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>10</th>\n",
+ " <td> Maher</td>\n",
+ " <td> F</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>11</th>\n",
+ " <td> Ming</td>\n",
+ " <td> M</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>12</th>\n",
+ " <td> Monroe</td>\n",
+ " <td> F</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>13</th>\n",
+ " <td> Reed</td>\n",
+ " <td> F</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>14</th>\n",
+ " <td> Rubinstein</td>\n",
+ " <td> F</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>15</th>\n",
+ " <td> Tennent</td>\n",
+ " <td> M</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>16</th>\n",
+ " <td> Thornton</td>\n",
+ " <td> F</td>\n",
+ " </tr>\n",
+ " </tbody>\n",
+ "</table>\n",
+ "</div>"
+ ],
+ "metadata": {},
+ "output_type": "pyout",
+ "prompt_number": 8,
+ "text": [
+ " patient_name gender\n",
+ "0 Bell F\n",
+ "1 Boswell F\n",
+ "2 Boswell M\n",
+ "3 Dixon F\n",
+ "4 Harris M\n",
+ "5 Harris M\n",
+ "6 Hunt M\n",
+ "7 James M\n",
+ "8 Jarvis F\n",
+ "9 Kay F\n",
+ "10 Maher F\n",
+ "11 Ming M\n",
+ "12 Monroe F\n",
+ "13 Reed F\n",
+ "14 Rubinstein F\n",
+ "15 Tennent M\n",
+ "16 Thornton F"
+ ]
+ }
+ ],
+ "prompt_number": 8
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "\u201cDisplay the names of patients and their gender, eliminating duplicate rows\u201d:"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "collapsed": false,
+ "input": [
+ "df = pd.read_sql(\"SELECT DISTINCT patient_name, gender \\\n",
+ " FROM patient \\\n",
+ " ORDER BY patient_name, gender\", conn)\n",
+ "df"
+ ],
+ "language": "python",
+ "metadata": {},
+ "outputs": [
+ {
+ "html": [
+ "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
+ "<table border=\"1\" class=\"dataframe\">\n",
+ " <thead>\n",
+ " <tr style=\"text-align: right;\">\n",
+ " <th></th>\n",
+ " <th>patient_name</th>\n",
+ " <th>gender</th>\n",
+ " </tr>\n",
+ " </thead>\n",
+ " <tbody>\n",
+ " <tr>\n",
+ " <th>0 </th>\n",
+ " <td> Bell</td>\n",
+ " <td> F</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>1 </th>\n",
+ " <td> Boswell</td>\n",
+ " <td> F</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>2 </th>\n",
+ " <td> Boswell</td>\n",
+ " <td> M</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>3 </th>\n",
+ " <td> Dixon</td>\n",
+ " <td> F</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>4 </th>\n",
+ " <td> Harris</td>\n",
+ " <td> M</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>5 </th>\n",
+ " <td> Hunt</td>\n",
+ " <td> M</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>6 </th>\n",
+ " <td> James</td>\n",
+ " <td> M</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>7 </th>\n",
+ " <td> Jarvis</td>\n",
+ " <td> F</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>8 </th>\n",
+ " <td> Kay</td>\n",
+ " <td> F</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>9 </th>\n",
+ " <td> Maher</td>\n",
+ " <td> F</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>10</th>\n",
+ " <td> Ming</td>\n",
+ " <td> M</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>11</th>\n",
+ " <td> Monroe</td>\n",
+ " <td> F</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>12</th>\n",
+ " <td> Reed</td>\n",
+ " <td> F</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>13</th>\n",
+ " <td> Rubinstein</td>\n",
+ " <td> F</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>14</th>\n",
+ " <td> Tennent</td>\n",
+ " <td> M</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>15</th>\n",
+ " <td> Thornton</td>\n",
+ " <td> F</td>\n",
+ " </tr>\n",
+ " </tbody>\n",
+ "</table>\n",
+ "</div>"
+ ],
+ "metadata": {},
+ "output_type": "pyout",
+ "prompt_number": 9,
+ "text": [
+ " patient_name gender\n",
+ "0 Bell F\n",
+ "1 Boswell F\n",
+ "2 Boswell M\n",
+ "3 Dixon F\n",
+ "4 Harris M\n",
+ "5 Hunt M\n",
+ "6 James M\n",
+ "7 Jarvis F\n",
+ "8 Kay F\n",
+ "9 Maher F\n",
+ "10 Ming M\n",
+ "11 Monroe F\n",
+ "12 Reed F\n",
+ "13 Rubinstein F\n",
+ "14 Tennent M\n",
+ "15 Thornton F"
+ ]
+ }
+ ],
+ "prompt_number": 9
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ " "
+ ]
+ },
+ {
+ "cell_type": "heading",
+ "level": 6,
+ "metadata": {},
+ "source": [
+ "Selection"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "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",
+ " \n",
+ "SELECT *\n",
+ "FROM {table}\n",
+ "WHERE {condition}\n",
+ " \n",
+ "For example, \u201cDisplay the details of all the female patients\u201d:\n"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "collapsed": false,
+ "input": [
+ "df = pd.read_sql(\"SELECT * \\\n",
+ " FROM patient \\\n",
+ " WHERE gender = 'F' \\\n",
+ " ORDER BY patient_id\", conn)\n",
+ "df"
+ ],
+ "language": "python",
+ "metadata": {},
+ "outputs": [
+ {
+ "html": [
+ "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
+ "<table border=\"1\" class=\"dataframe\">\n",
+ " <thead>\n",
+ " <tr style=\"text-align: right;\">\n",
+ " <th></th>\n",
+ " <th>patient_id</th>\n",
+ " <th>patient_name</th>\n",
+ " <th>date_of_birth</th>\n",
+ " <th>gender</th>\n",
+ " <th>height</th>\n",
+ " <th>weight</th>\n",
+ " </tr>\n",
+ " </thead>\n",
+ " <tbody>\n",
+ " <tr>\n",
+ " <th>0</th>\n",
+ " <td> p001</td>\n",
+ " <td> Thornton</td>\n",
+ " <td> 1980-01-22</td>\n",
+ " <td> F</td>\n",
+ " <td> 162.3</td>\n",
+ " <td> 71.6</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>1</th>\n",
+ " <td> p009</td>\n",
+ " <td> Kay</td>\n",
+ " <td> 1980-09-25</td>\n",
+ " <td> F</td>\n",
+ " <td> 164.7</td>\n",
+ " <td> 53.2</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>2</th>\n",
+ " <td> p031</td>\n",
+ " <td> Rubinstein</td>\n",
+ " <td> 1980-12-23</td>\n",
+ " <td> F</td>\n",
+ " <td> NaN</td>\n",
+ " <td> NaN</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>3</th>\n",
+ " <td> p037</td>\n",
+ " <td> Boswell</td>\n",
+ " <td> 1981-06-11</td>\n",
+ " <td> F</td>\n",
+ " <td> NaN</td>\n",
+ " <td> NaN</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>4</th>\n",
+ " <td> p039</td>\n",
+ " <td> Maher</td>\n",
+ " <td> 1981-10-09</td>\n",
+ " <td> F</td>\n",
+ " <td> 161.9</td>\n",
+ " <td> 73.0</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>5</th>\n",
+ " <td> p068</td>\n",
+ " <td> Monroe</td>\n",
+ " <td> 1981-10-21</td>\n",
+ " <td> F</td>\n",
+ " <td> 165.0</td>\n",
+ " <td> 62.6</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>6</th>\n",
+ " <td> p079</td>\n",
+ " <td> Dixon</td>\n",
+ " <td> 1982-05-05</td>\n",
+ " <td> F</td>\n",
+ " <td> 163.9</td>\n",
+ " <td> 56.5</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>7</th>\n",
+ " <td> p080</td>\n",
+ " <td> Bell</td>\n",
+ " <td> 1982-06-11</td>\n",
+ " <td> F</td>\n",
+ " <td> 171.3</td>\n",
+ " <td> 49.2</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>8</th>\n",
+ " <td> p087</td>\n",
+ " <td> Reed</td>\n",
+ " <td> 1982-06-14</td>\n",
+ " <td> F</td>\n",
+ " <td> 160.0</td>\n",
+ " <td> 59.1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>9</th>\n",
+ " <td> p089</td>\n",
+ " <td> Jarvis</td>\n",
+ " <td> 1982-11-09</td>\n",
+ " <td> F</td>\n",
+ " <td> 172.9</td>\n",
+ " <td> 53.4</td>\n",
+ " </tr>\n",
+ " </tbody>\n",
+ "</table>\n",
+ "</div>"
+ ],
+ "metadata": {},
+ "output_type": "pyout",
+ "prompt_number": 10,
+ "text": [
+ " patient_id patient_name date_of_birth gender height weight\n",
+ "0 p001 Thornton 1980-01-22 F 162.3 71.6\n",
+ "1 p009 Kay 1980-09-25 F 164.7 53.2\n",
+ "2 p031 Rubinstein 1980-12-23 F NaN NaN\n",
+ "3 p037 Boswell 1981-06-11 F NaN NaN\n",
+ "4 p039 Maher 1981-10-09 F 161.9 73.0\n",
+ "5 p068 Monroe 1981-10-21 F 165.0 62.6\n",
+ "6 p079 Dixon 1982-05-05 F 163.9 56.5\n",
+ "7 p080 Bell 1982-06-11 F 171.3 49.2\n",
+ "8 p087 Reed 1982-06-14 F 160.0 59.1\n",
+ "9 p089 Jarvis 1982-11-09 F 172.9 53.4"
+ ]
+ }
+ ],
+ "prompt_number": 10
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ " "
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "\u201cDisplay the details of patients whose weight has not been recorded\u201d:"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "collapsed": false,
+ "input": [
+ "df = pd.read_sql(\"SELECT * \\\n",
+ " FROM patient \\\n",
+ " WHERE weight IS NULL \\\n",
+ " ORDER BY patient_id\", conn)\n",
+ "df"
+ ],
+ "language": "python",
+ "metadata": {},
+ "outputs": [
+ {
+ "html": [
+ "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
+ "<table border=\"1\" class=\"dataframe\">\n",
+ " <thead>\n",
+ " <tr style=\"text-align: right;\">\n",
+ " <th></th>\n",
+ " <th>patient_id</th>\n",
+ " <th>patient_name</th>\n",
+ " <th>date_of_birth</th>\n",
+ " <th>gender</th>\n",
+ " <th>height</th>\n",
+ " <th>weight</th>\n",
+ " </tr>\n",
+ " </thead>\n",
+ " <tbody>\n",
+ " <tr>\n",
+ " <th>0</th>\n",
+ " <td> p031</td>\n",
+ " <td> Rubinstein</td>\n",
+ " <td> 1980-12-23</td>\n",
+ " <td> F</td>\n",
+ " <td> None</td>\n",
+ " <td> None</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>1</th>\n",
+ " <td> p037</td>\n",
+ " <td> Boswell</td>\n",
+ " <td> 1981-06-11</td>\n",
+ " <td> F</td>\n",
+ " <td> None</td>\n",
+ " <td> None</td>\n",
+ " </tr>\n",
+ " </tbody>\n",
+ "</table>\n",
+ "</div>"
+ ],
+ "metadata": {},
+ "output_type": "pyout",
+ "prompt_number": 11,
+ "text": [
+ " patient_id patient_name date_of_birth gender height weight\n",
+ "0 p031 Rubinstein 1980-12-23 F None None\n",
+ "1 p037 Boswell 1981-06-11 F None None"
+ ]
+ }
+ ],
+ "prompt_number": 11
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ " "
+ ]
+ },
+ {
+ "cell_type": "heading",
+ "level": 6,
+ "metadata": {},
+ "source": [
+ "Aggregation"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "Summarising data by subset groupings \u2013 aggregation, is effected by including aggregate functions in the SELECT clause and using the GROUP BY clause:\n",
+ " \n",
+ "SELECT {aggregate function(s)} FROM {table(s)} [WHERE {condition} ] [GROUP BY {column list} [HAVING {condition}]]\n",
+ "\n",
+ "For example, \"Display some summary statistics about patients\":"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "collapsed": false,
+ "input": [
+ "df = pd.read_sql(\"SELECT COUNT(*) AS number_of_patients, \\\n",
+ " COUNT(DISTINCT patient_name) AS number_of_distinct_names, \\\n",
+ " COUNT(weight) AS number_weighed, \\\n",
+ " MIN(weight) AS minimum_weight, \\\n",
+ " MAX(weight) AS maximum_weight, \\\n",
+ " CAST(AVG(weight) AS DECIMAL(4,1)) AS average_weight \\\n",
+ " FROM patient\", conn)\n",
+ "df"
+ ],
+ "language": "python",
+ "metadata": {},
+ "outputs": [
+ {
+ "html": [
+ "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
+ "<table border=\"1\" class=\"dataframe\">\n",
+ " <thead>\n",
+ " <tr style=\"text-align: right;\">\n",
+ " <th></th>\n",
+ " <th>number_of_patients</th>\n",
+ " <th>number_of_distinct_names</th>\n",
+ " <th>number_weighed</th>\n",
+ " <th>minimum_weight</th>\n",
+ " <th>maximum_weight</th>\n",
+ " <th>average_weight</th>\n",
+ " </tr>\n",
+ " </thead>\n",
+ " <tbody>\n",
+ " <tr>\n",
+ " <th>0</th>\n",
+ " <td> 17</td>\n",
+ " <td> 15</td>\n",
+ " <td> 15</td>\n",
+ " <td> 49.2</td>\n",
+ " <td> 91.4</td>\n",
+ " <td> 67.5</td>\n",
+ " </tr>\n",
+ " </tbody>\n",
+ "</table>\n",
+ "</div>"
+ ],
+ "metadata": {},
+ "output_type": "pyout",
+ "prompt_number": 12,
+ "text": [
+ " number_of_patients number_of_distinct_names number_weighed \\\n",
+ "0 17 15 15 \n",
+ "\n",
+ " minimum_weight maximum_weight average_weight \n",
+ "0 49.2 91.4 67.5 "
+ ]
+ }
+ ],
+ "prompt_number": 12
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ " "
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "\"Display separate summary statistics about female and male patients\":"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "collapsed": false,
+ "input": [
+ "df = pd.read_sql(\"SELECT gender, \\\n",
+ " COUNT(*) AS number_of_patients, \\\n",
+ " COUNT(DISTINCT patient_name) AS number_of_distinct_names, \\\n",
+ " COUNT(weight) AS number_weighed, \\\n",
+ " MIN(weight) AS minimum_weight, \\\n",
+ " MAX(weight) AS maximum_weight, \\\n",
+ " CAST(AVG(weight) AS DECIMAL(4,1)) AS average_weight \\\n",
+ " FROM patient \\\n",
+ " GROUP BY gender\", conn)\n",
+ "df"
+ ],
+ "language": "python",
+ "metadata": {},
+ "outputs": [
+ {
+ "html": [
+ "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
+ "<table border=\"1\" class=\"dataframe\">\n",
+ " <thead>\n",
+ " <tr style=\"text-align: right;\">\n",
+ " <th></th>\n",
+ " <th>gender</th>\n",
+ " <th>number_of_patients</th>\n",
+ " <th>number_of_distinct_names</th>\n",
+ " <th>number_weighed</th>\n",
+ " <th>minimum_weight</th>\n",
+ " <th>maximum_weight</th>\n",
+ " <th>average_weight</th>\n",
+ " </tr>\n",
+ " </thead>\n",
+ " <tbody>\n",
+ " <tr>\n",
+ " <th>0</th>\n",
+ " <td> F</td>\n",
+ " <td> 10</td>\n",
+ " <td> 10</td>\n",
+ " <td> 8</td>\n",
+ " <td> 49.2</td>\n",
+ " <td> 73.0</td>\n",
+ " <td> 59.8</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>1</th>\n",
+ " <td> M</td>\n",
+ " <td> 7</td>\n",
+ " <td> 6</td>\n",
+ " <td> 7</td>\n",
+ " <td> 64.3</td>\n",
+ " <td> 91.4</td>\n",
+ " <td> 76.2</td>\n",
+ " </tr>\n",
+ " </tbody>\n",
+ "</table>\n",
+ "</div>"
+ ],
+ "metadata": {},
+ "output_type": "pyout",
+ "prompt_number": 13,
+ "text": [
+ " gender number_of_patients number_of_distinct_names number_weighed \\\n",
+ "0 F 10 10 8 \n",
+ "1 M 7 6 7 \n",
+ "\n",
+ " minimum_weight maximum_weight average_weight \n",
+ "0 49.2 73.0 59.8 \n",
+ "1 64.3 91.4 76.2 "
+ ]
+ }
+ ],
+ "prompt_number": 13
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ " "
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "\"Display separate summary statistics about patients by their year of birth\":"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "collapsed": false,
+ "input": [
+ "df = pd.read_sql(\"SELECT EXTRACT(YEAR FROM date_of_birth) AS birth_year, \\\n",
+ " COUNT(*) AS number_of_patients, \\\n",
+ " COUNT(DISTINCT patient_name) AS number_of_distinct_names, \\\n",
+ " COUNT(weight) AS number_weighed, \\\n",
+ " MIN(weight) AS minimum_weight, \\\n",
+ " MAX(weight) AS maximum_weight, \\\n",
+ " CAST(AVG(weight) AS DECIMAL(4,1)) AS average_weight \\\n",
+ " FROM patient \\\n",
+ " GROUP BY EXTRACT(YEAR FROM date_of_birth) \\\n",
+ " ORDER BY EXTRACT(YEAR FROM date_of_birth)\", conn)\n",
+ "df"
+ ],
+ "language": "python",
+ "metadata": {},
+ "outputs": [
+ {
+ "html": [
+ "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
+ "<table border=\"1\" class=\"dataframe\">\n",
+ " <thead>\n",
+ " <tr style=\"text-align: right;\">\n",
+ " <th></th>\n",
+ " <th>birth_year</th>\n",
+ " <th>number_of_patients</th>\n",
+ " <th>number_of_distinct_names</th>\n",
+ " <th>number_weighed</th>\n",
+ " <th>minimum_weight</th>\n",
+ " <th>maximum_weight</th>\n",
+ " <th>average_weight</th>\n",
+ " </tr>\n",
+ " </thead>\n",
+ " <tbody>\n",
+ " <tr>\n",
+ " <th>0</th>\n",
+ " <td> 1980</td>\n",
+ " <td> 6</td>\n",
+ " <td> 6</td>\n",
+ " <td> 5</td>\n",
+ " <td> 53.2</td>\n",
+ " <td> 71.6</td>\n",
+ " <td> 66.1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>1</th>\n",
+ " <td> 1981</td>\n",
+ " <td> 5</td>\n",
+ " <td> 5</td>\n",
+ " <td> 4</td>\n",
+ " <td> 62.6</td>\n",
+ " <td> 85.4</td>\n",
+ " <td> 74.4</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>2</th>\n",
+ " <td> 1982</td>\n",
+ " <td> 6</td>\n",
+ " <td> 6</td>\n",
+ " <td> 6</td>\n",
+ " <td> 49.2</td>\n",
+ " <td> 91.4</td>\n",
+ " <td> 64.0</td>\n",
+ " </tr>\n",
+ " </tbody>\n",
+ "</table>\n",
+ "</div>"
+ ],
+ "metadata": {},
+ "output_type": "pyout",
+ "prompt_number": 14,
+ "text": [
+ " birth_year number_of_patients number_of_distinct_names number_weighed \\\n",
+ "0 1980 6 6 5 \n",
+ "1 1981 5 5 4 \n",
+ "2 1982 6 6 6 \n",
+ "\n",
+ " minimum_weight maximum_weight average_weight \n",
+ "0 53.2 71.6 66.1 \n",
+ "1 62.6 85.4 74.4 \n",
+ "2 49.2 91.4 64.0 "
+ ]
+ }
+ ],
+ "prompt_number": 14
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ " "
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "\"Display separate summary statistics about patients by their year of birth where at least 5 patients have had their weight recorded\":"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "collapsed": false,
+ "input": [
+ "df = pd.read_sql(\"SELECT EXTRACT(YEAR FROM date_of_birth) AS birth_year, \\\n",
+ " COUNT(*) AS number_of_patients, \\\n",
+ " COUNT(DISTINCT patient_name) AS number_of_distinct_names, \\\n",
+ " COUNT(weight) AS number_weighed, \\\n",
+ " MIN(weight) AS minimum_weight, \\\n",
+ " MAX(weight) AS maximum_weight, \\\n",
+ " CAST(AVG(weight) AS DECIMAL(4,1)) AS average_weight \\\n",
+ " FROM patient \\\n",
+ " GROUP BY EXTRACT(YEAR FROM date_of_birth) \\\n",
+ " HAVING COUNT(weight) >= 5 \\\n",
+ " ORDER BY EXTRACT(YEAR FROM date_of_birth)\", conn)\n",
+ "df"
+ ],
+ "language": "python",
+ "metadata": {},
+ "outputs": [
+ {
+ "html": [
+ "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
+ "<table border=\"1\" class=\"dataframe\">\n",
+ " <thead>\n",
+ " <tr style=\"text-align: right;\">\n",
+ " <th></th>\n",
+ " <th>birth_year</th>\n",
+ " <th>number_of_patients</th>\n",
+ " <th>number_of_distinct_names</th>\n",
+ " <th>number_weighed</th>\n",
+ " <th>minimum_weight</th>\n",
+ " <th>maximum_weight</th>\n",
+ " <th>average_weight</th>\n",
+ " </tr>\n",
+ " </thead>\n",
+ " <tbody>\n",
+ " <tr>\n",
+ " <th>0</th>\n",
+ " <td> 1980</td>\n",
+ " <td> 6</td>\n",
+ " <td> 6</td>\n",
+ " <td> 5</td>\n",
+ " <td> 53.2</td>\n",
+ " <td> 71.6</td>\n",
+ " <td> 66.1</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <th>1</th>\n",
+ " <td> 1982</td>\n",
+ " <td> 6</td>\n",
+ " <td> 6</td>\n",
+ " <td> 6</td>\n",
+ " <td> 49.2</td>\n",
+ " <td> 91.4</td>\n",
+ " <td> 64.0</td>\n",
+ " </tr>\n",
+ " </tbody>\n",
+ "</table>\n",
+ "</div>"
+ ],
+ "metadata": {},
+ "output_type": "pyout",
+ "prompt_number": 15,
+ "text": [
+ " birth_year number_of_patients number_of_distinct_names number_weighed \\\n",
+ "0 1980 6 6 5 \n",
+ "1 1982 6 6 6 \n",
+ "\n",
+ " minimum_weight maximum_weight average_weight \n",
+ "0 53.2 71.6 66.1 \n",
+ "1 49.2 91.4 64.0 "
+ ]
+ }
+ ],
+ "prompt_number": 15
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ " \n",
+ " "
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ " "
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ " "
+ ]
+ },
+ {
+ "cell_type": "heading",
+ "level": 3,
+ "metadata": {},
+ "source": [
+ "Exercises"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "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",
+ "\n",
+ "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",
+ "\n",
+ "3. Write and execute SQL SELECT statements to answer the following questions about the patient data:\n",
+ " \n",
+ " (i) Display the details of female patients who were born before 1981.\n",
+ " \n",
+ " (ii) For patients who have the same name, display those names.\n",
+ " \n",
+ " (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?"
+ ]
+ }
+ ],
+ "metadata": {}
+ }
+ ]
+}
\ No newline at end of file
projects / tm351-notebooks.git / blobdiff