{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "## Introduction" ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "collapsed": false, "jupyter": { "outputs_hidden": false } }, "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": 2, "metadata": { "collapsed": false, "jupyter": { "outputs_hidden": false } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite:///factbook.db\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
idcodenameareaarea_landarea_waterpopulationpopulation_growthbirth_ratedeath_ratemigration_rate
1afAfghanistan6522306522300325643422.3238.5713.891.51
2alAlbania2874827398135030292780.312.926.583.3
3agAlgeria238174123817410395421661.8423.674.310.92
4anAndorra4684680855800.128.136.960.0
5aoAngola124670012467000196253532.7838.7811.490.46
" ], "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": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT * FROM facts limit 5;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Summary Statistics" ] }, { "cell_type": "code", "execution_count": 3, "metadata": { "collapsed": false, "jupyter": { "outputs_hidden": false } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite:///factbook.db\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
min_popmax_popmin_pop_grwthmax_pop_grwth
072564900110.04.02
" ], "text/plain": [ "[(0, 7256490011, 0.0, 4.02)]" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT\n", " MIN(population) min_pop,\n", " MAX(population) max_pop, \n", " MIN(population_growth) min_pop_grwth,\n", " MAX(population_growth) max_pop_grwth \n", "FROM facts;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Exploring Outliers" ] }, { "cell_type": "code", "execution_count": 4, "metadata": { "collapsed": false, "jupyter": { "outputs_hidden": false } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite:///factbook.db\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
idcodenameareaarea_landarea_waterpopulationpopulation_growthbirth_ratedeath_ratemigration_rate
261xxWorldNoneNoneNone72564900111.0818.67.8None
" ], "text/plain": [ "[(261, 'xx', 'World', None, None, None, 7256490011, 1.08, 18.6, 7.8, None)]" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT * FROM facts\n", "WHERE population == (\n", " SELECT MAX(population) FROM facts\n", ");" ] }, { "cell_type": "code", "execution_count": 5, "metadata": { "collapsed": false, "jupyter": { "outputs_hidden": false } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite:///factbook.db\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
idcodenameareaarea_landarea_waterpopulationpopulation_growthbirth_ratedeath_ratemigration_rate
250ayAntarcticaNone280000None0NoneNoneNoneNone
" ], "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 * FROM facts\n", "WHERE population == (\n", " SELECT MIN(population) FROM facts\n", ");" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Exploring Average Population and Area" ] }, { "cell_type": "code", "execution_count": 6, "metadata": { "collapsed": false, "jupyter": { "outputs_hidden": false } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite:///factbook.db\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
avg_populationavg_area
32242666.56846473555093.546184739
" ], "text/plain": [ "[(32242666.56846473, 555093.546184739)]" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT AVG(population) avg_population, AVG(area) avg_area\n", " FROM facts\n", " WHERE name <> 'World';" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Finding Densely Populated Countries" ] }, { "cell_type": "code", "execution_count": 7, "metadata": { "collapsed": false, "jupyter": { "outputs_hidden": false } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite:///factbook.db\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
idcodenameareaarea_landarea_waterpopulationpopulation_growthbirth_ratedeath_ratemigration_rate
14bgBangladesh148460130170182901689577451.621.145.610.46
65gmGermany3570223486728350808544080.178.4711.421.24
85jaJapan377915364485134301269196590.167.939.510.0
138rpPhilippines30000029817018301009983761.6124.276.112.09
173thThailand5131205108902230679764050.3411.197.80.0
185ukUnited Kingdom2436102419301680640882220.5412.179.352.54
192vmVietnam33121031007021140943488350.9715.965.930.3
" ], "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", " (85, 'ja', 'Japan', 377915, 364485, 13430, 126919659, 0.16, 7.93, 9.51, 0.0),\n", " (138, 'rp', 'Philippines', 300000, 298170, 1830, 100998376, 1.61, 24.27, 6.11, 2.09),\n", " (173, 'th', 'Thailand', 513120, 510890, 2230, 67976405, 0.34, 11.19, 7.8, 0.0),\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": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT * FROM facts\n", "WHERE population > (\n", " SELECT AVG(population)\n", " FROM facts\n", ")\n", " AND area < (\n", " SELECT AVG(area)\n", " FROM facts\n", ")" ] } ], "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.6" } }, "nbformat": 4, "nbformat_minor": 4 }