intro to sql.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# A gentle introduction to SQL\n",
    "\n",
    "\n",
    "This notebook introduces some of the basic commands for querying and modifying a database using the Structured Query Language, SQL. \n",
    "\n",
    "Part One retrieves data in the form of some csv files, from elsewhere in the internet. Then we're going to use a particular handy tool to turn this data into a database. \n",
    "\n",
    "In Part two, we'll walk through some basic SQL commands for exploring and transforming this data. The amphitheatre data is courtesy Sebastian Heath, [https://doi.org/10.5281/zenodo.596149](https://doi.org/10.5281/zenodo.596149). The aqueduct data is from [Pelagios Commons](http://commons.pelagios.org).\n",
    "\n",
    "## Part One"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We've already obtained the amphitheatre data from Sebastian Heath using the `curl` command, like so:\n",
    "\n",
    "`!curl https://raw.githubusercontent.com/sfsheath/roman-amphitheaters/master/roman-amphitheaters.csv > amphi.csv`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We also downloaded information about roman aqueducts from the [peripleo api](http://peripleo.pelagios.org/peripleo/search?query=roman+AND+aqueduct&prettyprint=true) as json, and converted to csv using [this tool](https://github.com/zemirco/json2csv).\n",
    "\n",
    "Finally, we want to turn these two csv files into a single database containing two tables. We will use the command line tool '[sqlitebiter](https://github.com/thombashi/sqlitebiter)' by Tsuyoshi Hombashi to do this, which we have already installed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[37m[INFO] sqlitebiter file: convert 'amphi.csv' to 'amphi' table\u001b[39;49;00m\n",
      "\u001b[37m[INFO] sqlitebiter file: convert 'aqua.csv' to 'aqua' table\u001b[39;49;00m\n",
      "\u001b[37m[INFO] sqlitebiter file: number of created tables: 2\u001b[39;49;00m\n",
      "\u001b[37m[INFO] sqlitebiter file: database path: /home/jovyan/roman.db\u001b[39;49;00m\n",
      "\u001b[0m"
     ]
    }
   ],
   "source": [
    "!sqlitebiter -o roman.db file \"amphi.csv\" \"aqua.csv\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CREATE TABLE IF NOT EXISTS '_source_info_' (source_id INTEGER PRIMARY KEY AUTOINCREMENT, dir_name TEXT, base_name TEXT NOT NULL, format TEXT NOT NULL, size INTEGER, mtime INTEGER);\r\n",
      "CREATE TABLE sqlite_sequence(name,seq);\r\n",
      "CREATE TABLE IF NOT EXISTS 'amphi' (id TEXT, title TEXT, label TEXT, latintoponym TEXT, pleiades TEXT, welchid INTEGER, golvinid INTEGER, buildingtype TEXT, chronogroup TEXT, secondcentury INTEGER, capacity INTEGER, modcountry TEXT, romanregion TEXT, arenamajor REAL, arenaminor REAL, extmajor REAL, extminor REAL, exteriorheight REAL, longitude REAL, latitude REAL, elevation INTEGER);\r\n",
      "CREATE TABLE IF NOT EXISTS 'aqua' (identifier TEXT, title TEXT, \"object_type\" TEXT, \"dataset_path\" TEXT, \"geo_bounds\" TEXT, names TEXT, \"temporal_bounds\" TEXT, depictions TEXT, matches TEXT);\r\n"
     ]
    }
   ],
   "source": [
    "!sqlite3 roman.db .schema .exit"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "_source_info_  amphi          aqua         \r\n"
     ]
    }
   ],
   "source": [
    "!sqlite3 roman.db .tables .exit"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Part Two\n",
    "\n",
    "Now that we have a database, we'll bring it into python so that we can query it. Once a database is in python, we can do a wide variety of data science type visualizations or explorations, although these are beyond the remit of the current notebook.\n",
    "\n",
    "The first thing we're going to do is create a function that opens a connection to the database, and allows us to build queries. After we create the function, we can create query objects, and then `run_query`. Students might also want to consult [this tutorial](https://www.dataquest.io/blog/sql-basics/)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create a function for querying the database\n",
    "import sqlite3\n",
    "import pandas as pd\n",
    "\n",
    "db = sqlite3.connect('roman.db')\n",
    "\n",
    "def run_query(query):\n",
    "    return pd.read_sql_query(query,db)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's give it a try. We're going to build a query that asks, 'show us every column in the amphi table, but only for the first five rows.'\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>title</th>\n",
       "      <th>label</th>\n",
       "      <th>latintoponym</th>\n",
       "      <th>pleiades</th>\n",
       "      <th>welchid</th>\n",
       "      <th>golvinid</th>\n",
       "      <th>buildingtype</th>\n",
       "      <th>chronogroup</th>\n",
       "      <th>secondcentury</th>\n",
       "      <th>...</th>\n",
       "      <th>modcountry</th>\n",
       "      <th>romanregion</th>\n",
       "      <th>arenamajor</th>\n",
       "      <th>arenaminor</th>\n",
       "      <th>extmajor</th>\n",
       "      <th>extminor</th>\n",
       "      <th>exteriorheight</th>\n",
       "      <th>longitude</th>\n",
       "      <th>latitude</th>\n",
       "      <th>elevation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>duraEuroposAmphitheater</td>\n",
       "      <td>Amphitheater at Dura Europos</td>\n",
       "      <td>Dura</td>\n",
       "      <td>Dura Europus</td>\n",
       "      <td>https://pleiades.stoa.org/places/893989</td>\n",
       "      <td></td>\n",
       "      <td>129</td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>severan</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>Syria</td>\n",
       "      <td>syria</td>\n",
       "      <td>31</td>\n",
       "      <td>25</td>\n",
       "      <td>50</td>\n",
       "      <td>44</td>\n",
       "      <td></td>\n",
       "      <td>40.728926</td>\n",
       "      <td>34.749855</td>\n",
       "      <td>223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>arlesAmphitheater</td>\n",
       "      <td>Amphitheater at Arles</td>\n",
       "      <td>Arles</td>\n",
       "      <td>Arelate</td>\n",
       "      <td>https://pleiades.stoa.org/places/148217</td>\n",
       "      <td></td>\n",
       "      <td>154</td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>flavian</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>France</td>\n",
       "      <td>narbonensis</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>136</td>\n",
       "      <td>107</td>\n",
       "      <td></td>\n",
       "      <td>4.631111</td>\n",
       "      <td>43.677778</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>lyonAmphitheater</td>\n",
       "      <td>Amphitheater at Lyon</td>\n",
       "      <td>Lyon</td>\n",
       "      <td>Lugdunum</td>\n",
       "      <td>https://pleiades.stoa.org/places/167717</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>second-century</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>France</td>\n",
       "      <td>lugdunensis</td>\n",
       "      <td>67.6</td>\n",
       "      <td>42</td>\n",
       "      <td>105</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>4.830556</td>\n",
       "      <td>45.770556</td>\n",
       "      <td>206</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ludusMagnusArena</td>\n",
       "      <td>Ludus Magnus Arena</td>\n",
       "      <td>Ludus Magnus</td>\n",
       "      <td>Ludus Magnus</td>\n",
       "      <td>https://pleiades.stoa.org/places/423025</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>practice-arena</td>\n",
       "      <td>imperial</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>Italy</td>\n",
       "      <td>regio-i</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>12.494913</td>\n",
       "      <td>41.889950</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>romeFlavianAmphitheater</td>\n",
       "      <td>Flavian Amphitheater at Rome</td>\n",
       "      <td>Colosseum</td>\n",
       "      <td></td>\n",
       "      <td>https://pleiades.stoa.org/places/423025</td>\n",
       "      <td></td>\n",
       "      <td>152</td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>flavian</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>Italy</td>\n",
       "      <td>regio-i</td>\n",
       "      <td>83</td>\n",
       "      <td>48</td>\n",
       "      <td>189</td>\n",
       "      <td>156</td>\n",
       "      <td>52</td>\n",
       "      <td>12.492269</td>\n",
       "      <td>41.890169</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        id                         title         label  \\\n",
       "0  duraEuroposAmphitheater  Amphitheater at Dura Europos          Dura   \n",
       "1        arlesAmphitheater         Amphitheater at Arles         Arles   \n",
       "2         lyonAmphitheater          Amphitheater at Lyon          Lyon   \n",
       "3         ludusMagnusArena            Ludus Magnus Arena  Ludus Magnus   \n",
       "4  romeFlavianAmphitheater  Flavian Amphitheater at Rome     Colosseum   \n",
       "\n",
       "   latintoponym                                 pleiades welchid golvinid  \\\n",
       "0  Dura Europus  https://pleiades.stoa.org/places/893989              129   \n",
       "1       Arelate  https://pleiades.stoa.org/places/148217              154   \n",
       "2      Lugdunum  https://pleiades.stoa.org/places/167717                    \n",
       "3  Ludus Magnus  https://pleiades.stoa.org/places/423025                    \n",
       "4                https://pleiades.stoa.org/places/423025              152   \n",
       "\n",
       "     buildingtype     chronogroup  secondcentury    ...     modcountry  \\\n",
       "0    amphitheater         severan              0    ...          Syria   \n",
       "1    amphitheater         flavian              1    ...         France   \n",
       "2    amphitheater  second-century              1    ...         France   \n",
       "3  practice-arena        imperial              0    ...          Italy   \n",
       "4    amphitheater         flavian              1    ...          Italy   \n",
       "\n",
       "   romanregion arenamajor arenaminor extmajor extminor exteriorheight  \\\n",
       "0        syria         31         25       50       44                  \n",
       "1  narbonensis         47         32      136      107                  \n",
       "2  lugdunensis       67.6         42      105                           \n",
       "3      regio-i                                                          \n",
       "4      regio-i         83         48      189      156             52   \n",
       "\n",
       "   longitude   latitude  elevation  \n",
       "0  40.728926  34.749855        223  \n",
       "1   4.631111  43.677778         21  \n",
       "2   4.830556  45.770556        206  \n",
       "3  12.494913  41.889950         22  \n",
       "4  12.492269  41.890169         22  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = 'SELECT * FROM amphi LIMIT 5;'\n",
    "run_query(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>identifier</th>\n",
       "      <th>title</th>\n",
       "      <th>object_type</th>\n",
       "      <th>dataset_path</th>\n",
       "      <th>geo_bounds</th>\n",
       "      <th>names</th>\n",
       "      <th>temporal_bounds</th>\n",
       "      <th>depictions</th>\n",
       "      <th>matches</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>http://dare.ht.lu.se/places/43530</td>\n",
       "      <td>Roman aqueduct, Mitiline, Lesbos</td>\n",
       "      <td>Place</td>\n",
       "      <td>[{\"title\":\"dare-20160328\",\"id\":\"dare-20160328\"}]</td>\n",
       "      <td>{\"min_lon\":26.514763,\"max_lon\":26.514763,\"min_...</td>\n",
       "      <td>[\"Roman aqueduct, Mitiline, Lesbos\"]</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>http://pleiades.stoa.org/places/738817254</td>\n",
       "      <td>Ansignan aqueduct</td>\n",
       "      <td>Place</td>\n",
       "      <td>[{\"title\":\"pleiades\",\"id\":\"pleiades\"}]</td>\n",
       "      <td>{\"min_lon\":2.5140266,\"max_lon\":2.5140266,\"min_...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>http://pleiades.stoa.org/places/403927</td>\n",
       "      <td>Caldaccoli aqueduct</td>\n",
       "      <td>Place</td>\n",
       "      <td>[{\"title\":\"pleiades\",\"id\":\"pleiades\"}]</td>\n",
       "      <td>{\"min_lon\":10.4367702,\"max_lon\":10.4367702,\"mi...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>http://topostext.org/place/352254BTei</td>\n",
       "      <td>Lyttos aqueduct (Lasithi)</td>\n",
       "      <td>Place</td>\n",
       "      <td>[{\"title\":\"ToposText Places\",\"id\":\"ToposText P...</td>\n",
       "      <td>{\"min_lon\":25.3818,\"max_lon\":25.3818,\"min_lat\"...</td>\n",
       "      <td></td>\n",
       "      <td>{\"start\":-750,\"end\":640}</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>http://pleiades.stoa.org/places/246891</td>\n",
       "      <td>Albarracín-Cella Roman aqueduct</td>\n",
       "      <td>Place</td>\n",
       "      <td>[{\"title\":\"pleiades\",\"id\":\"pleiades\"}]</td>\n",
       "      <td>{\"min_lon\":-1.5,\"max_lon\":-1.5,\"min_lat\":40.5,...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  identifier  \\\n",
       "0          http://dare.ht.lu.se/places/43530   \n",
       "1  http://pleiades.stoa.org/places/738817254   \n",
       "2     http://pleiades.stoa.org/places/403927   \n",
       "3      http://topostext.org/place/352254BTei   \n",
       "4     http://pleiades.stoa.org/places/246891   \n",
       "\n",
       "                              title object_type  \\\n",
       "0  Roman aqueduct, Mitiline, Lesbos       Place   \n",
       "1                 Ansignan aqueduct       Place   \n",
       "2               Caldaccoli aqueduct       Place   \n",
       "3         Lyttos aqueduct (Lasithi)       Place   \n",
       "4   Albarracín-Cella Roman aqueduct       Place   \n",
       "\n",
       "                                        dataset_path  \\\n",
       "0   [{\"title\":\"dare-20160328\",\"id\":\"dare-20160328\"}]   \n",
       "1             [{\"title\":\"pleiades\",\"id\":\"pleiades\"}]   \n",
       "2             [{\"title\":\"pleiades\",\"id\":\"pleiades\"}]   \n",
       "3  [{\"title\":\"ToposText Places\",\"id\":\"ToposText P...   \n",
       "4             [{\"title\":\"pleiades\",\"id\":\"pleiades\"}]   \n",
       "\n",
       "                                          geo_bounds  \\\n",
       "0  {\"min_lon\":26.514763,\"max_lon\":26.514763,\"min_...   \n",
       "1  {\"min_lon\":2.5140266,\"max_lon\":2.5140266,\"min_...   \n",
       "2  {\"min_lon\":10.4367702,\"max_lon\":10.4367702,\"mi...   \n",
       "3  {\"min_lon\":25.3818,\"max_lon\":25.3818,\"min_lat\"...   \n",
       "4  {\"min_lon\":-1.5,\"max_lon\":-1.5,\"min_lat\":40.5,...   \n",
       "\n",
       "                                  names           temporal_bounds depictions  \\\n",
       "0  [\"Roman aqueduct, Mitiline, Lesbos\"]                                        \n",
       "1                                                                              \n",
       "2                                                                              \n",
       "3                                        {\"start\":-750,\"end\":640}              \n",
       "4                                                                              \n",
       "\n",
       "  matches  \n",
       "0          \n",
       "1          \n",
       "2          \n",
       "3          \n",
       "4          "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# just check that the aquaduct table is in there too\n",
    "query = 'SELECT * FROM aqua LIMIT 5;'\n",
    "run_query(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Basic Query Commands\n",
    "\n",
    "SELECT, LIMIT, ORDER BY : using these, we can ask, 'Which amphitheatre is at the highest elevation?'\n",
    "\n",
    "Use SELECT to retrieve the id and elevation columns FROM the amphi table\n",
    "\n",
    "Use ORDER BY to sort the elevation column and use the DESC keyword to specify that you want to sort in descending order\n",
    "\n",
    "Use LIMIT to restrict the output to 1 row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>elevation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>lambaesisAmphitheater</td>\n",
       "      <td>1170</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      id  elevation\n",
       "0  lambaesisAmphitheater       1170"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = '''\n",
    "SELECT id, elevation \n",
    "FROM amphi\n",
    "ORDER BY elevation DESC\n",
    "LIMIT 1;\n",
    "'''\n",
    "\n",
    "run_query(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's get the top 10 now"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>elevation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>lambaesisAmphitheater</td>\n",
       "      <td>1170</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>albaFucensAmphitheater</td>\n",
       "      <td>977</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>mactarisAmphitheater</td>\n",
       "      <td>911</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>tebessaAmphitheater</td>\n",
       "      <td>877</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>leonAmphitheater</td>\n",
       "      <td>846</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>bostraAmphitheater</td>\n",
       "      <td>845</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>segobrigaAmphitheater</td>\n",
       "      <td>817</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>siccaVeneriaAmphitheater</td>\n",
       "      <td>748</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>sanBenedettoDeiMarsiAmphitheater</td>\n",
       "      <td>683</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>sanVittorinoAmphitheater</td>\n",
       "      <td>672</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 id  elevation\n",
       "0             lambaesisAmphitheater       1170\n",
       "1            albaFucensAmphitheater        977\n",
       "2              mactarisAmphitheater        911\n",
       "3               tebessaAmphitheater        877\n",
       "4                  leonAmphitheater        846\n",
       "5                bostraAmphitheater        845\n",
       "6             segobrigaAmphitheater        817\n",
       "7          siccaVeneriaAmphitheater        748\n",
       "8  sanBenedettoDeiMarsiAmphitheater        683\n",
       "9          sanVittorinoAmphitheater        672"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = '''\n",
    "SELECT id, elevation \n",
    "FROM amphi\n",
    "ORDER BY elevation DESC\n",
    "LIMIT 10;\n",
    "'''\n",
    "\n",
    "run_query(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Following this pattern, can you create a query that also provides the geographic coordinates? In the block below see if you can construct and run that query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Querying with Conditions\n",
    "\n",
    "Now let's create a query that creates a subset of data using a logical operator. We need the 'WHERE' command."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>title</th>\n",
       "      <th>label</th>\n",
       "      <th>latintoponym</th>\n",
       "      <th>pleiades</th>\n",
       "      <th>welchid</th>\n",
       "      <th>golvinid</th>\n",
       "      <th>buildingtype</th>\n",
       "      <th>chronogroup</th>\n",
       "      <th>secondcentury</th>\n",
       "      <th>...</th>\n",
       "      <th>modcountry</th>\n",
       "      <th>romanregion</th>\n",
       "      <th>arenamajor</th>\n",
       "      <th>arenaminor</th>\n",
       "      <th>extmajor</th>\n",
       "      <th>extminor</th>\n",
       "      <th>exteriorheight</th>\n",
       "      <th>longitude</th>\n",
       "      <th>latitude</th>\n",
       "      <th>elevation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>segobrigaAmphitheater</td>\n",
       "      <td>Amphitheater at Segobriga</td>\n",
       "      <td>Segobriga</td>\n",
       "      <td>Segobriga</td>\n",
       "      <td>https://pleiades.stoa.org/places/266036</td>\n",
       "      <td></td>\n",
       "      <td>88</td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>flavian</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>Spain</td>\n",
       "      <td>tarraconensis</td>\n",
       "      <td>47</td>\n",
       "      <td>31</td>\n",
       "      <td>75</td>\n",
       "      <td>64</td>\n",
       "      <td></td>\n",
       "      <td>-2.813944</td>\n",
       "      <td>39.886018</td>\n",
       "      <td>817</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>cyreneAmphitheater</td>\n",
       "      <td>Amphitheater at Cyrene</td>\n",
       "      <td>Cyrene</td>\n",
       "      <td></td>\n",
       "      <td>https://pleiades.stoa.org/places/373778</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>imperial</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>Libya</td>\n",
       "      <td>crete-et-cyrenaica</td>\n",
       "      <td>32.7</td>\n",
       "      <td>28.8</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>21.850808</td>\n",
       "      <td>32.824526</td>\n",
       "      <td>550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>grumentoAmphitheater</td>\n",
       "      <td>Amphitheater at Grumento</td>\n",
       "      <td>Grumento</td>\n",
       "      <td>Grumentum</td>\n",
       "      <td>https://pleiades.stoa.org/places/442603</td>\n",
       "      <td></td>\n",
       "      <td>92</td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>imperial</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>Italy</td>\n",
       "      <td>regio-iii</td>\n",
       "      <td>60</td>\n",
       "      <td>40</td>\n",
       "      <td>90</td>\n",
       "      <td>70</td>\n",
       "      <td></td>\n",
       "      <td>15.911579</td>\n",
       "      <td>40.287640</td>\n",
       "      <td>579</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>albaFucensAmphitheater</td>\n",
       "      <td>Amphitheater at Alba Fucens</td>\n",
       "      <td>Alba Fucens</td>\n",
       "      <td>Alba Fucens</td>\n",
       "      <td>https://pleiades.stoa.org/places/413005</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>julio-claudian</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>Italy</td>\n",
       "      <td>regio-iv</td>\n",
       "      <td>64</td>\n",
       "      <td>37</td>\n",
       "      <td>103</td>\n",
       "      <td>76</td>\n",
       "      <td></td>\n",
       "      <td>13.412289</td>\n",
       "      <td>42.077200</td>\n",
       "      <td>977</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>lambaesisAmphitheater</td>\n",
       "      <td>Amphitheater at Lambaesis</td>\n",
       "      <td>Lambaesis</td>\n",
       "      <td></td>\n",
       "      <td>https://pleiades.stoa.org/places/334570</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>second-century</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>mauretania</td>\n",
       "      <td>68</td>\n",
       "      <td>55</td>\n",
       "      <td>104.6</td>\n",
       "      <td>94</td>\n",
       "      <td></td>\n",
       "      <td>6.259935</td>\n",
       "      <td>35.489247</td>\n",
       "      <td>1170</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>mactarisAmphitheater</td>\n",
       "      <td>Amphitheater at Mactaris</td>\n",
       "      <td>Mactaris</td>\n",
       "      <td></td>\n",
       "      <td>https://pleiades.stoa.org/places/324774</td>\n",
       "      <td></td>\n",
       "      <td>119</td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>second-century</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>Tunisia</td>\n",
       "      <td>proconsularis</td>\n",
       "      <td>38.4</td>\n",
       "      <td>24.8</td>\n",
       "      <td>63.2</td>\n",
       "      <td>49.6</td>\n",
       "      <td></td>\n",
       "      <td>9.206673</td>\n",
       "      <td>35.855628</td>\n",
       "      <td>911</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>sbeitlaAmphitheater</td>\n",
       "      <td>Amphitheater at Sbeitla</td>\n",
       "      <td>Sbeitla</td>\n",
       "      <td>Sufetula</td>\n",
       "      <td>https://pleiades.stoa.org/places/324816</td>\n",
       "      <td></td>\n",
       "      <td>113</td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>second-century</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>Tunisia</td>\n",
       "      <td>proconsularis</td>\n",
       "      <td>47</td>\n",
       "      <td>37</td>\n",
       "      <td>72</td>\n",
       "      <td>60</td>\n",
       "      <td></td>\n",
       "      <td>9.114582</td>\n",
       "      <td>35.243617</td>\n",
       "      <td>556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>tebessaAmphitheater</td>\n",
       "      <td>Amphitheater at Theveste</td>\n",
       "      <td>Tebessa</td>\n",
       "      <td>Theveste</td>\n",
       "      <td>https://pleiades.stoa.org/places/324831</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>flavian</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>proconsularis</td>\n",
       "      <td>52.8</td>\n",
       "      <td>39.5</td>\n",
       "      <td>83</td>\n",
       "      <td>70</td>\n",
       "      <td></td>\n",
       "      <td>8.123809</td>\n",
       "      <td>35.401171</td>\n",
       "      <td>877</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>susaAmphitheater</td>\n",
       "      <td>Amphitheater at Susa</td>\n",
       "      <td>Susa</td>\n",
       "      <td>Segusio</td>\n",
       "      <td>https://pleiades.stoa.org/places/167919</td>\n",
       "      <td></td>\n",
       "      <td>15</td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>imperial</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>Italy</td>\n",
       "      <td>alpes-cottiae</td>\n",
       "      <td>44</td>\n",
       "      <td>36</td>\n",
       "      <td>60</td>\n",
       "      <td>52</td>\n",
       "      <td></td>\n",
       "      <td>7.044970</td>\n",
       "      <td>45.133185</td>\n",
       "      <td>546</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>sophiaAmphitheater</td>\n",
       "      <td>Amphitheater at Sophia</td>\n",
       "      <td>Sophia</td>\n",
       "      <td>Ulpia Serdica</td>\n",
       "      <td>https://pleiades.stoa.org/places/207439</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>post-severan</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>Bulgaria</td>\n",
       "      <td>thracia</td>\n",
       "      <td>60.5</td>\n",
       "      <td>43</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>23.328650</td>\n",
       "      <td>42.697178</td>\n",
       "      <td>554</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>rodezAmphitheater</td>\n",
       "      <td>Amphitheater at Rodez</td>\n",
       "      <td>Rodez</td>\n",
       "      <td>Segodunum</td>\n",
       "      <td>https://pleiades.stoa.org/places/138579</td>\n",
       "      <td></td>\n",
       "      <td>22</td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>imperial</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>France</td>\n",
       "      <td>aquitania</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>2.571162</td>\n",
       "      <td>44.353698</td>\n",
       "      <td>611</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>sanBenedettoDeiMarsiAmphitheater</td>\n",
       "      <td>Amphitheater at San Benedetto dei Marsi</td>\n",
       "      <td>San Benedetto</td>\n",
       "      <td>Marruvium</td>\n",
       "      <td>https://pleiades.stoa.org/places/432927</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>imperial</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>Italy</td>\n",
       "      <td>regio-iv</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>101</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>13.627505</td>\n",
       "      <td>42.006504</td>\n",
       "      <td>683</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>sanVittorinoAmphitheater</td>\n",
       "      <td>Amphitheater at San Vittorino</td>\n",
       "      <td>San Vittorino</td>\n",
       "      <td>Amiternum</td>\n",
       "      <td>https://pleiades.stoa.org/places/413013</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>first-century</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>Italy</td>\n",
       "      <td>regio-iv</td>\n",
       "      <td>64</td>\n",
       "      <td>42</td>\n",
       "      <td>90</td>\n",
       "      <td>68</td>\n",
       "      <td></td>\n",
       "      <td>13.306027</td>\n",
       "      <td>42.400531</td>\n",
       "      <td>672</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>sisapoAmphitheater</td>\n",
       "      <td>Amphitheater at Sisapo</td>\n",
       "      <td>Sisapo</td>\n",
       "      <td></td>\n",
       "      <td>https://pleiades.stoa.org/places/266043</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>imperial</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>Spain</td>\n",
       "      <td>baetica</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>-4.516667</td>\n",
       "      <td>38.645833</td>\n",
       "      <td>659</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>leonAmphitheater</td>\n",
       "      <td>Amphitheater at León</td>\n",
       "      <td>León</td>\n",
       "      <td>Legio</td>\n",
       "      <td>https://pleiades.stoa.org/places/236512</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>imperial</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>Spain</td>\n",
       "      <td>tarraconensis</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>-5.566944</td>\n",
       "      <td>42.598889</td>\n",
       "      <td>846</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>bernAmphitheater</td>\n",
       "      <td>Amphitheater at Bern</td>\n",
       "      <td>Bern</td>\n",
       "      <td>Bremodorum</td>\n",
       "      <td>https://pleiades.stoa.org/places/177471</td>\n",
       "      <td></td>\n",
       "      <td>42</td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>imperial</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>Switzerland</td>\n",
       "      <td>germania-superior</td>\n",
       "      <td>27.6</td>\n",
       "      <td>23.5</td>\n",
       "      <td>42.6</td>\n",
       "      <td>38.5</td>\n",
       "      <td></td>\n",
       "      <td>7.451179</td>\n",
       "      <td>46.975715</td>\n",
       "      <td>547</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>bostraAmphitheater</td>\n",
       "      <td>Amphitheater at Bosra</td>\n",
       "      <td>Bosra</td>\n",
       "      <td>Nova Trajana Bostra</td>\n",
       "      <td>https://pleiades.stoa.org/places/678073</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>imperial</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>Syria</td>\n",
       "      <td>syria</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>36.479844</td>\n",
       "      <td>32.517923</td>\n",
       "      <td>845</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>siccaVeneriaAmphitheater</td>\n",
       "      <td>Amphitheater at Sicca Veneria</td>\n",
       "      <td>Sicca Veneria</td>\n",
       "      <td>Sicca Veneria</td>\n",
       "      <td>https://pleiades.stoa.org/places/315152</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>imperial</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>Tunisia</td>\n",
       "      <td>proconsularis</td>\n",
       "      <td>70</td>\n",
       "      <td>50</td>\n",
       "      <td>100</td>\n",
       "      <td>80</td>\n",
       "      <td></td>\n",
       "      <td>8.715302</td>\n",
       "      <td>36.185361</td>\n",
       "      <td>748</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>aostaAmphitheater</td>\n",
       "      <td>Amphitheater at Aosta</td>\n",
       "      <td>Aosta</td>\n",
       "      <td>Augusta Praetoria</td>\n",
       "      <td>https://pleiades.stoa.org/places/383579</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>julio-claudian</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>Italy</td>\n",
       "      <td>regio-xi</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>150</td>\n",
       "      <td>115</td>\n",
       "      <td></td>\n",
       "      <td>7.323481</td>\n",
       "      <td>45.740914</td>\n",
       "      <td>595</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>aphrodisiasAmphitheater</td>\n",
       "      <td>Late Roman Amphitheater at Aphrodisas</td>\n",
       "      <td>Aphrodisias</td>\n",
       "      <td>Aphrodisias</td>\n",
       "      <td>https://pleiades.stoa.org/places/638753</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>arena-in-stadium</td>\n",
       "      <td>fourth-century</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>Turkey</td>\n",
       "      <td>asia</td>\n",
       "      <td>59</td>\n",
       "      <td>30</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>28.723061</td>\n",
       "      <td>37.712553</td>\n",
       "      <td>526</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>tusculumAmphitheater</td>\n",
       "      <td>Tusculum Amphitheater</td>\n",
       "      <td>Tusculum</td>\n",
       "      <td></td>\n",
       "      <td>https://pleiades.stoa.org/places/423108</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>second-century</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>Italy</td>\n",
       "      <td>regio-i</td>\n",
       "      <td>49</td>\n",
       "      <td>30</td>\n",
       "      <td>73</td>\n",
       "      <td>54</td>\n",
       "      <td></td>\n",
       "      <td>12.702103</td>\n",
       "      <td>41.798372</td>\n",
       "      <td>582</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>compsaAmphitheater</td>\n",
       "      <td>Amphitheater at Compsa</td>\n",
       "      <td>Compsa</td>\n",
       "      <td>Compsa</td>\n",
       "      <td>https://pleiades.stoa.org/places/442550</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>republican</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>Italy</td>\n",
       "      <td>regio-ii</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>15.330556</td>\n",
       "      <td>40.870278</td>\n",
       "      <td>572</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>22 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  id                                    title  \\\n",
       "0              segobrigaAmphitheater                Amphitheater at Segobriga   \n",
       "1                 cyreneAmphitheater                   Amphitheater at Cyrene   \n",
       "2               grumentoAmphitheater                 Amphitheater at Grumento   \n",
       "3             albaFucensAmphitheater              Amphitheater at Alba Fucens   \n",
       "4              lambaesisAmphitheater                Amphitheater at Lambaesis   \n",
       "5               mactarisAmphitheater                 Amphitheater at Mactaris   \n",
       "6                sbeitlaAmphitheater                  Amphitheater at Sbeitla   \n",
       "7                tebessaAmphitheater                 Amphitheater at Theveste   \n",
       "8                   susaAmphitheater                     Amphitheater at Susa   \n",
       "9                 sophiaAmphitheater                   Amphitheater at Sophia   \n",
       "10                 rodezAmphitheater                    Amphitheater at Rodez   \n",
       "11  sanBenedettoDeiMarsiAmphitheater  Amphitheater at San Benedetto dei Marsi   \n",
       "12          sanVittorinoAmphitheater            Amphitheater at San Vittorino   \n",
       "13                sisapoAmphitheater                   Amphitheater at Sisapo   \n",
       "14                  leonAmphitheater                     Amphitheater at León   \n",
       "15                  bernAmphitheater                     Amphitheater at Bern   \n",
       "16                bostraAmphitheater                    Amphitheater at Bosra   \n",
       "17          siccaVeneriaAmphitheater            Amphitheater at Sicca Veneria   \n",
       "18                 aostaAmphitheater                    Amphitheater at Aosta   \n",
       "19           aphrodisiasAmphitheater    Late Roman Amphitheater at Aphrodisas   \n",
       "20              tusculumAmphitheater                    Tusculum Amphitheater   \n",
       "21                compsaAmphitheater                   Amphitheater at Compsa   \n",
       "\n",
       "            label         latintoponym  \\\n",
       "0       Segobriga            Segobriga   \n",
       "1          Cyrene                        \n",
       "2        Grumento            Grumentum   \n",
       "3     Alba Fucens          Alba Fucens   \n",
       "4       Lambaesis                        \n",
       "5        Mactaris                        \n",
       "6         Sbeitla             Sufetula   \n",
       "7         Tebessa             Theveste   \n",
       "8            Susa              Segusio   \n",
       "9          Sophia        Ulpia Serdica   \n",
       "10          Rodez            Segodunum   \n",
       "11  San Benedetto            Marruvium   \n",
       "12  San Vittorino            Amiternum   \n",
       "13         Sisapo                        \n",
       "14           León                Legio   \n",
       "15           Bern           Bremodorum   \n",
       "16          Bosra  Nova Trajana Bostra   \n",
       "17  Sicca Veneria        Sicca Veneria   \n",
       "18          Aosta    Augusta Praetoria   \n",
       "19    Aphrodisias          Aphrodisias   \n",
       "20       Tusculum                        \n",
       "21         Compsa               Compsa   \n",
       "\n",
       "                                   pleiades welchid golvinid  \\\n",
       "0   https://pleiades.stoa.org/places/266036               88   \n",
       "1   https://pleiades.stoa.org/places/373778                    \n",
       "2   https://pleiades.stoa.org/places/442603               92   \n",
       "3   https://pleiades.stoa.org/places/413005                    \n",
       "4   https://pleiades.stoa.org/places/334570                    \n",
       "5   https://pleiades.stoa.org/places/324774              119   \n",
       "6   https://pleiades.stoa.org/places/324816              113   \n",
       "7   https://pleiades.stoa.org/places/324831                    \n",
       "8   https://pleiades.stoa.org/places/167919               15   \n",
       "9   https://pleiades.stoa.org/places/207439                    \n",
       "10  https://pleiades.stoa.org/places/138579               22   \n",
       "11  https://pleiades.stoa.org/places/432927                    \n",
       "12  https://pleiades.stoa.org/places/413013                    \n",
       "13  https://pleiades.stoa.org/places/266043                    \n",
       "14  https://pleiades.stoa.org/places/236512                    \n",
       "15  https://pleiades.stoa.org/places/177471               42   \n",
       "16  https://pleiades.stoa.org/places/678073                    \n",
       "17  https://pleiades.stoa.org/places/315152                    \n",
       "18  https://pleiades.stoa.org/places/383579                    \n",
       "19  https://pleiades.stoa.org/places/638753                    \n",
       "20  https://pleiades.stoa.org/places/423108                    \n",
       "21  https://pleiades.stoa.org/places/442550                    \n",
       "\n",
       "        buildingtype     chronogroup  secondcentury    ...       modcountry  \\\n",
       "0       amphitheater         flavian              1    ...            Spain   \n",
       "1       amphitheater        imperial              1    ...            Libya   \n",
       "2       amphitheater        imperial              1    ...            Italy   \n",
       "3       amphitheater  julio-claudian              1    ...            Italy   \n",
       "4       amphitheater  second-century              1    ...          Algeria   \n",
       "5       amphitheater  second-century              1    ...          Tunisia   \n",
       "6       amphitheater  second-century              1    ...          Tunisia   \n",
       "7       amphitheater         flavian              1    ...          Algeria   \n",
       "8       amphitheater        imperial              1    ...            Italy   \n",
       "9       amphitheater    post-severan              0    ...         Bulgaria   \n",
       "10      amphitheater        imperial              1    ...           France   \n",
       "11      amphitheater        imperial              1    ...            Italy   \n",
       "12      amphitheater   first-century              1    ...            Italy   \n",
       "13      amphitheater        imperial              1    ...            Spain   \n",
       "14      amphitheater        imperial              1    ...            Spain   \n",
       "15      amphitheater        imperial              1    ...      Switzerland   \n",
       "16      amphitheater        imperial              1    ...            Syria   \n",
       "17      amphitheater        imperial              1    ...          Tunisia   \n",
       "18      amphitheater  julio-claudian              1    ...            Italy   \n",
       "19  arena-in-stadium  fourth-century              0    ...           Turkey   \n",
       "20      amphitheater  second-century              1    ...            Italy   \n",
       "21      amphitheater      republican              1    ...            Italy   \n",
       "\n",
       "           romanregion arenamajor arenaminor extmajor extminor exteriorheight  \\\n",
       "0        tarraconensis         47         31       75       64                  \n",
       "1   crete-et-cyrenaica       32.7       28.8                                    \n",
       "2            regio-iii         60         40       90       70                  \n",
       "3             regio-iv         64         37      103       76                  \n",
       "4           mauretania         68         55    104.6       94                  \n",
       "5        proconsularis       38.4       24.8     63.2     49.6                  \n",
       "6        proconsularis         47         37       72       60                  \n",
       "7        proconsularis       52.8       39.5       83       70                  \n",
       "8        alpes-cottiae         44         36       60       52                  \n",
       "9              thracia       60.5         43                                    \n",
       "10           aquitania                                                          \n",
       "11            regio-iv                            101                           \n",
       "12            regio-iv         64         42       90       68                  \n",
       "13             baetica                                                          \n",
       "14       tarraconensis                                                          \n",
       "15   germania-superior       27.6       23.5     42.6     38.5                  \n",
       "16               syria                                                          \n",
       "17       proconsularis         70         50      100       80                  \n",
       "18            regio-xi                            150      115                  \n",
       "19                asia         59         30                                    \n",
       "20             regio-i         49         30       73       54                  \n",
       "21            regio-ii                                                          \n",
       "\n",
       "    longitude   latitude  elevation  \n",
       "0   -2.813944  39.886018        817  \n",
       "1   21.850808  32.824526        550  \n",
       "2   15.911579  40.287640        579  \n",
       "3   13.412289  42.077200        977  \n",
       "4    6.259935  35.489247       1170  \n",
       "5    9.206673  35.855628        911  \n",
       "6    9.114582  35.243617        556  \n",
       "7    8.123809  35.401171        877  \n",
       "8    7.044970  45.133185        546  \n",
       "9   23.328650  42.697178        554  \n",
       "10   2.571162  44.353698        611  \n",
       "11  13.627505  42.006504        683  \n",
       "12  13.306027  42.400531        672  \n",
       "13  -4.516667  38.645833        659  \n",
       "14  -5.566944  42.598889        846  \n",
       "15   7.451179  46.975715        547  \n",
       "16  36.479844  32.517923        845  \n",
       "17   8.715302  36.185361        748  \n",
       "18   7.323481  45.740914        595  \n",
       "19  28.723061  37.712553        526  \n",
       "20  12.702103  41.798372        582  \n",
       "21  15.330556  40.870278        572  \n",
       "\n",
       "[22 rows x 21 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = '''\n",
    "SELECT * \n",
    "FROM amphi\n",
    "WHERE elevation > 500;\n",
    "'''\n",
    "\n",
    "run_query(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Our condition can be string data too; in which case we put the string in quotation marks:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>title</th>\n",
       "      <th>label</th>\n",
       "      <th>latintoponym</th>\n",
       "      <th>pleiades</th>\n",
       "      <th>welchid</th>\n",
       "      <th>golvinid</th>\n",
       "      <th>buildingtype</th>\n",
       "      <th>chronogroup</th>\n",
       "      <th>secondcentury</th>\n",
       "      <th>...</th>\n",
       "      <th>modcountry</th>\n",
       "      <th>romanregion</th>\n",
       "      <th>arenamajor</th>\n",
       "      <th>arenaminor</th>\n",
       "      <th>extmajor</th>\n",
       "      <th>extminor</th>\n",
       "      <th>exteriorheight</th>\n",
       "      <th>longitude</th>\n",
       "      <th>latitude</th>\n",
       "      <th>elevation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>arlesAmphitheater</td>\n",
       "      <td>Amphitheater at Arles</td>\n",
       "      <td>Arles</td>\n",
       "      <td>Arelate</td>\n",
       "      <td>https://pleiades.stoa.org/places/148217</td>\n",
       "      <td></td>\n",
       "      <td>154</td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>flavian</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>France</td>\n",
       "      <td>narbonensis</td>\n",
       "      <td>47</td>\n",
       "      <td>32</td>\n",
       "      <td>136</td>\n",
       "      <td>107</td>\n",
       "      <td></td>\n",
       "      <td>4.631111</td>\n",
       "      <td>43.677778</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>romeFlavianAmphitheater</td>\n",
       "      <td>Flavian Amphitheater at Rome</td>\n",
       "      <td>Colosseum</td>\n",
       "      <td></td>\n",
       "      <td>https://pleiades.stoa.org/places/423025</td>\n",
       "      <td></td>\n",
       "      <td>152</td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>flavian</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>Italy</td>\n",
       "      <td>regio-i</td>\n",
       "      <td>83</td>\n",
       "      <td>48</td>\n",
       "      <td>189</td>\n",
       "      <td>156</td>\n",
       "      <td>52</td>\n",
       "      <td>12.492269</td>\n",
       "      <td>41.890169</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>newsteadAmphitheater</td>\n",
       "      <td>Amphitheater at Newstead</td>\n",
       "      <td>Newstead</td>\n",
       "      <td>Trimontium</td>\n",
       "      <td>https://pleiades.stoa.org/places/89304</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>flavian</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>britannia</td>\n",
       "      <td>37</td>\n",
       "      <td>23</td>\n",
       "      <td>70</td>\n",
       "      <td>60</td>\n",
       "      <td></td>\n",
       "      <td>-2.691900</td>\n",
       "      <td>55.602600</td>\n",
       "      <td>83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>pozzuoliFlavianAmphitheater</td>\n",
       "      <td>Flavian Amphitheater at Pozzuoli</td>\n",
       "      <td>Pozzuoli (Flavian)</td>\n",
       "      <td>Puteoli</td>\n",
       "      <td>https://pleiades.stoa.org/places/432815</td>\n",
       "      <td></td>\n",
       "      <td>153</td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>flavian</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>Italy</td>\n",
       "      <td>regio-i</td>\n",
       "      <td>74.8</td>\n",
       "      <td>42</td>\n",
       "      <td>149</td>\n",
       "      <td>116</td>\n",
       "      <td></td>\n",
       "      <td>14.125315</td>\n",
       "      <td>40.825929</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>segobrigaAmphitheater</td>\n",
       "      <td>Amphitheater at Segobriga</td>\n",
       "      <td>Segobriga</td>\n",
       "      <td>Segobriga</td>\n",
       "      <td>https://pleiades.stoa.org/places/266036</td>\n",
       "      <td></td>\n",
       "      <td>88</td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>flavian</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>Spain</td>\n",
       "      <td>tarraconensis</td>\n",
       "      <td>47</td>\n",
       "      <td>31</td>\n",
       "      <td>75</td>\n",
       "      <td>64</td>\n",
       "      <td></td>\n",
       "      <td>-2.813944</td>\n",
       "      <td>39.886018</td>\n",
       "      <td>817</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>parisAmphitheater</td>\n",
       "      <td>Amphitheater at Paris</td>\n",
       "      <td>Paris</td>\n",
       "      <td>Lutetia Parisiorum</td>\n",
       "      <td>https://pleiades.stoa.org/places/109126</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>gallo-roman-amphitheater</td>\n",
       "      <td>flavian</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>France</td>\n",
       "      <td>lugdunensis</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>2.352855</td>\n",
       "      <td>48.845097</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>nimesAmphitheater</td>\n",
       "      <td>Amphitheater at Nimes</td>\n",
       "      <td>Nimes</td>\n",
       "      <td>Nemausus</td>\n",
       "      <td>https://pleiades.stoa.org/places/148142</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>flavian</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>France</td>\n",
       "      <td>narbonensis</td>\n",
       "      <td>69.1</td>\n",
       "      <td>38.4</td>\n",
       "      <td>133.4</td>\n",
       "      <td>101.4</td>\n",
       "      <td></td>\n",
       "      <td>4.359599</td>\n",
       "      <td>43.834876</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>chesterAmphitheater</td>\n",
       "      <td>Amphitheater at Chester</td>\n",
       "      <td>Chester</td>\n",
       "      <td>Deva Victrix</td>\n",
       "      <td>https://pleiades.stoa.org/places/79420</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>flavian</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>britannia</td>\n",
       "      <td>56.7</td>\n",
       "      <td>48.3</td>\n",
       "      <td>95.5</td>\n",
       "      <td>86.6</td>\n",
       "      <td></td>\n",
       "      <td>-2.886928</td>\n",
       "      <td>53.189079</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>arezzoAmphitheater</td>\n",
       "      <td>Amphitheater at Arezzo</td>\n",
       "      <td>Arezzo</td>\n",
       "      <td>Arretium</td>\n",
       "      <td>https://pleiades.stoa.org/places/413032</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>flavian</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>Italy</td>\n",
       "      <td>regio-vii</td>\n",
       "      <td>71.9</td>\n",
       "      <td>42.7</td>\n",
       "      <td>109.4</td>\n",
       "      <td>80</td>\n",
       "      <td></td>\n",
       "      <td>11.880370</td>\n",
       "      <td>43.460491</td>\n",
       "      <td>263</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>londonAmphitheater</td>\n",
       "      <td>Amphitheater at London</td>\n",
       "      <td>London</td>\n",
       "      <td>Londinium</td>\n",
       "      <td>https://pleiades.stoa.org/places/79574</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>flavian</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>britannia</td>\n",
       "      <td>60</td>\n",
       "      <td>40</td>\n",
       "      <td>100</td>\n",
       "      <td>85</td>\n",
       "      <td></td>\n",
       "      <td>-0.092136</td>\n",
       "      <td>51.515522</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>urbisagliaAmphitheater</td>\n",
       "      <td>Amphitheater at Urbs Salvia</td>\n",
       "      <td>Urbs Salvia</td>\n",
       "      <td>Urbs Saglia</td>\n",
       "      <td>https://pleiades.stoa.org/places/413364</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>flavian</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>Italy</td>\n",
       "      <td>regio-v</td>\n",
       "      <td>59</td>\n",
       "      <td>35</td>\n",
       "      <td>98</td>\n",
       "      <td>71</td>\n",
       "      <td></td>\n",
       "      <td>13.387224</td>\n",
       "      <td>43.200877</td>\n",
       "      <td>219</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>vindonissaAmphitheater</td>\n",
       "      <td>Amphitheater at Vindonissa</td>\n",
       "      <td>Vindonissa</td>\n",
       "      <td></td>\n",
       "      <td>https://pleiades.stoa.org/places/177661</td>\n",
       "      <td></td>\n",
       "      <td>17</td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>flavian</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>Switzerland</td>\n",
       "      <td>germania-superior</td>\n",
       "      <td>64</td>\n",
       "      <td>51</td>\n",
       "      <td>112</td>\n",
       "      <td>98</td>\n",
       "      <td></td>\n",
       "      <td>8.213605</td>\n",
       "      <td>47.476290</td>\n",
       "      <td>361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>frejusAmphitheater</td>\n",
       "      <td>Amphitheater Fréjus</td>\n",
       "      <td>Fréjus</td>\n",
       "      <td>Forum Julii</td>\n",
       "      <td>https://pleiades.stoa.org/places/157836</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>flavian</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>France</td>\n",
       "      <td>narbonensis</td>\n",
       "      <td>67.7</td>\n",
       "      <td>39.7</td>\n",
       "      <td>113.7</td>\n",
       "      <td>85.7</td>\n",
       "      <td></td>\n",
       "      <td>6.728710</td>\n",
       "      <td>43.434460</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>tebessaAmphitheater</td>\n",
       "      <td>Amphitheater at Theveste</td>\n",
       "      <td>Tebessa</td>\n",
       "      <td>Theveste</td>\n",
       "      <td>https://pleiades.stoa.org/places/324831</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>flavian</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>proconsularis</td>\n",
       "      <td>52.8</td>\n",
       "      <td>39.5</td>\n",
       "      <td>83</td>\n",
       "      <td>70</td>\n",
       "      <td></td>\n",
       "      <td>8.123809</td>\n",
       "      <td>35.401171</td>\n",
       "      <td>877</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>aquileiaAmphitheater</td>\n",
       "      <td>Amphitheater at Aquileia</td>\n",
       "      <td>Aquileia</td>\n",
       "      <td>Aquileia</td>\n",
       "      <td>https://pleiades.stoa.org/places/187290</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>flavian</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>Italy</td>\n",
       "      <td>regio-x</td>\n",
       "      <td>72</td>\n",
       "      <td>46</td>\n",
       "      <td>142</td>\n",
       "      <td>118</td>\n",
       "      <td></td>\n",
       "      <td>13.370713</td>\n",
       "      <td>45.768239</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>terminiImereseAmphitheater</td>\n",
       "      <td>Amphitheater at Termini Imerese</td>\n",
       "      <td>Termini</td>\n",
       "      <td>Thermae Himerae</td>\n",
       "      <td>https://pleiades.stoa.org/places/462513</td>\n",
       "      <td></td>\n",
       "      <td>164</td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>flavian</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>Italy</td>\n",
       "      <td>sicilia</td>\n",
       "      <td>53</td>\n",
       "      <td>30</td>\n",
       "      <td>98</td>\n",
       "      <td>75</td>\n",
       "      <td></td>\n",
       "      <td>13.695186</td>\n",
       "      <td>37.983696</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>bolsenaAmphitheater</td>\n",
       "      <td>Amphitheater at Bolsena</td>\n",
       "      <td>Bolsena</td>\n",
       "      <td>Volsinii Novi</td>\n",
       "      <td>https://pleiades.stoa.org/places/413389</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>flavian</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>Italy</td>\n",
       "      <td>regio-vii</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>11.990158</td>\n",
       "      <td>42.650243</td>\n",
       "      <td>417</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>narbonneAmphitheater</td>\n",
       "      <td>Amphitheater at Narbonne</td>\n",
       "      <td>Narbonne</td>\n",
       "      <td>Narbo</td>\n",
       "      <td>https://pleiades.stoa.org/places/246347</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>flavian</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>France</td>\n",
       "      <td>narbonensis</td>\n",
       "      <td>75</td>\n",
       "      <td>46.6</td>\n",
       "      <td>121.6</td>\n",
       "      <td>93.2</td>\n",
       "      <td></td>\n",
       "      <td>3.010181</td>\n",
       "      <td>43.184873</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>capuaImperialAmphitheater</td>\n",
       "      <td>Imperial Amphitheater at Capua</td>\n",
       "      <td>Capua II</td>\n",
       "      <td>Capua</td>\n",
       "      <td>https://pleiades.stoa.org/places/432754</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>flavian</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>Italy</td>\n",
       "      <td>regio-i</td>\n",
       "      <td>76.12</td>\n",
       "      <td>45.83</td>\n",
       "      <td>165</td>\n",
       "      <td>135</td>\n",
       "      <td></td>\n",
       "      <td>14.250089</td>\n",
       "      <td>41.085935</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>dorchesterAmphitheater</td>\n",
       "      <td>Amphitheater at Dorchester / Maumbury Henge</td>\n",
       "      <td>Maumbury</td>\n",
       "      <td>Durnovaria</td>\n",
       "      <td>https://pleiades.stoa.org/places/79431</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>flavian</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>britannia</td>\n",
       "      <td>58</td>\n",
       "      <td>47</td>\n",
       "      <td>80</td>\n",
       "      <td>77</td>\n",
       "      <td></td>\n",
       "      <td>-2.440246</td>\n",
       "      <td>50.708044</td>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>autunAmphitheater</td>\n",
       "      <td>Amphitheater at Autun</td>\n",
       "      <td>Autun</td>\n",
       "      <td>Augustodunum</td>\n",
       "      <td>https://pleiades.stoa.org/places/177460</td>\n",
       "      <td></td>\n",
       "      <td>157</td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>flavian</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>France</td>\n",
       "      <td>lugdunensis</td>\n",
       "      <td>74</td>\n",
       "      <td>49</td>\n",
       "      <td>154</td>\n",
       "      <td>130</td>\n",
       "      <td></td>\n",
       "      <td>4.310856</td>\n",
       "      <td>46.953003</td>\n",
       "      <td>317</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>bobadelaAmphitheater</td>\n",
       "      <td>Amphitheater at Bobadela</td>\n",
       "      <td>Bobadela</td>\n",
       "      <td>Elbocoris</td>\n",
       "      <td>https://pleiades.stoa.org/places/236458</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>flavian</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>Portugal</td>\n",
       "      <td>lusitania</td>\n",
       "      <td>50</td>\n",
       "      <td>40</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>-7.893572</td>\n",
       "      <td>40.361088</td>\n",
       "      <td>383</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>chichesterAmphitheater</td>\n",
       "      <td>Amphitheater at Chichester</td>\n",
       "      <td>Chichester</td>\n",
       "      <td>Noviomagus Reginorum</td>\n",
       "      <td>https://pleiades.stoa.org/places/79622</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>amphitheater</td>\n",
       "      <td>flavian</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>britannia</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>70</td>\n",
       "      <td>60</td>\n",
       "      <td></td>\n",
       "      <td>-0.771186</td>\n",
       "      <td>50.835394</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>23 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                             id                                        title  \\\n",
       "0             arlesAmphitheater                        Amphitheater at Arles   \n",
       "1       romeFlavianAmphitheater                 Flavian Amphitheater at Rome   \n",
       "2          newsteadAmphitheater                     Amphitheater at Newstead   \n",
       "3   pozzuoliFlavianAmphitheater             Flavian Amphitheater at Pozzuoli   \n",
       "4         segobrigaAmphitheater                    Amphitheater at Segobriga   \n",
       "5             parisAmphitheater                        Amphitheater at Paris   \n",
       "6             nimesAmphitheater                        Amphitheater at Nimes   \n",
       "7           chesterAmphitheater                      Amphitheater at Chester   \n",
       "8            arezzoAmphitheater                       Amphitheater at Arezzo   \n",
       "9            londonAmphitheater                       Amphitheater at London   \n",
       "10       urbisagliaAmphitheater                  Amphitheater at Urbs Salvia   \n",
       "11       vindonissaAmphitheater                   Amphitheater at Vindonissa   \n",
       "12           frejusAmphitheater                          Amphitheater Fréjus   \n",
       "13          tebessaAmphitheater                     Amphitheater at Theveste   \n",
       "14         aquileiaAmphitheater                     Amphitheater at Aquileia   \n",
       "15   terminiImereseAmphitheater              Amphitheater at Termini Imerese   \n",
       "16          bolsenaAmphitheater                      Amphitheater at Bolsena   \n",
       "17         narbonneAmphitheater                     Amphitheater at Narbonne   \n",
       "18    capuaImperialAmphitheater               Imperial Amphitheater at Capua   \n",
       "19       dorchesterAmphitheater  Amphitheater at Dorchester / Maumbury Henge   \n",
       "20            autunAmphitheater                        Amphitheater at Autun   \n",
       "21         bobadelaAmphitheater                     Amphitheater at Bobadela   \n",
       "22       chichesterAmphitheater                   Amphitheater at Chichester   \n",
       "\n",
       "                 label          latintoponym  \\\n",
       "0                Arles               Arelate   \n",
       "1            Colosseum                         \n",
       "2             Newstead            Trimontium   \n",
       "3   Pozzuoli (Flavian)               Puteoli   \n",
       "4            Segobriga             Segobriga   \n",
       "5                Paris    Lutetia Parisiorum   \n",
       "6                Nimes              Nemausus   \n",
       "7              Chester          Deva Victrix   \n",
       "8               Arezzo              Arretium   \n",
       "9               London             Londinium   \n",
       "10         Urbs Salvia           Urbs Saglia   \n",
       "11          Vindonissa                         \n",
       "12              Fréjus           Forum Julii   \n",
       "13             Tebessa              Theveste   \n",
       "14            Aquileia              Aquileia   \n",
       "15             Termini       Thermae Himerae   \n",
       "16             Bolsena         Volsinii Novi   \n",
       "17            Narbonne                 Narbo   \n",
       "18            Capua II                 Capua   \n",
       "19            Maumbury            Durnovaria   \n",
       "20               Autun          Augustodunum   \n",
       "21            Bobadela             Elbocoris   \n",
       "22          Chichester  Noviomagus Reginorum   \n",
       "\n",
       "                                   pleiades welchid golvinid  \\\n",
       "0   https://pleiades.stoa.org/places/148217              154   \n",
       "1   https://pleiades.stoa.org/places/423025              152   \n",
       "2    https://pleiades.stoa.org/places/89304                    \n",
       "3   https://pleiades.stoa.org/places/432815              153   \n",
       "4   https://pleiades.stoa.org/places/266036               88   \n",
       "5   https://pleiades.stoa.org/places/109126                    \n",
       "6   https://pleiades.stoa.org/places/148142                    \n",
       "7    https://pleiades.stoa.org/places/79420                    \n",
       "8   https://pleiades.stoa.org/places/413032                    \n",
       "9    https://pleiades.stoa.org/places/79574                    \n",
       "10  https://pleiades.stoa.org/places/413364                    \n",
       "11  https://pleiades.stoa.org/places/177661               17   \n",
       "12  https://pleiades.stoa.org/places/157836                    \n",
       "13  https://pleiades.stoa.org/places/324831                    \n",
       "14  https://pleiades.stoa.org/places/187290                    \n",
       "15  https://pleiades.stoa.org/places/462513              164   \n",
       "16  https://pleiades.stoa.org/places/413389                    \n",
       "17  https://pleiades.stoa.org/places/246347                    \n",
       "18  https://pleiades.stoa.org/places/432754                    \n",
       "19   https://pleiades.stoa.org/places/79431                    \n",
       "20  https://pleiades.stoa.org/places/177460              157   \n",
       "21  https://pleiades.stoa.org/places/236458                    \n",
       "22   https://pleiades.stoa.org/places/79622                    \n",
       "\n",
       "                buildingtype chronogroup  secondcentury    ...      \\\n",
       "0               amphitheater     flavian              1    ...       \n",
       "1               amphitheater     flavian              1    ...       \n",
       "2               amphitheater     flavian              1    ...       \n",
       "3               amphitheater     flavian              1    ...       \n",
       "4               amphitheater     flavian              1    ...       \n",
       "5   gallo-roman-amphitheater     flavian              1    ...       \n",
       "6               amphitheater     flavian              1    ...       \n",
       "7               amphitheater     flavian              1    ...       \n",
       "8               amphitheater     flavian              1    ...       \n",
       "9               amphitheater     flavian              1    ...       \n",
       "10              amphitheater     flavian              1    ...       \n",
       "11              amphitheater     flavian              1    ...       \n",
       "12              amphitheater     flavian              1    ...       \n",
       "13              amphitheater     flavian              1    ...       \n",
       "14              amphitheater     flavian              1    ...       \n",
       "15              amphitheater     flavian              1    ...       \n",
       "16              amphitheater     flavian              1    ...       \n",
       "17              amphitheater     flavian              1    ...       \n",
       "18              amphitheater     flavian              1    ...       \n",
       "19              amphitheater     flavian              1    ...       \n",
       "20              amphitheater     flavian              1    ...       \n",
       "21              amphitheater     flavian              1    ...       \n",
       "22              amphitheater     flavian              1    ...       \n",
       "\n",
       "        modcountry        romanregion arenamajor arenaminor extmajor extminor  \\\n",
       "0           France        narbonensis         47         32      136      107   \n",
       "1            Italy            regio-i         83         48      189      156   \n",
       "2   United Kingdom          britannia         37         23       70       60   \n",
       "3            Italy            regio-i       74.8         42      149      116   \n",
       "4            Spain      tarraconensis         47         31       75       64   \n",
       "5           France        lugdunensis                                           \n",
       "6           France        narbonensis       69.1       38.4    133.4    101.4   \n",
       "7   United Kingdom          britannia       56.7       48.3     95.5     86.6   \n",
       "8            Italy          regio-vii       71.9       42.7    109.4       80   \n",
       "9   United Kingdom          britannia         60         40      100       85   \n",
       "10           Italy            regio-v         59         35       98       71   \n",
       "11     Switzerland  germania-superior         64         51      112       98   \n",
       "12          France        narbonensis       67.7       39.7    113.7     85.7   \n",
       "13         Algeria      proconsularis       52.8       39.5       83       70   \n",
       "14           Italy            regio-x         72         46      142      118   \n",
       "15           Italy            sicilia         53         30       98       75   \n",
       "16           Italy          regio-vii                                           \n",
       "17          France        narbonensis         75       46.6    121.6     93.2   \n",
       "18           Italy            regio-i      76.12      45.83      165      135   \n",
       "19  United Kingdom          britannia         58         47       80       77   \n",
       "20          France        lugdunensis         74         49      154      130   \n",
       "21        Portugal          lusitania         50         40                     \n",
       "22  United Kingdom          britannia                             70       60   \n",
       "\n",
       "   exteriorheight  longitude   latitude  elevation  \n",
       "0                   4.631111  43.677778         21  \n",
       "1              52  12.492269  41.890169         22  \n",
       "2                  -2.691900  55.602600         83  \n",
       "3                  14.125315  40.825929         41  \n",
       "4                  -2.813944  39.886018        817  \n",
       "5                   2.352855  48.845097         46  \n",
       "6                   4.359599  43.834876         50  \n",
       "7                  -2.886928  53.189079         23  \n",
       "8                  11.880370  43.460491        263  \n",
       "9                  -0.092136  51.515522         29  \n",
       "10                 13.387224  43.200877        219  \n",
       "11                  8.213605  47.476290        361  \n",
       "12                  6.728710  43.434460         12  \n",
       "13                  8.123809  35.401171        877  \n",
       "14                 13.370713  45.768239          2  \n",
       "15                 13.695186  37.983696         76  \n",
       "16                 11.990158  42.650243        417  \n",
       "17                  3.010181  43.184873         14  \n",
       "18                 14.250089  41.085935         34  \n",
       "19                 -2.440246  50.708044         78  \n",
       "20                  4.310856  46.953003        317  \n",
       "21                 -7.893572  40.361088        383  \n",
       "22                 -0.771186  50.835394         14  \n",
       "\n",
       "[23 rows x 21 columns]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = '''\n",
    "SELECT * \n",
    "FROM amphi\n",
    "WHERE chronogroup = \"flavian\";\n",
    "'''\n",
    "\n",
    "run_query(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Can you write a query that pulls only the Flavian amphitheatres in France? Hint: you'll need the AND command."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Adding some maths\n",
    "\n",
    "How many such amphitheatres are there? This is where you'd use the COUNT command. Let's count up the number of amphitheatres from the second century."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>COUNT(*)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   COUNT(*)\n",
       "0        50"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = '''\n",
    "SELECT COUNT(*)\n",
    "FROM amphi\n",
    "WHERE chronogroup = \"second-century\";\n",
    "'''\n",
    "\n",
    "run_query(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can rename that result like so:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Total Count of Second Century Amphitheatres in the DB</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Total Count of Second Century Amphitheatres in the DB\n",
       "0                                                 50    "
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = '''\n",
    "SELECT COUNT(*) AS \"Total Count of Second Century Amphitheatres in the DB\"\n",
    "FROM amphi\n",
    "WHERE chronogroup = \"second-century\";\n",
    "'''\n",
    "\n",
    "run_query(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " SUM, AVG, MIN and MAX \n",
    " \n",
    " What was the average capacity?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Average Capacity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5791.476923</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Average Capacity\n",
       "0       5791.476923"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = '''\n",
    "SELECT AVG(capacity) AS \"Average Capacity\"\n",
    "FROM amphi;\n",
    "'''\n",
    "\n",
    "run_query(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Average Length</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>32.982759</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Average Length\n",
       "0       32.982759"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = '''\n",
    "SELECT AVG(arenamajor) AS \"Average Length\"\n",
    "FROM amphi;\n",
    "'''\n",
    "\n",
    "run_query(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can group rows by one value versus another to see how they compare. Is there a difference in average length of the long axis in Julio-Claudian versus Flavian amphitheatres?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>chronogroup</th>\n",
       "      <th>Average Length</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>post-severan</td>\n",
       "      <td>62.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>fourth-century</td>\n",
       "      <td>59.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>neronian</td>\n",
       "      <td>57.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>augustan</td>\n",
       "      <td>56.125000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>flavian</td>\n",
       "      <td>54.266087</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>late-second-century</td>\n",
       "      <td>46.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>hadrianic</td>\n",
       "      <td>45.626667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>third-century</td>\n",
       "      <td>43.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>julio-claudian</td>\n",
       "      <td>43.273571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>second-century</td>\n",
       "      <td>42.856800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>severan</td>\n",
       "      <td>42.450000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>first-century</td>\n",
       "      <td>42.227778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>imperial</td>\n",
       "      <td>22.010092</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>caesarean</td>\n",
       "      <td>19.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>republican</td>\n",
       "      <td>10.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>late-1st-early-2nd</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            chronogroup  Average Length\n",
       "0          post-severan       62.750000\n",
       "1        fourth-century       59.000000\n",
       "2              neronian       57.000000\n",
       "3              augustan       56.125000\n",
       "4               flavian       54.266087\n",
       "5   late-second-century       46.333333\n",
       "6             hadrianic       45.626667\n",
       "7         third-century       43.500000\n",
       "8        julio-claudian       43.273571\n",
       "9        second-century       42.856800\n",
       "10              severan       42.450000\n",
       "11        first-century       42.227778\n",
       "12             imperial       22.010092\n",
       "13            caesarean       19.333333\n",
       "14           republican       10.250000\n",
       "15   late-1st-early-2nd        0.000000"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = '''\n",
    "SELECT chronogroup, AVG(arenamajor) AS \"Average Length\"\n",
    "FROM amphi\n",
    "GROUP BY chronogroup\n",
    "ORDER BY \"Average Length\" DESC;\n",
    "'''\n",
    "\n",
    "run_query(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## JOIN\n",
    "\n",
    "Now let's tell the database how the two tables are joined together. The `label` field in the `amphi` table contains the modern day description of the location of amphitheatres, and the `title` field in the `aqua` table contains a description of the modern day location of the aqueducts. Normally, when we join two tables, we want to perform the join on columns that are keyed together. In a sales database for instance there might a table of `orders` and another for `shipping address`, and each one contains a `customer_id` column. In such a case, we use `=` to say \n",
    "\n",
    "```\n",
    "FROM orders \n",
    "INNER JOIN shipping_address \n",
    "ON orders.customer_id = shipping_addres.customer_id\n",
    "```\n",
    "\n",
    "But archaeological data is rarely so straightforward. In our two tables here, we have to pattern match in order to make the two fields join up - there is no 'primary key' to help us know that a row in one table is talking about the same thing in another table. Instead of `=` we're going to use the [LIKE command](http://www.sqlitetutorial.net/sqlite-like/). LIKE uses two different kinds of wildcards, `%` and `_`. \n",
    "\n",
    "+ % matches any sequence of zero or more characters\n",
    "+ _ matches any single character.\n",
    "\n",
    "If we said, `LIKE 'Arl%'` we would find matches on Arles, Arlate and so on. Placing the `%` on either side would find strings that _contain_ Arl. In our case, we want to find instances in the `aqua` table's `title` column that contain strings from the `amphi` table's `label` column.\n",
    "\n",
    "To join to our first table all matching rows from our second, we do an '[inner join](http://www.sqlitetutorial.net/sqlite-inner-join/)'. The syntax generally is:\n",
    "\n",
    "```\n",
    "SELECT relevant-columns            # these will be the columns displayed in your result\n",
    "FROM tableA                        # the table to join\n",
    "INNER JOIN tableB                  # with this table\n",
    "ON tableA.title = tableB.label     # by these criteria\n",
    "```\n",
    "\n",
    "The query below displays the result of joining the `aqua` table to the `amphi` table using the `labels` column data as the middle piece in a wildcard: `%string%`, but uses the || characters to indicate we want the string values, not the literal characters amphi.label.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>label</th>\n",
       "      <th>identifier</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>mitilineAmphitheater</td>\n",
       "      <td>Mitiline</td>\n",
       "      <td>http://dare.ht.lu.se/places/43530</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     id     label                         identifier\n",
       "0  mitilineAmphitheater  Mitiline  http://dare.ht.lu.se/places/43530"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = '''\n",
    "SELECT amphi.id, amphi.label, aqua.identifier\n",
    "FROM aqua \n",
    "INNER JOIN amphi\n",
    "ON aqua.title LIKE '%' || amphi.label || '%';\n",
    "'''\n",
    "\n",
    "\n",
    "run_query(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* true confession: There is not an amphitheatre at Mitilene to our knowledge. We added one row to the table manually so that this join example would work properly. (When we retrieved the data from the Pelagios api, we only downloaded the first page of results, in order to keep the notebook light)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We've also created a small notebook that shows how to import a database into R, and to build queries for it. Once you've done that, you can pass the results as a dataframe and use the full power of R to analyze. The notebook [is here](SQLite Database and R.ipynb).\n",
    "\n",
    "This is also possible in python, of course, and we have an example [notebook here](visualizing%20results%20of%20sql%20query%20in%20python.ipynb)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}