Assignment1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "84f5743d",
   "metadata": {},
   "source": [
    "### Before you begin\n",
    "\n",
    "To check whether the code you've written is correct, we'll use **automark**. For this, we created for each of you an account with the username being your student number."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 247,
   "id": "c94b006b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----------------------------------------------------------------------\n",
      "| Li, Hualiya                                                        |\n",
      "----------------------------------------------------------------------\n",
      "| a0_t1_sailor_avg_experience                       | completed      |\n",
      "| a0_t2_sailor_avg_experience_pyspark               | completed      |\n",
      "| a1a_t1_all_model_number_b                         | completed      |\n",
      "| a1a_t2_all_model_number_not_b                     | completed      |\n",
      "| a1a_t3_avg_PCs_speed                              | completed      |\n",
      "| a1a_t4_max_printer_price                          | completed      |\n",
      "| a1a_t5_min_spending_visa                          | completed      |\n",
      "| a1a_t6_customer_id                                | completed      |\n",
      "| a1a_t7_highest_price_USD                          | completed      |\n",
      "| a1b_t1a_at_least_four_products                    | completed      |\n",
      "| a1b_t1b_decending_speed_laptop                    | completed      |\n",
      "| a1b_t1c_customer_number_purchase                  | completed      |\n",
      "| a1b_t1d_maker_with_ram                            | completed      |\n",
      "| a1b_t2a_avg_money_spent                           | completed      |\n",
      "| a1b_t2b_maximum_price_printer                     | completed      |\n",
      "| a2_t1a_max_measurement_per_sensor                 | not attempted  |\n",
      "| a2_t1b_global_max_measurement                     | not attempted  |\n",
      "| a2_t2_commacount                                  | not attempted  |\n",
      "| a2_t3_max_weekly_visitors                         | not attempted  |\n",
      "| a2_t4a_compute_max_value_per_sensor               | not attempted  |\n",
      "| a2_t4b_compute_global_max_value                   | not attempted  |\n",
      "| a2_t5_compute_max_weekly_visitors                 | not attempted  |\n",
      "| a2_t6_customers_with_certain_visa_payment         | not attempted  |\n",
      "| a2_t7_find_payment_types                          | not attempted  |\n",
      "| a2_t8_quantities_per_city                         | not attempted  |\n",
      "----------------------------------------------------------------------\n"
     ]
    }
   ],
   "source": [
    "import automark as am\n",
    "\n",
    "# fill in you student number as your username\n",
    "am.configure(username='13872893')\n",
    "\n",
    "# to check your progress, you can run this function\n",
    "am.get_progress()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2100ce66",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Settings things up for the excerises\n",
    "\n",
    "In this assignment, we work with an example database consisting of the following six tables:\n",
    "\n",
    "<img src=\"schema.png\" alt=\"Schema of the Database\" width=\"900\"/>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "75e47375",
   "metadata": {},
   "source": [
    "### DuckDB\n",
    "\n",
    "We will query this database with [DuckDB](https://duckdb.org/), an embeddable analytical database management system, developed by the [CWI Amsterdam](https://www.cwi.nl/research/groups/database-architectures). \n",
    "\n",
    "DuckDB is designed to be easy to install and allows to seamlessly work in a jupyter notebook.\n",
    "\n",
    "DuckDB also provides a [SQL Introduction](https://duckdb.org/docs/sql/introduction) that you can refer to when working on this assignment."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 221,
   "id": "b4cf90f9",
   "metadata": {},
   "outputs": [],
   "source": [
    "import duckdb"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4bf4a5e9",
   "metadata": {},
   "source": [
    "We connect to an in-memory database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 222,
   "id": "ce496c0d",
   "metadata": {},
   "outputs": [],
   "source": [
    "con = duckdb.connect(database=':memory:')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4d382adc",
   "metadata": {},
   "source": [
    "Next, we create all the relations using SQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 223,
   "id": "b71643a7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<duckdb.DuckDBPyConnection at 0x55911b5f70>"
      ]
     },
     "execution_count": 223,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Customers Table\n",
    "con.execute('''\n",
    "CREATE TABLE IF NOT EXISTS customers (\n",
    "    customer_id char(10) NOT NULL, \n",
    "    firstname varchar(32) default NULL, \n",
    "    lastname varchar(32) default NULL, \n",
    "    city varchar(32) default NULL,\n",
    "    address varchar(128) default NULL, \n",
    "    email varchar(128) default NULL, \n",
    "    \n",
    "    PRIMARY KEY (customer_id) )\n",
    "''')\n",
    "\n",
    "# Laptops Table\n",
    "con.execute('''\n",
    "CREATE TABLE IF NOT EXISTS laptops (\n",
    "    model char(4) NOT NULL default '',\n",
    "    speed double default NULL, \n",
    "    ram int default NULL, \n",
    "    hd int default NULL, \n",
    "    screen double default NULL, \n",
    "    price double default NULL, \n",
    "    \n",
    "    PRIMARY KEY  (model) )\n",
    "''')\n",
    "\n",
    "# PCs Table\n",
    "con.execute('''\n",
    "CREATE TABLE IF NOT EXISTS pcs (\n",
    "    model char(4) NOT NULL, \n",
    "    speed double NOT NULL, \n",
    "    ram int NOT NULL, \n",
    "    hd int NOT NULL, \n",
    "    price double NOT NULL, \n",
    "    \n",
    "    PRIMARY KEY  (model) )\n",
    "''')\n",
    "\n",
    "# Printers Table\n",
    "con.execute('''CREATE TABLE IF NOT EXISTS printers (\n",
    "    model char(4) NOT NULL default '', \n",
    "    color varchar(5) default NULL, \n",
    "    type varchar(10) default NULL,\n",
    "    price double default NULL,\n",
    "\n",
    "    PRIMARY KEY  (model) )\n",
    "''')\n",
    "\n",
    "# Products Table\n",
    "con.execute('''CREATE TABLE IF NOT EXISTS products ( \n",
    "    maker char(1) default NULL,\n",
    "    model char(4) NOT NULL default '',\n",
    "    type varchar(10) default NULL,\n",
    "\n",
    "PRIMARY KEY  (model) )\n",
    "''')\n",
    "\n",
    "# Sales Table\n",
    "con.execute('''CREATE TABLE IF NOT EXISTS sales ( \n",
    "    customer_id char(10) NOT NULL default '', \n",
    "    model char(4) NOT NULL default '',   \n",
    "    quantity int default NULL,   \n",
    "    day date NOT NULL default '0000-00-00', \n",
    "    paid double default NULL,   \n",
    "    type_of_payment varchar(32) default NULL,   \n",
    "    \n",
    "    PRIMARY KEY  (customer_id,model,day) )\n",
    "''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6b52271f",
   "metadata": {},
   "source": [
    "And fill the tables with some example data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 224,
   "id": "2c8d0f7a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<duckdb.DuckDBPyConnection at 0x55911b5f70>"
      ]
     },
     "execution_count": 224,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Customers Table\n",
    "con.execute('''\n",
    "INSERT INTO customers (customer_id, firstname, lastname, city, address, email)\n",
    "VALUES \n",
    "    ('1122334455', 'Ann', 'O''Brien', 'Rotterdam', '1 Jervis St.', 'ann@uva.nl'), \n",
    "    ('1231231231', 'John', 'Doe', 'Amsterdam', NULL, NULL), \n",
    "    ('1234567890', 'Maya', 'Ramanath', 'Diemen', 'Dalsteindreef 3002', NULL),\n",
    "    ('9876543210', 'Ji', 'Zhang', 'Utrecht', 'Jaarbeursplien 24', 'jack@ucc.ie'), \n",
    "    ('9999999999', 'Norah', 'Jones', 'Amsterdam', 'Passheuvelweg 34', 'nj@yahoo.com');\n",
    "''')\n",
    "\n",
    "# Laptops Table\n",
    "con.execute('''\n",
    "INSERT INTO laptops (model, speed, ram, hd, screen, price) \n",
    "VALUES \n",
    "    ('2001', 2, 2048, 240, 20.1, 3673),\n",
    "    ('2002', 1.73, 1024, 80, 17, 949), \n",
    "    ('2003', 1.8, 512, 60, 15.4, 549), \n",
    "    ('2004', 2, 512, 60, 13.3, 1150),\n",
    "    ('2005', 2.16, 1024, 120, 17, 2500), \n",
    "    ('2006', 2, 2048, 80, 15.4, 1700), \n",
    "    ('2007', 1.83, 1024, 120, 13.3, 1429),\n",
    "    ('2008', 1.6, 1024, 100, 15.4, 900), \n",
    "    ('2009', 1.6, 512, 80, 14.1, 680), \n",
    "    ('2010', 2, 2048, 160, 15.4, 2300);\n",
    "''')\n",
    "\n",
    "# PCs Table\n",
    "con.execute('''\n",
    "INSERT INTO pcs (model, speed, ram, hd, price) \n",
    "VALUES \n",
    "    ('1001', 2.66, 1024, 250, 2114),\n",
    "    ('1002', 2.1, 512, 250, 995), \n",
    "    ('1003', 1.42, 512, 80, 478), \n",
    "    ('1004', 2.8, 1024, 250, 649), \n",
    "    ('1005', 3.2, 512, 250, 630), \n",
    "    ('1006', 3.2, 1024, 320, 1049), \n",
    "    ('1007', 2.2, 1024, 200, 510), \n",
    "    ('1008', 2.2, 2048, 250, 770), \n",
    "    ('1009', 2, 1024, 250, 650), \n",
    "    ('1010', 2.8, 2048, 300, 770),\n",
    "    ('1011', 1.86, 2048, 160, 959), \n",
    "    ('1012', 2.8, 1024, 160, 649), \n",
    "    ('1013', 3.06, 512, 80, 529);\n",
    "''')\n",
    "\n",
    "# Printers Table\n",
    "con.execute('''\n",
    "INSERT INTO printers (model, color, type, price) \n",
    "VALUES \n",
    "    ('3001', 'TRUE', 'ink-jet', 99),\n",
    "    ('3002', 'FALSE', 'laser', 239), \n",
    "    ('3003', 'TRUE', 'laser', 899), \n",
    "    ('3004', 'TRUE', 'ink-jet', 120),\n",
    "    ('3005', 'FALSE', 'laser', 120), \n",
    "    ('3006', 'TRUE', 'ink-jet', 100), \n",
    "    ('3007', 'TRUE', 'laser', 200);\n",
    "''')\n",
    "\n",
    "# Products Table\n",
    "con.execute('''\n",
    "INSERT INTO products (maker, model, type) \n",
    "VALUES \n",
    "    ('A', '1001', 'pc'), \n",
    "    ('A', '1002', 'pc'),\n",
    "    ('A', '1003', 'pc'), \n",
    "    ('B', '1004', 'pc'), \n",
    "    ('B', '1005', 'pc'), \n",
    "    ('B', '1006', 'pc'), \n",
    "    ('C', '1007', 'pc'),\n",
    "    ('D', '1008', 'pc'), \n",
    "    ('D', '1009', 'pc'), \n",
    "    ('D', '1010', 'pc'), \n",
    "    ('E', '1011', 'pc'), \n",
    "    ('E', '1012', 'pc'),\n",
    "    ('E', '1013', 'pc'), \n",
    "    ('E', '2001', 'laptop'), \n",
    "    ('E', '2002', 'laptop'), \n",
    "    ('E', '2003', 'laptop'),\n",
    "    ('A', '2004', 'laptop'), \n",
    "    ('A', '2005', 'laptop'), \n",
    "    ('A', '2006', 'laptop'), \n",
    "    ('B', '2007', 'laptop'),\n",
    "    ('F', '2008', 'laptop'), \n",
    "    ('F', '2009', 'laptop'), \n",
    "    ('G', '2010', 'laptop'), \n",
    "    ('E', '3001', 'printer'), \n",
    "    ('E', '3002', 'printer'), \n",
    "    ('E', '3003', 'printer'), \n",
    "    ('D', '3004', 'printer'), \n",
    "    ('D', '3005', 'printer'),\n",
    "    ('H', '3006', 'printer'), \n",
    "    ('H', '3007', 'printer'); \n",
    "''')\n",
    "\n",
    "# Sales Table\n",
    "con.execute('''\n",
    "INSERT INTO sales (customer_id, model, quantity, day, paid, type_of_payment) \n",
    "VALUES \n",
    "    ('1122334455', '2010', 1, '2020-12-19', 2300, 'mastercard credit'), \n",
    "    ('1122334455', '3001', 1, '2020-12-18', 99, 'cash'), \n",
    "    ('1231231231', '2002', 2, '2020-12-19', 1898, 'visa credit'), \n",
    "    ('1231231231', '3002', 1, '2020-12-18', 239, 'cash'), \n",
    "    ('1234567890', '1001', 1, '2020-12-20', 1902.6, 'mastercard credit'),\n",
    "    ('9876543210', '1007', 1, '2020-12-17', 510, 'visa debit'), \n",
    "    ('9876543210', '1007', 3, '2020-12-19', 1530, 'visa debit'), \n",
    "    ('9876543210', '2002', 1, '2020-12-17', 949, 'visa debit'), \n",
    "    ('9999999999', '1007', 1, '2020-12-20', 459, 'visa credit'), \n",
    "    ('9999999999', '3007', 2, '2020-12-20', 360, 'visa credit');\n",
    "''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5599752d",
   "metadata": {},
   "source": [
    "Now, we can query the database using SQL and convert the results to pandas dataframes to look at them:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 225,
   "id": "048b1d98",
   "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>maker</th>\n",
       "      <th>model</th>\n",
       "      <th>type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>1001</td>\n",
       "      <td>pc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A</td>\n",
       "      <td>1002</td>\n",
       "      <td>pc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A</td>\n",
       "      <td>1003</td>\n",
       "      <td>pc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>B</td>\n",
       "      <td>1004</td>\n",
       "      <td>pc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>B</td>\n",
       "      <td>1005</td>\n",
       "      <td>pc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>B</td>\n",
       "      <td>1006</td>\n",
       "      <td>pc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>C</td>\n",
       "      <td>1007</td>\n",
       "      <td>pc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>D</td>\n",
       "      <td>1008</td>\n",
       "      <td>pc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>D</td>\n",
       "      <td>1009</td>\n",
       "      <td>pc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>D</td>\n",
       "      <td>1010</td>\n",
       "      <td>pc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>E</td>\n",
       "      <td>1011</td>\n",
       "      <td>pc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>E</td>\n",
       "      <td>1012</td>\n",
       "      <td>pc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>E</td>\n",
       "      <td>1013</td>\n",
       "      <td>pc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>E</td>\n",
       "      <td>2001</td>\n",
       "      <td>laptop</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>E</td>\n",
       "      <td>2002</td>\n",
       "      <td>laptop</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>E</td>\n",
       "      <td>2003</td>\n",
       "      <td>laptop</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>A</td>\n",
       "      <td>2004</td>\n",
       "      <td>laptop</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>A</td>\n",
       "      <td>2005</td>\n",
       "      <td>laptop</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>A</td>\n",
       "      <td>2006</td>\n",
       "      <td>laptop</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>B</td>\n",
       "      <td>2007</td>\n",
       "      <td>laptop</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>F</td>\n",
       "      <td>2008</td>\n",
       "      <td>laptop</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>F</td>\n",
       "      <td>2009</td>\n",
       "      <td>laptop</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>G</td>\n",
       "      <td>2010</td>\n",
       "      <td>laptop</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>E</td>\n",
       "      <td>3001</td>\n",
       "      <td>printer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>E</td>\n",
       "      <td>3002</td>\n",
       "      <td>printer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>E</td>\n",
       "      <td>3003</td>\n",
       "      <td>printer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>D</td>\n",
       "      <td>3004</td>\n",
       "      <td>printer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>D</td>\n",
       "      <td>3005</td>\n",
       "      <td>printer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>H</td>\n",
       "      <td>3006</td>\n",
       "      <td>printer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>H</td>\n",
       "      <td>3007</td>\n",
       "      <td>printer</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   maker model     type\n",
       "0      A  1001       pc\n",
       "1      A  1002       pc\n",
       "2      A  1003       pc\n",
       "3      B  1004       pc\n",
       "4      B  1005       pc\n",
       "5      B  1006       pc\n",
       "6      C  1007       pc\n",
       "7      D  1008       pc\n",
       "8      D  1009       pc\n",
       "9      D  1010       pc\n",
       "10     E  1011       pc\n",
       "11     E  1012       pc\n",
       "12     E  1013       pc\n",
       "13     E  2001   laptop\n",
       "14     E  2002   laptop\n",
       "15     E  2003   laptop\n",
       "16     A  2004   laptop\n",
       "17     A  2005   laptop\n",
       "18     A  2006   laptop\n",
       "19     B  2007   laptop\n",
       "20     F  2008   laptop\n",
       "21     F  2009   laptop\n",
       "22     G  2010   laptop\n",
       "23     E  3001  printer\n",
       "24     E  3002  printer\n",
       "25     E  3003  printer\n",
       "26     D  3004  printer\n",
       "27     D  3005  printer\n",
       "28     H  3006  printer\n",
       "29     H  3007  printer"
      ]
     },
     "execution_count": 225,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products = con.execute(\"SELECT * FROM products\").fetchdf()\n",
    "products"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "6aae8110",
   "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>model</th>\n",
       "      <th>speed</th>\n",
       "      <th>ram</th>\n",
       "      <th>hd</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>2.66</td>\n",
       "      <td>1024</td>\n",
       "      <td>250</td>\n",
       "      <td>2114.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>2.10</td>\n",
       "      <td>512</td>\n",
       "      <td>250</td>\n",
       "      <td>995.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>1.42</td>\n",
       "      <td>512</td>\n",
       "      <td>80</td>\n",
       "      <td>478.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>2.80</td>\n",
       "      <td>1024</td>\n",
       "      <td>250</td>\n",
       "      <td>649.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>3.20</td>\n",
       "      <td>512</td>\n",
       "      <td>250</td>\n",
       "      <td>630.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1006</td>\n",
       "      <td>3.20</td>\n",
       "      <td>1024</td>\n",
       "      <td>320</td>\n",
       "      <td>1049.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1007</td>\n",
       "      <td>2.20</td>\n",
       "      <td>1024</td>\n",
       "      <td>200</td>\n",
       "      <td>510.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1008</td>\n",
       "      <td>2.20</td>\n",
       "      <td>2048</td>\n",
       "      <td>250</td>\n",
       "      <td>770.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1009</td>\n",
       "      <td>2.00</td>\n",
       "      <td>1024</td>\n",
       "      <td>250</td>\n",
       "      <td>650.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1010</td>\n",
       "      <td>2.80</td>\n",
       "      <td>2048</td>\n",
       "      <td>300</td>\n",
       "      <td>770.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1011</td>\n",
       "      <td>1.86</td>\n",
       "      <td>2048</td>\n",
       "      <td>160</td>\n",
       "      <td>959.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1012</td>\n",
       "      <td>2.80</td>\n",
       "      <td>1024</td>\n",
       "      <td>160</td>\n",
       "      <td>649.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1013</td>\n",
       "      <td>3.06</td>\n",
       "      <td>512</td>\n",
       "      <td>80</td>\n",
       "      <td>529.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   model  speed   ram   hd   price\n",
       "0   1001   2.66  1024  250  2114.0\n",
       "1   1002   2.10   512  250   995.0\n",
       "2   1003   1.42   512   80   478.0\n",
       "3   1004   2.80  1024  250   649.0\n",
       "4   1005   3.20   512  250   630.0\n",
       "5   1006   3.20  1024  320  1049.0\n",
       "6   1007   2.20  1024  200   510.0\n",
       "7   1008   2.20  2048  250   770.0\n",
       "8   1009   2.00  1024  250   650.0\n",
       "9   1010   2.80  2048  300   770.0\n",
       "10  1011   1.86  2048  160   959.0\n",
       "11  1012   2.80  1024  160   649.0\n",
       "12  1013   3.06   512   80   529.0"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pcs = con.execute(\"SELECT * FROM pcs\").fetchdf()\n",
    "pcs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "06773dac",
   "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>model</th>\n",
       "      <th>color</th>\n",
       "      <th>type</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3001</td>\n",
       "      <td>TRUE</td>\n",
       "      <td>ink-jet</td>\n",
       "      <td>99.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3002</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>laser</td>\n",
       "      <td>239.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3003</td>\n",
       "      <td>TRUE</td>\n",
       "      <td>laser</td>\n",
       "      <td>899.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3004</td>\n",
       "      <td>TRUE</td>\n",
       "      <td>ink-jet</td>\n",
       "      <td>120.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3005</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>laser</td>\n",
       "      <td>120.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>3006</td>\n",
       "      <td>TRUE</td>\n",
       "      <td>ink-jet</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>3007</td>\n",
       "      <td>TRUE</td>\n",
       "      <td>laser</td>\n",
       "      <td>200.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  model  color     type  price\n",
       "0  3001   TRUE  ink-jet   99.0\n",
       "1  3002  FALSE    laser  239.0\n",
       "2  3003   TRUE    laser  899.0\n",
       "3  3004   TRUE  ink-jet  120.0\n",
       "4  3005  FALSE    laser  120.0\n",
       "5  3006   TRUE  ink-jet  100.0\n",
       "6  3007   TRUE    laser  200.0"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "printers = con.execute(\"SELECT * FROM printers\").fetchdf()\n",
    "printers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "82b779ce",
   "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>customer_id</th>\n",
       "      <th>model</th>\n",
       "      <th>quantity</th>\n",
       "      <th>day</th>\n",
       "      <th>paid</th>\n",
       "      <th>type_of_payment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1122334455</td>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-12-19</td>\n",
       "      <td>2300.0</td>\n",
       "      <td>mastercard credit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1122334455</td>\n",
       "      <td>3001</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-12-18</td>\n",
       "      <td>99.0</td>\n",
       "      <td>cash</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1231231231</td>\n",
       "      <td>2002</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-12-19</td>\n",
       "      <td>1898.0</td>\n",
       "      <td>visa credit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1231231231</td>\n",
       "      <td>3002</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-12-18</td>\n",
       "      <td>239.0</td>\n",
       "      <td>cash</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1234567890</td>\n",
       "      <td>1001</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-12-20</td>\n",
       "      <td>1902.6</td>\n",
       "      <td>mastercard credit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>9876543210</td>\n",
       "      <td>1007</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-12-17</td>\n",
       "      <td>510.0</td>\n",
       "      <td>visa debit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>9876543210</td>\n",
       "      <td>1007</td>\n",
       "      <td>3</td>\n",
       "      <td>2020-12-19</td>\n",
       "      <td>1530.0</td>\n",
       "      <td>visa debit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>9876543210</td>\n",
       "      <td>2002</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-12-17</td>\n",
       "      <td>949.0</td>\n",
       "      <td>visa debit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9999999999</td>\n",
       "      <td>1007</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-12-20</td>\n",
       "      <td>459.0</td>\n",
       "      <td>visa credit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9999999999</td>\n",
       "      <td>3007</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-12-20</td>\n",
       "      <td>360.0</td>\n",
       "      <td>visa credit</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  customer_id model  quantity        day    paid    type_of_payment\n",
       "0  1122334455  2010         1 2020-12-19  2300.0  mastercard credit\n",
       "1  1122334455  3001         1 2020-12-18    99.0               cash\n",
       "2  1231231231  2002         2 2020-12-19  1898.0        visa credit\n",
       "3  1231231231  3002         1 2020-12-18   239.0               cash\n",
       "4  1234567890  1001         1 2020-12-20  1902.6  mastercard credit\n",
       "5  9876543210  1007         1 2020-12-17   510.0         visa debit\n",
       "6  9876543210  1007         3 2020-12-19  1530.0         visa debit\n",
       "7  9876543210  2002         1 2020-12-17   949.0         visa debit\n",
       "8  9999999999  1007         1 2020-12-20   459.0        visa credit\n",
       "9  9999999999  3007         2 2020-12-20   360.0        visa credit"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sales = con.execute(\"SELECT * FROM sales\").fetchdf()\n",
    "sales"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "4534860f",
   "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>customer_id</th>\n",
       "      <th>firstname</th>\n",
       "      <th>lastname</th>\n",
       "      <th>city</th>\n",
       "      <th>address</th>\n",
       "      <th>email</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1122334455</td>\n",
       "      <td>Ann</td>\n",
       "      <td>O'Brien</td>\n",
       "      <td>Rotterdam</td>\n",
       "      <td>1 Jervis St.</td>\n",
       "      <td>ann@uva.nl</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1231231231</td>\n",
       "      <td>John</td>\n",
       "      <td>Doe</td>\n",
       "      <td>Amsterdam</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1234567890</td>\n",
       "      <td>Maya</td>\n",
       "      <td>Ramanath</td>\n",
       "      <td>Diemen</td>\n",
       "      <td>Dalsteindreef 3002</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9876543210</td>\n",
       "      <td>Ji</td>\n",
       "      <td>Zhang</td>\n",
       "      <td>Utrecht</td>\n",
       "      <td>Jaarbeursplien 24</td>\n",
       "      <td>jack@ucc.ie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9999999999</td>\n",
       "      <td>Norah</td>\n",
       "      <td>Jones</td>\n",
       "      <td>Amsterdam</td>\n",
       "      <td>Passheuvelweg 34</td>\n",
       "      <td>nj@yahoo.com</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  customer_id firstname  lastname       city             address         email\n",
       "0  1122334455       Ann   O'Brien  Rotterdam        1 Jervis St.    ann@uva.nl\n",
       "1  1231231231      John       Doe  Amsterdam                 NaN           NaN\n",
       "2  1234567890      Maya  Ramanath     Diemen  Dalsteindreef 3002           NaN\n",
       "3  9876543210        Ji     Zhang    Utrecht   Jaarbeursplien 24   jack@ucc.ie\n",
       "4  9999999999     Norah     Jones  Amsterdam    Passheuvelweg 34  nj@yahoo.com"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customers = con.execute(\"SELECT * FROM customers\").fetchdf()\n",
    "customers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "ce0ea4c9",
   "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>model</th>\n",
       "      <th>speed</th>\n",
       "      <th>ram</th>\n",
       "      <th>hd</th>\n",
       "      <th>screen</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2001</td>\n",
       "      <td>2.00</td>\n",
       "      <td>2048</td>\n",
       "      <td>240</td>\n",
       "      <td>20.1</td>\n",
       "      <td>3673.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2002</td>\n",
       "      <td>1.73</td>\n",
       "      <td>1024</td>\n",
       "      <td>80</td>\n",
       "      <td>17.0</td>\n",
       "      <td>949.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2003</td>\n",
       "      <td>1.80</td>\n",
       "      <td>512</td>\n",
       "      <td>60</td>\n",
       "      <td>15.4</td>\n",
       "      <td>549.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2004</td>\n",
       "      <td>2.00</td>\n",
       "      <td>512</td>\n",
       "      <td>60</td>\n",
       "      <td>13.3</td>\n",
       "      <td>1150.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2005</td>\n",
       "      <td>2.16</td>\n",
       "      <td>1024</td>\n",
       "      <td>120</td>\n",
       "      <td>17.0</td>\n",
       "      <td>2500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2006</td>\n",
       "      <td>2.00</td>\n",
       "      <td>2048</td>\n",
       "      <td>80</td>\n",
       "      <td>15.4</td>\n",
       "      <td>1700.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2007</td>\n",
       "      <td>1.83</td>\n",
       "      <td>1024</td>\n",
       "      <td>120</td>\n",
       "      <td>13.3</td>\n",
       "      <td>1429.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2008</td>\n",
       "      <td>1.60</td>\n",
       "      <td>1024</td>\n",
       "      <td>100</td>\n",
       "      <td>15.4</td>\n",
       "      <td>900.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2009</td>\n",
       "      <td>1.60</td>\n",
       "      <td>512</td>\n",
       "      <td>80</td>\n",
       "      <td>14.1</td>\n",
       "      <td>680.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2010</td>\n",
       "      <td>2.00</td>\n",
       "      <td>2048</td>\n",
       "      <td>160</td>\n",
       "      <td>15.4</td>\n",
       "      <td>2300.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  model  speed   ram   hd  screen   price\n",
       "0  2001   2.00  2048  240    20.1  3673.0\n",
       "1  2002   1.73  1024   80    17.0   949.0\n",
       "2  2003   1.80   512   60    15.4   549.0\n",
       "3  2004   2.00   512   60    13.3  1150.0\n",
       "4  2005   2.16  1024  120    17.0  2500.0\n",
       "5  2006   2.00  2048   80    15.4  1700.0\n",
       "6  2007   1.83  1024  120    13.3  1429.0\n",
       "7  2008   1.60  1024  100    15.4   900.0\n",
       "8  2009   1.60   512   80    14.1   680.0\n",
       "9  2010   2.00  2048  160    15.4  2300.0"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "laptops = con.execute(\"SELECT * FROM laptops\").fetchdf()\n",
    "laptops"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fd770a9d",
   "metadata": {},
   "source": [
    "The following helper function allows us to run queries on the database:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "76665ea3",
   "metadata": {},
   "outputs": [],
   "source": [
    "def execute_local(query):\n",
    "    con = duckdb.connect(database=':memory:', read_only=False)\n",
    "    con.register('products', products)\n",
    "    con.register('customers', customers)\n",
    "    con.register('sales',sales)\n",
    "    con.register('laptops',laptops)\n",
    "    con.register('pcs',pcs)\n",
    "    \n",
    "    result = con.execute(query).fetchdf()\n",
    "    \n",
    "    return result"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bfde7be2",
   "metadata": {},
   "source": [
    "This code is required to display things in a nicer way."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "dc26547f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style>\n",
       "  table {margin-left: 0 !important;}\n",
       "</style>\n"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%html\n",
    "<style>\n",
    "  table {margin-left: 0 !important;}\n",
    "</style>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "56fda07d",
   "metadata": {},
   "source": [
    "## Part A: Basic SQL Select Queries\n",
    "\n",
    "In the following, we ask you to write a set of SQL queries for querying the database.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "11707a0d",
   "metadata": {},
   "source": [
    "### Task 1. List only the model number of all products made by maker B."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "0e624460",
   "metadata": {},
   "outputs": [],
   "source": [
    "def a1a_t1_all_model_number_b():\n",
    "    \"\"\"List only the model number of all products made by maker B.\n",
    "    \"\"\"\n",
    "    #################\n",
    "    ### YOUR CODE ###\n",
    "    #################\n",
    "    query = \"SELECT model FROM Products WHERE products.maker == 'B';\"\n",
    "\n",
    "    \n",
    "    return query"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "39ae4ab9",
   "metadata": {},
   "source": [
    "You can test your query by running it on our local database via the `execute_local` function and inspecting the results.\n",
    "\n",
    "We expect the following result:\n",
    "\n",
    "|__model__|\n",
    "|---|\n",
    "|1004|\n",
    "|1005|\n",
    "|1006|\n",
    "|2007|\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "b433b41d",
   "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>model</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  model\n",
       "0  1004\n",
       "1  1005\n",
       "2  1006\n",
       "3  2007"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "execute_local(a1a_t1_all_model_number_b())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "17767741",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The current version of local tests is outdated. The local tests are removed.\n",
      "Downloading local tests...\n",
      "Local tests are downloaded.\n",
      "Running local tests...\n",
      "a1a_t1_all_model_number_b successfully passed local tests\n",
      "Running remote test...\n",
      "Test was successful. Congratulations!\n"
     ]
    }
   ],
   "source": [
    "am.test_student_function(a1a_t1_all_model_number_b)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e1edeee2",
   "metadata": {},
   "source": [
    "### Task 2. List the model numbers of all products not made by maker B."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "ecf9cf9e",
   "metadata": {},
   "outputs": [],
   "source": [
    "def a1a_t2_all_model_number_not_b():\n",
    "    \"\"\"List the model numbers of all products not made by maker B.\"\"\"\n",
    "    #################\n",
    "    ### YOUR CODE ###\n",
    "    #################\n",
    "    query = \"SELECT model FROM Products WHERE products.maker != 'B';\"\n",
    "\n",
    "\n",
    "\n",
    "    return query"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b65d2bcd",
   "metadata": {},
   "source": [
    "You can test your query by running it on our local database via the `execute_local` function and inspecting the results.\n",
    "\n",
    "We expect the following result:\n",
    "\n",
    "|__model__|\n",
    "|---|\n",
    "|1001|\n",
    "|1002|\n",
    "|1003|\n",
    "|1007|\n",
    "|1008|\n",
    "|1009|\n",
    "|1010|\n",
    "|1011|\n",
    "|1012|\n",
    "|1013|\n",
    "|2001|\n",
    "|2002|\n",
    "|2003|\n",
    "|2004|\n",
    "|2005|\n",
    "|2006|\n",
    "|2008|\n",
    "|2009|\n",
    "|2010|\n",
    "|3001|\n",
    "|3002|\n",
    "|3003|\n",
    "|3004|\n",
    "|3005|\n",
    "|3006|\n",
    "|3007|"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "10027177",
   "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>model</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>3001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>3002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>3003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>3004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>3005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>3006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>3007</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   model\n",
       "0   1001\n",
       "1   1002\n",
       "2   1003\n",
       "3   1007\n",
       "4   1008\n",
       "5   1009\n",
       "6   1010\n",
       "7   1011\n",
       "8   1012\n",
       "9   1013\n",
       "10  2001\n",
       "11  2002\n",
       "12  2003\n",
       "13  2004\n",
       "14  2005\n",
       "15  2006\n",
       "16  2008\n",
       "17  2009\n",
       "18  2010\n",
       "19  3001\n",
       "20  3002\n",
       "21  3003\n",
       "22  3004\n",
       "23  3005\n",
       "24  3006\n",
       "25  3007"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "execute_local(a1a_t2_all_model_number_not_b())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "47fbe074",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Running local tests...\n",
      "a1a_t2_all_model_number_not_b successfully passed local tests\n",
      "Running remote test...\n",
      "Test was successful. Congratulations!\n"
     ]
    }
   ],
   "source": [
    "am.test_student_function(a1a_t2_all_model_number_not_b)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2268b094",
   "metadata": {},
   "source": [
    "###  Task 3. Return the average speed of all PCs with speed >=3.00."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "ef90d554",
   "metadata": {},
   "outputs": [],
   "source": [
    "def a1a_t3_avg_PCs_speed():\n",
    "    \"\"\"Return the average speed all PCs with speed >= 3.00\"\"\"\n",
    "    #################\n",
    "    ### YOUR CODE ###\n",
    "    #################\n",
    "    query = \"SELECT AVG(speed) FROM pcs WHERE pcs.speed >=3;\"\n",
    "    \n",
    "    return query"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "83842922",
   "metadata": {},
   "source": [
    "You can test your query by running it on our local database via the `execute_local` function and inspecting the results.\n",
    "\n",
    "We expect the following result:\n",
    "\n",
    "|__avg(speed)__|\n",
    "|---|\n",
    "|3.153333|"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "e757fc9b",
   "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>avg(speed)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3.153333</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   avg(speed)\n",
       "0    3.153333"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "execute_local(a1a_t3_avg_PCs_speed())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "e6920581",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Running local tests...\n",
      "a1a_t3_avg_PCs_speed successfully passed local tests\n",
      "Running remote test...\n",
      "Test was successful. Congratulations!\n"
     ]
    }
   ],
   "source": [
    "am.test_student_function(a1a_t3_avg_PCs_speed)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "beb1d33d",
   "metadata": {},
   "source": [
    "###  Task 4. Return the price of the most expensive color laser printer."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "71f3642d",
   "metadata": {},
   "outputs": [],
   "source": [
    "def a1a_t4_max_printer_price():\n",
    "    \"\"\"Return the price of the most expensive color laser printer\"\"\"\n",
    "    #################\n",
    "    ### YOUR CODE ###\n",
    "    #################\n",
    "    #query =\"SELECT price FROM printers WHERE price == MAX(price);\"\n",
    "    #query = \"SELECT MAX(price) FROM printers;\"\n",
    "    query = \"SELECT MAX(price) FROM printers p where p.color == TRUE and p.type == 'laser';\"\n",
    "    return query"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3bdd066f",
   "metadata": {},
   "source": [
    "You can test your query by running it on our local database via the `execute_local` function and inspecting the results.\n",
    "\n",
    "We expect the following result:\n",
    "\n",
    "|__max(price)__|\n",
    "|---|\n",
    "|899.0|"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "38f43466",
   "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>max(price)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>899.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   max(price)\n",
       "0       899.0"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "execute_local(a1a_t4_max_printer_price())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "4ef1f5c0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Running local tests...\n",
      "a1a_t4_max_printer_price successfully passed local tests\n",
      "Running remote test...\n",
      "Test was successful. Congratulations!\n"
     ]
    }
   ],
   "source": [
    "am.test_student_function(a1a_t4_max_printer_price)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c7d21ec5",
   "metadata": {},
   "source": [
    "###  Task 5. Return the minimum amount paid by customers who used a visa card (debit or credit) to purchase a product."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "id": "ab85b000",
   "metadata": {},
   "outputs": [],
   "source": [
    "def a1a_t5_min_spending_visa():\n",
    "    \"\"\"Return the minimum amount paid by customers who used a visa card (debit or credit) to purchase a product\"\"\"\n",
    "    #################\n",
    "    ### YOUR CODE ###\n",
    "    #################\n",
    "    query = \"Select min(paid) from sales where type_of_payment ILIKE '%visa%';\"\n",
    "   \n",
    "    return query"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a0958ffb",
   "metadata": {},
   "source": [
    "You can test your query by running it on our local database via the `execute_local` function and inspecting the results.\n",
    "\n",
    "We expect the following result:\n",
    "\n",
    "|__min(paid)__|\n",
    "|---|\n",
    "|360.0|"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "id": "c90e6770",
   "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>min(paid)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>360.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   min(paid)\n",
       "0      360.0"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "execute_local(a1a_t5_min_spending_visa())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "id": "6f195291",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Running local tests...\n",
      "a1a_t5_min_spending_visa successfully passed local tests\n",
      "Running remote test...\n",
      "Test was successful. Congratulations!\n"
     ]
    }
   ],
   "source": [
    "am.test_student_function(a1a_t5_min_spending_visa)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3c918481",
   "metadata": {},
   "source": [
    "### Task 6. Find the `customer_id` of customers who have the letter 'e' either in their first name or in their last name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "id": "7dae2496",
   "metadata": {},
   "outputs": [],
   "source": [
    "def a1a_t6_customer_id():\n",
    "    \"\"\"Find the customer_id of customers who have the letter 'e' either in their first name \n",
    "       or in their last name\"\"\"\n",
    "    #################\n",
    "    ### YOUR CODE ###\n",
    "    #################\n",
    "    query = \"Select customer_id from customers where (customers.firstname like '%e%') or (customers.lastname like '%e%');\"\n",
    "    \n",
    "    return query"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "572d6fc9",
   "metadata": {},
   "source": [
    "You can test your query by running it on our local database via the `execute_local` function and inspecting the results.\n",
    "\n",
    "We expect the following result:\n",
    "\n",
    "|__customer_id__|\n",
    "|---|\n",
    "|1122334455|\n",
    "|1231231231|\n",
    "|9999999999|"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "id": "40b0e167",
   "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>customer_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1122334455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1231231231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9999999999</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  customer_id\n",
       "0  1122334455\n",
       "1  1231231231\n",
       "2  9999999999"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "execute_local(a1a_t6_customer_id())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "id": "a5f05f15",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Running local tests...\n",
      "a1a_t6_customer_id successfully passed local tests\n",
      "Running remote test...\n",
      "Test was successful. Congratulations!\n"
     ]
    }
   ],
   "source": [
    "am.test_student_function(a1a_t6_customer_id)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b61833ab",
   "metadata": {},
   "source": [
    "### Task 7. Assume all prices in the Laptops table are in Euro. List the model numbers of all laptops with ram at least 1024. For each model, list also its price in USD. Assume that 1 USD = 0.85 EURO. Name the price column 'price (USD)'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "id": "90b83e3c",
   "metadata": {},
   "outputs": [],
   "source": [
    "def a1a_t7_highest_price_USD():\n",
    "    \"\"\"Assume all prices in the table Laptops are in Euro. List the prices of laptops with at least 1024 ram. \n",
    "    You should return the price in USD in a column called 'price (USD)'. Assume that 1 USD = 0.85 EURO. \n",
    "    Name the price column 'price (USD)'.\n",
    "    \"\"\"\n",
    "    #################\n",
    "    ### YOUR CODE ###\n",
    "    #################\n",
    "    query = \"select model, price/0.85 as 'price (USD)' from laptops where laptops.ram >= 1024;\"\n",
    " \n",
    "    return query"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7bcc6c8e",
   "metadata": {},
   "source": [
    "You can test your query by running it on our local database via the `execute_local` function and inspecting the results.\n",
    "\n",
    "We expect the following result:\n",
    "\n",
    "|__model__|__price (USD)__|\n",
    "|---|---|\n",
    "|2001|4321.176471|\n",
    "|2002|1116.470588|\n",
    "|2005|2941.176471|\n",
    "|2006|2000.000000|\n",
    "|2007|1681.176471|\n",
    "|2008|1058.823529|\n",
    "|2010|2705.882353|"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "id": "02619e23",
   "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>model</th>\n",
       "      <th>price (USD)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2001</td>\n",
       "      <td>4321.176471</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2002</td>\n",
       "      <td>1116.470588</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2005</td>\n",
       "      <td>2941.176471</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2006</td>\n",
       "      <td>2000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2007</td>\n",
       "      <td>1681.176471</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2008</td>\n",
       "      <td>1058.823529</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2010</td>\n",
       "      <td>2705.882353</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  model  price (USD)\n",
       "0  2001  4321.176471\n",
       "1  2002  1116.470588\n",
       "2  2005  2941.176471\n",
       "3  2006  2000.000000\n",
       "4  2007  1681.176471\n",
       "5  2008  1058.823529\n",
       "6  2010  2705.882353"
      ]
     },
     "execution_count": 115,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "execute_local(a1a_t7_highest_price_USD())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "id": "db83a906",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Running local tests...\n",
      "a1a_t7_highest_price_USD successfully passed local tests\n",
      "Running remote test...\n",
      "Test was successful. Congratulations!\n"
     ]
    }
   ],
   "source": [
    "am.test_student_function(a1a_t7_highest_price_USD)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c864d953",
   "metadata": {},
   "source": [
    "## Part B: SQL queries involving joins and grouping"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f9ff3545",
   "metadata": {},
   "source": [
    "### Task 1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f9786d50",
   "metadata": {},
   "source": [
    "### Task 1a. Return a list of makers that make more than four different products."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 177,
   "id": "6568e172",
   "metadata": {},
   "outputs": [],
   "source": [
    "def a1b_t1a_at_least_four_products():\n",
    "    \"\"\"Return a list of makers that make more than four different products.\n",
    "    \"\"\"\n",
    "    #################\n",
    "    ### YOUR CODE ###\n",
    "    #################\n",
    "    #query = \"select maker from products where maker in (select maker,count(model) as v from products where v>4 group by maker);\"\n",
    "    query = \"select maker from (select maker,count(model) as v from products group by maker) where v>4;\"\n",
    "    return query"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b9ac555f",
   "metadata": {},
   "source": [
    "You can test your query by running it on our local database via the `execute_local` function and inspecting the results.\n",
    "\n",
    "We expect the following result:\n",
    "\n",
    "|__maker__|\n",
    "|---|\n",
    "|A|\n",
    "|D|\n",
    "|E|"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 178,
   "id": "37a75840",
   "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>maker</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>D</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  maker\n",
       "0     A\n",
       "1     D\n",
       "2     E"
      ]
     },
     "execution_count": 178,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "execute_local(a1b_t1a_at_least_four_products())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "id": "c89807da",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Running local tests...\n",
      "a1b_t1a_at_least_four_products successfully passed local tests\n",
      "Running remote test...\n",
      "Test was successful. Congratulations!\n"
     ]
    }
   ],
   "source": [
    "am.test_student_function(a1b_t1a_at_least_four_products)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "790eff41",
   "metadata": {},
   "source": [
    "### Task 1b. List all the laptop model numbers that have a speed greater than 1.7 in descending order."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "id": "95cff257",
   "metadata": {},
   "outputs": [],
   "source": [
    "def a1b_t1b_decending_speed_laptop():\n",
    "    \"\"\" List all the laptop model numbers that have a speed greater than 1.7 in descending order.\n",
    "    \"\"\"\n",
    "    #################\n",
    "    ### YOUR CODE ###\n",
    "    #################\n",
    "    query = \"select model from laptops where speed > 1.7 ORDER BY speed DESC; \"\n",
    "    \n",
    "    return query"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2814eb32",
   "metadata": {},
   "source": [
    "You can test your query by running it on our local database via the `execute_local` function and inspecting the results.\n",
    "\n",
    "We expect the following result:\n",
    "\n",
    "|__model__|\n",
    "|---|\n",
    "|2005|\n",
    "|2001|\n",
    "|2004|\n",
    "|2006|\n",
    "|2010|\n",
    "|2007|\n",
    "|2003|\n",
    "|2002|"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "id": "120908db",
   "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>model</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2002</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  model\n",
       "0  2005\n",
       "1  2001\n",
       "2  2004\n",
       "3  2006\n",
       "4  2010\n",
       "5  2007\n",
       "6  2003\n",
       "7  2002"
      ]
     },
     "execution_count": 155,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "execute_local(a1b_t1b_decending_speed_laptop())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "id": "6f8f67ec",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Running local tests...\n",
      "a1b_t1b_decending_speed_laptop successfully passed local tests\n",
      "Running remote test...\n",
      "Test was successful. Congratulations!\n"
     ]
    }
   ],
   "source": [
    "am.test_student_function(a1b_t1b_decending_speed_laptop)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c333a6bb",
   "metadata": {},
   "source": [
    "### Task 1c. List all first names of customers in an ascending order based on the number of purchases made by customers with that first name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 211,
   "id": "2a7e5447",
   "metadata": {},
   "outputs": [],
   "source": [
    "def a1b_t1c_customer_number_purchase():\n",
    "    \"\"\" List firstnames of customers in an ascending order \n",
    "    based on the number of purchases made by customers with this firstname.\n",
    "    \"\"\"\n",
    "    #################\n",
    "    ### YOUR CODE ###\n",
    "    #################\n",
    "    #query = \"select customer_id,sum(quantity) as b from sales group by customer_id order by b asc;\"\n",
    "    query = \"select firstname from \\\n",
    "            (select firstname, sum(sales.quantity) as b from customers\\\n",
    "            join sales on customers.customer_id = sales.customer_id\\\n",
    "            group by firstname\\\n",
    "            order by b asc)\"\n",
    "    return query"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c19030aa",
   "metadata": {},
   "source": [
    "You can test your query by running it on our local database via the `execute_local` function and inspecting the results.\n",
    "\n",
    "We expect the following result:\n",
    "\n",
    "|__firstname__|\n",
    "|---|\n",
    "|Maya|\n",
    "|Ann|\n",
    "|John|\n",
    "|Nora|\n",
    "|Ji|"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 212,
   "id": "1a7d379a",
   "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>firstname</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Maya</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Ann</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>John</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Norah</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Ji</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  firstname\n",
       "0      Maya\n",
       "1       Ann\n",
       "2      John\n",
       "3     Norah\n",
       "4        Ji"
      ]
     },
     "execution_count": 212,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "execute_local(a1b_t1c_customer_number_purchase())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 213,
   "id": "bd7c6660",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Running local tests...\n",
      "a1b_t1c_customer_number_purchase successfully passed local tests\n",
      "Running remote test...\n",
      "Test was successful. Congratulations!\n"
     ]
    }
   ],
   "source": [
    "am.test_student_function(a1b_t1c_customer_number_purchase)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "143c5b33",
   "metadata": {},
   "source": [
    "### Task 1d. List all the makers (with only one entry per maker) who make PCs with RAM greater than 1500."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 214,
   "id": "81174ba7",
   "metadata": {},
   "outputs": [],
   "source": [
    "def a1b_t1d_maker_with_ram():\n",
    "    \"\"\" List all the makers (with only one entry per maker) who make PCs with RAM greater than 1500.\n",
    "    \"\"\"\n",
    "    #################\n",
    "    ### YOUR CODE ###\n",
    "    #################   \n",
    "    query = \"select unique(maker) from\\\n",
    "            (select maker,ram from products\\\n",
    "             join pcs on products.model = pcs.model\\\n",
    "             where ram>1500)\"\n",
    "\n",
    "    return query"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a00ce340",
   "metadata": {},
   "source": [
    "You can test your query by running it on our local database via the `execute_local` function and inspecting the results.\n",
    "\n",
    "We expect the following result:\n",
    "\n",
    "|__maker__|\n",
    "|---|\n",
    "|D|\n",
    "|E|"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 226,
   "id": "49336a73",
   "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>maker</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>D</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>E</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  maker\n",
       "0     D\n",
       "1     E"
      ]
     },
     "execution_count": 226,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "execute_local(a1b_t1d_maker_with_ram())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 227,
   "id": "108bbb2d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Running local tests...\n",
      "a1b_t1d_maker_with_ram successfully passed local tests\n",
      "Running remote test...\n",
      "Test was successful. Congratulations!\n"
     ]
    }
   ],
   "source": [
    "am.test_student_function(a1b_t1d_maker_with_ram)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d389d825",
   "metadata": {},
   "source": [
    "### Task 2\n",
    "\n",
    "Write SQL SELECT statements for performing the following queries. Use aggregations and group by.\n",
    "\n",
    "### Task 2a. Find the city and the average amount of money spent by customers in each city. Name the column for the amount 'avg_spend'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 237,
   "id": "f0a8e9a6",
   "metadata": {},
   "outputs": [],
   "source": [
    "def a1b_t2a_avg_money_spent():\n",
    "    \"\"\" Find the city and the average amount of money spent by customers in each city. \n",
    "        Name the column for the amount 'avg_spend'\n",
    "    \"\"\"\n",
    "    #################\n",
    "    ### YOUR CODE ###\n",
    "    #################\n",
    "    query = \"select city, avg(paid) as avg_spend from sales\\\n",
    "    join customers on customers.customer_id = sales.customer_id\\\n",
    "    group by city;\"\n",
    "    \n",
    "    return query"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1ba105b0",
   "metadata": {},
   "source": [
    "You can test your query by running it on our local database via the `execute_local` function and inspecting the results.\n",
    "\n",
    "We expect the following result:\n",
    "\n",
    "|__city__|__avg_spend__|\n",
    "|---|---|\n",
    "|Rotterdam|1199.500000|\n",
    "|Amsterdam|739.000000|\n",
    "|Diemen|1902.600000|\n",
    "|Utrecht|996.333333|"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 238,
   "id": "3b3e3b81",
   "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>city</th>\n",
       "      <th>avg_spend</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Rotterdam</td>\n",
       "      <td>1199.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Amsterdam</td>\n",
       "      <td>739.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Diemen</td>\n",
       "      <td>1902.600000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Utrecht</td>\n",
       "      <td>996.333333</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        city    avg_spend\n",
       "0  Rotterdam  1199.500000\n",
       "1  Amsterdam   739.000000\n",
       "2     Diemen  1902.600000\n",
       "3    Utrecht   996.333333"
      ]
     },
     "execution_count": 238,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "execute_local(a1b_t2a_avg_money_spent())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 239,
   "id": "152064ca",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Running local tests...\n",
      "a1b_t2a_avg_money_spent successfully passed local tests\n",
      "Running remote test...\n",
      "Test was successful. Congratulations!\n"
     ]
    }
   ],
   "source": [
    "am.test_student_function(a1b_t2a_avg_money_spent)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c46ce8bf",
   "metadata": {},
   "source": [
    "###  Task 2b. Find the maximum price for both color and non-color printers. Name the column for the maximum price 'max_price'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 244,
   "id": "794e236c",
   "metadata": {},
   "outputs": [],
   "source": [
    "def a1b_t2b_maximum_price_printer():\n",
    "    \"\"\" Find the maximum price for both a color and non-color printers.\n",
    "        Name the column for the maximum price 'max_price'.\n",
    "    \"\"\"\n",
    "    #################\n",
    "    ### YOUR CODE ###\n",
    "    #################\n",
    "    query = \"select color, max(price) as max_price from printers group by color\"\n",
    "    \n",
    "    return query"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1fc705f7",
   "metadata": {},
   "source": [
    "You can test your query by running it on our local database via the `execute_local` function and inspecting the results.\n",
    "\n",
    "We expect the following result:\n",
    "\n",
    "|__color__|__max_price__|\n",
    "|---|---|\n",
    "|TRUE|899.0|\n",
    "|FALSE|239.0|"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 245,
   "id": "526f0b68",
   "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>color</th>\n",
       "      <th>max_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>TRUE</td>\n",
       "      <td>899.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>FALSE</td>\n",
       "      <td>239.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   color  max_price\n",
       "0   TRUE      899.0\n",
       "1  FALSE      239.0"
      ]
     },
     "execution_count": 245,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "execute_local(a1b_t2b_maximum_price_printer())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 246,
   "id": "c0018a85",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Running local tests...\n",
      "a1b_t2b_maximum_price_printer successfully passed local tests\n",
      "Running remote test...\n",
      "Test was successful. Congratulations!\n"
     ]
    }
   ],
   "source": [
    "am.test_student_function(a1b_t2b_maximum_price_printer)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f5381f43-bcd5-422e-8a39-87816ba89cec",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}