Analyzing CIA Factbook Data Project.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Analyzing CIA Factbook Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Introduction"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the following project, I will work with [CIA World Factbook](https://www.cia.gov/the-world-factbook/countries/antarctica/) dataset to analyze the data using SQL.\n",
    "\n",
    "The Factbook dataset contains the following columns:\n",
    "\n",
    "- name - the name of the country\n",
    "- area - the country's total area (both land and area)\n",
    "- area_land — the country's land area in square kilometers.\n",
    "- area_water — the country's waterarea in square kilometers.\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 per year per 1,000 people.\n",
    "- death_rate — the country's death rate, or the number of death per year per 1,000 people.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Connect the notebook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "%load_ext sql\n",
    "%sql sqlite:///factbook.db"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Overview of the Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///factbook.db\n",
      "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",
       "    <tr>\n",
       "        <td>6</td>\n",
       "        <td>ac</td>\n",
       "        <td>Antigua and Barbuda</td>\n",
       "        <td>442</td>\n",
       "        <td>442</td>\n",
       "        <td>0</td>\n",
       "        <td>92436</td>\n",
       "        <td>1.24</td>\n",
       "        <td>15.85</td>\n",
       "        <td>5.69</td>\n",
       "        <td>2.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7</td>\n",
       "        <td>ar</td>\n",
       "        <td>Argentina</td>\n",
       "        <td>2780400</td>\n",
       "        <td>2736690</td>\n",
       "        <td>43710</td>\n",
       "        <td>43431886</td>\n",
       "        <td>0.93</td>\n",
       "        <td>16.64</td>\n",
       "        <td>7.33</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>am</td>\n",
       "        <td>Armenia</td>\n",
       "        <td>29743</td>\n",
       "        <td>28203</td>\n",
       "        <td>1540</td>\n",
       "        <td>3056382</td>\n",
       "        <td>0.15</td>\n",
       "        <td>13.61</td>\n",
       "        <td>9.34</td>\n",
       "        <td>5.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9</td>\n",
       "        <td>as</td>\n",
       "        <td>Australia</td>\n",
       "        <td>7741220</td>\n",
       "        <td>7682300</td>\n",
       "        <td>58920</td>\n",
       "        <td>22751014</td>\n",
       "        <td>1.07</td>\n",
       "        <td>12.15</td>\n",
       "        <td>7.14</td>\n",
       "        <td>5.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>au</td>\n",
       "        <td>Austria</td>\n",
       "        <td>83871</td>\n",
       "        <td>82445</td>\n",
       "        <td>1426</td>\n",
       "        <td>8665550</td>\n",
       "        <td>0.55</td>\n",
       "        <td>9.41</td>\n",
       "        <td>9.42</td>\n",
       "        <td>5.56</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),\n",
       " (6, 'ac', 'Antigua and Barbuda', 442, 442, 0, 92436, 1.24, 15.85, 5.69, 2.21),\n",
       " (7, 'ar', 'Argentina', 2780400, 2736690, 43710, 43431886, 0.93, 16.64, 7.33, 0.0),\n",
       " (8, 'am', 'Armenia', 29743, 28203, 1540, 3056382, 0.15, 13.61, 9.34, 5.8),\n",
       " (9, 'as', 'Australia', 7741220, 7682300, 58920, 22751014, 1.07, 12.15, 7.14, 5.65),\n",
       " (10, 'au', 'Austria', 83871, 82445, 1426, 8665550, 0.55, 9.41, 9.42, 5.56)]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "    FROM facts\n",
    "    LIMIT 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Summary Statistics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///factbook.db\n",
      "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, MAX(population) AS max_pop, MIN(population_growth) AS min_pop_growth, MAX(population_growth) AS max_pop_growth\n",
    "    FROM facts;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exploring Outliers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///factbook.db\n",
      "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);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///factbook.db\n",
      "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);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Summary Statistics revisited"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///factbook.db\n",
      "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, MAX(population) AS max_pop, MIN(population_growth) AS min_pop_growth, MAX(population_growth) AS max_pop_growth\n",
    "    FROM facts\n",
    "    WHERE name != 'World';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exploring Average Population and Area"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///factbook.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>avg_pop</th>\n",
       "        <th>avg_area</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>62094928.32231405</td>\n",
       "        <td>555093.546184739</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(62094928.32231405, 555093.546184739)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT AVG(population) AS avg_pop, AVG(area) AS avg_area\n",
    "    FROM facts;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///factbook.db\n",
      "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');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Most Populated Country. Highest Growth Rate Country."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///factbook.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>most_populated</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('China',)]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name AS most_populated\n",
    "    FROM facts\n",
    "    WHERE population == (SELECT MAX(population)\n",
    "                            FROM facts\n",
    "                            WHERE name <> 'World');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///factbook.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>highest_growth</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4.02</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(4.02,)]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT population_growth AS highest_growth\n",
    "    FROM facts\n",
    "    WHERE population_growth == (SELECT MAX(population_growth)\n",
    "                                    FROM facts);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///factbook.db\n",
      "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>37</td>\n",
       "        <td>ch</td>\n",
       "        <td>China</td>\n",
       "        <td>9596960</td>\n",
       "        <td>9326410</td>\n",
       "        <td>270550</td>\n",
       "        <td>1367485388</td>\n",
       "        <td>0.45</td>\n",
       "        <td>12.49</td>\n",
       "        <td>7.53</td>\n",
       "        <td>0.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>162</td>\n",
       "        <td>od</td>\n",
       "        <td>South Sudan</td>\n",
       "        <td>644329</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>12042910</td>\n",
       "        <td>4.02</td>\n",
       "        <td>36.91</td>\n",
       "        <td>8.18</td>\n",
       "        <td>11.47</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(37, 'ch', 'China', 9596960, 9326410, 270550, 1367485388, 0.45, 12.49, 7.53, 0.44),\n",
       " (162, 'od', 'South Sudan', 644329, None, None, 12042910, 4.02, 36.91, 8.18, 11.47)]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "    FROM facts\n",
    "    WHERE population == (SELECT MAX(population)\n",
    "                            FROM facts\n",
    "                            WHERE name <> 'World') \n",
    "    OR population_growth == (SELECT MAX(population_growth)\n",
    "                                  FROM facts\n",
    "                                  WHERE name <> 'World');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Countries with Highest ratios of water to land. Countries with more water than land"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///factbook.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>British Indian Ocean Territory</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Virgin Islands</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('British Indian Ocean Territory',), ('Virgin Islands',)]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name\n",
    "    FROM facts\n",
    "    WHERE area_water / area_land;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///factbook.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>British Indian Ocean Territory</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Virgin Islands</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('British Indian Ocean Territory',), ('Virgin Islands',)]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name\n",
    "    FROM facts\n",
    "    WHERE area_water > area_land\n",
    "    LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///factbook.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>area</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>British Indian Ocean Territory</td>\n",
       "        <td>54400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Virgin Islands</td>\n",
       "        <td>1910</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('British Indian Ocean Territory', 54400), ('Virgin Islands', 1910)]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name, area\n",
    "    FROM facts\n",
    "    WHERE area_water / area_land\n",
    "    AND area_water > area_land;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Countries will add most people next year."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///factbook.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>projected_population</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Zimbabwe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Zambia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Yemen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Western Sahara</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>West Bank</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Zimbabwe',), ('Zambia',), ('Yemen',), ('Western Sahara',), ('West Bank',)]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name  AS projected_population\n",
    "    FROM facts\n",
    "    WHERE population * population_growth\n",
    "    AND name != 'World'\n",
    "    ORDER BY projected_population DESC\n",
    "    LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Countries with a higher death rate than birth rate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///factbook.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>death_rate</th>\n",
       "        <th>birth_rate</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ukraine</td>\n",
       "        <td>14.46</td>\n",
       "        <td>10.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Slovenia</td>\n",
       "        <td>11.37</td>\n",
       "        <td>8.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Serbia</td>\n",
       "        <td>13.66</td>\n",
       "        <td>9.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Saint Pierre and Miquelon</td>\n",
       "        <td>9.72</td>\n",
       "        <td>7.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Russia</td>\n",
       "        <td>13.69</td>\n",
       "        <td>11.6</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Ukraine', 14.46, 10.72),\n",
       " ('Slovenia', 11.37, 8.42),\n",
       " ('Serbia', 13.66, 9.08),\n",
       " ('Saint Pierre and Miquelon', 9.72, 7.42),\n",
       " ('Russia', 13.69, 11.6)]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name, death_rate, birth_rate\n",
    "    FROM facts\n",
    "    WHERE death_rate > birth_rate\n",
    "    ORDER BY name DESC\n",
    "    LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///factbook.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>death_rate</th>\n",
       "        <th>birth_rate</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Austria</td>\n",
       "        <td>9.42</td>\n",
       "        <td>9.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Belarus</td>\n",
       "        <td>13.36</td>\n",
       "        <td>10.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bosnia and Herzegovina</td>\n",
       "        <td>9.75</td>\n",
       "        <td>8.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bulgaria</td>\n",
       "        <td>14.44</td>\n",
       "        <td>8.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Croatia</td>\n",
       "        <td>12.18</td>\n",
       "        <td>9.45</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Austria', 9.42, 9.41),\n",
       " ('Belarus', 13.36, 10.7),\n",
       " ('Bosnia and Herzegovina', 9.75, 8.87),\n",
       " ('Bulgaria', 14.44, 8.92),\n",
       " ('Croatia', 12.18, 9.45)]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name, death_rate, birth_rate\n",
    "    FROM facts\n",
    "    WHERE death_rate > birth_rate\n",
    "    ORDER BY death_rate > birth_rate DESC\n",
    "    LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Countries with a higher birth rate than death rate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///factbook.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>birth_rate</th>\n",
       "        <th>death_rate</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Gaza Strip</td>\n",
       "        <td>31.11</td>\n",
       "        <td>3.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Kuwait</td>\n",
       "        <td>19.91</td>\n",
       "        <td>2.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Iraq</td>\n",
       "        <td>31.45</td>\n",
       "        <td>3.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United Arab Emirates</td>\n",
       "        <td>15.43</td>\n",
       "        <td>1.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Oman</td>\n",
       "        <td>24.44</td>\n",
       "        <td>3.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jordan</td>\n",
       "        <td>25.37</td>\n",
       "        <td>3.79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Solomon Islands</td>\n",
       "        <td>25.77</td>\n",
       "        <td>3.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>West Bank</td>\n",
       "        <td>22.99</td>\n",
       "        <td>3.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Qatar</td>\n",
       "        <td>9.84</td>\n",
       "        <td>1.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Vanuatu</td>\n",
       "        <td>25.04</td>\n",
       "        <td>4.09</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Gaza Strip', 31.11, 3.04),\n",
       " ('Kuwait', 19.91, 2.18),\n",
       " ('Iraq', 31.45, 3.77),\n",
       " ('United Arab Emirates', 15.43, 1.97),\n",
       " ('Oman', 24.44, 3.36),\n",
       " ('Jordan', 25.37, 3.79),\n",
       " ('Solomon Islands', 25.77, 3.85),\n",
       " ('West Bank', 22.99, 3.5),\n",
       " ('Qatar', 9.84, 1.53),\n",
       " ('Vanuatu', 25.04, 4.09)]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name, birth_rate, death_rate\n",
    "    FROM facts\n",
    "    WHERE birth_rate > death_rate\n",
    "    ORDER BY birth_rate / death_rate DESC\n",
    "    LIMIT 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Countries with Highest population / area"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///factbook.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>population</th>\n",
       "        <th>area</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Macau</td>\n",
       "        <td>592731</td>\n",
       "        <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Monaco</td>\n",
       "        <td>30535</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Singapore</td>\n",
       "        <td>5674472</td>\n",
       "        <td>697</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Hong Kong</td>\n",
       "        <td>7141106</td>\n",
       "        <td>1108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Gaza Strip</td>\n",
       "        <td>1869055</td>\n",
       "        <td>360</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Gibraltar</td>\n",
       "        <td>29258</td>\n",
       "        <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bahrain</td>\n",
       "        <td>1346613</td>\n",
       "        <td>760</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Maldives</td>\n",
       "        <td>393253</td>\n",
       "        <td>298</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Malta</td>\n",
       "        <td>413965</td>\n",
       "        <td>316</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bermuda</td>\n",
       "        <td>70196</td>\n",
       "        <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sint Maarten</td>\n",
       "        <td>39689</td>\n",
       "        <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bangladesh</td>\n",
       "        <td>168957745</td>\n",
       "        <td>148460</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Macau', 592731, 28),\n",
       " ('Monaco', 30535, 2),\n",
       " ('Singapore', 5674472, 697),\n",
       " ('Hong Kong', 7141106, 1108),\n",
       " ('Gaza Strip', 1869055, 360),\n",
       " ('Gibraltar', 29258, 6),\n",
       " ('Bahrain', 1346613, 760),\n",
       " ('Maldives', 393253, 298),\n",
       " ('Malta', 413965, 316),\n",
       " ('Bermuda', 70196, 54),\n",
       " ('Sint Maarten', 39689, 34),\n",
       " ('Bangladesh', 168957745, 148460)]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name, population, area\n",
    "    FROM facts\n",
    "    WHERE population / area\n",
    "    ORDER BY population / area DESC\n",
    "    LIMIT 12;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "celltoolbar": "Raw Cell Format",
  "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.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}