Lab2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Before you turn this problem in, make sure everything runs as expected. First, **restart the kernel** (in the menubar, select Kernel$\\rightarrow$Restart) and then **run all cells** (in the menubar, select Cell$\\rightarrow$Run All).\n",
    "\n",
    "Make sure you fill in any place that says `YOUR CODE HERE` or \"YOUR ANSWER HERE\", as well as your name and collaborators below:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "NAME = \"Sreya Dhar\"\n",
    "COLLABORATORS = \"\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "markdown",
     "checksum": "dc866d270ec6fe8e4c1e9658b13c6ae3",
     "grade": false,
     "grade_id": "cell-ae2cf18f5c0fc6b2",
     "locked": true,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "source": [
    "# Lab 2\n",
    "\n",
    "\n",
    "### Due Monday 12/14/2020 @ 11:59 PM EST (New York) \n",
    "\n",
    "\n",
    "\n",
    "\n",
    "### Objectives of Lab 2\n",
    "1. Parse a large 'messy' file\n",
    "1. Create a normalized database from a large 'messy' file\n",
    "1. Optimize code to load 600,000+ rows into a database quickly\n",
    "1. Practice SQL queries\n",
    "1. Create plots to visualize the data -- I have already done this for you!\n",
    "\n",
    "\n",
    "### Description of data\n",
    "Open `lab2_data.csv` file and understand its contents. The file consists\n",
    "of 11 columns separated by tab and each row is a customer with all of the products they have ordered. The columns are:\n",
    "1. Name -- The `Name` column is made up of the customer `FirstName` and `LastName` separated by a space \n",
    "1. Address -- The `Address` column has the customer address \n",
    "1. City -- The `City` column has the customer city \n",
    "1. Country -- The `Country` column has the customer country \n",
    "1. Region -- The `Region` column has the customer region \n",
    "1. ProductName* \n",
    "1. ProductCategory*\n",
    "1. ProductCategoryDescription*\n",
    "1. ProductUnitPrice*\n",
    "1. QuantityOrderded*\n",
    "1. OrderDate*\n",
    "\n",
    "All the `*` columns are linked, meaning if you split the columns on a semicolon and zip them up, you will get one product name, product category, product category description, product unit price, quantity ordered, and ordered date. Remember, each row/line in the data represents a customer and all their product orders. The product orders are separated by a semicolon. \n",
    "\n",
    "In this lab you will parse the data file and create a normalized database with six tables. To make this lab manageable, I will lay down the steps you need to create the following tables:\n",
    "```SQL\n",
    "Region \n",
    "  [RegionID] Integer not null primary key\n",
    "  [Region] Text not null\n",
    "Country\n",
    "  [CountryID] integer not null Primary key\n",
    "  [Country] Text not null\n",
    "Customer\n",
    "  [CustomerID] integer not null Primary Key\n",
    "  [FirstName] Text not null\n",
    "  [LastName] Text not null\n",
    "  [Address] Text not null\n",
    "  [City] Text not null\n",
    "  [CountryID] integer not null foreign key to Country table\n",
    "  [RegionID] integer not null foreign key to Region table\n",
    "ProductCateogry\n",
    "  [ProductCategoryID] integer not null Primary Key\n",
    "  [ProductCategory] Text not null\n",
    "  [ProductCategoryDescription] Text not null\n",
    "Product\n",
    "  [ProductID] integer not null Primary key\n",
    "  [ProductName] Text not null\n",
    "  [ProductUnitPrice] Real not null\n",
    "  [ProductCategoryID] integer not null foreign key to ProductCateogry table\n",
    "OrderDetail\n",
    "  [OrderID] integer not null Primary Key\n",
    "  [CustomerID] integer not null foreign key to Customer table\n",
    "  [ProductID] integer not null foreign key to Product table\n",
    "  [OrderDate] integer not null \n",
    "  [QuantityOrdered] integer not null\n",
    "```\n",
    "\n",
    "### Hints\n",
    "Note: The `create_table` function has been updated. Please study it and try to understand how it is different. You can now use it to drop a table before inserting into it. This is useful because you do not have to start from scratch. You can drop an individual table before recreating it if it exists. \n",
    "\n",
    "Also checkout out this link to understand how `executemany` works: https://www.kite.com/python/docs/sqlite3.Cursor.executemany and incorporate it into your insert_table functions. If you use `execute`, your insertions will be very slow. If you have only one value to insert, the values tuple will look like \n",
    "```python\n",
    "values = (('Graduate', ), ('Undergraduate',))\n",
    "```\n",
    "\n",
    "Always, always use the `with` context to insert. \n",
    "\n",
    "#### Note: There is one manually graded part of this lab which I will use to make sure you guys are using `executemany` and not `execute` to insert into the tables. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "5e7be47391847cc0d03f392879154cec",
     "grade": false,
     "grade_id": "cell-3735739772045261",
     "locked": true,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "### Utility Functions\n",
    "from IPython.display import display, HTML\n",
    "import pandas as pd\n",
    "import sqlite3\n",
    "from sqlite3 import Error\n",
    "\n",
    "def create_connection(db_file, delete_db=False):\n",
    "    import os\n",
    "    if delete_db and os.path.exists(db_file):\n",
    "        os.remove(db_file)\n",
    "\n",
    "    conn = None\n",
    "    try:\n",
    "        conn = sqlite3.connect(db_file)\n",
    "        conn.execute(\"PRAGMA foreign_keys = 1\")\n",
    "    except Error as e:\n",
    "        print(e)\n",
    "\n",
    "    return conn\n",
    "\n",
    "\n",
    "def create_table(conn, create_table_sql, drop_table_name=None):\n",
    "    \n",
    "    if drop_table_name: # You can optionally pass drop_table_name to drop the table. \n",
    "        try:\n",
    "            c = conn.cursor()\n",
    "            c.execute(\"\"\"DROP TABLE IF EXISTS %s\"\"\" % (drop_table_name))\n",
    "        except Error as e:\n",
    "            print(e)\n",
    "    \n",
    "    try:\n",
    "        c = conn.cursor()\n",
    "        c.execute(create_table_sql)\n",
    "    except Error as e:\n",
    "        print(e)\n",
    "        \n",
    "def execute_sql_statement(sql_statement, conn):\n",
    "    cur = conn.cursor()\n",
    "    cur.execute(sql_statement)\n",
    "\n",
    "    rows = cur.fetchall()\n",
    "\n",
    "    return rows\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "cd778b51dad0ee50b9e8d0eb74c2e26a",
     "grade": true,
     "grade_id": "cell-adf05840d1de37f4",
     "locked": true,
     "points": 0,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "# Delete old database file\n",
    "\n",
    "normalized_database_filename = 'normalized.db'\n",
    "conn = create_connection(normalized_database_filename, delete_db=True)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "markdown",
     "checksum": "b35c364d3d98d8f40a38020fac58a479",
     "grade": false,
     "grade_id": "cell-0063d6359782bbdc",
     "locked": true,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "source": [
    "# Step 1 - Create the Region Table\n",
    "\n",
    "### Tasks\n",
    "\n",
    "1. Loop over the data and find the distinct regions\n",
    "1. Create the region table\n",
    "1. Populate the region table with the distinct regions. Make sure to sort the regions before inserting.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "103be8b9f6c5b968023da645a1ca0201",
     "grade": false,
     "grade_id": "cell-ac21dfd31890faee",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def step1_create_region_table(data_filename, normalized_database_filename):\n",
    "    # Inputs: Name of the data and normalized database filename\n",
    "    # Output: None\n",
    "    normalised_conn = create_connection(normalized_database_filename, True)\n",
    "    cur = normalised_conn.cursor()\n",
    "    create_table_region_sql = \"create table [Region] ([RegionID] integer not null primary key, [Region] text not null)\"\n",
    "   \n",
    "\n",
    "    \n",
    "    insert_region = pd.read_csv(data_filename, sep = '\\t')\n",
    "    region_list = insert_region['Region'].unique()\n",
    "    region_list = sorted(region_list)\n",
    "#     print(region_list)\n",
    "#     region_list=[r.replace('\"', '') for r in region_list]\n",
    "#     print(region_list)\n",
    "    regionID = list(range(1,10))\n",
    "    region_tup = zip(regionID, list(region_list))\n",
    "#     print(list(region_tup))\n",
    "#     sql_statement = \"Insert into Region (RegionID, Region) Values(?, ?)\"\n",
    "#     cur.executemany(sql_statement, list(region_tup)) \n",
    "    \n",
    "    def insert_region(conn, values):\n",
    "        sql_statement = \"Insert into Region (RegionID, Region) Values(?, ?)\"\n",
    "        cur=conn.cursor()\n",
    "        cur.executemany(sql_statement, values)\n",
    "        return cur.lastrowid\n",
    "    \n",
    "    \n",
    "#     for row in cur.execute(sql_statement):\n",
    "    \n",
    "    \n",
    "#     print(ele[0], ele[1])  \n",
    "    with normalised_conn:\n",
    "        create_table(normalised_conn, create_table_region_sql, 'Region')\n",
    "        insert_region(normalised_conn, region_tup)\n",
    "#         sql_statement = \"select * from Region\"\n",
    "#         insert_region(normalised_conn, sql_statement)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# data_filename = 'lab2_data.csv'\n",
    "# normalized_database_filename = 'normalized.db'\n",
    "# step1_create_region_table(data_filename, normalized_database_filename)\n",
    "# data = pd.read_csv(\"step1.csv\")\n",
    "# conn = create_connection(normalized_database_filename)\n",
    "# cur = conn.cursor()\n",
    "# df = pd.read_sql_query(\"\"\"SELECT * FROM Region\"\"\", conn)\n",
    "# print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "fdb4cc3df475d33cad1778b9dbbac4e7",
     "grade": true,
     "grade_id": "cell-a58c8f7320367143",
     "locked": true,
     "points": 5,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "# 5 Points\n",
    "data_filename = 'lab2_data.csv'\n",
    "normalized_database_filename = 'normalized.db'\n",
    "step1_create_region_table(data_filename, normalized_database_filename)\n",
    "data = pd.read_csv(\"step1.csv\")\n",
    "conn = create_connection(normalized_database_filename)\n",
    "cur = conn.cursor()\n",
    "df = pd.read_sql_query(\"\"\"SELECT * FROM Region\"\"\", conn)\n",
    "assert df.equals(data) == True"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "markdown",
     "checksum": "762d305baf7a02db21b8985c7208695b",
     "grade": false,
     "grade_id": "cell-873571e447820350",
     "locked": true,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "source": [
    "# Step 2 - Create the Country Table\n",
    "\n",
    "### Tasks\n",
    "\n",
    "1. Loop over the data and find the distinct countries\n",
    "1. Create the country table\n",
    "1. Populate the country table with the distinct countries. Make sure to sort the countries before inserting.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "7aee55f39e0b56ad9fcc4e4139cfda6c",
     "grade": false,
     "grade_id": "cell-03063871a161495b",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def step2_create_country_table(data_filename, normalized_database_filename):\n",
    "    # Inputs: Name of the data and normalized database filename\n",
    "    # Output: None\n",
    "    \n",
    "    # YOUR CODE HERE\n",
    "    normalised_conn = create_connection(normalized_database_filename)\n",
    "    cur = normalised_conn.cursor()\n",
    "    create_table_country_sql = \"create table [Country] ([CountryID] integer not null primary key, [Country] text not null)\"\n",
    "       \n",
    "    insert_country = pd.read_csv(data_filename, sep = '\\t')\n",
    "    country_list = insert_country['Country'].unique()\n",
    "    country_list = sorted(country_list)\n",
    "    countryID = list(range(1,22))\n",
    "    country_tup = zip(countryID, list(country_list))\n",
    "#     print(list(country_tup))\n",
    "    def insert_country(conn, values):\n",
    "        sql_statement = \"Insert into Country (CountryID, Country) Values(?, ?)\"\n",
    "        cur=conn.cursor()\n",
    "        cur.executemany(sql_statement, values)\n",
    "        return cur.lastrowid\n",
    "    \n",
    "    with normalised_conn:\n",
    "        create_table(normalised_conn, create_table_country_sql, 'Country')\n",
    "        insert_country(normalised_conn, country_tup)\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# data_filename = 'lab2_data.csv'\n",
    "# normalized_database_filename = 'normalized.db'\n",
    "# step2_create_country_table(data_filename, normalized_database_filename)\n",
    "# data = pd.read_csv(\"step2.csv\")\n",
    "# conn = create_connection(normalized_database_filename)\n",
    "# cur = conn.cursor()\n",
    "# df = pd.read_sql_query(\"\"\"SELECT * FROM Country\"\"\", conn)\n",
    "# print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "0c88c83438457276a98d370fcaf9a496",
     "grade": true,
     "grade_id": "cell-35fa916465a32fee",
     "locked": true,
     "points": 5,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "# 5 Points\n",
    "data_filename = 'lab2_data.csv'\n",
    "normalized_database_filename = 'normalized.db'\n",
    "step2_create_country_table(data_filename, normalized_database_filename)\n",
    "data = pd.read_csv(\"step2.csv\")\n",
    "conn = create_connection(normalized_database_filename)\n",
    "cur = conn.cursor()\n",
    "df = pd.read_sql_query(\"\"\"SELECT * FROM Country\"\"\", conn)\n",
    "assert df.equals(data) == True"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "markdown",
     "checksum": "260230073a37bbdd884879ea259f2768",
     "grade": false,
     "grade_id": "cell-99e73b0ca4c9326f",
     "locked": true,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "source": [
    "# Step 3 - Create dictionary to map Region to RegionID\n",
    "\n",
    "The purpose of this step is to create a dictionary to look up the primary key lookup for a given region. \n",
    "\n",
    "Tasks\n",
    "1. Write an SQL query to fetch all the rows from the Region table\n",
    "1. Transform the row into a dictionary whose key is Region and value is RegionID\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "d540d7542e8fe1609e409c44e9cefdba",
     "grade": false,
     "grade_id": "cell-ef8d4afc4d8c7b1d",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def step3_create_region_to_regionid_dictionary(normalized_database_filename):\n",
    "    normalised_conn = create_connection(normalized_database_filename)\n",
    "    cur = normalised_conn.cursor()\n",
    "    \n",
    "    # YOUR CODE HERE\n",
    "    sql_statement = \"Select * from Region\"\n",
    "    region_dict = {} \n",
    "    \n",
    "    cur.execute(sql_statement)\n",
    "    rows = cur.fetchall()\n",
    "    for row in rows:\n",
    "#         print(row)s\n",
    "        region_dict[row[1]] = row[0]\n",
    "    print(region_dict)\n",
    "    return region_dict\n",
    "\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "41caba3b9e4d4091023ecc190079bb03",
     "grade": true,
     "grade_id": "cell-0e998f0f33ca5e4f",
     "locked": true,
     "points": 5,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'British Isles': 1, 'Central America': 2, 'Eastern Europe': 3, 'North America': 4, 'Northern Europe': 5, 'Scandinavia': 6, 'South America': 7, 'Southern Europe': 8, 'Western Europe': 9}\n"
     ]
    }
   ],
   "source": [
    "# 5 Points\n",
    "normalized_database_filename = 'normalized.db'\n",
    "region_to_regionid_dict = step3_create_region_to_regionid_dictionary(normalized_database_filename)\n",
    "expected_solution = {\n",
    "    'British Isles': 1,\n",
    "    'Central America': 2,\n",
    "    'Eastern Europe': 3, \n",
    "    'North America': 4,\n",
    "    'Northern Europe': 5,\n",
    "    'Scandinavia': 6, \n",
    "    'South America': 7,\n",
    "    'Southern Europe': 8,\n",
    "    'Western Europe': 9\n",
    "}\n",
    "assert expected_solution == region_to_regionid_dict"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "markdown",
     "checksum": "509666ce91b5017b757ed6ea4a10be16",
     "grade": false,
     "grade_id": "cell-c5ab2a32c05ac75c",
     "locked": true,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "source": [
    "# Step 4 - Create dictionary to map Country to CountryID\n",
    "\n",
    "The purpose of this step is to create a dictionary to look up the primary key lookup for a given country. \n",
    "\n",
    "\n",
    "Tasks\n",
    "1. Write an SQL query to fetch all the rows from the Country table\n",
    "1. Transform the row into a dictionary whose key is Country and value is CountryID"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "9bb870a7200246781ff8277038c9d191",
     "grade": false,
     "grade_id": "cell-698d198055989252",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def step4_create_country_to_countryid_dictionary(normalized_database_filename):\n",
    "    \n",
    "    \n",
    "    # YOUR CODE HERE\n",
    "    sql_statement = \"Select * from Country\"\n",
    "    country_dict = {} \n",
    "    \n",
    "    cur.execute(sql_statement)\n",
    "    rows = cur.fetchall()\n",
    "    for row in rows:\n",
    "#         print(row)s\n",
    "        country_dict[row[1]] = row[0]\n",
    "    print(country_dict)\n",
    "    return country_dict\n",
    "        \n",
    "        \n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "57d9b613e0a172149112c9741be0a91e",
     "grade": true,
     "grade_id": "cell-feef162373ce30c7",
     "locked": true,
     "points": 5,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'Argentina': 1, 'Austria': 2, 'Belgium': 3, 'Brazil': 4, 'Canada': 5, 'Denmark': 6, 'Finland': 7, 'France': 8, 'Germany': 9, 'Ireland': 10, 'Italy': 11, 'Mexico': 12, 'Norway': 13, 'Poland': 14, 'Portugal': 15, 'Spain': 16, 'Sweden': 17, 'Switzerland': 18, 'UK': 19, 'USA': 20, 'Venezuela': 21}\n"
     ]
    }
   ],
   "source": [
    "# 5 Points\n",
    "normalized_database_filename = 'normalized.db'\n",
    "country_to_countryid_dict = step4_create_country_to_countryid_dictionary(normalized_database_filename)\n",
    "expected_solution = {\n",
    "    'Argentina': 1, \n",
    "    'Austria': 2, \n",
    "    'Belgium': 3, \n",
    "    'Brazil': 4,\n",
    "    'Canada': 5, \n",
    "    'Denmark': 6, \n",
    "    'Finland': 7, \n",
    "    'France': 8, \n",
    "    'Germany': 9, \n",
    "    'Ireland': 10, \n",
    "    'Italy': 11, \n",
    "    'Mexico': 12, \n",
    "    'Norway': 13, \n",
    "    'Poland': 14, \n",
    "    'Portugal': 15,\n",
    "    'Spain': 16, \n",
    "    'Sweden': 17, \n",
    "    'Switzerland': 18,\n",
    "    'UK': 19, \n",
    "    'USA': 20, \n",
    "    'Venezuela': 21\n",
    "}\n",
    "assert expected_solution == country_to_countryid_dict"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "markdown",
     "checksum": "7d8439f8fc60a86d862d56f15a94ef67",
     "grade": false,
     "grade_id": "cell-8c6a2fc726eb0641",
     "locked": true,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "source": [
    "# Step 5 - Create the customer table\n",
    "\n",
    "Note: You will be using `region_to_regionid_dict` and `country_to_countryid_dict` in this step when inserting into the customer table. Both should already be available inside the step5 function, so you can just use them. \n",
    "\n",
    "Tasks\n",
    "1. Loop over the data file and grab the name, address, city, country and region\n",
    "1. Create the customer table\n",
    "1. Populate the customer table\n",
    "\n",
    "### NOTE!\n",
    "- One of name when split on space results in three elements. In this case, the first name is the first element and the other two elements in the last name. \n",
    "- Insert the names sorted by first and lastname as one string. Hint: user lambda to combine first and last name and sort. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "3bf8c00017e70d4d63a331a1c72f6cf9",
     "grade": false,
     "grade_id": "cell-032e547d9d6100da",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def step5_create_customer_table(data_filename, normalized_database_filename):\n",
    "\n",
    "    # YOUR CODE HERE\n",
    "    normalised_conn = create_connection(normalized_database_filename)\n",
    "    cur = normalised_conn.cursor()\n",
    "    \n",
    "    create_table_cust_sql = \"\"\"create table [customer] ([CustomerID] integer not null Primary Key,\n",
    "                                                        [FirstName] Text not null,  \n",
    "                                                        [LastName] Text not null,\n",
    "                                                        [Address] Text not null,\n",
    "                                                        [City] Text not null,\n",
    "                                                        [CountryID] integer not null,\n",
    "                                                        [RegionID] integer not null)\"\"\"\n",
    "\n",
    "                                                        \n",
    "    insert_customer = pd.read_csv(data_filename, sep = '\\t')\n",
    "\n",
    "    customerID = list(range(1,92))\n",
    "    fullnames = []\n",
    "    for ele in insert_customer['Name']:\n",
    "        fullnames.append(ele.strip())\n",
    "        \n",
    "    fullnames = sorted(fullnames)\n",
    "    f_name= []\n",
    "    l_name= []\n",
    "    \n",
    "    for ele in range(len(fullnames)):\n",
    "        f_name.append(fullnames[ele].split(' ')[0].strip())\n",
    "        l_name.append(' '.join(fullnames[ele].split(' ')[1:]))\n",
    "#         print(l_name)\n",
    "        \n",
    "    address = [ele for ele in insert_customer['Address']]\n",
    "    city = [ele for ele in insert_customer['City']]\n",
    "    country=[ele for ele in insert_customer['Country']]\n",
    "    region= [ele for ele in insert_customer['Region']]\n",
    "    countryID = [country_to_countryid_dict[country[ele]] for ele in range(len(country))]\n",
    "    regionID =[region_to_regionid_dict[region[ele]] for ele in range(len(country))]\n",
    "        \n",
    "    customer_tup = zip(customerID, list(f_name), list(l_name), list(address), list(city), countryID, regionID) \n",
    "#     print(customer_tup)\n",
    "    def insert_cust(conn, values):\n",
    "        sql_statement = \"Insert into customer (CustomerID, FirstName, LastName, Address, City, countryID, regionID) Values(?, ?, ?, ?, ?, ?, ?)\"\n",
    "        cur=conn.cursor()\n",
    "        cur.executemany(sql_statement, values)\n",
    "        return cur.lastrowid\n",
    "    \n",
    "    \n",
    "    with normalised_conn:\n",
    "        create_table(normalised_conn, create_table_cust_sql, 'customer')\n",
    "        insert_cust(normalised_conn, customer_tup)\n",
    "    \n",
    "    \n",
    "  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# normalized_database_filename = 'normalized.db'\n",
    "# data_filename = 'lab2_data.csv'\n",
    "# step5_create_customer_table(data_filename, normalized_database_filename)\n",
    "# data = pd.read_csv(\"step5.csv\")\n",
    "# conn = create_connection(normalized_database_filename)\n",
    "# cur = conn.cursor()\n",
    "# df = pd.read_sql_query(\"\"\"SELECT * FROM Customer\"\"\", conn)\n",
    "# display(df)\n",
    "# display(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "4c02e910dad43d13f3cb706127a1f348",
     "grade": true,
     "grade_id": "cell-fd82245a8abdd38a",
     "locked": true,
     "points": 10,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "# 10 Points\n",
    "normalized_database_filename = 'normalized.db'\n",
    "data_filename = 'lab2_data.csv'\n",
    "step5_create_customer_table(data_filename, normalized_database_filename)\n",
    "data = pd.read_csv(\"step5.csv\")\n",
    "conn = create_connection(normalized_database_filename)\n",
    "cur = conn.cursor()\n",
    "df = pd.read_sql_query(\"\"\"SELECT * FROM Customer\"\"\", conn)\n",
    "assert df.equals(data) == True"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "markdown",
     "checksum": "d0ff33afd72bc782357bbc3dbbb51457",
     "grade": false,
     "grade_id": "cell-753c3ff7842269fb",
     "locked": true,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "source": [
    "# Step 6 - Create dictionary to map Name (FirstName LastName) to CustomerID\n",
    "\n",
    "The purpose of this step is to create a dictionary to look up the primary key lookup for a given name (FirstName LastName)\n",
    "\n",
    "Tasks\n",
    "1. Write an SQL query to fetch all the rows from the Customer table\n",
    "1. Transform the row into a dictionary whose key is the name of the customer (FirstName LastName) and value is CustomerID\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "47b1d6b0bf88ac6b038707db02722883",
     "grade": false,
     "grade_id": "cell-ce6a2a9c2cc2e5e3",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def step6_create_customer_to_customerid_dictionary(normalized_database_filename):\n",
    "    \n",
    "    \n",
    "    # YOUR CODE HERE\n",
    "    normalised_conn = create_connection(normalized_database_filename)\n",
    "    cur = normalised_conn.cursor()\n",
    "    sql_statement = \"Select customerID, FirstName, LastName from Customer\"\n",
    "    customer_dict = {} \n",
    "\n",
    "    \n",
    "    cur.execute(sql_statement)\n",
    "    rows = cur.fetchall()\n",
    "    customerID = []\n",
    "    f_name = []\n",
    "    l_name = []\n",
    "    \n",
    "    for row in rows:\n",
    "#         print(rows)\n",
    "        customerID.append(row[0])\n",
    "        f_name.append(row[1])\n",
    "        l_name.append(row[2])\n",
    "    \n",
    "    for ele in range(len(f_name)):\n",
    "        fullname = ' '.join([f_name[ele], l_name[ele]])\n",
    "        customer_dict[str(fullname)]=int(customerID[ele])\n",
    "    \n",
    "    \n",
    "    return customer_dict\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "ffdaa351d473a1ac377819b45b0fe9ea",
     "grade": true,
     "grade_id": "cell-6ea2afa9c16a91c6",
     "locked": true,
     "points": 5,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "# 5 Points\n",
    "normalized_database_filename = 'normalized.db'\n",
    "customer_to_customerid_dict = step6_create_customer_to_customerid_dictionary(normalized_database_filename)\n",
    "expected_solution = {\n",
    "     'Alejandra Camino': 1,\n",
    "     'Alexander Feuer': 2,\n",
    "     'Ana Trujillo': 3,\n",
    "     'Anabela Domingues': 4,\n",
    "     'André Fonseca': 5,\n",
    "     'Ann Devon': 6,\n",
    "     'Annette Roulet': 7,\n",
    "     'Antonio Moreno': 8,\n",
    "     'Aria Cruz': 9,\n",
    "     'Art Braunschweiger': 10,\n",
    "     'Bernardo Batista': 11,\n",
    "     'Carine Schmitt': 12,\n",
    "     'Carlos González': 13,\n",
    "     'Carlos Hernández': 14,\n",
    "     'Catherine Dewey': 15,\n",
    "     'Christina Berglund': 16,\n",
    "     'Daniel Tonini': 17,\n",
    "     'Diego Roel': 18,\n",
    "     'Dominique Perrier': 19,\n",
    "     'Eduardo Saavedra': 20,\n",
    "     'Elizabeth Brown': 21,\n",
    "     'Elizabeth Lincoln': 22,\n",
    "     'Felipe Izquierdo': 23,\n",
    "     'Fran Wilson': 24,\n",
    "     'Francisco Chang': 25,\n",
    "     'Frédérique Citeaux': 26,\n",
    "     'Georg Pipps': 27,\n",
    "     'Giovanni Rovelli': 28,\n",
    "     'Guillermo Fernández': 29,\n",
    "     'Hanna Moos': 30,\n",
    "     'Hari Kumar': 31,\n",
    "     'Helen Bennett': 32,\n",
    "     'Helvetius Nagy': 33,\n",
    "     'Henriette Pfalzheim': 34,\n",
    "     'Horst Kloss': 35,\n",
    "     'Howard Snyder': 36,\n",
    "     'Isabel de Castro': 37,\n",
    "     'Jaime Yorres': 38,\n",
    "     'Janete Limeira': 39,\n",
    "     'Janine Labrune': 40,\n",
    "     'Jean Fresnière': 41,\n",
    "     'John Steel': 42,\n",
    "     'Jonas Bergulfsen': 43,\n",
    "     'Jose Pavarotti': 44,\n",
    "     'José Pedro Freyre': 45,\n",
    "     'Jytte Petersen': 46,\n",
    "     'Karin Josephs': 47,\n",
    "     'Karl Jablonski': 48,\n",
    "     'Laurence Lebihan': 49,\n",
    "     'Lino Rodriguez': 50,\n",
    "     'Liu Wong': 51,\n",
    "     'Liz Nixon': 52,\n",
    "     'Lúcia Carvalho': 53,\n",
    "     'Manuel Pereira': 54,\n",
    "     'Maria Anders': 55,\n",
    "     'Maria Larsson': 56,\n",
    "     'Marie Bertrand': 57,\n",
    "     'Mario Pontes': 58,\n",
    "     'Martine Rancé': 59,\n",
    "     'Martín Sommer': 60,\n",
    "     'Mary Saveley': 61,\n",
    "     'Matti Karttunen': 62,\n",
    "     'Maurizio Moroni': 63,\n",
    "     'Michael Holz': 64,\n",
    "     'Miguel Angel Paolino': 65,\n",
    "     'Palle Ibsen': 66,\n",
    "     'Paolo Accorti': 67,\n",
    "     'Pascale Cartrain': 68,\n",
    "     'Patricia McKenna': 69,\n",
    "     'Patricio Simpson': 70,\n",
    "     'Paul Henriot': 71,\n",
    "     'Paula Parente': 72,\n",
    "     'Paula Wilson': 73,\n",
    "     'Pedro Afonso': 74,\n",
    "     'Peter Franken': 75,\n",
    "     'Philip Cramer': 76,\n",
    "     'Pirkko Koskitalo': 77,\n",
    "     'Renate Messner': 78,\n",
    "     'Rene Phillips': 79,\n",
    "     'Rita Müller': 80,\n",
    "     'Roland Mendel': 81,\n",
    "     'Sergio Gutiérrez': 82,\n",
    "     'Simon Crowther': 83,\n",
    "     'Sven Ottlieb': 84,\n",
    "     'Thomas Hardy': 85,\n",
    "     'Victoria Ashworth': 86,\n",
    "     'Yang Wang': 87,\n",
    "     'Yoshi Latimer': 88,\n",
    "     'Yoshi Tannamuri': 89,\n",
    "     'Yvonne Moncada': 90,\n",
    "     'Zbyszek Piestrzeniewicz': 91\n",
    "}\n",
    "\n",
    "assert expected_solution == customer_to_customerid_dict"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "markdown",
     "checksum": "6b205001bdfd840481d98b2ff7fadeca",
     "grade": false,
     "grade_id": "cell-3c24af40aa10c6fb",
     "locked": true,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "source": [
    "# Step 7 - Create Product Category Table\n",
    "\n",
    "### Tasks\n",
    "\n",
    "1. Loop over the data and find the distinct product categories\n",
    "1. Create the product category table\n",
    "1. Populate the product category table with the distinct product categories. Make sure to sort the categories before inserting.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "8bb5ee5da389d8dcfce4790823dcbcaf",
     "grade": false,
     "grade_id": "cell-08bd41f3ee8e5b0f",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def step7_create_productcategory_table(data_filename, normalized_database_filename):\n",
    "    # Inputs: Name of the data and normalized database filename\n",
    "    # Output: None\n",
    "\n",
    "    \n",
    "    # YOUR CODE HERE\n",
    "    normalised_conn = create_connection(normalized_database_filename)\n",
    "    cur = normalised_conn.cursor()\n",
    "    create_table_productcategory_sql = \"\"\"create table [ProductCategory] ([ProductCategoryID] integer not null Primary Key,\n",
    "                                                                [ProductCategory] Text not null,  \n",
    "                                                                [ProductCategoryDescription] Text not null)\"\"\"\n",
    "\n",
    "    insert_productcategory = pd.read_csv(data_filename, sep = '\\t')\n",
    "    productcategory = []\n",
    "    for ele in insert_productcategory['ProductCategory']:\n",
    "        ele = ele.strip().split(';')\n",
    "        for x in ele:\n",
    "            if x not in productcategory:\n",
    "                productcategory.append(x)\n",
    "#     print(productcategory)  \n",
    "\n",
    "    productcategoryID = list(range(1,9))\n",
    "    \n",
    "    productcategorydescription = []\n",
    "    for ele in insert_productcategory['ProductCategoryDescription']:\n",
    "        ele = ele.strip().split(';')\n",
    "        for x in ele:\n",
    "            if x not in productcategorydescription:\n",
    "                productcategorydescription.append(x)\n",
    "    print(productcategorydescription)\n",
    "    \n",
    "    order = [1, 6, 0, 5, 3, 4, 7, 2]\n",
    "    productcategorydescription = [productcategorydescription[x] for x in order]\n",
    "#     print(productcategorydescription)\n",
    "    \n",
    "    productcategory_tup = zip(productcategoryID, list(sorted(productcategory)), productcategorydescription)\n",
    "#     print(list(productcategory_tup))\n",
    "    \n",
    "    def insert_productcategory(conn, values):\n",
    "        sql_statement = \"Insert into ProductCategory (ProductCategoryID, ProductCategory, ProductCategoryDescription) Values(?, ?, ?)\"\n",
    "        cur=conn.cursor()\n",
    "        cur.executemany(sql_statement, values)\n",
    "        return cur.lastrowid\n",
    "    \n",
    "    with normalised_conn:\n",
    "        create_table(normalised_conn, create_table_productcategory_sql, 'ProductCategory')\n",
    "        insert_productcategory(normalised_conn, productcategory_tup)\n",
    "        \n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# normalized_database_filename = 'normalized.db'\n",
    "# data_filename = 'lab2_data.csv'\n",
    "# step7_create_productcategory_table(data_filename, normalized_database_filename)\n",
    "# data = pd.read_csv(\"step7.csv\")\n",
    "# conn = create_connection(normalized_database_filename)\n",
    "# cur = conn.cursor()\n",
    "# df = pd.read_sql_query(\"\"\"SELECT * FROM ProductCategory\"\"\", conn)\n",
    "# display(df)\n",
    "# display(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "96d0a857fc437795c2e1b74dc314dca0",
     "grade": true,
     "grade_id": "cell-1e1b759917d49004",
     "locked": true,
     "points": 5,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Desserts, candies, and sweet breads', 'Soft drinks, coffees, teas, beers, and ales', 'Seaweed and fish', 'Breads, crackers, pasta, and cereal', 'Prepared meats', 'Cheeses', 'Sweet and savory sauces, relishes, spreads, and seasonings', 'Dried fruit and bean curd']\n"
     ]
    }
   ],
   "source": [
    "# 5 Points\n",
    "normalized_database_filename = 'normalized.db'\n",
    "data_filename = 'lab2_data.csv'\n",
    "step7_create_productcategory_table(data_filename, normalized_database_filename)\n",
    "data = pd.read_csv(\"step7.csv\")\n",
    "conn = create_connection(normalized_database_filename)\n",
    "cur = conn.cursor()\n",
    "df = pd.read_sql_query(\"\"\"SELECT * FROM ProductCategory\"\"\", conn)\n",
    "assert df.equals(data) == True"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "markdown",
     "checksum": "e027591076c1d0ba8cfef1f54455eea2",
     "grade": false,
     "grade_id": "cell-fb02711b485a0b10",
     "locked": true,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "source": [
    "# Step 8 - Create dictionary to map productcategory to productcategoryid\n",
    "\n",
    "The purpose of this step is to create a dictionary to look up the primary key lookup for a given product category\n",
    "\n",
    "Tasks\n",
    "1. Write an SQL query to fetch all the rows from the product category table\n",
    "1. Transform the row into a dictionary whose key is the product category and value is product category id\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "2d051b337db67c731036b5a4fa5bff39",
     "grade": false,
     "grade_id": "cell-d6e481e4dc0947b6",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def step8_create_productcategory_to_productcategoryid_dictionary(normalized_database_filename):\n",
    "    \n",
    "    \n",
    "    # YOUR CODE HERE\n",
    "    sql_statement = \"Select ProductCategoryID, ProductCategory from ProductCategory\"\n",
    "    ProductCategory_dict = {} \n",
    "    \n",
    "    cur.execute(sql_statement)\n",
    "    rows = cur.fetchall()\n",
    "    for row in rows:\n",
    "#         print(row)s\n",
    "        ProductCategory_dict[row[1]] = row[0]\n",
    "    print(ProductCategory_dict)\n",
    "    return ProductCategory_dict\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "de3ff6b992a7ce921ce88a08d8af0195",
     "grade": true,
     "grade_id": "cell-8bfcf4c0ca777fcb",
     "locked": true,
     "points": 5,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'Beverages': 1, 'Condiments': 2, 'Confections': 3, 'Dairy Products': 4, 'Grains/Cereals': 5, 'Meat/Poultry': 6, 'Produce': 7, 'Seafood': 8}\n"
     ]
    }
   ],
   "source": [
    "# 5 Points\n",
    "normalized_database_filename = 'normalized.db'\n",
    "productcategory_to_productcategoryid_dict = step8_create_productcategory_to_productcategoryid_dictionary(normalized_database_filename)\n",
    "expected_solution = {\n",
    "    'Beverages': 1,\n",
    "    'Condiments': 2,\n",
    "    'Confections': 3,\n",
    "    'Dairy Products': 4,\n",
    "    'Grains/Cereals': 5,\n",
    "    'Meat/Poultry': 6,\n",
    "    'Produce': 7,\n",
    "    'Seafood': 8\n",
    "}\n",
    "assert expected_solution == productcategory_to_productcategoryid_dict"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "markdown",
     "checksum": "f2ce39f6b757dc3bd9b4abe2fc212256",
     "grade": false,
     "grade_id": "cell-f29d1a2b636e1362",
     "locked": true,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "source": [
    "# Step 9 - Create Product Table\n",
    "\n",
    "Note: You will be using `productcategory_to_productcategoryid_dict` in this step when inserting into the product table.\n",
    "\n",
    "### Tasks\n",
    "\n",
    "1. Loop over the data and find the distinct products\n",
    "1. Create the product table\n",
    "1. Populate the product table with the distinct products. Make sure to sort the products (by name) before inserting.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "e3f34e7aa0e2aa51db46bcb2d1a1b136",
     "grade": false,
     "grade_id": "cell-28caf5a23d2281ef",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def step9_create_product_table(data_filename, normalized_database_filename):\n",
    "    # Inputs: Name of the data and normalized database filename\n",
    "    # Output: None\n",
    "\n",
    "    \n",
    "    # YOUR CODE HERE\n",
    "    normalised_conn = create_connection(normalized_database_filename)\n",
    "    cur = normalised_conn.cursor()\n",
    "    create_table_product_sql = \"\"\"create table [Product] ([ProductID] integer not null Primary Key,\n",
    "                                                                [ProductName] Text not null,  \n",
    "                                                                [ProductUnitPrice] Real not null,\n",
    "                                                                [ProductCategoryID] integer not null)\"\"\"\n",
    " \n",
    "    insert_product = pd.read_csv(data_filename, sep = '\\t')\n",
    "    insert_product = insert_product.loc[:, ['ProductName', 'ProductCategory', 'ProductUnitPrice']]\n",
    "    product_tup=[]\n",
    "    \n",
    "    for index, row in insert_product.iterrows():\n",
    "        for productname, productcategory, productunitprice in zip(row[0].split(';'), row[1].split(';'), row[2].split(';')):\n",
    "            productname = productname.strip()\n",
    "            productcategory = productcategory.strip()\n",
    "            productunitprice = float(productunitprice.strip())\n",
    "            productcategoryID = productcategory_to_productcategoryid_dict[productcategory]\n",
    "#             print(productcategoryID)\n",
    "            productID = list(range(1,78))\n",
    "            product_ele = (productname, productunitprice, productcategoryID)\n",
    "            if product_ele not in product_tup:\n",
    "                product_tup.append(product_ele)\n",
    "#                 print(product_tup)\n",
    "                \n",
    "    product_tup = sorted(product_tup, key=lambda ele:ele[0])\n",
    "#     print(product_tup)\n",
    "    \n",
    "    def insert_product(conn, values):\n",
    "        sql_statement = \"Insert into Product (ProductName, ProductUnitPrice, ProductCategoryID) Values(?, ?, ?)\"\n",
    "        cur=conn.cursor()\n",
    "        cur.executemany(sql_statement, values)\n",
    "        return cur.lastrowid\n",
    "    \n",
    "    with normalised_conn:\n",
    "        create_table(normalised_conn, create_table_product_sql, 'Product')\n",
    "        insert_product(normalised_conn, product_tup)  \n",
    "        \n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# normalized_database_filename = 'normalized.db'\n",
    "# data_filename = 'lab2_data.csv'\n",
    "# step9_create_product_table(data_filename, normalized_database_filename)\n",
    "# data = pd.read_csv(\"step9.csv\")\n",
    "# conn = create_connection(normalized_database_filename)\n",
    "# cur = conn.cursor()\n",
    "# df = pd.read_sql_query(\"\"\"SELECT * FROM Product\"\"\", conn)\n",
    "# display(df)\n",
    "# display(data)\n",
    "# jupyter notebook --NotebookApp.iopub_data_rate_limit=1.0e10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "09170a5b9c6fca0a36dc8bfaa65f364c",
     "grade": true,
     "grade_id": "cell-3443fde607418320",
     "locked": true,
     "points": 10,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "# 10 Points\n",
    "normalized_database_filename = 'normalized.db'\n",
    "data_filename = 'lab2_data.csv'\n",
    "step9_create_product_table(data_filename, normalized_database_filename)\n",
    "data = pd.read_csv(\"step9.csv\")\n",
    "conn = create_connection(normalized_database_filename)\n",
    "cur = conn.cursor()\n",
    "df = pd.read_sql_query(\"\"\"SELECT * FROM Product\"\"\", conn)\n",
    "assert df.equals(data) == True\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "markdown",
     "checksum": "90f39906dec1e17b45ebbfe7d4d59d1a",
     "grade": false,
     "grade_id": "cell-826804c074f5a023",
     "locked": true,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "source": [
    "# Step 10 - Create dictionary to map product to productid\n",
    "\n",
    "The purpose of this step is to create a dictionary to look up the primary key lookup for a given product \n",
    "\n",
    "Tasks\n",
    "1. Write an SQL query to fetch all the rows from the product table\n",
    "1. Transform the row into a dictionary whose key is the product and value is product id\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "75b0e2572645a31c28ab2bf007aff2a3",
     "grade": false,
     "grade_id": "cell-d1db4474fd3ab56f",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def step10_create_product_to_productid_dictionary(normalized_database_filename):\n",
    "    \n",
    "    \n",
    "    # YOUR CODE HERE\n",
    "    \n",
    "    sql_statement = \"Select ProductID, ProductName from product \"\n",
    "    Product_dict = {} \n",
    "    \n",
    "    cur.execute(sql_statement)\n",
    "    rows = cur.fetchall()\n",
    "    for row in rows:\n",
    "#         print(row)s\n",
    "        Product_dict[row[1]] = row[0]\n",
    "#     print(Product_dict)\n",
    "    return Product_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "9f34122d530a1193ac150c2fdd1fbca5",
     "grade": true,
     "grade_id": "cell-770f336ec19ab72a",
     "locked": true,
     "points": 5,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "# 5 Points\n",
    "normalized_database_filename = 'normalized.db'\n",
    "product_to_productid_dict = step10_create_product_to_productid_dictionary(normalized_database_filename)\n",
    "expected_solution = {\n",
    "    'Alice Mutton': 1,\n",
    "    'Aniseed Syrup': 2,\n",
    "    'Boston Crab Meat': 3,\n",
    "    'Camembert Pierrot': 4,\n",
    "    'Carnarvon Tigers': 5,\n",
    "    'Chai': 6,\n",
    "    'Chang': 7,\n",
    "    'Chartreuse verte': 8,\n",
    "    \"Chef Anton's Cajun Seasoning\": 9,\n",
    "    \"Chef Anton's Gumbo Mix\": 10,\n",
    "    'Chocolade': 11,\n",
    "    'Côte de Blaye': 12,\n",
    "    'Escargots de Bourgogne': 13,\n",
    "    'Filo Mix': 14,\n",
    "    'Flotemysost': 15,\n",
    "    'Geitost': 16,\n",
    "    'Genen Shouyu': 17,\n",
    "    'Gnocchi di nonna Alice': 18,\n",
    "    'Gorgonzola Telino': 19,\n",
    "    \"Grandma's Boysenberry Spread\": 20,\n",
    "    'Gravad lax': 21,\n",
    "    'Guaraná Fantástica': 22,\n",
    "    'Gudbrandsdalsost': 23,\n",
    "    'Gula Malacca': 24,\n",
    "    'Gumbär Gummibärchen': 25,\n",
    "    \"Gustaf's Knäckebröd\": 26,\n",
    "    'Ikura': 27,\n",
    "    'Inlagd Sill': 28,\n",
    "    'Ipoh Coffee': 29,\n",
    "    \"Jack's New England Clam Chowder\": 30,\n",
    "    'Konbu': 31,\n",
    "    'Lakkalikööri': 32,\n",
    "    'Laughing Lumberjack Lager': 33,\n",
    "    'Longlife Tofu': 34,\n",
    "    'Louisiana Fiery Hot Pepper Sauce': 35,\n",
    "    'Louisiana Hot Spiced Okra': 36,\n",
    "    'Manjimup Dried Apples': 37,\n",
    "    'Mascarpone Fabioli': 38,\n",
    "    'Maxilaku': 39,\n",
    "    'Mishi Kobe Niku': 40,\n",
    "    'Mozzarella di Giovanni': 41,\n",
    "    'Nord-Ost Matjeshering': 42,\n",
    "    'Northwoods Cranberry Sauce': 43,\n",
    "    'NuNuCa Nuß-Nougat-Creme': 44,\n",
    "    'Original Frankfurter grüne Soße': 45,\n",
    "    'Outback Lager': 46,\n",
    "    'Pavlova': 47,\n",
    "    'Perth Pasties': 48,\n",
    "    'Pâté chinois': 49,\n",
    "    'Queso Cabrales': 50,\n",
    "    'Queso Manchego La Pastora': 51,\n",
    "    'Raclette Courdavault': 52,\n",
    "    'Ravioli Angelo': 53,\n",
    "    'Rhönbräu Klosterbier': 54,\n",
    "    'Rogede sild': 55,\n",
    "    'Röd Kaviar': 56,\n",
    "    'Rössle Sauerkraut': 57,\n",
    "    'Sasquatch Ale': 58,\n",
    "    'Schoggi Schokolade': 59,\n",
    "    'Scottish Longbreads': 60,\n",
    "    'Singaporean Hokkien Fried Mee': 61,\n",
    "    \"Sir Rodney's Marmalade\": 62,\n",
    "    \"Sir Rodney's Scones\": 63,\n",
    "    \"Sirop d'érable\": 64,\n",
    "    'Spegesild': 65,\n",
    "    'Steeleye Stout': 66,\n",
    "    'Tarte au sucre': 67,\n",
    "    'Teatime Chocolate Biscuits': 68,\n",
    "    'Thüringer Rostbratwurst': 69,\n",
    "    'Tofu': 70,\n",
    "    'Tourtière': 71,\n",
    "    'Tunnbröd': 72,\n",
    "    \"Uncle Bob's Organic Dried Pears\": 73,\n",
    "    'Valkoinen suklaa': 74,\n",
    "    'Vegie-spread': 75,\n",
    "    'Wimmers gute Semmelknödel': 76,\n",
    "    'Zaanse koeken': 77\n",
    "}\n",
    "assert expected_solution == product_to_productid_dict"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "markdown",
     "checksum": "4d5c7878dc25f7ab74334e0ecc5a61bf",
     "grade": false,
     "grade_id": "cell-ea39763ce1ed5841",
     "locked": true,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "source": [
    "# Step 11 - Create OrderDetail Table\n",
    "\n",
    "Note: You will be using `product_to_productid_dict` and `customer_to_customerid_dict`\n",
    "in this step when inserting into the order detail table.\n",
    "\n",
    "### Tasks\n",
    "\n",
    "1. Loop over the data and figure out the orders\n",
    "1. Create the orderdetails table\n",
    "1. Populate the orderdetails table with the orders\n",
    "\n",
    "\n",
    "### Hint\n",
    "- convert orderdate and quantity ordered to int!\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "fbdc9836b26a35e4758e0273ff66aef5",
     "grade": false,
     "grade_id": "cell-1668eb465ac6e575",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def step11_create_orderdetail_table(data_filename, normalized_database_filename):\n",
    "    # Inputs: Name of the data and normalized database filename\n",
    "    # Output: None\n",
    "\n",
    "    \n",
    "    # YOUR CODE HERE\n",
    "#     customer_to_customerid_dict\n",
    "#     product_to_productid_dict\n",
    "    \n",
    "    normalised_conn = create_connection(normalized_database_filename)\n",
    "    cur = normalised_conn.cursor()\n",
    "    \n",
    "    create_table_orderdetail_sql = \"\"\"create table [OrderDetail] ([OrderID] integer not null Primary Key,\n",
    "                                                                    [CustomerID] integer not null ,\n",
    "                                                                    [ProductID] integer not null ,\n",
    "                                                                    [OrderDate] integer not null,\n",
    "                                                                    [QuantityOrdered] integer not null)\"\"\"\n",
    "\n",
    "    \n",
    "    insert_orderdetail = pd.read_csv(data_filename, sep = '\\t')\n",
    "    orderdetail = []\n",
    "    for row in insert_orderdetail.itertuples():\n",
    "        quantityordered, orderdate, product = list(row[10].strip().split(';')), list(row[11].strip().split(';')), list(row[6].strip().split(';'))\n",
    "        name = list(row[1].strip().split(';')*len(product))\n",
    "        final_name = [int(customer_to_customerid_dict[ele]) for ele in name]\n",
    "        final_product = [int(product_to_productid_dict[ele]) for ele in product]\n",
    "        for ele1, ele2, ele3, ele4 in zip(final_name, final_product, orderdate, quantityordered):\n",
    "            orderdetail.append((int(ele1), int(ele2), int(ele3), int(ele4)))\n",
    "    \n",
    "    def insert_order(conn, values):\n",
    "        sql_statement = \"Insert into OrderDetail (CustomerID, ProductID, OrderDate, QuantityOrdered) Values(?, ?, ?, ?)\"\n",
    "        cur=conn.cursor()\n",
    "        cur.executemany(sql_statement, values)\n",
    "        return cur.lastrowid\n",
    "    \n",
    "    with normalised_conn:\n",
    "        create_table(normalised_conn, create_table_orderdetail_sql, 'OrderDetail')\n",
    "        insert_order(normalised_conn, orderdetail)\n",
    "    \n",
    "\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "f7af762def6aa49ff0dce98d8a9df35e",
     "grade": true,
     "grade_id": "cell-954b55c537feebf4",
     "locked": true,
     "points": 20,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "# 20 Points\n",
    "normalized_database_filename = 'normalized.db'\n",
    "data_filename = 'lab2_data.csv'\n",
    "step11_create_orderdetail_table(data_filename, normalized_database_filename)\n",
    "data = pd.read_csv(\"step11.csv\")\n",
    "conn = create_connection(normalized_database_filename)\n",
    "cur = conn.cursor()\n",
    "df = pd.read_sql_query(\"\"\"SELECT * FROM OrderDetail LIMIT 1000\"\"\", conn)\n",
    "assert df.equals(data) == True\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "aa7c7447f059e51154aedea27ffea6fa",
     "grade": true,
     "grade_id": "cell-7ae5ca80201ae802",
     "locked": true,
     "points": 20,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "# 20 Points\n",
    "normalized_database_filename = 'normalized.db'\n",
    "conn = create_connection(normalized_database_filename)\n",
    "cur = conn.cursor()\n",
    "df = pd.read_sql_query(\"\"\"SELECT count(*) FROM OrderDetail\"\"\", conn)\n",
    "assert int(df.iloc[0]) == 621806\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "e96ecf28e68aefd36df682ed7903f0dc",
     "grade": false,
     "grade_id": "cell-e0c62596e90f00ed",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def ex1(conn, CustomerName):\n",
    "    \n",
    "    # Simply, you are fetching all the rows for a given CustomerName. \n",
    "    # Write an SQL statement that SELECTs From the OrderDetail table and joins with the Customer and Product table.\n",
    "    # Pull out the following columns. \n",
    "    # Name -- concatenation of FirstName and LastName\n",
    "    # ProductName\n",
    "    # OrderDate\n",
    "    # ProductUnitPrice\n",
    "    # QuantityOrdered\n",
    "    # Total -- which is calculated from multiplying ProductUnitPrice with QuantityOrdered -- round to two decimal places\n",
    "    # HINT: USE customer_to_customerid_dict to map customer name to customer id and then use where clause with CustomerID\n",
    "    \n",
    "    # YOUR CODE HERE\n",
    "    sql_statement = ''' select Customer.FirstName||' '||Customer.LastName as Name, PRODUCT.ProductName, OrderDetail.OrderDate, PRODUCT.ProductUnitPrice, OrderDetail.QuantityOrdered, round(PRODUCT.ProductUnitPrice*OrderDetail.QuantityOrdered, 2) as Total\n",
    "    from OrderDetail  join Customer  on Customer.CustomerID=OrderDetail.CustomerID join Product on PRODUCT.ProductID=OrderDetail.ProductID  where Customer.FirstName||' '||Customer.LastName = '{}';'''.format(CustomerName)\n",
    "    df = pd.read_sql_query(sql_statement, conn)\n",
    "    return sql_statement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "0f6c9db2a0ab6ca0770f6b5ffe68207a",
     "grade": true,
     "grade_id": "cell-ec9cf3d14b979157",
     "locked": true,
     "points": 5,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "# 5 Points\n",
    "normalized_database_filename = 'normalized.db'\n",
    "conn = create_connection(normalized_database_filename)\n",
    "sql_statement = ex1(conn, 'Alejandra Camino')\n",
    "data = pd.read_csv(\"ex1_1.csv\")\n",
    "cur = conn.cursor()\n",
    "df = pd.read_sql_query(sql_statement, conn)\n",
    "assert df.equals(data) == True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "d0c36cd71ff3aba211875964ffe1782d",
     "grade": true,
     "grade_id": "cell-597a6e93985e2aeb",
     "locked": true,
     "points": 5,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "# 5 Points\n",
    "normalized_database_filename = 'normalized.db'\n",
    "conn = create_connection(normalized_database_filename)\n",
    "sql_statement = ex1(conn, 'Eduardo Saavedra')\n",
    "data = pd.read_csv(\"ex1_2.csv\")\n",
    "cur = conn.cursor()\n",
    "df = pd.read_sql_query(sql_statement, conn)\n",
    "assert df.equals(data) == True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "5b825e15d274c753676af81fe45a522e",
     "grade": false,
     "grade_id": "cell-b6d96550bc8ec339",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def ex2(conn, CustomerName):\n",
    "    \n",
    "    # Simply, you are summing the total for a given CustomerName. \n",
    "    # Write an SQL statement that SELECTs From the OrderDetail table and joins with the Customer and Product table.\n",
    "    # Pull out the following columns. \n",
    "    # Name -- concatenation of FirstName and LastName\n",
    "    # Total -- which is calculated from multiplying ProductUnitPrice with QuantityOrdered -- sum first and then round to two decimal places\n",
    "    # HINT: USE customer_to_customerid_dict to map customer name to customer id and then use where clause with CustomerID\n",
    "    \n",
    "    # YOUR CODE HERE\n",
    "    sql_statement = ''' select Name, round(sum(Total), 2) as Total from(select Customer.FirstName||' '||Customer.LastName as Name, Product.ProductUnitPrice*OrderDetail.QuantityOrdered as Total from OrderDetail \n",
    "                     join Customer on Customer.CustomerID=OrderDetail.CustomerID join Product on Product.ProductID=OrderDetail.ProductID  where Customer.FirstName||' '||Customer.LastName = '{}')  group by 1;'''.format(CustomerName)\n",
    "    df = pd.read_sql_query(sql_statement, conn)\n",
    "    return sql_statement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "4ea134f5214f8ca174521202affd220d",
     "grade": true,
     "grade_id": "cell-54fdb84b73431dd0",
     "locked": true,
     "points": 5,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "# 5 Points\n",
    "normalized_database_filename = 'normalized.db'\n",
    "conn = create_connection(normalized_database_filename)\n",
    "sql_statement = ex2(conn, 'Alejandra Camino')\n",
    "data = pd.read_csv(\"ex2_1.csv\")\n",
    "cur = conn.cursor()\n",
    "df = pd.read_sql_query(sql_statement, conn)\n",
    "assert df.equals(data) == True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "27401cfc758471b37725bbd87b526448",
     "grade": true,
     "grade_id": "cell-02e468b7722bcf94",
     "locked": true,
     "points": 5,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "# 5 Points\n",
    "normalized_database_filename = 'normalized.db'\n",
    "conn = create_connection(normalized_database_filename)\n",
    "sql_statement = ex2(conn, 'Eduardo Saavedra')\n",
    "data = pd.read_csv(\"ex2_2.csv\")\n",
    "cur = conn.cursor()\n",
    "df = pd.read_sql_query(sql_statement, conn)\n",
    "assert df.equals(data) == True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "9d0c2cc36b2095292d7b348cdb638391",
     "grade": false,
     "grade_id": "cell-6868e040915abd2f",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def ex3(conn):\n",
    "    \n",
    "    # Simply, find the total for all the customers\n",
    "    # Write an SQL statement that SELECTs From the OrderDetail table and joins with the Customer and Product table.\n",
    "    # Pull out the following columns. \n",
    "    # Name -- concatenation of FirstName and LastName\n",
    "    # Total -- which is calculated from multiplying ProductUnitPrice with QuantityOrdered -- sum first and then round to two decimal places\n",
    "    # ORDER BY Total Descending \n",
    "    # YOUR CODE HERE\n",
    "    sql_statement = ''' select Name, round(sum(Total), 2) as Total from(select Customer.FirstName||' '||Customer.LastName as Name, Product.ProductUnitPrice*OrderDetail.QuantityOrdered as Total\n",
    "                    from OrderDetail join Customer on Customer.CustomerID=OrderDetail.CustomerID join Product on Product.ProductID=OrderDetail.ProductID) group by 1 order by Total desc; '''\n",
    "    df = pd.read_sql_query(sql_statement, conn)\n",
    "    return sql_statement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "4e842bd32bc6794c38933f9011c8e4e3",
     "grade": true,
     "grade_id": "cell-c088926530ebd106",
     "locked": true,
     "points": 5,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "# 5 Points\n",
    "normalized_database_filename = 'normalized.db'\n",
    "conn = create_connection(normalized_database_filename)\n",
    "sql_statement = ex3(conn)\n",
    "data = pd.read_csv(\"ex3.csv\")\n",
    "cur = conn.cursor()\n",
    "df = pd.read_sql_query(sql_statement, conn)\n",
    "assert df.equals(data) == True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "bb33a3f50d8f51db06b2ea78c6761b49",
     "grade": false,
     "grade_id": "cell-8cbed5eeeb2b66bf",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def ex4(conn):\n",
    "    \n",
    "    # Simply, find the total for all the region\n",
    "    # Write an SQL statement that SELECTs From the OrderDetail table and joins with the Customer, Product, and Region table.\n",
    "    # Pull out the following columns. \n",
    "    # Region\n",
    "    # Total -- which is calculated from multiplying ProductUnitPrice with QuantityOrdered -- sum first and then round to two decimal places\n",
    "    # ORDER BY Total Descending \n",
    "    # YOUR CODE HERE\n",
    "    sql_statement = ''' select Region, round(sum(Total), 2) as Total from(select Region.Region, Product.ProductUnitPrice*OrderDetail.QuantityOrdered as Total from OrderDetail join Customer on Customer.CustomerID=OrderDetail.CustomerID join Product on Product.ProductID=OrderDetail.ProductID join Region on Region.RegionID=Customer.RegionID) group by 1 order by Total desc; '''\n",
    "                    \n",
    "    df = pd.read_sql_query(sql_statement, conn)\n",
    "    return sql_statement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "593d6829b6de30b6efb04177f50d5f7f",
     "grade": true,
     "grade_id": "cell-6c417c7bca62f7e9",
     "locked": true,
     "points": 5,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<AxesSubplot:ylabel='Total'>"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAVcAAADnCAYAAACqjBXZAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjMuMSwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy/d3fzzAAAACXBIWXMAAAsTAAALEwEAmpwYAAA9yUlEQVR4nO2deXhb1bW33yXJQxIPmUNCCA5kBkMgCSUhM5Tb3tAChZaxNZ3okJbL1K/+Sgdz2/s17S2F0hZCaXsT2lJSZoh7S5gSSCBAZpE4A2QiEyGTMzgeJK3vj71FhJFtyZZ0ZHu/z6Mn0jn77L2kyD/ts/baa4mq4nA4HI7U4vPaAIfD4eiIOHF1OByONODE1eFwONKAE1eHw+FIA05cHQ6HIw04cXU4HI404MTV4XA40oATV4fD4UgDTlwdDocjDThxdTgcjjTgxNXhcDjSgBNXh8PhSANOXB0OhyMNOHF1OByONODE1eFwONKAE1eHw+FIA05cHQ6HIw04cXU4HI404MTV4XA40oATV4fD4UgDTlwdDocjDThxdTgcjjTgxNXhcDjSgBNXh8PhSANOXB0OhyMNOHF1OByONODE1eFwONJAwGsDHB2bkvJKP9AfGAicbP+NffQCIvYRbuYRAvYCO4D3Yv59d+usGYcz944cjsQQVfXaBkcHoaS8cgRwgX2MwojnSYA/zUPvATbYx3pgObB066wZ9Wke1+FoEieujlZRUl6ZC4zFCOlEYALQ21OjPspxYAnwMvAS8NbWWTPC3prk6Ew4cXUkhL29nwZciBHTsUC+p0Ylx2HgVU6I7aqts2a4L78jbThxdTRJSXmlD5gEXA1cAfTx1qKUcgCYD/xp66wZr3htjKPj4cTV8XEqikuPaJerS+v+dAMwwGtzMsBG4M/AnK2zZryfrkFE5G5gm6reY18/B7ynql+zr+8Cdqrqr5PocypQr6qvpdxg0/9W4AhmURHgFVW9KR1jdTScuDoMFcVdgC8C3wTOAbi+/v8GF0dKSz21K7OEgErgj8D/ptpHKyJXAl9Q1S+IiA94CyOM4+3514FbVHVpEn1WAEdV9VdJXBNQ1VCCbbcCY1V1X6L9t2acjogT185ORXFf4DvAt2i0ILU8MvSVK+rvnOyJXd6zE5gD/HnrrBmbU9GhiAwA3lDVU0SkFLgdE6Z2FVADvA/0BUqBXwMFwD7gBlXdLSI3YX78QsA6oBxYiplVfgB8FxMtMRsYZIe9WVWXWBE+HTgN2I6JrBhkXw8C7lHVe+PYvJU44ioiC4HbVXWZiPQGlqlqiYjcAHzO2u4HLsfcFZxm3+ONqromxp4hmO/dL1X1Qdv394AvAHnAk6r6k6Q+6CzBxbl2ViqKRwG3AtdjvsQf4xzZVJpDqL6BQG5GbcsOTgbuAH5QUl75d+BHbRVZVd0lIiERGYSJrnjdjjMeqAaCgAK/BS5V1Q9E5Crgv4CvYMR0sKrWiUh3VT0kIrOJmbmKyMPA3aq62I7zHDDSmjAKmKiqx624jcAsUhYCG0TkflVtiGP6yyISncXPVdW7W3ir5wJnqeoBEfktsFJVLxOR6cBDwGjb7izgfKAbsFJEKoEzgaHAeYAAz4jIZFVtd35xJ66djYriIcDPMDMDaa6pT+hxpX/RG38PX/iJjNiWnQhwLfD5kvLKPwA/baNf9jWMsE7AzE5Pts+rMaFjwzEC87yIgJn97bbXrgH+JiJPAU810f9FwCh7LUCRiBTY58+o6vGYtpWqWgfUicheoB9mc0ZjpiXpFnheVQ/Y5xMxi6Go6ksi0ktEiuy5p609x0XkZYygTgQuBlbaNgUYsXXi6shSKor7AT8Gvg7kJHrZt/zP6t/DF6bNrHZEDjATKCspr7wb+O+ts2YcaUU/SzBiWgq8jdlldhsmVOx/MGK+NuqHbcQMYDLwGeAO61pojA84X1VrYw9asT3WqG1dzPMwyelBiBPb5xuH5DUepyka+yQV8/5/rqoPJGFLVuJyC3R0KooLqSj+KfAu8G2SEFaAU2TvucUcPZQO09opBcCPgM0l5ZU3l5RXxnWpNMNrwCXAAVUN2xled4xr4DWML7SPiEQXuXJE5Ay7AHaKqr4MfB8otrYcwdzWR1mA8b1irx+d/FtMiK3AGPv8ymbavQpcZ22ZCuxT1eh25UtFJF9EegFTMQt8zwFfic62ReRkEembauMzgRPXjkxF8ZWYP9YfYvxaSSNC7jcDz65JqV0dg97A3cCGkvLKMhsTnAhBe+3SRseqVXWfqtZjxOoXIrIaWIWZ6fqBv4pIEHPLfK+qHgKeBS4XkVUiMgm4CRgrImtEZB1mAaytvGz7XyUiD9ljvwK+JSIraX5nXgUwRkTWALOAsphzazCbOpYCP1XVXaq6AHgYeN2+18f46I9Hu8FFC3REKooHAr8HPpuK7qq165qz6/54Vir66sAsAb68ddaMTV4b0h5oTQhZe8PNXDsSFcU+KopvwoTppERYAYql5qzBsmt7qvrroFwArCopr/yPkvLKZhcKHZ0DN3PtKFQUD8bcTp2fju6fDZ+/8LsNN01NR98dkFcws9iUxMc62idu5toRqCj+AsYPlxZhBfg331uD09V3B2QyZhZ7ndeGOLzDzVzbM2bL6j3AjZkY7rr6H7y9JHLmmZkYqwMxF5i5ddaMRMOTHB0EN3Ntr1QUjwTeJEPCCnBL4LEDLbdyNKIMWF5SXjnaa0McmcWJa3ukovjfMcKa0VnkubLpzACheNsjHc0zHFhSUl75Ga8NcWQOJ67tjYri7wLPYALIM4pPtOeV/ldWZHrcDkJX4MmS8sqveW2IIzM4cW0vVBT7qSi+F7iX9NekapJv+Z9xTvrW4wceLCmv/LHXhjjSj1vQag9UFBcAj2D2lnuKKnVn1/2h9jAFxV7b0s6ZjVnoinhtiCM9uJlrtlNRXAy8QBYIK4AIed8IzHfbYdvON4HHS8or21MdMkcSOHHNZiqKuwPPA1mV8u86/4tu1poaLgNeKCmv7OG1IY7U02ZxFZE7RGStTRSxSkRaJQQiMlVEJsS8nmPLYiRy7WUioiIyojVjJzjGWBH5WKb2tFFR3BN4ERiXsTETpJhjpSWy+z2v7eggXAAsLimvPMVrQxyppU3iatOiXQKcq6pnYRL1tvaPbiom+09ruAZYbP9NObYW0LKMFWarKO6FEdZzMzJekoggtwYee9drOzoQo4DnS8ore3ptiCN1tHXm2h+Tn7EOwKZM2wUgIheKyEoRCYrIn0Ukzx7famvuRGeDC0WkBOODuiUmdRrAZBF5TUQ2NzWLtXkfJwJfxZSAjh6fKiKLRORpe/0sEblORN60Np1u2/URkcdF5C37uMAerxCRv4jIEuAvtr/50TFF5H9sP2tE5Ap7/H4RWWZn8ne26hM1PtYXOVEKIyv5lO+tEq9t6GAMB55xPtiOQ1vFdQFwiohsFJH7RGQKgIjkY4q7XaWqpZgM599qqhNV3YpZPb1bVUer6qv2VH+McF6CyQUZj0uBf6nqRmC/iIyJOXc2RrRHYiqbDlPV8zDVPaMJhX9jxx2HKUfxx5jrRwEXqWrjGfGPMPk3S+2M/SV7/A5VHYupDTRFRJJL01dRnAM8Ye3OanIlVDLB9/Zar+3oYFwAPOSyanUM2iSuqnoUk438Rkz1yXm2+uNwYIsVPDD7q1tTRfQpVY2o6jpMfZ94XIMJU8L+GyuEb6nqbjuzfhfzYwAmOXGJfX4R8DsRWYUJzm+u5hAx1/w++kJVD9qnXxCRFZgkKmdgxDkZ/gRMT/Iaz7gl8Nh+r23ogHwe+G+vjXC0nTbX0FLVMLAQWGgzh5dxorhYPJqrvdOY2Bo/H/s1F5GeGDEqFRHFBGmrLc3b+PpIzOsIJ957MjWHmkREBmNKJY9T1YMiMoeW398JKop/hpldtxvG2O2wIQJJlY5xtMhtJeWV27bOmvFbrw1xtJ62LmgNF5GhMYdGA9swpUVKRGSIPf5FYJF9vpUTtXeuiLm2cS2gRLgS+IuqnqqqJap6CrAFmNTCdbG0pubQ85hiddFregBFGDGuFpF+wKcTtqCi+OuYMs7tCp9oz8/5X23uh9TReu4pKa+81GsjHK2nrT7XAmCuiKyzNXJGARV2Fvhl4FE7m41gfKoAdwK/EZFlmIqTURrXAkqEa4AnGx17nOSiBlpTc+hnQA8RedvWOZqmqqsxM/b1mKTVSxIavaJ4MnBfEvZmFd/2PxNuuZWjFfiAv5eUV2ZVjLMjcdz2Vy+pKO4PrABO8tqU1uK2w6adD4BPbJ01Y4vXhjiSw+3Q8oqK4gAwj3YsrGC2w94YqHTbYdNHH+CvSVSXdWQJ7j/MO/6T5HzDWcv1/heKvLahgzMB+F6LrRxZhRNXL6go/iRQ7rUZqaKYY2edKnt2eG1HB+c/S8orS702wpE4TlwzjUnGMpc4oWXtFbsd9h2v7ejg5GI2GLiwt3aCE9fMcxdm51mH4tO+N0/12oZOwGjgJ14b4UgMJ66ZpKL4IuArXpuRDnIlNPh831q3HTb9lLvwrPaBE9dMUVHcDfiD12akk1sDj+3z2oZOgB/jHujitSGO5nHimjn+CxjstRHpZKxsdNVhM8Mw4BdeG+FoHieumaCi+Fxitth2VHyivS73L3bbYTPDd0rKKy/w2ghH0zhxzQx30Uk+62/7n3bbYTOD4LJnZTWd4g/eUyqKL8FUWegUlMj75xRyrNprOzoJ40vKK69ouZnDC5y4ppOKYj/wS6/NyCQi5LvtsBnl5yXllW1OHepIPU5c08vXMFUQOhVuO2xGGQp8w2sjHB/HiWu6qCguwKRX7HR05+hZg+R9tx02c9zham9lH05c08eNNF2apkMjgtzitsNmkv6Y75sji3D5XNOBKTT4LtBpa9HXaWDL8LqHOnRcb5axCzht66wZdS22dGQEN3NND9fQiYUVIE9Cgz8h69Z5bUcnYgDwda+NcJzAiWuqqSgWXO5NAG7NeewDr23oZJS7rFnZgxPX1PPvwJleG5ENjJMNZ/gJh7y2oxNxMub758gCnLimnlu9NiBb8In2vty/eIXXdnQybvDaAIfBiWsqqSgeDEzz2oxswm2HzTgzSsor+3hthMOJa6q5gQ5UYSAVDJY9bjtsZskBrvPaCIcT19RhFrLKvDYj2xAh/2uBf7rtsJnlBq8NcDhxTSXTAVfqJA5f8j9f6LUNnYyzS8orR3ttRGfHiWvq+LLXBmQr3Tl6ttsOm3Fu8NqAzo4T11RQUdwFuNxrM7IVEeTmwONuO2xmudbFvHqLE9fUcCHQ1WsjspkZvqWDvLahk9EHmOG1EZ0ZJ66p4RKvDch28iR02nlS5bbDZpZrvTagM+PENTW4GUICuO2wGWeq1wZ0Zpy4tpWK4nOAgV6b0R44T9a77bCZpU9JeeUor43orDhxbTvOJZAgPtHel/mWuOqwmWWy1wZ0Vpy4tp1Pem1Ae2Jm4OkGr23oZEzx2oDOihPXtmCSYo/z2oz2xGDZfW4BNYe9tqMT4WauHuHEtW2MBlztoiSw22FXe21HJ2JASXnlEK+N6Iw4cW0b4702oD3itsNmHOca8AAnrm3DiWsr6MGRs0+RvTu9tqMT4VwDHuDEtW04cW0FdjvsJq/t6ES4masHOHFtLRXFvXFZsFrNJb6lnbqAY4Y5taS88mSvjehsOHFtPcO9NqA9kycNp4+T9VVe29GJOM1rAzobTlxbzzCvDWjv3Bp4dK/XNnQi3F1WhnHi2nqGem1Ae+cTvvWj3HbYjOHENcM4cW09bubaRnyifT7re81th80MTlwzjBPX1uPENQV8J/CU2w6bGZy4ZpiA1wYkgoic29x5VV2RKVtiON2DMTscp8nucwqoOXyUrkVe29LBceKaYdqFuAJ3NXNOMcUBM0dFcRGu8kBKEKHLV/3/u/w34Ssmem1LB8dVgsgwoqpe29D+qCgeArgg+BSxXwtXjql74Byv7egE9Ns6a4aL0MgQ7WXm+iEiciYwipiEKar6UCZt+GtRYQ+f6uv9wuH8vqFwt97hcGHPSLhHnrokLq2hJ0dGD5QPdu3QPgO8tqWDcyrgxDVDtCtxFZGfYEpXjAL+CXwaWAxkVFx/0atHX+JtfVWt8cOhXNUjXVSPFUYitT3CkVCvcDjSNxyWfqFwoF8olNc3HO7SJxwu6BkOdy+KaJGAZNL+bMNuh914e8M3nbiml0HAW14b0VloV+IKXAmcDaxU1S+LSD/grx7Y0T3uUZGuYeh6XITjwAG/n20tFTdWDQkcyoHD+ZHIsW4RrekeidT3DIfDfcJh+obC/n7hcE6/UKhLn3C4W+9wpKhHONwjBzpU2eRLfK+fcjvf9NqMjo5bNMwg7U1cj6tqRERCIlKEucXxYo966r6kIgGF3vXQu97v57AfdidyneoRP1TnqR7pGtGaokiktkc4HOodDmsfM0vO6RcO5/UNhbv2CYcLe4bDxQWqWZvqL18aTh8rG6qW6fCRXtvSgcn12oDORHsT12Ui0h14EFgOHAVe98AO7z83kcIwFNaIUOODffhpcTKrWu+DQzmqh/NVjxVE9Hj3SLihVzgS7hMKS79wyN8vFM7tGw7n9wmFC3qHw0XdI5EefvBn4i3dGnh077UNP3Timj461N1OtuO9SCSBqn7bPp0tIv8CilR1jQemtM/NFyK5EehbJ9K3Dqj2w86WvgImnKQ6AIfyVI92jUSOF0UidT3DkXCfcDjSNxT2WbdFXt+wXdwLR7p3UU06VO0TvqpRPiLhCL6MiHknxIlrBmlX4ioiL6rqhQCqurXxsUyakuHxvENEgOIQFIdEOObz8QHwbkvXqdb64GB0ca8gEqntHo409AqHtd8JX3Ke9SUX9ApHiosikV6f9b224qnIxLHpf2OdkoTEVUTCQDDm0COqOiuZgURkKlCvqq8lc10S/W8FjgBhe+gVVb0pHWO1lnYhriKSjwna7y0iPTghbkWAF3kqO4+4thaR/Aj0rxXpXwsc9Pt5r+XFvfBNLx7cPP6U4Pba7jW5h3L3Fvhya/x5eTUNubnHI7m5xzUn97g/J1CfEwjU5wX8Dd18vnCBiHYDbZ93ExmkIZwbgRmJND2uqqPbONxUjNsuYXEVkYCqJpPIZ5qq7kvWsFaM0yrahbgC3wBuBgYAsVtdDwO/88Ae94ecBi57XZeW7IwMWuurbxjygd8/refVQ/b4qz+o8u/8YIdvf78GwiOReJ+9RgKBukO5ubWHc3OPH8vNPX48N6+mLi+3JpyTe1xzc2vJyanN8fsb8vz+hi4+X6SbSKQ7UCTSeX4o8wL1bXK3iMiPgc8AXTCi+Q1VVRG5CfgmEALWAeX2dVhErge+C6wHZnNip9jNqrpERCowW8lPA7aLyAbb5jT77z2qem8SNi4EblfVZSLSG1imqiUicgPwOaAA8IvI5cCf7Tg1wI2quibGniFAb+CXqvqg7ft7wBeAPOBJVf1Jc7a0C3FV1d8AvxGR76rqb722B7Pl1pFCutZq9dWvRIYdLtq1P6fL1SM2Hv7L4i1HgzlTT7r64IV5pVMBjlO/f5N/9/pN/t0clGPDEXqbq8UXCuX3DIXye9bUdE9iVI0EAnUHO5Eo1yfYrouIrIp5/XNVnQf8TlX/E0BE/gJcAjyLEdPBqlonIt1V9ZCIzAaOquqvbPuHgbtVdbGIDAKeA6KLl6OAiap63IrbCGAaUAhsEJH7VTVegp+XrQsDYK6q3t3C+zoXOEtVD4jIbzEhnZeJyHRMrPxo2+4s4HygG7BSRCqBMzFpRs/D3Lk+IyKTVfWVpgZrF+IawwP2VzJacG0h8EATH3w6OZrh8To83380vMqnTOlasycAkFt49Zi6Q/dvX7BrzsSBXYetGN/3s726SO6pZ4VPveCs8Kkoqnvk0LqqwM69O3z7e9UTOiP+rLY52iTKh3Jza6ubEGXJCdT5/YH6/CwT5eOJtmvCLTBNRP4PxkXXE1iLEdc1wN9E5CngqSb6vAgYZVz4ABSJSIF9/oyqxtpWqap1QJ2I7AX6ATvi2ZOkW+B5VT1gn08ErgBQ1ZdEpJcN7wR42tpzXERexgjqROBiIJoiswAjth1GXO/DOOXvs6+/CNwPfC3DdhzK8HgdmmE7dP2IHUwEyG041gPVIyI5hblF1+fXH55TvaNm47lPbLvn+IS+ly7s3+X0iSISEET6a49R/Rt6jAKopf7gJv+eqo3+XRE7q+2TPovFFwrl9wiF8nu0M1E+1toL7brHfcBYVX3PzjCj271nYCY8nwHuEJHSOF34gPNVtbZRv/Hsqot5HiY5nQpxwm3XeDt6ou+/8Z2pYmarP1fVBxI1pF2Ia4wDepyqnh1z6iURWe2BSQc9GLNjoqo/mBcOS0wsrS/SsCfizy30+XueGuj6yTdDNc+PC2uoy6vvPz61Z17/DVNPuiqc48sbFdtNPrk9SsODJpSGB6Govi/V66sCO/a859vf085qsyC8K4WinFtTn2sW+ogjygUikSKguJEoV7fB+KhQ7bMzziuBx0TEB5yiqi+LyGLgasys7ggf3WyzAON7/W8AERmtqqvaYE9TbAXGAG9aG5viVeA64Kc2smGfqh62Yn+piPwc4xaYinF7HLdt/6aqR0XkZKBBVZvM1dAuxBXzQZ2LcZCfrqrvAojIaZwIxcgkhzwYs0Ny9SuRxV3rmRR7LKfh6KE6f08AAnml50Ua3l0Yadg8FeBA3e7hT2y7J3Jur08uGlJ4zlgR6da4T0HkJO0+4qSG7iMAamk49I5/d9VG/+7wQTk6VIV+6X9nqSQ1ouzzhfZemFhyzsY+13+parmIPAi8DezhRI4CP/BXESnGzO7utT7XZzHieylGVG8Cfi8iazC68wq0eb9zrM91jap+CfgV8A8RuRGobObaCuDP1p4aoCzm3BrgZcyC1k9VdRewS0RGAq9bAT4KXE8ziXDaRcpBEVmpqudYx/McYLM9VQJ8WVVfzqQ9pXNLhwIbMzlmR6SwRg88+JswPuO/+5Dlo295pbr7kKhfHdVIuK76gdXo8Y8kTe8W6L5jev9r93QNFCYcF6uo7pXDG6sCO3a/59vXo87MatvLJKOtDKuoqHCpMpvBujs+XIhrC+3lS9VHRG61zx/gxC1kGDgH8yuTSfZneLwOyQ/mhdf6+OisFaBbzW6t7j7kw9ciPn9e0ZcG1VX/YTdo/+jxY6FDA599776Bw4rGvja657ShIr4W/ayCSD8tHt6voXg4QB0N1e/496zb6N8dPiBHhqhwUoreXjbyvtcGdCbay8x1N2bhKq5TX1XvzKxFUDq39AjGt+RoBaVbIm//8JHIGfHSLe4YMGnpxmFXn9/4eLhhW7Dh6OMjiLPTKNeXf3DqSVev7ZHXr00VDfZK9cYq/85d2/37utfRcGYHmtXWVlRUdPHaiM5Ee/ni7I7G12URW4B4q6KOFvBFNPy9xyOBpvLYFhzb1T3ecX/OqaWRvHNeCdetnNz4XH2ktseCXXMmntx12MrxfT/b0y/+VtWM6qvFw/qGiocRgnpCh82sdlfDfjOr7d9yD1mLm7VmmPYirtkYsL0ZJ66t4ksvRhbnNzClqfNda/Y0ueCU03Xa5EjDtiUaOXBBvPM7azae88S2u2sn9L1s4QAbttVaO3MJFI0KDzx/VHggAB/I4U3r/Tt3bvN/UFxrZrXtKRHKO14b0NloL+Ka6cQsibC55SaOxvQ4ons/vaz5fevRWFdE4uafzS26dnTdofvfgfCQeOcjGs5f/P7jU3vm9t84tf9VDTm+vDNSYDp9tGhon1DR0EmhkdQTOrLZ//7KDf5dtfvkyOkq6kWOi2RY77UBnY12sUc+ZldFNuHEtRX88JHwJoHiltr5Ig17mjonktstt+haPyaWskkO1O8e9sS2e0ZuOrx8kaqmdFddLoHCEeGTz7u0ftzkr9ZNP/myuvPeHREasChfc1agCW8zzSRViTQSkTtEZK2IrBGRVSLyiXQYIyJTRWS+ff5ZESlPcf8DROSxVPaZLO1l5pqNuJCWJBmzKbLqlH3EvZ1vTGysazx8/j6DA12mLw0df+ljC1+Nm67Y/8KUDdVv7Zze/7qqroHCccnYnCi9tfD0iaGRp08MjaSB0NHN/r2rNvh31X4gh09T0YHpGDNJWpy5ish4TL6Ac22egN5koHqBqj4DPJPiPnfR/CaCtNMuZq5ZysqWmzii+MPacMtTkYSjK/JrD7S4Dz6QP/p8X2DQokT6OxaqPvnZ9+4bt3L/i6+pRj5I1I7WkEOgYHh4wHmfrR87+at10wdeXnfe5pGhkxd10dzl6Ee2dmaStxNo0x+zU6kOQFX3qeouERknIq+JyGoReVNECkWkREReFZEV9jEBPpyRLhSRx0RkvYj8TWzUvYh8yh5bgclQhT1+g4j8zj6fIyL32vE2i8iV9niBiLxoxwrazQmIyCwRmRnTV4WI3G7te9sei2trumkXoVjZSunc0u14U8Or3fGNf4YXXrhapybafv2wqxftGjCpyUWvKKqRUF317LVo7dkttY2S68s/NPWkq4I98k76WIxtumkgXLPFt3fthsDO4x/I4ZKI6KCWr2ozuysqKlqsrGu3tS7GJGZ5AZiHKaO0HrhKVd+yyU1qMDPaiKrWishQ4O+qOtZuJX0aOAPYBSwBvgcsw9ztTccsrs0DuqrqJTYd4FhV/Y6IzMFsO70Kkx3rGVUdYhcmu9otqr2BpZjEKaMxaQmn2PewDvg3TCz8fFU9U0S6xrO1tR9moji3QNtYhhPXFuldrbunr9akbscLju7MS6SdiC+QV/Sl/nXVD+4F7ZvINfWR2u4Lds2ddHLXoSvH97201WFbrSEHf9dhkf7jhtWbqK4DcnTLev/O7Vv8e7sdp/5M5GPJRlLBipabgN0zPwazsWMaRgD/CxMK+ZZtcxjAbjv+nYiMxmzmGRbT1ZuqusO2W4XZSXkU2KKqm+zxvwI3NmHKU6oaAdaJqfAMJmLo/4nIZCCCSZLfT1VXikhfERkA9AEO2sQyJTH95TRja9pw4to2lgGXe21EtvPjh8NbBcYnc01Tsa7xEF9B35yCS1c3HH2qJ0l8p3fWbLJhW5cuGtBlyAQRyXhoVU8tGDwhNHzwhNBwQoSPb/XtXbY+sOvYXqkuiYimSvQTdmGpahiTynOhiASBmU00vQUTO3s2xr0Ym+2qLVmtGl8fDcO8DiOeY1S1QUyZl+gP0aMY/+pJmB+EZGxNG05c28Yyrw3Idiasiyw/6VBywgrNx7rGw59z2tmR3NJF4fpgi66EWEzY1hNTeuSetGla/6vrUxW21RoC+LsMifQfO8TOag/KsW3r/Tu3bvHv7VZD3RkIrd1hldD3VESGY26fo4u1ozFRBp8SkXHWLVCIyRBVDOywpe7LaLlC8HqgJCbx0jVJvodiYK8V1mlA7A/PPExF6N4QN346WVtTghPXtvEm5pc5C9LZZR+BkNbNfDbSqzXXthTrGo+cbp+cEgltX6qR6pYiCD7Gwfo9Q5/Ydk/knJ4XLhpaNGaMnEjk7Bk9tNup40PDTh0fGkaIcO023wfL1vt3HnvfVz0oIjo4wW4iQEKLfpjt3L8VU74+hPGN3gj8jz3eBSOsF2Fyuz4uIl8C/kULuVKtv/NGoFJEajAp/xL+vwX+BjxrZ9PLiIl+UNW1VvR3quruONcmZWuqcAtabaR0bulSIC2xgO2dm54OL5y4LvFFrMYsnHT3pog/d2gy16jWHak7dP8+iCQqPh+jW6B45/T+1+7qGihKS9hWKjgkx7bbWW3+MerORGiqlPlbFRUV52XUOAfgQrFSwXNeG5CNnHRA37tgnbbpRyen4eihZK8RySvMLbwmTBtmJyZs6/5xK/e/+Hq6w7ZaS3ftNuj80LDJ19RNPO/LddP80+vPXD4g3GORT6Xx5pYXPDHQ4dwCKeA54MdeG5Ft/Pjh8G5pYyRFfu2B43X5TW8kaApfoN+QQJdJS0LHX01ow0JTbDy8bPzWo28fmnLSVa/29CBsK1H8+PJOi/Qbc1rEuKmrpWbHev/OLZv9e/PqCbkff49w4tp23sCUz2hxS2dnYdrqyJu9j9DmW9HGeV2TIZA/7oJww+ZFGtqZ1AJXY+ojtd2f3zV30oCuQ1ZN6HtZd7/4S9rSXyYo1q4DPxEaOvAToaFHMHGqDg9wboE2EiwLhoEXvbYjW8ht0Jqv/yvSYsB6IiQa69qkLQVXTkByE9mZ1CK7at4Z/cS2u0/aeWzTQg+qDbeWBQNnTcrGPAedAieuqeFprw3IFv7j6cibgQgp2UufTKxrPET8OXmFX+oFJFN+uUkiGs5fvPeJqc/vemhrQ6RubSr6TDPzvTagM+PENTU8SYYCk7OZU/bqlrGbNGX7tpONdY2H+Iv653S7ZDspLGRpw7ZGbqxe9kqqs22lkDrgKa+N6Mw4cU0BwbLgEdwsgR/9PXxQUphF6cNY1zbizx12ri935OJU2BSDb+WBFyfP3/HA4ZrQ4TdT3HcqmD9w1qRDXhvRmXHimjoe9toAL/n0W5HXu9dwbsstk6O5vK7JkNP1U5PxFaZcBGtC1QOefe/+81bsfyHbwrb+4rUBnR0nrqnjn8Ahr43wgi51euRLL7Y+aL85WhPrGg8RkbzCLw4H37ZU9NeYTYeXj39q+29zDtTteTUd/SfJPsz30eEhTlxTRLAsWAc87rUdXnDbE5EVfk1PSepE8romivjyi3MLr6rFbOFMOdGwrVfff3xVWENb0jFGgswbOGtSe4lo6LA4cU0t93ltQKY5bbduKt2qbQrWb45uNbtTuj/bF+g/PJA/fnkq+2yMCdu6p/+OYxu9CttyLoEswIlrCgmWBVcAr3ltRya545FwjaRxM0pbY13jEegyfqL4+6X19j2i4fwle5/0ImxrxcBZk97I4HiOJnDimnru9dqATHH5ksjiwloSrgDQGtoa69oUuYVXnQc5CRXtawsxYVspL5LYBL/JwBiOBHDimnoeB3Z4bUS66XZcq696NTI83eOkItY1HiKBvNyi6wuBg+novxG+lQdenJKBsK09wCMtNRIRFZG7Yl7fLiIVyQxka2VNiHk9J1rvKp3YGlk7bWXa6KN7usdtDU5cU0ywLBgC7vfajnRT/mh4tU/pk+5xUhXrGg+fv8fAnK6ffheT8zTtNArb2puGIe5NcLtrHfA5W4sqaWw9q6lASjaMiCEZLbpbVUfHPA4lMVbGci87cU0PD2BqBnVIRrynVcN2MjFT46Uq1jUe/ryRY305Q19JV//x2HR4+fgnt9+bd6Bu96uauoTKR0j8Rz0E/AFT/uQj2EqpL4nIGlttdZA9PkdEZovIG8A/gG8Ct9iZYzRj2OTGVVvttd8Tkbdsn3fGjLNBRB7CVKadJCJVIvKgiKwVkQU2OXdCxFaQta/n22KJiMhREblLRFYD40XkVhF52z5ujrEnWq22Skz12q723BgRWSQiy0XkORHpn4hNTlzTQLAsuB/4rdd2pANRjZT/IxyRDH53UhXr2mT/3S6ZjHTLaMmehkhd8fO7Hpq0eO8Tq1MUtjU7yR1ZvweuE5HG2dx+C8xV1bMw2f9j1xAGAhNU9XPAbE7MIKOLg/2BicAlwCwAEbkYU6X1PEzZmDG2yCD2+H2qegawzb7+vX19CLiiCdujor5KRF5O4L12A95Q1bMxYXhfxiS4Px/4uoicY9sNt/aMBA4D37Z11X4LXKmqY4A/Y4o2togT1/RxF2Y20aG4elFkSdd6MlpnKpWxrvEQEV9e0RdPA8m4r3xXzTujn9h6z4A2hm1VY8UsUWwV14eAmxqdGs+J3YZ/gY/coTxqCxg2xVOqGlHVdUDUV36xfazEVKEdgRFRgG2qujTm+i2quso+X46pGhuPWLfAtGbsiRLmRAz6ROBJVT1mFxifwFS7BXhPVZfY53+1bYcDZwLP20q2P4TEEhO1KK5eO79F5DJrw4hkxkzSvrEiktJVfjt7vTuVfXpN0THdf9nremamx011rGs8xNe1Z27B5w/z0cqjGSFCOM+Ebc3dWh+pbU2KxF8MnDXpQCuuuwf4KmZmlwgtVXeIV7VVgJ/HiOEQVf1TE/21pWpsiI/qWWyJ8toWfhSiNP6eKcb+tTH2l6rqxYkYlMjM1Wvn9zXAYpKvFpkQIhJQ1WWq2vgXPBXcRYrS3WUDP5gXXifQI9PjpiPWNR6+nIGj/HnjPEvCcrD+/aFPbvvNqA3Vby3SxBfxdtHK8CtVPYDxn3415vBrwNX2+XWYQoLxOEJiBQafA74SLfgoIieLSN/W2NsCW4HRIuITkVOgyWTtrwKXiUhXEekGXM6J9zhIRKKViq/F6M4GoE/0uIjkiEhCd26JiGtGnd+N+i/ATM2/yon/8OhMeJGIPG2vnyUi14nImyISFJHTbbs+IvK4daa/JSIX2OMVIvIXEVkC/MX2Nz86poj8j+1njYhcYY/fLyLLrLP9zgQ+N4JlwcMk6J/Jds7aHAkOfj9zi1ixpCvWNR45XSdNEn/vVGfQSgbfqgMvTZm/Y/bRY4mFbd05cNakmjaMdxemJHWU7wJfFpE1wBeB/2jiumeByxv9TX8MVV2AcTO8LqZy62MkV/U1HrE+11UiUgIsAbYA6zB+4hVN2LMCmIOp3PwG8EdVXWlPbwBmikgVZhJxv6rWA1cCv7ALYqtIcKLYYvVXETkKDADWAGcDXwcKVLVCRJ4FHlPVuSLyFeCzqnqZiMzB/Iddqqph60Y4qqq/sn3OwdyKXIXxwTyjqh+r5yEi1wHTVfWrIvIa8F1VXW5XAZ8CRgIHgM32Q/qJiPwHMFhVbxaRhzEO6sVW+J9T1ZHWns8AE1X1uO3vdlW9RER+AeSp6s3Whh6qelBEeqrqATGhHC8CN6nqmpY+4NK5pQGMvynjt9OpwhfR8Ny7wu/khUh7XGs86nO6HVx8wS8zNmNWbThed+j+7RDy5P3GMqTw3NfP7XXh6SK+eLO9DcAZA2dNSlmu2s6KFej5qqlzeyW0oJVB53djruFEUPQjfNQ18Jaq7lbVOuBdYIE9HuSEI/wi4HfWEf0MUCQn6tE/o6rxFkouwqykAqCq0SDzL4jICoxQngGMaua9fYiNe52ZSNtspeyFyGKvhBXSG+saD5GcLrlF1+djFoo85Z0jK8Y/uf3evP3xw7ZudcKavSQTLXAP6Xd+nzgg0hOYDvxRRLYC38MIXLRt7PWRmNcRTjjCfcD5Mc7ok2O2ICZcellEBgO3AxfaEJVKPuowb5ZgWfAV2mkyjR5HdO+nlus5LbdML+mMdY07nr/nqYGun9zAxxc5Mk5DpK74hY+HbT02cNYkl1YwRajq1lTOWiEJcc2Q8zuWK4G/qOqpqlqiqqdgfCrJlDhegPEhASAioxO45nliZpoi0gMowohxtYj0Az6dhA1RvkcWzISS5Ud/D28S8/49Jd2xrvEI5JWe58sZnNENBs0RE7b1HE37Qh1ZQrJxrml1fjfiGkxtqlgeJ7mogZuAsXZhah1mYa0lfgb0ELN7YzUwTVVXY9wB6zFukCXNdRCPYFnwfeCOZK/zknEbIisH7idt6QSTId2xrk2R0+3SiUiXlS23zAw2bOvJgbMm7fLaFkfztLig5UgdpXNLBTObvshrW1rCH9aGuXeFt+eGOd1rWwDWD7t60a4Bk6Z4MbZGju2rq/5DA2hC2x7TzEvARbfNm+/+cLMct0MrgwTLggrcgIlwyGq+/q/IkmwRVshcrGs8xNetd07B5/YBXmf3PwZ8zQlr+8CJa4YJlgV3At/w2o7m6HNId01bo+O8tiOWTMa6xsOfc2qpP+8crxOhf+u2efO9LB/jSAInrh4QLAs+Bsz12o6m+PHfw9sl8aiQjJCuvK7JkNN12hTx9fRKYB+8bd78dhlx0llx4uod3wXe8dqIxkxcG1nW7xDne21HYzId69qkHUXXng3+dzM87Apiol4c7QMnrh4RLAseAS4lizJnBUJa9+35kbQnwG4tmY51jYdIbrfcomt9ZO7/7RDw+dvmzc94QhlH23Di6iHBsuA6TAhbVixQzJwfeT0Q4VSv7WgKL2Jd4+Hz9xkc6DKtNdmrWsMNt82bvzlDYzlSiBNXjwmWBZ8GKry2o/9+3T6hSrPOHRCLV7Gu8QjknzPeFxi0KM3D/Oq2efOfTvMYjjThxDU7+Cknkvl6wo8fDr8vSWzp9YJM5HVNhpyCz12A5K9OU/dPA99PU9+ODODENQuw8a9lmBRoGefClZE3eh0lq0Kv4lFwdJdnsa7xEPEF8oq+eBJIqosNLgWuuW3e/IwUTnSkByeuWUKwLHgM+HdMsbaMkdugNV97LpJQ2QqvKTi2q3G9J88RX2G/nG6X7sbkPU4FG4HP3DZvfta4QBytw4lrFhEsCx7A1BvK2ALGzU9F3vQrJ2dqvLaQDbGu8fDnnna2P7c06XwTcXgP+ORt8+Z3mOoVnRknrllGsCy4G/gksDvdYw3aq5vHvKMpqT2fCXIbjvbkRMrIrCKn2yeniK94acstm2QvJmfA9uYaichJIvKIiLwrptTzP0VkWGsGFFOOekArrqsQkdubOb9KRB5p6nwqEJE/ikhCOZW9wolrFhIsC27GzGA/SOc4P/p7uFogN51jpJpsiHVtityi688AX2u2p34AXHzbvPkbm2tkcxk/CSxU1dNtqef/S9PJ5lviBkyVkXhj+VvToYiMBPzAJFujKuWIiF9Vv2YT7WctTlyzlGBZ8G1M7tr30tH/jDcjrxXX4HkS7GTJaTh2sOVW3iCSV5hbeE2YJBKxA9uAibfNm59I1ME0oEFVZ0cPqOpqVX3VjC/fs7Xi1kTrvNk6d1Ui8qCt/7ZARLrYunVjgb/ZmWYXEdkqIr+wFTc+LyJft/2ttrXouiZg4zWYxPALMJtksHYsFJG7bR26KhEZJyJPiMgmEflZTLvrbS28VSLyQFTkReSoiNxl04COt/2Ntec+JSIrrJ0v2mPnicjrIrJSTK2+jFfScOKaxQTLghswpXOandEkS5c6PXL9S5HTUtlnpsiv3Z/VCz2+QL8hgS6TViXYfB1GWBP9/z0TWB7vhIhcDAzFVD0dDYwRkcn29FDg96p6BmbH1xWq+hiwDLjOVumIfq77VfVcVX0EeEJVx6nq2UAVH02U3xRXYUoy/Z2P516uV9WxwGxMqNlM+55uEJFedtZ7FXCBqo7GlNe+zl7bDXhDVc9W1Q8LSIpIH+BB+57OBj5vT60HJqnqOcCPgf+XgO0pxYlrlhMsC27HzGBXparP2x+PrPArJ6Wqv0zSrWZPVsW6xiOQP+4CCQxoqYLBm8Dk2+bN35GiYS+2j5WYXAQjMKIKsEVVV9nnyzlRYy4e82Kenykir9qqrddhasc1iZ1J7lPV7ZginufYck1RnrH/BoG1MTXwNgOnABcCY4C3bN27C4HoJCBM/Fjw84FXVHULfFgxBaAYeFRE3gbubsn2dODEtR0QLAvuBaYCbS45cvou3XjmNs2K6gKtwcu8rsmQW/D585HcpsLqXgAuvG3e/P1JdrsWIz7xEODnMfXihqjqn+y52LwEYU7UmItHrEtjDvAdVS0F7qTlTSbXACNszbt3MeWBrog5H1vnrnENvIB9D3Nj3sNwVa2wbWpbKHjamJ8CL9u6WJ9JwPaU48S1nRAsC1ZjKhj8odWdqOodj4Rrpfk/rqwmG2Nd4yHiz80r/FIvoHFY1cPAjNvmzW9N1MNLQJ6I3HhiHDnLlk56DvhKtLqxiJwsIvHKccfSUm27QmC3iORw4vY8LiLiA74AlNqadyUYn2syZZleBK6M2i0iPUWkpVwXS4HJtogoMTPlYmCnfX5DEjakDCeu7YhgWbAhWBb8BvBtWpEV/4oluqSgjrNSb1nmyNZY13iIv6h/TrdLtmNmZmHg9tvmzb/utnnz61vTny2tfTlwkQ3FWgv8HNijqgswwv26vY1/jJaLgs4BZkcXtOKc/xFm1+ASjA+zOSYBO1U1trbXK8AoEUmoPI5d/f8hsMDW5XseaPZaVf0AuBF4wi52Rd0avwR+LiIr8Wgy4WpotVNK55ZOwfwB9W6pLUDBcT30x3vCIV+C7bOZl6b87ih2htYeaDi24J/h+rd/fdu8+S96bYsjc7iZazslWBZchAmlSShw/fuPhtd0BGGF7I51jcPrOd0u/oYT1s6HE9d2TLAsuA1zO3Yn5rYzLiO367phO5mYMcPSTDbHusYQAX4FTJk5e3qqIgIc7Yh2u7DhMATLgiGgonRu6b+Av8JHK7aKaqT80TDSgX5I82v3H6/L7+G1Gc3xDvDlmbOnL26xpaPD0mH+4Do7wbLgUkzw+J9jj1+7MLK4Sz1ZvQc7WbI41lWB3wFnO2F1uJlrByJYFjwKfLV0bukjwH3Fx7T7Z5dqu44OiEeWxrpuAb4yc/b0hV4b4sgO3My1AxIsCz4PlH7jn5H/yvbqAq0hy2JdazAhS6OcsDpicaFYHZyqESNLgF9j4iM7BPU5BQcWX/CLni23TDsPA993C1aOeDhx7SRUjRg5HbMlsN3kb20Oj2Nd3wJumTl7eioSZDs6KE5cOxlVI0ZeBPwE2ndo1sJJd78T8ecOyfCwbwL/OXP29MoMj+toh7gFrU7GyPVVLwAvVI0YeSFGZCd5bFKryGk4drDOn7E8368Dd86cPf25TA3oaP84ce2kjFxf9SLwYtWIkdOA24FP0Y4WODMQ6xoB/gncM3P2dLe7ypE0Tlw7OSPXV70MvFw1YuQg4CuYhMhZXw22W80ere6eFq/AfkxCk/tnzp7+bjoGcHQOnM/V8RGqRoz0A58Gvg7MwNRDyjp2DJi8dOOwq85PUXchTLq7ucATM2dPr2uhvcPRIm7m6vgII9dXhYH5wPyqESMHAJ/DJBueShYVM0xBrGsIeBn4B0ZQD7TQ3uFICjdzdSRE1YiRhRi/7GeAfwd6eWlPK2Nd92MSTi8Anpo5e3rjRNYOR8pw4upIGus6mICZzV6AqWOU8V1TCcS6HsUken4RU1pl1czZ01v1hReRMKb2k2AykH1HVV9rou1rqjpBREqACar6sD0+FviSqt7UxHVTgdtV9ZIWbJkDzLdFBuOdrwCOquqvWn5njnTh3AKOpLGug1ftg6oRI33ASEx9p+hjBGme3foiDXtiYl33Y4rzRQv0rQQ2zZw9PZKi4Y7biqSIyL9hKgBMiW0gIgFVDalqdKNGCXAtZicXqroMU3HV0Qlw4upoMyPXV0UwxfPWAg9Fj1tXQgkw2D6iz0/ClEqOfXTFzAqjRIDjmIJ5NfbfY8Ae4D1gR+GR7Ruruw/ZCbyb4Vv8IuAgfDjb/Kl9PQIYJiJHVbUAmAWMtJVM52IE/3ZVvUREpgC/sf0pEC2DXSAij3GijPb12sztpYjMAj6L8SEvUNXbG50/Hfg90AfzOX5dVdeLyOcxcc5hoFpVJ+NIKU5cHWlj5PqqI5hb6WBLbatGjBSgC5AH1I5cX3W8xf7bbGFSdLEimY+p6zQ95ty5wJnR8s4xlBNzm2+FOMrtwExVXWKLCtba4+dgykDvwrg0LgDipi8UkV6YnBEjVFVFpHucZn8Avqmqm0TkE8B91vYfA/+mqjubuM7RRpy4OrKCkeurFDOzqvHaliaIdQuMBx4SkTPtuTfjCGtLLAF+LSJ/A55Q1R0iEu1rhx1nFWa231Ru2GqMKP9JROZjojw+xIr2BOBR2zeYH6/o+HNE5B/AE0na7kiAdrMjJ9WIyB0islZE1tjql59oZT9TRWRCzOs5InJl6ixtctwKEdlpbY8+uqd7XAeo6uuYemR97KFjrehjFvA1zGx9iYiMsKdiY2zDNDMBUtUQcB6mUOUlwL8aNfEBh1R1dMxjpL32m5hKq6cAy+0s2JFCOuXM1c48LgHOVdU6EelN62M4p2JWpeOuHCdpl2AiOBJdhLm7tSvCIuJX1SbrbjmaxgqhH7OI1hxHaKK8tYicrqpBICgi4zD+2kNJ2lEAdFXVf4rIEmBz7HlVPSwiW0Tk86r6qP1+naWqq+34bwBviMinMSLb0vtxJEFnnbn2B/apah2Aqu6L1lsXkQtFZKWIBEXkzyKSZ49vtSKMiIwVkYU21OabwC125hhNgjJZRF4Tkc2xs1gR+Z6IvGVny3faYyUiskFEHgLeBiaJSJWIPGhn1guaqCkfFxG5QUR+F/N6ftTXJyJHReQuW999vIjcKiJv28fNMfasF5G/WTseE5Gu9twYEVkkIstF5LlE69F3ELpE7xCAeUBZAj9Oa4CwiKwWkVsanbvZfu5rgAbgf1thUyEw3/axGLg1TpvrgK/a//O1wKX2+H/b7/jbmInB6laM72gOVe10D6AAWAVsxDj4p9jj+ZiV6GH29UPAzfb5VqC3fT4WWGifV2AWLaJ9zwEexfxwjQLesccvxiwuiD03H7NCXIJZGT/ftivBrPyOtq//gVkxbvweKoCd9n2sAl62x28AfhfTbj4w1T5X4Av2+RjMQlM3+3msxSymlNh2F9h2f8YsvuRg/gj72ONXAX/2+v/SPdwjWx+dcuaqqkcx4nIj8AEwT0RuAIYDW1R1o206lxMhMsnwlKpGVHUd0M8eu9g+onGYI4Ch9tw2VV0ac/0WVV1lny/HCF487tYTvrRpCdgVBh63zycCT6rqMft5PMGJ9IPvqWo0EfRfbdvhmPCg5+3s7Ye0gwQvDodXdEqfK4CaW7qFwEIRCQJlGOFrihAn3Cgt1aWKXZSQmH9/rqoPxDa0roXGCyKNFzUSdgs0shM+amutJuZnbRxXqRj716rq+CRscTg6LZ1y5ioiw0VkaMyh0cA2YANQIiLRXT9fBBbZ51sxs12AK2KubXLRohHPAV+xixCIyMki0rdVb6B5tgKjRcQnIqdgVpPj8SpwmYh0FZFumHjJV+25QXbRD8wOo8WYz6ZP9LiI5IjIGWmw3+HoEHRKccX4GOeKyDq7GDAKqFDVWuDLmLjAIMYXOttecyfwGxFZhplNRnkWuLzRgtbHUNUFmG2Qr9u+HyMxUW6O6EJa9FGCiV/cAqwD7sW4IOLZswLjH34TeAP4o6pGZ+4bgJkiUgX0AO5X1XrgSuAXdnFkFR2kHpfDkQ5c4hbHR7ACPV9Vz2yprcPhaJrOOnN1OByOtOJmrg6Hw5EG3MzV4XA40oATV4fD4UgDTlwdDocjDThxdTgcjjTgxNXhcDjSgBNXh8PhSANOXB0OhyMNOHF1OByONODE1eFwONKAE1eHw+FIA05cHQ6HIw04cXU4HI404MTV4XA40oATV4fD4UgDTlwdDocjDThxdTgcjjTw/wH/NjllzZ+yXwAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# 5 Points\n",
    "normalized_database_filename = 'normalized.db'\n",
    "conn = create_connection(normalized_database_filename)\n",
    "sql_statement = ex4(conn)\n",
    "data = pd.read_csv(\"ex4.csv\")\n",
    "cur = conn.cursor()\n",
    "df = pd.read_sql_query(sql_statement, conn)\n",
    "assert df.equals(data) == True\n",
    "\n",
    "df.set_index('Region', inplace=True)\n",
    "df.plot.pie(y='Total', legend=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "38d0d90dd5c0f8c9f7c4e0ea3702a9c6",
     "grade": false,
     "grade_id": "cell-d223667d8732715d",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def ex5(conn):\n",
    "    \n",
    "    # Simply, find the total for all the countries\n",
    "    # Write an SQL statement that SELECTs From the OrderDetail table and joins with the Customer, Product, and Country table.\n",
    "    # Pull out the following columns. \n",
    "    # Country\n",
    "    # Total -- which is calculated from multiplying ProductUnitPrice with QuantityOrdered -- sum first and then round to two decimal places\n",
    "    # ORDER BY Total Descending \n",
    "    # YOUR CODE HERE\n",
    "    sql_statement = ''' select Country, round(sum(Total), 2) as Total from(select Country.Country, Product.ProductUnitPrice*OrderDetail.QuantityOrdered as Total\n",
    "                    from OrderDetail join Customer on Customer.CustomerID=OrderDetail.CustomerID join Product on Product.ProductID=OrderDetail.ProductID \n",
    "                    join Country on Country.CountryID=Customer.CountryID) group by 1 order by Total desc; '''\n",
    "    \n",
    "    df = pd.read_sql_query(sql_statement, conn)\n",
    "    return sql_statement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Country</th>\n",
       "      <th>Total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>USA</td>\n",
       "      <td>65090437.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Germany</td>\n",
       "      <td>55556847.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>France</td>\n",
       "      <td>54388486.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Brazil</td>\n",
       "      <td>47467768.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>UK</td>\n",
       "      <td>34560755.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Mexico</td>\n",
       "      <td>25146864.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Spain</td>\n",
       "      <td>23968999.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Venezuela</td>\n",
       "      <td>21404996.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Canada</td>\n",
       "      <td>15103910.37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>14803866.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Italy</td>\n",
       "      <td>14437243.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Denmark</td>\n",
       "      <td>10611364.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Sweden</td>\n",
       "      <td>10479408.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Portugal</td>\n",
       "      <td>10384378.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Finland</td>\n",
       "      <td>10361234.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Switzerland</td>\n",
       "      <td>10324316.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Austria</td>\n",
       "      <td>9961995.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Belgium</td>\n",
       "      <td>9671730.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Poland</td>\n",
       "      <td>5196640.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Norway</td>\n",
       "      <td>4647933.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Ireland</td>\n",
       "      <td>4434537.62</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Country        Total\n",
       "0           USA  65090437.08\n",
       "1       Germany  55556847.68\n",
       "2        France  54388486.62\n",
       "3        Brazil  47467768.74\n",
       "4            UK  34560755.49\n",
       "5        Mexico  25146864.21\n",
       "6         Spain  23968999.47\n",
       "7     Venezuela  21404996.25\n",
       "8        Canada  15103910.37\n",
       "9     Argentina  14803866.67\n",
       "10        Italy  14437243.02\n",
       "11      Denmark  10611364.69\n",
       "12       Sweden  10479408.14\n",
       "13     Portugal  10384378.66\n",
       "14      Finland  10361234.75\n",
       "15  Switzerland  10324316.28\n",
       "16      Austria   9961995.02\n",
       "17      Belgium   9671730.34\n",
       "18       Poland   5196640.06\n",
       "19       Norway   4647933.35\n",
       "20      Ireland   4434537.62"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Country</th>\n",
       "      <th>Total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>USA</td>\n",
       "      <td>65090437.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Germany</td>\n",
       "      <td>55556847.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>France</td>\n",
       "      <td>54388486.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Brazil</td>\n",
       "      <td>47467768.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>UK</td>\n",
       "      <td>34560755.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Mexico</td>\n",
       "      <td>25146864.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Spain</td>\n",
       "      <td>23968999.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Venezuela</td>\n",
       "      <td>21404996.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Canada</td>\n",
       "      <td>15103910.37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>14803866.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Italy</td>\n",
       "      <td>14437243.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Denmark</td>\n",
       "      <td>10611364.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Sweden</td>\n",
       "      <td>10479408.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Portugal</td>\n",
       "      <td>10384378.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Finland</td>\n",
       "      <td>10361234.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Switzerland</td>\n",
       "      <td>10324316.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Austria</td>\n",
       "      <td>9961995.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Belgium</td>\n",
       "      <td>9671730.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Poland</td>\n",
       "      <td>5196640.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Norway</td>\n",
       "      <td>4647933.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Ireland</td>\n",
       "      <td>4434537.62</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Country        Total\n",
       "0           USA  65090437.08\n",
       "1       Germany  55556847.68\n",
       "2        France  54388486.62\n",
       "3        Brazil  47467768.74\n",
       "4            UK  34560755.49\n",
       "5        Mexico  25146864.21\n",
       "6         Spain  23968999.47\n",
       "7     Venezuela  21404996.25\n",
       "8        Canada  15103910.37\n",
       "9     Argentina  14803866.67\n",
       "10        Italy  14437243.02\n",
       "11      Denmark  10611364.69\n",
       "12       Sweden  10479408.14\n",
       "13     Portugal  10384378.66\n",
       "14      Finland  10361234.75\n",
       "15  Switzerland  10324316.28\n",
       "16      Austria   9961995.02\n",
       "17      Belgium   9671730.34\n",
       "18       Poland   5196640.06\n",
       "19       Norway   4647933.35\n",
       "20      Ireland   4434537.62"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "normalized_database_filename = 'normalized.db'\n",
    "conn = create_connection(normalized_database_filename)\n",
    "sql_statement = ex5(conn)\n",
    "data = pd.read_csv(\"ex5.csv\")\n",
    "cur = conn.cursor()\n",
    "df = pd.read_sql_query(sql_statement, conn)\n",
    "display(df)\n",
    "display(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "e916d4c6119fe608302a5504461c30a5",
     "grade": true,
     "grade_id": "cell-bafad28b2bc9beea",
     "locked": true,
     "points": 5,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<AxesSubplot:ylabel='Total'>"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAARUAAADnCAYAAAAww8JEAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjMuMSwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy/d3fzzAAAACXBIWXMAAAsTAAALEwEAmpwYAABedElEQVR4nO2dd3gc1dXG3zOzTcXq7k3ukrFsueCCu2mhlxAIoZheYkjBQPwFCIKQ4JAQQjGYQBIMCcSEHptQjeXebVm2ZWOM5apm9bpl5nx/3Fl7JW2ZXa20Wml+z7OPdmfv3Lm72j1777nnvIeYGQYGBgbhQor0AAwMDLoWhlExMDAIK4ZRMTAwCCuGUTEwMAgrhlExMDAIK4ZRMTAwCCuGUTEwMAgrhlExMDAIK4ZRMTAwCCuGUTEwMAgrhlExMDAIK4ZRMTAwCCuGUTEwMAgrhlExMDAIK4ZRMTAwCCuGUTEwMAgrhlExMDAIK4ZRMTAwCCuGUTGIGETUm4jeJqLviWg7EW0koqsiPS6DtmEYFYOIQEQE4CMAa5h5KDNPBPBjAAN0nm9qx+EZtAHDqBhEinkAHMy81H2AmY8w84tEJBPRH4loKxHtJqK7AYCI5hDRWiL6BMA+7XEuEX2szXYWE9ENRLSFiPKJaJh23mVEtJmIdhLRV0TUWzueQ0R/J6LV2vk/044/SUS/cI+LiH5HRD/vwPcmqjGMikGkOAvADh/P3Q6gmpnPBnA2gDuJaIj23AQAP2fmkdrjcQDuAZAJ4CYAI5l5MoDXAdyvtVkHYCozjwfwbwAPe1wrA8CFACYDeJyIzAD+DuBmACAiCWIG9c+2vdzugzGFNOgUENESADMAOAAcATCWiK7Rnk4EMEJ7bgszH/Y4dSszF2l9HALwhXY8H8Bc7f4AAMuJqC8ACwDP81cysx2AnYhKAfRm5kIiKiei8QB6A9jJzOVhfsldFmOmYhAp9kLMOgAAzLwAwLkAegIgAPczc7Z2G8LMbmNR36Ifu8d91eOxijM/mi8CeImZswDcDcDm43zF45zXAdwC4FaImYuBTrq0USEihYh2edzSIz0mg9OsAmAjons9jsVqfz8HcK+2FAERjSSiuDZcKxHACe3+fJ3nfAjgBxDLr8/bcO1uR1df/jQyc7a3J7TdB2JmtWOHZAAAzMxEdCWA54joYQBlELOQXwH4D4B0ADu0/1MZgCvbcLkcAP8hokoIYzbEf3OAmR1E9A2AKmZW2nDtbgd15bKnRFTHzPEej9MhfnU2A5gI4GIAiyB+jWIAvMfMj2ttCwEsA3AZADOAHzHzfiKKh5hOTwLAAJ5g5veJ6AIATwCwAjgE4FZmruuI12kQfjQH7Q6I//vBSI8nmujqM5UYItql3T8M4JcQDr/5zLwJAIjoEWauICIZwNdENJaZd2vnnGLmCUT0UwAPArgDwGMQOxNZ2vnJRJQG4FEA5zFzPRH9CsADAJ7soNfZjKxlWSaI1zkIQD8AfefUN5heLD2VASAFQLJ2kyH8CN5uKoBqiGXDCQDHPe6fAFCKnOou+YtERKMBrADwoWFQgqerG5Vmyx9tpnLEbVA0riWiuyDei74ARgNwG5UPtL/bAVyt3T8PYosRAMDMlUR0qXbeejFbhwXAxnC/GG9kLcvqC2Csxy0LYnvV4tnuW4tlM4ApYby0AzmJhRDvzTYAWwHsQE51S0dq1MHM+wAMjfQ4opWublS8cfpDr8U+PAjgbM04vAHvOwOeuwLeIABfMvP1YR5rK7KWZWVBxFWcB7F70lPPebWSFBPmoVgAjNRu7tetIidxP84YmY0QhqZLzmgMvNMdjYonCRBGplqLsrwIwOoA53wJYAGAXwBi+QNgE4AlRDScmb/Tdir6M/O3bR1g1rKsVADnQxiSCyCWM0HTJFFCW8eiAwlixjYaWvAYgFLkJH4G4H8APkNOdVUHjMMggnRro8LMeUS0E8B+AMcArNdx2lMQBmQPxAzmCWb+gIhuAfAOEVm1do8CCMmoZC3LGgDxpbwSwqHc5q1/p/ClRIJeEK/lZgBO5CR+A+B9AB8hp7o0QmMyaEe69O5PNJG1LMsKYURug1jahD2GaPvhow5LC19LBFEhtneXAvgYOdWuCI/HIEwYRiXCZC3LmggRtfkTiB2ZduOLoyeK+ypKn/a8RoicAPAagL8ip7oo0oMxaBuGUYkA2pbvDRBb3OM66rpvnSw+kG13jOqo64WAC8DHAF5GTvWqSA/GIDQMo9KBZC3LioGIdXkQIoakQ3mm9NT2i+obJnb0dUOkAMBiAP9ETrUR9RxFdOncn85C1rIsW9ayrF9CBOC9gAgYFAA4aTLZA7fqNGRCRDTnISfx8kgPxkA/3Xr3p73JWpZlAXAngF8jxK3gcHLSJDsjPYYQGAPgY+QkbgSwCDnVayI9IAP/GDOVdiJrWdZcAHkAXkInMCgAUGwyRfNadxqAXOQk/g85ieMjPRgD3xg+lTCTtSyrF4BnAdwY6bG0JNPuWPfuyeIZkR5HGGCIpdEvjWC6zocxUwkTWcuypKxlWfcCOIBOaFAAoFKWrIFbRQUEIaC0z/C3dD4MoxIGspZljYfIc3kZQFJkR+ObWkmKDdwqqugL4W95GzmJqZEejIHAWP60gaxlWRKEFMJjEDICnRoT87GdhccGRnoc7UQpgAXIqX4v0gPp7hhGJUSylmX1BvA2RKmJ6IC5Pr/wWFtkGaOB9wHci5zqskgPpLtiLH9CIGtZ1jwAuxBNBgUAiOKaiBojPYx25ocAdiAncWqkB9JdMYxKEGjO2Mch5A86Yw5NQMpkuSLSY+gABkBsPy+I9EC6I4ZR0Ym23PkCQkQ5at+3EpNcHekxdBAWAC/93yMPPJO+aGVX2fWKCqL2y9GRZC3LyoBQMjs30mNpK0UmuSHSY+govlIm5L6jnPsQgFXpi1b2ivR4uguGUQlA1rKsqRBlM7vErsmJ6Mr/CZliTt56p/OBmdrDcwBsSV+0MiuSY+ouGEbFD1nLsi4G8DWALhMDUWQyRWP+T1A42HT4fPszIxmS5+d7MIAN6YtWnh+pcXUXDKPig6xlWTdDaHt0qYCxYlmmSI+hPWFG7RWO36q1iEv08nQ8gI/TF608r6PH1Z0wjIoXspZlPQTgDXTBLO4yk9zlXpMbZvCvXHcWFPDgYX6axQD4JH3RyugKB4giDKPSgqxlWU8DeAYiv6TLUSV1mfyfVqxUp655V5k7WUfTGAD/TV+0cm57j6k7YhgVD7KWZT0GUQa1y1IvSV0yovaYmrb5Puf9s4I4JRbAivRFK2e315i6K4ZR0chalvUzRKhMaUdiJ/Lma4hqmth86AeOP4wGKNjZZSyAlemLVgZjjAwCYBgVAF/MyPxxTBM/EelxdARK5Or/tAvMqL7M8Tu5HjE9QuwiDsKwdAWdmU5BtzcqBRmZFw48hbdefUkpSarjrp+ERmSrI6qL9DDCATPUXzp/evAgD0hvY1fxAP6XvmhldttHZdCtjUpBRuYEAO8BMNmcGPXyEqWhbzkfjfS42psyU9fI//lAnbn2I3XGpDB1Fw/gg/RFK7vUTC4SdFujUpCR2QvAfyE+TAAAk4rBz72m2Iaf4AORG1n7UyLLNZEeQ1s5rPbeuNB5b7idrEMAvJO+aGW3/V6Eg2755hVkZMoA/g0vgtQSo9fv3lT6TDyo7urwgXUQJ02mqM7/aWTLt5c4nh7bTt1fAFEv2yBEuqVRAfB7AD5jFAhIfPg9NePcnermDhxTh3HCZHJEegyhojJVXOR4OqYBtvbcGv+/9EUrr27H/rs03c6oFGRkXgng4UDtCLDd9Zk66do1ytr2H1XHUmSSo7IYOjOUnzp/VljIfTsiufON9EUrMzvgOl2ObmVUCjIyh0OE3+uCAPma9TxzwX+V3PYbVcdTYjJFZbTwv5Rz132mTpnQQZfrAeCj9EUrEzroel2GbmNUCjIybRD6pUEHf83ew7Mfe1vJRRcR9C2Xoy//54A6YP2jrts7Ovp1JIC30hetjEojHCm6jVEB8DiAkJ17WUd49rOvKxsklaNy6eBJlSTFRHoMwVDPtoLLHU9FqrD85QDuiNC1o5JuYVS0eJQH29rPwFOY/vISZZfFyVG9e1IvUXzgVp0DlansQscfEu2w2CI4jD8YynH66fJGpSAj0wzg7wiTjEFKHSa9+qLyfXwDV4ajv0jgIIoKPwEzXHc4F548zj0jXYs6GcBzER5D1NDljQqAXwEYF84O4+wYs/QlpSKtmovC2W9HoUaJkt3flIs2rFInhPV/1wZ+YqjG6aNLG5WCjMxMAI+2R98WBcNefEVRB5by4fbov10hMldLUqdW1d+jpq99ynVTZ8sefjl90cpILsOigi5rVAoyMgnA6wDaTZRIZvT/49+UhNFHeF97XaO96Mz1f2o4Zs/Vjif0iC11NMMRwo8UEaUT0Z4Wx3KI6EEimkpEm4loFxEVEFFOi3Z/IaITRBQ139WoGWgI/AhCRb1dkYDUx99WBk0rULe397XCSZFJ7pSZygpTyYX2Z9IcMHdWhbqH0hetHB3G/pYBuIuZswGMAfCu+wnNkFwF4BiAqBGTapNRISJFs7B5RLSDiMLyJSai14lotHa/kIjSgjlfc87+Phxj0QMB8b/4SM26ZIu6oaOu2VY6Y/4PMxzznYtKi5Damas/WgAsDWPsSi8ARQDAzApzs1nvHAB7AbwC4PowXa/daetMpZGZs5l5HID/A/B0ywZEFPSuCzPf0eLNDZa7AfgTPw47BFhu/lqdOv+r6Ii+PWmSO139n5eUKzevU7OioTbPTAA/DlNfzwE4QEQfEtHdROTps7kewDsAPgRwCRGZw3TNdiWcy58EAJUAQERziGgtEX0CYJ927CMi2k5Ee4noLu3Y5dpMZxcRHSCiw9rx1UQUkk5GQUZmDwCPheUVBQkB0iVbefbCDzq/YSkymTpVdPAOdfjaZ13XzgzcstPwmyAkEny918zMTwKYBFFS9ycAPgMAIrIAuBjAR8xcA2AzgAvbNuSOoa2xGzFEtAuADUBfAJ5lDyYAGMN8enfkNmauIKIYAFuJ6H1m/gTAJwBARO8CCMeX8UGIKWXEmHKAZz+1zLXmsZvlGdxJHWwlps5T/6eK43Zf6/jN1EiPI0gyIGYrb+toWw4R6+JJCoDDAMDMhwC8QkSvASgjolQIf2ASgHwS0ruxABoBrAjH4NuTcC1/MgD8AMCbRKfFh7d4GBQA+BkR5QHYBFFCdIT7CSJ6WOtrSVsGU5CR2RvAA23pI1yMPIlZzy9VNptc3OmWGUDnyf9xsVR0gf2Zvi6YomJq3wJdsxVmrgNQRETzAICIUiC+L+uI6BKP78wIAAqAKoilzx3MnM7M6RACUucTUacvbhe2X1Fm3gggDUBP7VC9+zkimgPgPADTNP/LTojZDYjoPIidmnvCMIxfwEPJLdL0qcK0V5Yo+2LsXBvpsbSkRpIi/uFkRtNPHI9UliK5Z+DWnZJR0O9buRnAY9rMfhWAJ7QZyk0QPpVdAN4CcANEGMQPAKx0n8zM9RA1vS8L1+Dbi7D9WhFRBgAZYqrXkkQAlczcoLWbqp0zGMASABcyc2Nbrl+QkRmP8BimsJLYgPGvvqgU3H+PnFYdT53my9NAkc//+ZPr2m1bODPaVewfho4lkLbx0EoYjJl9GaVWWrnMHBXCUW2dqcS4Ha0AlgOYz8yKl3afATARUQGAxRBLIAC4BSJk/COtn0/bMJY7IdagnQ6bE5kvL1Hqe1fw8UiPxY0jwvV/Niqj1yxRrox2gwIA44zw/eZQV5AIKcjIlAAcApAe4aH4RSWUPDJfrjnUl0YEbt3OMCt5hcdIikAAZDn32DnZ/nKWgs7h1wkDXxQuviQqdmY6gk65MxECl6GTGxQAkBi9f/+G0iv7kLo70mMBkVwlSR2eae1k+fj59mcGdSGDAgAXpC9a2VkSHyNOVzEq90V6AHohIPH/3lVHzMlTt0R6LCUmuaojr8eMhmsdv6mvQGJUZEkHiSHkpBH1RqUgI3MYgHMjPY5gICDm3k/VCT9cp66L5DiKTaYOzf95ynXjrp08YlRHXrMDuTZ90Uo50oPoDES9UYHYz+80gVx6IcB03Vp1xt2fKqsjNYaTJrnD8n9WK2Nz/6Zc3O4JnhGkF5oHf3ZbuopRiVrOzeM5v/53ZES1T5pMzo64TiknbbvV+XA0heCHyk8iPYDOQFQblYKMzCwA4UxDjwjZh3n2M39X1ne0qHZH5P84WT5yvv2Z4Qwpqj9rOrkqfdHKzirZ0GFE+z86qmcpnqSXYsZLLys7LM62BQEGQ6ncvvk/zKi72vGEoxrxSe15nU5EIkQSYLcm2o1KuNLPOwVptZi89EXlYFwjd4jUY4UsWdqrb2bwY65b9+Tz0MjH5HQsXeaHLlSi1qgUZGROgUiy6lLE2zF26UtKaWoNF7f3tdoz/+dzddKafyrnR1vmcTi4NH3Ryh6RHkQkiVqjgihIrAoVqwsjXnxFcQ0o48L2vE6jJLVL/s8JTt1yj/OXnU20uqOIAXBFpAcRSaLZqHTp7TuTigF/el2JzzjGBe11DWdrjY82Y2fT4Qvtf8gAKOq2+cNIl/3B00NUGhUtI/nsSI+jvZGAtCf+qQyYfEDd2R79M5DkAsK248SMmssdT6EOsVFRrKwdmR7pAUSSqDQqAGYhjLINnRkCeiz8QB194TZ1Y/g7J6oIU6kOZvBDrrv3H+BBXc7PFQL90xetHBTpQUSKaDUqXXrp0xICrLd9qU654RtlTbj7LpHlsOw0faJOW/OeMrsz1uqJFF05etgv0WpUWonddHUIkK7YxLN+8WF4RbXDUf/nqNpr08+d93VXx6wvDKMSLRRkZCYDyI70OCLFOft59pNvudYQsxqO/k6aTG0Ktmti83cXOZ4e080ds94wjEoUMQHROe6wkXEcs577q7LJpLCjrX2dNJlCdtSqjKqLHU+b6xETcWnKTsi49EUrI64DHAmi8cs5JtID6Az0q8A5Ly9R8m0ObtPypdgkhzTjYYbyM+f9h77nfoPbcv0ujAlAt/QxGUYlikmqx8RXX1SOJtSzN7FxXZSZ5JA0QN5V5qxboU6bGOp1uwndcglkGJUoJ8aB0a8sUWp6VfGJUM6vkOSgs2oPqX03/Mp1V9QUDI8gUyI9gEgQVUalICOTAJwV6XF0NswKhjy/VJHSi/lQsOfWSlJMMO0b2HrgUsfvs4O9TjdlaKQHEAmiyqgAGAygWydr+UJm9P3DP5SUrMNqfjDnNUmkO/pVZSr/gWNxXCOs3dIBGQIDIz2ASBBtRsWYpfiBgORH/60Om5WvbtV7jt78H2a47nX+/OhR7j0g9BF2OxLTF63sdjtj0WZUjBDwABAQu2CFOv7KDep6fSdQggMIuDX9pnL+hs/VyePbPMDuR7ebrfjMnyGiCf5OZOYd4R9OQPpF4JpRBwGm63PVc1JrOPdvP5ADOlRPyXJFP0Xp4+v5/erAdY+7bjUiZkNjIIB2yzTvjPhLynvWz3OMyOTfGEZFJwTQhTt5ds8aJXfxtf4NS4lJrvJlVOrYtu8Kx28ntc8ouwXGTMUNM3e6/Jpt4xfaZdWZa2uqYFtTucnWVG6NaSqPtzZVJlkd1akSK+0mjxitTDjEsxf/w7X21/Plc1SJvMakFJlMdePtrVdAClPZhfY/JNthsbX7QLsu3c4HpUs+gIjGQKjWn/5wMfOb7TUoX9QkDp0AwPevJnMVsavCpNhrTM6GRoujxmGzV7KtqVyOaTxltTWVx9nsFYlWe3WqrDqD2kqNZoYWY+aLryibf3G3PM5polYG4qTJZG95jBnO250PFZ1Az7EdM8ouizFTaQkRPQ5gDoRR+RTARQDWAehwowIgxe+zRElM5iSnZIbTHI/G2F7wmdfPXEusVMqKo9rkaqi3OGodVnulGtNULtmayi0xjadibfbKRKu9KtmkNEW96FDPGkxZ+qKSd/+9cnqDjRI9nztpklvl//xVuXTTajW7O9TqaW8Mo+KFawCMA7CTmW8lot4A/tm+w/JJ+OQPiXowmXq4JBNc5lg0xaTB5+YScyOxWi6pjmqTq7He4qy1W+1Viq2pQrI1lZtiGstjbU3lPWz2qhSzqz7sEo3hokcTxr36onLgZ/fI9soe1Mt9vLhF/Z88dejap10/MQxKeOgZ6QF0NHqMSiMzq0TkIqIEAKWIgPVdcs8qCaKuSsdDFMMkD1CkmAGKKQZ2Wwpqe/jIo2N2AnxKVp1Vsqux3uKsa7LYq122pgqKaTplimkqj7E1lfewNlUmWZx1qQTu0G19qwujlrysHH3wDvnIyVQaDABlsnz6c1DDsfnXOHK6ZXh5O2GO9AA6Gj1GZRsRJQF4DcB2AHUAwi9tGJh4RENcDZEZoL6KbO2ryFY4rElAvA9fHbMKcJmkuqpkpanW7KxvtNqrXTa7cETHNJbbhB+oMsniqE6TWA3LB9SkYtCfX1PKHrtJPnCwP42qlCUrAChMxRfYn+nlhMlweIcPw6i0hJl/qt1dSkSfAUhg5t3tOyyvdGhJ0A6BSAKopypbeqqyBU5LAhri+npvK2otV0rsqpBd9lqTq77B6qhxWpsqIfxAp6wxTeVxtqbKRIujKk1WXX53bCRGz6feVKzPXCPt2j9MimWG/Sbn/5UXI8WIWg4vhlFpCRF9zcznAgCzqEPjeawDaerg63UuiAhAskrmZNVihtMSj8bY3r7bM9cQKxWy4qgVjugah81eqdqaKqSYxlMWW1N5nK2pIuGBD6sHfji7accLKVdVbFDHGH6U8KNboJ2I6phZd1g/EaUDWMHMbc7cJ6I5AB5k5kvb2pe/iFobgFgAaUSUDMAtF5gAoH9bLxwsC5bOU5fcs8qFbqKi32aIEphMCZ6O6JoWTZgVh2Lfta1filpW2lTjvLHnwRNH41VTtUVGnRxjqnQmSTXOHma70yLDyRI5VRku1UQutkJlG1SOASOeAAvOfD4MmtMmES0iMjFzVM3S/X1B7wbwC4goVs+Q/BoAL7XjmPzRBOFbMWgDqlJxxNWwupBdx0ae2/9G5Zuh3yRUnKhsytjRIJ2TPNCGpETLd9YSiyshLzEp+WStLanSWRmTIJ2ypFqO00DXcQxqLEYfVxWSpSbEJLKKeLi4lpxqHRxqAzmUJnKodrKrChyKQg4V5FQJTlUmF5uhqFYoiAFzHBjxABKo6xql+mBP0GYNvwVQCSCDiDIBLIYI7bACWMLMr7Y4Jx3AWwDitEP3MfMGra8cAKcgtIi2A7iRmZmIfgDgLwAaIMJEwoK/iNrnATxPRPcz84vhumAbscMwKiHBrLoU++5trqZNZnDDBBNZUi4ZdO/B7dZjqiW5yLq57GpLwognXQl7bjAX1R4yjYrvTyObror7vqKsKV8+KtdQ4ziQah0VX/H91JQ9JclJRc64+EqWZWe8UzJziaVv7THLoIqjcYMbjmGQUoK+1iokJzchvheT5H9blVmFi2s0o1RPDqWRHKodDtVJdkUhh8pwqiCnKsOpyqSwFQrboHKsh1HqrM7lUHWEJwAYw8yHieguANXMfDYRWQGsJ6IvINJl3JQCOJ+Zm4hoBIB3cCZQdDxEhv9JAOsBTCeibRCbL/MAfAdgeYjjbIWepcSrRPQziAJeALAawKvM7AzXIIKge/tVQkBVqk+6GnMPqs5DGQBPBQCbHF96yYA7K1wShhTIJ2hIXOXBwsRBjTPsMc4vB75rPufQFThoSY4tKHzROjppqvOapGmDFGI+IJ/cvKfmmHysNu3sY0fHWt1XiIuvLExOPlk8Ovmg6+y4Lckmk2MY0elfTNjZ0liCvsXHMLDiKNIbjmOQWoI+liokJzTBJoyOmRLZLCUitvk3RTcKN8Gp1pBTrSOH0giH2kh21UkOxQWHqpBDJW22ZCKXaobCVqgcCxVxAHpQ+/1YhVqtYAszH9buXwBgLBFdoz1OBDACwLce7c0AXiKibAAKgJEt+joOAES0C0A6xLLsMDMf1I7/E8BdIY61GXqMysvagF/WHt8E4BUAd4RjAEHSKpzcoDXMrCqOfdtdjesZXDcRHomYCea0wxf2v8UkkZzxmWX7GpPZPlaWXb3VNGvNf45e6eg96q3aiiMf86hjc+0Hh9xl21eznPZVbeg3OumcjaOTpqWPUQYNdMBVs08+vq3AdNxSD3t2fV3qsPq61GHHj2W5R6DGxVV+n5x8sig5ucgVF1+RNNB0ZOggOjJkupdZtmZ0io5iUMVRpDe6jU41khKaYOvNJKUFfNEy2SDLNrbJvTiUDRdmxW2UxGxJbRKzJcVJdlUlh6rCqRA5WYZLNWt+JSvUZks4b9+noJc/Xs4jAPcz8+eeDbQlj5tfAiiBCFSV0PwH2PN7o6Cd/ZL+HLVuB9HZzDzO46lVRJTXnoPygzFT8QOrtSXOhrUFqvPACIBb1ZrubRu8Z3af6/oRUUol1RUWU9W0nskn81SSeqlJlqH/Uydb9pS9/P2cK2Ol55auqjjr29oj+zLmT3DZd2zZW5Wbta9qQ/xZSdPXZiZNHZKtpE/PVtLRBEflXtOxPfvlk3GNcIwDQQZIqq9PGVpfnzL0+HH3xgRzbGzV4eTkoqLklJPO+PiKRJPJPpQICVY4YgbhyNBBODIUWNvqddnZ0liMfkXaTCc0oxMIIhkWOZktcjLiQpwtudR6zTA1kENtgEO1k1PV9V0hIgbgOfsfSkQ5zJwD4HMA9xLRKmZ2EtFIAC01iRMBHNcCVecDCCRovh9AOhENY+ZDAK7XM049+LNYWyDWdYrHhUFEQyGsXSQwZiotYGZWnd/ucDWuc7JaPQnCmdeK9PgxWyanXTyGiGIB4HNLXikI6WlpR+saIaVClmIh0aEPlTlFvytfH//gHakZrz2/tWxcfv3BvKyfniVbRjbaa/51aE/Vupl7qzY4hXGZMsRGlgETXcNmTnQNQwPsZfmmowXfykUJdjjHgjyDFYkaGpKHNDQkDzlxYvTp0cfGVh9JSi46kZx80hEfX55oNtuHEjWPnLbCETMYhUMHo9CH0bE2FKFv0TEMqtSWV1yC3pZqJCXaxfKq7UZHDyYpDiYpjmOaGSW9usF2ABYi8jbW1yGWLDtIhBaUAbiyRZtXAbxLRDcD+AwBZkia7+UuACuJqAHijQ2LVCuJmCovTxDtZObxRDQPwBsAvteeSgdwKzN/E44BBMOSe1atRzcte9ASVhvKXY3r9iiOfemA6rf2zpikGWtHJ51zDpGQPjgile360rI7GwCmTP3P9iJLn6Rf07PDLOtL1ifXVZ21y3qX9eKBffNQZer97OtKSk3CkOLt4x9IYVCaqzF3rWLfcTaAGILkHJM8fVNG4pQhEsnNwobr0FS823TkwHdycYoDrjEg/bs7MTHVR5OFobHH9yhPMJubhhCFnvdlRyujo5agj60aSQl2WHszSamh9q2Dl4rnZt8fqBER1QH4HYB4Zn6EiB7U7udoy5y/A0iDMCi3MvNRInoDYvY+HsIBez6AmQCqIXZ7fsnMbxLRmxA7QwfhfYfoTQAfMPNH2lj+BeBdZv44lBfsz6gcB/Bn7WEMzkynFIh8oD97PbEdWXLPqvcA/LCjr9uZUByH8lyNa+tZrZgEHTse03pevnpQfOYc92MGq8usq791kZoBsDpj5j/rDtPQksfojyNM+6pyTcfqZ6+0/N+6RMvxIRcN6Jcycy/vve+/6oSG2D5Ht0z6tYkleYCqlB121Py7EXCOBgBhXGZsykicMlQiqVUMUw01nsiTC787JJf0cpGSGcrrtsXUHE9OKjqenHKiqUeP8h6aofGfta6TJljri9Cv+DgGVRxBeuNxDORS9LGGyeg8Vjw3+6lAjTSj0g/Abgi/yJ04Y1T+C+A9Zl5GRLcBuJyZr9SMShqAK5hZIaKlAP4L4AiAfwDYxcx3EtFBiFLBDED13CFi5klENBvCAF1JRIkAdgEYEWp8jL/ljwzhEW/5C2NC5BTtDwdu0vVgtana1bQhT7Hn9weUcYHPAAiknNvvxg2p1n5zPI/vkgs3ukidDgBx8RXfE2G4CrkIANQ0axKO1eNJ500py6Wn+l9U35D7vzFxs7MKefWc/OI50zY/XrRpyuOHIPccZk1a4HLWr8xVnQenM1RzfuWamXsr1znGJM9cMypx8jBP45LAMf1nujL7z3RlopLqj+wyFRYWSqX9FFJH6H0PmhoTBhQ1JgwoKhp1+pjNVnsiKbnoWEryiab4HuXxFktjOhGCXurYYI8bgsPDhuDwsJnIbX1tFkbnGAZXHEF64wkM5BL0sdYgMVEzOv6M20m942DmGm3W8DM03zWaBuBq7f5bAJ7xeO4/zOx2R6yF2KU9ArGZchcR9QdQycz1msFotUPEzLlE9DIR9YT40X6/LQF3/oxKETM/GWrH3kKIiSgHYitrjPbce0SUAuBrAC8w8z8CdNutjIriPLLX1ZhbxcqpCTizpR8QmUyNF/W/Y3ecObFZ2L0TSsMO0/fD3I/TUo8VARiuQJQ+VZOt6QzwZh49uo5t+54qK5/6ZVzs0ZcvleeMPO7a2K+ycto5Gx+p2DjlyX2KyTbaEn/ZbMV5dK+z7oM4QE1XoVp2V+bO2lO51m1chkskNZMATea4wXOdZw0GzkI51R7aaTp8/Kh0apBKHLSoeVNTj/7FRT36Fxed2T21WuuKkpOLjiannGjs0eNUnGZo2iQ/4Gl0vD3fyLY6baZT2dLoyHAF+5n9C0SwaaDvghtP38kaAAsADALwCICrIKRL3I4ofztEbwK4EcCPAdwa5Jib4c+otHuEo2Y5PwfwVx0GBegGRoXZXutq3LxTse/qDbiCTu6zSLbKSwbcfdwi21rJF6wx79vKhNN6tSkpYgNBgSzWwGYpEYSjYAx6Xbm4/BemD0Y/dqqi+PGeqYMevl0e97e/KAeszvpR0zf+2rxxyhM7nZYe42XzoLOkpAUNjtr31rJSNBMAPI1LVvKsNSMTzx4hkdQqUzKVeww7zzl2GACUUvWBXabCouNS+VCVeFCwr9uN3R7ft7h4RN/i4jOTIKu1vjgpqehocsqJhoQep2It1sbBROwncSo4YtAUPxTfDx962u3YjGOBfLVEpGh/90CIZH8A4HYIPwoAbID4sr8FMQPxqj3GzMc0R6+Fmb8nonUAHgRwn9ak1Q4RET3IzH+C8JtuAVDMzPv0vXLv+DMq7Z0wGA/gfwDeZuZXdJ7TZY2K6jp5wNmwupSV4vEIYlbiSZwp6fhFA253yGTKavlcHZqKD0ulzbaZY+Oq+gGACum0Y41j5BPUoAx61XXZxJ/LH1RfXVc/eUly4tZSs+nsB2+X455/VSk3KfbU6RsfHb1pym82N9lSpxCZY60J189U7AXbnA2fDQTEF1aFasmrXD0rv3KNIytl9pqRCZO8GhcA6MWJoy5wjhsFAEVUuW+XqbDspFQxgqntYud2e1yfkpLhfUpKhp8+ZrE0lCQlFx1NST5R3yPhVKzV2jCQiH2kiIeMC2IpEohGAGDmMZqTtApotoy7H8A/iOghiO9s6y2wM2zGGf/nWgBP40wI/ssA3vfYITq9hc3MJURUAOAjHeP1i78w/Yq2dh6APwN4nZmfC+Kc7yH+UV0iqZDZ2eBq2rpDadqRDDjOAjAq4Ek+SLH0/fbcfjcmSCR5FW/5wpL3HQgz3I9NJns1kToUAFRI6ukxJVkdaGhAI6yxm9TM3GlywezXikt7XdG/r70khQb8+Wpp58IP1ESJXdZpmx6ftGXS/62rj+8/AwBka+YkyTy4wlH7ziZWq6e6+1ShWvIqvpmVX7HGPjZl1poRfowLAPTl5NF9nclgMB+XKnbnmQorS6gqkwm9fJ0TLA5HbO/SkmG9S0vOrGjMloay5KTiI8kpJ+oSEspiNEPTFqN2+Nx5h3RFnntkJ68FMBZCMPvvRLQbIjfnLmbeTUS34Ez4/fsAHiWiRwCUA7iBmW8iohwi+jtE2dVCADdAuBcOEtFyAPMBTNfOBwBooQbu8P424XP3p80dEw0GsNKLT6UWQBbETCULwExmLtXb75J7Vu0DENIOQmdBdZUecjZ+c4JdJ8YhDGp2/WNH7Jze66qh2nKyFSVUtf+/lu0jPeNGevY8vC0jc90kANiJCXl/okfGAYBU1LDdsrtyIgBk0NHvP7MuGgoA9/VOy82NjZ0NALd+oeRetJ1nAwADvHPcz9dUJY9sVgbE1bh5vatpfRZEVnszJMj2sSmzN41ImDhKIslnvSFPGKwekcry8kxHasuo5iwQ2nMb+DRmc+OpJGFoahMSymxWa/0ASWK9Cvkfnzvv0JWBGrklD4jIBPFF/wxCE/oUMz+hhXX8mZmz3UaFme/T1AOqtOTAOwBkMvNC7Xt2AYC5EJsqBwD0gTBWb0AUjjdB+G6WQuz2/A3Ac8z8F52vzSft+Ytfjtaasik4s4T5N8Te+qdENJeZa3X2uxdRaFSYXXbFvnO7q2lLPNg+FoBXp1+wjEyYtCE7Zd4kIvK5vfylZXdj80A0IDXt6OmUfNXtUwGgplhPx7zs50FDy7nHrlSqzf5jafnZ0wbHnFSI+v3jAnl25jHXuvRSzCCAJuQ9P3tP5q25pb0nnTYsppgp0yVLxglH7b++Bzdle15bhWLdVbFq9u6KXPu4lDm5IxImZBBJfn0cBJLS1V7j0x29oEJ1HZZKt+82HWksp7osUPvJjDqdMWllZUPSysrO+JFNpqaKpOTiwpTkEzUJiWVWq7VugCSxN4nVPTovE6Pl5ABipvI3iGXMDwGAmVcRUaom5+rJAADLiagvRHiBp3tgJTPbAdiJqBRAb4gYlg+ZuQEAiOgTrf+vIOqUh4V2MyrMXEdERUQ0T3tTUgD8AMDzEBYUzPwcEfUB8AERXcLMejI690B4tKMCVSkvdDWsPqK6jmQhzIF7E1PPzx3WY/wsLcrSK/vlE1uayDm55fHExNLTYQHu3R8AgFVOY6CYxC8bnndd3fikeRlimGMfrKjM+0NqSj8A+PV8+ezXn1f2xjpEfesxBf+Y/a2zNvd4/zmzNEEpSHJif2vivf1cDV/lKo78qRBp+6dRoVh3Vnw9O69ydVN28tw1wxPGjwpkXABAgmQapvaZOMzRBwpUx3dy8dZ8+aijiurHgto/3MHlsqWcKktPOVWWfvqYydRUmZRUUpiccqI6MaHUarXV95MkdafOLhuZOdvzgJ9/qScvQsxgPvGQOHDTofk+nrS35uvNAB7TrPAqAE+4w/3dMPOvABwH8BYR6RnP3rCPMswwK05X086NTVWv7HTULBusuo7MRqDyIkFeYnafa3OHJ0yY7c+gqFCdG0wHvGynMpvNTae9lorHTAUAYJOPuu/+SznvbIWpBABurKmblqwoOwHAZSLrwjvkFJVweuk68rv3Zg/7/uMN8IhxICIyx50/29LjhqOA6YDXcbJi21Hx1az3jzyXeLBmxxpmVfdyWIZkGaX0O/sax9Tpt9jnmM9xjtqUoMZsBKNBbx/hwOWyJZ86NXj8wW/PmbNt25XT1q+7YfDaNTe1pTTwWghfiFtf5RQzt9TZSsSZHKD5OvpcA+BKIoohoh4ALmvD+HzSrtZL25pqVemQmW9p8TiYffFIiG7rQlWqjrsacw+pzu8zAZ7WHtcgSM4L+9+6JdGSFrBG8ibTwY0qcaudJC3o7fTyq6VRURMtjXJTo/s505fqpIIfyFt7A8CrxaUJ1/br4wKRqTyR+v7+Win/keVqklvPZPCxL6dbnLVbCkbdOBZ0pnCZZOo9wpq0wOGs+yRXdR2eCS8/aAq7bDvKv5yVV/FN47iUubnDe2RnEkm6nbMmyLbRyoCpo5UBcMJVv18+uWGv6ZipDk3jQM1nSR1AWU5OTlt2K3PQ3FHrzWjkAPgPEVVC/Gj7jfVh5h2aozYPQn9laxvG55N2c9S2J0vuWbUXwpEVcZhVRXHs3e5q3EiazEC7zf5MZKm9ZOBdB21y3IRAbZvgrPqndQ3DS87M4MG71g4anH86MC4Xc7f8le47vUSSj9dvMe+tOv24P8qK1ll/3otIbFXe0adX7uYY22mj9uNcZe3VG7hZoF1Zatau/DF3D0VrPwAU5+HdzrqPkwHVb6kXmUyN2SnztgzrMW40BRJ68oMdzuoC+Xj+PtMJWwPs40AdIkb9aU5OziUdcJ1OR+cveeGdLyM9AFZrix11K1bbq54vcTV8ORlcdzba8f20yfGlVwxacEKPQQGAr835ed4MCgAkpzTPmldbDFtNtTb7sp9Az77HOW2b+/FzJWXjJY8du3/Plmd+26957ETP8vzsCTv/fAKsnmp5fdk8ZKw16d4kknv5i7eAwq6Y7eVfzH7/yHNx39XsymVWy/y194UV5sRsZciMn9hnTLrRPrMu25W+1sbmHeB2zbZf0459d2qi1ah8EYmLMjMrjv3b7NWvb7ZXv5amOr+dA7QpjkEXCea0w5cNvMdukiwZetpXUv2RIqnSp1M4Tgt6c6N6OmoBcIypL4ss19P8wXX96aVyD+aEn1ZWH/R8/vEb5am1NjTTDkmq+T5z8rana8FKq/wXImsPa8KNM02x529Gi2u1RGFX7Pbyz2e/f+S5uENtMC4AYIMleZJr2Mwb7bMm/MQ+oyLLNWiNlU15YKiBzw6KgFn8RKQQ0S4iyiOiHUQU0JGvJR4GavM6EUVsJh+tRmU1Qtf+DBpW68uc9Z/l2queP+as/3QSqzXuff52p5dt0N4f9L8tQSJZd1XIL8y7in1N8T2D3ty0ctQCgEVqFgm6Qp06wcHy6WN3Vdec00NR80/3IZP5gTvlfgo1T6CLrz85ZNrmJ5hU7zkwJmvWFGvi3QzqsSXQ61LYFbvNbVxq83KZ2a8xCkQsrD2nuEbMusk+e9yP7dNLM10D1phZ1rsN7I9qCIHpQDQyc7YmgvZ/ENGvbYaZ72hrqH1biEqjsmDpvAaIGJd2RXF8t8te/Y+N9upXExXHvtmAGnJOSiikx4/ZOqfPj4cQke5Ar6PSqbxaqcln2dLk5JMHqYW2iTejoiaaW8QNEb2vzCo8/Qigl0tKzaLKoqA6nno+cYNczS10WWOayvtP3/hoD0mx7/c2JpLietqS7pws26asg46SFgq7Yred+mz2+0eei/k+DMYFAOJh6zPdNWrWfPucMdfapx0f6eqba2K5IMTuvs7JyQl2aZUAoZ4PACCih4hoKxHtJqInWjYmIknLLN5PRF8S0aekadgS0WoimqTdr/M45xpNLgFE9AYRvUJEm4joeyKaQ0R/J6ICd5tQiUqjotEuSyBWG6ucDV/nNlU+/72z/pNsViunIQJK7WOSZqydnHbxBC18WhcM5lXmPX7Hmpp2rJUimFejkmprtVvyjOu6LOYzma3ZdkfGWLujmXHfP5Ay354jtdpKtThr02Zs+HU/k7Pep7yiOWb6DEvCLRUga76vNs3Gzc64raeNy+5cZi7Xc14gEjh2wCzX6Nm32Odk/tA+pXCY0jtXZulg4DNP85nOdjHa8mc/hLrbbwGAiC6ACJmfDKGDMpGIWu7iXQ0hmDYaQjc6lN3GZO28XwL4BMBzEKr7WZo8QkgYRkVDcRbusVe/ud5e/YpNsefNBpShgc9qH6b2vGz1WcnTZ5Km1KaXPLlwQyARpMSE0riWxxQvcqZqqrWV2FIlElIO8MBtnseWlJSNoRZ5Yh9Pk6bvTqdWwiQmpSlh+sZHRlqbKn1uZUpyyiBr4r2jJcvo1Wiu2eoTYVz+N/uDI89ZD9fm54Yzby2Z49PnOsfMvtU+d8SV9smH0pWeqyUmf1vFDJEoqwf38icDIjD0TS3u6ALtthMilD4Dwsh4MgNCS0Vl5mLo8OF44b8stn/zAZQwcz6LmedeCIMVEtFsVHZCSOuFDLO9xtmQu6ap8oVvnXUfjGH11HQAfmsQtycEUs7re9OawfGj5wR7rhNKw3bT9wEMIbPZ0thKGMnbTIXjTAPZS4r9b103JXk+TlLV5Nuqa1oFJP7+OmlGVVxrv4KsOmOmbf7N+Nj6og2+RkkkyZa4H8yx9PjxIUDWq/EKFzvjt5z6dPYHR56zhNu4AECakGqYc5t93pDL7JMODFRSVxPTsRbNtuTk5BwPtm9m3giRmdwTQnbkac3gZDPzcGb+W4jD9vzftvxsu6NuVTSPwFXRBp9h1BqVBUvnMULcWlZdJwrsNf9aa69aIiv27bMA18jAZ7UvMpkaLxlw97ZUW7+QZA/Wmgu2MsFv6n5cXOX3LUWlgdZbygBEnLhZavWLvF4dM6aBrc0iY39WWT09VlWb+R9UieQH7pCHuiQcRQskVk1Ttj41LbH6kN9tV8nUL8OatKC/ZBqYiyAE7s8Yl7+YC2v35DJzZeCzgqM3J4660Jk953b7vIEXO8bv7ack5xKjCMB7ofRHRBkQkgXlEBpDtxFRvPZcfyJqGQS4HsAPNd9Kb/gQPAdQQkSZWrT6VaGMLVii1qho/EdvQ2Zng7Nx/bqmyhf3OWqXZ7JSMhNnBIAjikWyVV4+cMF3ceZEnw5Wf9SjqeR7qWRSoHZpaUe9Sht66qk0O97D5FUM6B/Khc3C6CVAer6kTEGLSMq6WEp+7GbZzl6U3QmgiTv/PCutbFdr/UbPdmSyWXr8aLY57rJdAOmWZgQAFzt6bD61cvYHR/5iKqzbu5qZq4I5Xy/91JSzLnZOmH2bfV7vS+0Tg6n05/ap7IKoEDifmRVm/gLA2wA2ElE+hKFqmdP0PkR6yz4A/4RYJnn7fy0CsAJC6KkomNcVKlEZUetmyT2rTBC5Dz5DuVVXyUFn4zdF7DoZFpmBcOMhrBSyD+dDy5a15VLtzEDtssevXNejR8WMlsffwY1rVtBVrWZI8ve1680Ha6a3PB6Lpvq91tsUouayBtf067PugNXSqv8Ltqubbv9CneKrXvKBEdflnug3c1agLDpWm6odtcv3sloeUmKmmSw1E9Mu3DEoLjObiJJC6SMA6wYsnhnw/xAuiCheS9xNhVBtm675VyJKVM9UFiyd54Kw0s1gdjW5Gresb6paku+o/dcIdp2chU5oUFIsfb+9eMCdprYYlFKqPlBOta2++N6Ija32ql3izVELAGqa1Wv7BtjitvKoVhm4S0tKR4G51a/lFxOlqVtHtnbcuhl1cPnsIYUr1+OMgLNXSLIlWhPnn2OKmbMRHtuvenGyI2FT2X/nfHjkL9KRun2r2ctY28jbYe4vECu0Wc5aAL/tDAYFiHKjouHW8YSqnDrsqH0v1171QqOrad10sL2VrGJnoX/siJ3n9bupt16RIl98Ycmrb6mV4g1ZdlRLkuJVw8Vr8BsA7mFObxlz4ibHOb+VUFGaovb8cW2d1y3jP10tzT6VAJ8BbkOO/G/GqG/f2QahAeIXk23CNGvinXZQ3LZAbb1x2rgcfR5H6wrCZVwaEQbVtGBg5jmaI3c0M7/Rkdf2R9QblQVL5+112fPebqp6ZZej5s0hquvobLQWh+pUjEyYtGF6r6vO8qXUppcD8smtTeTUlQuUnHLyu5ZBb25UXxUyiWSYyKua8z5OH1bJ8a0MyK/KK8+xqmrrmA4iWni7nOGUvatDA0D/ovVTxux9bR+YA4eiSz362JLuniRbJ65BiEXQnao9cWPZJ+EyLssHLJ5Z1YbzuwxRb1QAwNXw9RfgxuxIj0MPE1LPz81OmTfNn1KbHlSorvWm/bpr3KSlHfWprOdr+QMAHG/2GVD2kuvKVpolJsD0TFm515KbjTZK+NWtMnnbqnbT61Te+PF5zx+Bzu1gc+zsWZaEm4oBc8hh6Z7G5Vj9/lCNiy7xdiK6kohY2+0JGu18n3k9RHSPJmwdMbqEUYGQpmxzqHY7w7N6X5s7IoCwkl42m77bEEytnISEUp+KaP6Mippi9fkZWaZcMElhahUrNK+hMTvd4fSqe3O8Jw1Zcqn0LcN3Al9y1cGzzt6+uBKs6tqtkOSeQ6xJC0ZK5hGrIYTRQ8Kp2hM3lH6sGZcDq72IIvliy4DFMwPmLmlcD6FuH2pB9CvhQ/aDiEzMvJSZ3wyx77DQJYzKwuUr7BBhzp0SguT8Qf/bN/SNHRJQWEkPdjir98rHxgRu6YbZYmkc7utZX1vKAKCkWX3mHblgMq9Sx3tV4vtrcWk6mL3OWNZkSWevPYv8xqj0qDs+bOqWJxRSXXpKXIBIMlniL5tjjv/RAaB1fE0wCOPy0ZyPjr6gHtdnXJ7XN0aKh4iEvR2ijg+0nJsVHm1e0sStQUSLiWiflv/zJy2L+XIAf9S2oodpeT5/IaJtAH5OQkn/Qe38O7X8oTwiej+YlI+20CWMisZLaB4V2CkwkaX2soE/3Z1oSdO1Q6OHr835uxBEHWFfQW9uvAa/aXCiZRj7CZf/reum4exFNqCvovS9oq7epyP1pcvlOUXJ/lX8YhtPDThn02OxkmL3KkPpDdk88Cxr0oLeJPdts56JQ21KWn/auHy72oc4+3Hoj5e6AsBnzPwtgHIimuirobZNfBWAs5h5LICnmHkDRI7OQ5qD1h1tbGHmScz8bItuPmDms7Us6AIIY9budBmjsnD5ihMA/hrpcXhik+PKrhi04ESMKc7nhydYqqj+yEmpMqjksdS0Y36DxvwtfyCRBbJ3Zy0AHOXeA4qQ4tV4/OZUxTQzs89Zw8O3yWPtJnzrb2xWR03P6Rsf6WNyNuz2184TrbjZLHPsRdsAoa/bFoRx+XDOR0dfcHkxLn8csHimrhwliCXPv7X7/4b/JVA1RFnSvxHR1YBfzV1fAXdjiGitFkB3A0SyYLvTZYyKxtMIcScg3AhhpXub9Aor6eVzc14RKLis6ZQU/6koPnd/NDjW5DfH6o/O67x+jiyA5amycp+OXruF4h66XY5RAb9OWbOrMXH6xkeGW+xVQW0hy9bMSdbEu80kJYZF19ihNiW7jcuJ+oOrmdVDAF7Tc65WTWIegNeJqBDAQwCuhVC693z/bACgFUifDBFNeyn8Zz57XWZC1Pi5j5mzADyBDspr61JGZeHyFUXQ6YVvT0IRVtLDMenU7lqpcWrgls2Jja32mxPkd6YCQE2x+lVF+0idPsHJcsvEOgDAxfUNk/o5XT6dmMUpNPC5q6RChn9pR1l1xJ6z6bFxMQ0lPhMRvUFSbIo18fZpJtuM9fCz6xQMDrUpeV3pB3PeK/zziwMWz9T7I3YNgLeYeTAzpzPzQIg6PRKA0URk1aJ8zwVO+18SmflTCGmCcVo/tWgdsu+LHgCKiMgMTZm/I+hSRkXjD/BtududUISV9KBppQSdOSrLjhpJ8i/joAT4GKhpVr/+G4YkfahM95lN/Nfi0r5gbvL1/OYMacLnE2idr+fdSKyap2757dSEmsNB+0tMMZOnWxJurwPF6K3FE4gTKpRXg2h/PYAPWxx7H8Jh+y5EPat3IbLvAWEQVmhq+usAPKAd/zeAh4hoJxEFKkj3GERRsvUAvApktQdRnfvji2evu/QPAB7u6OuOSZqxdnTSOecEq4Oihzy5cMNW86Ggc17S0gq3Z45e69ensxiP5eZTtu+dKUVtsH5VZCM/P0KpqD61zXpvAvlYmj3QKy33y7hYv7tff3zdtW5wGVrlDnkjL+ue1eWpWXP0tPWEmdnV8PUaxbG7VXGzILl34fIVS9twfpelK85UAOAZiGlihxGqsJIeXFAat5kOpYdyrr+gNzf+dn8AALIUCwl+t2nLkZj2HffzKb70+7LyyTKzX+fOr2+RJzVaoCuIbVz+0jl9i9av1tPWE1Hc7LzZlh43HPNV3EwH+9CJQxgiTZc0KguXryiHJs3X3rRFWEkva80FW5gQkmp/QmJpfKA2AY0KAI4xBdxFecp1Y6saP25szDG/Lq884et5AHCayLbwDjlZJX3iW5kH3p6TXvi/tZ46uXqRTL2HW5MWDJHMQ1bDTyCeD365cPmKkIPsujpd0qho/AXQ96sXKm0VVtJDPeylh6SSELekmS1elN5aoseoqMmWgNumuWp2ViNbfGq5XltbNyXNpfjdwTmVSH2f/pFUxDqrJQwtXDFz5Hfvboa+OtzNIJItlvir5pjjr9oDSF4dzV74ZOHyFREpERMtdFmjsnD5CieAn7ZX/20VVtLLl5a8AyAEnG14Iy6u8rC/oDc3KuSAaQNqmk1X8uObyvl+Q+tfKy5NDWQA8oZJYz+eSpv1XA8ABpxYM+2sff/I9xXBGwiP4maBnMUNAH6up08ieoSI9mrRsLuIKODnhIieJKLztPu/CFcErGeUbRj6esOt2u+LLmtUAGDh8hW5AJaFu984U+KJywcuqLDItnaVViil6m9P6dRK8UZqqv+gNzcKpMBGJcWqK8/oBdfV45l9l9kY7nQOmdHYFDBu5O258syD/fRX+etdtn1idt6LhxGiuptW3GyGKfb8LfCtffzUwuUrCgP3RdMgYksmaNGw5wEIOBNi5t8w81faw18AaLNRIaIOqU/lSZuMipZt+U+PxyYiKvPMZQiyv/bIsHwAohh1WBDCSnfJsmQKtJ3XZr6w5NXp0UrxRUrqCV1be3qWPzBLiUyBvxj1iOmxk4f73bb9U+mpiRJzwGTB39woT6uzQXckbUrVgTGTdjxTBlZD/n+brFmTrYl3A1Kr4mZ5AP6ks5u+AE6xpg2j1SXqT0QfAAARXUFEjURkISIbkYhYds8CiOhnAPoB+IaIviGiy92yk0R0gEio+RPRRCLKJaLtRPQ5EfXVjjfLB/IcmK98IO3aLxDRBhJ1gNw1hEjLRzpARF/Bj8qim7bOVOohQoFjtMfnQ8g7hkR7ZFguXL6iAsDPwtFXv9jhu8IhrKSHg1KRbq0UX8TGVukap6pjpgIAbJN1/W9znPP9XjeOOf6BiqqASX9a1cO+CunXVk2oPTpiypanGklV9PpIWkFSXE9bYrPiZk4A87UltR6+ADCQiL4lUfBrNkT8Sbb2/EyIuJSzAUyBiCU5DTO/AOAkgLnMPJeZP3Er60MzblpA24sArmHmiRBiZb/z6CaUfKC+EAmPlwJYrB27CsAoiMzomwEEDGsIx/LnUwDu6vbXw0P9iojiSFQ926IF61yhHX+eiH6j3b+QiNaQUAX3zLAcTkRf0Zk6s8M0q/lHItpDRPlEdJ2eAS5cvmI5ghDJ9saIhIkbZ/S6OpPaKKykBxWqa515v+6EQW9oQW+6ZlO6jUqSRVfC5m4eNqKaY/3OMObX1J6TqCi7AvVVFU89n/yJXMmAz+C5lsQ1lgyetvk3ZklxBFMArBXu4mYkpz28cPkKn0XQWsJCZGoigLsgllLLAdwI4BARZUKE3/8ZwCwIA+O3UL0bInoYolbQEogv+hgAX5KQlHwUgKcaXyj5QB9pdYT2AeitHZsF4B1NkPskgFWBxhkOo/JvAD8mIhuAsWhudR8BsIqZJwOYC5GyHQdRN/Y6IpoL4AUAt3LrbcF/AViiWdRzIJTAr4aw9uMg1ql/dE/5dHAHAN01ZDyZkHpe7viUc6cSUVuCpXSzxfTdBoXUNi2vkpNPfkc6l06s82OgpNl0Vx942XVFwPiYpcVlcYF0aQGgYBCNfme2pKc28Wls9qo+0zc+mia7Gr1KM+hFklOOWRNufjHY87Qv4WpmfhzAfQB+CGANgIsgZj5fQcwKZkCHUdEcuD8CcI/7EIC9HrWBspj5Ao9TQskH8vzRCFnzp81GhZl3Q1Qzux5i1uLJBQAWaZZ0NcQLGMTMDQDuhKjb85JHCjcAgIh6AOjPzB9q12jSzpmBM1azBEAuxBQyIAuXr6iBSOAKRh5BE1aaGBZhJT3Y4azeIx9rczZpatoxvQJDumcqaqp1sN4+/6H8YJLK5LcM6RiHY8TEJruumtgfnSNNzx/sWzzbG2ZXffKMDb9ONztqWpVh1UkNgBsXLJ0XVF1kIhpFRJ5b+dkAjkAYj18A2MjMZQBSIWYc3orCn87xIaLBAJYA+BEzu3ONDgDoqTmFQURmItLzuQk2H2gNxARA1n7A5wY6IVy7P59AOLFaCv8SgB96WNNBzOwuOpUFUTgppKCuUFi4fMUOAAv1tA23sJJevjHv2QlCm/OGEhNLdG9D6zUqsMo9GdCl2O6A2ZqrjvX2ZWnGC6Vl40h8wQLyux9LM6pjEZSBkFVH3PSNj46JaSzbFMx5GncuWDqvMITz4gEsI01gCcIfkQMxi+8NnN7V2g0gn73nyvwVwGdE9A2AWyAM0Eeas/ZTFtvy1wD4AxHlAdgFHf4OBJ8P9CGAgxAxX28C/jVwgDbm/hBRHTPHE9EAAFcz8wtENAfAg8x8KRH9HqKa/f3MzEQ0npl3apb3S4iqap8CuJuZNxNRDoA6Zv4TEW0CsJiZP9KWHTJEvdm7AVwMIAXANgBTgi1N8Ox1l74LMZX0iokstRcPuOvbcOqg6KGaGo79x7KxF6hNOSkAmGfM/Gc1EZL0tP4Flmwqoz66sp+tucVbqUnRNTtMp6Jj31gW9g+0DHs5KXHdK8mJunJ+ejRwxasvKvUmFUFlgDNI2TrxVxvqegzUW5fn2QVL54UltqO7EZaZCjMf1zzWLfktADOA3US0F8BvtWXE3yAMz0kI7/Prmk/Gk5sA/Eyz9BsA9IGwmrshPOCrADwcYq0Tn/6V9hBW0svn5l3H225QgLi4qsN6DQoAsN6ZCgA10aw7wKyQ+w4sQXLAmcW9VdXT41RVl++jNpZSHrtZbvJW9dAfBJYnb188M7miQM8S6hsAvwqmf4MzdMksZT08e92lYyFSyk9rUySYUwsv7H+rHG4dFD0cl8rzP7PsCksw3aBBeesGp+/W9csPAPfj1S0VlDZZT1v5WP0W874qXW0B4Efy6i1/NP81YPttNuu+W/v0yoCo+RuQC7epG2//Ug1KAc/Nvoybc4v7TPG1rP0ewOQFS+f59QcZ+KZLR9T6Y+HyFbshHLcuwC2sdHuPSBgUBvPX5vyw/S9SUvQFvZ25vr4vMgCoqdZWRcT88Z4ya5KLpYDxLZOa7KPHOBy6nLYA8PkkadrWEbQ6mLG4Gb3/zdmDjn65xksiYi2AywMZFCJSPILRdhFROhEFFI/SgtIC1rzWAxEVEpHuEi0dSbc1KgCwcPmKzwD8tL2ElfSSLx/d6CQlbPqhsXH6gt7c6N1SBgCONfVj4WDX27f0X3Xad3ravlxcdhYx6y5n+qcfSrPKe8Cn3II/hn//0azhhz7YBGZ3QJsTwDULls7Tswxr9Nh8yGbmQmYOqb5zV6RbGxUAWLh8xWuT0y7+lDqofEFLXFCatpoO6d6qDUQwQW9u1CBmKgAAi1QYTPOnnT8Zzexbkd9Nsqqm3FxTm6+3XyaSHrhDHuWU/Wu9+GLQ8VXnjN6/LE9LRLxlwdJ5IWcfE1Gd9neONiN5j4j2E9G/vIUjENErRLSNRNLhEx7HC4noCS3gM5+0omNElEpEX2jtX0cb4kjam25vVABg4B9m5SBC2rbrzPs3M3H/cPWXnFykO+jNDSO4GBw1wRywLKknpUjueZj76ppRPFBRNcOmqrrFkxptlLDoFhksYkqCpk/J1kkTdz77ywVL5wVTXD3GY+nTUiISAMZDxKOMBjAUgLek0EeYeRJEwOhsIhrr8dwpZp4A8Zl070A9DmAdM58FsWExKIjxdiiGUTnDfejgAtsNsJd9JxW3Kb+nJak6lN5awpCC+hyoqbagS7b+zvUTXTNBCZCeKz0VVP2mY71oyCuXSPv9VT30wyNTt3yqSxHfA8/lz1Vent+i7YiqEPEj6V7aXEtEOyBygs5C86qDH2h/t3ucOwvAPwGAmVcC0L1M7GgMo6IxYPFMFWIbO+xSCb740rJ7P0i3MrouEhNLgl7GqcHOVNKsQQcsfq1OzG5is640iRmNTWOHB+G0BYDVY6XJ6wJUPfTCU5n7C34f5Dl68DSKCoBm8gNENARiBnKuJo2wEt7D5VudGw0YRsWDAYtnKgBuhQiJblfKqOZgGdWE2bnnv7ypz7OC9KlwnGlQKMuNfynn+S9A5MGrxWUj4L0ioE9evFyeU5wUOOJT49HM/QWPBdN/GEmAiLOpJqLeEPlAgVgD4CcAQEQXAUhuv+G1DcOotGDA4pk8YPHM+3Am9btd+MKSVwMKUHAnSGJjqwqJgv+wBWtUQEQwU2Gw13nO9cPxzPqC1nopSq8f1dYFnbPz0O3yWLsJ/rKTGcAvM/cX/M5Pm3aFmfMglj37AbwNETIfiCcAzNKCSK8GcLT9Rtg2um3wmx6OL1r7fwDCPj3+TirettqyNyzxCp4MHLR7XXp6nu6gNze34Z/77RQTVCVF89ayNXKFI2ht3o8tj64dJ32vK1TeBbimDB54xCEFrG/TjL7lfPQvf1V6UOtfcxeA2zL3F7wVTH8GwWHMVPwwYPHMpyEcuKE4AL2igpW15oJ2mbqmphwP8Rci+AxsNcUW0mfnCefNPfW2NQGmxWWngq4qWJRKg/58lXS4RdXDegBXGAal/TGMSgAGLJ65BEIJK6Qty5ZsDYNWii+CDXpzE/TyB4CaZu0duFVrdvDIjFqO0a1xcn5D44SBTmfQGcabM6QJX44/XfXwKIDpmfsLWkpztMIjWnYPEf0nmPglIsomoouDHavOvsMmXt3eGEZFBwMWz/wfgKkAdEWG+sIBV02+fDQzPKNqjiw7a4MNenMTilHhBPNQBvTWEW7GUtdlQW2HvlZcOghCTycoXv+BPHvvIFoG4OzM/QV6ldvc28VjIMqE3BPoBOC0wHQ2RAZ9t8YwKjoZsHhmAYQgVEii3gDwjXnPDhDaJV8jKfnkwWCD3tyEYlRAJEMWgs3B8rpy8SSV9cdZ9Hcp/S6pbwglHP/lJ26Q78zcXxCqEPZaAMOJKIWIPiJRbmOTO1BNmz28RUTrAbwF4EkIQaNdRHRdy9mFNvtJ1+4/RkJMeh0RvUNnZFS9ClNHE4ZRCYIBi2dWAbgcQugmKD9LNTUcPyaVh5RVq4e0tKMhL884xM8Bx5tCyuS1w2Jbr2bpVskHgCfLyqeamI/obN4A4Jb8+fkL8ufn6xWrboY287gIQD7EzstOLabk1xBiRW5GAziPma8H8BsAy7WZji+NWBDR2RDykuO0a3g67f0JU0cFhlEJEm3L+SkA5wL6c06+MOcdC4dWii8SE0t068e2JoSZCgA1xRpy/kmO6+Z0Zuh2LFsA6+OnKvTMOHYAmJA/Pz/UIMYYTf50G4Qv5m8QMqZvAQAzrwKQSkTuEq+feEg86mU6gI81mdRaAP/1eM6fMHVUEHVGhUKo/Oalj8uJaFFbxjFg8czVEHkbrwZqe0Iq31MtNbTbLAUAQgl6cxPS8geAkmYLeSl3iPsPPoVEv/WBWnJlXf3ZvV0uX8sgFcAfAEzLn58fauF1oHkI/v0cuJyqv7gbF5p/x1oKkXnjDfgWpo4KosqoUIiV31qi1VFpc3DbgMUz6wYsnnkPgAsB+IwW/cqsO/E2JGJjqw6HEvTmQWjLn0TLUEbg7GNf/Nl1TdDnvlZc2gtakS4PDgKYkT8/f1H+/PygayrrYC00kWhNLvUUM3tbbp4Wq9YoBDBBO28CAHeVx/UALiNRSCwe4jPtJlhh6k5HVBkVeKn8xswntXTxZ7RU8S1ENBwAiOgyItpMoubQV1pINIjoFiJ6SbvvtTJbMAxYPPMLiBos/2j5nKaVMqYNrzkgqWlHQy7gBoQ+U4FMVkihOWsBYLkyd5KLJd2FwgBgiNM1eG5Do3uL2QngjwCy8+fn6w3PD4UcABM1adPFAOb7aPcNgNFuRy2A9wGkaFGw9wH4FgCYeSuEWPxuAP+D8Nu443GCFabudERVRK1m1ddB1Jj9CsIplktEhQBeY+bfkSibeq0mvJ0MoEoT3b4DQCYzLySiWwBMYub7iOgNAHEArgOQAbFGDnkpcXzR2vMAPA9gtAtK0zJr7ikmDkotLVjGZf9vTULCqaCjW93ciP+UMUm6g9I8sWwoXSfVOoOO4nXzovmF1ZfJm+YEc04jUcOcQf2/bJCkR/Ln5+uKeSGiRyByZxSIpdLdzKy7CHyAvuuYWXf1Au2ceGau03Z31gC4i5lDLSXSqYiqmYq3ym+agQDOyBa8A8DtvxgA4HPN6fUQfDu9vFVmC4kBi2d+BeHV//k68/5v2tugAEBcXGVby7CGnIOkJlvaFG38O+eNmcxC0lMnR2OYb9t8694rgzAoYVk2h5m/ag7hHQDe7yoGBYgyowL4rPwGoNlOgvv+ixDFyrIgSnv4cnqFpTKbmwGLZ7oGLJ75wndy8Y0AnoMIomoXZNlZJ0lKyDMrAGBQ6Ealp61NKQfFSOl9hHvriUGphlgaZCCn2ud2rQ9CLZg+jIg+I1EAfa2HCtsQItqoLbef8rwQET2kxZnsJk3RjYSGbQERvaZtMnwB4HbNGZzBzE8H+Xo6NVFlVMh35TdALF/cf93r60ScKRjvax3cbuTk5FTk5OQ8AFGF7u9oB+OSlFQUctCbByGfryZZhoYojnSap13X+9vhqINI6hyCnOqnkFMdShRvqAXT/wpRs2oihP7Jy9rx5wG8ov1YnfYJEdEFAEZA1ErOhvDDuJelIyDK+J4FoApnfgy7HNEmABMP4EUiSoLYrvsOYil0KYBkzZFmhyjBCggH23+IqBKiTtCQlh12BDk5OYUAbs/JyXkcwAMQJV+DWoP7Ii3taNAJd14IXYLBJMWBcBgc+nv7uTp5vJ1Nh63k8uyjCmKm+RfkVFeEPD6IZTMRTYQwHnMhipcvgveC6TKAtZr/7hyIz4+7K3ec0XScMQpvQWxlA6LM7wUQBgsQ/+MREPEuh5l5l3bcU9GtyxFVjlpfaI7aSdq0ttOTk5OTArF0ux9oW9j+5Cnvb7VaG3RVDPTFDXivCa2LuenGsq5kg1TvapPg1JOmf+TebPpyNkRA4RIAryGnOixJnC3RdvjmA9gCEWdyCYAfQ8SIyBD+tyMADjBzXy/nlwPozcwuLQjupFap81kA3zLzqy3apwNYoeUTQQvJj2fmnPZ4fZEmqpY/XQVtWfQkgMEQYdgh70JYLA3hyHhuk1iUmmwJOVbF3cULrqtKIVIghiOn+tlwGhQ/y2afBdO1OJTDRPQjrQ8ionHa+eshjBDQPJbkcwC3abMcEFF/IuoVrtcRLUTb8scrzJwe6TGEQk5OTgOEr+XvOTk5WRBSlj+Bzh0oLegtHEu6thmVNFsCjgedRAyIZcHfAfx92+IbjrVjrJevZXM9WhdM7+NRMP0GAK8Q0aMQ5Xv/DVFy9+cA3iaiXwH42H0RZv5CW05t1JZMdQBuRHNdly5Pl1j+dCVycnJMEBG6P4SYlvv8pRs4aPf69PQ8b+UfguIGer9tHTjUKts3RUk6Wx+FKDHxIYC1hYsvCcnJS0QKRNCYGcJQvAngOW5ddTAihBK70lXoEjOVrkROTo4LQl19ZU5OjgThRLwUwGUQuUanSUk50eZfQBWSgjbOVGCRkphwnBjeYnJUiOS8zwB8Urj4ku1tutYZGpk5GwC0JcbbEILSj4ep/5AgMUXptIW+OgJjphJF5OTkDIbIjp4JYMY5099WZFkZ1ZY+XTA55tPyoOv4tMSypniT1KhMhTAi+yD8RF8D+KJw8SVhL3beciZAREMBbIVwfEsQ4fRzIHZsljDzq1reTg6AUxBpFdsB3KhFXBdCBE5eBDHzuQvA0wCGA/gjMy/VfCUfQ2jfmgE8yswfa47Yz7XXPBFCqGmv5rxNg8hCfkqr19PliTqjQkTfAFjMzJ97HPsFgFHMfG/EBtYCLfx/BTO/117X+HrVsDQILQ73bTyAgQjil9IOS9Nt9E6oOz8OCM2PfNOB6rWmwroDALYXLr4kqAqGoeBteUFEVRCO1isA9GLmp4jICuFY/RGEY/xjiMjqk9rxh5h5nWZU/sDMrxDRcxDGezpEwOQeZu5NQmMllplrNGOxCWLLeDCA7wGcw8yb3OMDMAwix+dRZv6yHd+OTkU0Ln/egfC8f+5x7McAHo7McCLHufMOnYJYVnzmPvb1qmFWiHicodptAEREqfvWG0AMxC+4mUG+llDVEIXY3bciiEDC49rtMIBvi+dmixD7ueF9bW3kAgBjPZJDEyG+/A5o1QMBQAuTT4fIJwOEAQCEryZe0zqpJSK75uStB/B7LaBNBdAfZ5zqR9wGRcMMMVNbwMy5YX+FnZhoNCrvAXiKiCzM7NCmnv0gxHU2QnxZDgG4VQt6KoSoOngZxD/6R8y8n4jiIIKrxmjHc7Sp7Os4o8TVH8BLAHIBPMjMlwKAluG8jZnf0IKq/gyxw3AKwC3M3Czzloh+o10/BsAGiGS2dpkinjvvkB0iuzVghuvXq4ZJNtitEMsFhviiMABX8dzsqNqx0JY/CoBSiJna/Z6zWa3NHPivHuh+Tm3RTtXa3QCgJ4CJzOzUPlvuWV5LXRUXxPLqQojPT7ch6uJUmLkCImjJXdXtxxBh2I9AyPpNgHAMPuBxmreC148AWMXMkyF+Z/9IRHHMfIfmALwCwki84WssJDQvXgRwjRbK/XcA3opUvaRJBI6BMCyXemnT4Zw775B67rxDjcVzs+uL52Y3FM/Nbiqem23vCIOiLQ/ceTE/0dE+nYj2+HiuJ4ClEO8zQ8xi79X+PyCikdqPSFtJBFCqGZS5EMseXzCA2wBkaFvP3YZonKkAZ5ZAH2t/PwRwJYD1WnyABWhW/tKz4PXV2v0LAFxOZ4SJbQAGASggEV36H4hfuyMkat96YxTETOdL7boyPHJBPJhLRA9DSDakANiL5hKC3Zl0iNict4M8zy376N5SfgtixggAr2v97tB2Y8ogPh9t5V8A/ksi630bAswGmVkhousBfEJEtcz8sr/2XQZmjrobxFKjFEJV61uIpcU7PtoWAkjT7k8CsFq7vx3CuevtnDcALPJ4PAPApx6PXwdwC4AsiGhMX31cA2GsSgAM1I7nQCy1Iv4+Rvh/WKf93QThv9kF4JcQxmAthCTADgjnJ7Tje7T7awBke/S1DsC4SL8m4yZuUbf8AU7rqnwDsdx4B+KDOZ3OKL7FEdHIAN18DuB+7ZcMRDRe+7sAQA9uLjd5BELRy6o57M7Vjh8A0JOIphFRHyJaTkRHiWg7hGZHX5xZc5/StiSDVpbzhnv5EAmI6EoiYtKkANrIIgBrWcgAPAfxY3E+i+XqdQBeI48CXUR0OYBiCKMO7f9sY1Gf2KATEJVGReMdCDGkd1jkbdwC4B0tU3kjhIqbP34LMXXeTULu77fa8QcBZJGQBNxFRPcw8zEA70Kkx78LLQuVhSjyNRBZqocg4kd+y8K/sh1AIjNXAXhNO/dziFiKaOd6iNnB9S2f0LZd24IZwpDkQyxBh8OjQBczfwLxv75U85ncBj9+L4MIEOmpUle4AZgHYI2X4/EQ24o7ILYpr9COp0PEd7wG4V/5AkCM9tydEIYnD0LjNFY7PgTCWOYDeApnlg9er9GOrzUeYmt5JEQWLyCCzNZCbMl+C/Fj9TKEz+FLAJ9COLMBERyWC7Hz8jmEj2sFgNUQxvkEgAoIGYIYCIdnGURA3VEIg/IShNP9G4il02aIOJFrIvGeGLcWn5FID6Ar3AD8DCLvpOVxE4AE7X4aRCIbaUbFBc0vADH7uVG7n+px/lMQzmJoX9ibtfsLPIyK12u042u9AcDftPsbNCMxB2JLdYh2/BrNkEgA+gCo1I6ZtXN6QiTbXQfhbM/VjMqzEEp5r0FoEN+qGZWXtPdsj4dRmQhRNOyYdp3RAL6LxHti3Jrfonn5Ew0QRLDUbogviWew1GH2Ltrjq5jUdJzR4X1L5zXag+shsnWh/XUvgbYws7u42gwA/2Gh+1sMMaMAPHbLIGYhj0LsiCkQTnQTxAxnOsRSMgPN40VOw8zbIdT03+bW+sId/Z4YeBCtW8qdjb3w7oD1FyzVMggrRrv/BoArmTmPhKj3HI923gLm/F0jrBBRCsRSL4uIGGILnSESIP0V1TrdBUROTKvCakS0GsBbzHxQi1jdxsy/IqICCAGuQgiDe4vWvp926rYW/QMd+J4YtMaYqYSHVQCsRHSX+wCJIt6DoT9Yyo2vYlK+hIGCCchqK9dAfPEHM3M6Mw+ECNef2aLdegA/JCKJRK2lOdrx07tlgAgeJKJAZT1bFugCxIxnM4TPxJuh7cj3xKAFhlEJAywW71cBOI+IDmm7SU9D+BUmaUuZm6GvOJSvYlI/B7BA66u/x/F/hXCNULkeItDQk/fRehfofYj8oH0A/gnx5a9mj90yIsqDiE0JJEPZskAXIBzEA3FG9LwlHfmeGLQg6rKUDaIDOlMsKxUirWK65l8x6OIYPhWD9mKFFihogYjdMQxKN8GYqRgYGIQVw6diYGAQVgyjYmBgEFYMo2JgYBBWDKNiYGAQVgyjYmBgEFYMo2JgYBBWDKNiYGAQVgyjYmBgEFYMo2JgYBBWDKNiYGAQVgyjYmBgEFYMo2JgYBBWDKNiYGAQVgyjYmBgEFb+HxvikDzxn88sAAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# 5 Points\n",
    "normalized_database_filename = 'normalized.db'\n",
    "conn = create_connection(normalized_database_filename)\n",
    "sql_statement = ex5(conn)\n",
    "data = pd.read_csv(\"ex5.csv\")\n",
    "cur = conn.cursor()\n",
    "df = pd.read_sql_query(sql_statement, conn)\n",
    "assert df.equals(data) == True\n",
    "\n",
    "df.set_index('Country', inplace=True)\n",
    "df.plot.pie(y='Total', legend=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "markdown",
     "checksum": "07d8271ef9f1899ab1e3b5194eb9dfc5",
     "grade": false,
     "grade_id": "cell-edbbdf1c75a41829",
     "locked": true,
     "points": 20,
     "schema_version": 3,
     "solution": false,
     "task": true
    }
   },
   "source": [
    "# 20 Points\n",
    "# Did the student use executemany for all the insertions?"
   ]
  }
 ],
 "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.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}