#!/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[7]:


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


# In[10]:


get_ipython().run_cell_magic('sql', '', 'SELECT *\nFROM facts\nLIMIT 5\n')


# In[12]:


get_ipython().run_cell_magic('sql', '', "SELECT MIN(population) AS 'Minimum Population',\n       MAX(population) AS 'Maximum Population',\n       MIN(population_growth) AS 'Minimum population growth',\n       MAX(population_growth) AS 'Maximum population growth'\n  FROM facts;\n")


# In[17]:


get_ipython().run_cell_magic('sql', '', 'SELECT name, population\n  FROM facts\n ORDER BY population DESC\n LIMIT 25;\n')


# In[16]:


get_ipython().run_cell_magic('sql', '', 'SELECT name, population\n  FROM facts\n ORDER BY population\n LIMIT 25;\n')


# In[19]:


get_ipython().run_cell_magic('sql', '', "SELECT MIN(population) AS 'Minimum Population',\n       MAX(population) AS 'Maximum Population',\n       MIN(population_growth) AS 'Minimum population growth',\n       MAX(population_growth) AS 'Maximum population growth'\n  FROM facts\n WHERE name != 'World';\n")


# In[20]:


get_ipython().run_cell_magic('sql', '', 'SELECT AVG(population) AS avg_pop,\n       AVG(area) AS avg_area\n  FROM facts;\n')


# In[15]:


get_ipython().run_cell_magic('sql', '', "SELECT name,\n       population AS pop_above_avg,\n       area AS area_below_avg\n  FROM facts\n WHERE (population > (SELECT AVG(population)\n                       FROM facts) AND\n       area < (SELECT AVG(area)\n                 FROM facts))\n       AND name != 'World';\n")


# In[31]:


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


# In[32]:


get_ipython().run_cell_magic('sql', '', "SELECT name, MAX(population_growth)\n  FROM facts\n WHERE (name != 'World' AND name != 'European Union');\n")


# In[46]:


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


# In[73]:


get_ipython().run_cell_magic('sql', '', 'SELECT name, ROUND(((CAST(area_water AS FLOAT) / CAST(area AS FLOAT)) * 100), 2) AS Ratio_of_Water \n  FROM facts\n WHERE area_water != 0\n ORDER BY Ratio_of_Water DESC;\n')


# In[74]:


get_ipython().run_cell_magic('sql', '', 'SELECT name, ROUND(((CAST(area_water AS FLOAT) / CAST(area AS FLOAT)) * 100), 2) AS Ratio_of_Water \n  FROM facts\n WHERE area_water != 0 AND Ratio_of_Water > 50.00\n ORDER BY Ratio_of_Water DESC;\n')


# In[ ]:


get_ipython().run_cell_magic('sql', '', 'SELECT name, ROUND(((CAST(area_water AS FLOAT) / CAST(area AS FLOAT)) * 100), 2) AS Ratio_of_Water \n  FROM facts\n WHERE area_water != 0 AND Ratio_of_Water > 50.00\n ORDER BY Ratio_of_Water DESC;\n')


# In[6]:


get_ipython().run_cell_magic('sql', '', 'SELECT name, population_growth\n  FROM facts\n ORDER BY population_growth DESC\n LIMIT 10;\n')


# In[8]:


get_ipython().run_cell_magic('sql', '', "SELECT name AS 'Countries with higher death rate vs birth rate'\n  FROM facts\n WHERE death_rate > birth_rate;\n")


# In[14]:


get_ipython().run_cell_magic('sql', '', 'SELECT name, ROUND((CAST(population AS FLOAT) / CAST(area AS FLOAT)), 2) AS pop_per_area\n  FROM facts\n ORDER BY pop_per_area DESC;\n')


# In[ ]: