#!/usr/bin/env python
# coding: utf-8

# In[2]:


get_ipython().run_cell_magic('capture', '', '%load_ext sql\n%sql sqlite:///factbook.db\n')


# In[3]:


get_ipython().run_cell_magic('sql', '', 'SELECT *\n  FROM sqlite_master\n')


# In[4]:


get_ipython().run_cell_magic('sql', '', 'SELECT *\n  FROM facts\n LIMIT 5\n')


# In[6]:


get_ipython().run_cell_magic('sql', '', 'SELECT MIN(population), MAX(population),\n       MIN(population_growth), MAX(population_growth)\n  FROM facts\n')


# In[7]:


get_ipython().run_cell_magic('sql', '', 'SELECT *\n  FROM facts\n WHERE population = (SELECT MIN(population) FROM facts)\n')


# In[8]:


get_ipython().run_cell_magic('sql', '', 'SELECT *\n  FROM facts\n WHERE population = (SELECT MAX(population) FROM facts)\n')


# In[11]:


get_ipython().run_cell_magic('sql', '', 'SELECT *\n  FROM facts\n WHERE population = (SELECT MAX(population) FROM facts) OR\n       population = (SELECT MIN(population) FROM facts)\n')


# In[12]:


get_ipython().run_cell_magic('sql', '', "SELECT MIN(population), MAX(population),\n       MIN(population_growth), MAX(population_growth)\n  FROM facts\n WHERE name <> 'World'\n")


# In[17]:


get_ipython().run_cell_magic('sql', '', 'SELECT *\n  FROM facts\n WHERE population > (SELECT AVG(population) FROM facts) AND\n       area < (SELECT AVG(area) FROM facts)\n')


# In[16]:


get_ipython().run_cell_magic('sql', '', 'SELECT AVG(population) FROM facts\n')


# In[20]:


get_ipython().run_cell_magic('sql', '', "SELECT *\n  FROM facts\n WHERE population = (SELECT MAX(population) FROM facts\n                      WHERE name <> 'World')\n")


# In[22]:


get_ipython().run_cell_magic('sql', '', "SELECT *\n  FROM facts\n WHERE population = (SELECT MAX(population) FROM facts\n                      WHERE name <> 'World') OR \n       population_growth = (SELECT MAX(population_growth) FROM facts\n                      WHERE name <> 'World')\n")


# In[24]:


get_ipython().run_cell_magic('sql', '', 'SELECT *, area_water/area_land AS ratio_wl\n  FROM facts\n WHERE ratio_wl = (SELECT MAX(area_water/area_land) FROM facts)\n')


# In[25]:


get_ipython().run_cell_magic('sql', '', 'SELECT *\n  FROM facts\n WHERE area_water > area_land\n')


# In[45]:


get_ipython().run_cell_magic('sql', '', "SELECT *, ROUND((population_growth/100) * population) AS addl_people\n  FROM facts\n WHERE addl_people > (SELECT AVG((population_growth / 100) * population)\n                        FROM facts\n                       WHERE name <> 'World')\n ORDER BY addl_people\n LIMIT 10\n")


# In[46]:


get_ipython().run_cell_magic('sql', '', 'SELECT *\n  FROM facts\n WHERE death_rate > birth_rate\n')


# In[47]:


get_ipython().run_cell_magic('sql', '', "SELECT *, population/area as population_area_ratio\n  FROM facts\n WHERE population_area_ratio = (SELECT MAX(population/area) FROM facts) OR\n       (population > (SELECT AVG(population) FROM facts WHERE name <> 'World') AND\n        area < (SELECT AVG(area) FROM facts WHERE name <> 'World'))\n")


# In[ ]: