Assignment 1 - Big Data, Db and SQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Homework Assignment 1\n",
    "### The Art of Analyzing Big Data- The Data Scientist’s Toolbox "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For this homework you will need to write code that analyzes real-world datasets. The code needs to be written in Python using the [sqlite3](https://docs.python.org/2/library/sqlite3.html) package. \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Babies Names Dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Task 1 (for everyone):** Write a code that uses the  [babies names dataset](https://catalog.data.gov/dataset/baby-names-from-social-security-card-applications-data-by-state-and-district-of-#topic=developers_navigation) and creates a table named (Names) with the dataset data and the following columns: 'State', 'Gender', 'Name', 'Number' (5pt)\n",
    "**Bonus:** Load the data using a Batch INSERT SQL Query (5pt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import os\n",
    "\"\"\"\n",
    "A Db file with the babies names dataset was created before to be use with this assingment.\n",
    "\"\"\"\n",
    "Db_Path = '../datasets/'\n",
    "Db_name = 'Names1'\n",
    "\n",
    "db = sqlite3.connect(f'{Db_Path}{Db_name}.db')\n",
    "c = db.cursor()\n",
    "c.execute(\"create table Names(State, Gender, Year INT, Name, Number INT)\")\n",
    "directory = os.fsdecode(Db_Path+'babynames')\n",
    "\n",
    "for file in os.listdir(directory):\n",
    "    filename = os.fsdecode(file)\n",
    "    with open(os.path.join(directory, filename), \"r\") as input_file:\n",
    "        records = [tuple(line.rstrip('\\n').split(',')) for line in input_file]\n",
    "    c.executemany(\"insert into Names values (?,?,?,?,?);\", records)\n",
    "db.commit()\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Task 2:** Write a query that returns the statistics for the name Mary (5pt). Use the [the timeit package](https://docs.python.org/3/library/timeit.html) to measure the time it takes the query to run (5pt). [Create an index](https://www.w3schools.com/sql/sql_create_index.asp)  on the _Name_ column and use the [the timeit package](https://docs.python.org/3/library/timeit.html) to measure the time it takes the query to run with the index (10pt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3.17 s ± 116 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "%%timeit\n",
    "# Part 1 - running query without index\n",
    "query = \"\"\"SELECT Year, sum(NUMBER) as c FROM Names Where Name=='Mary' GROUP BY Year \"\"\"\n",
    "c.execute(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x14f40d1c810>"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "index_query = \"CREATE INDEX name_index ON Names(Name)\"\n",
    "c.execute(index_query)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "264 ms ± 6.8 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "%%timeit\n",
    "# Part 1- running query with index\n",
    "query = \"\"\"SELECT Year, sum(NUMBER) as c FROM Names Where Name=='Mary' GROUP BY Year \"\"\"\n",
    "c.execute(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAYkAAAD8CAYAAACCRVh7AAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDIuMi4zLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvIxREBQAAIABJREFUeJzt3X+wXGWd5/H3h3CRiw7coIEKN2SCNRFHRRJIQWYzO+PgmgRczV2UAceZpJSqWJZjicswhlmrojguUWZGpHQZKWEMM66QVQxZAWOW6FrLAJKYQAyIiTrCTbKAkwRxc0cv8bt/9NPk5KZP/7rdt/t0f15VXff0c57TOacb+tvP83yf5ygiMDMzq+S4Tp+AmZl1LwcJMzPL5SBhZma5HCTMzCyXg4SZmeVykDAzs1wOEmZmlstBwszMcjlImJlZruM7fQLNetWrXhVz5szp9GmYmRXG1q1bfx4RMxo5prBBYs6cOWzZsqXTp2FmVhiSftboMe5uMjOzXA4SZmaWy0HCzMxyOUiYmVkuBwkzM8tV2Oymolm/bQ83bHySvQfHOGNokGuWnM3I/OFOn5aZWVUOElNg/bY9XHvXDsbGDwOw5+AY1961A+CoQOFAYmbdxt1NU+CGjU++FCDKxsYPc8PGJ196Xg4kew6OERwJJOu37ZniszUzO8JBYgrsPThWs7yeQGJmNtUcJKbAGUODNcvrCSRmZlPNQWIKXLPkbAYHph1VJkpdSovWbGb9tj11BRIzs6nmIDEFRuYPc/2l5zCcvvAFRNpXHnv4o9fOOCaQDA5M45olZ0/tyZqZZSgiatfqQgsWLIgiLvC3aM1m9uR0IQ0NDiDBwUPjfZvd5Awvs/aRtDUiFjRyjFNg26jSF161MYaDY+MMDkzjM5fP68svxnpThc1s6rgl0SYTv/Cg1H104sBxHDg0XvXYocEBXv6y44/5Nd3rv7LzWlnDQ4M8sOqiDpyRWW9xS6KL5KW0vuz44xgcmHbMvqyDY+McHCsFkvKv6S0/28/Xtu7p6V/ZzvAy6z41B64lnS1pe+bxC0lXSTpV0iZJu9Lf6am+JN0kabekxySdl3mtFan+LkkrMuXnS9qRjrlJktpzuVMn74vt+bHxowax6zE2fpivPPx0z8+jcIaXWfepGSQi4smImBcR84DzgUPA14FVwP0RMRe4Pz0HuBiYmx4rgZsBJJ0KrAYuBC4AVpcDS6qzMnPc0pZcXQdV+8IbmT/MA6su4sbL5x2T0ZTncE63YC/9yq6UKuwML7POajQF9s3AjyPiZ8AyYG0qXwuMpO1lwO1R8hAwJGkmsATYFBH7I+IAsAlYmvadHBEPRmmA5PbMaxVWPV942dRYUep7n37SQMXXm5bTuOqlX9mV3o/rLz2nZ7rTzIqo0TGJK4CvpO3TI2IfQETsk3RaKh8Gns4cM5rKqpWPVigvtPIXW62B5pH5w8cs8ldpwPsd5w8fNSZRLu+1X9kT3w8z66y6g4SkE4C3A9fWqlqhLJoor3QOKyl1SzF79uwap9F5zXzhVQsuC3771J7Obpqo17O5zIqgkZbExcD3I+KZ9PwZSTNTK2Im8GwqHwXOzBw3C9ibyt80ofw7qXxWhfrHiIhbgFuglALbwLkXSl5w6adf2Z4zYdYdGhmTeBdHupoANgDlDKUVwN2Z8uUpy2kh8HzqltoILJY0PQ1YLwY2pn0vSFqYspqWZ16r763ftodFazZz1qp7XlrnqR+0alXcfn3/zFqlrpaEpJOAtwDvyxSvAdZJuhJ4Crgsld8LXALsppQJ9R6AiNgv6RPAI6nedRGxP22/H/gSMAjclx59r19+TTcyM72RbK5+ef/M2skzrlukHf3n1WYgX7Pk7J7or290Znojs689g9vsaM3MuPYqsC3QrrvK5f1qLr9+L9zFLq9bKYKay6vX4hncZpPnINEC7bqrXN4ciGlSz8y+rndmeqXl1WsFCs/gNps8B4kWaNcv1rwJeUWbfV1t8LiemenDQ4PH5ETXExQ9g9ts8rzAXwucMTRYse/7OIn12/Y0PVaQN2fiho1PVvz3uvEXcq3B42uWnF1xTCL7RV6t223Rms3sPTjGKVXuxdELYzdmneIg0QKVvuigtN7SZLNp8uZG1Ppi7RbVuuKy11btizwvCAMvlZdXzS2XZd93BwWz5jlItED5S+jqdY8e0xWU/UJs9b9XhF/I9XTF1foizwvC1bTjfTfrRw4SLTIyf5gP37m94r52jBUU5RdyXiugka6xbFDMa1FU0q1jNGZF4oHrSZg4IDuUs4LrVI4VdNsM41YNHpcHsRu50UhAV7wHZkXmlkSTKg3IDhwnBqaJ8cNHupymcqygG2cYt7prrNr4RCXd8B6YFZlnXDcpbzZv3v2p26k82zvvy7OXZhhXmqENcNLAcbxsYFru/cN76T0wa5bvcT2Fqk0C27568ZSdR96XZlYn+ubbtcx3rZbJWavuqbjO/N6DY1563KwJDhJNasWAbCtUSjGdaKrPqd3dXtUG7fM+l1MGB7quK86sCDxw3aRumc1bq5XQiXOqNjei3QPreZ+LRM8sZWI2lRwkmtQt92Ou1kro1Dl1cmHCvM/lYM5YhdNkzarzwHXB5S213YngUJY3qD9Nqrju1FQMKuedU/nf9/iE9QMvFT4Fum0eQre0aLK6cWHCSudUVuSl1s3azQPXDejGeQjlf7ubfgV348KEtWZtj40f5up1jx5V18wcJBpSa7E6O6IbFyYsn1NemmwrFmQ06zXubmpA0e505q6xyqq1XJzxZHa0uoKEpCFJX5X0Q0lPSPo9SadK2iRpV/o7PdWVpJsk7Zb0mKTzMq+zItXfJWlFpvx8STvSMTdJamSJnilTpDudVbql6lV3bmf+dd/qaLAor8H00zVv5YFVF3XkF3u18Qno3qBv1gn1tiQ+C3wzIl4LnAs8AawC7o+IucD96TnAxcDc9FgJ3Awg6VRgNXAhcAGwuhxYUp2VmeOWTu6y2qNb5kbUI2+S3YFD430/SFtu0UzL+S3SjUHfrFNqBglJJwN/ANwKEBG/joiDwDJgbaq2FhhJ28uA26PkIWBI0kxgCbApIvZHxAFgE7A07Ts5Ih6MUj7u7ZnX6ird0l1Sj2q/hruhS6XTXWEj84f52z8+tzBB36xT6hm4fjXwHPAPks4FtgIfAk6PiH0AEbFP0mmp/jDwdOb40VRWrXy0QvkxJK2k1OJg9uzZdZx663VbJlGeWquldrJLpVuyxIp08yazTqknSBwPnAd8MCIelvRZjnQtVVKpDR9NlB9bGHELcAuUJtNVO+l+V+tubp3sUummLLGiBH2zTqknSIwCoxHxcHr+VUpB4hlJM1MrYibwbKb+mZnjZwF7U/mbJpR/J5XPqlC/axRx9dDy+X1sw86j7v8M7etSqfd9KlqWmFk/qzkmERH/F3haUvlb5c3A48AGoJyhtAK4O21vAJanLKeFwPOpW2ojsFjS9DRgvRjYmPa9IGlhympannmtjquUJVSUgd+R+cNsX72YGy+f1/ZxlEbepyJliZn1u3on030Q+LKkE4CfAO+hFGDWSboSeAq4LNW9F7gE2A0cSnWJiP2SPgE8kupdFxH70/b7gS8Bg8B96dEVuqlrpFlT0aXSyPtUqSvMA8Zm3amuIBER24FKi0K9uULdAD6Q8zq3AbdVKN8CvKGec5lqvdw10sputEbeJw8YmxWHl+WooVtuLtRqrc4wavR98oCxWTF4WY4aijSBrhHVuocqyZvXUC7fc3DsmDS1XnifzPqdWxI19GrXSCPdQ3mtji0/28/Xtu55qbyczxwU8x4NRcxiM2s3B4k69FLXSPmLMG+SycTuofXb9nD1ukePuRfE2PhhvvLw08eUlwNEu28i1GrdMsHPrNu4u6mPZNNUKxGlL8dyd1K5ft7Ngjp5E6FWa7T7zaxfuCXRR/IW/YMj3URw5Ff0iQPH5davpoiD+r2cxWY2GW5J9JFqX3gT2wRj44c5cGi8Yt1qijpYnRfYArriXhxmneIg0Ufa/Qu/m1fFrcX3wDarzEGij+Sl804/aWDSry3o2E2EWiG7DHwlHp+wfuUg0Ufy7oex+m2vr3qntrLhocHcgFLEcYiJynfNy7stoscnrB954LrPVEvnvWHjk7mZT+W01ompolDccYg8vTrL3qwZbkkYcORX9I2Xz6s6w7xId+drVq/OsjdrhlsSdpR6Zpj30uTCSnp1lr1ZMxQ5E6K63YIFC2LLli2dPg0zs8KQtDUiKq3oncstCbMavKaT9TMHCbMqvKaT9TsPXJtV4TWdrN85SJhV4TWdrN/VFSQk/YukHZK2S9qSyk6VtEnSrvR3eiqXpJsk7Zb0mKTzMq+zItXfJWlFpvz89Pq707F585nMplTe3AjPmbB+0UhL4o8iYl5mZHwVcH9EzAXuT88BLgbmpsdK4GYoBRVgNXAhcAGwuhxYUp2VmeOWNn1FZi3kORPW7ybT3bQMWJu21wIjmfLbo+QhYEjSTGAJsCki9kfEAWATsDTtOzkiHoxSPu7tmdcy66h+mDxoVk292U0BfEtSAF+IiFuA0yNiH0BE7JN0Wqo7DDydOXY0lVUrH61QbtYVen3yoFk19QaJRRGxNwWCTZJ+WKVupfGEaKL82BeWVlLqlmL27NnVz9jMzCatru6miNib/j4LfJ3SmMIzqauI9PfZVH0UODNz+Cxgb43yWRXKK53HLRGxICIWzJgxo55TNzOzSagZJCS9XNJvlbeBxcAPgA1AOUNpBXB32t4ALE9ZTguB51O31EZgsaTpacB6MbAx7XtB0sKU1bQ881pmZtZB9XQ3nQ58PWWlHg/894j4pqRHgHWSrgSeAi5L9e8FLgF2A4eA9wBExH5JnwAeSfWui4j9afv9wJeAQeC+9DDrOl6iw/qNF/gzq1PevTSc7WRF0cwCf55xbVYnL9Fh/chBwqxOXqLD+pGDhFmdvESH9SMHCbM6eYkO60e+n0QOZ7HYRL6tqfUjB4kKfKMZy+MlOqzfuLupAmexmJmVOEhU4CwWM7MSB4kKnMViZlbiIFGBs1isHuu37WHRms2cteoeFq3ZzPptezp9SmYt54HrCpzFYrU4ucH6hYNEDmexWDXVkhv83431Enc3mTXByQ3WLxwkzJrg5AbrFw4SZk1wcoP1CwcJsyaMzB/m+kvPYXhoEAFDgwOcOHAcH75zuzOdrKf4pkNmk1TpZkQA008aYPXbXu+BbOsavumQWQdUynQCOHBonGvv2uFWhRWag4TZJFXLaPKaX1Z0dQcJSdMkbZP0jfT8LEkPS9ol6U5JJ6Tyl6Xnu9P+OZnXuDaVPylpSaZ8aSrbLWlV6y7PrP1qZTQ5LdaKrJGWxIeAJzLPPwV8JiLmAgeAK1P5lcCBiPgd4DOpHpJeB1wBvB5YCvy3FHimAZ8HLgZeB7wr1TUrhEqZTllOi7UiqytISJoFvBX4Ynou4CLgq6nKWmAkbS9Lz0n735zqLwPuiIhfRcRPgd3ABemxOyJ+EhG/Bu5Idc0KoZzpNDQ4cMw+p8Va0dXbkrgR+EvgN+n5K4GDEfFiej4KlFM4hoGnAdL+51P9l8onHJNXfgxJKyVtkbTlueeeq/PUzdpvZP4w21cv5sbL572UFjs8NMj1l57j7CYrtJprN0n6j8CzEbFV0pvKxRWqRo19eeWVAlXFvNyIuAW4BUopsFVO26wjvOaX9Zp6FvhbBLxd0iXAicDJlFoWQ5KOT62FWcDeVH8UOBMYlXQ8cAqwP1Nelj0mr9zMzDqoZndTRFwbEbMiYg6lgefNEfFu4NvAO1O1FcDdaXtDek7avzlKM/Y2AFek7KezgLnA94BHgLkpW+qE9G9saMnVmZnZpExmqfCPAHdI+mtgG3BrKr8V+EdJuym1IK4AiIidktYBjwMvAh+IiMMAkv4c2AhMA26LiJ2TOC8zM2sRL8thZtYnmlmWwzcdyli/bY/vRmdmluEgkfh2lGZmx/LaTUm121GamfUrB4nEt6M0MzuWg0Ti21GamR3LQSLx7SjNzI7lgeukPDjt7CYzsyMcJDK87o61klOqrRc4SJi1gVOqrVd4TMKsDZxSbb3CQcKsDZxSbb3CQcKsDZxSbb3CQYJS//GiNZs5a9U9LFqzmfXb9nT6lKzgnFJtvaLvB649wGjt4JRq6xV9HySqDTD6f2ibDKdUWy/o++4mDzCameXr+yDhAUYzs3x9HyQ8wGhmlq9mkJB0oqTvSXpU0k5JH0/lZ0l6WNIuSXdKOiGVvyw93532z8m81rWp/ElJSzLlS1PZbkmrWn+Z+UbmD3P9pecwPDSIgOGhQa6/9Bz3JZuZUd/A9a+AiyLil5IGgP8j6T7gPwOfiYg7JP09cCVwc/p7ICJ+R9IVwKeAyyW9DrgCeD1wBvC/JL0m/RufB94CjAKPSNoQEY+38Dqr8gCjmVllNVsSUfLL9HQgPQK4CPhqKl8LjKTtZek5af+bJSmV3xERv4qInwK7gQvSY3dE/CQifg3ckeqamVmH1TUmIWmapO3As8Am4MfAwYh4MVUZBco/xYeBpwHS/ueBV2bLJxyTV25mZh1W1zyJiDgMzJM0BHwd+N1K1dJf5ezLK68UqKJCGZJWAisBZs+eXeOszbqHlw23omoouykiDgLfARYCQ5LKQWYWsDdtjwJnAqT9pwD7s+UTjskrr/Tv3xIRCyJiwYwZMxo5dbOOKc/q33NwjODIrH4v/2JFUE9204zUgkDSIPAfgCeAbwPvTNVWAHen7Q3pOWn/5oiIVH5Fyn46C5gLfA94BJibsqVOoDS4vaEVF2fWDbxsuBVZPd1NM4G1kqZRCirrIuIbkh4H7pD018A24NZU/1bgHyXtptSCuAIgInZKWgc8DrwIfCB1YyHpz4GNwDTgtojY2bIrNOswz+q3IqsZJCLiMWB+hfKfUMpMmlj+b8BlOa/1SeCTFcrvBe6t43zNCueMoUH2VAgIntVvRdD3M67N2s2z+q3I+n4VWLN287LhVmQOEmZTwLP6rajc3WRmZrkcJMzMLJe7m8ymmGdfW5E4SJhNId9T3Yqmb4OEf81ZJ/ie6lY0fRkk/GvOOsWzr61o+nLg2mvpWKf4nupWNH0ZJPxrzjrFs6+taPqqu6k8DlHxZhX415y1n2dfW9H0TZCYOA4xkX/N2VTx7Gsrkr4JEpXGIcqG/WvOzKyivgkSeeMNAh5YddHUnoxZ4lRs63Z9M3DtrBLrNr6tqRVB3wQJZ5VYt3EqthVB33Q3OavEuo1Tsa0I+iZIgLNKrLv4tqZWBDW7mySdKenbkp6QtFPSh1L5qZI2SdqV/k5P5ZJ0k6Tdkh6TdF7mtVak+rskrciUny9pRzrmJklqx8WadRN3gVoR1DMm8SJwdUT8LrAQ+ICk1wGrgPsjYi5wf3oOcDEwNz1WAjdDKagAq4ELgQuA1eXAkuqszBy3dPKXZtbdRuYPc/2l5zA8NIiAocEBThw4jg/fuZ1FazZ7ANu6Qs0gERH7IuL7afsF4AlgGFgGrE3V1gIjaXsZcHuUPAQMSZoJLAE2RcT+iDgAbAKWpn0nR8SDERHA7ZnXMutpI/OHeWDVRXzm8nn86sXfcODQ+EuZTlfduZ35133LwcI6qqHsJklzgPnAw8DpEbEPSoEEOC1VGwaezhw2msqqlY9WKK/076+UtEXSlueee66RUzfranmTPQ8cGndarHVU3UFC0iuArwFXRcQvqlWtUBZNlB9bGHFLRCyIiAUzZsyodcpmhVEto8lpsdZJdQUJSQOUAsSXI+KuVPxM6ioi/X02lY8CZ2YOnwXsrVE+q0K5Wd+oldHktFjrlHqymwTcCjwREX+X2bUBKGcorQDuzpQvT1lOC4HnU3fURmCxpOlpwHoxsDHte0HSwvRvLc+8lllfqJTplOW0WOuUeuZJLAL+DNghaXsq+ytgDbBO0pXAU8Blad+9wCXAbuAQ8B6AiNgv6RPAI6nedRGxP22/H/gSMAjclx5mfaM8f+djG3ZycGz8qH1Oi7VOUimhqHgWLFgQW7Zs6fRpmLWcF/2zdpG0NSIWNHSMg4RZ93LAsFZqJkj01bIcZkUy8UZZ5VViAQcKmzJ9swqsWdF4lVjrBg4SZl3Kq8RaN3CQMOtSvlGWdQMHCbMu5VVirRt44NqsS/lGWdYNHCTMuphvlGWd5iBhVhCeM2Gd4CBhVgCeM2Gd4oFrswLwnAnrFAcJswLwnAnrFAcJswLImxsR4PthW1s5SJgVQLX7TZTHJxworB0cJMwKYGT+MNdfeg7DOS2KsfHDXHXndrcqrOWc3WRWEOU5E2etuqfyTeBpPuvJ6bWWx/eTMCuYRWs2s6eOAevhOr/sJ6bXAojSeEe9r2HF4JsOmfWBSl/qeQYHpvGO84f59g+fO6aVUG491Ao4Dhi9o5kgUXNMQtJtkp6V9INM2amSNknalf5OT+WSdJOk3ZIek3Re5pgVqf4uSSsy5edL2pGOuUmSGrkAs35Ta3wia2z8MP/00FPsOThGcKQ76qPrd3DtXTvqapGUf0Z6gLw/1TNw/SVg6YSyVcD9ETEXuD89B7gYmJseK4GboRRUgNXAhcAFwOpyYEl1VmaOm/hvmdkEI/OHeWDVRdx4+bzcrKc85cBRT0uk0rFXr3vUgaKP1AwSEfFdYP+E4mXA2rS9FhjJlN8eJQ8BQ5JmAkuATRGxPyIOAJuApWnfyRHxYJT6vW7PvJaZ1dBIq6JVDke4RdFHms1uOj0i9gFExD5Jp6XyYeDpTL3RVFatfLRCuZnVqZz11MhYRTXlMYhqyim3H9uwEwkOHhp3VlSPanUKbKXxhGiivPKLSyspdU0xe/bsZs7PrGdl7z9Rz1jDRIMD07j+0nOOeo1aAePg2PhL206/7U11ZTdJmgN8IyLekJ4/CbwptSJmAt+JiLMlfSFtfyVbr/yIiPel8i8A30mPb0fEa1P5u7L1qnF2k1m+RlsVeZlL67ft4ep1j3K4wSzIocGBl1oYp6TtA4fGmSZxOOKl/QcOjR8TiJxN1T5tyW7KsQEoZyitAO7OlC9PWU4LgedTt9RGYLGk6WnAejGwMe17QdLClNW0PPNaZtak7FiFKH3h/unC2RVvh3rj5fN4YNVFFb+MR+YP87d/fG7Dg+MHx8Y5cGicyGwDLwWbbNnE8JPNpvrwnduZs+oezyTvoJotCUlfodQSeBXwDKUspfXAOmA28BRwWUTsT1/0n6OUoXQIeE9EbEmv817gr9LLfjIi/iGVL6CUQTUI3Ad8MOpo3rglYda4Zrt26p1T0U5uYUyeJ9OZWVu1anB8shwwmjOV3U1m1ocmdmMNDQ4w/aSBKT+PbJfUVXduZ/5133J3VJu4JWFmkzbZFka5ZVBP+m2t13DrIp+7m8ysY7LjHafUmd00cX5FduyjFQGjUpZVP8/pcJAws57RqoCRpzwvpJ8ChYOEmfWkdgaMcuumH7qpmgkSvumQmXW98tIjUAoYH9uw86jZ3pNRnrvR7IzxXueWhJkVUjtbF3ljJkXn7iYz60vtHr/olcwpBwkz63v1ZFlNRpEDhoOEmVkNrZw1XrSA4SBhZlaHbPdUObtpsooQMBwkzMya0I41qaafNMDqt72+q4KFg4SZWZMqjWVUut9FI2rN/K41M73VrRIHCTOzFmt35lQtlQJNs2m5nkxnZtZiEyfyTXXAKP8brbhVbDMcJMzM6tTpgJE1Nn6YGzY+6SBhZtaN2rlUSL32TsGdAn3TITOzSRqZP8z21Yu58fJ5DA8NAqWxhHY7I/1b7eSWhJlZi1Tqjsqb+V1PdlO1bqzBgWlcs+Tstl9T1wQJSUuBzwLTgC9GxJoOn5KZWdOyAaNZeYFmKhcd7IogIWka8HngLcAo8IikDRHxeGfPzMysc1oRaCarW8YkLgB2R8RPIuLXwB3Asg6fk5lZ3+uWIDEMPJ15PprKjiJppaQtkrY899xzU3ZyZmb9qluCRKVEgGPGayLilohYEBELZsyYMQWnZWbW37olSIwCZ2aezwL2duhczMws6ZYg8QgwV9JZkk4ArgA2dPiczMz6Xtcs8CfpEuBGSimwt0XEJ2vUfw742VScWwu8Cvh5p0+izXyNvcHX2BvyrvG3I6KhvvquCRK9TNKWRldeLBpfY2/wNfaGVl5jt3Q3mZlZF3KQMDOzXA4SU+OWTp/AFPA19gZfY29o2TV6TMLMzHK5JWFmZrkcJJok6TZJz0r6QabsXEkPStoh6X9KOjmVv0XS1lS+VdJFmWPOT+W7Jd0kaSqWoa9LI9eY2T9b0i8l/UWmbKmkJ9M1rprKa6im0euT9Ma0b2faf2Iq74nPUNKApLWp/AlJ12aO6crPEEDSmZK+nc55p6QPpfJTJW2StCv9nZ7KlT6n3ZIek3Re5rVWpPq7JK3o1DVN1MQ1vjtd22OS/lnSuZnXauyzjAg/mngAfwCcB/wgU/YI8Idp+73AJ9L2fOCMtP0GYE/mmO8Bv0dpaZL7gIs7fW3NXGNm/9eA/wH8RXo+Dfgx8GrgBOBR4HWdvrYmPsPjgceAc9PzVwLTeukzBP4EuCNtnwT8CzCnmz/DdK4zgfPS9m8BPwJeB3waWJXKVwGfStuXpM9JwELg4VR+KvCT9Hd62p7e6etr8hr/XfncgYsz19jwZ+mWRJMi4rvA/gnFZwPfTdubgHekutsiorzMyE7gREkvkzQTODkiHozSJ3g7MNL+s69PI9cIIGmE0v9YOzP1u3aF3wavbzHwWEQ8mo7914g43GOfYQAvl3Q8MAj8GvgFXfwZAkTEvoj4ftp+AXiC0gKhy4C1qdpajnwuy4Dbo+QhYCh9jkuATRGxPyIOUHpvlk7hpeRq9Boj4p/TNQA8RGmpI2jis3SQaK0fAG9P25dx9HpUZe8AtkXEryh9yKOZfRVXv+0yFa9R0suBjwAfn1C/rhV+u0jeZ/gaICRtlPR9SX+ZynvmMwS+Cvw/YB/wFPA3EbGfAn2GkuZQark/DJweEfug9CULnJaq5V1PIa6zzmvMupJSywmauEYHidZ6L/ABSVspNQl/nd0p6fXAp4D3lYsqvEa3p5vlXePHgc9ExC8n1C/aNeZd3/HA7wPvTn//k6Q3U7yVQfKlAAACQklEQVTrg/xrvAA4DJwBnAVcLenVFOQaJb2CUnfnVRHxi2pVK5RFlfKu0cA1luv/EaUg8ZFyUYVqVa+xK+5M1ysi4oeUuiWQ9BrgreV9kmYBXweWR8SPU/EoR5qBUIDVb6tc44XAOyV9GhgCfiPp34CtFGiF3yrXNwr874j4edp3L6W+/n+idz7DPwG+GRHjwLOSHgAWUPrl2dWfoaQBSl+eX46Iu1LxM5JmRsS+1J30bCrPW3V6FHjThPLvtPO8G9HgNSLpjcAXKY2R/WsqbnjFbbckWkjSaenvccBHgb9Pz4eAe4BrI+KBcv3UPHxB0sKUEbMcuHvKT7wBedcYEf8+IuZExBxKCzX+14j4HAVb4Tfv+oCNwBslnZT67P8QeLyXPkNKXUwXpeyfl1Ma1P0hXf4Zpvf9VuCJiPi7zK4NQDlDaQVHPpcNwPJ0nQuB59PnuBFYLGl6yhJanMo6rtFrlDQbuAv4s4j4UaZ+459lp0fti/oAvkKp73acUnS+EvgQpayDHwFrODJZ8aOU+nq3Zx6npX0LKPUR/xj4XPmYbng0co0TjvsYKbspPb8k1f8x8F86fV3NXh/wp5QG5X8AfDpT3hOfIfAKSplpO4HHgWu6/TNM5/b7lLpMHsv8/3UJpQy0+4Fd6e+pqb6Az6dr2QEsyLzWe4Hd6fGeTl/bJK7xi8CBTN0tzX6WnnFtZma53N1kZma5HCTMzCyXg4SZmeVykDAzs1wOEmZmlstBwszMcjlImJlZLgcJMzPL9f8B4yCAKIB7yUoAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Show babies named 'Mary' distrubution over the years.\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline\n",
    "query = \"\"\"SELECT Year, sum(NUMBER) as c FROM Names Where Name=='Mary' GROUP BY Year \"\"\"\n",
    "year_list = []\n",
    "count_list = []\n",
    "for row in c.execute(query):\n",
    "    year_list.append(row[0])\n",
    "    count_list.append(row[1])\n",
    "plt.scatter(year_list, count_list)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***Question 1:*** Write a function that returns how many babies were born in a given state in a given year.\n",
    "Use it to calculate the number of babies born in LA in 1950 (15pt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "there were 48148 babies born in NY in the year 1910\n"
     ]
    }
   ],
   "source": [
    "def calc_babies_in_year_and_state(state,year):\n",
    "    query = \"\"\"SELECT sum(number) FROM Names WHERE State==? AND Year==?\"\"\"\n",
    "    print(\"there were {} babies born in {} in the year {}\".format( c.execute(query, (state, year)).fetchone()[0],state,year))\n",
    "\n",
    "calc_babies_in_year_and_state('NY',1910)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Question:** For the state of NY write code that calculates the second most popular female/male names in each decade in each state (10pt). Visualize it somehow using Matplotlib (5pt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAYYAAAD8CAYAAABzTgP2AAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDIuMi4zLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvIxREBQAAIABJREFUeJzt3Xt4VeWZ9/HvTRIi54hEi6BCR1rkEAJEQWwVkOEkw6EzWhigWNqiLbTWFhV4RRF1hrFUW7SF6iuDTB2oSn0NQqt44MLWWiQICBVMEORYiEICCQZzuN8/9kqaJUkIyU52gN/nuva1977Xs9Z+HgL8sp611l7m7oiIiJRqFOsOiIhIw6JgEBGREAWDiIiEKBhERCREwSAiIiEKBhERCVEwiIhIiIJBRERCFAwiIhISH+sOVKVNmzbeoUOHWHdDROSskpGR8Ym7J9d0/QYdDB06dGDDhg2x7oaIyFnFzD6uzfqaShIRkRAFwxc0b978lNqiRYtYunRpDHojIlL/GvRUUkNx++23x7oLIiL1RnsM1TBnzhzmz58PwIIFC+jSpQspKSmMHTsWgPXr19OvXz969uxJv3792LFjRyy7KyJSK6fdYzCzC4B1QGLQ/gV3v9/MlgA3ALlB01vdfZOZGfBLYDhwIqhvDLY1Cbg3aP+Quz8TzcHUh3nz5rFr1y4SExPJyckBoHPnzqxbt474+Hhee+01Zs2axYoVK2LcUxGRmqnOVNJJYKC755lZAvAnM/tDsOwud3/hC+2HAZ2CRx9gIdDHzFoD9wNpgAMZZpbu7kejMZD6kpKSwvjx4xk9ejSjR48GIDc3l0mTJpGZmYmZUVhYGONeiojU3GmnkjwiL3ibEDyquu3bKGBpsN47QJKZtQWGAGvc/UgQBmuAobXrfv1btWoVU6dOJSMjg969e1NUVMTs2bMZMGAAW7duZeXKlRQUFMS6myIiNVatYwxmFmdmm4DDRP5z/2uw6GEz22Jmj5lZYlBrB+wtt/q+oFZZ/axRUlLC3r17GTBgAI888gg5OTnk5eWRm5tLu3aRoSxZsiS2nRQRqaVqBYO7F7t7KtAeuMbMugEzgc7A1UBr4J6guVW0iSrqIWY2xcw2mNmG7Ozs6nQvqk6cOEH79u3LHo8++mjZsuLiYiZMmED37t3p2bMnd955J0lJSdx9993MnDmT6667juLi4nrvs4hINJl7VbNCFaxgdj+Q7+7zy9X6A9PdfYSZ/QZY6+7LgmU7gP6lD3e/LaiH2lUkLS3Na3Plc4cZq2q8bnXtnndTnX+GiMiZMLMMd0+r6fqn3WMws2QzSwpeNwEGAduD4wYEZyGNBrYGq6QD37KIvkCuux8EXgEGm9mFZnYhMDioiYhIA1Kds5LaAs+YWRyRIHnO3V82szfMLJnIFNEmoPQqsNVETlXNInK66rcB3P2ImT0IvBu0m+vuR6I3FBERiYbTBoO7bwF6VlAfWEl7B6ZWsmwxsPgM+ygiIvVIVz6LiEiIgkFEREIUDCIiEqJgEBGREAWDiIiEKBhERCREwSAiIiEKBhERCVEwiIhIiIJBRERCFAwiIhKiYBARkRAFg4iIhCgYREQkRMEgIiIhCgYREQlRMIiISIiCQUREQhQMIiISomAQEZEQBYOIiIScNhjM7AIzW29mm81sm5k9ENQ7mtlfzSzTzH5nZo2DemLwPitY3qHctmYG9R1mNqSuBiUiIjVXnT2Gk8BAd+8BpAJDzawv8F/AY+7eCTgKfCdo/x3gqLtfCTwWtMPMugBjga7AUODXZhYXzcGIiEjtnTYYPCIveJsQPBwYCLwQ1J8BRgevRwXvCZbfaGYW1Je7+0l33wVkAddEZRQiIhI11TrGYGZxZrYJOAysAXYCOe5eFDTZB7QLXrcD9gIEy3OBi8rXK1hHREQaiGoFg7sXu3sq0J7Ib/lXVdQseLZKllVWDzGzKWa2wcw2ZGdnV6d7IiISRWd0VpK75wBrgb5AkpnFB4vaAweC1/uAywCC5a2AI+XrFaxT/jOedPc0d09LTk4+k+6JiEgUVOespGQzSwpeNwEGAR8AbwL/FjSbBLwUvE4P3hMsf8PdPaiPDc5a6gh0AtZHayAiIhId8advQlvgmeAMokbAc+7+spn9DVhuZg8B7wFPB+2fBv7HzLKI7CmMBXD3bWb2HPA3oAiY6u7F0R2OiIjU1mmDwd23AD0rqH9EBWcVuXsBcHMl23oYePjMuykiIvVFVz6LiEiIgkFEREIUDCIiEqJgaECaN29+Sm3RokUsXbo0Br0RkfNVdc5Kkhi6/fbbY90FETnPaI+hgZszZw7z588HYMGCBXTp0oWUlBTGjh0LwPr16+nXrx89e/akX79+7NixI5bdFZFzgPYYziLz5s1j165dJCYmkpOTA0Dnzp1Zt24d8fHxvPbaa8yaNYsVK1bEuKcicjZTMJxFUlJSGD9+PKNHj2b06MiX2ebm5jJp0iQyMzMxMwoLC2PcSxE522kq6SyyatUqpk6dSkZGBr1796aoqIjZs2czYMAAtm7dysqVKykoKIh1N0XkLKdgOEuUlJSwd+9eBgwYwCOPPEJOTg55eXnk5ubSrl3k28uXLFkS206KyDlBU0l1pMOMVWe8Tn7+CeJbtCl73/Lq0ZR8fgJLaMLjh1ZyaNksSk7mA06zrsNISkri7rvvZtKkSTz66KMMHDgwiiMQkfOVgqEBueKelVUu/9KER06pXXvttXz44Ydl7x988MGo90tEzi+aShIRkRAFg4iIhCgYREQkRMEgIiIhCgYRkXpiZkycOLHsfVFREcnJyYwYMaLK9dauXVvWJj09nXnz5tVpP3VWkohIPWnWrBlbt27ls88+o0mTJqxZs6bsOqTqGjlyJCNHjqyjHkZoj0FEpB4NGzaMVasi1zktW7aMcePGlS3Lz89n8uTJXH311fTs2ZOXXnrplPWXLFnCtGnTAHj++efp1q0bPXr04Prrrwdg27ZtAFeZ2SYz22JmnQDM7P+ZWYaZbTOzKVX1UcEgIlKPxo4dy/LlyykoKGDLli306dOnbNnDDz/MwIEDeffdd3nzzTe56667yM/Pr3Rbc+fO5ZVXXmHz5s2kp6cDkXu4AIfcPRVIA/YFzSe7e++g9iMzu6iy7SoYRETqUUpKCrt372bZsmUMHz48tOzVV19l3rx5pKam0r9/fwoKCtizZ0+l27ruuuu49dZbeeqppyguLgYiF70Cbc3sHuAKd/8saP4jM9sMvANcBnSqbLunDQYzu8zM3jSzD4JdkDuC+hwz2x/srmwys+Hl1plpZllmtsPMhpSrDw1qWWY243SfLSJyLho5ciTTp08PTSMBuDsrVqxg06ZNbNq0iT179nDVVVdVup1Fixbx0EMPsXfvXlJTU/n000/593//d4As4DPgFTMbaGb9gUHAte7eA3gPuKCy7VZnj6EI+Km7XwX0BaaaWZdg2WPunho8VgMEy8YCXYGhwK/NLM7M4oBfAcOALsC4ctsRETlvTJ48mfvuu4/u3buH6kOGDOHxxx/H3QF47733qtzOzp076dOnD3PnzqVNmzbs3buXjz76COCkuy8A0oEUoBVw1N1PmFlnIv+XV+q0weDuB919Y/D6OPABUNVh9FHAcnc/6e67iCTXNcEjy90/cvfPgeVBWxGR80r79u254447TqnPnj2bwsJCUlJS6NatG7Nnz65yO3fddRfdu3enW7duXH/99fTo0YPf/e53AF3NbBPQGVgK/BGIN7MtwINEppMqZaXJVB1m1gFYB3QDfgLcChwDNhDZqzhqZk8A77j7b4N1ngb+EGxiqLt/N6hPBPq4+7QvfMYUYArA5Zdf3vvjjz+udv++qCbfcHqmds+7qcF9tojUrYb+79vMMtw9rabrV/vgs5k1B1YAP3b3Y8BC4J+AVOAg8PPSphWs7lXUwwX3J909zd3TkpOTq9s9ERGJkmpd4GZmCURC4Vl3/z2Aux8qt/wp4OXg7T4iR7xLtQcOBK8rq4uISANRnbOSDHga+MDdHy1Xb1uu2Rhga/A6HRhrZolm1pHIKVHrgXeBTmbW0cwaEzlAnR6dYYiISLRUZ4/hOmAi8H5wMANgFpGzilKJTAftBm4DcPdtZvYc8DciZzRNdfdiADObBrwCxAGL3X1bFMciIiJRcNpgcPc/UfHxgdVVrPMw8HAF9dVVrSex07x5c/Ly8kK1RYsW0bRpU771rW/FqFciEgv6Ej2p1O233x7rLohIDOgrMaRSc+bMYf78+QAsWLCALl26kJKSwtixYwFYv349/fr1o2fPnvTr148dO3bEsrsiEiXaY5BqmTdvHrt27SIxMZGcnBwAOnfuzLp164iPj+e1115j1qxZrFixIsY9FZHaUjBItaSkpDB+/HhGjx7N6NGjAcjNzWXSpElkZmZiZhQWFsa4lyISDZpKkmpZtWoVU6dOJSMjg969e1NUVMTs2bMZMGAAW7duZeXKlRQUFMS6myISBQoGOa2SkhL27t3LgAEDeOSRR8jJySEvL4/c3Nyyu08tWbIktp0UqabmzZtHZTvH31tN3tbXo7KthkZTSQLAiRMnaN++fdn7n/zkJ2Wvi4uLmTBhArm5ubg7d955J0lJSdx9991MmjSJRx99lIEDB8ai2yIx06Ln8NM3OkspGM5BNfmCr8vvXhl6v+Bw8KIAnpj9KnxtZtmyRTkwg8gNQT788MOy+oMPPliT7sp5qKLrZmqiNtfa/OxnP+O5557j5MmTjBkzhgceeID8/HxuueUW9u3bR3FxMbNnz+ab3/wmM2bMID09nfj4eAYPHgzxA8j507NYQhNa9fkGhUcPcOSVX1F8IhdrFEebUTOIa5ZE9u8fpKQgHy8uIun6iTTtVOW3XTcYCgYROWvV9FqbV199lczMTNavX4+7M3LkSNatW0d2djaXXnpp2T2Zc3NzOXLkCC+++CLbt2/HzMjJyeGFeX8Obe+TlfNp1fffaPqVfnjR57iXYHEJJI+5l0aJTSk+kcvf/2c6Ta7sQ+Rbhho2HWMQkZj52c9+xtVXX01KSgr3338/APn5+dx000306NGDbt26ld5fgBkzZpRdSzN9+nQgfK1NVlYWgwYNokePHvTq1YudO3eSl5fHjTfeSK9evejevTsvvfQSEAmGV199lZ49e9KrVy+2b99OZmYm3bt357XXXuOee+7hrbfeolWrVrRs2ZILLriA7373u/z+97+nadOmoTGUnDxB8fFPafqVfgBYfGMaJVwA7uSse4YDi6dx6Hf3Upz3KSX5OfXy51pb2mMQkZio7W/tXzR+/HhmzJjBmDFjKCgooKSkhMaNG/Piiy/SsmVLPvnkE/r2jUzluDszZ87ktttuO2U7GRkZrF69mpkzZzJ48GDuu+8+1q9fz+uvv87y5ct54okn4JqfnnZ8+X9bS/GJY7Sd9AssLp59CyfjxZ/X8k+tfmiPQURiIpq/tR8/fpz9+/czZswYAC644AKaNm2KuzNr1ixSUlIYNGgQ+/fvx90ZMmQIixcvLjvOsX//fg4fPsyBAwdo2rQpEyZMYPr06WzcuLHsDLzhw4fzi1/8gk2bNoU+u1FiU+JatOHEh38BwIsKKSksoORkPnFNW2Fx8RR8vIXiY4c5W2iPQURiora/tb/xxhuhbVXk2WefJTs7m4yMDBISErjiiis4duwYgwcP5oMPPuDaa68FIgfDf/vb35KVlcVdd91Fo0aNSEhIYOHChRw/fpxRo0ZRUFCAu/PYY49x/wfhz2kz4id8+sqvIgekG8XRZvRMmnXpz+EVczn4zI9pfPGXiW/dvoIeNkwKBhGJiSFDhjB79mzGjx9P8+bN2b9/PwkJCRQVFdG6dWsmTJhA8+bNWbJkCXl5eZw4cYLhw4fTt29frrzySjrMWEXOnz7EEprwxCdvkV3SnIu/cS9Nv3ItXlSIezF5m9+m6OgJOs1+lYKPt3Bozx4SkjsGZ+5dCTfNA+A4cONT2yMdG/6fZX1MS4vcHXP9+vWhvt8/YxVJXxtf9j6hdTu+NO4/Thlj24k/P6V2NlAwiEi9KioqIjExsd5/a7e4BGgUT8trRsdg1GcXBYOI1FhNrpn5/PBH5CVepN/aGzAFg4jUm+PvreZ4xkouvPF7se6KVEHBICL1pkXP4ef0V0mcK3S6qsRcRV9qtmjRIpYuXRqD3oiI9hikQdJtRUVi57R7DGZ2mZm9aWYfmNk2M7sjqLc2szVmlhk8XxjUzcwWmFmWmW0xs17ltjUpaJ9pZpPqblhyttNtRUVipzp7DEXAT919o5m1ADLMbA1wK/C6u88zsxlEvnDzHmAY0Cl49AEWAn3MrDVwP5AGeLCddHc/Gu1ByblFtxUVqV+nDQZ3PwgcDF4fN7MPgHbAKKB/0OwZYC2RYBgFLPXIpYjvmFmSmbUN2q5x9yMAQbgMBZZFcTxyDtJtRUXq1xkdfDazDkBP4K/AJUFolIbHxUGzdsDecqvtC2qV1UWqVJe3FTUzJk6cWPa+qKiI5ORkRowYAUB6ejrz5s2rdP3du3fTrVu3M/rM8tNkt956Ky+88EINei5Sd6odDGbWHFgB/Njdj1XVtIKaV1H/4udMMbMNZrYhOzu7ut2Tc1Rd31a0WbNmbN26lc8++wyANWvWlG0XYOTIkcyYMaNWY2iITheIlVm7dm0oNHPfeb5O+ymxUa1gMLMEIqHwrLv/PigfCqaICJ5LvzpwH3BZudXbAweqqIe4+5PunubuacnJyWcyFjlLld5WtPTx6KOPli0rva1o9+7d6dmzZ+i2ojNnzuS6666juLi4Vp8/bNiwsq94XrZsGePGjStbtmTJEqZNmwbAoUOHGDNmDD169KBHjx68/fbbZX383ve+R9euXRk8eHBZyOzcuZOhQ4fSu3dvvv71r7N9+/Yq+5GRkcENN9xA7969GTJkCAcPHqzVuKpyukCsjpEjR9Kq78110T2JsdMeY7DI7YaeBj5w90fLLUoHJgHzgueXytWnmdlyIgefc939oJm9AvxH6dlLwGBgJnJOORtvKzp27Fjmzp3LiBEj2LJlC5MnT+att946pd2PfvQjbrjhBl588UWKi4vJy8vj6NGjZGZmsmzZMp566iluueUWVqxYwYQJE5gyZQqLFi2iU6dO/PWvf+UHP/hB6BtBzYwvf/nLjBgxgsLCQqZNm0ZmZiZ9+/Zl4sSJTJw4kUGDBlW6x7J7925GjBjB1q1bqz3WOXPmlF03UlxczL333svPf/7zskAsHXd+fj4//OEPef/99ykqKmLOnDmMGjUqtK0lS5ZwZM0LtP7n75O//U/k/vl/wRrRKLEZXxr/X3ye/TGf/uEXeHERuJM8eiYJrdtx+PcPUXwsGy8qpEXaSFqkDq12/6V+VOespOuAicD7Zlb6ReSziATCc2b2HWAPUPqrw2pgOJAFnAC+DeDuR8zsQeDdoN3c0gPRIrGUkpLC7t27WbZsGcOHV35V7htvvFF20V1cXBytWrXi6NGjdOzYkdTUVAB69+7N7t27ycvL4+233+bmm//xG/XJkydD22vWrBk5OTmcPHmSHTt2sHnzZtyddevW8fHHH9O2bds6ncbq0KEDf/7znykoKDglEB9++GEGDhzI4sWLycnJ4ZprrmHQoEGVbiv3z8u4+Ja5xLdoQ0lB5B4HeZv+QIveI2nedQBeXIiXlABw0bA7iGvSgpLCk/x96Z00/Wo/4pq0rLNxypmrzllJf6Li4wMAN1bQ3oGplWxrMbD4TDooUh9GjhzJ9OnTWbt2LZ9++ukZrZuYmFj2Oi4ujs8++4ySkhKSkpJOuanLF7Vr146NGzeSkpJCs2bNmD59Om+99RYvv/xy2TTWE088waFDh7j99tv56KOPAFi4cCGXXnpp2TTW22+/Tbt27XjppZdo0qQJO3fuZOrUqWRnZ9O0aVOeeuopOnfuHPrs1q1bs23bNpYtW0avXr2444472L17N0OGDOHvf/876enpZQfJCwoK2LNnT+V/Bu2v4tNVv6Bp56/R9KuRW1wmtutM7tvPBbe9vJaE1pGpquMZ6WU3tSk69glFRw4Q107B0JDoKzFEgMmTJ3PffffRvXv3StvceOONLFy4EIhMwxw7Vvk5GC1btqRjx448/3zk4Ky7s3nz5lPalf7WXnoDmdJpnsLCQvbv31/WrnQaa/PmzWzcuJGuXbsCkJmZydSpU9m2bRtJSUll13JMmTKFxx9/nIyMDObPn88PfvCDCvuZlpbG9OnTycjI4IEHHuDrX/86kydPZv/+/axYsYJNmzaxadMm9uzZw1VXXVXpeC8aMo2k6ydSfPwTDv73jyj+7BjNuvQn+V9nY/GNOfzcfXz28WYK9myhYPdmvjRxPpdOfoLGl3z5rLnd5flEwSACtG/fnjvuuKPKNr/85S9588036d69O71792bbtm1Vtn/22Wd5+umn6dGjB127di27EX15rVu3Jjs7u+y4xG9+8xvWrVtHamoqWVlZZe3eeOMNvv/97wP/mMYCTjuNlZqaym233VbpgeyBAwfyve99j127djF9+nTWrVvHQw89RIsWLXj88cfL7oz23nvvVTnWwqMHSbz0qyR9fQKNmrSk+NgnFOb8nfikL9EybSRNruxD4eHdlJw8QaMLmtEo4QIKP93LyQO6Yr0h0nclyTmjJge+20z7XcXrdft+UE+G5sN4OWizu4L/3Msf/J0+fXrZ644dO/LHP/7xlPZz5swpe16yZAlz584NTWPNnz+/bCppw4YNVfY/MTGxrP+5f/0QL/yMJz/9A4VxTcgZ+nCobfk7np34vJgXMvaxKmc3CRddxskWl5J3zSSK1r/I8Zvup6TwJM+8/hRPXtwRcAb36c7LL79caT+Orl1M0ZEDgHPBFT1IuLgjx955nvxtayEujrhmF9LqunE0SriA4+/9gQOLp5HQuh2Jl361yvFJbCgYRGJs8uTJtGrViu7du7N27doK25ROY/34xz+muLiY/Pz8SrfXKLEp8a0uIX/7n2jW+Wu4O4XZu2h88ZfL2lz+kxf4ZNVjQOQGNyUnjmFxCVz8b/fjxUUU5fydi4ZOK2v/8rybAOjfvz/9+/cHIhfnzdkeOaX84jH/55R+tLr2Flpde8sp9UtueaDqPxCJOQWDSIxVdxprypQpPP3008TFxbFw4ULatm1bafuL/mU6R179NblvL4eSYppedX0oGMqzuASSR8/kyGu/oeRkPpSU0CJtJI2Tr6jVuOTspWAQiYJYTmOVbqNVn2+U1ROSvsQlt8w9pX35W2G2uenOsteNL/kyXxr/X2c8Bjk36eCziIiEKBhERCREwSAiIiEKBhERCVEwiIhIiIJBRERCFAwiIhKiYBARkRAFg4iIhCgYREQkRMEgIiIhCgYREQlRMIiISIiCQUREQhQMIiISctpgMLPFZnbYzLaWq80xs/1mtil4DC+3bKaZZZnZDjMbUq4+NKhlmdmM6A9FRESioTp7DEuAoRXUH3P31OCxGsDMugBjga7BOr82szgziwN+BQwDugDjgrYiItLAnPYObu6+zsw6VHN7o4Dl7n4S2GVmWcA1wbIsd/8IwMyWB23/dsY9FhGROlWbYwzTzGxLMNV0YVBrB+wt12ZfUKusLiIiDUxNg2Eh8E9AKnAQ+HlQtwraehX1U5jZFDPbYGYbsrOza9g9ERGpqRoFg7sfcvdidy8BnuIf00X7gMvKNW0PHKiiXtG2n3T3NHdPS05Orkn3RESkFmoUDGbWttzbMUDpGUvpwFgzSzSzjkAnYD3wLtDJzDqaWWMiB6jTa95tERGpK6c9+Gxmy4D+QBsz2wfcD/Q3s1Qi00G7gdsA3H2bmT1H5KByETDV3YuD7UwDXgHigMXuvi3qoxERkVqrzllJ4yooP11F+4eBhyuorwZWn1HvRESk3unKZxERCVEwiIhIiIJBRERCFAwiIhKiYBARkRAFg4iIhCgYREQkRMEgIiIhCgYREQlRMIiISIiCQUREQhQMIiISomAQEZEQBYOIiIQoGEREJETBICIiIQoGEREJUTCIiEiIgkFEREIUDCIiEqJgEBGRkNMGg5ktNrPDZra1XK21ma0xs8zg+cKgbma2wMyyzGyLmfUqt86koH2mmU2qm+GIiEhtVWePYQkw9Au1GcDr7t4JeD14DzAM6BQ8pgALIRIkwP1AH+Aa4P7SMBERkYbltMHg7uuAI18ojwKeCV4/A4wuV1/qEe8ASWbWFhgCrHH3I+5+FFjDqWEjIiINQE2PMVzi7gcBgueLg3o7YG+5dvuCWmX1U5jZFDPbYGYbsrOza9g9ERGpqWgffLYKal5F/dSi+5PunubuacnJyVHtnIiInF5Ng+FQMEVE8Hw4qO8DLivXrj1woIq6iIg0MDUNhnSg9MyiScBL5erfCs5O6gvkBlNNrwCDzezC4KDz4KAmIiINTPzpGpjZMqA/0MbM9hE5u2ge8JyZfQfYA9wcNF8NDAeygBPAtwHc/YiZPQi8G7Sb6+5fPKAtIiINwGmDwd3HVbLoxgraOjC1ku0sBhafUe9ERKTe6cpnEREJUTCIiEiIgkFEREIUDCIiEqJgEBGREAWDiIiEKBhERCREwSAiIiEKBhERCVEwiIhIiIJBRERCFAwiIhKiYBARkRAFg4iIhCgYREQkRMEgIiIhCgYREQlRMIiISIiCQUREQhQMIiISUqtgMLPdZva+mW0ysw1BrbWZrTGzzOD5wqBuZrbAzLLMbIuZ9YrGAEREJLqisccwwN1T3T0teD8DeN3dOwGvB+8BhgGdgscUYGEUPltERKKsLqaSRgHPBK+fAUaXqy/1iHeAJDNrWwefLyIitVDbYHDgVTPLMLMpQe0Sdz8IEDxfHNTbAXvLrbsvqImISAMSX8v1r3P3A2Z2MbDGzLZX0dYqqPkpjSIBMwXg8ssvr2X3RETkTNVqj8HdDwTPh4EXgWuAQ6VTRMHz4aD5PuCycqu3Bw5UsM0n3T3N3dOSk5Nr0z0REamBGgeDmTUzsxalr4HBwFYgHZgUNJsEvBS8Tge+FZyd1BfILZ1yEhGRhqM2U0mXAC+aWel2/tfd/2hm7wLPmdl3gD3AzUH71cBwIAs4AXy7Fp8tIiJ1pMbB4O4W1lC3AAAFHUlEQVQfAT0qqH8K3FhB3YGpNf08ERGpH7ryWUREQhQMIiISomAQEZEQBYOIiIQoGEREJETBICIiIQoGEREJUTCIiEiIgkFEREIUDCIiEqJgEBGREAWDiIiEKBhERCREwSAiIiEKBhERCVEwiIhIiIJBRERCFAwiIhKiYBARkRAFg4iIhCgYREQkpN6DwcyGmtkOM8sysxn1/fkiIlK1eg0GM4sDfgUMA7oA48ysS332QUREqlbfewzXAFnu/pG7fw4sB0bVcx9ERKQK9R0M7YC95d7vC2oiItJAmLvX34eZ3QwMcffvBu8nAte4+w/LtZkCTAnefhXYUW8dhDbAJ/X4eQ2Fxn1+OV/HDefP2K9w9+SarhwfzZ5Uwz7gsnLv2wMHyjdw9yeBJ+uzU6XMbIO7p8Xis2NJ4z6/nK/jhvN77GeivqeS3gU6mVlHM2sMjAXS67kPIiJShXrdY3D3IjObBrwCxAGL3X1bffZBRESqVt9TSbj7amB1fX9uNcVkCqsB0LjPL+fruOH8Hnu11evBZxERafj0lRgiIhJyzgeDmS02s8NmtrVcrYeZ/cXM3jezlWbWMqiPN7NN5R4lZpYaLOsdtM8yswVmZrEaU3VEY9xm1tTMVpnZdjPbZmbzYjei6onWz7vcuunlt9VQRfHveWMze9LMPgx+7v8aqzFVRxTHPS5ov8XM/mhmbWI1pgbB3c/pB3A90AvYWq72LnBD8Hoy8GAF63UHPir3fj1wLWDAH4BhsR5bXY8baAoMCF43Bt46H8ZdrvYN4H/Lb6uhPqL49/wB4KHgdSOgTazHVtfjJnKs9XDpWIFHgDmxHlssH+f8HoO7rwOOfKH8VWBd8HoNUNFvReOAZQBm1hZo6e5/8cjfnKXA6LrpcXREY9zufsLd3wxefw5sJHLtSYMVjXEDmFlz4CfAQ3XQzaiL1riJ/Ef6n8E2S9y9QV8MFqVxW/BoFswEtOQL11edb875YKjEVmBk8Ppmwhfdlfom//iL047IxXmlztav8jjTcZcxsyTgX4DX66x3dacm434Q+Dlwom67VqfOaNzBzxjgQTPbaGbPm9kldd/NqDujcbt7IfB94H0igdAFeLruu9lwna/BMBmYamYZQAvg8/ILzawPcMLdS+ctKzqecDaeznWm4y6txxP5R7TA3T+qr85G0RmNO5h3vtLdX6z3nkbXmf6844nsEf7Z3XsBfwHm12N/o+VMf94JRIKhJ3ApsAWYWa89bmDq/TqGhsDdtwODAczsK8BNX2gylvBvj/sIT6Gc8lUeZ4MajLvUk0Cmu/+ibntYN2ow7muB3ma2m8i/kYvNbK2796/73kZPDcb9KZE9pNJAfB74Th13M+pqMO7UYL2dwTrPAef1vWLOyz0GM7s4eG4E3AssKresEZHdz+WlNXc/CBw3s77BHOS3gJfqtdNRcKbjDuoPAa2AH9dfT6OrBj/vhe5+qbt3AL4GfHi2hQLUaNwOrAT6B6Ubgb/VU3ejpgZ/z/cDXcys9Evn/hn4oH562zCd88FgZsuI7BJ/1cz2mdl3iNwg6ENgO5Hf/P+73CrXA/sqmDL5PvB/gSxgJ5EzkxqsaIzbzNoD/4fInOvG4BS/79bbIGogij/vs0oUx30PMMfMtgATgZ/Wfe9rLhrjdvcDRM7GWheMOxX4j/oaQ0OkK59FRCTknN9jEBGRM6NgEBGREAWDiIiEKBhERCREwSAiIiEKBhERCVEwiIhIiIJBRERC/j+z0ny4z26gcQAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "def second_max_for_decade(state,decade,gender):\n",
    "\n",
    "    query = '''SELECT Name, Year, MAX(Number) as Second_Max\n",
    "            FROM Names\n",
    "            NATURAL JOIN \n",
    "                (SELECT Year, MAX(Number) AS Max_decade\n",
    "                FROM Names\n",
    "                WHERE State=? AND Year / 10 = ? AND Gender = ?\n",
    "                GROUP BY Year)\n",
    "            WHERE State=? AND Year / 10 = ? AND Gender = ? AND Number != Max_decade\n",
    "            GROUP BY Year'''\n",
    "    return c.execute(query,(state,decade,gender,state,decade,gender))\n",
    "\n",
    "a = second_max_for_decade('NY',197,'F')\n",
    "names,years,maxs = [], [], []\n",
    "for record in a.fetchall():\n",
    "    names.append(record[0])\n",
    "    years.append(record[1])\n",
    "    maxs.append(record[2])\n",
    "    \n",
    "fig, ax = plt.subplots()\n",
    "ax.bar(years, maxs)\n",
    "for i, txt in enumerate(names):\n",
    "    ax.annotate(txt, (years[i], maxs[i]))\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "c.close()\n",
    "db.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Flavors of Cacao Dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using the [Flavors of Cacao](https://www.kaggle.com/rombikuboktaeder/choco-flavors) dataset, answer the following questions:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Question 1:** Write a function that returns the number of bars manufactured where the bars' BroadBean Origin is a given country. Use the function to calculate the number of bars where BroadBean Origin is 'Fiji' (15 pt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "6\n"
     ]
    }
   ],
   "source": [
    "Db_Path = '../datasets/'\n",
    "Db_name = 'flavors_of_cacao'\n",
    "\n",
    "db = sqlite3.connect(f'{Db_Path}{Db_name}.db')\n",
    "c = db.cursor()\n",
    "\n",
    "def calc_num_of_bars_in_state(country):\n",
    "    query = '''Select count(DISTINCT Specific_Bean_Origin_or_Bar_Name) FROM Cacao\n",
    "                WHERE Broad_Bean_Origin=? '''\n",
    "    return c.execute(query,(country,)).fetchone()[0]\n",
    "\n",
    "print(calc_num_of_bars_in_state('Fiji'))\n",
    "c.close()\n",
    "db.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Kickstarter Projects Dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using the [Kickstarter Projects Dataset](https://www.kaggle.com/kemical/kickstarter-projects#ks-projects-201801.csv), answer the following questions:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Question 1:** On average which project category received the highest pledged USD? (15 pt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "on average the project category that received the highest pledged USD is: 3D Printing\n"
     ]
    }
   ],
   "source": [
    "Db_Path = '../datasets/'\n",
    "Db_name = 'ks18'\n",
    "\n",
    "db = sqlite3.connect(f'{Db_Path}{Db_name}.db')\n",
    "c = db.cursor()\n",
    "\n",
    "query = '''select avg(usdpledged) as avgU ,category from \n",
    "            ksProjects\n",
    "            group by category order by avgU desc limit 0,1'''\n",
    "\n",
    "print(\"on average the project category that received the highest pledged USD is: {}\".format(c.execute(query).fetchone()[1]))\n",
    "\n",
    "c.close()\n",
    "db.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Oscars Datasets"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using the [Oscars Dataset](https://www.kaggle.com/theacademy/academy-awards), answer the following questions:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Question 1:** Who is the female actress with the most Oscar nominees? (10pt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "the female actress with most oscar nominees is: Meryl Streep\n"
     ]
    }
   ],
   "source": [
    "Db_Path = '../datasets/'\n",
    "Db_name = 'Oscar'\n",
    "\n",
    "db = sqlite3.connect(f'{Db_Path}{Db_name}.db')\n",
    "c = db.cursor()\n",
    "query = '''select count(*) as cnt,Name from Oscar\n",
    "where Award like '%Actress%'\n",
    "group by Name order by cnt desc limit 0,1'''\n",
    "print(\"the female actress with most oscar nominees is: {}\".format(c.execute(query).fetchone()[1]))\n",
    "\n",
    "c.close()\n",
    "db.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Select a Dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Open Question:** Select an interesting dataset and use SQL to discover something interesting (10pt)\n",
    "\n",
    "There is a very common joke about superheros that superheroes writes, like stan lee, were lazy and a lot of the time gave their character first and last names that start with the same letter like peter parker, sue storm and more. lets see if that really the case!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "acording to the database and unlike common joke \n",
      "only 5.38% of comicbook heroes have the same first letter \n",
      "in their first and last name\n"
     ]
    }
   ],
   "source": [
    "Db_Path = '../datasets/'\n",
    "Db_name = 'marvel'\n",
    "\n",
    "db = sqlite3.connect(f'{Db_Path}{Db_name}.db')\n",
    "c = db.cursor()\n",
    "\n",
    "query1 = \"Select count(*) from marvel_dc_characters\"\n",
    "num_of_characters = c.execute(query1).fetchone()[0]\n",
    "\n",
    "query2 = \"\"\"SELECT count(*) as cnt FROM ( \n",
    "\tSELECT substr(first_name,1,1) AS sub1, substr(last_name,1,1) AS sub2\t\n",
    "\tfrom(\n",
    "\t\tSELECT \n",
    "\t\t\tsubstr(name, 1, instr(name, ' ') - 1) AS first_name,\n",
    "\t\t\tsubstr(name,    instr(name, ' ') + 1) AS last_name\n",
    "\t\tfrom marvel_dc_characters))\n",
    "where sub1=sub2\"\"\"\n",
    "num_of_characters_with_same_letter = c.execute(query2).fetchone()[0]\n",
    "\n",
    "print(\"\"\"acording to the database and unlike common joke \n",
    "only {}% of comicbook heroes have the same first letter \n",
    "in their first and last name\"\"\".format(round((num_of_characters_with_same_letter/num_of_characters)*100, 2)))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.6.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}