country-population.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Populated Countries\n",
    "\n",
    "## Introduction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: None@factbook.db'"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%capture\n",
    "%load_ext sql\n",
    "%sql sqlite:///factbook.db"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Overview of the data\n",
    "\n",
    "We'll begin by getting a sense of what the data looks like."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>type</th>\n",
       "        <th>name</th>\n",
       "        <th>tbl_name</th>\n",
       "        <th>rootpage</th>\n",
       "        <th>sql</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>table</td>\n",
       "        <td>sqlite_sequence</td>\n",
       "        <td>sqlite_sequence</td>\n",
       "        <td>3</td>\n",
       "        <td>CREATE TABLE sqlite_sequence(name,seq)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>table</td>\n",
       "        <td>facts</td>\n",
       "        <td>facts</td>\n",
       "        <td>47</td>\n",
       "        <td>CREATE TABLE &quot;facts&quot; (&quot;id&quot; INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, &quot;code&quot; varchar(255) NOT NULL, &quot;name&quot; varchar(255) NOT NULL, &quot;area&quot; integer, &quot;area_land&quot; integer, &quot;area_water&quot; integer, &quot;population&quot; integer, &quot;population_growth&quot; float, &quot;birth_rate&quot; float, &quot;death_rate&quot; float, &quot;migration_rate&quot; float)</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('table', 'sqlite_sequence', 'sqlite_sequence', 3, 'CREATE TABLE sqlite_sequence(name,seq)'),\n",
       " ('table', 'facts', 'facts', 47, 'CREATE TABLE \"facts\" (\"id\" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, \"code\" varchar(255) NOT NULL, \"name\" varchar(255) NOT NULL, \"area\" integer, \"a ... (4 characters truncated) ... land\" integer, \"area_water\" integer, \"population\" integer, \"population_growth\" float, \"birth_rate\" float, \"death_rate\" float, \"migration_rate\" float)')]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "  FROM sqlite_master\n",
    " WHERE type='table';"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "        <th>code</th>\n",
       "        <th>name</th>\n",
       "        <th>area</th>\n",
       "        <th>area_land</th>\n",
       "        <th>area_water</th>\n",
       "        <th>population</th>\n",
       "        <th>population_growth</th>\n",
       "        <th>birth_rate</th>\n",
       "        <th>death_rate</th>\n",
       "        <th>migration_rate</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>af</td>\n",
       "        <td>Afghanistan</td>\n",
       "        <td>652230</td>\n",
       "        <td>652230</td>\n",
       "        <td>0</td>\n",
       "        <td>32564342</td>\n",
       "        <td>2.32</td>\n",
       "        <td>38.57</td>\n",
       "        <td>13.89</td>\n",
       "        <td>1.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>al</td>\n",
       "        <td>Albania</td>\n",
       "        <td>28748</td>\n",
       "        <td>27398</td>\n",
       "        <td>1350</td>\n",
       "        <td>3029278</td>\n",
       "        <td>0.3</td>\n",
       "        <td>12.92</td>\n",
       "        <td>6.58</td>\n",
       "        <td>3.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>ag</td>\n",
       "        <td>Algeria</td>\n",
       "        <td>2381741</td>\n",
       "        <td>2381741</td>\n",
       "        <td>0</td>\n",
       "        <td>39542166</td>\n",
       "        <td>1.84</td>\n",
       "        <td>23.67</td>\n",
       "        <td>4.31</td>\n",
       "        <td>0.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>an</td>\n",
       "        <td>Andorra</td>\n",
       "        <td>468</td>\n",
       "        <td>468</td>\n",
       "        <td>0</td>\n",
       "        <td>85580</td>\n",
       "        <td>0.12</td>\n",
       "        <td>8.13</td>\n",
       "        <td>6.96</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>ao</td>\n",
       "        <td>Angola</td>\n",
       "        <td>1246700</td>\n",
       "        <td>1246700</td>\n",
       "        <td>0</td>\n",
       "        <td>19625353</td>\n",
       "        <td>2.78</td>\n",
       "        <td>38.78</td>\n",
       "        <td>11.49</td>\n",
       "        <td>0.46</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'af', 'Afghanistan', 652230, 652230, 0, 32564342, 2.32, 38.57, 13.89, 1.51),\n",
       " (2, 'al', 'Albania', 28748, 27398, 1350, 3029278, 0.3, 12.92, 6.58, 3.3),\n",
       " (3, 'ag', 'Algeria', 2381741, 2381741, 0, 39542166, 1.84, 23.67, 4.31, 0.92),\n",
       " (4, 'an', 'Andorra', 468, 468, 0, 85580, 0.12, 8.13, 6.96, 0.0),\n",
       " (5, 'ao', 'Angola', 1246700, 1246700, 0, 19625353, 2.78, 38.78, 11.49, 0.46)]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "  FROM facts\n",
    " LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here are the descriptions for some of the columns:\n",
    "\n",
    "- name - The name of the country.\n",
    "- area - The total land and sea area of the country.\n",
    "- population - The country's population.\n",
    "- population_growth- The country's population growth as a percentage.\n",
    "- birth_rate - The country's birth rate, or the number of births a year per 1,000 people.\n",
    "- death_rate - The country's death rate, or the number of death a year per 1,000 people.\n",
    "- area- The country's total area (both land and water).\n",
    "- area_land - The country's land area in square kilometers.\n",
    "- area_water - The country's waterarea in square kilometers.\n",
    "Let's start by calculating some summary statistics and see what they tell us.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>min_pop</th>\n",
       "        <th>max_pop</th>\n",
       "        <th>min_pop_growth</th>\n",
       "        <th>max_pop_growth</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>0</td>\n",
       "        <td>7256490011</td>\n",
       "        <td>0.0</td>\n",
       "        <td>4.02</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(0, 7256490011, 0.0, 4.02)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT MIN(population) AS min_pop, \n",
    "       MAX(population) AS max_pop,\n",
    "       MIN(population_growth) AS min_pop_growth,\n",
    "       MAX(population_growth) max_pop_growth\n",
    "  FROM facts;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "A few things stick out from the summary statistics in the last screen:\n",
    "\n",
    "- There's a country with a population of 0\n",
    "- There's a country with a population of 7256490011 (or more than 7.2 billion people)\n",
    "Let's use subqueries to zoom in on just these countries without using the specific values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "        <th>code</th>\n",
       "        <th>name</th>\n",
       "        <th>area</th>\n",
       "        <th>area_land</th>\n",
       "        <th>area_water</th>\n",
       "        <th>population</th>\n",
       "        <th>population_growth</th>\n",
       "        <th>birth_rate</th>\n",
       "        <th>death_rate</th>\n",
       "        <th>migration_rate</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>250</td>\n",
       "        <td>ay</td>\n",
       "        <td>Antarctica</td>\n",
       "        <td>None</td>\n",
       "        <td>280000</td>\n",
       "        <td>None</td>\n",
       "        <td>0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(250, 'ay', 'Antarctica', None, 280000, None, 0, None, None, None, None)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "  FROM facts\n",
    " WHERE population == (SELECT MIN(population)\n",
    "                        FROM facts\n",
    "                     );"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It seems like the table contains a row for Antarctica, which explains the population of 0. This seems to match the CIA Factbook [page for Antarctica](https://www.cia.gov/library/publications/the-world-factbook/geos/ay.html):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "        <th>code</th>\n",
       "        <th>name</th>\n",
       "        <th>area</th>\n",
       "        <th>area_land</th>\n",
       "        <th>area_water</th>\n",
       "        <th>population</th>\n",
       "        <th>population_growth</th>\n",
       "        <th>birth_rate</th>\n",
       "        <th>death_rate</th>\n",
       "        <th>migration_rate</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>261</td>\n",
       "        <td>xx</td>\n",
       "        <td>World</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>7256490011</td>\n",
       "        <td>1.08</td>\n",
       "        <td>18.6</td>\n",
       "        <td>7.8</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(261, 'xx', 'World', None, None, None, 7256490011, 1.08, 18.6, 7.8, None)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "  FROM facts\n",
    " WHERE population == (SELECT MAX(population)\n",
    "                        FROM facts\n",
    "                     );"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We also see that the table contains a row for the whole world, which explains the maximum population of over 7.2 billion we found earlier.\n",
    "\n",
    "Now that we know this, we should recalculate the summary statistics we calculated earlier, while excluding the row for the whole world."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>min_pop</th>\n",
       "        <th>max_pop</th>\n",
       "        <th>min_pop_growth</th>\n",
       "        <th>max_pop_growth</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>0</td>\n",
       "        <td>1367485388</td>\n",
       "        <td>0.0</td>\n",
       "        <td>4.02</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(0, 1367485388, 0.0, 4.02)]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT MIN(population) AS min_pop, \n",
    "       MAX(population) AS max_pop,\n",
    "       MIN(population_growth) AS min_pop_growth,\n",
    "       MAX(population_growth) max_pop_growth\n",
    "  FROM facts\n",
    " WHERE name != 'World';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There's a country whose population closes in on 1.4 billion!\n",
    "\n",
    "## Exploring Average population and area\n",
    "\n",
    "Let's explore density. Density depends on the population and the country's area. Let's look at the average values for these two columns.\n",
    "\n",
    "We should take care of discarding the row for the whole planet."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>avg_population</th>\n",
       "        <th>avg_area</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>32242666.56846473</td>\n",
       "        <td>555093.546184739</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(32242666.56846473, 555093.546184739)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT AVG(population) AS avg_population, AVG(area) AS avg_area\n",
    "  FROM facts\n",
    " WHERE name != 'World';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We see that the average population is around 32 million and the average area is 555 thousand square kilometers.\n",
    "\n",
    "## Finding densely populated countries\n",
    "\n",
    "To finish, we'll build on the query above to find countries that are densely populated. We'll identify countries that have:\n",
    "\n",
    "- Above average values for population.\n",
    "- Below average values for area."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "        <th>code</th>\n",
       "        <th>name</th>\n",
       "        <th>area</th>\n",
       "        <th>area_land</th>\n",
       "        <th>area_water</th>\n",
       "        <th>population</th>\n",
       "        <th>population_growth</th>\n",
       "        <th>birth_rate</th>\n",
       "        <th>death_rate</th>\n",
       "        <th>migration_rate</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>14</td>\n",
       "        <td>bg</td>\n",
       "        <td>Bangladesh</td>\n",
       "        <td>148460</td>\n",
       "        <td>130170</td>\n",
       "        <td>18290</td>\n",
       "        <td>168957745</td>\n",
       "        <td>1.6</td>\n",
       "        <td>21.14</td>\n",
       "        <td>5.61</td>\n",
       "        <td>0.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>65</td>\n",
       "        <td>gm</td>\n",
       "        <td>Germany</td>\n",
       "        <td>357022</td>\n",
       "        <td>348672</td>\n",
       "        <td>8350</td>\n",
       "        <td>80854408</td>\n",
       "        <td>0.17</td>\n",
       "        <td>8.47</td>\n",
       "        <td>11.42</td>\n",
       "        <td>1.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>80</td>\n",
       "        <td>iz</td>\n",
       "        <td>Iraq</td>\n",
       "        <td>438317</td>\n",
       "        <td>437367</td>\n",
       "        <td>950</td>\n",
       "        <td>37056169</td>\n",
       "        <td>2.93</td>\n",
       "        <td>31.45</td>\n",
       "        <td>3.77</td>\n",
       "        <td>1.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>83</td>\n",
       "        <td>it</td>\n",
       "        <td>Italy</td>\n",
       "        <td>301340</td>\n",
       "        <td>294140</td>\n",
       "        <td>7200</td>\n",
       "        <td>61855120</td>\n",
       "        <td>0.27</td>\n",
       "        <td>8.74</td>\n",
       "        <td>10.19</td>\n",
       "        <td>4.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>85</td>\n",
       "        <td>ja</td>\n",
       "        <td>Japan</td>\n",
       "        <td>377915</td>\n",
       "        <td>364485</td>\n",
       "        <td>13430</td>\n",
       "        <td>126919659</td>\n",
       "        <td>0.16</td>\n",
       "        <td>7.93</td>\n",
       "        <td>9.51</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>91</td>\n",
       "        <td>ks</td>\n",
       "        <td>Korea, South</td>\n",
       "        <td>99720</td>\n",
       "        <td>96920</td>\n",
       "        <td>2800</td>\n",
       "        <td>49115196</td>\n",
       "        <td>0.14</td>\n",
       "        <td>8.19</td>\n",
       "        <td>6.75</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>120</td>\n",
       "        <td>mo</td>\n",
       "        <td>Morocco</td>\n",
       "        <td>446550</td>\n",
       "        <td>446300</td>\n",
       "        <td>250</td>\n",
       "        <td>33322699</td>\n",
       "        <td>1.0</td>\n",
       "        <td>18.2</td>\n",
       "        <td>4.81</td>\n",
       "        <td>3.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>138</td>\n",
       "        <td>rp</td>\n",
       "        <td>Philippines</td>\n",
       "        <td>300000</td>\n",
       "        <td>298170</td>\n",
       "        <td>1830</td>\n",
       "        <td>100998376</td>\n",
       "        <td>1.61</td>\n",
       "        <td>24.27</td>\n",
       "        <td>6.11</td>\n",
       "        <td>2.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>139</td>\n",
       "        <td>pl</td>\n",
       "        <td>Poland</td>\n",
       "        <td>312685</td>\n",
       "        <td>304255</td>\n",
       "        <td>8430</td>\n",
       "        <td>38562189</td>\n",
       "        <td>0.09</td>\n",
       "        <td>9.74</td>\n",
       "        <td>10.19</td>\n",
       "        <td>0.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>163</td>\n",
       "        <td>sp</td>\n",
       "        <td>Spain</td>\n",
       "        <td>505370</td>\n",
       "        <td>498980</td>\n",
       "        <td>6390</td>\n",
       "        <td>48146134</td>\n",
       "        <td>0.89</td>\n",
       "        <td>9.64</td>\n",
       "        <td>9.04</td>\n",
       "        <td>8.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>173</td>\n",
       "        <td>th</td>\n",
       "        <td>Thailand</td>\n",
       "        <td>513120</td>\n",
       "        <td>510890</td>\n",
       "        <td>2230</td>\n",
       "        <td>67976405</td>\n",
       "        <td>0.34</td>\n",
       "        <td>11.19</td>\n",
       "        <td>7.8</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>182</td>\n",
       "        <td>ug</td>\n",
       "        <td>Uganda</td>\n",
       "        <td>241038</td>\n",
       "        <td>197100</td>\n",
       "        <td>43938</td>\n",
       "        <td>37101745</td>\n",
       "        <td>3.24</td>\n",
       "        <td>43.79</td>\n",
       "        <td>10.69</td>\n",
       "        <td>0.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>185</td>\n",
       "        <td>uk</td>\n",
       "        <td>United Kingdom</td>\n",
       "        <td>243610</td>\n",
       "        <td>241930</td>\n",
       "        <td>1680</td>\n",
       "        <td>64088222</td>\n",
       "        <td>0.54</td>\n",
       "        <td>12.17</td>\n",
       "        <td>9.35</td>\n",
       "        <td>2.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>192</td>\n",
       "        <td>vm</td>\n",
       "        <td>Vietnam</td>\n",
       "        <td>331210</td>\n",
       "        <td>310070</td>\n",
       "        <td>21140</td>\n",
       "        <td>94348835</td>\n",
       "        <td>0.97</td>\n",
       "        <td>15.96</td>\n",
       "        <td>5.93</td>\n",
       "        <td>0.3</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(14, 'bg', 'Bangladesh', 148460, 130170, 18290, 168957745, 1.6, 21.14, 5.61, 0.46),\n",
       " (65, 'gm', 'Germany', 357022, 348672, 8350, 80854408, 0.17, 8.47, 11.42, 1.24),\n",
       " (80, 'iz', 'Iraq', 438317, 437367, 950, 37056169, 2.93, 31.45, 3.77, 1.62),\n",
       " (83, 'it', 'Italy', 301340, 294140, 7200, 61855120, 0.27, 8.74, 10.19, 4.1),\n",
       " (85, 'ja', 'Japan', 377915, 364485, 13430, 126919659, 0.16, 7.93, 9.51, 0.0),\n",
       " (91, 'ks', 'Korea, South', 99720, 96920, 2800, 49115196, 0.14, 8.19, 6.75, 0.0),\n",
       " (120, 'mo', 'Morocco', 446550, 446300, 250, 33322699, 1.0, 18.2, 4.81, 3.36),\n",
       " (138, 'rp', 'Philippines', 300000, 298170, 1830, 100998376, 1.61, 24.27, 6.11, 2.09),\n",
       " (139, 'pl', 'Poland', 312685, 304255, 8430, 38562189, 0.09, 9.74, 10.19, 0.46),\n",
       " (163, 'sp', 'Spain', 505370, 498980, 6390, 48146134, 0.89, 9.64, 9.04, 8.31),\n",
       " (173, 'th', 'Thailand', 513120, 510890, 2230, 67976405, 0.34, 11.19, 7.8, 0.0),\n",
       " (182, 'ug', 'Uganda', 241038, 197100, 43938, 37101745, 3.24, 43.79, 10.69, 0.74),\n",
       " (185, 'uk', 'United Kingdom', 243610, 241930, 1680, 64088222, 0.54, 12.17, 9.35, 2.54),\n",
       " (192, 'vm', 'Vietnam', 331210, 310070, 21140, 94348835, 0.97, 15.96, 5.93, 0.3)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "  FROM facts\n",
    " WHERE (population > (SELECT AVG(population)\n",
    "                        FROM facts\n",
    "                       WHERE name != 'World'))\n",
    "   AND (area < (SELECT AVG(area)\n",
    "                  FROM facts\n",
    "                 WHERE name != 'World'));\n",
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Some of these countries are generally known to be densely populated, so we have confidence in our results!"
   ]
  }
 ],
 "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}