Homework6.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": "3c515e758330b4612d019bf8cc822c46",
     "grade": false,
     "grade_id": "cell-7f51021c2127a4a3",
     "locked": true,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "source": [
    "# Instructions \n",
    "For every exercise, simply write your sql statement in a variable called `sql_statement`. `output columns` tells you which columns to include in your select statement. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "a7447b33af5eaccfe88b03bc28a5588d",
     "grade": false,
     "grade_id": "cell-17a56c170dc1df96",
     "locked": true,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "# Setup\n",
    "from IPython.display import display, HTML\n",
    "import pandas as pd\n",
    "import sqlite3\n",
    "conn = sqlite3.connect(\"homework6.db\")\n",
    "cur = conn.cursor()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "6e75b6cea97e2d364a75b73df6fe8014",
     "grade": false,
     "grade_id": "cell-2a0c2d8ed5d8900a",
     "locked": true,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "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>cust_id</th>\n",
       "      <th>cust_name</th>\n",
       "      <th>cust_address</th>\n",
       "      <th>cust_city</th>\n",
       "      <th>cust_state</th>\n",
       "      <th>cust_zip</th>\n",
       "      <th>cust_country</th>\n",
       "      <th>cust_contact</th>\n",
       "      <th>cust_email</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10001</td>\n",
       "      <td>Coyote Inc.</td>\n",
       "      <td>200 Maple Lane</td>\n",
       "      <td>Detroit</td>\n",
       "      <td>MI</td>\n",
       "      <td>44444</td>\n",
       "      <td>USA</td>\n",
       "      <td>Y Lee</td>\n",
       "      <td>ylee@coyote.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10002</td>\n",
       "      <td>Mouse House</td>\n",
       "      <td>333 Fromage Lane</td>\n",
       "      <td>Columbus</td>\n",
       "      <td>OH</td>\n",
       "      <td>43333</td>\n",
       "      <td>USA</td>\n",
       "      <td>Jerry Mouse</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10003</td>\n",
       "      <td>Wascals</td>\n",
       "      <td>1 Sunny Place</td>\n",
       "      <td>Muncie</td>\n",
       "      <td>IN</td>\n",
       "      <td>42222</td>\n",
       "      <td>USA</td>\n",
       "      <td>Jim Jones</td>\n",
       "      <td>rabbit@wascally.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10004</td>\n",
       "      <td>Yosemite Place</td>\n",
       "      <td>829 Riverside Drive</td>\n",
       "      <td>Phoenix</td>\n",
       "      <td>AZ</td>\n",
       "      <td>88888</td>\n",
       "      <td>USA</td>\n",
       "      <td>Y Sam</td>\n",
       "      <td>sam@yosemite.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10005</td>\n",
       "      <td>E Fudd</td>\n",
       "      <td>4545 53rd Street</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>54545</td>\n",
       "      <td>USA</td>\n",
       "      <td>E Fudd</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   cust_id       cust_name         cust_address cust_city cust_state cust_zip  \\\n",
       "0    10001     Coyote Inc.       200 Maple Lane   Detroit         MI    44444   \n",
       "1    10002     Mouse House     333 Fromage Lane  Columbus         OH    43333   \n",
       "2    10003         Wascals        1 Sunny Place    Muncie         IN    42222   \n",
       "3    10004  Yosemite Place  829 Riverside Drive   Phoenix         AZ    88888   \n",
       "4    10005          E Fudd     4545 53rd Street   Chicago         IL    54545   \n",
       "\n",
       "  cust_country cust_contact           cust_email  \n",
       "0          USA        Y Lee      ylee@coyote.com  \n",
       "1          USA  Jerry Mouse                 None  \n",
       "2          USA    Jim Jones  rabbit@wascally.com  \n",
       "3          USA        Y Sam     sam@yosemite.com  \n",
       "4          USA       E Fudd                 None  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sql_statement = \"select * from customers;\"\n",
    "df = pd.read_sql_query(sql_statement, conn)\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "0b3d3b9129908c0a0a923a96e29fb87e",
     "grade": false,
     "grade_id": "cell-a7be26c54d4fb759",
     "locked": true,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "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>order_num</th>\n",
       "      <th>order_date</th>\n",
       "      <th>cust_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20005</td>\n",
       "      <td>2005-09-01</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20006</td>\n",
       "      <td>2005-09-12</td>\n",
       "      <td>10003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20007</td>\n",
       "      <td>2005-09-30</td>\n",
       "      <td>10004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20008</td>\n",
       "      <td>2005-10-03</td>\n",
       "      <td>10005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20009</td>\n",
       "      <td>2005-10-08</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_num  order_date  cust_id\n",
       "0      20005  2005-09-01    10001\n",
       "1      20006  2005-09-12    10003\n",
       "2      20007  2005-09-30    10004\n",
       "3      20008  2005-10-03    10005\n",
       "4      20009  2005-10-08    10001"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sql_statement = \"select * from orders;\"\n",
    "df = pd.read_sql_query(sql_statement, conn)\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "6908d16ca9765cabb730b4a65b2b9f4b",
     "grade": false,
     "grade_id": "cell-ecf3e72e767cd1c0",
     "locked": true,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "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>vend_id</th>\n",
       "      <th>vend_name</th>\n",
       "      <th>vend_address</th>\n",
       "      <th>vend_city</th>\n",
       "      <th>vend_state</th>\n",
       "      <th>vend_zip</th>\n",
       "      <th>vend_country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>Anvils R Us</td>\n",
       "      <td>123 Main Street</td>\n",
       "      <td>Southfield</td>\n",
       "      <td>MI</td>\n",
       "      <td>48075</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>LT Supplies</td>\n",
       "      <td>500 Park Street</td>\n",
       "      <td>Anytown</td>\n",
       "      <td>OH</td>\n",
       "      <td>44333</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>ACME</td>\n",
       "      <td>555 High Street</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>CA</td>\n",
       "      <td>90046</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>Furball Inc.</td>\n",
       "      <td>1000 5th Avenue</td>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "      <td>11111</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>Jet Set</td>\n",
       "      <td>42 Galaxy Road</td>\n",
       "      <td>London</td>\n",
       "      <td>None</td>\n",
       "      <td>N16 6PS</td>\n",
       "      <td>England</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1006</td>\n",
       "      <td>Jouets Et Ours</td>\n",
       "      <td>1 Rue Amusement</td>\n",
       "      <td>Paris</td>\n",
       "      <td>None</td>\n",
       "      <td>45678</td>\n",
       "      <td>France</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   vend_id       vend_name     vend_address    vend_city vend_state vend_zip  \\\n",
       "0     1001     Anvils R Us  123 Main Street   Southfield         MI    48075   \n",
       "1     1002     LT Supplies  500 Park Street      Anytown         OH    44333   \n",
       "2     1003            ACME  555 High Street  Los Angeles         CA    90046   \n",
       "3     1004    Furball Inc.  1000 5th Avenue     New York         NY    11111   \n",
       "4     1005         Jet Set   42 Galaxy Road       London       None  N16 6PS   \n",
       "5     1006  Jouets Et Ours  1 Rue Amusement        Paris       None    45678   \n",
       "\n",
       "  vend_country  \n",
       "0          USA  \n",
       "1          USA  \n",
       "2          USA  \n",
       "3          USA  \n",
       "4      England  \n",
       "5       France  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sql_statement = \"select * from vendors;\"\n",
    "df = pd.read_sql_query(sql_statement, conn)\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "2e1045e96bb2a16dfbd1c638c2ed4fcd",
     "grade": false,
     "grade_id": "cell-3fd260a71dcd1b77",
     "locked": true,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "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>prod_id</th>\n",
       "      <th>vend_id</th>\n",
       "      <th>prod_name</th>\n",
       "      <th>prod_price</th>\n",
       "      <th>prod_desc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ANV01</td>\n",
       "      <td>1001</td>\n",
       "      <td>.5 ton anvil</td>\n",
       "      <td>5.99</td>\n",
       "      <td>.5 ton anvil, black, complete with handy hook</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ANV02</td>\n",
       "      <td>1001</td>\n",
       "      <td>1 ton anvil</td>\n",
       "      <td>9.99</td>\n",
       "      <td>1 ton anvil, black, complete with handy hook a...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ANV03</td>\n",
       "      <td>1001</td>\n",
       "      <td>2 ton anvil</td>\n",
       "      <td>14.99</td>\n",
       "      <td>2 ton anvil, black, complete with handy hook a...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>OL1</td>\n",
       "      <td>1002</td>\n",
       "      <td>Oil can</td>\n",
       "      <td>8.99</td>\n",
       "      <td>Oil can, red</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>FU1</td>\n",
       "      <td>1002</td>\n",
       "      <td>Fuses</td>\n",
       "      <td>3.42</td>\n",
       "      <td>1 dozen, extra long</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>SLING</td>\n",
       "      <td>1003</td>\n",
       "      <td>Sling</td>\n",
       "      <td>4.49</td>\n",
       "      <td>Sling, one size fits all</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>TNT1</td>\n",
       "      <td>1003</td>\n",
       "      <td>TNT (1 stick)</td>\n",
       "      <td>2.50</td>\n",
       "      <td>TNT, red, single stick</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>TNT2</td>\n",
       "      <td>1003</td>\n",
       "      <td>TNT (5 sticks)</td>\n",
       "      <td>10.00</td>\n",
       "      <td>TNT, red, pack of 10 sticks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>FB</td>\n",
       "      <td>1003</td>\n",
       "      <td>Bird seed</td>\n",
       "      <td>10.00</td>\n",
       "      <td>Large bag (suitable for road runners)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>FC</td>\n",
       "      <td>1003</td>\n",
       "      <td>Carrots</td>\n",
       "      <td>2.50</td>\n",
       "      <td>Carrots (rabbit hunting season only)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>SAFE</td>\n",
       "      <td>1003</td>\n",
       "      <td>Safe</td>\n",
       "      <td>50.00</td>\n",
       "      <td>Safe with combination lock</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>DTNTR</td>\n",
       "      <td>1003</td>\n",
       "      <td>Detonator</td>\n",
       "      <td>13.00</td>\n",
       "      <td>Detonator (plunger powered), fuses not included</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>JP1000</td>\n",
       "      <td>1005</td>\n",
       "      <td>JetPack 1000</td>\n",
       "      <td>35.00</td>\n",
       "      <td>JetPack 1000, intended for single use</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>JP2000</td>\n",
       "      <td>1005</td>\n",
       "      <td>JetPack 2000</td>\n",
       "      <td>55.00</td>\n",
       "      <td>JetPack 2000, multi-use</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   prod_id  vend_id       prod_name  prod_price  \\\n",
       "0    ANV01     1001    .5 ton anvil        5.99   \n",
       "1    ANV02     1001     1 ton anvil        9.99   \n",
       "2    ANV03     1001     2 ton anvil       14.99   \n",
       "3      OL1     1002         Oil can        8.99   \n",
       "4      FU1     1002           Fuses        3.42   \n",
       "5    SLING     1003           Sling        4.49   \n",
       "6     TNT1     1003   TNT (1 stick)        2.50   \n",
       "7     TNT2     1003  TNT (5 sticks)       10.00   \n",
       "8       FB     1003       Bird seed       10.00   \n",
       "9       FC     1003         Carrots        2.50   \n",
       "10    SAFE     1003            Safe       50.00   \n",
       "11   DTNTR     1003       Detonator       13.00   \n",
       "12  JP1000     1005    JetPack 1000       35.00   \n",
       "13  JP2000     1005    JetPack 2000       55.00   \n",
       "\n",
       "                                            prod_desc  \n",
       "0       .5 ton anvil, black, complete with handy hook  \n",
       "1   1 ton anvil, black, complete with handy hook a...  \n",
       "2   2 ton anvil, black, complete with handy hook a...  \n",
       "3                                        Oil can, red  \n",
       "4                                 1 dozen, extra long  \n",
       "5                            Sling, one size fits all  \n",
       "6                              TNT, red, single stick  \n",
       "7                         TNT, red, pack of 10 sticks  \n",
       "8               Large bag (suitable for road runners)  \n",
       "9                Carrots (rabbit hunting season only)  \n",
       "10                         Safe with combination lock  \n",
       "11    Detonator (plunger powered), fuses not included  \n",
       "12              JetPack 1000, intended for single use  \n",
       "13                            JetPack 2000, multi-use  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sql_statement = \"select * from products;\"\n",
    "df = pd.read_sql_query(sql_statement, conn)\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "4ab333535688ff21b4fbf06385fb5695",
     "grade": false,
     "grade_id": "cell-a7ccc14efeb20556",
     "locked": true,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "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>order_num</th>\n",
       "      <th>order_item</th>\n",
       "      <th>prod_id</th>\n",
       "      <th>quantity</th>\n",
       "      <th>item_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20005</td>\n",
       "      <td>1</td>\n",
       "      <td>ANV01</td>\n",
       "      <td>10</td>\n",
       "      <td>5.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20005</td>\n",
       "      <td>2</td>\n",
       "      <td>ANV02</td>\n",
       "      <td>3</td>\n",
       "      <td>9.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20005</td>\n",
       "      <td>3</td>\n",
       "      <td>TNT2</td>\n",
       "      <td>5</td>\n",
       "      <td>10.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20005</td>\n",
       "      <td>4</td>\n",
       "      <td>FB</td>\n",
       "      <td>1</td>\n",
       "      <td>10.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20006</td>\n",
       "      <td>1</td>\n",
       "      <td>JP2000</td>\n",
       "      <td>1</td>\n",
       "      <td>55.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>20007</td>\n",
       "      <td>1</td>\n",
       "      <td>TNT2</td>\n",
       "      <td>100</td>\n",
       "      <td>10.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>20008</td>\n",
       "      <td>1</td>\n",
       "      <td>FC</td>\n",
       "      <td>50</td>\n",
       "      <td>2.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>20009</td>\n",
       "      <td>1</td>\n",
       "      <td>FB</td>\n",
       "      <td>1</td>\n",
       "      <td>10.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>20009</td>\n",
       "      <td>2</td>\n",
       "      <td>OL1</td>\n",
       "      <td>1</td>\n",
       "      <td>8.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>20009</td>\n",
       "      <td>3</td>\n",
       "      <td>SLING</td>\n",
       "      <td>1</td>\n",
       "      <td>4.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>20009</td>\n",
       "      <td>4</td>\n",
       "      <td>ANV03</td>\n",
       "      <td>1</td>\n",
       "      <td>14.99</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    order_num  order_item prod_id  quantity  item_price\n",
       "0       20005           1   ANV01        10        5.99\n",
       "1       20005           2   ANV02         3        9.99\n",
       "2       20005           3    TNT2         5       10.00\n",
       "3       20005           4      FB         1       10.00\n",
       "4       20006           1  JP2000         1       55.00\n",
       "5       20007           1    TNT2       100       10.00\n",
       "6       20008           1      FC        50        2.50\n",
       "7       20009           1      FB         1       10.00\n",
       "8       20009           2     OL1         1        8.99\n",
       "9       20009           3   SLING         1        4.49\n",
       "10      20009           4   ANV03         1       14.99"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sql_statement = \"select * from orderitems;\"\n",
    "df = pd.read_sql_query(sql_statement, conn)\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "06bb7109ab245db9d3b66ed9d9e1af82",
     "grade": false,
     "grade_id": "cell-32bb4aed2805f864",
     "locked": true,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "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>note_id</th>\n",
       "      <th>prod_id</th>\n",
       "      <th>note_date</th>\n",
       "      <th>note_text</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>TNT2</td>\n",
       "      <td>2005-08-17</td>\n",
       "      <td>Customer complaint:\\nSticks not individually w...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>102</td>\n",
       "      <td>OL1</td>\n",
       "      <td>2005-08-18</td>\n",
       "      <td>Can shipped full, refills not available.\\nNeed...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>103</td>\n",
       "      <td>SAFE</td>\n",
       "      <td>2005-08-18</td>\n",
       "      <td>Safe is combination locked, combination not pr...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>104</td>\n",
       "      <td>FC</td>\n",
       "      <td>2005-08-19</td>\n",
       "      <td>Quantity varies, sold by the sack load.\\nAll g...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>105</td>\n",
       "      <td>TNT2</td>\n",
       "      <td>2005-08-20</td>\n",
       "      <td>Included fuses are short and have been known t...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>106</td>\n",
       "      <td>TNT2</td>\n",
       "      <td>2005-08-22</td>\n",
       "      <td>Matches not included, recommend purchase of ma...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>107</td>\n",
       "      <td>SAFE</td>\n",
       "      <td>2005-08-23</td>\n",
       "      <td>Please note that no returns will be accepted i...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>108</td>\n",
       "      <td>ANV01</td>\n",
       "      <td>2005-08-25</td>\n",
       "      <td>Multiple customer returns, anvils failing to d...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>109</td>\n",
       "      <td>ANV03</td>\n",
       "      <td>2005-09-01</td>\n",
       "      <td>Item is extremely heavy. Designed for dropping...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>110</td>\n",
       "      <td>FC</td>\n",
       "      <td>2005-09-01</td>\n",
       "      <td>Customer complaint: rabbit has been able to de...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>111</td>\n",
       "      <td>SLING</td>\n",
       "      <td>2005-09-02</td>\n",
       "      <td>Shipped unassembled, requires common tools (in...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>112</td>\n",
       "      <td>SAFE</td>\n",
       "      <td>2005-09-02</td>\n",
       "      <td>Customer complaint:\\nCircular hole in safe flo...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>113</td>\n",
       "      <td>ANV01</td>\n",
       "      <td>2005-09-05</td>\n",
       "      <td>Customer complaint:\\nNot heavy enough to gener...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>114</td>\n",
       "      <td>SAFE</td>\n",
       "      <td>2005-09-07</td>\n",
       "      <td>Call from individual trapped in safe plummetin...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    note_id prod_id   note_date  \\\n",
       "0       101    TNT2  2005-08-17   \n",
       "1       102     OL1  2005-08-18   \n",
       "2       103    SAFE  2005-08-18   \n",
       "3       104      FC  2005-08-19   \n",
       "4       105    TNT2  2005-08-20   \n",
       "5       106    TNT2  2005-08-22   \n",
       "6       107    SAFE  2005-08-23   \n",
       "7       108   ANV01  2005-08-25   \n",
       "8       109   ANV03  2005-09-01   \n",
       "9       110      FC  2005-09-01   \n",
       "10      111   SLING  2005-09-02   \n",
       "11      112    SAFE  2005-09-02   \n",
       "12      113   ANV01  2005-09-05   \n",
       "13      114    SAFE  2005-09-07   \n",
       "\n",
       "                                            note_text  \n",
       "0   Customer complaint:\\nSticks not individually w...  \n",
       "1   Can shipped full, refills not available.\\nNeed...  \n",
       "2   Safe is combination locked, combination not pr...  \n",
       "3   Quantity varies, sold by the sack load.\\nAll g...  \n",
       "4   Included fuses are short and have been known t...  \n",
       "5   Matches not included, recommend purchase of ma...  \n",
       "6   Please note that no returns will be accepted i...  \n",
       "7   Multiple customer returns, anvils failing to d...  \n",
       "8   Item is extremely heavy. Designed for dropping...  \n",
       "9   Customer complaint: rabbit has been able to de...  \n",
       "10  Shipped unassembled, requires common tools (in...  \n",
       "11  Customer complaint:\\nCircular hole in safe flo...  \n",
       "12  Customer complaint:\\nNot heavy enough to gener...  \n",
       "13  Call from individual trapped in safe plummetin...  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sql_statement = \"select * from productnotes;\"\n",
    "df = pd.read_sql_query(sql_statement, conn)\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "c5eec4658de439c4e917eff8fd6ab28c",
     "grade": false,
     "grade_id": "cell-2a0ca9880d79016e",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def ex1():\n",
    "    # Write an SQL statement that SELECTs all rows from the `customers` table\n",
    "    # output columns: cust_name, cust_email\n",
    "\n",
    "    # YOUR CODE HERE\n",
    "\n",
    "    sql_statement = \"select cust_name, cust_email from customers\"\n",
    "    df = pd.read_sql_query(sql_statement, conn)\n",
    "    display(df)\n",
    "    return sql_statement\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "ea2e2d9c33f5f1158843936004e96148",
     "grade": true,
     "grade_id": "cell-1fe431c57dc91fe0",
     "locked": true,
     "points": 1,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "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>cust_name</th>\n",
       "      <th>cust_email</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Coyote Inc.</td>\n",
       "      <td>ylee@coyote.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mouse House</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Wascals</td>\n",
       "      <td>rabbit@wascally.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Yosemite Place</td>\n",
       "      <td>sam@yosemite.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>E Fudd</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        cust_name           cust_email\n",
       "0     Coyote Inc.      ylee@coyote.com\n",
       "1     Mouse House                 None\n",
       "2         Wascals  rabbit@wascally.com\n",
       "3  Yosemite Place     sam@yosemite.com\n",
       "4          E Fudd                 None"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sql_statement = ex1()\n",
    "data = pd.read_csv(\"ex1.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": 11,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "4e2fe19c0923fed9ff0168a0c1e30425",
     "grade": false,
     "grade_id": "cell-a8176ef4a0acd1cb",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def ex2():\n",
    "    # Write an SQL statement that SELECTs all rows from the `products` table\n",
    "    # output columns: vend_id\n",
    "\n",
    "    # YOUR CODE HERE\n",
    "    sql_statement = \"select vend_id from products\"\n",
    "    df = pd.read_sql_query(sql_statement, conn)\n",
    "    display(df)\n",
    "    return sql_statement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "5cc6dd74f34c9dd10306bc5cf64d7347",
     "grade": true,
     "grade_id": "cell-079430acc428fad0",
     "locked": true,
     "points": 1,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "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>vend_id</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>1001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1005</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    vend_id\n",
       "0      1001\n",
       "1      1001\n",
       "2      1001\n",
       "3      1002\n",
       "4      1002\n",
       "5      1003\n",
       "6      1003\n",
       "7      1003\n",
       "8      1003\n",
       "9      1003\n",
       "10     1003\n",
       "11     1003\n",
       "12     1005\n",
       "13     1005"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sql_statement = ex2()\n",
    "data = pd.read_csv(\"ex2.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": 13,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "8261f20a0dd3a10bb9c89a8d4d46e707",
     "grade": false,
     "grade_id": "cell-21158581a7c25b2c",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def ex3():\n",
    "    # Write an SQL statement that SELECTs distinct rows for vend_id from the `products` table\n",
    "    # output columns: vend_id\n",
    "\n",
    "    # YOUR CODE HERE\n",
    "    sql_statement = \"SELECT DISTINCT vend_id FROM products\"\n",
    "    df = pd.read_sql_query(sql_statement, conn)\n",
    "    display(df)\n",
    "    return sql_statement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "6a1ff8a027b591ded5be9fdea7f2c718",
     "grade": true,
     "grade_id": "cell-b95a001073bdacb2",
     "locked": true,
     "points": 1,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "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>vend_id</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>1005</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   vend_id\n",
       "0     1001\n",
       "1     1002\n",
       "2     1003\n",
       "3     1005"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sql_statement = ex3()\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": 15,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "a480177806c5a2276df7ebb48c963a23",
     "grade": false,
     "grade_id": "cell-a3f50355c33d670f",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def ex4():\n",
    "    # Write an SQL statement that SELECTs the first five rows from the `products` table\n",
    "    # output columns: prod_name\n",
    "\n",
    "    # YOUR CODE HERE\n",
    "    \n",
    "    sql_statement = \"SELECT prod_name FROM products LIMIT 5\"\n",
    "    df = pd.read_sql_query(sql_statement, conn)\n",
    "    display(df)\n",
    "    return sql_statement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "98b00513d1112e955dbf8b730aa7e592",
     "grade": true,
     "grade_id": "cell-3f3364d8202f3f82",
     "locked": true,
     "points": 1,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "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>prod_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>.5 ton anvil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1 ton anvil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2 ton anvil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Oil can</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Fuses</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      prod_name\n",
       "0  .5 ton anvil\n",
       "1   1 ton anvil\n",
       "2   2 ton anvil\n",
       "3       Oil can\n",
       "4         Fuses"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sql_statement = ex4()\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"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "b85fb1fad21ee604afea25c851637f8f",
     "grade": false,
     "grade_id": "cell-7f72469e3761a45b",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def ex5():\n",
    "    # Write an SQL statement that SELECTs 4 rows starting from row 3 from `products` table\n",
    "    # output columns: prod_name\n",
    "\n",
    "    # YOUR CODE HERE\n",
    "\n",
    "    sql_statement = \"SELECT prod_name FROM products LIMIT 4 OFFSET 3\"\n",
    "    df = pd.read_sql_query(sql_statement, conn)\n",
    "    display(df)\n",
    "    return sql_statement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "289beb074320d6a4b509fe6323ae7683",
     "grade": true,
     "grade_id": "cell-1c05866a01ea7c1e",
     "locked": true,
     "points": 1,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "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>prod_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Oil can</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Fuses</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Sling</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>TNT (1 stick)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       prod_name\n",
       "0        Oil can\n",
       "1          Fuses\n",
       "2          Sling\n",
       "3  TNT (1 stick)"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sql_statement = ex5()\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"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "db7618b98ceae07a09a5482d93509ccf",
     "grade": false,
     "grade_id": "cell-fa1c5c26029a0e36",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def ex6():\n",
    "    # Write an SQL statement that SELECTs all rows from `products` table and sorts by prod_name\n",
    "    # output columns: prod_name\n",
    "\n",
    "    # YOUR CODE HERE\n",
    "    sql_statement = \"SELECT prod_name FROM products ORDER BY prod_name\"\n",
    "    df = pd.read_sql_query(sql_statement, conn)\n",
    "    display(df)\n",
    "    return sql_statement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "e50f70eaac50947a1eb0aefa09558ef9",
     "grade": true,
     "grade_id": "cell-80b68ef15116bb6b",
     "locked": true,
     "points": 1,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "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>prod_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>.5 ton anvil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1 ton anvil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2 ton anvil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Bird seed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Carrots</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Detonator</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Fuses</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>JetPack 1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>JetPack 2000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Oil can</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Safe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Sling</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>TNT (1 stick)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>TNT (5 sticks)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         prod_name\n",
       "0     .5 ton anvil\n",
       "1      1 ton anvil\n",
       "2      2 ton anvil\n",
       "3        Bird seed\n",
       "4          Carrots\n",
       "5        Detonator\n",
       "6            Fuses\n",
       "7     JetPack 1000\n",
       "8     JetPack 2000\n",
       "9          Oil can\n",
       "10            Safe\n",
       "11           Sling\n",
       "12   TNT (1 stick)\n",
       "13  TNT (5 sticks)"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sql_statement = ex6()\n",
    "data = pd.read_csv(\"ex6.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": 21,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "928ec408ed02f20c6de28a27caad5d59",
     "grade": false,
     "grade_id": "cell-e56f4ce05a7da66f",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def ex7():\n",
    "    # Write an SQL statement that SELECTs all rows from `products` table and sorts by prod_price and then prod_name \n",
    "    # output columns: prod_id, prod_price, prod_name\n",
    "\n",
    "    # YOUR CODE HERE\n",
    "    sql_statement = \"SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price , prod_name\"\n",
    "    df = pd.read_sql_query(sql_statement, conn)\n",
    "    display(df)\n",
    "    return sql_statement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "d6dbf8e8c41633691b62a0132d31b4f7",
     "grade": true,
     "grade_id": "cell-104c39ebf54b9a15",
     "locked": true,
     "points": 1,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "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>prod_id</th>\n",
       "      <th>prod_price</th>\n",
       "      <th>prod_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>FC</td>\n",
       "      <td>2.50</td>\n",
       "      <td>Carrots</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TNT1</td>\n",
       "      <td>2.50</td>\n",
       "      <td>TNT (1 stick)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>FU1</td>\n",
       "      <td>3.42</td>\n",
       "      <td>Fuses</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>SLING</td>\n",
       "      <td>4.49</td>\n",
       "      <td>Sling</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ANV01</td>\n",
       "      <td>5.99</td>\n",
       "      <td>.5 ton anvil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>OL1</td>\n",
       "      <td>8.99</td>\n",
       "      <td>Oil can</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>ANV02</td>\n",
       "      <td>9.99</td>\n",
       "      <td>1 ton anvil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>FB</td>\n",
       "      <td>10.00</td>\n",
       "      <td>Bird seed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>TNT2</td>\n",
       "      <td>10.00</td>\n",
       "      <td>TNT (5 sticks)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>DTNTR</td>\n",
       "      <td>13.00</td>\n",
       "      <td>Detonator</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>ANV03</td>\n",
       "      <td>14.99</td>\n",
       "      <td>2 ton anvil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>JP1000</td>\n",
       "      <td>35.00</td>\n",
       "      <td>JetPack 1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>SAFE</td>\n",
       "      <td>50.00</td>\n",
       "      <td>Safe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>JP2000</td>\n",
       "      <td>55.00</td>\n",
       "      <td>JetPack 2000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   prod_id  prod_price       prod_name\n",
       "0       FC        2.50         Carrots\n",
       "1     TNT1        2.50   TNT (1 stick)\n",
       "2      FU1        3.42           Fuses\n",
       "3    SLING        4.49           Sling\n",
       "4    ANV01        5.99    .5 ton anvil\n",
       "5      OL1        8.99         Oil can\n",
       "6    ANV02        9.99     1 ton anvil\n",
       "7       FB       10.00       Bird seed\n",
       "8     TNT2       10.00  TNT (5 sticks)\n",
       "9    DTNTR       13.00       Detonator\n",
       "10   ANV03       14.99     2 ton anvil\n",
       "11  JP1000       35.00    JetPack 1000\n",
       "12    SAFE       50.00            Safe\n",
       "13  JP2000       55.00    JetPack 2000"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sql_statement = ex7()\n",
    "data = pd.read_csv(\"ex7.csv\")\n",
    "df = pd.read_sql_query(sql_statement, conn)\n",
    "assert df.equals(data) == True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "ce8000713ef7e2c355ac90dab64111b2",
     "grade": false,
     "grade_id": "cell-ecd9ce2d2b771e14",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def ex8():\n",
    "    # Write an SQL statement that SELECTs all rows from `products` table and sorts by prod_price (descending order)\n",
    "    # and then prod_name \n",
    "    # output columns: prod_id, prod_price, prod_name\n",
    "\n",
    "    # YOUR CODE HERE\n",
    "    sql_statement = \"SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC, prod_name \"\n",
    "    df = pd.read_sql_query(sql_statement, conn)\n",
    "    display(df)\n",
    "    return sql_statement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "7c723c1a802c3bac64e96b7be12e0f66",
     "grade": true,
     "grade_id": "cell-168678a4644278f7",
     "locked": true,
     "points": 1,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "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>prod_id</th>\n",
       "      <th>prod_price</th>\n",
       "      <th>prod_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>JP2000</td>\n",
       "      <td>55.00</td>\n",
       "      <td>JetPack 2000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>SAFE</td>\n",
       "      <td>50.00</td>\n",
       "      <td>Safe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>JP1000</td>\n",
       "      <td>35.00</td>\n",
       "      <td>JetPack 1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ANV03</td>\n",
       "      <td>14.99</td>\n",
       "      <td>2 ton anvil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DTNTR</td>\n",
       "      <td>13.00</td>\n",
       "      <td>Detonator</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>FB</td>\n",
       "      <td>10.00</td>\n",
       "      <td>Bird seed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>TNT2</td>\n",
       "      <td>10.00</td>\n",
       "      <td>TNT (5 sticks)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>ANV02</td>\n",
       "      <td>9.99</td>\n",
       "      <td>1 ton anvil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>OL1</td>\n",
       "      <td>8.99</td>\n",
       "      <td>Oil can</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>ANV01</td>\n",
       "      <td>5.99</td>\n",
       "      <td>.5 ton anvil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>SLING</td>\n",
       "      <td>4.49</td>\n",
       "      <td>Sling</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>FU1</td>\n",
       "      <td>3.42</td>\n",
       "      <td>Fuses</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>FC</td>\n",
       "      <td>2.50</td>\n",
       "      <td>Carrots</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>TNT1</td>\n",
       "      <td>2.50</td>\n",
       "      <td>TNT (1 stick)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   prod_id  prod_price       prod_name\n",
       "0   JP2000       55.00    JetPack 2000\n",
       "1     SAFE       50.00            Safe\n",
       "2   JP1000       35.00    JetPack 1000\n",
       "3    ANV03       14.99     2 ton anvil\n",
       "4    DTNTR       13.00       Detonator\n",
       "5       FB       10.00       Bird seed\n",
       "6     TNT2       10.00  TNT (5 sticks)\n",
       "7    ANV02        9.99     1 ton anvil\n",
       "8      OL1        8.99         Oil can\n",
       "9    ANV01        5.99    .5 ton anvil\n",
       "10   SLING        4.49           Sling\n",
       "11     FU1        3.42           Fuses\n",
       "12      FC        2.50         Carrots\n",
       "13    TNT1        2.50   TNT (1 stick)"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sql_statement = ex8()\n",
    "data = pd.read_csv(\"ex8.csv\")\n",
    "df = pd.read_sql_query(sql_statement, conn)\n",
    "assert df.equals(data) == True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "4dca77f3cfc44302b8ce70b89e1d3b04",
     "grade": false,
     "grade_id": "cell-8d81798c71ef1fa8",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def ex9():\n",
    "    # Write an SQL statement that SELECTs all rows from `products` table where the price of product is 2.50\n",
    "    # output columns: prod_id, prod_price, prod_name\n",
    "\n",
    "    # YOUR CODE HERE\n",
    "    sql_statement = \"SELECT   prod_id, prod_price, prod_name FROM products WHERE prod_price LIKE 2.50\"\n",
    "    df = pd.read_sql_query(sql_statement, conn)\n",
    "    display(df)\n",
    "    return sql_statement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "2960353dd5ad2b87d405251bb01a4fc5",
     "grade": true,
     "grade_id": "cell-7b3443eb610c0ff8",
     "locked": true,
     "points": 1,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "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>prod_id</th>\n",
       "      <th>prod_price</th>\n",
       "      <th>prod_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>TNT1</td>\n",
       "      <td>2.5</td>\n",
       "      <td>TNT (1 stick)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>FC</td>\n",
       "      <td>2.5</td>\n",
       "      <td>Carrots</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  prod_id  prod_price      prod_name\n",
       "0    TNT1         2.5  TNT (1 stick)\n",
       "1      FC         2.5        Carrots"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sql_statement = ex9()\n",
    "data = pd.read_csv(\"ex9.csv\")\n",
    "df = pd.read_sql_query(sql_statement, conn)\n",
    "assert df.equals(data) == True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "b8218324f4de69fec85452dd618ea1ea",
     "grade": false,
     "grade_id": "cell-430f263a819eca75",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def ex10():\n",
    "    # Write an SQL statement that SELECTs all rows from `products` table where the name of product is Oil can\n",
    "    # output columns: prod_id, prod_price, prod_name\n",
    "\n",
    "    # YOUR CODE HERE\n",
    "    sql_statement = \"SELECT   prod_id, prod_price, prod_name FROM products WHERE prod_name LIKE 'Oil can'\"\n",
    "    df = pd.read_sql_query(sql_statement, conn)\n",
    "    display(df)\n",
    "    return sql_statement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "9f019248df5682870100e670787fe168",
     "grade": true,
     "grade_id": "cell-9f8f47d37aa4acd9",
     "locked": true,
     "points": 1,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "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>prod_id</th>\n",
       "      <th>prod_price</th>\n",
       "      <th>prod_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>OL1</td>\n",
       "      <td>8.99</td>\n",
       "      <td>Oil can</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  prod_id  prod_price prod_name\n",
       "0     OL1        8.99   Oil can"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sql_statement = ex10()\n",
    "data = pd.read_csv(\"ex10.csv\")\n",
    "df = pd.read_sql_query(sql_statement, conn)\n",
    "assert df.equals(data) == True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "341299b9e5d4918e1c2c213970092e53",
     "grade": false,
     "grade_id": "cell-8824150a6590d820",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def ex11():\n",
    "    # Write an SQL statement that SELECTs all rows from `products` table where the price of product is \n",
    "    # less than or equal to 10\n",
    "    # output columns: prod_id, prod_price, prod_name\n",
    "\n",
    "    # YOUR CODE HERE\n",
    "    sql_statement = \"SELECT   prod_id, prod_price, prod_name FROM products WHERE prod_price  <=10\"\n",
    "    df = pd.read_sql_query(sql_statement, conn)\n",
    "    display(df)\n",
    "    return sql_statement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "08e4d8a5cacb1b6b601d8151bc214a09",
     "grade": true,
     "grade_id": "cell-1a77a2e114245d4d",
     "locked": true,
     "points": 1,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "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>prod_id</th>\n",
       "      <th>prod_price</th>\n",
       "      <th>prod_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ANV01</td>\n",
       "      <td>5.99</td>\n",
       "      <td>.5 ton anvil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ANV02</td>\n",
       "      <td>9.99</td>\n",
       "      <td>1 ton anvil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>OL1</td>\n",
       "      <td>8.99</td>\n",
       "      <td>Oil can</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>FU1</td>\n",
       "      <td>3.42</td>\n",
       "      <td>Fuses</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SLING</td>\n",
       "      <td>4.49</td>\n",
       "      <td>Sling</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>TNT1</td>\n",
       "      <td>2.50</td>\n",
       "      <td>TNT (1 stick)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>TNT2</td>\n",
       "      <td>10.00</td>\n",
       "      <td>TNT (5 sticks)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>FB</td>\n",
       "      <td>10.00</td>\n",
       "      <td>Bird seed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>FC</td>\n",
       "      <td>2.50</td>\n",
       "      <td>Carrots</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  prod_id  prod_price       prod_name\n",
       "0   ANV01        5.99    .5 ton anvil\n",
       "1   ANV02        9.99     1 ton anvil\n",
       "2     OL1        8.99         Oil can\n",
       "3     FU1        3.42           Fuses\n",
       "4   SLING        4.49           Sling\n",
       "5    TNT1        2.50   TNT (1 stick)\n",
       "6    TNT2       10.00  TNT (5 sticks)\n",
       "7      FB       10.00       Bird seed\n",
       "8      FC        2.50         Carrots"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sql_statement = ex11()\n",
    "data = pd.read_csv(\"ex11.csv\")\n",
    "df = pd.read_sql_query(sql_statement, conn)\n",
    "assert df.equals(data) == True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "27a124fc8474b594621243f44b1bdb7a",
     "grade": false,
     "grade_id": "cell-d7cdfc80f9f8c2a2",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def ex12():\n",
    "    # Write an SQL statement that SELECTs all rows from `products` table where the vendor id is not equal to 1003\n",
    "    # output columns: vend_id, prod_name\n",
    "\n",
    "    # YOUR CODE HERE\n",
    "    sql_statement = \"SELECT vend_id,  prod_name FROM products WHERE vend_id  !=1003\"\n",
    "    df = pd.read_sql_query(sql_statement, conn)\n",
    "    display(df)\n",
    "    return sql_statement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "711bed00faac61b79781f7db2f20e755",
     "grade": true,
     "grade_id": "cell-9d40be671b53010b",
     "locked": true,
     "points": 1,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "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>vend_id</th>\n",
       "      <th>prod_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>.5 ton anvil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1001</td>\n",
       "      <td>1 ton anvil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1001</td>\n",
       "      <td>2 ton anvil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1002</td>\n",
       "      <td>Oil can</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1002</td>\n",
       "      <td>Fuses</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1005</td>\n",
       "      <td>JetPack 1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1005</td>\n",
       "      <td>JetPack 2000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   vend_id     prod_name\n",
       "0     1001  .5 ton anvil\n",
       "1     1001   1 ton anvil\n",
       "2     1001   2 ton anvil\n",
       "3     1002       Oil can\n",
       "4     1002         Fuses\n",
       "5     1005  JetPack 1000\n",
       "6     1005  JetPack 2000"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sql_statement = ex12()\n",
    "data = pd.read_csv(\"ex12.csv\")\n",
    "df = pd.read_sql_query(sql_statement, conn)\n",
    "assert df.equals(data) == True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "c1ea7e07318aca4d9ff7a6814cf639bd",
     "grade": false,
     "grade_id": "cell-30a1770485f404f8",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def ex13():\n",
    "    # Write an SQL statement that SELECTs all rows from `products` table where the product prices are between 5 and 10\n",
    "    # output columns: prod_name, prod_price\n",
    "\n",
    "    # YOUR CODE HERE\n",
    "    sql_statement = \"SELECT prod_name,  prod_price  FROM products WHERE   prod_price BETWEEN 5 and 10\"\n",
    "    df = pd.read_sql_query(sql_statement, conn)\n",
    "    display(df)\n",
    "    return sql_statement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "f814f845e242d4394c97b9baf7e7cea8",
     "grade": true,
     "grade_id": "cell-2b48cda3d54fc65c",
     "locked": true,
     "points": 1,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "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>prod_name</th>\n",
       "      <th>prod_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>.5 ton anvil</td>\n",
       "      <td>5.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1 ton anvil</td>\n",
       "      <td>9.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Oil can</td>\n",
       "      <td>8.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>TNT (5 sticks)</td>\n",
       "      <td>10.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bird seed</td>\n",
       "      <td>10.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        prod_name  prod_price\n",
       "0    .5 ton anvil        5.99\n",
       "1     1 ton anvil        9.99\n",
       "2         Oil can        8.99\n",
       "3  TNT (5 sticks)       10.00\n",
       "4       Bird seed       10.00"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sql_statement = ex13()\n",
    "data = pd.read_csv(\"ex13.csv\")\n",
    "df = pd.read_sql_query(sql_statement, conn)\n",
    "assert df.equals(data) == True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "2a2c2a2a9f66b5be62f880506898f600",
     "grade": false,
     "grade_id": "cell-e1c8ec01f6bdc9c2",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def ex14():\n",
    "    # Write an SQL statement that SELECTs all rows from the `customers` table where the customer email is empty\n",
    "    # output columns: cust_id, cust_name\n",
    "\n",
    "    # YOUR CODE HERE\n",
    "    sql_statement = \"SELECT cust_id,  cust_name  FROM customers WHERE  cust_email is Null\"\n",
    "    df = pd.read_sql_query(sql_statement, conn)\n",
    "    display(df)\n",
    "    return sql_statement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "4d1f2ce2ff25a6d96d8a8e5b232408fb",
     "grade": true,
     "grade_id": "cell-f016b55ec76fa1f6",
     "locked": true,
     "points": 1,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "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>cust_id</th>\n",
       "      <th>cust_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10002</td>\n",
       "      <td>Mouse House</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10005</td>\n",
       "      <td>E Fudd</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   cust_id    cust_name\n",
       "0    10002  Mouse House\n",
       "1    10005       E Fudd"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sql_statement = ex14()\n",
    "data = pd.read_csv(\"ex14.csv\")\n",
    "df = pd.read_sql_query(sql_statement, conn)\n",
    "assert df.equals(data) == True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "1c34771fad4f96748118bc5c5d3ffeec",
     "grade": false,
     "grade_id": "cell-96b9f7ad1db0df5c",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def ex15():\n",
    "    # Write an SQL statement that SELECTs all rows from the `products` table where the vender id is 1003 and\n",
    "    # the price is less than or equal to 10. \n",
    "    # output columns: vend_id, prod_id, prod_price, prod_name\n",
    "\n",
    "    # YOUR CODE HERE\n",
    "    sql_statement = \"SELECT vend_id, prod_id,   prod_price, prod_name  FROM products WHERE vend_id like 1003 and prod_price <= 10\"\n",
    "    df = pd.read_sql_query(sql_statement, conn)\n",
    "    display(df)\n",
    "    return sql_statement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "7cd0fc88ef56674a218945559abb933c",
     "grade": true,
     "grade_id": "cell-0c69bab494531d3b",
     "locked": true,
     "points": 1,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "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>vend_id</th>\n",
       "      <th>prod_id</th>\n",
       "      <th>prod_price</th>\n",
       "      <th>prod_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1003</td>\n",
       "      <td>SLING</td>\n",
       "      <td>4.49</td>\n",
       "      <td>Sling</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1003</td>\n",
       "      <td>TNT1</td>\n",
       "      <td>2.50</td>\n",
       "      <td>TNT (1 stick)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>TNT2</td>\n",
       "      <td>10.00</td>\n",
       "      <td>TNT (5 sticks)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1003</td>\n",
       "      <td>FB</td>\n",
       "      <td>10.00</td>\n",
       "      <td>Bird seed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1003</td>\n",
       "      <td>FC</td>\n",
       "      <td>2.50</td>\n",
       "      <td>Carrots</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   vend_id prod_id  prod_price       prod_name\n",
       "0     1003   SLING        4.49           Sling\n",
       "1     1003    TNT1        2.50   TNT (1 stick)\n",
       "2     1003    TNT2       10.00  TNT (5 sticks)\n",
       "3     1003      FB       10.00       Bird seed\n",
       "4     1003      FC        2.50         Carrots"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sql_statement = ex15()\n",
    "data = pd.read_csv(\"ex15.csv\")\n",
    "df = pd.read_sql_query(sql_statement, conn)\n",
    "assert df.equals(data) == True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "f1e41507bbf743105838cf98925c51ae",
     "grade": false,
     "grade_id": "cell-6243f74de5453152",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def ex16():\n",
    "    # Write an SQL statement that SELECTs all rows from the `products` table where the vender id is 1002 or 1003 and\n",
    "    # the price is greater than or equal to 5. \n",
    "    # output columns: vend_id, prod_id, prod_price, prod_name\n",
    "\n",
    "    # YOUR CODE HERE\n",
    "    sql_statement = \"SELECT vend_id, prod_id,   prod_price, prod_name  FROM products WHERE vend_id IN (1002 , 1003) and prod_price >= 5\"\n",
    "    df = pd.read_sql_query(sql_statement, conn)\n",
    "    display(df)\n",
    "    return sql_statement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "66fb6b5d8a90f9617e27a2541885e2e2",
     "grade": true,
     "grade_id": "cell-724d7b68a015bddd",
     "locked": true,
     "points": 1,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "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>vend_id</th>\n",
       "      <th>prod_id</th>\n",
       "      <th>prod_price</th>\n",
       "      <th>prod_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1002</td>\n",
       "      <td>OL1</td>\n",
       "      <td>8.99</td>\n",
       "      <td>Oil can</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1003</td>\n",
       "      <td>TNT2</td>\n",
       "      <td>10.00</td>\n",
       "      <td>TNT (5 sticks)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>FB</td>\n",
       "      <td>10.00</td>\n",
       "      <td>Bird seed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1003</td>\n",
       "      <td>SAFE</td>\n",
       "      <td>50.00</td>\n",
       "      <td>Safe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1003</td>\n",
       "      <td>DTNTR</td>\n",
       "      <td>13.00</td>\n",
       "      <td>Detonator</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   vend_id prod_id  prod_price       prod_name\n",
       "0     1002     OL1        8.99         Oil can\n",
       "1     1003    TNT2       10.00  TNT (5 sticks)\n",
       "2     1003      FB       10.00       Bird seed\n",
       "3     1003    SAFE       50.00            Safe\n",
       "4     1003   DTNTR       13.00       Detonator"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sql_statement = ex16()\n",
    "data = pd.read_csv(\"ex16.csv\")\n",
    "df = pd.read_sql_query(sql_statement, conn)\n",
    "assert df.equals(data) == True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "98d9131e9bdafe75ede6ba0052cc970a",
     "grade": false,
     "grade_id": "cell-b99e32e0579a9767",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def ex17():\n",
    "    # Write an SQL statement that SELECTs all rows from the `products` table where the vender id is 1002 or 1003 or 1005.\n",
    "    # Use the IN operator for this!\n",
    "    # output columns: vend_id, prod_id, prod_price, prod_name\n",
    "\n",
    "    # YOUR CODE HERE\n",
    "    sql_statement = \"SELECT vend_id, prod_id,   prod_price, prod_name  FROM products WHERE vend_id in (1002, 1003 , 1005) \"\n",
    "    df = pd.read_sql_query(sql_statement, conn)\n",
    "    display(df)\n",
    "    return sql_statement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "718893ecbfa22966b3ddadfd047f8815",
     "grade": true,
     "grade_id": "cell-1753e3d7e199d906",
     "locked": true,
     "points": 1,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "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>vend_id</th>\n",
       "      <th>prod_id</th>\n",
       "      <th>prod_price</th>\n",
       "      <th>prod_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1002</td>\n",
       "      <td>OL1</td>\n",
       "      <td>8.99</td>\n",
       "      <td>Oil can</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>FU1</td>\n",
       "      <td>3.42</td>\n",
       "      <td>Fuses</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>SLING</td>\n",
       "      <td>4.49</td>\n",
       "      <td>Sling</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1003</td>\n",
       "      <td>TNT1</td>\n",
       "      <td>2.50</td>\n",
       "      <td>TNT (1 stick)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1003</td>\n",
       "      <td>TNT2</td>\n",
       "      <td>10.00</td>\n",
       "      <td>TNT (5 sticks)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1003</td>\n",
       "      <td>FB</td>\n",
       "      <td>10.00</td>\n",
       "      <td>Bird seed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1003</td>\n",
       "      <td>FC</td>\n",
       "      <td>2.50</td>\n",
       "      <td>Carrots</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1003</td>\n",
       "      <td>SAFE</td>\n",
       "      <td>50.00</td>\n",
       "      <td>Safe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1003</td>\n",
       "      <td>DTNTR</td>\n",
       "      <td>13.00</td>\n",
       "      <td>Detonator</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1005</td>\n",
       "      <td>JP1000</td>\n",
       "      <td>35.00</td>\n",
       "      <td>JetPack 1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1005</td>\n",
       "      <td>JP2000</td>\n",
       "      <td>55.00</td>\n",
       "      <td>JetPack 2000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    vend_id prod_id  prod_price       prod_name\n",
       "0      1002     OL1        8.99         Oil can\n",
       "1      1002     FU1        3.42           Fuses\n",
       "2      1003   SLING        4.49           Sling\n",
       "3      1003    TNT1        2.50   TNT (1 stick)\n",
       "4      1003    TNT2       10.00  TNT (5 sticks)\n",
       "5      1003      FB       10.00       Bird seed\n",
       "6      1003      FC        2.50         Carrots\n",
       "7      1003    SAFE       50.00            Safe\n",
       "8      1003   DTNTR       13.00       Detonator\n",
       "9      1005  JP1000       35.00    JetPack 1000\n",
       "10     1005  JP2000       55.00    JetPack 2000"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sql_statement = ex17()\n",
    "data = pd.read_csv(\"ex17.csv\")\n",
    "df = pd.read_sql_query(sql_statement, conn)\n",
    "assert df.equals(data) == True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "7a5dc5b679750b61565e64983b6e937f",
     "grade": false,
     "grade_id": "cell-49e3a1e8a03c0bbb",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def ex18():\n",
    "    # Write an SQL statement that SELECTs all rows from the `products` table where the vender id is NOT 1002 or 1003.\n",
    "    # Use the NOT IN operator for this!\n",
    "    # output columns: vend_id, prod_id, prod_price, prod_name\n",
    "\n",
    "    # YOUR CODE HERE\n",
    "    sql_statement = \"SELECT vend_id, prod_id,   prod_price, prod_name  FROM products WHERE vend_id not in (1002, 1003 )\"\n",
    "    df = pd.read_sql_query(sql_statement, conn)\n",
    "    display(df)\n",
    "    return sql_statement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "e814dfa5afed6b794c16e9bdf28ba6e7",
     "grade": true,
     "grade_id": "cell-c208b2ff8691f0ac",
     "locked": true,
     "points": 1,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "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>vend_id</th>\n",
       "      <th>prod_id</th>\n",
       "      <th>prod_price</th>\n",
       "      <th>prod_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>ANV01</td>\n",
       "      <td>5.99</td>\n",
       "      <td>.5 ton anvil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1001</td>\n",
       "      <td>ANV02</td>\n",
       "      <td>9.99</td>\n",
       "      <td>1 ton anvil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1001</td>\n",
       "      <td>ANV03</td>\n",
       "      <td>14.99</td>\n",
       "      <td>2 ton anvil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1005</td>\n",
       "      <td>JP1000</td>\n",
       "      <td>35.00</td>\n",
       "      <td>JetPack 1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>JP2000</td>\n",
       "      <td>55.00</td>\n",
       "      <td>JetPack 2000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   vend_id prod_id  prod_price     prod_name\n",
       "0     1001   ANV01        5.99  .5 ton anvil\n",
       "1     1001   ANV02        9.99   1 ton anvil\n",
       "2     1001   ANV03       14.99   2 ton anvil\n",
       "3     1005  JP1000       35.00  JetPack 1000\n",
       "4     1005  JP2000       55.00  JetPack 2000"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sql_statement = ex18()\n",
    "data = pd.read_csv(\"ex18.csv\")\n",
    "df = pd.read_sql_query(sql_statement, conn)\n",
    "assert df.equals(data) == True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "9cb3a5812fa821f611a375bc31309b51",
     "grade": false,
     "grade_id": "cell-a8f0d4f44b59bb9c",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def ex19():\n",
    "    # Write an SQL statement that SELECTs all rows from the `products` table where the product name starts with 'jet'\n",
    "    # output columns: prod_id, prod_price, prod_name\n",
    "\n",
    "    # YOUR CODE HERE\n",
    "    sql_statement = \"SELECT prod_id,   prod_price, prod_name  FROM products WHERE prod_name like 'Jet%'\"\n",
    "    df = pd.read_sql_query(sql_statement, conn)\n",
    "    display(df)\n",
    "    return sql_statement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "cc327bc680963eb843e355c43c2fba0d",
     "grade": true,
     "grade_id": "cell-146ffc73aef549ba",
     "locked": true,
     "points": 1,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "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>prod_id</th>\n",
       "      <th>prod_price</th>\n",
       "      <th>prod_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>JP1000</td>\n",
       "      <td>35.0</td>\n",
       "      <td>JetPack 1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>JP2000</td>\n",
       "      <td>55.0</td>\n",
       "      <td>JetPack 2000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  prod_id  prod_price     prod_name\n",
       "0  JP1000        35.0  JetPack 1000\n",
       "1  JP2000        55.0  JetPack 2000"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sql_statement = ex19()\n",
    "data = pd.read_csv(\"ex19.csv\")\n",
    "df = pd.read_sql_query(sql_statement, conn)\n",
    "assert df.equals(data) == True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "2e4d3a0ab272134b5546ea5d7e4f99e1",
     "grade": false,
     "grade_id": "cell-509a5f27687a3002",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def ex20():\n",
    "    # Write an SQL statement that SELECTs all rows from the `products` table where the product name ends with 'anvil'\n",
    "    # output columns: prod_id, prod_price, prod_name\n",
    "\n",
    "    # YOUR CODE HERE\n",
    "    sql_statement = \"SELECT prod_id,   prod_price, prod_name  FROM products WHERE prod_name like '%anvil'\"\n",
    "    df = pd.read_sql_query(sql_statement, conn)\n",
    "    display(df)\n",
    "    return sql_statement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "26d4dfa4214cfb46547eb1d5ceeab6d4",
     "grade": true,
     "grade_id": "cell-9d5610737d4dd8cb",
     "locked": true,
     "points": 1,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "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>prod_id</th>\n",
       "      <th>prod_price</th>\n",
       "      <th>prod_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ANV01</td>\n",
       "      <td>5.99</td>\n",
       "      <td>.5 ton anvil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ANV02</td>\n",
       "      <td>9.99</td>\n",
       "      <td>1 ton anvil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ANV03</td>\n",
       "      <td>14.99</td>\n",
       "      <td>2 ton anvil</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  prod_id  prod_price     prod_name\n",
       "0   ANV01        5.99  .5 ton anvil\n",
       "1   ANV02        9.99   1 ton anvil\n",
       "2   ANV03       14.99   2 ton anvil"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sql_statement = ex20()\n",
    "data = pd.read_csv(\"ex20.csv\")\n",
    "df = pd.read_sql_query(sql_statement, conn)\n",
    "assert df.equals(data) == True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "19af7914f1eaa85e5fda44e4cb4e4d3f",
     "grade": false,
     "grade_id": "cell-9a1fc74e65dfecc2",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def ex21():\n",
    "    # Write an SQL statement that SELECTs all rows from the `vendors` table. Concatenate vendor name and vendor country\n",
    "    # as vend_title. Order by vend_title. Leave space in between -- example `ACME (USA)`\n",
    "    # output columns: vend_title\n",
    "\n",
    "    # YOUR CODE HERE  \n",
    "    sql_statement = \"select vend_name || ' ' || '('||vend_country||')' as vend_title FROM vendors  order by vend_title;\"\n",
    "    df = pd.read_sql_query(sql_statement, conn)\n",
    "    display(df)\n",
    "    return sql_statement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "dd672a6547f08343d71f36544fe99928",
     "grade": true,
     "grade_id": "cell-ba4ff89538f3488b",
     "locked": true,
     "points": 1,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "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>vend_title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ACME (USA)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Anvils R Us (USA)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Furball Inc. (USA)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Jet Set (England)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Jouets Et Ours (France)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>LT Supplies (USA)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                vend_title\n",
       "0               ACME (USA)\n",
       "1        Anvils R Us (USA)\n",
       "2       Furball Inc. (USA)\n",
       "3        Jet Set (England)\n",
       "4  Jouets Et Ours (France)\n",
       "5        LT Supplies (USA)"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sql_statement = ex21()\n",
    "data = pd.read_csv(\"ex21.csv\")\n",
    "df = pd.read_sql_query(sql_statement, conn)\n",
    "assert df.equals(data) == True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "4176fd3162ff82c35596aaa5600971c9",
     "grade": false,
     "grade_id": "cell-95e41e48b2853dca",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def ex22():\n",
    "    # Write an SQL statement that SELECTs all rows from the `orderitems` table where order number is 20005. \n",
    "    # Display an extra calculated column called `expanded_price` that is the result of quantity multiplied by item_price.\n",
    "    # Round the value to two decimal places. \n",
    "    # output columns: prod_id, quantity, item_price, expanded_price\n",
    "\n",
    "    # YOUR CODE HERE\n",
    "    sql_statement = \"SELECT prod_id, quantity, item_price, round(quantity*item_price, 2) as expanded_price FROM orderitems where order_num is 20005; \"\n",
    "    df = pd.read_sql_query(sql_statement, conn)\n",
    "    display(df)\n",
    "    return sql_statement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "6636eadd35335585867ad0d4877fd2c0",
     "grade": true,
     "grade_id": "cell-45fceb3b463d3e90",
     "locked": true,
     "points": 1,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "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>prod_id</th>\n",
       "      <th>quantity</th>\n",
       "      <th>item_price</th>\n",
       "      <th>expanded_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ANV01</td>\n",
       "      <td>10</td>\n",
       "      <td>5.99</td>\n",
       "      <td>59.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ANV02</td>\n",
       "      <td>3</td>\n",
       "      <td>9.99</td>\n",
       "      <td>29.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TNT2</td>\n",
       "      <td>5</td>\n",
       "      <td>10.00</td>\n",
       "      <td>50.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>FB</td>\n",
       "      <td>1</td>\n",
       "      <td>10.00</td>\n",
       "      <td>10.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  prod_id  quantity  item_price  expanded_price\n",
       "0   ANV01        10        5.99           59.90\n",
       "1   ANV02         3        9.99           29.97\n",
       "2    TNT2         5       10.00           50.00\n",
       "3      FB         1       10.00           10.00"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sql_statement = ex22()\n",
    "data = pd.read_csv(\"ex22.csv\")\n",
    "df = pd.read_sql_query(sql_statement, conn)\n",
    "assert df.equals(data) == True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "ef2f063ec898cd1d6a3a615c4beb06e1",
     "grade": false,
     "grade_id": "cell-40c81684d54794d4",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def ex23():\n",
    "    # Write an SQL statement that SELECTs all rows from the `orders` table where the order date is between \n",
    "    # 2005-09-13 and 2005-10-04\n",
    "    # output columns: order_num, order_date\n",
    "    # https://www.sqlitetutorial.net/sqlite-date-functions/sqlite-date-function/\n",
    "    \n",
    "    # YOUR CODE HERE\n",
    "    sql_statement = \"SELECT order_num, order_date from orders WHERE order_date BETWEEN '2005-09-13' AND '2005-10-04';\"\n",
    "    df = pd.read_sql_query(sql_statement, conn)\n",
    "    display(df)\n",
    "    return sql_statement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "8af290b61254b733454f6c890ec615dc",
     "grade": true,
     "grade_id": "cell-c039035a93124821",
     "locked": true,
     "points": 1,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "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>order_num</th>\n",
       "      <th>order_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20007</td>\n",
       "      <td>2005-09-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20008</td>\n",
       "      <td>2005-10-03</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_num  order_date\n",
       "0      20007  2005-09-30\n",
       "1      20008  2005-10-03"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sql_statement = ex23()\n",
    "data = pd.read_csv(\"ex23.csv\")\n",
    "df = pd.read_sql_query(sql_statement, conn)\n",
    "assert df.equals(data) == True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "113b56ba9d45731435009bc9c430879a",
     "grade": false,
     "grade_id": "cell-21190814ac5d189f",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def ex24():\n",
    "    # Write an SQL statement that calculates the average price of all rows in the `products` table. \n",
    "    # Call the average column avg_price\n",
    "    # output columns: avg_price\n",
    "\n",
    "    # YOUR CODE HERE prod_price\n",
    "    sql_statement = \"SELECT AVG(prod_price) as avg_price FROM Products ;\"\n",
    "    df = pd.read_sql_query(sql_statement, conn)\n",
    "    display(df)\n",
    "    return sql_statement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "c1be3d23de04d147e9ef5e5b5c06d1a8",
     "grade": true,
     "grade_id": "cell-81d287d3835e6c4f",
     "locked": true,
     "points": 1,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "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_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>16.133571</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   avg_price\n",
       "0  16.133571"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sql_statement = ex24()\n",
    "data = pd.read_csv(\"ex24.csv\")\n",
    "df = pd.read_sql_query(sql_statement, conn)\n",
    "assert df.equals(data) == True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "66208fc3e195dbdd6f02171e83f97a9b",
     "grade": false,
     "grade_id": "cell-17cbfaf8fa0dfec1",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def ex25():\n",
    "    # Write an SQL statement that calculates the average price of all rows in the `products` table \n",
    "    # where the vendor id is 1003 . Call the average column avg_price\n",
    "    # output columns: avg_price\n",
    "\n",
    "    # YOUR CODE HERE\n",
    "    sql_statement = \"SELECT AVG(prod_price) as avg_price FROM Products where vend_id like 1003;\"\n",
    "    df = pd.read_sql_query(sql_statement, conn)\n",
    "    display(df)\n",
    "    return sql_statement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "cfc2a6f2f6d6ba4cea06985980c34be5",
     "grade": true,
     "grade_id": "cell-4698550258a974de",
     "locked": true,
     "points": 1,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "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_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>13.212857</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   avg_price\n",
       "0  13.212857"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sql_statement = ex25()\n",
    "data = pd.read_csv(\"ex25.csv\")\n",
    "df = pd.read_sql_query(sql_statement, conn)\n",
    "assert df.equals(data) == True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "2a5b5b433c03fc3f8e96fd2d4223c9e0",
     "grade": false,
     "grade_id": "cell-f3c78716afb20fc5",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def ex26():\n",
    "    # Write an SQL statement that counts the number of customers in the `customers` table \n",
    "    # Call the count column num_cust\n",
    "    # output columns: num_cust\n",
    "\n",
    "    # YOUR CODE HERE\n",
    "    sql_statement = \"SELECT COUNT(*) as num_cust FROM customers ;\"\n",
    "    df = pd.read_sql_query(sql_statement, conn)\n",
    "    display(df)\n",
    "    return sql_statement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "5d1b9d05492afb7f1e02147af2ab5291",
     "grade": true,
     "grade_id": "cell-ca2b60efc1ff7b14",
     "locked": true,
     "points": 1,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "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>num_cust</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   num_cust\n",
       "0         5"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sql_statement = ex26()\n",
    "data = pd.read_csv(\"ex26.csv\")\n",
    "df = pd.read_sql_query(sql_statement, conn)\n",
    "assert df.equals(data) == True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "58b89cecaf73ab97cee6fba0d0065cac",
     "grade": false,
     "grade_id": "cell-c0376fb42d0c438c",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def ex27():\n",
    "    # Write an SQL statement that calculates the max price in the `products` table \n",
    "    # Call the max column max_price. Round the value to two decimal places. \n",
    "    # output columns: max_price\n",
    "\n",
    "    # YOUR CODE HERE\n",
    "    sql_statement = \"SELECT  MAX (prod_price) as max_price FROM products ;\"\n",
    "    df = pd.read_sql_query(sql_statement, conn)\n",
    "    display(df)\n",
    "    return sql_statement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "b39592cc3b06508af632668a85fab444",
     "grade": true,
     "grade_id": "cell-04c3f0f96d6d2a1d",
     "locked": true,
     "points": 1,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "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>55.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   max_price\n",
       "0       55.0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sql_statement = ex27()\n",
    "data = pd.read_csv(\"ex27.csv\")\n",
    "df = pd.read_sql_query(sql_statement, conn)\n",
    "assert df.equals(data) == True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "3279e299d482cbf33522033aa8bee021",
     "grade": false,
     "grade_id": "cell-753f208c1fdb63b3",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def ex28():\n",
    "    # Write an SQL statement that calculates the min price in the `products` table \n",
    "    # Call the min column min_price. Round the value to two decimal places. \n",
    "    # output columns: min_price\n",
    "\n",
    "    # YOUR CODE HERE\n",
    "    sql_statement = \"SELECT  Min (prod_price) as min_price FROM products ;\"\n",
    "    df = pd.read_sql_query(sql_statement, conn)\n",
    "    display(df)\n",
    "    return sql_statement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "5ffcee199154efc62f9b929e073e9093",
     "grade": true,
     "grade_id": "cell-81dee40be27d5df4",
     "locked": true,
     "points": 1,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "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_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   min_price\n",
       "0        2.5"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sql_statement = ex28()\n",
    "data = pd.read_csv(\"ex28.csv\")\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": "60dc7cad4d877991ab053162f8a6713e",
     "grade": false,
     "grade_id": "cell-44fb13d2b1ea1236",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def ex29():\n",
    "    # Write an SQL statement that sums the quantity in the `orderitems` table where order number is 20005. \n",
    "    # Call the sum column items_ordered\n",
    "    # output columns: items_ordered\n",
    "\n",
    "    # YOUR CODE HERE\n",
    "    sql_statement = \"SELECT  sum (quantity) as items_ordered FROM orderitems where order_num like 20005;\"\n",
    "    df = pd.read_sql_query(sql_statement, conn)\n",
    "    display(df)\n",
    "    return sql_statement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "3733167a62e786596fb7ccb8619817b9",
     "grade": true,
     "grade_id": "cell-a5b64b8bc8a24d67",
     "locked": true,
     "points": 1,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "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>items_ordered</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   items_ordered\n",
       "0             19"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sql_statement = ex29()\n",
    "data = pd.read_csv(\"ex29.csv\")\n",
    "df = pd.read_sql_query(sql_statement, conn)\n",
    "assert df.equals(data) == True"
   ]
  }
 ],
 "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
}