ddcps_cra.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "47455306",
   "metadata": {},
   "source": [
    "# Database Engineer Capstone"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "70157bba",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "import mysql.connector as connector"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "596e7a95",
   "metadata": {},
   "source": [
    "## Load Data from SQL database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3ff92281",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Create a connection\n",
    "\n",
    "mydb = connector.connect(\n",
    "  user=\"root\",\n",
    "  passwd=\"\",\n",
    "    database=\"littlelemon_db\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "2afee199",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<mysql.connector.connection_cext.CMySQLConnection object at 0x000001CC283E9CD0>\n"
     ]
    }
   ],
   "source": [
    "print(mydb)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "6fcbf540",
   "metadata": {},
   "outputs": [],
   "source": [
    "mycursor = mydb.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "e5aabbd2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<mysql.connector.cursor_cext.CMySQLCursor at 0x1cc283e9df0>"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mycursor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "73c8f465",
   "metadata": {},
   "outputs": [],
   "source": [
    "mydb.reconnect() #Reconnect cursor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "852ac931",
   "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>Tables_in_littlelemon_db</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>littlelemon_data</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Tables_in_littlelemon_db\n",
       "0         littlelemon_data"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('SHOW tables', mydb)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "215120a6",
   "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>Field</th>\n",
       "      <th>Type</th>\n",
       "      <th>Null</th>\n",
       "      <th>Key</th>\n",
       "      <th>Default</th>\n",
       "      <th>Extra</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Row Number</td>\n",
       "      <td>b'int'</td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>OrderID</td>\n",
       "      <td>b'text'</td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>OrderDate</td>\n",
       "      <td>b'text'</td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DeliveryDate</td>\n",
       "      <td>b'text'</td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Customer ID</td>\n",
       "      <td>b'text'</td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Customer Name</td>\n",
       "      <td>b'text'</td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>City</td>\n",
       "      <td>b'text'</td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Country</td>\n",
       "      <td>b'text'</td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>PostalCode</td>\n",
       "      <td>b'text'</td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>CountryCode</td>\n",
       "      <td>b'text'</td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Cost</td>\n",
       "      <td>b'int'</td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Sales</td>\n",
       "      <td>b'double'</td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Quantity</td>\n",
       "      <td>b'int'</td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Discount</td>\n",
       "      <td>b'double'</td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>DeliveryCost</td>\n",
       "      <td>b'double'</td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>CourseName</td>\n",
       "      <td>b'text'</td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>CuisineName</td>\n",
       "      <td>b'text'</td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>StarterName</td>\n",
       "      <td>b'text'</td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>DesertName</td>\n",
       "      <td>b'text'</td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Drink</td>\n",
       "      <td>b'text'</td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Sides</td>\n",
       "      <td>b'text'</td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Field       Type Null Key Default Extra\n",
       "0      Row Number     b'int'  YES        None      \n",
       "1         OrderID    b'text'  YES        None      \n",
       "2       OrderDate    b'text'  YES        None      \n",
       "3    DeliveryDate    b'text'  YES        None      \n",
       "4     Customer ID    b'text'  YES        None      \n",
       "5   Customer Name    b'text'  YES        None      \n",
       "6            City    b'text'  YES        None      \n",
       "7         Country    b'text'  YES        None      \n",
       "8      PostalCode    b'text'  YES        None      \n",
       "9     CountryCode    b'text'  YES        None      \n",
       "10           Cost     b'int'  YES        None      \n",
       "11          Sales  b'double'  YES        None      \n",
       "12       Quantity     b'int'  YES        None      \n",
       "13       Discount  b'double'  YES        None      \n",
       "14   DeliveryCost  b'double'  YES        None      \n",
       "15     CourseName    b'text'  YES        None      \n",
       "16    CuisineName    b'text'  YES        None      \n",
       "17    StarterName    b'text'  YES        None      \n",
       "18     DesertName    b'text'  YES        None      \n",
       "19          Drink    b'text'  YES        None      \n",
       "20          Sides    b'text'  YES        None      "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('SHOW columns FROM littlelemon_data', mydb)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "401de35c",
   "metadata": {},
   "source": [
    "## SQL queries to analyse the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "5ef413b1",
   "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>Row Number</th>\n",
       "      <th>OrderID</th>\n",
       "      <th>OrderDate</th>\n",
       "      <th>DeliveryDate</th>\n",
       "      <th>Customer ID</th>\n",
       "      <th>Customer Name</th>\n",
       "      <th>City</th>\n",
       "      <th>Country</th>\n",
       "      <th>PostalCode</th>\n",
       "      <th>CountryCode</th>\n",
       "      <th>...</th>\n",
       "      <th>Sales</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>Discount</th>\n",
       "      <th>DeliveryCost</th>\n",
       "      <th>CourseName</th>\n",
       "      <th>CuisineName</th>\n",
       "      <th>StarterName</th>\n",
       "      <th>DesertName</th>\n",
       "      <th>Drink</th>\n",
       "      <th>Sides</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8</td>\n",
       "      <td>56-271-4185</td>\n",
       "      <td>10/9/2021</td>\n",
       "      <td>12/27/2020</td>\n",
       "      <td>83-800-4789</td>\n",
       "      <td>Audie Willcocks</td>\n",
       "      <td>PÃ³voa do Valado</td>\n",
       "      <td>Portugal</td>\n",
       "      <td>3810-760</td>\n",
       "      <td>PT</td>\n",
       "      <td>...</td>\n",
       "      <td>337.500</td>\n",
       "      <td>3</td>\n",
       "      <td>91.41</td>\n",
       "      <td>91.05</td>\n",
       "      <td>Bean soup</td>\n",
       "      <td>Turkish</td>\n",
       "      <td>Flatbread</td>\n",
       "      <td>Ice cream</td>\n",
       "      <td>Corfu Red Wine</td>\n",
       "      <td>Potato salad</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12</td>\n",
       "      <td>61-057-1975</td>\n",
       "      <td>6/26/2022</td>\n",
       "      <td>4/17/2021</td>\n",
       "      <td>72-013-3529</td>\n",
       "      <td>Ashil Brogini</td>\n",
       "      <td>Wakayama-shi</td>\n",
       "      <td>Japan</td>\n",
       "      <td>649-6326</td>\n",
       "      <td>JP</td>\n",
       "      <td>...</td>\n",
       "      <td>152.400</td>\n",
       "      <td>3</td>\n",
       "      <td>98.01</td>\n",
       "      <td>25.98</td>\n",
       "      <td>Shwarma</td>\n",
       "      <td>Turkish</td>\n",
       "      <td>Hummus</td>\n",
       "      <td>Baklava</td>\n",
       "      <td>Turkish Coffee</td>\n",
       "      <td>Fries</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13</td>\n",
       "      <td>87-599-0366</td>\n",
       "      <td>12/3/2020</td>\n",
       "      <td>1/7/2022</td>\n",
       "      <td>09-688-8521</td>\n",
       "      <td>Arthur Mathewson</td>\n",
       "      <td>Qingshan</td>\n",
       "      <td>China</td>\n",
       "      <td>353715</td>\n",
       "      <td>CN</td>\n",
       "      <td>...</td>\n",
       "      <td>139.695</td>\n",
       "      <td>2</td>\n",
       "      <td>99.24</td>\n",
       "      <td>96.84</td>\n",
       "      <td>Greek salad</td>\n",
       "      <td>Greek</td>\n",
       "      <td>Olives</td>\n",
       "      <td>Greek yoghurt</td>\n",
       "      <td>Athens White wine</td>\n",
       "      <td>Tapas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>27</td>\n",
       "      <td>00-465-7031</td>\n",
       "      <td>8/25/2020</td>\n",
       "      <td>9/3/2022</td>\n",
       "      <td>84-300-6499</td>\n",
       "      <td>Noni Quickenden</td>\n",
       "      <td>Mont-Royal</td>\n",
       "      <td>Canada</td>\n",
       "      <td>649-6327</td>\n",
       "      <td>CA</td>\n",
       "      <td>...</td>\n",
       "      <td>465.000</td>\n",
       "      <td>3</td>\n",
       "      <td>91.14</td>\n",
       "      <td>95.63</td>\n",
       "      <td>Pizza</td>\n",
       "      <td>Italian</td>\n",
       "      <td>Flatbread</td>\n",
       "      <td>Cheesecake</td>\n",
       "      <td>Italian Coffee</td>\n",
       "      <td>Bruschetta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>28</td>\n",
       "      <td>01-975-7149</td>\n",
       "      <td>4/22/2021</td>\n",
       "      <td>4/19/2020</td>\n",
       "      <td>93-913-0650</td>\n",
       "      <td>Alan Paskell</td>\n",
       "      <td>Longzui</td>\n",
       "      <td>China</td>\n",
       "      <td>33620</td>\n",
       "      <td>CN</td>\n",
       "      <td>...</td>\n",
       "      <td>337.500</td>\n",
       "      <td>3</td>\n",
       "      <td>97.48</td>\n",
       "      <td>27.41</td>\n",
       "      <td>Carbonara</td>\n",
       "      <td>Turkish</td>\n",
       "      <td>Minestrone</td>\n",
       "      <td>Affogato</td>\n",
       "      <td>Roma Red wine</td>\n",
       "      <td>Focaccia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>31</td>\n",
       "      <td>48-370-5933</td>\n",
       "      <td>1/28/2022</td>\n",
       "      <td>3/12/2021</td>\n",
       "      <td>35-042-6593</td>\n",
       "      <td>Horatio Lundy</td>\n",
       "      <td>Huangcun</td>\n",
       "      <td>China</td>\n",
       "      <td>352 01</td>\n",
       "      <td>CN</td>\n",
       "      <td>...</td>\n",
       "      <td>252.405</td>\n",
       "      <td>2</td>\n",
       "      <td>94.76</td>\n",
       "      <td>29.39</td>\n",
       "      <td>Greek salad</td>\n",
       "      <td>Italian</td>\n",
       "      <td>Hummus</td>\n",
       "      <td>Greek yoghurt</td>\n",
       "      <td>Athens White wine</td>\n",
       "      <td>Tapas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>39</td>\n",
       "      <td>30-867-3284</td>\n",
       "      <td>10/5/2020</td>\n",
       "      <td>12/19/2022</td>\n",
       "      <td>26-631-4044</td>\n",
       "      <td>Derry Davis</td>\n",
       "      <td>La Loma</td>\n",
       "      <td>Panama</td>\n",
       "      <td>353 01</td>\n",
       "      <td>PA</td>\n",
       "      <td>...</td>\n",
       "      <td>104.070</td>\n",
       "      <td>2</td>\n",
       "      <td>91.41</td>\n",
       "      <td>80.86</td>\n",
       "      <td>Pizza</td>\n",
       "      <td>Italian</td>\n",
       "      <td>Olives</td>\n",
       "      <td>Cheesecake</td>\n",
       "      <td>Italian Coffee</td>\n",
       "      <td>Bruschetta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>43</td>\n",
       "      <td>48-873-2607</td>\n",
       "      <td>9/29/2019</td>\n",
       "      <td>3/12/2022</td>\n",
       "      <td>77-064-4656</td>\n",
       "      <td>Tiertza Doogan</td>\n",
       "      <td>Ovsyanka</td>\n",
       "      <td>Russia</td>\n",
       "      <td>692771</td>\n",
       "      <td>RU</td>\n",
       "      <td>...</td>\n",
       "      <td>112.500</td>\n",
       "      <td>3</td>\n",
       "      <td>98.01</td>\n",
       "      <td>35.33</td>\n",
       "      <td>Greek salad</td>\n",
       "      <td>Italian</td>\n",
       "      <td>Falafel</td>\n",
       "      <td>Greek yoghurt</td>\n",
       "      <td>Athens White wine</td>\n",
       "      <td>Tapas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>44</td>\n",
       "      <td>46-671-2432</td>\n",
       "      <td>4/13/2022</td>\n",
       "      <td>3/18/2021</td>\n",
       "      <td>29-631-4932</td>\n",
       "      <td>Ilario Michie</td>\n",
       "      <td>Pindiga</td>\n",
       "      <td>Nigeria</td>\n",
       "      <td>649-6328</td>\n",
       "      <td>NG</td>\n",
       "      <td>...</td>\n",
       "      <td>330.000</td>\n",
       "      <td>3</td>\n",
       "      <td>99.24</td>\n",
       "      <td>31.82</td>\n",
       "      <td>Bean soup</td>\n",
       "      <td>Turkish</td>\n",
       "      <td>Hummus</td>\n",
       "      <td>Ice cream</td>\n",
       "      <td>Corfu Red Wine</td>\n",
       "      <td>Potato salad</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>58</td>\n",
       "      <td>56-545-1829</td>\n",
       "      <td>2/15/2022</td>\n",
       "      <td>8/26/2020</td>\n",
       "      <td>14-427-0652</td>\n",
       "      <td>Prudence Eastlake</td>\n",
       "      <td>Stockholm</td>\n",
       "      <td>Sweden</td>\n",
       "      <td>3925550</td>\n",
       "      <td>SE</td>\n",
       "      <td>...</td>\n",
       "      <td>199.755</td>\n",
       "      <td>1</td>\n",
       "      <td>91.14</td>\n",
       "      <td>63.51</td>\n",
       "      <td>Carbonara</td>\n",
       "      <td>Italian</td>\n",
       "      <td>Olives</td>\n",
       "      <td>Affogato</td>\n",
       "      <td>Roma Red wine</td>\n",
       "      <td>Focaccia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>59</td>\n",
       "      <td>42-906-2475</td>\n",
       "      <td>10/11/2021</td>\n",
       "      <td>6/2/2021</td>\n",
       "      <td>96-883-5571</td>\n",
       "      <td>Gwenora Beauchamp</td>\n",
       "      <td>Petauke</td>\n",
       "      <td>Zambia</td>\n",
       "      <td>3810-763</td>\n",
       "      <td>ZM</td>\n",
       "      <td>...</td>\n",
       "      <td>78.120</td>\n",
       "      <td>3</td>\n",
       "      <td>97.48</td>\n",
       "      <td>3.76</td>\n",
       "      <td>Kabasa</td>\n",
       "      <td>Italian</td>\n",
       "      <td>Flatbread</td>\n",
       "      <td>Turkish yoghurt</td>\n",
       "      <td>Ankara White Wine</td>\n",
       "      <td>Meatballs</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>62</td>\n",
       "      <td>20-698-8725</td>\n",
       "      <td>1/12/2021</td>\n",
       "      <td>8/24/2021</td>\n",
       "      <td>89-302-9477</td>\n",
       "      <td>Jackie Kment</td>\n",
       "      <td>Báº¿n Cáº§u</td>\n",
       "      <td>Vietnam</td>\n",
       "      <td>33622</td>\n",
       "      <td>VN</td>\n",
       "      <td>...</td>\n",
       "      <td>179.700</td>\n",
       "      <td>1</td>\n",
       "      <td>94.76</td>\n",
       "      <td>81.98</td>\n",
       "      <td>Bean soup</td>\n",
       "      <td>Italian</td>\n",
       "      <td>Falafel</td>\n",
       "      <td>Ice cream</td>\n",
       "      <td>Corfu Red Wine</td>\n",
       "      <td>Potato salad</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>70</td>\n",
       "      <td>02-938-1657</td>\n",
       "      <td>10/16/2022</td>\n",
       "      <td>1/10/2020</td>\n",
       "      <td>25-492-4931</td>\n",
       "      <td>Deane Benbough</td>\n",
       "      <td>Dongxin</td>\n",
       "      <td>China</td>\n",
       "      <td>3791994</td>\n",
       "      <td>CN</td>\n",
       "      <td>...</td>\n",
       "      <td>337.500</td>\n",
       "      <td>1</td>\n",
       "      <td>91.41</td>\n",
       "      <td>48.78</td>\n",
       "      <td>Carbonara</td>\n",
       "      <td>Italian</td>\n",
       "      <td>Olives</td>\n",
       "      <td>Affogato</td>\n",
       "      <td>Roma Red wine</td>\n",
       "      <td>Focaccia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>74</td>\n",
       "      <td>89-262-2574</td>\n",
       "      <td>12/11/2019</td>\n",
       "      <td>3/27/2021</td>\n",
       "      <td>94-829-5714</td>\n",
       "      <td>Olive Jeannon</td>\n",
       "      <td>Kizhinga</td>\n",
       "      <td>Russia</td>\n",
       "      <td>4472915</td>\n",
       "      <td>RU</td>\n",
       "      <td>...</td>\n",
       "      <td>186.930</td>\n",
       "      <td>1</td>\n",
       "      <td>98.01</td>\n",
       "      <td>19.95</td>\n",
       "      <td>Bean soup</td>\n",
       "      <td>Italian</td>\n",
       "      <td>Falafel</td>\n",
       "      <td>Ice cream</td>\n",
       "      <td>Corfu Red Wine</td>\n",
       "      <td>Potato salad</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>75</td>\n",
       "      <td>56-933-2115</td>\n",
       "      <td>5/15/2022</td>\n",
       "      <td>7/14/2021</td>\n",
       "      <td>17-162-2135</td>\n",
       "      <td>Karoline Pocock</td>\n",
       "      <td>Bislig</td>\n",
       "      <td>Philippines</td>\n",
       "      <td>5020280</td>\n",
       "      <td>PH</td>\n",
       "      <td>...</td>\n",
       "      <td>216.135</td>\n",
       "      <td>3</td>\n",
       "      <td>99.24</td>\n",
       "      <td>22.75</td>\n",
       "      <td>Pizza</td>\n",
       "      <td>Italian</td>\n",
       "      <td>Hummus</td>\n",
       "      <td>Cheesecake</td>\n",
       "      <td>Italian Coffee</td>\n",
       "      <td>Bruschetta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>89</td>\n",
       "      <td>90-389-3298</td>\n",
       "      <td>8/24/2022</td>\n",
       "      <td>6/8/2021</td>\n",
       "      <td>24-902-5422</td>\n",
       "      <td>Delilah Ayerst</td>\n",
       "      <td>Ngedhusuba</td>\n",
       "      <td>Indonesia</td>\n",
       "      <td>987-0357</td>\n",
       "      <td>ID</td>\n",
       "      <td>...</td>\n",
       "      <td>480.000</td>\n",
       "      <td>2</td>\n",
       "      <td>91.14</td>\n",
       "      <td>29.57</td>\n",
       "      <td>Kabasa</td>\n",
       "      <td>Greek</td>\n",
       "      <td>Olives</td>\n",
       "      <td>Turkish yoghurt</td>\n",
       "      <td>Ankara White Wine</td>\n",
       "      <td>Meatballs</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>90</td>\n",
       "      <td>84-017-7714</td>\n",
       "      <td>2/22/2020</td>\n",
       "      <td>2/22/2022</td>\n",
       "      <td>38-777-7517</td>\n",
       "      <td>Hynda Skeel</td>\n",
       "      <td>Mata-Utu</td>\n",
       "      <td>Wallis and Futuna</td>\n",
       "      <td>356 01</td>\n",
       "      <td>WF</td>\n",
       "      <td>...</td>\n",
       "      <td>315.000</td>\n",
       "      <td>1</td>\n",
       "      <td>97.48</td>\n",
       "      <td>71.10</td>\n",
       "      <td>Shwarma</td>\n",
       "      <td>Italian</td>\n",
       "      <td>Flatbread</td>\n",
       "      <td>Baklava</td>\n",
       "      <td>Turkish Coffee</td>\n",
       "      <td>Fries</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>17 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Row Number      OrderID   OrderDate DeliveryDate  Customer ID  \\\n",
       "0            8  56-271-4185   10/9/2021   12/27/2020  83-800-4789   \n",
       "1           12  61-057-1975   6/26/2022    4/17/2021  72-013-3529   \n",
       "2           13  87-599-0366   12/3/2020     1/7/2022  09-688-8521   \n",
       "3           27  00-465-7031   8/25/2020     9/3/2022  84-300-6499   \n",
       "4           28  01-975-7149   4/22/2021    4/19/2020  93-913-0650   \n",
       "5           31  48-370-5933   1/28/2022    3/12/2021  35-042-6593   \n",
       "6           39  30-867-3284   10/5/2020   12/19/2022  26-631-4044   \n",
       "7           43  48-873-2607   9/29/2019    3/12/2022  77-064-4656   \n",
       "8           44  46-671-2432   4/13/2022    3/18/2021  29-631-4932   \n",
       "9           58  56-545-1829   2/15/2022    8/26/2020  14-427-0652   \n",
       "10          59  42-906-2475  10/11/2021     6/2/2021  96-883-5571   \n",
       "11          62  20-698-8725   1/12/2021    8/24/2021  89-302-9477   \n",
       "12          70  02-938-1657  10/16/2022    1/10/2020  25-492-4931   \n",
       "13          74  89-262-2574  12/11/2019    3/27/2021  94-829-5714   \n",
       "14          75  56-933-2115   5/15/2022    7/14/2021  17-162-2135   \n",
       "15          89  90-389-3298   8/24/2022     6/8/2021  24-902-5422   \n",
       "16          90  84-017-7714   2/22/2020    2/22/2022  38-777-7517   \n",
       "\n",
       "        Customer Name              City            Country PostalCode  \\\n",
       "0     Audie Willcocks  PÃ³voa do Valado           Portugal   3810-760   \n",
       "1       Ashil Brogini      Wakayama-shi              Japan   649-6326   \n",
       "2    Arthur Mathewson          Qingshan              China     353715   \n",
       "3     Noni Quickenden        Mont-Royal             Canada   649-6327   \n",
       "4        Alan Paskell           Longzui              China      33620   \n",
       "5       Horatio Lundy          Huangcun              China     352 01   \n",
       "6         Derry Davis           La Loma             Panama     353 01   \n",
       "7      Tiertza Doogan          Ovsyanka             Russia     692771   \n",
       "8       Ilario Michie           Pindiga            Nigeria   649-6328   \n",
       "9   Prudence Eastlake         Stockholm             Sweden    3925550   \n",
       "10  Gwenora Beauchamp           Petauke             Zambia   3810-763   \n",
       "11       Jackie Kment       Báº¿n Cáº§u            Vietnam      33622   \n",
       "12     Deane Benbough           Dongxin              China    3791994   \n",
       "13      Olive Jeannon          Kizhinga             Russia    4472915   \n",
       "14    Karoline Pocock            Bislig        Philippines    5020280   \n",
       "15     Delilah Ayerst        Ngedhusuba          Indonesia   987-0357   \n",
       "16        Hynda Skeel          Mata-Utu  Wallis and Futuna     356 01   \n",
       "\n",
       "   CountryCode  ...    Sales  Quantity  Discount  DeliveryCost   CourseName  \\\n",
       "0           PT  ...  337.500         3     91.41         91.05    Bean soup   \n",
       "1           JP  ...  152.400         3     98.01         25.98      Shwarma   \n",
       "2           CN  ...  139.695         2     99.24         96.84  Greek salad   \n",
       "3           CA  ...  465.000         3     91.14         95.63        Pizza   \n",
       "4           CN  ...  337.500         3     97.48         27.41    Carbonara   \n",
       "5           CN  ...  252.405         2     94.76         29.39  Greek salad   \n",
       "6           PA  ...  104.070         2     91.41         80.86        Pizza   \n",
       "7           RU  ...  112.500         3     98.01         35.33  Greek salad   \n",
       "8           NG  ...  330.000         3     99.24         31.82    Bean soup   \n",
       "9           SE  ...  199.755         1     91.14         63.51    Carbonara   \n",
       "10          ZM  ...   78.120         3     97.48          3.76       Kabasa   \n",
       "11          VN  ...  179.700         1     94.76         81.98    Bean soup   \n",
       "12          CN  ...  337.500         1     91.41         48.78    Carbonara   \n",
       "13          RU  ...  186.930         1     98.01         19.95    Bean soup   \n",
       "14          PH  ...  216.135         3     99.24         22.75        Pizza   \n",
       "15          ID  ...  480.000         2     91.14         29.57       Kabasa   \n",
       "16          WF  ...  315.000         1     97.48         71.10      Shwarma   \n",
       "\n",
       "   CuisineName StarterName       DesertName              Drink         Sides  \n",
       "0      Turkish   Flatbread        Ice cream     Corfu Red Wine  Potato salad  \n",
       "1      Turkish      Hummus          Baklava     Turkish Coffee         Fries  \n",
       "2        Greek      Olives    Greek yoghurt  Athens White wine         Tapas  \n",
       "3      Italian   Flatbread       Cheesecake     Italian Coffee    Bruschetta  \n",
       "4      Turkish  Minestrone         Affogato      Roma Red wine      Focaccia  \n",
       "5      Italian      Hummus    Greek yoghurt  Athens White wine         Tapas  \n",
       "6      Italian      Olives       Cheesecake     Italian Coffee    Bruschetta  \n",
       "7      Italian     Falafel    Greek yoghurt  Athens White wine         Tapas  \n",
       "8      Turkish      Hummus        Ice cream     Corfu Red Wine  Potato salad  \n",
       "9      Italian      Olives         Affogato      Roma Red wine      Focaccia  \n",
       "10     Italian   Flatbread  Turkish yoghurt  Ankara White Wine     Meatballs  \n",
       "11     Italian     Falafel        Ice cream     Corfu Red Wine  Potato salad  \n",
       "12     Italian      Olives         Affogato      Roma Red wine      Focaccia  \n",
       "13     Italian     Falafel        Ice cream     Corfu Red Wine  Potato salad  \n",
       "14     Italian      Hummus       Cheesecake     Italian Coffee    Bruschetta  \n",
       "15       Greek      Olives  Turkish yoghurt  Ankara White Wine     Meatballs  \n",
       "16     Italian   Flatbread          Baklava     Turkish Coffee         Fries  \n",
       "\n",
       "[17 rows x 21 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('SELECT * from littlelemon_data WHERE Discount > 90', mydb)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "546cd055",
   "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>Sides</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tapas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Potato salad</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bruschetta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Focaccia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Meatballs</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Fries</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Sides\n",
       "0         Tapas\n",
       "1  Potato salad\n",
       "2    Bruschetta\n",
       "3      Focaccia\n",
       "4     Meatballs\n",
       "5         Fries"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('SELECT DISTINCT(Sides) from littlelemon_data', mydb)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "59ea99ff",
   "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>CuisineName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Greek</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Italian</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Turkish</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  CuisineName\n",
       "0       Greek\n",
       "1     Italian\n",
       "2     Turkish"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('SELECT DISTINCT(CuisineName) from littlelemon_data', mydb)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "b770cd74",
   "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>Drink</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Athens White wine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Corfu Red Wine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Italian Coffee</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Roma Red wine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Ankara White Wine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Turkish Coffee</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               Drink\n",
       "0  Athens White wine\n",
       "1     Corfu Red Wine\n",
       "2     Italian Coffee\n",
       "3      Roma Red wine\n",
       "4  Ankara White Wine\n",
       "5     Turkish Coffee"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('SELECT DISTINCT(Drink) from littlelemon_data', mydb)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "55fefa69",
   "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>SUM(Sales)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20843.61</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   SUM(Sales)\n",
       "0    20843.61"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('SELECT SUM(Sales) from littlelemon_data', mydb)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "9aba2616",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Country</th>\n",
       "      <th>Sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Indonesia</td>\n",
       "      <td>480.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Thailand</td>\n",
       "      <td>480.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Libya</td>\n",
       "      <td>480.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Indonesia</td>\n",
       "      <td>480.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Russia</td>\n",
       "      <td>480.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>Zambia</td>\n",
       "      <td>78.120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>Panama</td>\n",
       "      <td>66.855</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>Russia</td>\n",
       "      <td>66.855</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>Azerbaijan</td>\n",
       "      <td>66.855</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>China</td>\n",
       "      <td>66.855</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>91 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Country    Sales\n",
       "0    Indonesia  480.000\n",
       "1     Thailand  480.000\n",
       "2        Libya  480.000\n",
       "3    Indonesia  480.000\n",
       "4       Russia  480.000\n",
       "..         ...      ...\n",
       "86      Zambia   78.120\n",
       "87      Panama   66.855\n",
       "88      Russia   66.855\n",
       "89  Azerbaijan   66.855\n",
       "90       China   66.855\n",
       "\n",
       "[91 rows x 2 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('SELECT Country, Sales from littlelemon_data Order By Sales DESC', mydb)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5e34d01e",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f28ea0cc",
   "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.11.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}