Tugas mySQL #2.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymysql\n",
    "import pandas as pd\n",
    "\n",
    "host = '127.0.0.1'\n",
    "port = \"3306\"\n",
    "user = 'root'\n",
    "password = ''\n",
    "database = 'classicmodels'\n",
    "\n",
    "conn = pymysql.connect(\n",
    "    host=host,\n",
    "    port=int(port),\n",
    "    user=user,\n",
    "    passwd=password,\n",
    "    db=database,\n",
    "    charset='utf8mb4')\n",
    "\n",
    "def run(sql):\n",
    "    df = pd.read_sql_query(sql,conn)\n",
    "    return df\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# One to many relationship"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Report the account representative for each customer. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>fullname_salesrep</th>\n",
       "      <th>customername</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Andy Fixter</td>\n",
       "      <td>Australian Collectors, Co.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Andy Fixter</td>\n",
       "      <td>Anna's Decorations, Ltd</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Andy Fixter</td>\n",
       "      <td>Souveniers And Things Co.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Andy Fixter</td>\n",
       "      <td>Australian Gift Network, Co</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Andy Fixter</td>\n",
       "      <td>Australian Collectables, Ltd</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Barry Jones</td>\n",
       "      <td>Baane Mini Imports</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Barry Jones</td>\n",
       "      <td>Blauer See Auto, Co.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Barry Jones</td>\n",
       "      <td>Volvo Model Replicas, Co</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Barry Jones</td>\n",
       "      <td>Herkku Gifts</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Barry Jones</td>\n",
       "      <td>Clover Collections, Co.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Barry Jones</td>\n",
       "      <td>Toms Spezialitäten, Ltd</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Barry Jones</td>\n",
       "      <td>Norway Gifts By Mail, Co.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Barry Jones</td>\n",
       "      <td>Bavarian Collectables Imports, Co.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Barry Jones</td>\n",
       "      <td>Scandinavian Gift Ideas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Foon Yue Tseng</td>\n",
       "      <td>Muscle Machine Inc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Foon Yue Tseng</td>\n",
       "      <td>American Souvenirs Inc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Foon Yue Tseng</td>\n",
       "      <td>Vitachrome Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Foon Yue Tseng</td>\n",
       "      <td>Québec Home Shopping Network</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Foon Yue Tseng</td>\n",
       "      <td>Classic Legends Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Foon Yue Tseng</td>\n",
       "      <td>Super Scale Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Foon Yue Tseng</td>\n",
       "      <td>Microscale Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>George Vanauf</td>\n",
       "      <td>Land of Toys Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>George Vanauf</td>\n",
       "      <td>Gift Depot Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>George Vanauf</td>\n",
       "      <td>Canadian Gift Exchange Network</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>George Vanauf</td>\n",
       "      <td>Royal Canadian Collectables, Ltd.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>George Vanauf</td>\n",
       "      <td>Mini Classics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>George Vanauf</td>\n",
       "      <td>Tekni Collectables Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>George Vanauf</td>\n",
       "      <td>Gift Ideas Corp.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>George Vanauf</td>\n",
       "      <td>Motor Mint Distributors Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Gerard Hernandez</td>\n",
       "      <td>Atelier graphique</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>Mami Nishi</td>\n",
       "      <td>King Kong Collectables, Co.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>Mami Nishi</td>\n",
       "      <td>Cruz &amp; Sons Co.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>Mami Nishi</td>\n",
       "      <td>Tokyo Collectables, Ltd</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>Martin Gerard</td>\n",
       "      <td>Enaco Distributors</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>Martin Gerard</td>\n",
       "      <td>Vida Sport, Ltd</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>Martin Gerard</td>\n",
       "      <td>CAF Imports</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>Martin Gerard</td>\n",
       "      <td>Precious Collectables</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>Martin Gerard</td>\n",
       "      <td>Corrida Auto Replicas, Ltd</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>Martin Gerard</td>\n",
       "      <td>Iberia Gift Imports, Corp.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>Pamela Castillo</td>\n",
       "      <td>Danish Wholesale Imports</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>Pamela Castillo</td>\n",
       "      <td>Heintze Collectables</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>Pamela Castillo</td>\n",
       "      <td>Amica Models &amp; Co.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>Pamela Castillo</td>\n",
       "      <td>Rovelli Gifts</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>Pamela Castillo</td>\n",
       "      <td>Petit Auto</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>Pamela Castillo</td>\n",
       "      <td>Royale Belge</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>Pamela Castillo</td>\n",
       "      <td>Salzburg Collectables</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>Pamela Castillo</td>\n",
       "      <td>L'ordine Souveniers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>Pamela Castillo</td>\n",
       "      <td>Mini Auto Werke</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>Pamela Castillo</td>\n",
       "      <td>Frau da Collezione</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>Peter Marsh</td>\n",
       "      <td>Handji Gifts&amp; Co</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>Peter Marsh</td>\n",
       "      <td>Down Under Souveniers, Inc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>Peter Marsh</td>\n",
       "      <td>GiftsForHim.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>Peter Marsh</td>\n",
       "      <td>Extreme Desk Decorations, Ltd</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>Peter Marsh</td>\n",
       "      <td>Kelly's Gift Shop</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>Steve Patterson</td>\n",
       "      <td>Diecast Classics Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>Steve Patterson</td>\n",
       "      <td>Auto-Moto Classics Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>Steve Patterson</td>\n",
       "      <td>Marta's Replicas Co.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>Steve Patterson</td>\n",
       "      <td>Gifts4AllAges.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>Steve Patterson</td>\n",
       "      <td>Online Diecast Creations Co.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>Steve Patterson</td>\n",
       "      <td>FunGiftIdeas.com</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   fullname_salesrep                        customername\n",
       "0        Andy Fixter          Australian Collectors, Co.\n",
       "1        Andy Fixter             Anna's Decorations, Ltd\n",
       "2        Andy Fixter           Souveniers And Things Co.\n",
       "3        Andy Fixter         Australian Gift Network, Co\n",
       "4        Andy Fixter        Australian Collectables, Ltd\n",
       "5        Barry Jones                  Baane Mini Imports\n",
       "6        Barry Jones                Blauer See Auto, Co.\n",
       "7        Barry Jones            Volvo Model Replicas, Co\n",
       "8        Barry Jones                        Herkku Gifts\n",
       "9        Barry Jones             Clover Collections, Co.\n",
       "10       Barry Jones             Toms Spezialitäten, Ltd\n",
       "11       Barry Jones           Norway Gifts By Mail, Co.\n",
       "12       Barry Jones  Bavarian Collectables Imports, Co.\n",
       "13       Barry Jones             Scandinavian Gift Ideas\n",
       "14    Foon Yue Tseng                  Muscle Machine Inc\n",
       "15    Foon Yue Tseng              American Souvenirs Inc\n",
       "16    Foon Yue Tseng                     Vitachrome Inc.\n",
       "17    Foon Yue Tseng        Québec Home Shopping Network\n",
       "18    Foon Yue Tseng                Classic Legends Inc.\n",
       "19    Foon Yue Tseng                    Super Scale Inc.\n",
       "20    Foon Yue Tseng                     Microscale Inc.\n",
       "21     George Vanauf                   Land of Toys Inc.\n",
       "22     George Vanauf                     Gift Depot Inc.\n",
       "23     George Vanauf      Canadian Gift Exchange Network\n",
       "24     George Vanauf   Royal Canadian Collectables, Ltd.\n",
       "25     George Vanauf                       Mini Classics\n",
       "26     George Vanauf             Tekni Collectables Inc.\n",
       "27     George Vanauf                    Gift Ideas Corp.\n",
       "28     George Vanauf        Motor Mint Distributors Inc.\n",
       "29  Gerard Hernandez                   Atelier graphique\n",
       "..               ...                                 ...\n",
       "70        Mami Nishi         King Kong Collectables, Co.\n",
       "71        Mami Nishi                     Cruz & Sons Co.\n",
       "72        Mami Nishi             Tokyo Collectables, Ltd\n",
       "73     Martin Gerard                  Enaco Distributors\n",
       "74     Martin Gerard                     Vida Sport, Ltd\n",
       "75     Martin Gerard                         CAF Imports\n",
       "76     Martin Gerard               Precious Collectables\n",
       "77     Martin Gerard          Corrida Auto Replicas, Ltd\n",
       "78     Martin Gerard          Iberia Gift Imports, Corp.\n",
       "79   Pamela Castillo            Danish Wholesale Imports\n",
       "80   Pamela Castillo                Heintze Collectables\n",
       "81   Pamela Castillo                  Amica Models & Co.\n",
       "82   Pamela Castillo                       Rovelli Gifts\n",
       "83   Pamela Castillo                          Petit Auto\n",
       "84   Pamela Castillo                        Royale Belge\n",
       "85   Pamela Castillo               Salzburg Collectables\n",
       "86   Pamela Castillo                 L'ordine Souveniers\n",
       "87   Pamela Castillo                     Mini Auto Werke\n",
       "88   Pamela Castillo                  Frau da Collezione\n",
       "89       Peter Marsh                    Handji Gifts& Co\n",
       "90       Peter Marsh          Down Under Souveniers, Inc\n",
       "91       Peter Marsh                     GiftsForHim.com\n",
       "92       Peter Marsh       Extreme Desk Decorations, Ltd\n",
       "93       Peter Marsh                   Kelly's Gift Shop\n",
       "94   Steve Patterson               Diecast Classics Inc.\n",
       "95   Steve Patterson             Auto-Moto Classics Inc.\n",
       "96   Steve Patterson                Marta's Replicas Co.\n",
       "97   Steve Patterson                   Gifts4AllAges.com\n",
       "98   Steve Patterson        Online Diecast Creations Co.\n",
       "99   Steve Patterson                    FunGiftIdeas.com\n",
       "\n",
       "[100 rows x 2 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "run(\"\"\"\n",
    "SELECT concat(firstname, ' ', lastname) as fullname_salesrep, customername\n",
    "FROM \n",
    "    employees E\n",
    "        INNER JOIN\n",
    "    customers C ON E.employeenumber = C.salesrepemployeenumber  \n",
    "ORDER BY fullname_salesrep\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Report total payments for Atelier graphique\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customername</th>\n",
       "      <th>total_payments</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Atelier graphique</td>\n",
       "      <td>22314.36</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        customername  total_payments\n",
       "0  Atelier graphique        22314.36"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "run(\"\"\"\n",
    "SELECT\n",
    "    customername,\n",
    "    SUM(amount) as total_payments\n",
    "FROM\n",
    "    customers C\n",
    "        INNER JOIN\n",
    "    payments P USING (customernumber)\n",
    "WHERE customername = 'atelier graphique'\n",
    "    \"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Report the total payments by date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>paymentdate</th>\n",
       "      <th>SUM(amount)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2003-01-16</td>\n",
       "      <td>10223.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2003-01-28</td>\n",
       "      <td>10549.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2003-01-30</td>\n",
       "      <td>5494.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2003-02-16</td>\n",
       "      <td>50218.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2003-02-20</td>\n",
       "      <td>53959.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2003-02-25</td>\n",
       "      <td>40206.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2003-03-02</td>\n",
       "      <td>52151.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2003-03-09</td>\n",
       "      <td>51001.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2003-03-12</td>\n",
       "      <td>22292.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2003-03-20</td>\n",
       "      <td>25833.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2003-03-27</td>\n",
       "      <td>48425.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2003-04-09</td>\n",
       "      <td>24212.79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2003-04-11</td>\n",
       "      <td>11044.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2003-04-16</td>\n",
       "      <td>21665.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2003-04-19</td>\n",
       "      <td>1627.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2003-04-20</td>\n",
       "      <td>33383.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2003-04-22</td>\n",
       "      <td>44380.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2003-05-09</td>\n",
       "      <td>3101.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2003-05-12</td>\n",
       "      <td>35826.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2003-05-20</td>\n",
       "      <td>45864.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2003-05-21</td>\n",
       "      <td>16700.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2003-05-25</td>\n",
       "      <td>50824.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2003-05-31</td>\n",
       "      <td>7565.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>2003-06-05</td>\n",
       "      <td>14571.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>2003-06-06</td>\n",
       "      <td>32641.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>2003-06-13</td>\n",
       "      <td>57131.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>2003-06-18</td>\n",
       "      <td>58841.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>2003-06-25</td>\n",
       "      <td>17032.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>2003-07-05</td>\n",
       "      <td>2880.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>2003-07-06</td>\n",
       "      <td>6036.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202</th>\n",
       "      <td>2005-02-02</td>\n",
       "      <td>12692.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>203</th>\n",
       "      <td>2005-02-03</td>\n",
       "      <td>14379.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>204</th>\n",
       "      <td>2005-02-06</td>\n",
       "      <td>39580.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>205</th>\n",
       "      <td>2005-02-09</td>\n",
       "      <td>27083.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>206</th>\n",
       "      <td>2005-02-12</td>\n",
       "      <td>28322.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>207</th>\n",
       "      <td>2005-02-14</td>\n",
       "      <td>33967.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>208</th>\n",
       "      <td>2005-02-15</td>\n",
       "      <td>46770.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>209</th>\n",
       "      <td>2005-02-22</td>\n",
       "      <td>49523.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>210</th>\n",
       "      <td>2005-03-01</td>\n",
       "      <td>21432.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>211</th>\n",
       "      <td>2005-03-02</td>\n",
       "      <td>3452.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>212</th>\n",
       "      <td>2005-03-05</td>\n",
       "      <td>101244.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>213</th>\n",
       "      <td>2005-03-10</td>\n",
       "      <td>23602.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>214</th>\n",
       "      <td>2005-03-15</td>\n",
       "      <td>46781.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>215</th>\n",
       "      <td>2005-03-18</td>\n",
       "      <td>120166.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>216</th>\n",
       "      <td>2005-03-25</td>\n",
       "      <td>65071.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>217</th>\n",
       "      <td>2005-03-27</td>\n",
       "      <td>3516.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>218</th>\n",
       "      <td>2005-04-15</td>\n",
       "      <td>30293.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>219</th>\n",
       "      <td>2005-04-16</td>\n",
       "      <td>83598.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>220</th>\n",
       "      <td>2005-04-18</td>\n",
       "      <td>27966.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221</th>\n",
       "      <td>2005-04-23</td>\n",
       "      <td>12190.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>222</th>\n",
       "      <td>2005-04-30</td>\n",
       "      <td>29848.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>223</th>\n",
       "      <td>2005-05-03</td>\n",
       "      <td>43964.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>224</th>\n",
       "      <td>2005-05-17</td>\n",
       "      <td>17928.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>225</th>\n",
       "      <td>2005-05-18</td>\n",
       "      <td>615.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>226</th>\n",
       "      <td>2005-05-19</td>\n",
       "      <td>75396.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>227</th>\n",
       "      <td>2005-05-20</td>\n",
       "      <td>29070.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>228</th>\n",
       "      <td>2005-05-23</td>\n",
       "      <td>75020.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>229</th>\n",
       "      <td>2005-05-25</td>\n",
       "      <td>30253.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>230</th>\n",
       "      <td>2005-06-03</td>\n",
       "      <td>12432.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>231</th>\n",
       "      <td>2005-06-09</td>\n",
       "      <td>46656.94</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>232 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    paymentdate  SUM(amount)\n",
       "0    2003-01-16     10223.83\n",
       "1    2003-01-28     10549.01\n",
       "2    2003-01-30      5494.78\n",
       "3    2003-02-16     50218.95\n",
       "4    2003-02-20     53959.21\n",
       "5    2003-02-25     40206.20\n",
       "6    2003-03-02     52151.81\n",
       "7    2003-03-09     51001.22\n",
       "8    2003-03-12     22292.62\n",
       "9    2003-03-20     25833.14\n",
       "10   2003-03-27     48425.69\n",
       "11   2003-04-09     24212.79\n",
       "12   2003-04-11     11044.30\n",
       "13   2003-04-16     21665.98\n",
       "14   2003-04-19      1627.56\n",
       "15   2003-04-20     33383.14\n",
       "16   2003-04-22     44380.15\n",
       "17   2003-05-09      3101.40\n",
       "18   2003-05-12     35826.33\n",
       "19   2003-05-20     45864.03\n",
       "20   2003-05-21     16700.47\n",
       "21   2003-05-25     50824.66\n",
       "22   2003-05-31      7565.08\n",
       "23   2003-06-05     14571.44\n",
       "24   2003-06-06     32641.98\n",
       "25   2003-06-13     57131.92\n",
       "26   2003-06-18     58841.35\n",
       "27   2003-06-25     17032.29\n",
       "28   2003-07-05      2880.00\n",
       "29   2003-07-06      6036.96\n",
       "..          ...          ...\n",
       "202  2005-02-02     12692.19\n",
       "203  2005-02-03     14379.90\n",
       "204  2005-02-06     39580.60\n",
       "205  2005-02-09     27083.78\n",
       "206  2005-02-12     28322.83\n",
       "207  2005-02-14     33967.73\n",
       "208  2005-02-15     46770.52\n",
       "209  2005-02-22     49523.67\n",
       "210  2005-03-01     21432.31\n",
       "211  2005-03-02      3452.75\n",
       "212  2005-03-05    101244.59\n",
       "213  2005-03-10     23602.90\n",
       "214  2005-03-15     46781.66\n",
       "215  2005-03-18    120166.58\n",
       "216  2005-03-25     65071.26\n",
       "217  2005-03-27      3516.04\n",
       "218  2005-04-15     30293.77\n",
       "219  2005-04-16     83598.04\n",
       "220  2005-04-18     27966.54\n",
       "221  2005-04-23     12190.85\n",
       "222  2005-04-30     29848.52\n",
       "223  2005-05-03     43964.87\n",
       "224  2005-05-17     17928.09\n",
       "225  2005-05-18       615.45\n",
       "226  2005-05-19     75396.26\n",
       "227  2005-05-20     29070.38\n",
       "228  2005-05-23     75020.13\n",
       "229  2005-05-25     30253.75\n",
       "230  2005-06-03     12432.32\n",
       "231  2005-06-09     46656.94\n",
       "\n",
       "[232 rows x 2 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "run(\"\"\"\n",
    "SELECT \n",
    "    paymentdate,\n",
    "    SUM(amount)\n",
    "FROM\n",
    "    payments\n",
    "GROUP BY paymentdate\n",
    "ORDER BY paymentdate\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Report the products that have not been sold"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>productcode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>S10_1678</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>S10_1949</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>S10_2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>S10_4698</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>S10_4757</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>S10_4962</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>S12_1099</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>S12_1108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>S12_1666</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>S12_2823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>S12_3148</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>S12_3380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>S12_3891</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>S12_3990</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>S12_4473</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>S12_4675</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>S18_1097</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>S18_1129</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>S18_1342</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>S18_1367</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>S18_1589</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>S18_1662</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>S18_1749</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>S18_1889</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>S18_1984</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>S18_2238</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>S18_2248</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>S18_2319</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>S18_2325</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>S18_2432</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>S24_4048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>S24_4258</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>S24_4278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>S24_4620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>S32_1268</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>S32_1374</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>S32_2206</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>S32_2509</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>S32_3207</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>S32_3522</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>S32_4289</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>S32_4485</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>S50_1341</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>S50_1392</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>S50_1514</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>S50_4713</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>S700_1138</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>S700_1691</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>S700_1938</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>S700_2047</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>S700_2466</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>S700_2610</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>S700_2824</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>S700_2834</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>S700_3167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104</th>\n",
       "      <td>S700_3505</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>S700_3962</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>S700_4002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>S72_1253</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108</th>\n",
       "      <td>S72_3212</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>109 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    productcode\n",
       "0      S10_1678\n",
       "1      S10_1949\n",
       "2      S10_2016\n",
       "3      S10_4698\n",
       "4      S10_4757\n",
       "5      S10_4962\n",
       "6      S12_1099\n",
       "7      S12_1108\n",
       "8      S12_1666\n",
       "9      S12_2823\n",
       "10     S12_3148\n",
       "11     S12_3380\n",
       "12     S12_3891\n",
       "13     S12_3990\n",
       "14     S12_4473\n",
       "15     S12_4675\n",
       "16     S18_1097\n",
       "17     S18_1129\n",
       "18     S18_1342\n",
       "19     S18_1367\n",
       "20     S18_1589\n",
       "21     S18_1662\n",
       "22     S18_1749\n",
       "23     S18_1889\n",
       "24     S18_1984\n",
       "25     S18_2238\n",
       "26     S18_2248\n",
       "27     S18_2319\n",
       "28     S18_2325\n",
       "29     S18_2432\n",
       "..          ...\n",
       "79     S24_4048\n",
       "80     S24_4258\n",
       "81     S24_4278\n",
       "82     S24_4620\n",
       "83     S32_1268\n",
       "84     S32_1374\n",
       "85     S32_2206\n",
       "86     S32_2509\n",
       "87     S32_3207\n",
       "88     S32_3522\n",
       "89     S32_4289\n",
       "90     S32_4485\n",
       "91     S50_1341\n",
       "92     S50_1392\n",
       "93     S50_1514\n",
       "94     S50_4713\n",
       "95    S700_1138\n",
       "96    S700_1691\n",
       "97    S700_1938\n",
       "98    S700_2047\n",
       "99    S700_2466\n",
       "100   S700_2610\n",
       "101   S700_2824\n",
       "102   S700_2834\n",
       "103   S700_3167\n",
       "104   S700_3505\n",
       "105   S700_3962\n",
       "106   S700_4002\n",
       "107    S72_1253\n",
       "108    S72_3212\n",
       "\n",
       "[109 rows x 1 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "run(\"SELECT DISTINCT productcode FROM orderdetails\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>productcode</th>\n",
       "      <th>productname</th>\n",
       "      <th>productline</th>\n",
       "      <th>quantityinstock</th>\n",
       "      <th>ordernumber</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>S18_3233</td>\n",
       "      <td>1985 Toyota Supra</td>\n",
       "      <td>Classic Cars</td>\n",
       "      <td>7733</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  productcode        productname   productline  quantityinstock ordernumber\n",
       "0    S18_3233  1985 Toyota Supra  Classic Cars             7733        None"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "run (\"\"\"\n",
    "SELECT P.productcode, productname, productline, quantityinstock, ordernumber\n",
    "FROM \n",
    "    products P\n",
    "        LEFT JOIN\n",
    "    orderdetails O USING(productcode)\n",
    "WHERE o.productcode is NULL\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. List the amount paid by each customer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customernumber</th>\n",
       "      <th>customername</th>\n",
       "      <th>total_pembayaran</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>103</td>\n",
       "      <td>Atelier graphique</td>\n",
       "      <td>22314.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>112</td>\n",
       "      <td>Signal Gift Stores</td>\n",
       "      <td>80180.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>114</td>\n",
       "      <td>Australian Collectors, Co.</td>\n",
       "      <td>180585.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>119</td>\n",
       "      <td>La Rochelle Gifts</td>\n",
       "      <td>116949.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>121</td>\n",
       "      <td>Baane Mini Imports</td>\n",
       "      <td>104224.79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>124</td>\n",
       "      <td>Mini Gifts Distributors Ltd.</td>\n",
       "      <td>584188.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>128</td>\n",
       "      <td>Blauer See Auto, Co.</td>\n",
       "      <td>75937.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>129</td>\n",
       "      <td>Mini Wheels Co.</td>\n",
       "      <td>66710.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>131</td>\n",
       "      <td>Land of Toys Inc.</td>\n",
       "      <td>107639.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>141</td>\n",
       "      <td>Euro+ Shopping Channel</td>\n",
       "      <td>715738.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>144</td>\n",
       "      <td>Volvo Model Replicas, Co</td>\n",
       "      <td>43680.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>145</td>\n",
       "      <td>Danish Wholesale Imports</td>\n",
       "      <td>107446.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>146</td>\n",
       "      <td>Saveley &amp; Henriot, Co.</td>\n",
       "      <td>130305.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>148</td>\n",
       "      <td>Dragon Souveniers, Ltd.</td>\n",
       "      <td>156251.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>151</td>\n",
       "      <td>Muscle Machine Inc</td>\n",
       "      <td>177913.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>157</td>\n",
       "      <td>Diecast Classics Inc.</td>\n",
       "      <td>98509.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>161</td>\n",
       "      <td>Technics Stores Inc.</td>\n",
       "      <td>104545.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>166</td>\n",
       "      <td>Handji Gifts&amp; Co</td>\n",
       "      <td>105420.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>167</td>\n",
       "      <td>Herkku Gifts</td>\n",
       "      <td>97562.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>171</td>\n",
       "      <td>Daedalus Designs Imports</td>\n",
       "      <td>61781.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>172</td>\n",
       "      <td>La Corne D'abondance, Co.</td>\n",
       "      <td>86553.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>173</td>\n",
       "      <td>Cambridge Collectables Co.</td>\n",
       "      <td>32198.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>175</td>\n",
       "      <td>Gift Depot Inc.</td>\n",
       "      <td>95424.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>177</td>\n",
       "      <td>Osaka Souveniers Co.</td>\n",
       "      <td>62361.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>181</td>\n",
       "      <td>Vitachrome Inc.</td>\n",
       "      <td>72497.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>186</td>\n",
       "      <td>Toys of Finland, Co.</td>\n",
       "      <td>95546.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>187</td>\n",
       "      <td>AV Stores, Co.</td>\n",
       "      <td>148410.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>189</td>\n",
       "      <td>Clover Collections, Co.</td>\n",
       "      <td>49898.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>198</td>\n",
       "      <td>Auto-Moto Classics Inc.</td>\n",
       "      <td>21554.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>201</td>\n",
       "      <td>UK Collectables, Ltd.</td>\n",
       "      <td>61167.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>357</td>\n",
       "      <td>GiftsForHim.com</td>\n",
       "      <td>56662.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>362</td>\n",
       "      <td>Gifts4AllAges.com</td>\n",
       "      <td>33533.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>363</td>\n",
       "      <td>Online Diecast Creations Co.</td>\n",
       "      <td>116449.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>379</td>\n",
       "      <td>Collectables For Less Inc.</td>\n",
       "      <td>73533.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>381</td>\n",
       "      <td>Royale Belge</td>\n",
       "      <td>29217.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>382</td>\n",
       "      <td>Salzburg Collectables</td>\n",
       "      <td>85060.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>385</td>\n",
       "      <td>Cruz &amp; Sons Co.</td>\n",
       "      <td>87468.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>386</td>\n",
       "      <td>L'ordine Souveniers</td>\n",
       "      <td>90143.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>398</td>\n",
       "      <td>Tokyo Collectables, Ltd</td>\n",
       "      <td>105548.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>406</td>\n",
       "      <td>Auto Canal+ Petit</td>\n",
       "      <td>86436.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>412</td>\n",
       "      <td>Extreme Desk Decorations, Ltd</td>\n",
       "      <td>66704.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>415</td>\n",
       "      <td>Bavarian Collectables Imports, Co.</td>\n",
       "      <td>31310.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>424</td>\n",
       "      <td>Classic Legends Inc.</td>\n",
       "      <td>69214.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>447</td>\n",
       "      <td>Gift Ideas Corp.</td>\n",
       "      <td>49967.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>448</td>\n",
       "      <td>Scandinavian Gift Ideas</td>\n",
       "      <td>76776.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>450</td>\n",
       "      <td>The Sharp Gifts Warehouse</td>\n",
       "      <td>59551.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>452</td>\n",
       "      <td>Mini Auto Werke</td>\n",
       "      <td>51059.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>455</td>\n",
       "      <td>Super Scale Inc.</td>\n",
       "      <td>70378.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>456</td>\n",
       "      <td>Microscale Inc.</td>\n",
       "      <td>29230.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>458</td>\n",
       "      <td>Corrida Auto Replicas, Ltd</td>\n",
       "      <td>112440.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>462</td>\n",
       "      <td>FunGiftIdeas.com</td>\n",
       "      <td>88627.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>471</td>\n",
       "      <td>Australian Collectables, Ltd</td>\n",
       "      <td>44920.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>473</td>\n",
       "      <td>Frau da Collezione</td>\n",
       "      <td>25358.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>475</td>\n",
       "      <td>West Coast Collectables Co.</td>\n",
       "      <td>43748.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>484</td>\n",
       "      <td>Iberia Gift Imports, Corp.</td>\n",
       "      <td>50987.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>486</td>\n",
       "      <td>Motor Mint Distributors Inc.</td>\n",
       "      <td>77726.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>487</td>\n",
       "      <td>Signal Collectibles Ltd.</td>\n",
       "      <td>42570.37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>489</td>\n",
       "      <td>Double Decker Gift Stores, Ltd</td>\n",
       "      <td>29586.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>495</td>\n",
       "      <td>Diecast Collectables</td>\n",
       "      <td>65541.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>496</td>\n",
       "      <td>Kelly's Gift Shop</td>\n",
       "      <td>114497.19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>98 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    customernumber                        customername  total_pembayaran\n",
       "0              103                   Atelier graphique          22314.36\n",
       "1              112                  Signal Gift Stores          80180.98\n",
       "2              114          Australian Collectors, Co.         180585.07\n",
       "3              119                   La Rochelle Gifts         116949.68\n",
       "4              121                  Baane Mini Imports         104224.79\n",
       "5              124        Mini Gifts Distributors Ltd.         584188.24\n",
       "6              128                Blauer See Auto, Co.          75937.76\n",
       "7              129                     Mini Wheels Co.          66710.56\n",
       "8              131                   Land of Toys Inc.         107639.94\n",
       "9              141              Euro+ Shopping Channel         715738.98\n",
       "10             144            Volvo Model Replicas, Co          43680.65\n",
       "11             145            Danish Wholesale Imports         107446.50\n",
       "12             146              Saveley & Henriot, Co.         130305.35\n",
       "13             148             Dragon Souveniers, Ltd.         156251.03\n",
       "14             151                  Muscle Machine Inc         177913.95\n",
       "15             157               Diecast Classics Inc.          98509.25\n",
       "16             161                Technics Stores Inc.         104545.22\n",
       "17             166                    Handji Gifts& Co         105420.57\n",
       "18             167                        Herkku Gifts          97562.47\n",
       "19             171            Daedalus Designs Imports          61781.70\n",
       "20             172           La Corne D'abondance, Co.          86553.52\n",
       "21             173          Cambridge Collectables Co.          32198.69\n",
       "22             175                     Gift Depot Inc.          95424.63\n",
       "23             177                Osaka Souveniers Co.          62361.22\n",
       "24             181                     Vitachrome Inc.          72497.64\n",
       "25             186                Toys of Finland, Co.          95546.46\n",
       "26             187                      AV Stores, Co.         148410.09\n",
       "27             189             Clover Collections, Co.          49898.27\n",
       "28             198             Auto-Moto Classics Inc.          21554.26\n",
       "29             201               UK Collectables, Ltd.          61167.18\n",
       "..             ...                                 ...               ...\n",
       "68             357                     GiftsForHim.com          56662.38\n",
       "69             362                   Gifts4AllAges.com          33533.47\n",
       "70             363        Online Diecast Creations Co.         116449.29\n",
       "71             379          Collectables For Less Inc.          73533.65\n",
       "72             381                        Royale Belge          29217.18\n",
       "73             382               Salzburg Collectables          85060.00\n",
       "74             385                     Cruz & Sons Co.          87468.30\n",
       "75             386                 L'ordine Souveniers          90143.31\n",
       "76             398             Tokyo Collectables, Ltd         105548.73\n",
       "77             406                   Auto Canal+ Petit          86436.97\n",
       "78             412       Extreme Desk Decorations, Ltd          66704.94\n",
       "79             415  Bavarian Collectables Imports, Co.          31310.09\n",
       "80             424                Classic Legends Inc.          69214.33\n",
       "81             447                    Gift Ideas Corp.          49967.78\n",
       "82             448             Scandinavian Gift Ideas          76776.44\n",
       "83             450           The Sharp Gifts Warehouse          59551.38\n",
       "84             452                     Mini Auto Werke          51059.99\n",
       "85             455                    Super Scale Inc.          70378.65\n",
       "86             456                     Microscale Inc.          29230.43\n",
       "87             458          Corrida Auto Replicas, Ltd         112440.09\n",
       "88             462                    FunGiftIdeas.com          88627.49\n",
       "89             471        Australian Collectables, Ltd          44920.76\n",
       "90             473                  Frau da Collezione          25358.32\n",
       "91             475         West Coast Collectables Co.          43748.72\n",
       "92             484          Iberia Gift Imports, Corp.          50987.85\n",
       "93             486        Motor Mint Distributors Inc.          77726.59\n",
       "94             487            Signal Collectibles Ltd.          42570.37\n",
       "95             489      Double Decker Gift Stores, Ltd          29586.15\n",
       "96             495                Diecast Collectables          65541.74\n",
       "97             496                   Kelly's Gift Shop         114497.19\n",
       "\n",
       "[98 rows x 3 columns]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "run(\"\"\"\n",
    "SELECT \n",
    "    C.customernumber,\n",
    "    customername,\n",
    "    SUM(amount) as total_pembayaran\n",
    "    \n",
    "FROM\n",
    "    customers C\n",
    "        INNER JOIN\n",
    "    payments P USING(customernumber)\n",
    "GROUP BY customernumber\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. How many orders have been placed by Herkku Gifts?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customername</th>\n",
       "      <th>customernumber</th>\n",
       "      <th>count(*)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Herkku Gifts</td>\n",
       "      <td>167</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customername  customernumber  count(*)\n",
       "0  Herkku Gifts             167         3"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "run(\"\"\"\n",
    "SELECT\n",
    "    customername,\n",
    "    customernumber,\n",
    "    count(*)\n",
    "FROM \n",
    "    customers C\n",
    "         INNER JOIN\n",
    "    orders O USING(customernumber)\n",
    "WHERE customername='Herkku Gifts'\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. Who are the employees in Boston?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>employeenumber</th>\n",
       "      <th>fullname</th>\n",
       "      <th>officecode</th>\n",
       "      <th>city</th>\n",
       "      <th>jobtitle</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1188</td>\n",
       "      <td>Julie Firrelli</td>\n",
       "      <td>2</td>\n",
       "      <td>Boston</td>\n",
       "      <td>Sales Rep</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1216</td>\n",
       "      <td>Steve Patterson</td>\n",
       "      <td>2</td>\n",
       "      <td>Boston</td>\n",
       "      <td>Sales Rep</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   employeenumber         fullname officecode    city   jobtitle\n",
       "0            1188   Julie Firrelli          2  Boston  Sales Rep\n",
       "1            1216  Steve Patterson          2  Boston  Sales Rep"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "run(\"\"\"\n",
    "SELECT \n",
    "    employeenumber,\n",
    "    CONCAT(firstname, ' ', lastname) as fullname,\n",
    "    E.officecode,\n",
    "    city,\n",
    "    jobtitle\n",
    "FROM\n",
    "    employees E\n",
    "        INNER JOIN\n",
    "    offices O USING (officecode)\n",
    "WHERE officecode = 2\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8. Report those payments greater than 100,000. Sort the report so the customer who made the highest payment appears first"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customernumber</th>\n",
       "      <th>customername</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>141</td>\n",
       "      <td>Euro+ Shopping Channel</td>\n",
       "      <td>120166.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>141</td>\n",
       "      <td>Euro+ Shopping Channel</td>\n",
       "      <td>116208.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>124</td>\n",
       "      <td>Mini Gifts Distributors Ltd.</td>\n",
       "      <td>111654.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>148</td>\n",
       "      <td>Dragon Souveniers, Ltd.</td>\n",
       "      <td>105743.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>124</td>\n",
       "      <td>Mini Gifts Distributors Ltd.</td>\n",
       "      <td>101244.59</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customernumber                  customername     amount\n",
       "0             141        Euro+ Shopping Channel  120166.58\n",
       "1             141        Euro+ Shopping Channel  116208.40\n",
       "2             124  Mini Gifts Distributors Ltd.  111654.40\n",
       "3             148       Dragon Souveniers, Ltd.  105743.00\n",
       "4             124  Mini Gifts Distributors Ltd.  101244.59"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "run (\"\"\"\n",
    "SELECT\n",
    "    C.customernumber,\n",
    "    customername,\n",
    "    amount\n",
    "FROM\n",
    "    customers C\n",
    "        INNER JOIN\n",
    "    payments USING (customernumber)\n",
    "WHERE amount > 100000\n",
    "ORDER BY amount DESC\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9. List the value of 'On Hold' orders"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ordernumber</th>\n",
       "      <th>customernumber</th>\n",
       "      <th>orderdate</th>\n",
       "      <th>requireddate</th>\n",
       "      <th>productcode</th>\n",
       "      <th>comments</th>\n",
       "      <th>status</th>\n",
       "      <th>jumlahnilai</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10334</td>\n",
       "      <td>144</td>\n",
       "      <td>2004-11-19</td>\n",
       "      <td>2004-11-28</td>\n",
       "      <td>S10_4962</td>\n",
       "      <td>The outstaniding balance for this customer exc...</td>\n",
       "      <td>On Hold</td>\n",
       "      <td>23014.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10401</td>\n",
       "      <td>328</td>\n",
       "      <td>2005-04-03</td>\n",
       "      <td>2005-04-14</td>\n",
       "      <td>S18_2581</td>\n",
       "      <td>Customer credit limit exceeded. Will ship when...</td>\n",
       "      <td>On Hold</td>\n",
       "      <td>43525.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10407</td>\n",
       "      <td>450</td>\n",
       "      <td>2005-04-22</td>\n",
       "      <td>2005-05-04</td>\n",
       "      <td>S18_1589</td>\n",
       "      <td>Customer credit limit exceeded. Will ship when...</td>\n",
       "      <td>On Hold</td>\n",
       "      <td>52229.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10414</td>\n",
       "      <td>362</td>\n",
       "      <td>2005-05-06</td>\n",
       "      <td>2005-05-13</td>\n",
       "      <td>S10_4757</td>\n",
       "      <td>Customer credit limit exceeded. Will ship when...</td>\n",
       "      <td>On Hold</td>\n",
       "      <td>50806.85</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ordernumber  customernumber   orderdate requireddate productcode  \\\n",
       "0        10334             144  2004-11-19   2004-11-28    S10_4962   \n",
       "1        10401             328  2005-04-03   2005-04-14    S18_2581   \n",
       "2        10407             450  2005-04-22   2005-05-04    S18_1589   \n",
       "3        10414             362  2005-05-06   2005-05-13    S10_4757   \n",
       "\n",
       "                                            comments   status  jumlahnilai  \n",
       "0  The outstaniding balance for this customer exc...  On Hold     23014.17  \n",
       "1  Customer credit limit exceeded. Will ship when...  On Hold     43525.04  \n",
       "2  Customer credit limit exceeded. Will ship when...  On Hold     52229.55  \n",
       "3  Customer credit limit exceeded. Will ship when...  On Hold     50806.85  "
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "run(\"\"\"\n",
    "SELECT \n",
    "    od.ordernumber,\n",
    "    customernumber,\n",
    "    orderdate,\n",
    "    requireddate,\n",
    "    productcode,\n",
    "    comments,\n",
    "    status,\n",
    "    SUM(quantityordered * priceeach) as jumlahnilai\n",
    "FROM \n",
    "    orders o\n",
    "        INNER JOIN\n",
    "    orderdetails Od using (ordernumber)\n",
    "WHERE status = 'on hold'\n",
    "GROUP BY ordernumber\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10. Report the number of orders 'On Hold' for each customer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customernumber</th>\n",
       "      <th>customername</th>\n",
       "      <th>ordernumber</th>\n",
       "      <th>status</th>\n",
       "      <th>comments</th>\n",
       "      <th>jumlahorder_onhold</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>144</td>\n",
       "      <td>Volvo Model Replicas, Co</td>\n",
       "      <td>10334</td>\n",
       "      <td>On Hold</td>\n",
       "      <td>The outstaniding balance for this customer exc...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>328</td>\n",
       "      <td>Tekni Collectables Inc.</td>\n",
       "      <td>10401</td>\n",
       "      <td>On Hold</td>\n",
       "      <td>Customer credit limit exceeded. Will ship when...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>362</td>\n",
       "      <td>Gifts4AllAges.com</td>\n",
       "      <td>10414</td>\n",
       "      <td>On Hold</td>\n",
       "      <td>Customer credit limit exceeded. Will ship when...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>450</td>\n",
       "      <td>The Sharp Gifts Warehouse</td>\n",
       "      <td>10407</td>\n",
       "      <td>On Hold</td>\n",
       "      <td>Customer credit limit exceeded. Will ship when...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customernumber               customername  ordernumber   status  \\\n",
       "0             144   Volvo Model Replicas, Co        10334  On Hold   \n",
       "1             328    Tekni Collectables Inc.        10401  On Hold   \n",
       "2             362          Gifts4AllAges.com        10414  On Hold   \n",
       "3             450  The Sharp Gifts Warehouse        10407  On Hold   \n",
       "\n",
       "                                            comments  jumlahorder_onhold  \n",
       "0  The outstaniding balance for this customer exc...                   1  \n",
       "1  Customer credit limit exceeded. Will ship when...                   1  \n",
       "2  Customer credit limit exceeded. Will ship when...                   1  \n",
       "3  Customer credit limit exceeded. Will ship when...                   1  "
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "run (\"\"\"\n",
    "SELECT\n",
    "    c.customernumber,\n",
    "    customername,\n",
    "    ordernumber,\n",
    "    status,\n",
    "    comments,\n",
    "    count(*) as jumlahorder_onhold\n",
    "FROM\n",
    "    customers C\n",
    "        INNER JOIN\n",
    "    orders O using (customernumber)\n",
    "WHERE status = 'on hold'\n",
    "GROUP BY customernumber\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Many to many relationship"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. List products sold by order date."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>productcode</th>\n",
       "      <th>productname</th>\n",
       "      <th>ordernumber</th>\n",
       "      <th>orderdate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>S18_1749</td>\n",
       "      <td>1917 Grand Touring Sedan</td>\n",
       "      <td>10100</td>\n",
       "      <td>2003-01-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>S18_2325</td>\n",
       "      <td>1932 Model A Ford J-Coupe</td>\n",
       "      <td>10101</td>\n",
       "      <td>2003-01-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>S18_1342</td>\n",
       "      <td>1937 Lincoln Berline</td>\n",
       "      <td>10102</td>\n",
       "      <td>2003-01-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>S10_1949</td>\n",
       "      <td>1952 Alpine Renault 1300</td>\n",
       "      <td>10103</td>\n",
       "      <td>2003-01-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>S12_3148</td>\n",
       "      <td>1969 Corvair Monza</td>\n",
       "      <td>10104</td>\n",
       "      <td>2003-01-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>S10_4757</td>\n",
       "      <td>1972 Alfa Romeo GTA</td>\n",
       "      <td>10105</td>\n",
       "      <td>2003-02-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>S18_1662</td>\n",
       "      <td>1980s Black Hawk Helicopter</td>\n",
       "      <td>10106</td>\n",
       "      <td>2003-02-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>S10_1678</td>\n",
       "      <td>1969 Harley Davidson Ultimate Chopper</td>\n",
       "      <td>10107</td>\n",
       "      <td>2003-02-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>S12_1099</td>\n",
       "      <td>1968 Ford Mustang</td>\n",
       "      <td>10108</td>\n",
       "      <td>2003-03-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>S18_1129</td>\n",
       "      <td>1993 Mazda RX-7</td>\n",
       "      <td>10109</td>\n",
       "      <td>2003-03-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>S18_1589</td>\n",
       "      <td>1965 Aston Martin DB5</td>\n",
       "      <td>10110</td>\n",
       "      <td>2003-03-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>S10_1949</td>\n",
       "      <td>1952 Alpine Renault 1300</td>\n",
       "      <td>10112</td>\n",
       "      <td>2003-03-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>S18_1342</td>\n",
       "      <td>1937 Lincoln Berline</td>\n",
       "      <td>10111</td>\n",
       "      <td>2003-03-25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>S12_1666</td>\n",
       "      <td>1958 Setra Bus</td>\n",
       "      <td>10113</td>\n",
       "      <td>2003-03-26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>S10_4962</td>\n",
       "      <td>1962 LanciaA Delta 16V</td>\n",
       "      <td>10114</td>\n",
       "      <td>2003-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>S12_4473</td>\n",
       "      <td>1957 Chevy Pickup</td>\n",
       "      <td>10115</td>\n",
       "      <td>2003-04-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>S32_3207</td>\n",
       "      <td>1950's Chicago Surface Lines Streetcar</td>\n",
       "      <td>10116</td>\n",
       "      <td>2003-04-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>S12_1108</td>\n",
       "      <td>2001 Ferrari Enzo</td>\n",
       "      <td>10117</td>\n",
       "      <td>2003-04-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>S700_3505</td>\n",
       "      <td>The Titanic</td>\n",
       "      <td>10118</td>\n",
       "      <td>2003-04-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>S10_4757</td>\n",
       "      <td>1972 Alfa Romeo GTA</td>\n",
       "      <td>10119</td>\n",
       "      <td>2003-04-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>S10_2016</td>\n",
       "      <td>1996 Moto Guzzi 1100i</td>\n",
       "      <td>10120</td>\n",
       "      <td>2003-04-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>S10_1678</td>\n",
       "      <td>1969 Harley Davidson Ultimate Chopper</td>\n",
       "      <td>10121</td>\n",
       "      <td>2003-05-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>S12_1099</td>\n",
       "      <td>1968 Ford Mustang</td>\n",
       "      <td>10122</td>\n",
       "      <td>2003-05-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>S18_1589</td>\n",
       "      <td>1965 Aston Martin DB5</td>\n",
       "      <td>10123</td>\n",
       "      <td>2003-05-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>S18_1749</td>\n",
       "      <td>1917 Grand Touring Sedan</td>\n",
       "      <td>10124</td>\n",
       "      <td>2003-05-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>S18_1342</td>\n",
       "      <td>1937 Lincoln Berline</td>\n",
       "      <td>10125</td>\n",
       "      <td>2003-05-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>S10_1949</td>\n",
       "      <td>1952 Alpine Renault 1300</td>\n",
       "      <td>10126</td>\n",
       "      <td>2003-05-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>S12_1108</td>\n",
       "      <td>2001 Ferrari Enzo</td>\n",
       "      <td>10127</td>\n",
       "      <td>2003-06-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>S18_3140</td>\n",
       "      <td>1903 Ford Model A</td>\n",
       "      <td>10128</td>\n",
       "      <td>2003-06-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>S10_4757</td>\n",
       "      <td>1972 Alfa Romeo GTA</td>\n",
       "      <td>10129</td>\n",
       "      <td>2003-06-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>296</th>\n",
       "      <td>S12_3891</td>\n",
       "      <td>1969 Ford Falcon</td>\n",
       "      <td>10396</td>\n",
       "      <td>2005-03-23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>297</th>\n",
       "      <td>S700_1938</td>\n",
       "      <td>The Mayflower</td>\n",
       "      <td>10397</td>\n",
       "      <td>2005-03-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>298</th>\n",
       "      <td>S18_1662</td>\n",
       "      <td>1980s Black Hawk Helicopter</td>\n",
       "      <td>10398</td>\n",
       "      <td>2005-03-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>299</th>\n",
       "      <td>S10_4757</td>\n",
       "      <td>1972 Alfa Romeo GTA</td>\n",
       "      <td>10400</td>\n",
       "      <td>2005-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300</th>\n",
       "      <td>S10_1678</td>\n",
       "      <td>1969 Harley Davidson Ultimate Chopper</td>\n",
       "      <td>10399</td>\n",
       "      <td>2005-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301</th>\n",
       "      <td>S18_2581</td>\n",
       "      <td>P-51-D Mustang</td>\n",
       "      <td>10401</td>\n",
       "      <td>2005-04-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>302</th>\n",
       "      <td>S10_2016</td>\n",
       "      <td>1996 Moto Guzzi 1100i</td>\n",
       "      <td>10402</td>\n",
       "      <td>2005-04-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>303</th>\n",
       "      <td>S10_1678</td>\n",
       "      <td>1969 Harley Davidson Ultimate Chopper</td>\n",
       "      <td>10403</td>\n",
       "      <td>2005-04-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>304</th>\n",
       "      <td>S12_1099</td>\n",
       "      <td>1968 Ford Mustang</td>\n",
       "      <td>10404</td>\n",
       "      <td>2005-04-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>305</th>\n",
       "      <td>S12_4675</td>\n",
       "      <td>1969 Dodge Charger</td>\n",
       "      <td>10405</td>\n",
       "      <td>2005-04-14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>306</th>\n",
       "      <td>S18_1129</td>\n",
       "      <td>1993 Mazda RX-7</td>\n",
       "      <td>10406</td>\n",
       "      <td>2005-04-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>307</th>\n",
       "      <td>S24_3969</td>\n",
       "      <td>1936 Mercedes Benz 500k Roadster</td>\n",
       "      <td>10408</td>\n",
       "      <td>2005-04-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>308</th>\n",
       "      <td>S18_1589</td>\n",
       "      <td>1965 Aston Martin DB5</td>\n",
       "      <td>10407</td>\n",
       "      <td>2005-04-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>309</th>\n",
       "      <td>S18_2325</td>\n",
       "      <td>1932 Model A Ford J-Coupe</td>\n",
       "      <td>10409</td>\n",
       "      <td>2005-04-23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>310</th>\n",
       "      <td>S18_1342</td>\n",
       "      <td>1937 Lincoln Berline</td>\n",
       "      <td>10410</td>\n",
       "      <td>2005-04-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>311</th>\n",
       "      <td>S10_1949</td>\n",
       "      <td>1952 Alpine Renault 1300</td>\n",
       "      <td>10411</td>\n",
       "      <td>2005-05-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>312</th>\n",
       "      <td>S12_4473</td>\n",
       "      <td>1957 Chevy Pickup</td>\n",
       "      <td>10412</td>\n",
       "      <td>2005-05-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>313</th>\n",
       "      <td>S12_1108</td>\n",
       "      <td>2001 Ferrari Enzo</td>\n",
       "      <td>10413</td>\n",
       "      <td>2005-05-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>314</th>\n",
       "      <td>S10_4757</td>\n",
       "      <td>1972 Alfa Romeo GTA</td>\n",
       "      <td>10414</td>\n",
       "      <td>2005-05-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>315</th>\n",
       "      <td>S18_3856</td>\n",
       "      <td>1941 Chevrolet Special Deluxe Cabriolet</td>\n",
       "      <td>10415</td>\n",
       "      <td>2005-05-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>316</th>\n",
       "      <td>S18_1662</td>\n",
       "      <td>1980s Black Hawk Helicopter</td>\n",
       "      <td>10416</td>\n",
       "      <td>2005-05-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>317</th>\n",
       "      <td>S10_1678</td>\n",
       "      <td>1969 Harley Davidson Ultimate Chopper</td>\n",
       "      <td>10417</td>\n",
       "      <td>2005-05-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>318</th>\n",
       "      <td>S18_3278</td>\n",
       "      <td>1969 Dodge Super Bee</td>\n",
       "      <td>10418</td>\n",
       "      <td>2005-05-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>319</th>\n",
       "      <td>S12_1099</td>\n",
       "      <td>1968 Ford Mustang</td>\n",
       "      <td>10419</td>\n",
       "      <td>2005-05-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>320</th>\n",
       "      <td>S18_2795</td>\n",
       "      <td>1928 Mercedes-Benz SSK</td>\n",
       "      <td>10421</td>\n",
       "      <td>2005-05-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>321</th>\n",
       "      <td>S18_1749</td>\n",
       "      <td>1917 Grand Touring Sedan</td>\n",
       "      <td>10420</td>\n",
       "      <td>2005-05-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>322</th>\n",
       "      <td>S18_1342</td>\n",
       "      <td>1937 Lincoln Berline</td>\n",
       "      <td>10422</td>\n",
       "      <td>2005-05-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>323</th>\n",
       "      <td>S18_2949</td>\n",
       "      <td>1913 Ford Model T Speedster</td>\n",
       "      <td>10423</td>\n",
       "      <td>2005-05-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>324</th>\n",
       "      <td>S10_4962</td>\n",
       "      <td>1962 LanciaA Delta 16V</td>\n",
       "      <td>10425</td>\n",
       "      <td>2005-05-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>325</th>\n",
       "      <td>S10_1949</td>\n",
       "      <td>1952 Alpine Renault 1300</td>\n",
       "      <td>10424</td>\n",
       "      <td>2005-05-31</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>326 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    productcode                              productname  ordernumber  \\\n",
       "0      S18_1749                 1917 Grand Touring Sedan        10100   \n",
       "1      S18_2325                1932 Model A Ford J-Coupe        10101   \n",
       "2      S18_1342                     1937 Lincoln Berline        10102   \n",
       "3      S10_1949                 1952 Alpine Renault 1300        10103   \n",
       "4      S12_3148                       1969 Corvair Monza        10104   \n",
       "5      S10_4757                      1972 Alfa Romeo GTA        10105   \n",
       "6      S18_1662              1980s Black Hawk Helicopter        10106   \n",
       "7      S10_1678    1969 Harley Davidson Ultimate Chopper        10107   \n",
       "8      S12_1099                        1968 Ford Mustang        10108   \n",
       "9      S18_1129                          1993 Mazda RX-7        10109   \n",
       "10     S18_1589                    1965 Aston Martin DB5        10110   \n",
       "11     S10_1949                 1952 Alpine Renault 1300        10112   \n",
       "12     S18_1342                     1937 Lincoln Berline        10111   \n",
       "13     S12_1666                           1958 Setra Bus        10113   \n",
       "14     S10_4962                   1962 LanciaA Delta 16V        10114   \n",
       "15     S12_4473                        1957 Chevy Pickup        10115   \n",
       "16     S32_3207   1950's Chicago Surface Lines Streetcar        10116   \n",
       "17     S12_1108                        2001 Ferrari Enzo        10117   \n",
       "18    S700_3505                              The Titanic        10118   \n",
       "19     S10_4757                      1972 Alfa Romeo GTA        10119   \n",
       "20     S10_2016                    1996 Moto Guzzi 1100i        10120   \n",
       "21     S10_1678    1969 Harley Davidson Ultimate Chopper        10121   \n",
       "22     S12_1099                        1968 Ford Mustang        10122   \n",
       "23     S18_1589                    1965 Aston Martin DB5        10123   \n",
       "24     S18_1749                 1917 Grand Touring Sedan        10124   \n",
       "25     S18_1342                     1937 Lincoln Berline        10125   \n",
       "26     S10_1949                 1952 Alpine Renault 1300        10126   \n",
       "27     S12_1108                        2001 Ferrari Enzo        10127   \n",
       "28     S18_3140                        1903 Ford Model A        10128   \n",
       "29     S10_4757                      1972 Alfa Romeo GTA        10129   \n",
       "..          ...                                      ...          ...   \n",
       "296    S12_3891                         1969 Ford Falcon        10396   \n",
       "297   S700_1938                            The Mayflower        10397   \n",
       "298    S18_1662              1980s Black Hawk Helicopter        10398   \n",
       "299    S10_4757                      1972 Alfa Romeo GTA        10400   \n",
       "300    S10_1678    1969 Harley Davidson Ultimate Chopper        10399   \n",
       "301    S18_2581                           P-51-D Mustang        10401   \n",
       "302    S10_2016                    1996 Moto Guzzi 1100i        10402   \n",
       "303    S10_1678    1969 Harley Davidson Ultimate Chopper        10403   \n",
       "304    S12_1099                        1968 Ford Mustang        10404   \n",
       "305    S12_4675                       1969 Dodge Charger        10405   \n",
       "306    S18_1129                          1993 Mazda RX-7        10406   \n",
       "307    S24_3969         1936 Mercedes Benz 500k Roadster        10408   \n",
       "308    S18_1589                    1965 Aston Martin DB5        10407   \n",
       "309    S18_2325                1932 Model A Ford J-Coupe        10409   \n",
       "310    S18_1342                     1937 Lincoln Berline        10410   \n",
       "311    S10_1949                 1952 Alpine Renault 1300        10411   \n",
       "312    S12_4473                        1957 Chevy Pickup        10412   \n",
       "313    S12_1108                        2001 Ferrari Enzo        10413   \n",
       "314    S10_4757                      1972 Alfa Romeo GTA        10414   \n",
       "315    S18_3856  1941 Chevrolet Special Deluxe Cabriolet        10415   \n",
       "316    S18_1662              1980s Black Hawk Helicopter        10416   \n",
       "317    S10_1678    1969 Harley Davidson Ultimate Chopper        10417   \n",
       "318    S18_3278                     1969 Dodge Super Bee        10418   \n",
       "319    S12_1099                        1968 Ford Mustang        10419   \n",
       "320    S18_2795                   1928 Mercedes-Benz SSK        10421   \n",
       "321    S18_1749                 1917 Grand Touring Sedan        10420   \n",
       "322    S18_1342                     1937 Lincoln Berline        10422   \n",
       "323    S18_2949              1913 Ford Model T Speedster        10423   \n",
       "324    S10_4962                   1962 LanciaA Delta 16V        10425   \n",
       "325    S10_1949                 1952 Alpine Renault 1300        10424   \n",
       "\n",
       "      orderdate  \n",
       "0    2003-01-06  \n",
       "1    2003-01-09  \n",
       "2    2003-01-10  \n",
       "3    2003-01-29  \n",
       "4    2003-01-31  \n",
       "5    2003-02-11  \n",
       "6    2003-02-17  \n",
       "7    2003-02-24  \n",
       "8    2003-03-03  \n",
       "9    2003-03-10  \n",
       "10   2003-03-18  \n",
       "11   2003-03-24  \n",
       "12   2003-03-25  \n",
       "13   2003-03-26  \n",
       "14   2003-04-01  \n",
       "15   2003-04-04  \n",
       "16   2003-04-11  \n",
       "17   2003-04-16  \n",
       "18   2003-04-21  \n",
       "19   2003-04-28  \n",
       "20   2003-04-29  \n",
       "21   2003-05-07  \n",
       "22   2003-05-08  \n",
       "23   2003-05-20  \n",
       "24   2003-05-21  \n",
       "25   2003-05-21  \n",
       "26   2003-05-28  \n",
       "27   2003-06-03  \n",
       "28   2003-06-06  \n",
       "29   2003-06-12  \n",
       "..          ...  \n",
       "296  2005-03-23  \n",
       "297  2005-03-28  \n",
       "298  2005-03-30  \n",
       "299  2005-04-01  \n",
       "300  2005-04-01  \n",
       "301  2005-04-03  \n",
       "302  2005-04-07  \n",
       "303  2005-04-08  \n",
       "304  2005-04-08  \n",
       "305  2005-04-14  \n",
       "306  2005-04-15  \n",
       "307  2005-04-22  \n",
       "308  2005-04-22  \n",
       "309  2005-04-23  \n",
       "310  2005-04-29  \n",
       "311  2005-05-01  \n",
       "312  2005-05-03  \n",
       "313  2005-05-05  \n",
       "314  2005-05-06  \n",
       "315  2005-05-09  \n",
       "316  2005-05-10  \n",
       "317  2005-05-13  \n",
       "318  2005-05-16  \n",
       "319  2005-05-17  \n",
       "320  2005-05-29  \n",
       "321  2005-05-29  \n",
       "322  2005-05-30  \n",
       "323  2005-05-30  \n",
       "324  2005-05-31  \n",
       "325  2005-05-31  \n",
       "\n",
       "[326 rows x 4 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "run(\"\"\"\n",
    "SELECT\n",
    "    p.productcode,\n",
    "    productname,\n",
    "    ordernumber,\n",
    "    orderdate\n",
    "    \n",
    "FROM\n",
    "    products p\n",
    "        INNER JOIN\n",
    "    orderdetails od USING(productcode)\n",
    "        INNER JOIN \n",
    "    orders o USING (ordernumber)\n",
    "GROUP BY ordernumber\n",
    "ORDER BY orderdate\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. List the order dates in descending order for orders for the 1940 Ford Pickup Truck."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>productcode</th>\n",
       "      <th>productname</th>\n",
       "      <th>orderdate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>S18_1097</td>\n",
       "      <td>1940 Ford Pickup Truck</td>\n",
       "      <td>2005-05-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>S18_1097</td>\n",
       "      <td>1940 Ford Pickup Truck</td>\n",
       "      <td>2005-05-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>S18_1097</td>\n",
       "      <td>1940 Ford Pickup Truck</td>\n",
       "      <td>2005-03-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>S18_1097</td>\n",
       "      <td>1940 Ford Pickup Truck</td>\n",
       "      <td>2005-02-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>S18_1097</td>\n",
       "      <td>1940 Ford Pickup Truck</td>\n",
       "      <td>2005-01-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>S18_1097</td>\n",
       "      <td>1940 Ford Pickup Truck</td>\n",
       "      <td>2004-12-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>S18_1097</td>\n",
       "      <td>1940 Ford Pickup Truck</td>\n",
       "      <td>2004-11-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>S18_1097</td>\n",
       "      <td>1940 Ford Pickup Truck</td>\n",
       "      <td>2004-11-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>S18_1097</td>\n",
       "      <td>1940 Ford Pickup Truck</td>\n",
       "      <td>2004-11-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>S18_1097</td>\n",
       "      <td>1940 Ford Pickup Truck</td>\n",
       "      <td>2004-10-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>S18_1097</td>\n",
       "      <td>1940 Ford Pickup Truck</td>\n",
       "      <td>2004-10-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>S18_1097</td>\n",
       "      <td>1940 Ford Pickup Truck</td>\n",
       "      <td>2004-09-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>S18_1097</td>\n",
       "      <td>1940 Ford Pickup Truck</td>\n",
       "      <td>2004-08-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>S18_1097</td>\n",
       "      <td>1940 Ford Pickup Truck</td>\n",
       "      <td>2004-07-19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>S18_1097</td>\n",
       "      <td>1940 Ford Pickup Truck</td>\n",
       "      <td>2004-06-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>S18_1097</td>\n",
       "      <td>1940 Ford Pickup Truck</td>\n",
       "      <td>2004-05-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>S18_1097</td>\n",
       "      <td>1940 Ford Pickup Truck</td>\n",
       "      <td>2004-03-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>S18_1097</td>\n",
       "      <td>1940 Ford Pickup Truck</td>\n",
       "      <td>2004-01-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>S18_1097</td>\n",
       "      <td>1940 Ford Pickup Truck</td>\n",
       "      <td>2003-12-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>S18_1097</td>\n",
       "      <td>1940 Ford Pickup Truck</td>\n",
       "      <td>2003-11-25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>S18_1097</td>\n",
       "      <td>1940 Ford Pickup Truck</td>\n",
       "      <td>2003-11-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>S18_1097</td>\n",
       "      <td>1940 Ford Pickup Truck</td>\n",
       "      <td>2003-11-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>S18_1097</td>\n",
       "      <td>1940 Ford Pickup Truck</td>\n",
       "      <td>2003-10-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>S18_1097</td>\n",
       "      <td>1940 Ford Pickup Truck</td>\n",
       "      <td>2003-09-19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>S18_1097</td>\n",
       "      <td>1940 Ford Pickup Truck</td>\n",
       "      <td>2003-07-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>S18_1097</td>\n",
       "      <td>1940 Ford Pickup Truck</td>\n",
       "      <td>2003-05-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>S18_1097</td>\n",
       "      <td>1940 Ford Pickup Truck</td>\n",
       "      <td>2003-03-26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>S18_1097</td>\n",
       "      <td>1940 Ford Pickup Truck</td>\n",
       "      <td>2003-01-29</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   productcode             productname   orderdate\n",
       "0     S18_1097  1940 Ford Pickup Truck  2005-05-31\n",
       "1     S18_1097  1940 Ford Pickup Truck  2005-05-01\n",
       "2     S18_1097  1940 Ford Pickup Truck  2005-03-09\n",
       "3     S18_1097  1940 Ford Pickup Truck  2005-02-17\n",
       "4     S18_1097  1940 Ford Pickup Truck  2005-01-20\n",
       "5     S18_1097  1940 Ford Pickup Truck  2004-12-10\n",
       "6     S18_1097  1940 Ford Pickup Truck  2004-11-29\n",
       "7     S18_1097  1940 Ford Pickup Truck  2004-11-18\n",
       "8     S18_1097  1940 Ford Pickup Truck  2004-11-04\n",
       "9     S18_1097  1940 Ford Pickup Truck  2004-10-21\n",
       "10    S18_1097  1940 Ford Pickup Truck  2004-10-11\n",
       "11    S18_1097  1940 Ford Pickup Truck  2004-09-08\n",
       "12    S18_1097  1940 Ford Pickup Truck  2004-08-17\n",
       "13    S18_1097  1940 Ford Pickup Truck  2004-07-19\n",
       "14    S18_1097  1940 Ford Pickup Truck  2004-06-15\n",
       "15    S18_1097  1940 Ford Pickup Truck  2004-05-04\n",
       "16    S18_1097  1940 Ford Pickup Truck  2004-03-10\n",
       "17    S18_1097  1940 Ford Pickup Truck  2004-01-29\n",
       "18    S18_1097  1940 Ford Pickup Truck  2003-12-05\n",
       "19    S18_1097  1940 Ford Pickup Truck  2003-11-25\n",
       "20    S18_1097  1940 Ford Pickup Truck  2003-11-13\n",
       "21    S18_1097  1940 Ford Pickup Truck  2003-11-06\n",
       "22    S18_1097  1940 Ford Pickup Truck  2003-10-21\n",
       "23    S18_1097  1940 Ford Pickup Truck  2003-09-19\n",
       "24    S18_1097  1940 Ford Pickup Truck  2003-07-24\n",
       "25    S18_1097  1940 Ford Pickup Truck  2003-05-28\n",
       "26    S18_1097  1940 Ford Pickup Truck  2003-03-26\n",
       "27    S18_1097  1940 Ford Pickup Truck  2003-01-29"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "run(\"\"\"\n",
    "SELECT\n",
    "    productcode,\n",
    "    productname,\n",
    "    orderdate\n",
    "FROM\n",
    "    products \n",
    "        INNER JOIN\n",
    "    orderdetails USING (productcode)\n",
    "        INNER JOIN\n",
    "    orders USING (ordernumber)\n",
    "WHERE productname = '1940 Ford Pickup Truck'\n",
    "GROUP BY orderdate\n",
    "ORDER BY orderdate DESC\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. List the names of customers and their corresponding order number where a particular order from that customer has a value greater than $25,000?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customername</th>\n",
       "      <th>ordernumber</th>\n",
       "      <th>particular_order</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Baane Mini Imports</td>\n",
       "      <td>10103</td>\n",
       "      <td>50218.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Euro+ Shopping Channel</td>\n",
       "      <td>10104</td>\n",
       "      <td>40206.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Danish Wholesale Imports</td>\n",
       "      <td>10105</td>\n",
       "      <td>53959.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Rovelli Gifts</td>\n",
       "      <td>10106</td>\n",
       "      <td>52151.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Cruz &amp; Sons Co.</td>\n",
       "      <td>10108</td>\n",
       "      <td>51001.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Motor Mint Distributors Inc.</td>\n",
       "      <td>10109</td>\n",
       "      <td>25833.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>AV Stores, Co.</td>\n",
       "      <td>10110</td>\n",
       "      <td>48425.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>La Corne D'abondance, Co.</td>\n",
       "      <td>10114</td>\n",
       "      <td>33383.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Dragon Souveniers, Ltd.</td>\n",
       "      <td>10117</td>\n",
       "      <td>44380.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Salzburg Collectables</td>\n",
       "      <td>10119</td>\n",
       "      <td>35826.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Australian Collectors, Co.</td>\n",
       "      <td>10120</td>\n",
       "      <td>45864.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Marseille Mini Autos</td>\n",
       "      <td>10122</td>\n",
       "      <td>50824.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Signal Gift Stores</td>\n",
       "      <td>10124</td>\n",
       "      <td>32641.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Corrida Auto Replicas, Ltd</td>\n",
       "      <td>10126</td>\n",
       "      <td>57131.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Muscle Machine Inc</td>\n",
       "      <td>10127</td>\n",
       "      <td>58841.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Stylish Desk Decors, Co.</td>\n",
       "      <td>10129</td>\n",
       "      <td>29429.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Mini Gifts Distributors Ltd.</td>\n",
       "      <td>10135</td>\n",
       "      <td>55601.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Kelly's Gift Shop</td>\n",
       "      <td>10138</td>\n",
       "      <td>32077.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Technics Stores Inc.</td>\n",
       "      <td>10140</td>\n",
       "      <td>38675.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Suominen Souveniers</td>\n",
       "      <td>10141</td>\n",
       "      <td>29716.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Mini Gifts Distributors Ltd.</td>\n",
       "      <td>10142</td>\n",
       "      <td>56052.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Mini Creations Ltd.</td>\n",
       "      <td>10143</td>\n",
       "      <td>41016.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Toys4GrownUps.com</td>\n",
       "      <td>10145</td>\n",
       "      <td>50342.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Collectables For Less Inc.</td>\n",
       "      <td>10147</td>\n",
       "      <td>32680.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Anna's Decorations, Ltd</td>\n",
       "      <td>10148</td>\n",
       "      <td>41554.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Signal Collectibles Ltd.</td>\n",
       "      <td>10149</td>\n",
       "      <td>29997.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Dragon Souveniers, Ltd.</td>\n",
       "      <td>10150</td>\n",
       "      <td>38350.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Oulu Toy Supplies, Inc.</td>\n",
       "      <td>10151</td>\n",
       "      <td>32723.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Euro+ Shopping Channel</td>\n",
       "      <td>10153</td>\n",
       "      <td>44939.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Toys of Finland, Co.</td>\n",
       "      <td>10155</td>\n",
       "      <td>37602.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>162</th>\n",
       "      <td>Euro+ Shopping Channel</td>\n",
       "      <td>10380</td>\n",
       "      <td>34404.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>163</th>\n",
       "      <td>Corporate Gift Ideas Co.</td>\n",
       "      <td>10381</td>\n",
       "      <td>32626.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>164</th>\n",
       "      <td>Mini Gifts Distributors Ltd.</td>\n",
       "      <td>10382</td>\n",
       "      <td>47765.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>165</th>\n",
       "      <td>Euro+ Shopping Channel</td>\n",
       "      <td>10383</td>\n",
       "      <td>36851.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166</th>\n",
       "      <td>Euro+ Shopping Channel</td>\n",
       "      <td>10386</td>\n",
       "      <td>46968.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>167</th>\n",
       "      <td>FunGiftIdeas.com</td>\n",
       "      <td>10388</td>\n",
       "      <td>30293.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>168</th>\n",
       "      <td>Scandinavian Gift Ideas</td>\n",
       "      <td>10389</td>\n",
       "      <td>27966.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>169</th>\n",
       "      <td>Mini Gifts Distributors Ltd.</td>\n",
       "      <td>10390</td>\n",
       "      <td>55902.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>170</th>\n",
       "      <td>Anna's Decorations, Ltd</td>\n",
       "      <td>10391</td>\n",
       "      <td>29848.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171</th>\n",
       "      <td>Down Under Souveniers, Inc</td>\n",
       "      <td>10393</td>\n",
       "      <td>33593.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172</th>\n",
       "      <td>Mini Gifts Distributors Ltd.</td>\n",
       "      <td>10396</td>\n",
       "      <td>27695.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>173</th>\n",
       "      <td>Reims Collectables</td>\n",
       "      <td>10398</td>\n",
       "      <td>46656.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>174</th>\n",
       "      <td>Kelly's Gift Shop</td>\n",
       "      <td>10399</td>\n",
       "      <td>30253.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>175</th>\n",
       "      <td>The Sharp Gifts Warehouse</td>\n",
       "      <td>10400</td>\n",
       "      <td>31755.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>176</th>\n",
       "      <td>Tekni Collectables Inc.</td>\n",
       "      <td>10401</td>\n",
       "      <td>43525.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>177</th>\n",
       "      <td>UK Collectables, Ltd.</td>\n",
       "      <td>10403</td>\n",
       "      <td>37258.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>178</th>\n",
       "      <td>Down Under Souveniers, Inc</td>\n",
       "      <td>10404</td>\n",
       "      <td>41426.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179</th>\n",
       "      <td>Mini Caravy</td>\n",
       "      <td>10405</td>\n",
       "      <td>35157.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>180</th>\n",
       "      <td>The Sharp Gifts Warehouse</td>\n",
       "      <td>10407</td>\n",
       "      <td>52229.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>181</th>\n",
       "      <td>GiftsForHim.com</td>\n",
       "      <td>10410</td>\n",
       "      <td>36442.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>182</th>\n",
       "      <td>Québec Home Shopping Network</td>\n",
       "      <td>10411</td>\n",
       "      <td>29070.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>183</th>\n",
       "      <td>Euro+ Shopping Channel</td>\n",
       "      <td>10412</td>\n",
       "      <td>46895.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>184</th>\n",
       "      <td>Gift Depot Inc.</td>\n",
       "      <td>10413</td>\n",
       "      <td>28500.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185</th>\n",
       "      <td>Gifts4AllAges.com</td>\n",
       "      <td>10414</td>\n",
       "      <td>50806.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>186</th>\n",
       "      <td>L'ordine Souveniers</td>\n",
       "      <td>10416</td>\n",
       "      <td>35362.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>187</th>\n",
       "      <td>Euro+ Shopping Channel</td>\n",
       "      <td>10417</td>\n",
       "      <td>28574.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>188</th>\n",
       "      <td>Salzburg Collectables</td>\n",
       "      <td>10419</td>\n",
       "      <td>52420.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>189</th>\n",
       "      <td>Souveniers And Things Co.</td>\n",
       "      <td>10420</td>\n",
       "      <td>42251.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190</th>\n",
       "      <td>Euro+ Shopping Channel</td>\n",
       "      <td>10424</td>\n",
       "      <td>29310.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>191</th>\n",
       "      <td>La Rochelle Gifts</td>\n",
       "      <td>10425</td>\n",
       "      <td>41623.44</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>192 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     customername  ordernumber  particular_order\n",
       "0              Baane Mini Imports        10103          50218.95\n",
       "1          Euro+ Shopping Channel        10104          40206.20\n",
       "2        Danish Wholesale Imports        10105          53959.21\n",
       "3                   Rovelli Gifts        10106          52151.81\n",
       "4                 Cruz & Sons Co.        10108          51001.22\n",
       "5    Motor Mint Distributors Inc.        10109          25833.14\n",
       "6                  AV Stores, Co.        10110          48425.69\n",
       "7       La Corne D'abondance, Co.        10114          33383.14\n",
       "8         Dragon Souveniers, Ltd.        10117          44380.15\n",
       "9           Salzburg Collectables        10119          35826.33\n",
       "10     Australian Collectors, Co.        10120          45864.03\n",
       "11           Marseille Mini Autos        10122          50824.66\n",
       "12             Signal Gift Stores        10124          32641.98\n",
       "13     Corrida Auto Replicas, Ltd        10126          57131.92\n",
       "14             Muscle Machine Inc        10127          58841.35\n",
       "15       Stylish Desk Decors, Co.        10129          29429.14\n",
       "16   Mini Gifts Distributors Ltd.        10135          55601.84\n",
       "17              Kelly's Gift Shop        10138          32077.44\n",
       "18           Technics Stores Inc.        10140          38675.13\n",
       "19            Suominen Souveniers        10141          29716.86\n",
       "20   Mini Gifts Distributors Ltd.        10142          56052.56\n",
       "21            Mini Creations Ltd.        10143          41016.75\n",
       "22              Toys4GrownUps.com        10145          50342.74\n",
       "23     Collectables For Less Inc.        10147          32680.31\n",
       "24        Anna's Decorations, Ltd        10148          41554.73\n",
       "25       Signal Collectibles Ltd.        10149          29997.09\n",
       "26        Dragon Souveniers, Ltd.        10150          38350.15\n",
       "27        Oulu Toy Supplies, Inc.        10151          32723.04\n",
       "28         Euro+ Shopping Channel        10153          44939.85\n",
       "29           Toys of Finland, Co.        10155          37602.48\n",
       "..                            ...          ...               ...\n",
       "162        Euro+ Shopping Channel        10380          34404.21\n",
       "163      Corporate Gift Ideas Co.        10381          32626.09\n",
       "164  Mini Gifts Distributors Ltd.        10382          47765.59\n",
       "165        Euro+ Shopping Channel        10383          36851.98\n",
       "166        Euro+ Shopping Channel        10386          46968.52\n",
       "167              FunGiftIdeas.com        10388          30293.77\n",
       "168       Scandinavian Gift Ideas        10389          27966.54\n",
       "169  Mini Gifts Distributors Ltd.        10390          55902.50\n",
       "170       Anna's Decorations, Ltd        10391          29848.52\n",
       "171    Down Under Souveniers, Inc        10393          33593.32\n",
       "172  Mini Gifts Distributors Ltd.        10396          27695.54\n",
       "173            Reims Collectables        10398          46656.94\n",
       "174             Kelly's Gift Shop        10399          30253.75\n",
       "175     The Sharp Gifts Warehouse        10400          31755.34\n",
       "176       Tekni Collectables Inc.        10401          43525.04\n",
       "177         UK Collectables, Ltd.        10403          37258.94\n",
       "178    Down Under Souveniers, Inc        10404          41426.81\n",
       "179                   Mini Caravy        10405          35157.75\n",
       "180     The Sharp Gifts Warehouse        10407          52229.55\n",
       "181               GiftsForHim.com        10410          36442.34\n",
       "182  Québec Home Shopping Network        10411          29070.38\n",
       "183        Euro+ Shopping Channel        10412          46895.48\n",
       "184               Gift Depot Inc.        10413          28500.78\n",
       "185             Gifts4AllAges.com        10414          50806.85\n",
       "186           L'ordine Souveniers        10416          35362.26\n",
       "187        Euro+ Shopping Channel        10417          28574.90\n",
       "188         Salzburg Collectables        10419          52420.07\n",
       "189     Souveniers And Things Co.        10420          42251.51\n",
       "190        Euro+ Shopping Channel        10424          29310.30\n",
       "191             La Rochelle Gifts        10425          41623.44\n",
       "\n",
       "[192 rows x 3 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "run(\"\"\"\n",
    "SELECT \n",
    "    customername,\n",
    "    ordernumber,\n",
    "    SUM(quantityordered * priceeach) AS particular_order\n",
    "FROM\n",
    "    customers\n",
    "        INNER JOIN\n",
    "    orders USING (customernumber)\n",
    "        INNER JOIN\n",
    "    orderdetails USING (ordernumber)\n",
    "GROUP BY ordernumber\n",
    "HAVING particular_order > 25000\n",
    "    \"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Are there any products that appear on all orders?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "scrolled": true
   },
   "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>productcode</th>\n",
       "      <th>productname</th>\n",
       "      <th>ordernumber</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>S18_1749</td>\n",
       "      <td>1917 Grand Touring Sedan</td>\n",
       "      <td>10100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>S18_2325</td>\n",
       "      <td>1932 Model A Ford J-Coupe</td>\n",
       "      <td>10101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>S18_1342</td>\n",
       "      <td>1937 Lincoln Berline</td>\n",
       "      <td>10102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>S10_1949</td>\n",
       "      <td>1952 Alpine Renault 1300</td>\n",
       "      <td>10103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>S12_3148</td>\n",
       "      <td>1969 Corvair Monza</td>\n",
       "      <td>10104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>S10_4757</td>\n",
       "      <td>1972 Alfa Romeo GTA</td>\n",
       "      <td>10105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>S18_1662</td>\n",
       "      <td>1980s Black Hawk Helicopter</td>\n",
       "      <td>10106</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>S10_1678</td>\n",
       "      <td>1969 Harley Davidson Ultimate Chopper</td>\n",
       "      <td>10107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>S12_1099</td>\n",
       "      <td>1968 Ford Mustang</td>\n",
       "      <td>10108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>S18_1129</td>\n",
       "      <td>1993 Mazda RX-7</td>\n",
       "      <td>10109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>S18_1589</td>\n",
       "      <td>1965 Aston Martin DB5</td>\n",
       "      <td>10110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>S18_1342</td>\n",
       "      <td>1937 Lincoln Berline</td>\n",
       "      <td>10111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>S10_1949</td>\n",
       "      <td>1952 Alpine Renault 1300</td>\n",
       "      <td>10112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>S12_1666</td>\n",
       "      <td>1958 Setra Bus</td>\n",
       "      <td>10113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>S10_4962</td>\n",
       "      <td>1962 LanciaA Delta 16V</td>\n",
       "      <td>10114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>S12_4473</td>\n",
       "      <td>1957 Chevy Pickup</td>\n",
       "      <td>10115</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>S32_3207</td>\n",
       "      <td>1950's Chicago Surface Lines Streetcar</td>\n",
       "      <td>10116</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>S12_1108</td>\n",
       "      <td>2001 Ferrari Enzo</td>\n",
       "      <td>10117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>S700_3505</td>\n",
       "      <td>The Titanic</td>\n",
       "      <td>10118</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>S10_4757</td>\n",
       "      <td>1972 Alfa Romeo GTA</td>\n",
       "      <td>10119</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>S10_2016</td>\n",
       "      <td>1996 Moto Guzzi 1100i</td>\n",
       "      <td>10120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>S10_1678</td>\n",
       "      <td>1969 Harley Davidson Ultimate Chopper</td>\n",
       "      <td>10121</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>S12_1099</td>\n",
       "      <td>1968 Ford Mustang</td>\n",
       "      <td>10122</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>S18_1589</td>\n",
       "      <td>1965 Aston Martin DB5</td>\n",
       "      <td>10123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>S18_1749</td>\n",
       "      <td>1917 Grand Touring Sedan</td>\n",
       "      <td>10124</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>S18_1342</td>\n",
       "      <td>1937 Lincoln Berline</td>\n",
       "      <td>10125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>S10_1949</td>\n",
       "      <td>1952 Alpine Renault 1300</td>\n",
       "      <td>10126</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>S12_1108</td>\n",
       "      <td>2001 Ferrari Enzo</td>\n",
       "      <td>10127</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>S18_3140</td>\n",
       "      <td>1903 Ford Model A</td>\n",
       "      <td>10128</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>S10_4757</td>\n",
       "      <td>1972 Alfa Romeo GTA</td>\n",
       "      <td>10129</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>296</th>\n",
       "      <td>S12_3891</td>\n",
       "      <td>1969 Ford Falcon</td>\n",
       "      <td>10396</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>297</th>\n",
       "      <td>S700_1938</td>\n",
       "      <td>The Mayflower</td>\n",
       "      <td>10397</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>298</th>\n",
       "      <td>S18_1662</td>\n",
       "      <td>1980s Black Hawk Helicopter</td>\n",
       "      <td>10398</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>299</th>\n",
       "      <td>S10_1678</td>\n",
       "      <td>1969 Harley Davidson Ultimate Chopper</td>\n",
       "      <td>10399</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300</th>\n",
       "      <td>S10_4757</td>\n",
       "      <td>1972 Alfa Romeo GTA</td>\n",
       "      <td>10400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301</th>\n",
       "      <td>S18_2581</td>\n",
       "      <td>P-51-D Mustang</td>\n",
       "      <td>10401</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>302</th>\n",
       "      <td>S10_2016</td>\n",
       "      <td>1996 Moto Guzzi 1100i</td>\n",
       "      <td>10402</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>303</th>\n",
       "      <td>S10_1678</td>\n",
       "      <td>1969 Harley Davidson Ultimate Chopper</td>\n",
       "      <td>10403</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>304</th>\n",
       "      <td>S12_1099</td>\n",
       "      <td>1968 Ford Mustang</td>\n",
       "      <td>10404</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>305</th>\n",
       "      <td>S12_4675</td>\n",
       "      <td>1969 Dodge Charger</td>\n",
       "      <td>10405</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>306</th>\n",
       "      <td>S18_1129</td>\n",
       "      <td>1993 Mazda RX-7</td>\n",
       "      <td>10406</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>307</th>\n",
       "      <td>S18_1589</td>\n",
       "      <td>1965 Aston Martin DB5</td>\n",
       "      <td>10407</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>308</th>\n",
       "      <td>S24_3969</td>\n",
       "      <td>1936 Mercedes Benz 500k Roadster</td>\n",
       "      <td>10408</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>309</th>\n",
       "      <td>S18_2325</td>\n",
       "      <td>1932 Model A Ford J-Coupe</td>\n",
       "      <td>10409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>310</th>\n",
       "      <td>S18_1342</td>\n",
       "      <td>1937 Lincoln Berline</td>\n",
       "      <td>10410</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>311</th>\n",
       "      <td>S10_1949</td>\n",
       "      <td>1952 Alpine Renault 1300</td>\n",
       "      <td>10411</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>312</th>\n",
       "      <td>S12_4473</td>\n",
       "      <td>1957 Chevy Pickup</td>\n",
       "      <td>10412</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>313</th>\n",
       "      <td>S12_1108</td>\n",
       "      <td>2001 Ferrari Enzo</td>\n",
       "      <td>10413</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>314</th>\n",
       "      <td>S10_4757</td>\n",
       "      <td>1972 Alfa Romeo GTA</td>\n",
       "      <td>10414</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>315</th>\n",
       "      <td>S18_3856</td>\n",
       "      <td>1941 Chevrolet Special Deluxe Cabriolet</td>\n",
       "      <td>10415</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>316</th>\n",
       "      <td>S18_1662</td>\n",
       "      <td>1980s Black Hawk Helicopter</td>\n",
       "      <td>10416</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>317</th>\n",
       "      <td>S10_1678</td>\n",
       "      <td>1969 Harley Davidson Ultimate Chopper</td>\n",
       "      <td>10417</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>318</th>\n",
       "      <td>S18_3278</td>\n",
       "      <td>1969 Dodge Super Bee</td>\n",
       "      <td>10418</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>319</th>\n",
       "      <td>S12_1099</td>\n",
       "      <td>1968 Ford Mustang</td>\n",
       "      <td>10419</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>320</th>\n",
       "      <td>S18_1749</td>\n",
       "      <td>1917 Grand Touring Sedan</td>\n",
       "      <td>10420</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>321</th>\n",
       "      <td>S18_2795</td>\n",
       "      <td>1928 Mercedes-Benz SSK</td>\n",
       "      <td>10421</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>322</th>\n",
       "      <td>S18_1342</td>\n",
       "      <td>1937 Lincoln Berline</td>\n",
       "      <td>10422</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>323</th>\n",
       "      <td>S18_2949</td>\n",
       "      <td>1913 Ford Model T Speedster</td>\n",
       "      <td>10423</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>324</th>\n",
       "      <td>S10_1949</td>\n",
       "      <td>1952 Alpine Renault 1300</td>\n",
       "      <td>10424</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>325</th>\n",
       "      <td>S10_4962</td>\n",
       "      <td>1962 LanciaA Delta 16V</td>\n",
       "      <td>10425</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>326 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    productcode                              productname  ordernumber\n",
       "0      S18_1749                 1917 Grand Touring Sedan        10100\n",
       "1      S18_2325                1932 Model A Ford J-Coupe        10101\n",
       "2      S18_1342                     1937 Lincoln Berline        10102\n",
       "3      S10_1949                 1952 Alpine Renault 1300        10103\n",
       "4      S12_3148                       1969 Corvair Monza        10104\n",
       "5      S10_4757                      1972 Alfa Romeo GTA        10105\n",
       "6      S18_1662              1980s Black Hawk Helicopter        10106\n",
       "7      S10_1678    1969 Harley Davidson Ultimate Chopper        10107\n",
       "8      S12_1099                        1968 Ford Mustang        10108\n",
       "9      S18_1129                          1993 Mazda RX-7        10109\n",
       "10     S18_1589                    1965 Aston Martin DB5        10110\n",
       "11     S18_1342                     1937 Lincoln Berline        10111\n",
       "12     S10_1949                 1952 Alpine Renault 1300        10112\n",
       "13     S12_1666                           1958 Setra Bus        10113\n",
       "14     S10_4962                   1962 LanciaA Delta 16V        10114\n",
       "15     S12_4473                        1957 Chevy Pickup        10115\n",
       "16     S32_3207   1950's Chicago Surface Lines Streetcar        10116\n",
       "17     S12_1108                        2001 Ferrari Enzo        10117\n",
       "18    S700_3505                              The Titanic        10118\n",
       "19     S10_4757                      1972 Alfa Romeo GTA        10119\n",
       "20     S10_2016                    1996 Moto Guzzi 1100i        10120\n",
       "21     S10_1678    1969 Harley Davidson Ultimate Chopper        10121\n",
       "22     S12_1099                        1968 Ford Mustang        10122\n",
       "23     S18_1589                    1965 Aston Martin DB5        10123\n",
       "24     S18_1749                 1917 Grand Touring Sedan        10124\n",
       "25     S18_1342                     1937 Lincoln Berline        10125\n",
       "26     S10_1949                 1952 Alpine Renault 1300        10126\n",
       "27     S12_1108                        2001 Ferrari Enzo        10127\n",
       "28     S18_3140                        1903 Ford Model A        10128\n",
       "29     S10_4757                      1972 Alfa Romeo GTA        10129\n",
       "..          ...                                      ...          ...\n",
       "296    S12_3891                         1969 Ford Falcon        10396\n",
       "297   S700_1938                            The Mayflower        10397\n",
       "298    S18_1662              1980s Black Hawk Helicopter        10398\n",
       "299    S10_1678    1969 Harley Davidson Ultimate Chopper        10399\n",
       "300    S10_4757                      1972 Alfa Romeo GTA        10400\n",
       "301    S18_2581                           P-51-D Mustang        10401\n",
       "302    S10_2016                    1996 Moto Guzzi 1100i        10402\n",
       "303    S10_1678    1969 Harley Davidson Ultimate Chopper        10403\n",
       "304    S12_1099                        1968 Ford Mustang        10404\n",
       "305    S12_4675                       1969 Dodge Charger        10405\n",
       "306    S18_1129                          1993 Mazda RX-7        10406\n",
       "307    S18_1589                    1965 Aston Martin DB5        10407\n",
       "308    S24_3969         1936 Mercedes Benz 500k Roadster        10408\n",
       "309    S18_2325                1932 Model A Ford J-Coupe        10409\n",
       "310    S18_1342                     1937 Lincoln Berline        10410\n",
       "311    S10_1949                 1952 Alpine Renault 1300        10411\n",
       "312    S12_4473                        1957 Chevy Pickup        10412\n",
       "313    S12_1108                        2001 Ferrari Enzo        10413\n",
       "314    S10_4757                      1972 Alfa Romeo GTA        10414\n",
       "315    S18_3856  1941 Chevrolet Special Deluxe Cabriolet        10415\n",
       "316    S18_1662              1980s Black Hawk Helicopter        10416\n",
       "317    S10_1678    1969 Harley Davidson Ultimate Chopper        10417\n",
       "318    S18_3278                     1969 Dodge Super Bee        10418\n",
       "319    S12_1099                        1968 Ford Mustang        10419\n",
       "320    S18_1749                 1917 Grand Touring Sedan        10420\n",
       "321    S18_2795                   1928 Mercedes-Benz SSK        10421\n",
       "322    S18_1342                     1937 Lincoln Berline        10422\n",
       "323    S18_2949              1913 Ford Model T Speedster        10423\n",
       "324    S10_1949                 1952 Alpine Renault 1300        10424\n",
       "325    S10_4962                   1962 LanciaA Delta 16V        10425\n",
       "\n",
       "[326 rows x 3 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "run(\"\"\"\n",
    "SELECT\n",
    "    productcode,\n",
    "    productname,\n",
    "    ordernumber\n",
    "FROM\n",
    "    orders o\n",
    "        INNER JOIN\n",
    "    orderdetails od USING (ordernumber)\n",
    "        INNER JOIN\n",
    "    products USING (productcode)\n",
    "GROUP BY od.ordernumber\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>productcode</th>\n",
       "      <th>banyak</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [productcode, banyak]\n",
       "Index: []"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "run(\"\"\"\n",
    "SELECT productcode, count(*) as banyak\n",
    "FROM orderdetails\n",
    "GROUP BY productcode\n",
    "HAVING banyak = (SELECT count(*) FROM orders)\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. List the names of products sold at less than 80% of the MSRP."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>productcode</th>\n",
       "      <th>productname</th>\n",
       "      <th>ordernumber</th>\n",
       "      <th>MSRP</th>\n",
       "      <th>priceeach</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>S10_2016</td>\n",
       "      <td>1996 Moto Guzzi 1100i</td>\n",
       "      <td>10354</td>\n",
       "      <td>118.94</td>\n",
       "      <td>95.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>S18_1129</td>\n",
       "      <td>1993 Mazda RX-7</td>\n",
       "      <td>10287</td>\n",
       "      <td>141.54</td>\n",
       "      <td>113.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>S18_1342</td>\n",
       "      <td>1937 Lincoln Berline</td>\n",
       "      <td>10356</td>\n",
       "      <td>102.74</td>\n",
       "      <td>82.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>S18_1589</td>\n",
       "      <td>1965 Aston Martin DB5</td>\n",
       "      <td>10266</td>\n",
       "      <td>124.44</td>\n",
       "      <td>99.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>S18_1589</td>\n",
       "      <td>1965 Aston Martin DB5</td>\n",
       "      <td>10331</td>\n",
       "      <td>124.44</td>\n",
       "      <td>99.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>S18_1662</td>\n",
       "      <td>1980s Black Hawk Helicopter</td>\n",
       "      <td>10143</td>\n",
       "      <td>157.69</td>\n",
       "      <td>126.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>S18_1662</td>\n",
       "      <td>1980s Black Hawk Helicopter</td>\n",
       "      <td>10360</td>\n",
       "      <td>157.69</td>\n",
       "      <td>126.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>S18_2248</td>\n",
       "      <td>1911 Ford Town Car</td>\n",
       "      <td>10312</td>\n",
       "      <td>60.54</td>\n",
       "      <td>48.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>S18_2581</td>\n",
       "      <td>P-51-D Mustang</td>\n",
       "      <td>10223</td>\n",
       "      <td>84.48</td>\n",
       "      <td>67.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>S18_2581</td>\n",
       "      <td>P-51-D Mustang</td>\n",
       "      <td>10263</td>\n",
       "      <td>84.48</td>\n",
       "      <td>67.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>S18_3232</td>\n",
       "      <td>1992 Ferrari 360 Spider red</td>\n",
       "      <td>10239</td>\n",
       "      <td>169.34</td>\n",
       "      <td>135.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>S18_3232</td>\n",
       "      <td>1992 Ferrari 360 Spider red</td>\n",
       "      <td>10281</td>\n",
       "      <td>169.34</td>\n",
       "      <td>135.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>S18_3232</td>\n",
       "      <td>1992 Ferrari 360 Spider red</td>\n",
       "      <td>10301</td>\n",
       "      <td>169.34</td>\n",
       "      <td>135.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>S18_3232</td>\n",
       "      <td>1992 Ferrari 360 Spider red</td>\n",
       "      <td>10394</td>\n",
       "      <td>169.34</td>\n",
       "      <td>135.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>S18_3259</td>\n",
       "      <td>Collectable Wooden Train</td>\n",
       "      <td>10128</td>\n",
       "      <td>100.84</td>\n",
       "      <td>80.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>S18_3482</td>\n",
       "      <td>1976 Ford Gran Torino</td>\n",
       "      <td>10355</td>\n",
       "      <td>146.99</td>\n",
       "      <td>117.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>S18_4409</td>\n",
       "      <td>1932 Alfa Romeo 8C2300 Spider Sport</td>\n",
       "      <td>10420</td>\n",
       "      <td>92.03</td>\n",
       "      <td>73.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>S18_4600</td>\n",
       "      <td>1940s Ford truck</td>\n",
       "      <td>10281</td>\n",
       "      <td>121.08</td>\n",
       "      <td>96.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>S18_4600</td>\n",
       "      <td>1940s Ford truck</td>\n",
       "      <td>10291</td>\n",
       "      <td>121.08</td>\n",
       "      <td>96.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>S18_4600</td>\n",
       "      <td>1940s Ford truck</td>\n",
       "      <td>10323</td>\n",
       "      <td>121.08</td>\n",
       "      <td>96.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>S24_1937</td>\n",
       "      <td>1939 Chevrolet Deluxe Coupe</td>\n",
       "      <td>10322</td>\n",
       "      <td>33.19</td>\n",
       "      <td>26.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>S24_1937</td>\n",
       "      <td>1939 Chevrolet Deluxe Coupe</td>\n",
       "      <td>10391</td>\n",
       "      <td>33.19</td>\n",
       "      <td>26.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>S24_2011</td>\n",
       "      <td>18th century schooner</td>\n",
       "      <td>10350</td>\n",
       "      <td>122.89</td>\n",
       "      <td>98.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>S24_2300</td>\n",
       "      <td>1962 Volkswagen Microbus</td>\n",
       "      <td>10114</td>\n",
       "      <td>127.79</td>\n",
       "      <td>102.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>S24_2300</td>\n",
       "      <td>1962 Volkswagen Microbus</td>\n",
       "      <td>10313</td>\n",
       "      <td>127.79</td>\n",
       "      <td>102.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>S24_2887</td>\n",
       "      <td>1952 Citroen-15CV</td>\n",
       "      <td>10124</td>\n",
       "      <td>117.44</td>\n",
       "      <td>93.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>S24_2887</td>\n",
       "      <td>1952 Citroen-15CV</td>\n",
       "      <td>10267</td>\n",
       "      <td>117.44</td>\n",
       "      <td>93.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>S24_3371</td>\n",
       "      <td>1971 Alpine Renault 1600s</td>\n",
       "      <td>10147</td>\n",
       "      <td>61.23</td>\n",
       "      <td>48.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>S24_3856</td>\n",
       "      <td>1956 Porsche 356A Coupe</td>\n",
       "      <td>10192</td>\n",
       "      <td>140.43</td>\n",
       "      <td>112.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>S24_3856</td>\n",
       "      <td>1956 Porsche 356A Coupe</td>\n",
       "      <td>10342</td>\n",
       "      <td>140.43</td>\n",
       "      <td>112.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>S24_3856</td>\n",
       "      <td>1956 Porsche 356A Coupe</td>\n",
       "      <td>10419</td>\n",
       "      <td>140.43</td>\n",
       "      <td>112.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>S24_3949</td>\n",
       "      <td>Corsair F4U ( Bird Cage)</td>\n",
       "      <td>10131</td>\n",
       "      <td>68.24</td>\n",
       "      <td>54.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>S24_3969</td>\n",
       "      <td>1936 Mercedes Benz 500k Roadster</td>\n",
       "      <td>10138</td>\n",
       "      <td>41.03</td>\n",
       "      <td>32.82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>S24_3969</td>\n",
       "      <td>1936 Mercedes Benz 500k Roadster</td>\n",
       "      <td>10162</td>\n",
       "      <td>41.03</td>\n",
       "      <td>32.82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>S24_3969</td>\n",
       "      <td>1936 Mercedes Benz 500k Roadster</td>\n",
       "      <td>10380</td>\n",
       "      <td>41.03</td>\n",
       "      <td>32.82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>S32_1374</td>\n",
       "      <td>1997 BMW F650 ST</td>\n",
       "      <td>10387</td>\n",
       "      <td>99.89</td>\n",
       "      <td>79.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>S32_3207</td>\n",
       "      <td>1950's Chicago Surface Lines Streetcar</td>\n",
       "      <td>10220</td>\n",
       "      <td>62.14</td>\n",
       "      <td>49.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>S32_3522</td>\n",
       "      <td>1996 Peterbilt 379 Stake Bed with Outrigger</td>\n",
       "      <td>10358</td>\n",
       "      <td>64.64</td>\n",
       "      <td>51.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>S50_1341</td>\n",
       "      <td>1930 Buick Marquette Phaeton</td>\n",
       "      <td>10143</td>\n",
       "      <td>43.64</td>\n",
       "      <td>34.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>S50_1514</td>\n",
       "      <td>1962 City of Detroit Streetcar</td>\n",
       "      <td>10185</td>\n",
       "      <td>58.58</td>\n",
       "      <td>46.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>S700_1691</td>\n",
       "      <td>American Airlines: B767-300</td>\n",
       "      <td>10308</td>\n",
       "      <td>91.34</td>\n",
       "      <td>73.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>S700_1691</td>\n",
       "      <td>American Airlines: B767-300</td>\n",
       "      <td>10353</td>\n",
       "      <td>91.34</td>\n",
       "      <td>73.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>S700_2610</td>\n",
       "      <td>The USS Constitution Ship</td>\n",
       "      <td>10273</td>\n",
       "      <td>72.28</td>\n",
       "      <td>57.82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>S700_4002</td>\n",
       "      <td>American Airlines: MD-11S</td>\n",
       "      <td>10386</td>\n",
       "      <td>74.03</td>\n",
       "      <td>59.22</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   productcode                                  productname  ordernumber  \\\n",
       "0     S10_2016                        1996 Moto Guzzi 1100i        10354   \n",
       "1     S18_1129                              1993 Mazda RX-7        10287   \n",
       "2     S18_1342                         1937 Lincoln Berline        10356   \n",
       "3     S18_1589                        1965 Aston Martin DB5        10266   \n",
       "4     S18_1589                        1965 Aston Martin DB5        10331   \n",
       "5     S18_1662                  1980s Black Hawk Helicopter        10143   \n",
       "6     S18_1662                  1980s Black Hawk Helicopter        10360   \n",
       "7     S18_2248                           1911 Ford Town Car        10312   \n",
       "8     S18_2581                               P-51-D Mustang        10223   \n",
       "9     S18_2581                               P-51-D Mustang        10263   \n",
       "10    S18_3232                  1992 Ferrari 360 Spider red        10239   \n",
       "11    S18_3232                  1992 Ferrari 360 Spider red        10281   \n",
       "12    S18_3232                  1992 Ferrari 360 Spider red        10301   \n",
       "13    S18_3232                  1992 Ferrari 360 Spider red        10394   \n",
       "14    S18_3259                     Collectable Wooden Train        10128   \n",
       "15    S18_3482                        1976 Ford Gran Torino        10355   \n",
       "16    S18_4409          1932 Alfa Romeo 8C2300 Spider Sport        10420   \n",
       "17    S18_4600                             1940s Ford truck        10281   \n",
       "18    S18_4600                             1940s Ford truck        10291   \n",
       "19    S18_4600                             1940s Ford truck        10323   \n",
       "20    S24_1937                  1939 Chevrolet Deluxe Coupe        10322   \n",
       "21    S24_1937                  1939 Chevrolet Deluxe Coupe        10391   \n",
       "22    S24_2011                        18th century schooner        10350   \n",
       "23    S24_2300                     1962 Volkswagen Microbus        10114   \n",
       "24    S24_2300                     1962 Volkswagen Microbus        10313   \n",
       "25    S24_2887                            1952 Citroen-15CV        10124   \n",
       "26    S24_2887                            1952 Citroen-15CV        10267   \n",
       "27    S24_3371                    1971 Alpine Renault 1600s        10147   \n",
       "28    S24_3856                      1956 Porsche 356A Coupe        10192   \n",
       "29    S24_3856                      1956 Porsche 356A Coupe        10342   \n",
       "30    S24_3856                      1956 Porsche 356A Coupe        10419   \n",
       "31    S24_3949                     Corsair F4U ( Bird Cage)        10131   \n",
       "32    S24_3969             1936 Mercedes Benz 500k Roadster        10138   \n",
       "33    S24_3969             1936 Mercedes Benz 500k Roadster        10162   \n",
       "34    S24_3969             1936 Mercedes Benz 500k Roadster        10380   \n",
       "35    S32_1374                             1997 BMW F650 ST        10387   \n",
       "36    S32_3207       1950's Chicago Surface Lines Streetcar        10220   \n",
       "37    S32_3522  1996 Peterbilt 379 Stake Bed with Outrigger        10358   \n",
       "38    S50_1341                 1930 Buick Marquette Phaeton        10143   \n",
       "39    S50_1514               1962 City of Detroit Streetcar        10185   \n",
       "40   S700_1691                  American Airlines: B767-300        10308   \n",
       "41   S700_1691                  American Airlines: B767-300        10353   \n",
       "42   S700_2610                    The USS Constitution Ship        10273   \n",
       "43   S700_4002                    American Airlines: MD-11S        10386   \n",
       "\n",
       "      MSRP  priceeach  \n",
       "0   118.94      95.15  \n",
       "1   141.54     113.23  \n",
       "2   102.74      82.19  \n",
       "3   124.44      99.55  \n",
       "4   124.44      99.55  \n",
       "5   157.69     126.15  \n",
       "6   157.69     126.15  \n",
       "7    60.54      48.43  \n",
       "8    84.48      67.58  \n",
       "9    84.48      67.58  \n",
       "10  169.34     135.47  \n",
       "11  169.34     135.47  \n",
       "12  169.34     135.47  \n",
       "13  169.34     135.47  \n",
       "14  100.84      80.67  \n",
       "15  146.99     117.59  \n",
       "16   92.03      73.62  \n",
       "17  121.08      96.86  \n",
       "18  121.08      96.86  \n",
       "19  121.08      96.86  \n",
       "20   33.19      26.55  \n",
       "21   33.19      26.55  \n",
       "22  122.89      98.31  \n",
       "23  127.79     102.23  \n",
       "24  127.79     102.23  \n",
       "25  117.44      93.95  \n",
       "26  117.44      93.95  \n",
       "27   61.23      48.98  \n",
       "28  140.43     112.34  \n",
       "29  140.43     112.34  \n",
       "30  140.43     112.34  \n",
       "31   68.24      54.59  \n",
       "32   41.03      32.82  \n",
       "33   41.03      32.82  \n",
       "34   41.03      32.82  \n",
       "35   99.89      79.91  \n",
       "36   62.14      49.71  \n",
       "37   64.64      51.71  \n",
       "38   43.64      34.91  \n",
       "39   58.58      46.86  \n",
       "40   91.34      73.07  \n",
       "41   91.34      73.07  \n",
       "42   72.28      57.82  \n",
       "43   74.03      59.22  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "run(\"\"\"\n",
    "SELECT \n",
    "    productcode,\n",
    "    productname,\n",
    "    ordernumber,\n",
    "    MSRP,\n",
    "    priceeach\n",
    "FROM \n",
    "    products\n",
    "        INNER JOIN\n",
    "    orderdetails USING (productcode)\n",
    "WHERE priceeach < MSRP * 80/100\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. Reports those products that have been sold with a markup of 100% or more (i.e., the priceEach is at least twice the buyPrice)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>productcode</th>\n",
       "      <th>productname</th>\n",
       "      <th>ordernumber</th>\n",
       "      <th>buyprice</th>\n",
       "      <th>priceeach</th>\n",
       "      <th>keuntungan</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>S10_1949</td>\n",
       "      <td>1952 Alpine Renault 1300</td>\n",
       "      <td>10103</td>\n",
       "      <td>98.58</td>\n",
       "      <td>214.30</td>\n",
       "      <td>115.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>S10_1949</td>\n",
       "      <td>1952 Alpine Renault 1300</td>\n",
       "      <td>10112</td>\n",
       "      <td>98.58</td>\n",
       "      <td>197.16</td>\n",
       "      <td>98.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>S10_1949</td>\n",
       "      <td>1952 Alpine Renault 1300</td>\n",
       "      <td>10126</td>\n",
       "      <td>98.58</td>\n",
       "      <td>205.73</td>\n",
       "      <td>107.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>S10_1949</td>\n",
       "      <td>1952 Alpine Renault 1300</td>\n",
       "      <td>10163</td>\n",
       "      <td>98.58</td>\n",
       "      <td>212.16</td>\n",
       "      <td>113.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>S10_1949</td>\n",
       "      <td>1952 Alpine Renault 1300</td>\n",
       "      <td>10174</td>\n",
       "      <td>98.58</td>\n",
       "      <td>207.87</td>\n",
       "      <td>109.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>S10_1949</td>\n",
       "      <td>1952 Alpine Renault 1300</td>\n",
       "      <td>10194</td>\n",
       "      <td>98.58</td>\n",
       "      <td>203.59</td>\n",
       "      <td>105.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>S10_1949</td>\n",
       "      <td>1952 Alpine Renault 1300</td>\n",
       "      <td>10206</td>\n",
       "      <td>98.58</td>\n",
       "      <td>203.59</td>\n",
       "      <td>105.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>S10_1949</td>\n",
       "      <td>1952 Alpine Renault 1300</td>\n",
       "      <td>10215</td>\n",
       "      <td>98.58</td>\n",
       "      <td>205.73</td>\n",
       "      <td>107.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>S10_1949</td>\n",
       "      <td>1952 Alpine Renault 1300</td>\n",
       "      <td>10228</td>\n",
       "      <td>98.58</td>\n",
       "      <td>214.30</td>\n",
       "      <td>115.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>S10_1949</td>\n",
       "      <td>1952 Alpine Renault 1300</td>\n",
       "      <td>10280</td>\n",
       "      <td>98.58</td>\n",
       "      <td>205.73</td>\n",
       "      <td>107.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>S10_1949</td>\n",
       "      <td>1952 Alpine Renault 1300</td>\n",
       "      <td>10291</td>\n",
       "      <td>98.58</td>\n",
       "      <td>210.01</td>\n",
       "      <td>111.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>S10_1949</td>\n",
       "      <td>1952 Alpine Renault 1300</td>\n",
       "      <td>10304</td>\n",
       "      <td>98.58</td>\n",
       "      <td>201.44</td>\n",
       "      <td>102.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>S10_1949</td>\n",
       "      <td>1952 Alpine Renault 1300</td>\n",
       "      <td>10312</td>\n",
       "      <td>98.58</td>\n",
       "      <td>214.30</td>\n",
       "      <td>115.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>S10_1949</td>\n",
       "      <td>1952 Alpine Renault 1300</td>\n",
       "      <td>10357</td>\n",
       "      <td>98.58</td>\n",
       "      <td>199.30</td>\n",
       "      <td>100.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>S10_1949</td>\n",
       "      <td>1952 Alpine Renault 1300</td>\n",
       "      <td>10411</td>\n",
       "      <td>98.58</td>\n",
       "      <td>205.73</td>\n",
       "      <td>107.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>S10_1949</td>\n",
       "      <td>1952 Alpine Renault 1300</td>\n",
       "      <td>10424</td>\n",
       "      <td>98.58</td>\n",
       "      <td>201.44</td>\n",
       "      <td>102.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>S10_4698</td>\n",
       "      <td>2003 Harley-Davidson Eagle Drag Bike</td>\n",
       "      <td>10134</td>\n",
       "      <td>91.02</td>\n",
       "      <td>187.85</td>\n",
       "      <td>96.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>S10_4698</td>\n",
       "      <td>2003 Harley-Davidson Eagle Drag Bike</td>\n",
       "      <td>10188</td>\n",
       "      <td>91.02</td>\n",
       "      <td>182.04</td>\n",
       "      <td>91.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>S10_4698</td>\n",
       "      <td>2003 Harley-Davidson Eagle Drag Bike</td>\n",
       "      <td>10201</td>\n",
       "      <td>91.02</td>\n",
       "      <td>191.72</td>\n",
       "      <td>100.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>S10_4698</td>\n",
       "      <td>2003 Harley-Davidson Eagle Drag Bike</td>\n",
       "      <td>10210</td>\n",
       "      <td>91.02</td>\n",
       "      <td>189.79</td>\n",
       "      <td>98.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>S10_4698</td>\n",
       "      <td>2003 Harley-Davidson Eagle Drag Bike</td>\n",
       "      <td>10223</td>\n",
       "      <td>91.02</td>\n",
       "      <td>189.79</td>\n",
       "      <td>98.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>S10_4698</td>\n",
       "      <td>2003 Harley-Davidson Eagle Drag Bike</td>\n",
       "      <td>10263</td>\n",
       "      <td>91.02</td>\n",
       "      <td>193.66</td>\n",
       "      <td>102.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>S10_4698</td>\n",
       "      <td>2003 Harley-Davidson Eagle Drag Bike</td>\n",
       "      <td>10308</td>\n",
       "      <td>91.02</td>\n",
       "      <td>187.85</td>\n",
       "      <td>96.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>S10_4698</td>\n",
       "      <td>2003 Harley-Davidson Eagle Drag Bike</td>\n",
       "      <td>10318</td>\n",
       "      <td>91.02</td>\n",
       "      <td>189.79</td>\n",
       "      <td>98.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>S10_4698</td>\n",
       "      <td>2003 Harley-Davidson Eagle Drag Bike</td>\n",
       "      <td>10362</td>\n",
       "      <td>91.02</td>\n",
       "      <td>182.04</td>\n",
       "      <td>91.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>S12_1099</td>\n",
       "      <td>1968 Ford Mustang</td>\n",
       "      <td>10287</td>\n",
       "      <td>95.34</td>\n",
       "      <td>190.68</td>\n",
       "      <td>95.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>S12_1099</td>\n",
       "      <td>1968 Ford Mustang</td>\n",
       "      <td>10341</td>\n",
       "      <td>95.34</td>\n",
       "      <td>192.62</td>\n",
       "      <td>97.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>S12_1108</td>\n",
       "      <td>2001 Ferrari Enzo</td>\n",
       "      <td>10105</td>\n",
       "      <td>95.59</td>\n",
       "      <td>205.72</td>\n",
       "      <td>110.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>S12_1108</td>\n",
       "      <td>2001 Ferrari Enzo</td>\n",
       "      <td>10117</td>\n",
       "      <td>95.59</td>\n",
       "      <td>195.33</td>\n",
       "      <td>99.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>S12_1108</td>\n",
       "      <td>2001 Ferrari Enzo</td>\n",
       "      <td>10127</td>\n",
       "      <td>95.59</td>\n",
       "      <td>193.25</td>\n",
       "      <td>97.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>470</th>\n",
       "      <td>S700_2047</td>\n",
       "      <td>HMS Bounty</td>\n",
       "      <td>10234</td>\n",
       "      <td>39.83</td>\n",
       "      <td>83.28</td>\n",
       "      <td>43.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>471</th>\n",
       "      <td>S700_2047</td>\n",
       "      <td>HMS Bounty</td>\n",
       "      <td>10249</td>\n",
       "      <td>39.83</td>\n",
       "      <td>85.99</td>\n",
       "      <td>46.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>472</th>\n",
       "      <td>S700_2047</td>\n",
       "      <td>HMS Bounty</td>\n",
       "      <td>10262</td>\n",
       "      <td>39.83</td>\n",
       "      <td>83.28</td>\n",
       "      <td>43.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>473</th>\n",
       "      <td>S700_2047</td>\n",
       "      <td>HMS Bounty</td>\n",
       "      <td>10296</td>\n",
       "      <td>39.83</td>\n",
       "      <td>89.61</td>\n",
       "      <td>49.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>474</th>\n",
       "      <td>S700_2047</td>\n",
       "      <td>HMS Bounty</td>\n",
       "      <td>10307</td>\n",
       "      <td>39.83</td>\n",
       "      <td>81.47</td>\n",
       "      <td>41.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>475</th>\n",
       "      <td>S700_2047</td>\n",
       "      <td>HMS Bounty</td>\n",
       "      <td>10339</td>\n",
       "      <td>39.83</td>\n",
       "      <td>86.90</td>\n",
       "      <td>47.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>476</th>\n",
       "      <td>S700_2047</td>\n",
       "      <td>HMS Bounty</td>\n",
       "      <td>10361</td>\n",
       "      <td>39.83</td>\n",
       "      <td>85.99</td>\n",
       "      <td>46.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>477</th>\n",
       "      <td>S700_2047</td>\n",
       "      <td>HMS Bounty</td>\n",
       "      <td>10386</td>\n",
       "      <td>39.83</td>\n",
       "      <td>85.09</td>\n",
       "      <td>45.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>478</th>\n",
       "      <td>S700_2047</td>\n",
       "      <td>HMS Bounty</td>\n",
       "      <td>10400</td>\n",
       "      <td>39.83</td>\n",
       "      <td>82.37</td>\n",
       "      <td>42.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>479</th>\n",
       "      <td>S700_2610</td>\n",
       "      <td>The USS Constitution Ship</td>\n",
       "      <td>10129</td>\n",
       "      <td>33.97</td>\n",
       "      <td>72.28</td>\n",
       "      <td>38.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>480</th>\n",
       "      <td>S700_2610</td>\n",
       "      <td>The USS Constitution Ship</td>\n",
       "      <td>10143</td>\n",
       "      <td>33.97</td>\n",
       "      <td>69.39</td>\n",
       "      <td>35.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>481</th>\n",
       "      <td>S700_2610</td>\n",
       "      <td>The USS Constitution Ship</td>\n",
       "      <td>10222</td>\n",
       "      <td>33.97</td>\n",
       "      <td>69.39</td>\n",
       "      <td>35.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>482</th>\n",
       "      <td>S700_2610</td>\n",
       "      <td>The USS Constitution Ship</td>\n",
       "      <td>10233</td>\n",
       "      <td>33.97</td>\n",
       "      <td>67.94</td>\n",
       "      <td>33.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>483</th>\n",
       "      <td>S700_2610</td>\n",
       "      <td>The USS Constitution Ship</td>\n",
       "      <td>10248</td>\n",
       "      <td>33.97</td>\n",
       "      <td>69.39</td>\n",
       "      <td>35.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>484</th>\n",
       "      <td>S700_2610</td>\n",
       "      <td>The USS Constitution Ship</td>\n",
       "      <td>10283</td>\n",
       "      <td>33.97</td>\n",
       "      <td>68.67</td>\n",
       "      <td>34.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>485</th>\n",
       "      <td>S700_2610</td>\n",
       "      <td>The USS Constitution Ship</td>\n",
       "      <td>10295</td>\n",
       "      <td>33.97</td>\n",
       "      <td>71.56</td>\n",
       "      <td>37.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>486</th>\n",
       "      <td>S700_2610</td>\n",
       "      <td>The USS Constitution Ship</td>\n",
       "      <td>10350</td>\n",
       "      <td>33.97</td>\n",
       "      <td>68.67</td>\n",
       "      <td>34.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>487</th>\n",
       "      <td>S700_2610</td>\n",
       "      <td>The USS Constitution Ship</td>\n",
       "      <td>10360</td>\n",
       "      <td>33.97</td>\n",
       "      <td>70.11</td>\n",
       "      <td>36.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>488</th>\n",
       "      <td>S700_2610</td>\n",
       "      <td>The USS Constitution Ship</td>\n",
       "      <td>10373</td>\n",
       "      <td>33.97</td>\n",
       "      <td>69.39</td>\n",
       "      <td>35.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>489</th>\n",
       "      <td>S700_2824</td>\n",
       "      <td>1982 Camaro Z28</td>\n",
       "      <td>10103</td>\n",
       "      <td>46.53</td>\n",
       "      <td>94.07</td>\n",
       "      <td>47.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>490</th>\n",
       "      <td>S700_2824</td>\n",
       "      <td>1982 Camaro Z28</td>\n",
       "      <td>10126</td>\n",
       "      <td>46.53</td>\n",
       "      <td>97.10</td>\n",
       "      <td>50.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>491</th>\n",
       "      <td>S700_2824</td>\n",
       "      <td>1982 Camaro Z28</td>\n",
       "      <td>10140</td>\n",
       "      <td>46.53</td>\n",
       "      <td>101.15</td>\n",
       "      <td>54.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>492</th>\n",
       "      <td>S700_2824</td>\n",
       "      <td>1982 Camaro Z28</td>\n",
       "      <td>10150</td>\n",
       "      <td>46.53</td>\n",
       "      <td>95.08</td>\n",
       "      <td>48.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>493</th>\n",
       "      <td>S700_2824</td>\n",
       "      <td>1982 Camaro Z28</td>\n",
       "      <td>10270</td>\n",
       "      <td>46.53</td>\n",
       "      <td>101.15</td>\n",
       "      <td>54.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>494</th>\n",
       "      <td>S700_2824</td>\n",
       "      <td>1982 Camaro Z28</td>\n",
       "      <td>10313</td>\n",
       "      <td>46.53</td>\n",
       "      <td>96.09</td>\n",
       "      <td>49.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>495</th>\n",
       "      <td>S700_2824</td>\n",
       "      <td>1982 Camaro Z28</td>\n",
       "      <td>10336</td>\n",
       "      <td>46.53</td>\n",
       "      <td>94.07</td>\n",
       "      <td>47.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>496</th>\n",
       "      <td>S700_2824</td>\n",
       "      <td>1982 Camaro Z28</td>\n",
       "      <td>10348</td>\n",
       "      <td>46.53</td>\n",
       "      <td>100.14</td>\n",
       "      <td>53.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>497</th>\n",
       "      <td>S700_2824</td>\n",
       "      <td>1982 Camaro Z28</td>\n",
       "      <td>10382</td>\n",
       "      <td>46.53</td>\n",
       "      <td>101.15</td>\n",
       "      <td>54.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>498</th>\n",
       "      <td>S700_4002</td>\n",
       "      <td>American Airlines: MD-11S</td>\n",
       "      <td>10198</td>\n",
       "      <td>36.27</td>\n",
       "      <td>74.03</td>\n",
       "      <td>37.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499</th>\n",
       "      <td>S700_4002</td>\n",
       "      <td>American Airlines: MD-11S</td>\n",
       "      <td>10284</td>\n",
       "      <td>36.27</td>\n",
       "      <td>73.29</td>\n",
       "      <td>37.02</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>500 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    productcode                           productname  ordernumber  buyprice  \\\n",
       "0      S10_1949              1952 Alpine Renault 1300        10103     98.58   \n",
       "1      S10_1949              1952 Alpine Renault 1300        10112     98.58   \n",
       "2      S10_1949              1952 Alpine Renault 1300        10126     98.58   \n",
       "3      S10_1949              1952 Alpine Renault 1300        10163     98.58   \n",
       "4      S10_1949              1952 Alpine Renault 1300        10174     98.58   \n",
       "5      S10_1949              1952 Alpine Renault 1300        10194     98.58   \n",
       "6      S10_1949              1952 Alpine Renault 1300        10206     98.58   \n",
       "7      S10_1949              1952 Alpine Renault 1300        10215     98.58   \n",
       "8      S10_1949              1952 Alpine Renault 1300        10228     98.58   \n",
       "9      S10_1949              1952 Alpine Renault 1300        10280     98.58   \n",
       "10     S10_1949              1952 Alpine Renault 1300        10291     98.58   \n",
       "11     S10_1949              1952 Alpine Renault 1300        10304     98.58   \n",
       "12     S10_1949              1952 Alpine Renault 1300        10312     98.58   \n",
       "13     S10_1949              1952 Alpine Renault 1300        10357     98.58   \n",
       "14     S10_1949              1952 Alpine Renault 1300        10411     98.58   \n",
       "15     S10_1949              1952 Alpine Renault 1300        10424     98.58   \n",
       "16     S10_4698  2003 Harley-Davidson Eagle Drag Bike        10134     91.02   \n",
       "17     S10_4698  2003 Harley-Davidson Eagle Drag Bike        10188     91.02   \n",
       "18     S10_4698  2003 Harley-Davidson Eagle Drag Bike        10201     91.02   \n",
       "19     S10_4698  2003 Harley-Davidson Eagle Drag Bike        10210     91.02   \n",
       "20     S10_4698  2003 Harley-Davidson Eagle Drag Bike        10223     91.02   \n",
       "21     S10_4698  2003 Harley-Davidson Eagle Drag Bike        10263     91.02   \n",
       "22     S10_4698  2003 Harley-Davidson Eagle Drag Bike        10308     91.02   \n",
       "23     S10_4698  2003 Harley-Davidson Eagle Drag Bike        10318     91.02   \n",
       "24     S10_4698  2003 Harley-Davidson Eagle Drag Bike        10362     91.02   \n",
       "25     S12_1099                     1968 Ford Mustang        10287     95.34   \n",
       "26     S12_1099                     1968 Ford Mustang        10341     95.34   \n",
       "27     S12_1108                     2001 Ferrari Enzo        10105     95.59   \n",
       "28     S12_1108                     2001 Ferrari Enzo        10117     95.59   \n",
       "29     S12_1108                     2001 Ferrari Enzo        10127     95.59   \n",
       "..          ...                                   ...          ...       ...   \n",
       "470   S700_2047                            HMS Bounty        10234     39.83   \n",
       "471   S700_2047                            HMS Bounty        10249     39.83   \n",
       "472   S700_2047                            HMS Bounty        10262     39.83   \n",
       "473   S700_2047                            HMS Bounty        10296     39.83   \n",
       "474   S700_2047                            HMS Bounty        10307     39.83   \n",
       "475   S700_2047                            HMS Bounty        10339     39.83   \n",
       "476   S700_2047                            HMS Bounty        10361     39.83   \n",
       "477   S700_2047                            HMS Bounty        10386     39.83   \n",
       "478   S700_2047                            HMS Bounty        10400     39.83   \n",
       "479   S700_2610             The USS Constitution Ship        10129     33.97   \n",
       "480   S700_2610             The USS Constitution Ship        10143     33.97   \n",
       "481   S700_2610             The USS Constitution Ship        10222     33.97   \n",
       "482   S700_2610             The USS Constitution Ship        10233     33.97   \n",
       "483   S700_2610             The USS Constitution Ship        10248     33.97   \n",
       "484   S700_2610             The USS Constitution Ship        10283     33.97   \n",
       "485   S700_2610             The USS Constitution Ship        10295     33.97   \n",
       "486   S700_2610             The USS Constitution Ship        10350     33.97   \n",
       "487   S700_2610             The USS Constitution Ship        10360     33.97   \n",
       "488   S700_2610             The USS Constitution Ship        10373     33.97   \n",
       "489   S700_2824                       1982 Camaro Z28        10103     46.53   \n",
       "490   S700_2824                       1982 Camaro Z28        10126     46.53   \n",
       "491   S700_2824                       1982 Camaro Z28        10140     46.53   \n",
       "492   S700_2824                       1982 Camaro Z28        10150     46.53   \n",
       "493   S700_2824                       1982 Camaro Z28        10270     46.53   \n",
       "494   S700_2824                       1982 Camaro Z28        10313     46.53   \n",
       "495   S700_2824                       1982 Camaro Z28        10336     46.53   \n",
       "496   S700_2824                       1982 Camaro Z28        10348     46.53   \n",
       "497   S700_2824                       1982 Camaro Z28        10382     46.53   \n",
       "498   S700_4002             American Airlines: MD-11S        10198     36.27   \n",
       "499   S700_4002             American Airlines: MD-11S        10284     36.27   \n",
       "\n",
       "     priceeach  keuntungan  \n",
       "0       214.30      115.72  \n",
       "1       197.16       98.58  \n",
       "2       205.73      107.15  \n",
       "3       212.16      113.58  \n",
       "4       207.87      109.29  \n",
       "5       203.59      105.01  \n",
       "6       203.59      105.01  \n",
       "7       205.73      107.15  \n",
       "8       214.30      115.72  \n",
       "9       205.73      107.15  \n",
       "10      210.01      111.43  \n",
       "11      201.44      102.86  \n",
       "12      214.30      115.72  \n",
       "13      199.30      100.72  \n",
       "14      205.73      107.15  \n",
       "15      201.44      102.86  \n",
       "16      187.85       96.83  \n",
       "17      182.04       91.02  \n",
       "18      191.72      100.70  \n",
       "19      189.79       98.77  \n",
       "20      189.79       98.77  \n",
       "21      193.66      102.64  \n",
       "22      187.85       96.83  \n",
       "23      189.79       98.77  \n",
       "24      182.04       91.02  \n",
       "25      190.68       95.34  \n",
       "26      192.62       97.28  \n",
       "27      205.72      110.13  \n",
       "28      195.33       99.74  \n",
       "29      193.25       97.66  \n",
       "..         ...         ...  \n",
       "470      83.28       43.45  \n",
       "471      85.99       46.16  \n",
       "472      83.28       43.45  \n",
       "473      89.61       49.78  \n",
       "474      81.47       41.64  \n",
       "475      86.90       47.07  \n",
       "476      85.99       46.16  \n",
       "477      85.09       45.26  \n",
       "478      82.37       42.54  \n",
       "479      72.28       38.31  \n",
       "480      69.39       35.42  \n",
       "481      69.39       35.42  \n",
       "482      67.94       33.97  \n",
       "483      69.39       35.42  \n",
       "484      68.67       34.70  \n",
       "485      71.56       37.59  \n",
       "486      68.67       34.70  \n",
       "487      70.11       36.14  \n",
       "488      69.39       35.42  \n",
       "489      94.07       47.54  \n",
       "490      97.10       50.57  \n",
       "491     101.15       54.62  \n",
       "492      95.08       48.55  \n",
       "493     101.15       54.62  \n",
       "494      96.09       49.56  \n",
       "495      94.07       47.54  \n",
       "496     100.14       53.61  \n",
       "497     101.15       54.62  \n",
       "498      74.03       37.76  \n",
       "499      73.29       37.02  \n",
       "\n",
       "[500 rows x 6 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "run(\"\"\"\n",
    "SELECT\n",
    "    productcode,\n",
    "    productname,\n",
    "    ordernumber,\n",
    "    buyprice,\n",
    "    priceeach,\n",
    "    priceeach-buyprice as keuntungan\n",
    "FROM\n",
    "    products\n",
    "        INNER JOIN\n",
    "    orderdetails USING (productcode)\n",
    "WHERE priceeach >= 2* buyprice\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. List the products ordered on a Monday."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>productcode</th>\n",
       "      <th>productname</th>\n",
       "      <th>ordernumber</th>\n",
       "      <th>day</th>\n",
       "      <th>orderdate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>S10_1678</td>\n",
       "      <td>1969 Harley Davidson Ultimate Chopper</td>\n",
       "      <td>10107</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2003-02-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>S10_1678</td>\n",
       "      <td>1969 Harley Davidson Ultimate Chopper</td>\n",
       "      <td>10145</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2003-08-25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>S10_1678</td>\n",
       "      <td>1969 Harley Davidson Ultimate Chopper</td>\n",
       "      <td>10201</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2003-12-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>S10_1678</td>\n",
       "      <td>1969 Harley Davidson Ultimate Chopper</td>\n",
       "      <td>10237</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2004-04-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>S10_1678</td>\n",
       "      <td>1969 Harley Davidson Ultimate Chopper</td>\n",
       "      <td>10263</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2004-06-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>S10_1678</td>\n",
       "      <td>1969 Harley Davidson Ultimate Chopper</td>\n",
       "      <td>10329</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2004-11-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>S10_1949</td>\n",
       "      <td>1952 Alpine Renault 1300</td>\n",
       "      <td>10112</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2003-03-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>S10_1949</td>\n",
       "      <td>1952 Alpine Renault 1300</td>\n",
       "      <td>10163</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2003-10-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>S10_1949</td>\n",
       "      <td>1952 Alpine Renault 1300</td>\n",
       "      <td>10270</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2004-07-19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>S10_1949</td>\n",
       "      <td>1952 Alpine Renault 1300</td>\n",
       "      <td>10304</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2004-10-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>S10_1949</td>\n",
       "      <td>1952 Alpine Renault 1300</td>\n",
       "      <td>10347</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2004-11-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>S10_2016</td>\n",
       "      <td>1996 Moto Guzzi 1100i</td>\n",
       "      <td>10107</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2003-02-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>S10_2016</td>\n",
       "      <td>1996 Moto Guzzi 1100i</td>\n",
       "      <td>10145</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2003-08-25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>S10_2016</td>\n",
       "      <td>1996 Moto Guzzi 1100i</td>\n",
       "      <td>10201</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2003-12-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>S10_2016</td>\n",
       "      <td>1996 Moto Guzzi 1100i</td>\n",
       "      <td>10210</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2004-01-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>S10_2016</td>\n",
       "      <td>1996 Moto Guzzi 1100i</td>\n",
       "      <td>10263</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2004-06-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>S10_2016</td>\n",
       "      <td>1996 Moto Guzzi 1100i</td>\n",
       "      <td>10298</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2004-09-27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>S10_2016</td>\n",
       "      <td>1996 Moto Guzzi 1100i</td>\n",
       "      <td>10329</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2004-11-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>S10_4698</td>\n",
       "      <td>2003 Harley-Davidson Eagle Drag Bike</td>\n",
       "      <td>10107</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2003-02-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>S10_4698</td>\n",
       "      <td>2003 Harley-Davidson Eagle Drag Bike</td>\n",
       "      <td>10145</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2003-08-25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>S10_4698</td>\n",
       "      <td>2003 Harley-Davidson Eagle Drag Bike</td>\n",
       "      <td>10201</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2003-12-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>S10_4698</td>\n",
       "      <td>2003 Harley-Davidson Eagle Drag Bike</td>\n",
       "      <td>10210</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2004-01-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>S10_4698</td>\n",
       "      <td>2003 Harley-Davidson Eagle Drag Bike</td>\n",
       "      <td>10237</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2004-04-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>S10_4698</td>\n",
       "      <td>2003 Harley-Davidson Eagle Drag Bike</td>\n",
       "      <td>10263</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2004-06-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>S10_4698</td>\n",
       "      <td>2003 Harley-Davidson Eagle Drag Bike</td>\n",
       "      <td>10329</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2004-11-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>S10_4757</td>\n",
       "      <td>1972 Alfa Romeo GTA</td>\n",
       "      <td>10119</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2003-04-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>S10_4757</td>\n",
       "      <td>1972 Alfa Romeo GTA</td>\n",
       "      <td>10155</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2003-10-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>S10_4757</td>\n",
       "      <td>1972 Alfa Romeo GTA</td>\n",
       "      <td>10316</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2004-11-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>S10_4757</td>\n",
       "      <td>1972 Alfa Romeo GTA</td>\n",
       "      <td>10373</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2005-01-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>S10_4962</td>\n",
       "      <td>1962 LanciaA Delta 16V</td>\n",
       "      <td>10270</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2004-07-19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>377</th>\n",
       "      <td>S700_2834</td>\n",
       "      <td>ATA: B757-300</td>\n",
       "      <td>10200</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2003-12-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>378</th>\n",
       "      <td>S700_2834</td>\n",
       "      <td>ATA: B757-300</td>\n",
       "      <td>10210</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2004-01-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>379</th>\n",
       "      <td>S700_2834</td>\n",
       "      <td>ATA: B757-300</td>\n",
       "      <td>10263</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2004-06-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>380</th>\n",
       "      <td>S700_3167</td>\n",
       "      <td>F/A 18 Hornet 1/72</td>\n",
       "      <td>10106</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2003-02-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>381</th>\n",
       "      <td>S700_3167</td>\n",
       "      <td>F/A 18 Hornet 1/72</td>\n",
       "      <td>10155</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2003-10-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>382</th>\n",
       "      <td>S700_3167</td>\n",
       "      <td>F/A 18 Hornet 1/72</td>\n",
       "      <td>10199</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2003-12-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>383</th>\n",
       "      <td>S700_3167</td>\n",
       "      <td>F/A 18 Hornet 1/72</td>\n",
       "      <td>10210</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2004-01-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>384</th>\n",
       "      <td>S700_3167</td>\n",
       "      <td>F/A 18 Hornet 1/72</td>\n",
       "      <td>10316</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2004-11-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>385</th>\n",
       "      <td>S700_3505</td>\n",
       "      <td>The Titanic</td>\n",
       "      <td>10118</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2003-04-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>386</th>\n",
       "      <td>S700_3505</td>\n",
       "      <td>The Titanic</td>\n",
       "      <td>10373</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2005-01-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>387</th>\n",
       "      <td>S700_3505</td>\n",
       "      <td>The Titanic</td>\n",
       "      <td>10397</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2005-03-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>388</th>\n",
       "      <td>S700_3962</td>\n",
       "      <td>The Queen Mary</td>\n",
       "      <td>10373</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2005-01-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>389</th>\n",
       "      <td>S700_3962</td>\n",
       "      <td>The Queen Mary</td>\n",
       "      <td>10397</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2005-03-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>390</th>\n",
       "      <td>S700_4002</td>\n",
       "      <td>American Airlines: MD-11S</td>\n",
       "      <td>10106</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2003-02-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>391</th>\n",
       "      <td>S700_4002</td>\n",
       "      <td>American Airlines: MD-11S</td>\n",
       "      <td>10119</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2003-04-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>392</th>\n",
       "      <td>S700_4002</td>\n",
       "      <td>American Airlines: MD-11S</td>\n",
       "      <td>10131</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2003-06-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>393</th>\n",
       "      <td>S700_4002</td>\n",
       "      <td>American Airlines: MD-11S</td>\n",
       "      <td>10155</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2003-10-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>394</th>\n",
       "      <td>S700_4002</td>\n",
       "      <td>American Airlines: MD-11S</td>\n",
       "      <td>10210</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2004-01-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>395</th>\n",
       "      <td>S700_4002</td>\n",
       "      <td>American Airlines: MD-11S</td>\n",
       "      <td>10316</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2004-11-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>396</th>\n",
       "      <td>S700_4002</td>\n",
       "      <td>American Airlines: MD-11S</td>\n",
       "      <td>10373</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2005-01-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>397</th>\n",
       "      <td>S72_1253</td>\n",
       "      <td>Boeing X-32A JSF</td>\n",
       "      <td>10106</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2003-02-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>398</th>\n",
       "      <td>S72_1253</td>\n",
       "      <td>Boeing X-32A JSF</td>\n",
       "      <td>10119</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2003-04-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>399</th>\n",
       "      <td>S72_1253</td>\n",
       "      <td>Boeing X-32A JSF</td>\n",
       "      <td>10131</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2003-06-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>400</th>\n",
       "      <td>S72_1253</td>\n",
       "      <td>Boeing X-32A JSF</td>\n",
       "      <td>10155</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2003-10-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>401</th>\n",
       "      <td>S72_1253</td>\n",
       "      <td>Boeing X-32A JSF</td>\n",
       "      <td>10316</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2004-11-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>402</th>\n",
       "      <td>S72_1253</td>\n",
       "      <td>Boeing X-32A JSF</td>\n",
       "      <td>10329</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2004-11-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>403</th>\n",
       "      <td>S72_1253</td>\n",
       "      <td>Boeing X-32A JSF</td>\n",
       "      <td>10373</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2005-01-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>404</th>\n",
       "      <td>S72_1253</td>\n",
       "      <td>Boeing X-32A JSF</td>\n",
       "      <td>10415</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2005-05-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>405</th>\n",
       "      <td>S72_3212</td>\n",
       "      <td>Pont Yacht</td>\n",
       "      <td>10373</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2005-01-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>406</th>\n",
       "      <td>S72_3212</td>\n",
       "      <td>Pont Yacht</td>\n",
       "      <td>10397</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2005-03-28</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>407 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    productcode                            productname  ordernumber     day  \\\n",
       "0      S10_1678  1969 Harley Davidson Ultimate Chopper        10107  Monday   \n",
       "1      S10_1678  1969 Harley Davidson Ultimate Chopper        10145  Monday   \n",
       "2      S10_1678  1969 Harley Davidson Ultimate Chopper        10201  Monday   \n",
       "3      S10_1678  1969 Harley Davidson Ultimate Chopper        10237  Monday   \n",
       "4      S10_1678  1969 Harley Davidson Ultimate Chopper        10263  Monday   \n",
       "5      S10_1678  1969 Harley Davidson Ultimate Chopper        10329  Monday   \n",
       "6      S10_1949               1952 Alpine Renault 1300        10112  Monday   \n",
       "7      S10_1949               1952 Alpine Renault 1300        10163  Monday   \n",
       "8      S10_1949               1952 Alpine Renault 1300        10270  Monday   \n",
       "9      S10_1949               1952 Alpine Renault 1300        10304  Monday   \n",
       "10     S10_1949               1952 Alpine Renault 1300        10347  Monday   \n",
       "11     S10_2016                  1996 Moto Guzzi 1100i        10107  Monday   \n",
       "12     S10_2016                  1996 Moto Guzzi 1100i        10145  Monday   \n",
       "13     S10_2016                  1996 Moto Guzzi 1100i        10201  Monday   \n",
       "14     S10_2016                  1996 Moto Guzzi 1100i        10210  Monday   \n",
       "15     S10_2016                  1996 Moto Guzzi 1100i        10263  Monday   \n",
       "16     S10_2016                  1996 Moto Guzzi 1100i        10298  Monday   \n",
       "17     S10_2016                  1996 Moto Guzzi 1100i        10329  Monday   \n",
       "18     S10_4698   2003 Harley-Davidson Eagle Drag Bike        10107  Monday   \n",
       "19     S10_4698   2003 Harley-Davidson Eagle Drag Bike        10145  Monday   \n",
       "20     S10_4698   2003 Harley-Davidson Eagle Drag Bike        10201  Monday   \n",
       "21     S10_4698   2003 Harley-Davidson Eagle Drag Bike        10210  Monday   \n",
       "22     S10_4698   2003 Harley-Davidson Eagle Drag Bike        10237  Monday   \n",
       "23     S10_4698   2003 Harley-Davidson Eagle Drag Bike        10263  Monday   \n",
       "24     S10_4698   2003 Harley-Davidson Eagle Drag Bike        10329  Monday   \n",
       "25     S10_4757                    1972 Alfa Romeo GTA        10119  Monday   \n",
       "26     S10_4757                    1972 Alfa Romeo GTA        10155  Monday   \n",
       "27     S10_4757                    1972 Alfa Romeo GTA        10316  Monday   \n",
       "28     S10_4757                    1972 Alfa Romeo GTA        10373  Monday   \n",
       "29     S10_4962                 1962 LanciaA Delta 16V        10270  Monday   \n",
       "..          ...                                    ...          ...     ...   \n",
       "377   S700_2834                          ATA: B757-300        10200  Monday   \n",
       "378   S700_2834                          ATA: B757-300        10210  Monday   \n",
       "379   S700_2834                          ATA: B757-300        10263  Monday   \n",
       "380   S700_3167                     F/A 18 Hornet 1/72        10106  Monday   \n",
       "381   S700_3167                     F/A 18 Hornet 1/72        10155  Monday   \n",
       "382   S700_3167                     F/A 18 Hornet 1/72        10199  Monday   \n",
       "383   S700_3167                     F/A 18 Hornet 1/72        10210  Monday   \n",
       "384   S700_3167                     F/A 18 Hornet 1/72        10316  Monday   \n",
       "385   S700_3505                            The Titanic        10118  Monday   \n",
       "386   S700_3505                            The Titanic        10373  Monday   \n",
       "387   S700_3505                            The Titanic        10397  Monday   \n",
       "388   S700_3962                         The Queen Mary        10373  Monday   \n",
       "389   S700_3962                         The Queen Mary        10397  Monday   \n",
       "390   S700_4002              American Airlines: MD-11S        10106  Monday   \n",
       "391   S700_4002              American Airlines: MD-11S        10119  Monday   \n",
       "392   S700_4002              American Airlines: MD-11S        10131  Monday   \n",
       "393   S700_4002              American Airlines: MD-11S        10155  Monday   \n",
       "394   S700_4002              American Airlines: MD-11S        10210  Monday   \n",
       "395   S700_4002              American Airlines: MD-11S        10316  Monday   \n",
       "396   S700_4002              American Airlines: MD-11S        10373  Monday   \n",
       "397    S72_1253                       Boeing X-32A JSF        10106  Monday   \n",
       "398    S72_1253                       Boeing X-32A JSF        10119  Monday   \n",
       "399    S72_1253                       Boeing X-32A JSF        10131  Monday   \n",
       "400    S72_1253                       Boeing X-32A JSF        10155  Monday   \n",
       "401    S72_1253                       Boeing X-32A JSF        10316  Monday   \n",
       "402    S72_1253                       Boeing X-32A JSF        10329  Monday   \n",
       "403    S72_1253                       Boeing X-32A JSF        10373  Monday   \n",
       "404    S72_1253                       Boeing X-32A JSF        10415  Monday   \n",
       "405    S72_3212                             Pont Yacht        10373  Monday   \n",
       "406    S72_3212                             Pont Yacht        10397  Monday   \n",
       "\n",
       "      orderdate  \n",
       "0    2003-02-24  \n",
       "1    2003-08-25  \n",
       "2    2003-12-01  \n",
       "3    2004-04-05  \n",
       "4    2004-06-28  \n",
       "5    2004-11-15  \n",
       "6    2003-03-24  \n",
       "7    2003-10-20  \n",
       "8    2004-07-19  \n",
       "9    2004-10-11  \n",
       "10   2004-11-29  \n",
       "11   2003-02-24  \n",
       "12   2003-08-25  \n",
       "13   2003-12-01  \n",
       "14   2004-01-12  \n",
       "15   2004-06-28  \n",
       "16   2004-09-27  \n",
       "17   2004-11-15  \n",
       "18   2003-02-24  \n",
       "19   2003-08-25  \n",
       "20   2003-12-01  \n",
       "21   2004-01-12  \n",
       "22   2004-04-05  \n",
       "23   2004-06-28  \n",
       "24   2004-11-15  \n",
       "25   2003-04-28  \n",
       "26   2003-10-06  \n",
       "27   2004-11-01  \n",
       "28   2005-01-31  \n",
       "29   2004-07-19  \n",
       "..          ...  \n",
       "377  2003-12-01  \n",
       "378  2004-01-12  \n",
       "379  2004-06-28  \n",
       "380  2003-02-17  \n",
       "381  2003-10-06  \n",
       "382  2003-12-01  \n",
       "383  2004-01-12  \n",
       "384  2004-11-01  \n",
       "385  2003-04-21  \n",
       "386  2005-01-31  \n",
       "387  2005-03-28  \n",
       "388  2005-01-31  \n",
       "389  2005-03-28  \n",
       "390  2003-02-17  \n",
       "391  2003-04-28  \n",
       "392  2003-06-16  \n",
       "393  2003-10-06  \n",
       "394  2004-01-12  \n",
       "395  2004-11-01  \n",
       "396  2005-01-31  \n",
       "397  2003-02-17  \n",
       "398  2003-04-28  \n",
       "399  2003-06-16  \n",
       "400  2003-10-06  \n",
       "401  2004-11-01  \n",
       "402  2004-11-15  \n",
       "403  2005-01-31  \n",
       "404  2005-05-09  \n",
       "405  2005-01-31  \n",
       "406  2005-03-28  \n",
       "\n",
       "[407 rows x 5 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "run(\"\"\"\n",
    "SELECT\n",
    "    productcode,\n",
    "    productname,\n",
    "    ordernumber,\n",
    "    dayname(orderdate) as day,\n",
    "    orderdate\n",
    "FROM\n",
    "    products\n",
    "        INNER JOIN\n",
    "    orderdetails USING (productcode)\n",
    "        INNER JOIN\n",
    "    orders USING (ordernumber)\n",
    "WHERE DAYOFWEEK(orderdate) = 2\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8. What is the quantity on hand for products listed on 'On Hold' orders?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>jumlah_produk</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1994.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   jumlah_produk\n",
       "0         1994.0"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "run(\"\"\"\n",
    "SELECT \n",
    "    SUM(quantityordered) as jumlah_produk\n",
    "FROM\n",
    "    orders\n",
    "        INNER JOIN\n",
    "    orderdetails USING (ordernumber)\n",
    "WHERE status='on hold'\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}