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

# In[1]:


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


# In[2]:


get_ipython().run_cell_magic('sql', '', "SELECT * \n  FROM sqlite_master\n WHERE type = 'table';\n")


# In[4]:


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


# In[5]:


get_ipython().run_cell_magic('sql', '', 'SELECT MIN(population) minp, MAX(population) maxp, MIN(population_growth) mingr, MAX(population_growth) maxgr\n  FROM facts;\n')


# In[7]:


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


# In[18]:


get_ipython().run_cell_magic('sql', '', "SELECT name maxpop\n  FROM facts\n WHERE population = (SELECT MAX(population)\n                       FROM facts\n                     WHERE name != 'World');\n")


# In[19]:


get_ipython().run_cell_magic('sql', '', "\nSELECT AVG(population) ap, AVG(area) aa\n  FROM facts\n WHERE name != 'World';\n")


# In[15]:


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


# In[22]:


get_ipython().run_cell_magic('sql', '', 'SELECT name, ROUND(area_water/area_land,4) wlratio\n  FROM facts\n WHERE wlratio > 1  \n ORDER BY wlratio DESC;\n')


# In[28]:


get_ipython().run_cell_magic('sql', '', "SELECT name, CAST(ROUND(population_growth*population) AS INTEGER) realgrowth\n  FROM facts\n WHERE name != 'World'\n ORDER BY realgrowth DESC\n LIMIT 10;\n")


# In[36]:


get_ipython().run_cell_magic('sql', '', "SELECT name drworse, ROUND(death_rate-birth_rate,2) drspread\n  FROM facts\n WHERE name != 'World' AND birth_rate < death_rate\n ORDER BY drspread DESC;\n")


# In[ ]: