SQL for Data Analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Analyzing CIA Factbook Data Using SQL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this project we will use SQL to analyze some of the data in the [CIA World Factbook](https://www.cia.gov/library/publications/the-world-factbook/). The purpose is to show how data stored in a database can be analyzed with SQL.\n",
    "\n",
    "The CIA World Factbook provides various data points about 267 world entities. Below are some examples of these data points:\n",
    "* Demographics\n",
    "* Enonomy\n",
    "* Transportation\n",
    "* Energy\n",
    "* Communications\n",
    "* Government\n",
    "* Military\n",
    "* Geography\n",
    "* History\n",
    "\n",
    "The database file in this project contains a selection of these data points. We will use SQL to answer some basic questions about the data. Most of the time the answers will be observed in cell output immediately following code cells and will not be spelled out into separate sentences to avoid wordiness and duplication."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Navigation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[(1) Connect to Database](#Connect-to-Database-Stored-Locally)\n",
    "\n",
    "[(2) List Tables](#List-Tables-Currently-In-The-Database)\n",
    "\n",
    "[(3) Inspect a Table](#Inspect-the-'facts'-Table)\n",
    "\n",
    "[(4) Calculate Summary Statistics (MIN, MAX, SUM, AVG)](#Calculate-Summary-Statistics-For-Select-Fields)\n",
    "\n",
    "[(5) Filter Data Dynamically by Using a Subquery](#View-Country-Names-With-Minimum-and-Maximum-Population)\n",
    "\n",
    "[(6) Calculate Summary Statistics With a Condition](#Calculate-Summary-Statistics-For-Select-Fields-With-Filtering)\n",
    "\n",
    "[(7) Answer Other Questions About Data](#Answer-Other-Questions-About-Data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Connect to Database Stored Locally"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "%load_ext sql\n",
    "%sql sqlite:///factbook.db"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[To Top](#Navigation)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### List Tables Currently In The Database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///factbook.db\n",
      "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",
    "\n",
    "SELECT *\n",
    "  FROM sqlite_master\n",
    " WHERE type = 'table';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[To Top](#Navigation)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Inspect the 'facts' Table"
   ]
  },
  {
   "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",
       "</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",
    "\n",
    "SELECT *\n",
    "  FROM facts\n",
    " LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This preview looks clean. The column headers are styled properly per database conventions."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The columns present in the database are:\n",
    "* `id` - unique identifier for each entry in the database\n",
    "* `code` - country code\n",
    "* `name` - country name\n",
    "* `area` - total land and water area in square kilometers\n",
    "* `area_land` - total land area in square kilometers\n",
    "* `area_water` - total water area in square kilometers\n",
    "* `population` - total population\n",
    "* `population_growth`- annual population growth as a percentage\n",
    "* `birth_rate` - number of people born per 1,000 people per year\n",
    "* `death_rate` - number of deaths per 1,000 people per year\n",
    "* `migration_rate` - migration rate. The DataQuest project instructions do not have a better description for this column. See more below."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We need to attempt to understand the `migration_rate` field more. Searching the CIA World Factbook site does not yield useful results because the database used for this project seems to have been selected by someone else who did not include the explanation for this field, or it was omitted by the author of this dataquest project. CIA has references to \"net migration\", which can be a negative or a potitive value. In this case, `migration_rate` does not appear to be the same as \"net migration\" because there are no negative values here (see cell below)."
   ]
  },
  {
   "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>name</th>\n",
       "        <th>migration_rate</th>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT name, migration_rate\n",
    "  FROM facts\n",
    " WHERE migration_rate < 0;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[To Top](#Navigation)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Calculate Summary Statistics For Select Fields "
   ]
  },
  {
   "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>MIN(population)</th>\n",
       "        <th>MAX(population)</th>\n",
       "        <th>MIN(population_growth)</th>\n",
       "        <th>MAX(population_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": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT MIN(population),\n",
    "       MAX(population),\n",
    "       MIN(population_growth),\n",
    "       MAX(population_growth)\n",
    "  FROM facts;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Minimum population is 0, which is either an error or is an entity that is not an inhabited area.\n",
    "Maximum population is over 7 billion, which appears to be the total population of the world.\n",
    "Minimum and maximum population growth seem plausible."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[To Top](#Navigation)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### View Country Names With Minimum and Maximum Population"
   ]
  },
  {
   "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>name</th>\n",
       "        <th>population</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Antarctica</td>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Antarctica', 0)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT name, population\n",
    "  FROM facts\n",
    " WHERE population == (SELECT MIN(population)\n",
    "                        FROM facts);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>population</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>World</td>\n",
       "        <td>7256490011</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('World', 7256490011)]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT name, population\n",
    "  FROM facts\n",
    " WHERE population == (SELECT MAX(population)\n",
    "                        FROM facts);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This query confirms our suspicion that not all entities in the database are countries. Some are continents or the entire world."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[To Top](#Navigation)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Calculate Summary Statistics For Select Fields With Filtering"
   ]
  },
  {
   "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>MIN(population)</th>\n",
       "        <th>MAX(population)</th>\n",
       "        <th>MIN(population_growth)</th>\n",
       "        <th>MAX(population_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": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT MIN(population),\n",
    "       MAX(population),\n",
    "       MIN(population_growth),\n",
    "       MAX(population_growth)\n",
    "  FROM facts\n",
    " WHERE population <> (SELECT MAX(population) /* filter out maximum population */\n",
    "                        FROM facts); "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///factbook.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>ROUND(AVG(population), 0)</th>\n",
       "        <th>ROUND(AVG(area), 0)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>32242667.0</td>\n",
       "        <td>582950.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(32242667.0, 582950.0)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT ROUND(AVG(population), 0), ROUND(AVG(area), 0)\n",
    "  FROM facts\n",
    " WHERE population <> (SELECT MAX(population) /* filter out maximum population */\n",
    "                        FROM facts);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[To Top](#Navigation)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Answer Other Questions About Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### See list of countries with above average population and below average area"
   ]
  },
  {
   "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>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bangladesh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Germany</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Japan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Philippines</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Thailand</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United Kingdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Vietnam</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Bangladesh',),\n",
       " ('Germany',),\n",
       " ('Japan',),\n",
       " ('Philippines',),\n",
       " ('Thailand',),\n",
       " ('United Kingdom',),\n",
       " ('Vietnam',)]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT name\n",
    "  FROM facts\n",
    " WHERE population > (SELECT AVG(population)\n",
    "                       FROM facts)\n",
    "   AND area       < (SELECT AVG(area)\n",
    "                       FROM facts);\n",
    "           "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### List 5 top countries by population (descending)"
   ]
  },
  {
   "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>name</th>\n",
       "        <th>population</th>\n",
       "        <th>population_growth</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>1367485388</td>\n",
       "        <td>0.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>India</td>\n",
       "        <td>1251695584</td>\n",
       "        <td>1.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United States</td>\n",
       "        <td>321368864</td>\n",
       "        <td>0.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Indonesia</td>\n",
       "        <td>255993674</td>\n",
       "        <td>0.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brazil</td>\n",
       "        <td>204259812</td>\n",
       "        <td>0.77</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('China', 1367485388, 0.45),\n",
       " ('India', 1251695584, 1.22),\n",
       " ('United States', 321368864, 0.78),\n",
       " ('Indonesia', 255993674, 0.92),\n",
       " ('Brazil', 204259812, 0.77)]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT name, population, population_growth\n",
    "  FROM facts\n",
    " WHERE name NOT IN ('World', 'European Union')\n",
    " ORDER BY population DESC\n",
    " LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### List 5 top countries by population growth rate (descending)"
   ]
  },
  {
   "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>name</th>\n",
       "        <th>population_growth</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>South Sudan</td>\n",
       "        <td>4.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Malawi</td>\n",
       "        <td>3.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Burundi</td>\n",
       "        <td>3.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Niger</td>\n",
       "        <td>3.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Uganda</td>\n",
       "        <td>3.24</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('South Sudan', 4.02),\n",
       " ('Malawi', 3.32),\n",
       " ('Burundi', 3.28),\n",
       " ('Niger', 3.25),\n",
       " ('Uganda', 3.24)]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT name, population_growth\n",
    "  FROM facts\n",
    " WHERE name NOT IN ('World', 'European Union')\n",
    " ORDER BY population_growth DESC\n",
    " LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### List top 5 countries with the highest ratio of water to 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",
       "        <th>water_to_land_ratio</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Virgin Islands</td>\n",
       "        <td>4.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Puerto Rico</td>\n",
       "        <td>0.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bahamas, The</td>\n",
       "        <td>0.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Guinea-Bissau</td>\n",
       "        <td>0.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Malawi</td>\n",
       "        <td>0.26</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Virgin Islands', 4.52),\n",
       " ('Puerto Rico', 0.55),\n",
       " ('Bahamas, The', 0.39),\n",
       " ('Guinea-Bissau', 0.28),\n",
       " ('Malawi', 0.26)]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT name, \n",
    "       ROUND(CAST(area_water AS Float) / CAST(area_land AS Float), 2) AS water_to_land_ratio\n",
    "  FROM facts\n",
    " WHERE name <> 'British Indian Ocean Territory'\n",
    " ORDER BY water_to_land_ratio DESC\n",
    " LIMIT 5;       "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### List all countries with more water than 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",
       "        <th>area_land</th>\n",
       "        <th>area_water</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Virgin Islands</td>\n",
       "        <td>346</td>\n",
       "        <td>1564</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Virgin Islands', 346, 1564)]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT name, area_land, area_water\n",
    "  FROM facts\n",
    " WHERE area_land < area_water\n",
    "   AND name <> 'British Indian Ocean Territory';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### List 5 countries that will add most people to their population next year"
   ]
  },
  {
   "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>population_added</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>India</td>\n",
       "        <td>15270686</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>6153684</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nigeria</td>\n",
       "        <td>4448270</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Pakistan</td>\n",
       "        <td>2906653</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ethiopia</td>\n",
       "        <td>2874562</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('India', 15270686),\n",
       " ('China', 6153684),\n",
       " ('Nigeria', 4448270),\n",
       " ('Pakistan', 2906653),\n",
       " ('Ethiopia', 2874562)]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT name, CAST(ROUND(population * population_growth / 100, 0) AS Int) AS population_added\n",
    "  FROM facts\n",
    " WHERE name <> 'World'\n",
    " ORDER BY population_added DESC\n",
    " LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It's worth noting that India will add more than 15 million people as compared to China's 6 million."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Quantify countries with higher death rate than birth rate, then list them"
   ]
  },
  {
   "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>number_of_countries_with_death_rate_higher_than_birth_rate</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>24</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(24,)]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT COUNT(*) AS number_of_countries_with_death_rate_higher_than_birth_rate\n",
    "  FROM facts\n",
    " WHERE birth_rate - death_rate < 0;"
   ]
  },
  {
   "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>birth_rate</th>\n",
       "        <th>death_rate</th>\n",
       "        <th>difference</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bulgaria</td>\n",
       "        <td>8.92</td>\n",
       "        <td>14.44</td>\n",
       "        <td>-5.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Serbia</td>\n",
       "        <td>9.08</td>\n",
       "        <td>13.66</td>\n",
       "        <td>-4.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Latvia</td>\n",
       "        <td>10.0</td>\n",
       "        <td>14.31</td>\n",
       "        <td>-4.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Lithuania</td>\n",
       "        <td>10.1</td>\n",
       "        <td>14.27</td>\n",
       "        <td>-4.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ukraine</td>\n",
       "        <td>10.72</td>\n",
       "        <td>14.46</td>\n",
       "        <td>-3.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Hungary</td>\n",
       "        <td>9.16</td>\n",
       "        <td>12.73</td>\n",
       "        <td>-3.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Germany</td>\n",
       "        <td>8.47</td>\n",
       "        <td>11.42</td>\n",
       "        <td>-2.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Slovenia</td>\n",
       "        <td>8.42</td>\n",
       "        <td>11.37</td>\n",
       "        <td>-2.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Romania</td>\n",
       "        <td>9.14</td>\n",
       "        <td>11.9</td>\n",
       "        <td>-2.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Croatia</td>\n",
       "        <td>9.45</td>\n",
       "        <td>12.18</td>\n",
       "        <td>-2.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Belarus</td>\n",
       "        <td>10.7</td>\n",
       "        <td>13.36</td>\n",
       "        <td>-2.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Monaco</td>\n",
       "        <td>6.65</td>\n",
       "        <td>9.24</td>\n",
       "        <td>-2.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Greece</td>\n",
       "        <td>8.66</td>\n",
       "        <td>11.09</td>\n",
       "        <td>-2.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Saint Pierre and Miquelon</td>\n",
       "        <td>7.42</td>\n",
       "        <td>9.72</td>\n",
       "        <td>-2.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Russia</td>\n",
       "        <td>11.6</td>\n",
       "        <td>13.69</td>\n",
       "        <td>-2.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Estonia</td>\n",
       "        <td>10.51</td>\n",
       "        <td>12.4</td>\n",
       "        <td>-1.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Portugal</td>\n",
       "        <td>9.27</td>\n",
       "        <td>11.02</td>\n",
       "        <td>-1.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Japan</td>\n",
       "        <td>7.93</td>\n",
       "        <td>9.51</td>\n",
       "        <td>-1.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>8.74</td>\n",
       "        <td>10.19</td>\n",
       "        <td>-1.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bosnia and Herzegovina</td>\n",
       "        <td>8.87</td>\n",
       "        <td>9.75</td>\n",
       "        <td>-0.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Czech Republic</td>\n",
       "        <td>9.63</td>\n",
       "        <td>10.34</td>\n",
       "        <td>-0.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Moldova</td>\n",
       "        <td>12.0</td>\n",
       "        <td>12.59</td>\n",
       "        <td>-0.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Poland</td>\n",
       "        <td>9.74</td>\n",
       "        <td>10.19</td>\n",
       "        <td>-0.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Austria</td>\n",
       "        <td>9.41</td>\n",
       "        <td>9.42</td>\n",
       "        <td>-0.01</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Bulgaria', 8.92, 14.44, -5.52),\n",
       " ('Serbia', 9.08, 13.66, -4.58),\n",
       " ('Latvia', 10.0, 14.31, -4.31),\n",
       " ('Lithuania', 10.1, 14.27, -4.17),\n",
       " ('Ukraine', 10.72, 14.46, -3.74),\n",
       " ('Hungary', 9.16, 12.73, -3.57),\n",
       " ('Germany', 8.47, 11.42, -2.95),\n",
       " ('Slovenia', 8.42, 11.37, -2.95),\n",
       " ('Romania', 9.14, 11.9, -2.76),\n",
       " ('Croatia', 9.45, 12.18, -2.73),\n",
       " ('Belarus', 10.7, 13.36, -2.66),\n",
       " ('Monaco', 6.65, 9.24, -2.59),\n",
       " ('Greece', 8.66, 11.09, -2.43),\n",
       " ('Saint Pierre and Miquelon', 7.42, 9.72, -2.3),\n",
       " ('Russia', 11.6, 13.69, -2.09),\n",
       " ('Estonia', 10.51, 12.4, -1.89),\n",
       " ('Portugal', 9.27, 11.02, -1.75),\n",
       " ('Japan', 7.93, 9.51, -1.58),\n",
       " ('Italy', 8.74, 10.19, -1.45),\n",
       " ('Bosnia and Herzegovina', 8.87, 9.75, -0.88),\n",
       " ('Czech Republic', 9.63, 10.34, -0.71),\n",
       " ('Moldova', 12.0, 12.59, -0.59),\n",
       " ('Poland', 9.74, 10.19, -0.45),\n",
       " ('Austria', 9.41, 9.42, -0.01)]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT name, birth_rate, death_rate, ROUND(birth_rate - death_rate, 2) AS difference\n",
    "  FROM facts\n",
    " WHERE difference < 0\n",
    " ORDER BY difference;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### List 5 counties with the highest population density"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>population</th>\n",
       "        <th>area_land</th>\n",
       "        <th>population_density</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Macau</td>\n",
       "        <td>592731</td>\n",
       "        <td>28</td>\n",
       "        <td>21168</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Monaco</td>\n",
       "        <td>30535</td>\n",
       "        <td>2</td>\n",
       "        <td>15267</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Singapore</td>\n",
       "        <td>5674472</td>\n",
       "        <td>687</td>\n",
       "        <td>8259</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Hong Kong</td>\n",
       "        <td>7141106</td>\n",
       "        <td>1073</td>\n",
       "        <td>6655</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Gaza Strip</td>\n",
       "        <td>1869055</td>\n",
       "        <td>360</td>\n",
       "        <td>5191</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Macau', 592731, 28, 21168),\n",
       " ('Monaco', 30535, 2, 15267),\n",
       " ('Singapore', 5674472, 687, 8259),\n",
       " ('Hong Kong', 7141106, 1073, 6655),\n",
       " ('Gaza Strip', 1869055, 360, 5191)]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT name, population, area_land, population / area_land AS population_density\n",
    "  FROM facts\n",
    " ORDER BY population_density DESC\n",
    " LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "These results are different from above where we listed [countries with above average population and below average areas.](#See-list-of-countries-with-above-average-population-and-below-average-area) This is because the highest density countries tend to be very small in land area and never above average in total population."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[To Top](#Navigation)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Conclusion"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This project demonstrated simple SQL usage to answer basic questions about a dataset containing mostly numeric data."
   ]
  }
 ],
 "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": 4
}