sqliteproject.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "82fd91c4-1beb-4120-8af9-16def8833649",
   "metadata": {
    "tags": []
   },
   "source": [
    "## SQLite Project\n",
    "Fatih Emre Ozturk"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e8026d79-866f-433b-b52f-f8b1a1431e65",
   "metadata": {},
   "source": [
    "In this project, I will show my SQLite skills in JupyterLab by using a database which I created from excel files with SQLite."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1cdb92fd-06eb-466f-af43-d2b87daf0184",
   "metadata": {},
   "source": [
    "#### JupyterLab - Database Connection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "ba0536db-32bb-48c9-90e5-cf73f7509876",
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "29b8c38e-5a62-479b-9dcb-25005ecd0c59",
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql sqlite:///sqlite.db"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d4c0a4ec-330b-477c-9e25-aeb51ffe58f8",
   "metadata": {},
   "source": [
    "#### The First Query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "c26e8346-2069-4f96-a1bc-de9da6e20dc7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>CustomerID</th>\n",
       "        <th>CustomerName</th>\n",
       "        <th>ContactName</th>\n",
       "        <th>Address</th>\n",
       "        <th>City</th>\n",
       "        <th>PostalCode</th>\n",
       "        <th>Country</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Alfreds Futterkiste</td>\n",
       "        <td>Maria Anders</td>\n",
       "        <td>Obere Str. 57</td>\n",
       "        <td>Berlin</td>\n",
       "        <td>12209</td>\n",
       "        <td>Germany</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Ana Trujillo Emparedados y helados</td>\n",
       "        <td>Ana Trujillo</td>\n",
       "        <td>Avda. de la Constitución 2222</td>\n",
       "        <td>México D.F.</td>\n",
       "        <td>5021</td>\n",
       "        <td>Mexico</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Antonio Moreno Taquería</td>\n",
       "        <td>Antonio Moreno</td>\n",
       "        <td>Mataderos 2312</td>\n",
       "        <td>México D.F.</td>\n",
       "        <td>5023</td>\n",
       "        <td>Mexico</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'Alfreds Futterkiste', 'Maria Anders', 'Obere Str. 57', 'Berlin', '12209', 'Germany'),\n",
       " (2, 'Ana Trujillo Emparedados y helados', 'Ana Trujillo', 'Avda. de la Constitución 2222', 'México D.F.', '5021', 'Mexico'),\n",
       " (3, 'Antonio Moreno Taquería', 'Antonio Moreno', 'Mataderos 2312', 'México D.F.', '5023', 'Mexico')]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT *\n",
    "FROM Customers\n",
    "LIMIT 3"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6c662cd3-852c-4a9e-b6c8-c2f0ab10fa68",
   "metadata": {},
   "source": [
    "#### Basic Queries "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4179ba3b-428d-49a7-ad21-6dfc561b4d03",
   "metadata": {},
   "source": [
    "What are the table names?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "bf64fca2-bc9c-4272-8829-db81ac187fc9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Categories</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Customers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Employees</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>OrderDetails</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Orders</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Products</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Shippers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Suppliers</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Categories',),\n",
       " ('Customers',),\n",
       " ('Employees',),\n",
       " ('OrderDetails',),\n",
       " ('Orders',),\n",
       " ('Products',),\n",
       " ('Shippers',),\n",
       " ('Suppliers',)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name \n",
    "FROM sqlite_master \n",
    "WHERE type=\"table\";"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "21f95a92-fa76-4634-b0fe-d9ccb340719f",
   "metadata": {},
   "source": [
    "Selecting Specific Columns from Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "1952c191-20a6-47b3-9d72-0364366f7234",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>ProductID</th>\n",
       "        <th>ProductName</th>\n",
       "        <th>Unit</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Chais</td>\n",
       "        <td>10 boxes x 20 bags</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Chang</td>\n",
       "        <td>24 - 12 oz bottles</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Aniseed Syrup</td>\n",
       "        <td>12 - 550 ml bottles</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'Chais', '10 boxes x 20 bags'),\n",
       " (2, 'Chang', '24 - 12 oz bottles'),\n",
       " (3, 'Aniseed Syrup', '12 - 550 ml bottles')]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT ProductID, ProductName, Unit \n",
    "FROM Products \n",
    "LIMIT 3;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "53aded48-6567-4466-a69d-b03306e99073",
   "metadata": {},
   "source": [
    "Mathematical Operations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "f166fc38-6c7a-4ddf-a752-5d43a07142ad",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Price</th>\n",
       "        <th>Price*2</th>\n",
       "        <th>Price*20/2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>18</td>\n",
       "        <td>36</td>\n",
       "        <td>180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>19</td>\n",
       "        <td>38</td>\n",
       "        <td>190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>20</td>\n",
       "        <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>22</td>\n",
       "        <td>44</td>\n",
       "        <td>220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>21</td>\n",
       "        <td>42</td>\n",
       "        <td>210</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(18, 36, 180), (19, 38, 190), (10, 20, 100), (22, 44, 220), (21, 42, 210)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT Price, Price*2, Price*20/2 from Products LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "15b35381-c09a-4d88-ad30-5f28e3f3802d",
   "metadata": {},
   "source": [
    "Alias"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "ce0b1a1d-52f3-44d1-bee8-1104ef2ec800",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Price</th>\n",
       "        <th>Double</th>\n",
       "        <th>third_price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>18</td>\n",
       "        <td>36</td>\n",
       "        <td>180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>19</td>\n",
       "        <td>38</td>\n",
       "        <td>190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>20</td>\n",
       "        <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>22</td>\n",
       "        <td>44</td>\n",
       "        <td>220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>21</td>\n",
       "        <td>42</td>\n",
       "        <td>210</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(18, 36, 180), (19, 38, 190), (10, 20, 100), (22, 44, 220), (21, 42, 210)]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT Price AS Price, Price*2 AS Double, Price*20/2 AS third_price \n",
    "FROM PRODUCTS \n",
    "LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fdcca2f6-26a6-40be-86ce-f83cd99d49a0",
   "metadata": {},
   "source": [
    "**Count |** How many customers are there?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "6218d59e-49a1-4242-950b-5866a23f5ad5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Customer_Count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>91</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(91,)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT COUNT(*) AS Customer_Count \n",
    "FROM Customers;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3ee1dbdd-b5ad-4993-9179-595bbede6e9b",
   "metadata": {},
   "source": [
    "**Order |** Ordering Products by their price"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "0b3f07c8-caa6-4ba6-aa1c-2a951b74afd6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>ProductID</th>\n",
       "        <th>ProductName</th>\n",
       "        <th>SupplierID</th>\n",
       "        <th>CategoryID</th>\n",
       "        <th>Unit</th>\n",
       "        <th>Price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>31</td>\n",
       "        <td>Gorgonzola Telino</td>\n",
       "        <td>14</td>\n",
       "        <td>4</td>\n",
       "        <td>12 - 100 g pkgs</td>\n",
       "        <td>435</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>38</td>\n",
       "        <td>Côte de Blaye</td>\n",
       "        <td>18</td>\n",
       "        <td>1</td>\n",
       "        <td>12 - 75 cl bottles</td>\n",
       "        <td>263</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>29</td>\n",
       "        <td>Thüringer Rostbratwurst</td>\n",
       "        <td>12</td>\n",
       "        <td>6</td>\n",
       "        <td>50 bags x 30 sausgs.</td>\n",
       "        <td>123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9</td>\n",
       "        <td>Mishi Kobe Niku</td>\n",
       "        <td>4</td>\n",
       "        <td>6</td>\n",
       "        <td>18 - 500 g pkgs.</td>\n",
       "        <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>20</td>\n",
       "        <td>Sir Rodney&#x27;s Marmalade</td>\n",
       "        <td>8</td>\n",
       "        <td>3</td>\n",
       "        <td>30 gift boxes</td>\n",
       "        <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>18</td>\n",
       "        <td>Carnarvon Tigers</td>\n",
       "        <td>7</td>\n",
       "        <td>8</td>\n",
       "        <td>16 kg pkg.</td>\n",
       "        <td>62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>59</td>\n",
       "        <td>Raclette Courdavault</td>\n",
       "        <td>28</td>\n",
       "        <td>4</td>\n",
       "        <td>5 kg pkg.</td>\n",
       "        <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>51</td>\n",
       "        <td>Manjimup Dried Apples</td>\n",
       "        <td>24</td>\n",
       "        <td>7</td>\n",
       "        <td>50 - 300 g pkgs.</td>\n",
       "        <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>62</td>\n",
       "        <td>Tarte au sucre</td>\n",
       "        <td>29</td>\n",
       "        <td>3</td>\n",
       "        <td>48 pies</td>\n",
       "        <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>43</td>\n",
       "        <td>Ipoh Coffee</td>\n",
       "        <td>20</td>\n",
       "        <td>1</td>\n",
       "        <td>16 - 500 g tins</td>\n",
       "        <td>46</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(31, 'Gorgonzola Telino', 14, 4, '12 - 100 g pkgs', 435),\n",
       " (38, 'Côte de Blaye', 18, 1, '12 - 75 cl bottles', 263),\n",
       " (29, 'Thüringer Rostbratwurst', 12, 6, '50 bags x 30 sausgs.', 123),\n",
       " (9, 'Mishi Kobe Niku', 4, 6, '18 - 500 g pkgs.', 97),\n",
       " (20, \"Sir Rodney's Marmalade\", 8, 3, '30 gift boxes', 81),\n",
       " (18, 'Carnarvon Tigers', 7, 8, '16 kg pkg.', 62),\n",
       " (59, 'Raclette Courdavault', 28, 4, '5 kg pkg.', 55),\n",
       " (51, 'Manjimup Dried Apples', 24, 7, '50 - 300 g pkgs.', 53),\n",
       " (62, 'Tarte au sucre', 29, 3, '48 pies', 49),\n",
       " (43, 'Ipoh Coffee', 20, 1, '16 - 500 g tins', 46)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM Products \n",
    "ORDER BY Price DESC \n",
    "LIMIT 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b576ad59-60cd-42fe-ad32-007b819371ce",
   "metadata": {},
   "source": [
    "**Where |** Filtering Customers in Sweden"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "0b4ca528-a870-4290-825e-72f10a1cb3d9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>CustomerID</th>\n",
       "        <th>CustomerName</th>\n",
       "        <th>ContactName</th>\n",
       "        <th>Address</th>\n",
       "        <th>City</th>\n",
       "        <th>PostalCode</th>\n",
       "        <th>Country</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>Berglunds snabbköp</td>\n",
       "        <td>Christina Berglund</td>\n",
       "        <td>Berguvsvägen 8</td>\n",
       "        <td>Luleå</td>\n",
       "        <td>S-958 22</td>\n",
       "        <td>Sweden</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>24</td>\n",
       "        <td>Folk och fä HB</td>\n",
       "        <td>Maria Larsson</td>\n",
       "        <td>Åkergatan 24</td>\n",
       "        <td>Bräcke</td>\n",
       "        <td>S-844 67</td>\n",
       "        <td>Sweden</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(5, 'Berglunds snabbköp', 'Christina Berglund', 'Berguvsvägen 8', 'Luleå', 'S-958 22', 'Sweden'),\n",
       " (24, 'Folk och fä HB', 'Maria Larsson', 'Åkergatan 24', 'Bräcke', 'S-844 67', 'Sweden')]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * \n",
    "FROM Customers \n",
    "WHERE Country = \"Sweden\";"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dc126ee2-553e-48cc-bbf2-8d8a21ff8a4a",
   "metadata": {},
   "source": [
    "Filtering Customers not in Berlin"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "46ab995c-b4e0-4496-997f-66f87a496a3e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>CustomerID</th>\n",
       "        <th>CustomerName</th>\n",
       "        <th>ContactName</th>\n",
       "        <th>Address</th>\n",
       "        <th>City</th>\n",
       "        <th>PostalCode</th>\n",
       "        <th>Country</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Ana Trujillo Emparedados y helados</td>\n",
       "        <td>Ana Trujillo</td>\n",
       "        <td>Avda. de la Constitución 2222</td>\n",
       "        <td>México D.F.</td>\n",
       "        <td>5021</td>\n",
       "        <td>Mexico</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Antonio Moreno Taquería</td>\n",
       "        <td>Antonio Moreno</td>\n",
       "        <td>Mataderos 2312</td>\n",
       "        <td>México D.F.</td>\n",
       "        <td>5023</td>\n",
       "        <td>Mexico</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>Around the Horn</td>\n",
       "        <td>Thomas Hardy</td>\n",
       "        <td>120 Hanover Sq.</td>\n",
       "        <td>London</td>\n",
       "        <td>WA1 1DP</td>\n",
       "        <td>UK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>Berglunds snabbköp</td>\n",
       "        <td>Christina Berglund</td>\n",
       "        <td>Berguvsvägen 8</td>\n",
       "        <td>Luleå</td>\n",
       "        <td>S-958 22</td>\n",
       "        <td>Sweden</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6</td>\n",
       "        <td>Blauer See Delikatessen</td>\n",
       "        <td>Hanna Moos</td>\n",
       "        <td>Forsterstr. 57</td>\n",
       "        <td>Mannheim</td>\n",
       "        <td>68306</td>\n",
       "        <td>Germany</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7</td>\n",
       "        <td>Blondel père et fils</td>\n",
       "        <td>Frédérique Citeaux</td>\n",
       "        <td>24, place Kléber</td>\n",
       "        <td>Strasbourg</td>\n",
       "        <td>67000</td>\n",
       "        <td>France</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>Bólido Comidas preparadas</td>\n",
       "        <td>Martín Sommer</td>\n",
       "        <td>C/ Araquil, 67</td>\n",
       "        <td>Madrid</td>\n",
       "        <td>28023</td>\n",
       "        <td>Spain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9</td>\n",
       "        <td>Bon app&#x27;</td>\n",
       "        <td>Laurence Lebihans</td>\n",
       "        <td>12, rue des Bouchers</td>\n",
       "        <td>Marseille</td>\n",
       "        <td>13008</td>\n",
       "        <td>France</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>Bottom-Dollar Marketse</td>\n",
       "        <td>Elizabeth Lincoln</td>\n",
       "        <td>23 Tsawassen Blvd.</td>\n",
       "        <td>Tsawassen</td>\n",
       "        <td>T2F 8M4</td>\n",
       "        <td>Canada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11</td>\n",
       "        <td>B&#x27;s Beverages</td>\n",
       "        <td>Victoria Ashworth</td>\n",
       "        <td>Fauntleroy Circus</td>\n",
       "        <td>London</td>\n",
       "        <td>EC2 5NT</td>\n",
       "        <td>UK</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2, 'Ana Trujillo Emparedados y helados', 'Ana Trujillo', 'Avda. de la Constitución 2222', 'México D.F.', '5021', 'Mexico'),\n",
       " (3, 'Antonio Moreno Taquería', 'Antonio Moreno', 'Mataderos 2312', 'México D.F.', '5023', 'Mexico'),\n",
       " (4, 'Around the Horn', 'Thomas Hardy', '120 Hanover Sq.', 'London', 'WA1 1DP', 'UK'),\n",
       " (5, 'Berglunds snabbköp', 'Christina Berglund', 'Berguvsvägen 8', 'Luleå', 'S-958 22', 'Sweden'),\n",
       " (6, 'Blauer See Delikatessen', 'Hanna Moos', 'Forsterstr. 57', 'Mannheim', '68306', 'Germany'),\n",
       " (7, 'Blondel père et fils', 'Frédérique Citeaux', '24, place Kléber', 'Strasbourg', '67000', 'France'),\n",
       " (8, 'Bólido Comidas preparadas', 'Martín Sommer', 'C/ Araquil, 67', 'Madrid', '28023', 'Spain'),\n",
       " (9, \"Bon app'\", 'Laurence Lebihans', '12, rue des Bouchers', 'Marseille', '13008', 'France'),\n",
       " (10, 'Bottom-Dollar Marketse', 'Elizabeth Lincoln', '23 Tsawassen Blvd.', 'Tsawassen', 'T2F 8M4', 'Canada'),\n",
       " (11, \"B's Beverages\", 'Victoria Ashworth', 'Fauntleroy Circus', 'London', 'EC2 5NT', 'UK')]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * \n",
    "FROM Customers \n",
    "WHERE NOT City = \"Berlin\" \n",
    "LIMIT 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a7869995-4c57-41f8-a689-613ca6f7b647",
   "metadata": {},
   "source": [
    "Filtering Customers in UK & London"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "57067e35-919c-40d1-8bfa-8a2e2ffc907e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>CustomerID</th>\n",
       "        <th>CustomerName</th>\n",
       "        <th>ContactName</th>\n",
       "        <th>Address</th>\n",
       "        <th>City</th>\n",
       "        <th>PostalCode</th>\n",
       "        <th>Country</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>Around the Horn</td>\n",
       "        <td>Thomas Hardy</td>\n",
       "        <td>120 Hanover Sq.</td>\n",
       "        <td>London</td>\n",
       "        <td>WA1 1DP</td>\n",
       "        <td>UK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11</td>\n",
       "        <td>B&#x27;s Beverages</td>\n",
       "        <td>Victoria Ashworth</td>\n",
       "        <td>Fauntleroy Circus</td>\n",
       "        <td>London</td>\n",
       "        <td>EC2 5NT</td>\n",
       "        <td>UK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16</td>\n",
       "        <td>Consolidated Holdings</td>\n",
       "        <td>Elizabeth Brown</td>\n",
       "        <td>Berkeley Gardens 12 Brewery</td>\n",
       "        <td>London</td>\n",
       "        <td>WX1 6LT</td>\n",
       "        <td>UK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>19</td>\n",
       "        <td>Eastern Connection</td>\n",
       "        <td>Ann Devon</td>\n",
       "        <td>35 King George</td>\n",
       "        <td>London</td>\n",
       "        <td>WX3 6FW</td>\n",
       "        <td>UK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>53</td>\n",
       "        <td>North/South</td>\n",
       "        <td>Simon Crowther</td>\n",
       "        <td>South House 300 Queensbridge</td>\n",
       "        <td>London</td>\n",
       "        <td>SW7 1RZ</td>\n",
       "        <td>UK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>72</td>\n",
       "        <td>Seven Seas Imports</td>\n",
       "        <td>Hari Kumar</td>\n",
       "        <td>90 Wadhurst Rd.</td>\n",
       "        <td>London</td>\n",
       "        <td>OX15 4NB</td>\n",
       "        <td>UK</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(4, 'Around the Horn', 'Thomas Hardy', '120 Hanover Sq.', 'London', 'WA1 1DP', 'UK'),\n",
       " (11, \"B's Beverages\", 'Victoria Ashworth', 'Fauntleroy Circus', 'London', 'EC2 5NT', 'UK'),\n",
       " (16, 'Consolidated Holdings', 'Elizabeth Brown', 'Berkeley Gardens 12 Brewery', 'London', 'WX1 6LT', 'UK'),\n",
       " (19, 'Eastern Connection', 'Ann Devon', '35 King George', 'London', 'WX3 6FW', 'UK'),\n",
       " (53, 'North/South', 'Simon Crowther', 'South House 300 Queensbridge', 'London', 'SW7 1RZ', 'UK'),\n",
       " (72, 'Seven Seas Imports', 'Hari Kumar', '90 Wadhurst Rd.', 'London', 'OX15 4NB', 'UK')]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * \n",
    "FROM Customers \n",
    "WHERE Country = \"UK\" AND City = \"London\";"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f13ec4c8-1e22-47ee-b8a7-e1b6cf640259",
   "metadata": {},
   "source": [
    "Customers in Mexico or UK"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "d475c94e-d8b4-48b7-aa28-0c435c4c51fd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>CustomerID</th>\n",
       "        <th>CustomerName</th>\n",
       "        <th>ContactName</th>\n",
       "        <th>Address</th>\n",
       "        <th>City</th>\n",
       "        <th>PostalCode</th>\n",
       "        <th>Country</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Ana Trujillo Emparedados y helados</td>\n",
       "        <td>Ana Trujillo</td>\n",
       "        <td>Avda. de la Constitución 2222</td>\n",
       "        <td>México D.F.</td>\n",
       "        <td>5021</td>\n",
       "        <td>Mexico</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Antonio Moreno Taquería</td>\n",
       "        <td>Antonio Moreno</td>\n",
       "        <td>Mataderos 2312</td>\n",
       "        <td>México D.F.</td>\n",
       "        <td>5023</td>\n",
       "        <td>Mexico</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>Around the Horn</td>\n",
       "        <td>Thomas Hardy</td>\n",
       "        <td>120 Hanover Sq.</td>\n",
       "        <td>London</td>\n",
       "        <td>WA1 1DP</td>\n",
       "        <td>UK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11</td>\n",
       "        <td>B&#x27;s Beverages</td>\n",
       "        <td>Victoria Ashworth</td>\n",
       "        <td>Fauntleroy Circus</td>\n",
       "        <td>London</td>\n",
       "        <td>EC2 5NT</td>\n",
       "        <td>UK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>13</td>\n",
       "        <td>Centro comercial Moctezuma</td>\n",
       "        <td>Francisco Chang</td>\n",
       "        <td>Sierras de Granada 9993</td>\n",
       "        <td>México D.F.</td>\n",
       "        <td>5022</td>\n",
       "        <td>Mexico</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16</td>\n",
       "        <td>Consolidated Holdings</td>\n",
       "        <td>Elizabeth Brown</td>\n",
       "        <td>Berkeley Gardens 12 Brewery</td>\n",
       "        <td>London</td>\n",
       "        <td>WX1 6LT</td>\n",
       "        <td>UK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>19</td>\n",
       "        <td>Eastern Connection</td>\n",
       "        <td>Ann Devon</td>\n",
       "        <td>35 King George</td>\n",
       "        <td>London</td>\n",
       "        <td>WX3 6FW</td>\n",
       "        <td>UK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>38</td>\n",
       "        <td>Island Trading</td>\n",
       "        <td>Helen Bennett</td>\n",
       "        <td>Garden House Crowther Way</td>\n",
       "        <td>Cowes</td>\n",
       "        <td>PO31 7PJ</td>\n",
       "        <td>UK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>53</td>\n",
       "        <td>North/South</td>\n",
       "        <td>Simon Crowther</td>\n",
       "        <td>South House 300 Queensbridge</td>\n",
       "        <td>London</td>\n",
       "        <td>SW7 1RZ</td>\n",
       "        <td>UK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>58</td>\n",
       "        <td>Pericles Comidas clásicas</td>\n",
       "        <td>Guillermo Fernández</td>\n",
       "        <td>Calle Dr. Jorge Cash 321</td>\n",
       "        <td>México D.F.</td>\n",
       "        <td>5033</td>\n",
       "        <td>Mexico</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>72</td>\n",
       "        <td>Seven Seas Imports</td>\n",
       "        <td>Hari Kumar</td>\n",
       "        <td>90 Wadhurst Rd.</td>\n",
       "        <td>London</td>\n",
       "        <td>OX15 4NB</td>\n",
       "        <td>UK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>80</td>\n",
       "        <td>Tortuga Restaurante</td>\n",
       "        <td>Miguel Angel Paolino</td>\n",
       "        <td>Avda. Azteca 123</td>\n",
       "        <td>México D.F.</td>\n",
       "        <td>5033</td>\n",
       "        <td>Mexico</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2, 'Ana Trujillo Emparedados y helados', 'Ana Trujillo', 'Avda. de la Constitución 2222', 'México D.F.', '5021', 'Mexico'),\n",
       " (3, 'Antonio Moreno Taquería', 'Antonio Moreno', 'Mataderos 2312', 'México D.F.', '5023', 'Mexico'),\n",
       " (4, 'Around the Horn', 'Thomas Hardy', '120 Hanover Sq.', 'London', 'WA1 1DP', 'UK'),\n",
       " (11, \"B's Beverages\", 'Victoria Ashworth', 'Fauntleroy Circus', 'London', 'EC2 5NT', 'UK'),\n",
       " (13, 'Centro comercial Moctezuma', 'Francisco Chang', 'Sierras de Granada 9993', 'México D.F.', '5022', 'Mexico'),\n",
       " (16, 'Consolidated Holdings', 'Elizabeth Brown', 'Berkeley Gardens 12 Brewery', 'London', 'WX1 6LT', 'UK'),\n",
       " (19, 'Eastern Connection', 'Ann Devon', '35 King George', 'London', 'WX3 6FW', 'UK'),\n",
       " (38, 'Island Trading', 'Helen Bennett', 'Garden House Crowther Way', 'Cowes', 'PO31 7PJ', 'UK'),\n",
       " (53, 'North/South', 'Simon Crowther', 'South House 300 Queensbridge', 'London', 'SW7 1RZ', 'UK'),\n",
       " (58, 'Pericles Comidas clásicas', 'Guillermo Fernández', 'Calle Dr. Jorge Cash 321', 'México D.F.', '5033', 'Mexico'),\n",
       " (72, 'Seven Seas Imports', 'Hari Kumar', '90 Wadhurst Rd.', 'London', 'OX15 4NB', 'UK'),\n",
       " (80, 'Tortuga Restaurante', 'Miguel Angel Paolino', 'Avda. Azteca 123', 'México D.F.', '5033', 'Mexico')]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * \n",
    "FROM Customers \n",
    "WHERE Country = \"UK\" OR Country =\"Mexico\";"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3abdfc01-0608-4bfc-b0a5-16fed40fe570",
   "metadata": {},
   "source": [
    "Customers in USA and in the cities Eugene or Elgin"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "97bbd5d2-be27-4682-ae74-1338ca77a655",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>CustomerID</th>\n",
       "        <th>CustomerName</th>\n",
       "        <th>ContactName</th>\n",
       "        <th>Address</th>\n",
       "        <th>City</th>\n",
       "        <th>PostalCode</th>\n",
       "        <th>Country</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>32</td>\n",
       "        <td>Great Lakes Food Market</td>\n",
       "        <td>Howard Snyder</td>\n",
       "        <td>2732 Baker Blvd.</td>\n",
       "        <td>Eugene</td>\n",
       "        <td>97403</td>\n",
       "        <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>36</td>\n",
       "        <td>Hungry Coyote Import Store</td>\n",
       "        <td>Yoshi Latimer</td>\n",
       "        <td>City Center Plaza 516 Main St.</td>\n",
       "        <td>Elgin</td>\n",
       "        <td>97827</td>\n",
       "        <td>USA</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(32, 'Great Lakes Food Market', 'Howard Snyder', '2732 Baker Blvd.', 'Eugene', '97403', 'USA'),\n",
       " (36, 'Hungry Coyote Import Store', 'Yoshi Latimer', 'City Center Plaza 516 Main St.', 'Elgin', '97827', 'USA')]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * \n",
    "FROM Customers \n",
    "WHERE Country = \"USA\" AND (City=\"Eugene\" OR City= \"Elgin\");"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "95ef91c3-3b09-4e95-93c1-90dd90a47d4b",
   "metadata": {},
   "source": [
    "In & not In"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "5605b9fe-46de-4336-ac24-c5b01433e103",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>CustomerID</th>\n",
       "        <th>CustomerName</th>\n",
       "        <th>ContactName</th>\n",
       "        <th>Address</th>\n",
       "        <th>City</th>\n",
       "        <th>PostalCode</th>\n",
       "        <th>Country</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Ana Trujillo Emparedados y helados</td>\n",
       "        <td>Ana Trujillo</td>\n",
       "        <td>Avda. de la Constitución 2222</td>\n",
       "        <td>México D.F.</td>\n",
       "        <td>5021</td>\n",
       "        <td>Mexico</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>Around the Horn</td>\n",
       "        <td>Thomas Hardy</td>\n",
       "        <td>120 Hanover Sq.</td>\n",
       "        <td>London</td>\n",
       "        <td>WA1 1DP</td>\n",
       "        <td>UK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6</td>\n",
       "        <td>Blauer See Delikatessen</td>\n",
       "        <td>Hanna Moos</td>\n",
       "        <td>Forsterstr. 57</td>\n",
       "        <td>Mannheim</td>\n",
       "        <td>68306</td>\n",
       "        <td>Germany</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>Bólido Comidas preparadas</td>\n",
       "        <td>Martín Sommer</td>\n",
       "        <td>C/ Araquil, 67</td>\n",
       "        <td>Madrid</td>\n",
       "        <td>28023</td>\n",
       "        <td>Spain</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2, 'Ana Trujillo Emparedados y helados', 'Ana Trujillo', 'Avda. de la Constitución 2222', 'México D.F.', '5021', 'Mexico'),\n",
       " (4, 'Around the Horn', 'Thomas Hardy', '120 Hanover Sq.', 'London', 'WA1 1DP', 'UK'),\n",
       " (6, 'Blauer See Delikatessen', 'Hanna Moos', 'Forsterstr. 57', 'Mannheim', '68306', 'Germany'),\n",
       " (8, 'Bólido Comidas preparadas', 'Martín Sommer', 'C/ Araquil, 67', 'Madrid', '28023', 'Spain')]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * \n",
    "FROM Customers \n",
    "WHERE CustomerID IN (2,4,6,8);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "faf72e80-2a19-4c84-9891-511e6c9d5b54",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>CustomerID</th>\n",
       "        <th>CustomerName</th>\n",
       "        <th>ContactName</th>\n",
       "        <th>Address</th>\n",
       "        <th>City</th>\n",
       "        <th>PostalCode</th>\n",
       "        <th>Country</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>43</td>\n",
       "        <td>Lazy K Kountry Store</td>\n",
       "        <td>John Steel</td>\n",
       "        <td>12 Orchestra Terrace</td>\n",
       "        <td>Walla Walla</td>\n",
       "        <td>99362</td>\n",
       "        <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>45</td>\n",
       "        <td>Let&#x27;s Stop N Shop</td>\n",
       "        <td>Jaime Yorres</td>\n",
       "        <td>87 Polk St. Suite 5</td>\n",
       "        <td>San Francisco</td>\n",
       "        <td>94117</td>\n",
       "        <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>48</td>\n",
       "        <td>Lonesome Pine Restaurant</td>\n",
       "        <td>Fran Wilson</td>\n",
       "        <td>89 Chiaroscuro Rd.</td>\n",
       "        <td>Portland</td>\n",
       "        <td>97219</td>\n",
       "        <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>55</td>\n",
       "        <td>Old World Delicatessen</td>\n",
       "        <td>Rene Phillips</td>\n",
       "        <td>2743 Bering St.</td>\n",
       "        <td>Anchorage</td>\n",
       "        <td>99508</td>\n",
       "        <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>65</td>\n",
       "        <td>Rattlesnake Canyon Grocery</td>\n",
       "        <td>Paula Wilson</td>\n",
       "        <td>2817 Milton Dr.</td>\n",
       "        <td>Albuquerque</td>\n",
       "        <td>87110</td>\n",
       "        <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71</td>\n",
       "        <td>Save-a-lot Markets</td>\n",
       "        <td>Jose Pavarotti</td>\n",
       "        <td>187 Suffolk Ln.</td>\n",
       "        <td>Boise</td>\n",
       "        <td>83720</td>\n",
       "        <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>75</td>\n",
       "        <td>Split Rail Beer &amp; Ale</td>\n",
       "        <td>Art Braunschweiger</td>\n",
       "        <td>P.O. Box 555</td>\n",
       "        <td>Lander</td>\n",
       "        <td>82520</td>\n",
       "        <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>77</td>\n",
       "        <td>The Big Cheese</td>\n",
       "        <td>Liz Nixon</td>\n",
       "        <td>89 Jefferson Way Suite 2</td>\n",
       "        <td>Portland</td>\n",
       "        <td>97201</td>\n",
       "        <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>78</td>\n",
       "        <td>The Cracker Box</td>\n",
       "        <td>Liu Wong</td>\n",
       "        <td>55 Grizzly Peak Rd.</td>\n",
       "        <td>Butte</td>\n",
       "        <td>59801</td>\n",
       "        <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>82</td>\n",
       "        <td>Trail&#x27;s Head Gourmet Provisioners</td>\n",
       "        <td>Helvetius Nagy</td>\n",
       "        <td>722 DaVinci Blvd.</td>\n",
       "        <td>Kirkland</td>\n",
       "        <td>98034</td>\n",
       "        <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>89</td>\n",
       "        <td>White Clover Markets</td>\n",
       "        <td>Karl Jablonski</td>\n",
       "        <td>305 - 14th Ave. S. Suite 3B</td>\n",
       "        <td>Seattle</td>\n",
       "        <td>98128</td>\n",
       "        <td>USA</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(43, 'Lazy K Kountry Store', 'John Steel', '12 Orchestra Terrace', 'Walla Walla', '99362', 'USA'),\n",
       " (45, \"Let's Stop N Shop\", 'Jaime Yorres', '87 Polk St. Suite 5', 'San Francisco', '94117', 'USA'),\n",
       " (48, 'Lonesome Pine Restaurant', 'Fran Wilson', '89 Chiaroscuro Rd.', 'Portland', '97219', 'USA'),\n",
       " (55, 'Old World Delicatessen', 'Rene Phillips', '2743 Bering St.', 'Anchorage', '99508', 'USA'),\n",
       " (65, 'Rattlesnake Canyon Grocery', 'Paula Wilson', '2817 Milton Dr.', 'Albuquerque', '87110', 'USA'),\n",
       " (71, 'Save-a-lot Markets', 'Jose Pavarotti', '187 Suffolk Ln.', 'Boise', '83720', 'USA'),\n",
       " (75, 'Split Rail Beer & Ale', 'Art Braunschweiger', 'P.O. Box 555', 'Lander', '82520', 'USA'),\n",
       " (77, 'The Big Cheese', 'Liz Nixon', '89 Jefferson Way Suite 2', 'Portland', '97201', 'USA'),\n",
       " (78, 'The Cracker Box', 'Liu Wong', '55 Grizzly Peak Rd.', 'Butte', '59801', 'USA'),\n",
       " (82, \"Trail's Head Gourmet Provisioners\", 'Helvetius Nagy', '722 DaVinci Blvd.', 'Kirkland', '98034', 'USA'),\n",
       " (89, 'White Clover Markets', 'Karl Jablonski', '305 - 14th Ave. S. Suite 3B', 'Seattle', '98128', 'USA')]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * \n",
    "FROM Customers \n",
    "WHERE Country = \"USA\" AND City NOT IN (\"Eugene\", \"Elgin\");"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "391d2276-d609-4820-97bc-4618dc4f1d83",
   "metadata": {},
   "source": [
    "**Selecting with Like**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a2128cac-049f-4260-a5e3-89fa99555688",
   "metadata": {},
   "source": [
    "* 'a%' : values start with “a”\n",
    "* '%a' : values end with “a”\n",
    "* %or%' : values have “or” in it\n",
    "* '_r%' : values have “r” in second letter\n",
    "* 'a%%' : values start with “a” and have at least three letters\n",
    "* 'a%o' : values start with “a” and end with “o”"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "f565a756-e12c-47eb-8c29-0a183b5f6afc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>CustomerID</th>\n",
       "        <th>CustomerName</th>\n",
       "        <th>ContactName</th>\n",
       "        <th>Address</th>\n",
       "        <th>City</th>\n",
       "        <th>PostalCode</th>\n",
       "        <th>Country</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Antonio Moreno Taquería</td>\n",
       "        <td>Antonio Moreno</td>\n",
       "        <td>Mataderos 2312</td>\n",
       "        <td>México D.F.</td>\n",
       "        <td>5023</td>\n",
       "        <td>Mexico</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>Around the Horn</td>\n",
       "        <td>Thomas Hardy</td>\n",
       "        <td>120 Hanover Sq.</td>\n",
       "        <td>London</td>\n",
       "        <td>WA1 1DP</td>\n",
       "        <td>UK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>36</td>\n",
       "        <td>Hungry Coyote Import Store</td>\n",
       "        <td>Yoshi Latimer</td>\n",
       "        <td>City Center Plaza 516 Main St.</td>\n",
       "        <td>Elgin</td>\n",
       "        <td>97827</td>\n",
       "        <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>40</td>\n",
       "        <td>La corne d&#x27;abondance</td>\n",
       "        <td>Daniel Tonini</td>\n",
       "        <td>67, avenue de l&#x27;Europe</td>\n",
       "        <td>Versailles</td>\n",
       "        <td>78000</td>\n",
       "        <td>France</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>43</td>\n",
       "        <td>Lazy K Kountry Store</td>\n",
       "        <td>John Steel</td>\n",
       "        <td>12 Orchestra Terrace</td>\n",
       "        <td>Walla Walla</td>\n",
       "        <td>99362</td>\n",
       "        <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>52</td>\n",
       "        <td>Morgenstern Gesundkost</td>\n",
       "        <td>Alexander Feuer</td>\n",
       "        <td>Heerstr. 22</td>\n",
       "        <td>Leipzig</td>\n",
       "        <td>4179</td>\n",
       "        <td>Germany</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>53</td>\n",
       "        <td>North/South</td>\n",
       "        <td>Simon Crowther</td>\n",
       "        <td>South House 300 Queensbridge</td>\n",
       "        <td>London</td>\n",
       "        <td>SW7 1RZ</td>\n",
       "        <td>UK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>55</td>\n",
       "        <td>Old World Delicatessen</td>\n",
       "        <td>Rene Phillips</td>\n",
       "        <td>2743 Bering St.</td>\n",
       "        <td>Anchorage</td>\n",
       "        <td>99508</td>\n",
       "        <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>72</td>\n",
       "        <td>Seven Seas Imports</td>\n",
       "        <td>Hari Kumar</td>\n",
       "        <td>90 Wadhurst Rd.</td>\n",
       "        <td>London</td>\n",
       "        <td>OX15 4NB</td>\n",
       "        <td>UK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>80</td>\n",
       "        <td>Tortuga Restaurante</td>\n",
       "        <td>Miguel Angel Paolino</td>\n",
       "        <td>Avda. Azteca 123</td>\n",
       "        <td>México D.F.</td>\n",
       "        <td>5033</td>\n",
       "        <td>Mexico</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(3, 'Antonio Moreno Taquería', 'Antonio Moreno', 'Mataderos 2312', 'México D.F.', '5023', 'Mexico'),\n",
       " (4, 'Around the Horn', 'Thomas Hardy', '120 Hanover Sq.', 'London', 'WA1 1DP', 'UK'),\n",
       " (36, 'Hungry Coyote Import Store', 'Yoshi Latimer', 'City Center Plaza 516 Main St.', 'Elgin', '97827', 'USA'),\n",
       " (40, \"La corne d'abondance\", 'Daniel Tonini', \"67, avenue de l'Europe\", 'Versailles', '78000', 'France'),\n",
       " (43, 'Lazy K Kountry Store', 'John Steel', '12 Orchestra Terrace', 'Walla Walla', '99362', 'USA'),\n",
       " (52, 'Morgenstern Gesundkost', 'Alexander Feuer', 'Heerstr. 22', 'Leipzig', '4179', 'Germany'),\n",
       " (53, 'North/South', 'Simon Crowther', 'South House 300 Queensbridge', 'London', 'SW7 1RZ', 'UK'),\n",
       " (55, 'Old World Delicatessen', 'Rene Phillips', '2743 Bering St.', 'Anchorage', '99508', 'USA'),\n",
       " (72, 'Seven Seas Imports', 'Hari Kumar', '90 Wadhurst Rd.', 'London', 'OX15 4NB', 'UK'),\n",
       " (80, 'Tortuga Restaurante', 'Miguel Angel Paolino', 'Avda. Azteca 123', 'México D.F.', '5033', 'Mexico')]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * \n",
    "FROM Customers \n",
    "WHERE CustomerName LIKE \"%or%\" \n",
    "LIMIT 10;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "e9e43805-8ca6-49b2-b86e-021c722971ec",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>CustomerID</th>\n",
       "        <th>CustomerName</th>\n",
       "        <th>ContactName</th>\n",
       "        <th>Address</th>\n",
       "        <th>City</th>\n",
       "        <th>PostalCode</th>\n",
       "        <th>Country</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Alfreds Futterkiste</td>\n",
       "        <td>Maria Anders</td>\n",
       "        <td>Obere Str. 57</td>\n",
       "        <td>Berlin</td>\n",
       "        <td>12209</td>\n",
       "        <td>Germany</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Ana Trujillo Emparedados y helados</td>\n",
       "        <td>Ana Trujillo</td>\n",
       "        <td>Avda. de la Constitución 2222</td>\n",
       "        <td>México D.F.</td>\n",
       "        <td>5021</td>\n",
       "        <td>Mexico</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Antonio Moreno Taquería</td>\n",
       "        <td>Antonio Moreno</td>\n",
       "        <td>Mataderos 2312</td>\n",
       "        <td>México D.F.</td>\n",
       "        <td>5023</td>\n",
       "        <td>Mexico</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>Around the Horn</td>\n",
       "        <td>Thomas Hardy</td>\n",
       "        <td>120 Hanover Sq.</td>\n",
       "        <td>London</td>\n",
       "        <td>WA1 1DP</td>\n",
       "        <td>UK</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'Alfreds Futterkiste', 'Maria Anders', 'Obere Str. 57', 'Berlin', '12209', 'Germany'),\n",
       " (2, 'Ana Trujillo Emparedados y helados', 'Ana Trujillo', 'Avda. de la Constitución 2222', 'México D.F.', '5021', 'Mexico'),\n",
       " (3, 'Antonio Moreno Taquería', 'Antonio Moreno', 'Mataderos 2312', 'México D.F.', '5023', 'Mexico'),\n",
       " (4, 'Around the Horn', 'Thomas Hardy', '120 Hanover Sq.', 'London', 'WA1 1DP', 'UK')]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * \n",
    "FROM Customers \n",
    "WHERE CustomerName LIKE \"a%\" \n",
    "LIMIT 10;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "3a6f053e-632e-4f08-9e5b-c8f3dce9e2a3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>CustomerID</th>\n",
       "        <th>CustomerName</th>\n",
       "        <th>ContactName</th>\n",
       "        <th>Address</th>\n",
       "        <th>City</th>\n",
       "        <th>PostalCode</th>\n",
       "        <th>Country</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>61</td>\n",
       "        <td>Que Delícia</td>\n",
       "        <td>Bernardo Batista</td>\n",
       "        <td>Rua da Panificadora, 12</td>\n",
       "        <td>Rio de Janeiro</td>\n",
       "        <td>02389-673</td>\n",
       "        <td>Brazil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>62</td>\n",
       "        <td>Queen Cozinha</td>\n",
       "        <td>Lúcia Carvalho</td>\n",
       "        <td>Alameda dos Canàrios, 891</td>\n",
       "        <td>São Paulo</td>\n",
       "        <td>05487-020</td>\n",
       "        <td>Brazil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>88</td>\n",
       "        <td>Wellington Importadora</td>\n",
       "        <td>Paula Parente</td>\n",
       "        <td>Rua do Mercado, 12</td>\n",
       "        <td>Resende</td>\n",
       "        <td>08737-363</td>\n",
       "        <td>Brazil</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(61, 'Que Delícia', 'Bernardo Batista', 'Rua da Panificadora, 12', 'Rio de Janeiro', '02389-673', 'Brazil'),\n",
       " (62, 'Queen Cozinha', 'Lúcia Carvalho', 'Alameda dos Canàrios, 891', 'São Paulo', '05487-020', 'Brazil'),\n",
       " (88, 'Wellington Importadora', 'Paula Parente', 'Rua do Mercado, 12', 'Resende', '08737-363', 'Brazil')]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * \n",
    "FROM Customers \n",
    "WHERE CustomerName LIKE \"%a\" AND Country LIKE \"_r%\" \n",
    "LIMIT 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1a39e237-edcd-4da2-aed1-25d5e5b22c66",
   "metadata": {},
   "source": [
    "**Between**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "983a2445-23e6-486a-8a66-bb8a1bec5040",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>ProductID</th>\n",
       "        <th>ProductName</th>\n",
       "        <th>SupplierID</th>\n",
       "        <th>CategoryID</th>\n",
       "        <th>Unit</th>\n",
       "        <th>Price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Chais</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>10 boxes x 20 bags</td>\n",
       "        <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Chang</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>24 - 12 oz bottles</td>\n",
       "        <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Aniseed Syrup</td>\n",
       "        <td>1</td>\n",
       "        <td>2</td>\n",
       "        <td>12 - 550 ml bottles</td>\n",
       "        <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16</td>\n",
       "        <td>Pavlova</td>\n",
       "        <td>7</td>\n",
       "        <td>3</td>\n",
       "        <td>32 - 500 g boxes</td>\n",
       "        <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>21</td>\n",
       "        <td>Sir Rodney&#x27;s Scones</td>\n",
       "        <td>8</td>\n",
       "        <td>3</td>\n",
       "        <td>24 pkgs. x 4 pieces</td>\n",
       "        <td>10</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'Chais', 1, 1, '10 boxes x 20 bags', 18),\n",
       " (2, 'Chang', 1, 1, '24 - 12 oz bottles', 19),\n",
       " (3, 'Aniseed Syrup', 1, 2, '12 - 550 ml bottles', 10),\n",
       " (16, 'Pavlova', 7, 3, '32 - 500 g boxes', 17),\n",
       " (21, \"Sir Rodney's Scones\", 8, 3, '24 pkgs. x 4 pieces', 10)]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * \n",
    "FROM Products \n",
    "WHERE Price BETWEEN 10 AND 20 \n",
    "LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "6899a7bf-961e-4fa5-9826-33323be35644",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>ProductID</th>\n",
       "        <th>ProductName</th>\n",
       "        <th>SupplierID</th>\n",
       "        <th>CategoryID</th>\n",
       "        <th>Unit</th>\n",
       "        <th>Price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>36</td>\n",
       "        <td>Inlagd Sill</td>\n",
       "        <td>17</td>\n",
       "        <td>8</td>\n",
       "        <td>24 - 250 g jars</td>\n",
       "        <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>42</td>\n",
       "        <td>Singaporean Hokkien Fried Mee</td>\n",
       "        <td>20</td>\n",
       "        <td>5</td>\n",
       "        <td>32 - 1 kg pkgs.</td>\n",
       "        <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>46</td>\n",
       "        <td>Spegesild</td>\n",
       "        <td>21</td>\n",
       "        <td>8</td>\n",
       "        <td>4 - 450 g glasses</td>\n",
       "        <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>54</td>\n",
       "        <td>Tourtière</td>\n",
       "        <td>25</td>\n",
       "        <td>6</td>\n",
       "        <td>16 pies</td>\n",
       "        <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>58</td>\n",
       "        <td>Escargots de Bourgogne</td>\n",
       "        <td>27</td>\n",
       "        <td>8</td>\n",
       "        <td>24 pieces</td>\n",
       "        <td>13</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(36, 'Inlagd Sill', 17, 8, '24 - 250 g jars', 19),\n",
       " (42, 'Singaporean Hokkien Fried Mee', 20, 5, '32 - 1 kg pkgs.', 14),\n",
       " (46, 'Spegesild', 21, 8, '4 - 450 g glasses', 12),\n",
       " (54, 'Tourtière', 25, 6, '16 pies', 16),\n",
       " (58, 'Escargots de Bourgogne', 27, 8, '24 pieces', 13)]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * \n",
    "FROM Products \n",
    "WHERE (Price BETWEEN 10 AND 20) AND NOT CategoryID IN (1,2,3) \n",
    "LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "b8f9596b-3ea2-450a-86d5-2860160557b7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>ProductName</th>\n",
       "        <th>Price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Chef Anton&#x27;s Cajun Seasoning</td>\n",
       "        <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Chef Anton&#x27;s Gumbo Mix</td>\n",
       "        <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Grandma&#x27;s Boysenberry Spread</td>\n",
       "        <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Uncle Bob&#x27;s Organic Dried Pears</td>\n",
       "        <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Northwoods Cranberry Sauce</td>\n",
       "        <td>40</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(\"Chef Anton's Cajun Seasoning\", 22),\n",
       " (\"Chef Anton's Gumbo Mix\", 21),\n",
       " (\"Grandma's Boysenberry Spread\", 25),\n",
       " (\"Uncle Bob's Organic Dried Pears\", 30),\n",
       " ('Northwoods Cranberry Sauce', 40)]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT ProductName, Price\n",
    "FROM Products \n",
    "WHERE Price NOT BETWEEN 10 AND 20 \n",
    "LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "de58a949-4543-4754-b277-d3c789108be0",
   "metadata": {},
   "source": [
    "#### Aggregation: Basic Statistics"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "53c14838-86d9-4146-8c81-0d038a951984",
   "metadata": {},
   "source": [
    "What is the product with minimum price?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "77224d30-5fe0-4cc3-9ac6-3601c1621433",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Minimum_Price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(6,)]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT MIN(Price) AS Minimum_Price\n",
    "FROM Products;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7420b4ca-9a27-4463-969a-758c87d1f3f9",
   "metadata": {},
   "source": [
    "What is the product with maximum price?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "fd7c2a72-0812-4d36-906b-2f9a0e3b0c44",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>maximum_price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>435</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(435,)]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT MAX(Price) AS maximum_price\n",
    "FROM Products;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f1d70be4-ecd5-4677-b85b-e10da8d1aab8",
   "metadata": {},
   "source": [
    "What is the average of prices?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "23843b71-220f-421c-a03d-ab5c5f9f29bb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>average_price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>39.15584415584416</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(39.15584415584416,)]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT AVG(Price) AS average_price\n",
    "FROM Products;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "602ad687-ab33-47bd-b962-bf054ca02fb2",
   "metadata": {},
   "source": [
    "Total price?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "858aa9cd-f820-4215-a9d2-4810ba82d994",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>total_price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3015</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(3015,)]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT SUM(Price) AS total_price\n",
    "FROM PRODUCTS;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e93173fe-67f3-4238-8345-9311fe765bce",
   "metadata": {},
   "source": [
    "Descriptive Statics on a table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "6bfc8710-9859-44f8-a11c-c8ab4abc1d29",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Minimum</th>\n",
       "        <th>Maximum</th>\n",
       "        <th>Average</th>\n",
       "        <th>Total</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6</td>\n",
       "        <td>435</td>\n",
       "        <td>39.15584415584416</td>\n",
       "        <td>3015</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(6, 435, 39.15584415584416, 3015)]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT MIN(Price) as Minimum , MAX(Price) as Maximum, AVG(Price) as Average, SUM(Price) as Total \n",
    "FROM Products;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b242cf87-d757-410f-b698-63241c486a9a",
   "metadata": {},
   "source": [
    "**Group By & Aggregation**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a1d35856-1dcd-47b8-93e8-dd21f451e657",
   "metadata": {},
   "source": [
    "sorting countries by number of customers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "30f7e4b5-7250-4ccb-b211-33a0b7d6b55c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Total_Customers</th>\n",
       "        <th>Country</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>13</td>\n",
       "        <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11</td>\n",
       "        <td>Germany</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11</td>\n",
       "        <td>France</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9</td>\n",
       "        <td>Brazil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7</td>\n",
       "        <td>UK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>Spain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>Mexico</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>Venezuela</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Italy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Canada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Argentina</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Switzerland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Sweden</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Portugal</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Finland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Denmark</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Belgium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Austria</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Poland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Norway</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Ireland</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(13, 'USA'),\n",
       " (11, 'Germany'),\n",
       " (11, 'France'),\n",
       " (9, 'Brazil'),\n",
       " (7, 'UK'),\n",
       " (5, 'Spain'),\n",
       " (5, 'Mexico'),\n",
       " (4, 'Venezuela'),\n",
       " (3, 'Italy'),\n",
       " (3, 'Canada'),\n",
       " (3, 'Argentina'),\n",
       " (2, 'Switzerland'),\n",
       " (2, 'Sweden'),\n",
       " (2, 'Portugal'),\n",
       " (2, 'Finland'),\n",
       " (2, 'Denmark'),\n",
       " (2, 'Belgium'),\n",
       " (2, 'Austria'),\n",
       " (1, 'Poland'),\n",
       " (1, 'Norway'),\n",
       " (1, 'Ireland')]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT COUNT(CustomerID) as Total_Customers , Country \n",
    "FROM Customers \n",
    "GROUP BY Country \n",
    "ORDER BY Total_Customers DESC;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1d7b6bc8-9cd1-4de1-b9b6-67331ad5ac06",
   "metadata": {},
   "source": [
    "average prices of products by category"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "1cc39edc-df3b-4a27-8328-cf883e802966",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Average_Price</th>\n",
       "        <th>CategoryID</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>42.75</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>28.25</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>33.76923076923077</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>74.9</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>23.571428571428573</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>55.166666666666664</td>\n",
       "        <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>32.2</td>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>26.5</td>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(42.75, 1),\n",
       " (28.25, 2),\n",
       " (33.76923076923077, 3),\n",
       " (74.9, 4),\n",
       " (23.571428571428573, 5),\n",
       " (55.166666666666664, 6),\n",
       " (32.2, 7),\n",
       " (26.5, 8)]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT AVG(Price) AS Average_Price, CategoryID \n",
    "FROM Products \n",
    "GROUP BY CategoryID;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "48b7018d-1824-4823-af36-475698fd65c6",
   "metadata": {},
   "source": [
    "#### Joins"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fadcffbd-4c16-41b8-bb19-ccd843ff6204",
   "metadata": {},
   "source": [
    "table 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "f5e7a05e-10ae-44b3-9689-04dd0b659bad",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>CategoryID</th>\n",
       "        <th>CategoryName</th>\n",
       "        <th>Description</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Beverages</td>\n",
       "        <td>Soft drinks, coffees, teas, beers, and ales</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Condiments</td>\n",
       "        <td>Sweet and savory sauces, relishes, spreads, and seasonings</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'Beverages', 'Soft drinks, coffees, teas, beers, and ales'),\n",
       " (2, 'Condiments', 'Sweet and savory sauces, relishes, spreads, and seasonings')]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * \n",
    "FROM Categories \n",
    "LIMIT 2;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ae8128e6-83af-4df1-8721-fac2a33cc1ef",
   "metadata": {},
   "source": [
    "table 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "9a7b8b21-43b6-452f-8274-4f3029e2c5ac",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>ProductID</th>\n",
       "        <th>ProductName</th>\n",
       "        <th>SupplierID</th>\n",
       "        <th>CategoryID</th>\n",
       "        <th>Unit</th>\n",
       "        <th>Price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Chais</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>10 boxes x 20 bags</td>\n",
       "        <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Chang</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>24 - 12 oz bottles</td>\n",
       "        <td>19</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'Chais', 1, 1, '10 boxes x 20 bags', 18),\n",
       " (2, 'Chang', 1, 1, '24 - 12 oz bottles', 19)]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * \n",
    "FROM Products \n",
    "LIMIT 2;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "83e31778-3843-41cf-9e1c-c4c9d1540d79",
   "metadata": {},
   "source": [
    "**Left Join**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "21ae17b8-f52c-43ee-a9c0-454b0a2fc277",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>ProductID</th>\n",
       "        <th>ProductName</th>\n",
       "        <th>SupplierID</th>\n",
       "        <th>CategoryID</th>\n",
       "        <th>Unit</th>\n",
       "        <th>Price</th>\n",
       "        <th>CategoryID_1</th>\n",
       "        <th>CategoryName</th>\n",
       "        <th>Description</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Chais</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>10 boxes x 20 bags</td>\n",
       "        <td>18</td>\n",
       "        <td>1</td>\n",
       "        <td>Beverages</td>\n",
       "        <td>Soft drinks, coffees, teas, beers, and ales</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Chang</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>24 - 12 oz bottles</td>\n",
       "        <td>19</td>\n",
       "        <td>1</td>\n",
       "        <td>Beverages</td>\n",
       "        <td>Soft drinks, coffees, teas, beers, and ales</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Aniseed Syrup</td>\n",
       "        <td>1</td>\n",
       "        <td>2</td>\n",
       "        <td>12 - 550 ml bottles</td>\n",
       "        <td>10</td>\n",
       "        <td>2</td>\n",
       "        <td>Condiments</td>\n",
       "        <td>Sweet and savory sauces, relishes, spreads, and seasonings</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>Chef Anton&#x27;s Cajun Seasoning</td>\n",
       "        <td>2</td>\n",
       "        <td>2</td>\n",
       "        <td>48 - 6 oz jars</td>\n",
       "        <td>22</td>\n",
       "        <td>2</td>\n",
       "        <td>Condiments</td>\n",
       "        <td>Sweet and savory sauces, relishes, spreads, and seasonings</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>Chef Anton&#x27;s Gumbo Mix</td>\n",
       "        <td>2</td>\n",
       "        <td>2</td>\n",
       "        <td>36 boxes</td>\n",
       "        <td>21</td>\n",
       "        <td>2</td>\n",
       "        <td>Condiments</td>\n",
       "        <td>Sweet and savory sauces, relishes, spreads, and seasonings</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'Chais', 1, 1, '10 boxes x 20 bags', 18, 1, 'Beverages', 'Soft drinks, coffees, teas, beers, and ales'),\n",
       " (2, 'Chang', 1, 1, '24 - 12 oz bottles', 19, 1, 'Beverages', 'Soft drinks, coffees, teas, beers, and ales'),\n",
       " (3, 'Aniseed Syrup', 1, 2, '12 - 550 ml bottles', 10, 2, 'Condiments', 'Sweet and savory sauces, relishes, spreads, and seasonings'),\n",
       " (4, \"Chef Anton's Cajun Seasoning\", 2, 2, '48 - 6 oz jars', 22, 2, 'Condiments', 'Sweet and savory sauces, relishes, spreads, and seasonings'),\n",
       " (5, \"Chef Anton's Gumbo Mix\", 2, 2, '36 boxes', 21, 2, 'Condiments', 'Sweet and savory sauces, relishes, spreads, and seasonings')]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * \n",
    "FROM Products \n",
    "LEFT OUTER JOIN Categories ON Products.CategoryID = Categories.CategoryID \n",
    "LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "772da2ee-2a85-4bc1-b14e-093034878fe3",
   "metadata": {},
   "source": [
    "**Inner Join**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ad996b4f-bbea-4b44-b07b-8d3fc3a5a208",
   "metadata": {},
   "source": [
    "table 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "7fd03bbb-3655-480c-80dd-dee10b0aec1c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>OrderID</th>\n",
       "        <th>CustomerID</th>\n",
       "        <th>EmployeeID</th>\n",
       "        <th>OrderDate</th>\n",
       "        <th>ShipperID</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10248</td>\n",
       "        <td>90</td>\n",
       "        <td>5</td>\n",
       "        <td>4.07.1996</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10249</td>\n",
       "        <td>81</td>\n",
       "        <td>6</td>\n",
       "        <td>5.07.1996</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(10248, 90, 5, '4.07.1996', 3), (10249, 81, 6, '5.07.1996', 1)]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * \n",
    "FROM Orders \n",
    "LIMIT 2;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "170f87a1-e9ca-4578-9f10-6ac8d1e4f888",
   "metadata": {},
   "source": [
    "table 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "c0a69506-40c9-4677-8a4d-44da4e2cf114",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>CustomerID</th>\n",
       "        <th>CustomerName</th>\n",
       "        <th>ContactName</th>\n",
       "        <th>Address</th>\n",
       "        <th>City</th>\n",
       "        <th>PostalCode</th>\n",
       "        <th>Country</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Alfreds Futterkiste</td>\n",
       "        <td>Maria Anders</td>\n",
       "        <td>Obere Str. 57</td>\n",
       "        <td>Berlin</td>\n",
       "        <td>12209</td>\n",
       "        <td>Germany</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Ana Trujillo Emparedados y helados</td>\n",
       "        <td>Ana Trujillo</td>\n",
       "        <td>Avda. de la Constitución 2222</td>\n",
       "        <td>México D.F.</td>\n",
       "        <td>5021</td>\n",
       "        <td>Mexico</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'Alfreds Futterkiste', 'Maria Anders', 'Obere Str. 57', 'Berlin', '12209', 'Germany'),\n",
       " (2, 'Ana Trujillo Emparedados y helados', 'Ana Trujillo', 'Avda. de la Constitución 2222', 'México D.F.', '5021', 'Mexico')]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * \n",
    "FROM Customers \n",
    "LIMIT 2;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "376beb4f-9973-4ec9-b3df-733cfdb6f179",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>OrderID</th>\n",
       "        <th>CustomerID</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10248</td>\n",
       "        <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10249</td>\n",
       "        <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10250</td>\n",
       "        <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10251</td>\n",
       "        <td>84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10252</td>\n",
       "        <td>76</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(10248, 90), (10249, 81), (10250, 34), (10251, 84), (10252, 76)]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT Orders.OrderID , Customers.CustomerID \n",
    "FROM Orders \n",
    "INNER JOIN Customers ON Orders.CustomerID  = Customers.CustomerID \n",
    "LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2da768eb-8063-44c7-85ad-3ffcdb91f2bb",
   "metadata": {},
   "source": [
    "#### SQLite & Pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "f6cb0943-012d-42e5-a35f-d03589701288",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "815187df-d301-4a39-ac48-98d18a4dcb47",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    }
   ],
   "source": [
    "df = %sql SELECT COUNT(CustomerID) as Total_Customers , Country FROM Customers GROUP BY Country ORDER BY Total_Customers DESC;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "7c4a1f86-ba89-4d5b-a614-10ab2d4b255b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "sql.run.ResultSet"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "a038ec52-c4b2-462f-90e5-928a2eff6afa",
   "metadata": {},
   "outputs": [],
   "source": [
    "dff = pd.DataFrame(df, columns = [\"Total_Customers\",\"Country\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "ea7ee7b4-f5e2-40d2-aedf-9d875cea2966",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.frame.DataFrame"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(dff)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "9e338ccb-fa79-4132-a627-856a2fd82c68",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAawAAAD4CAYAAACwoNL5AAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjQuMywgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy/MnkTPAAAACXBIWXMAAAsTAAALEwEAmpwYAAAyUUlEQVR4nO3debzVVd328c8lIIggJJqhZKBiKkqg4HwrpJWZY1E5pJgV6uOQ3lm392MplpU9eps5YUSKAw5paqSWA4JTOIAiCM6Ct6g5UA6IoMD3+WOtDZvjPufsc9j7nLMP1/v14nX2/k177aPydf1+a11LEYGZmVlbt1ZrN8DMzKwcLlhmZlYTXLDMzKwmuGCZmVlNcMEyM7Oa0LG1G9BebbDBBtG3b9/WboaZWU2ZPn362xGxYal9LlhV0rdvX6ZNm9bazTAzqymSXq5vX83eEpS0sInH95X0VIU+e5ik2ypxLTMzK0+76mFJ6hARy1q7HQCzXn2XvqfdXtXPmHfO16p6fTOztqRme1gFubczWdK1wCxJHSSdK+kxSTMlHVPinL6SHpD0eP6za9G1pki6SdIzkiZIUt63T972IPD1lv2WZmbWXnpYOwLbRsRcSaOAdyNiqKTOwEOS7gKKM6jeBL4UEYsl9QeuA4bkfYOBAcBrwEPAbpKmAX8Avgi8ANzQIt/KzFb4+OOPmT9/PosXL27tplgFdOnShT59+tCpU6eyz2kvBevRiJibX38ZGChpRH7fA+gPPFd0fCfgYkmDgGXAlnWuNR9A0gygL7AQmBsRz+ft1wCj6jYiF8tRAB3WKznIxcyaaf78+XTv3p2+ffuSb3xYjYoIFixYwPz58+nXr1/Z57WXgvVB0WsBJ0bEncUHSOpb9PYU4A3gC6TbosX/y7ak6PUyVv6OGk0JjoixwFiAzr37O1XYrIIWL17sYtVOSKJXr1689dZbTTqvvRSsYncCx0m6NyI+lrQl8GqdY3oA8yNiuaSRQIdGrvkM0E/S5hHxInBoY43YbpMeTPOgCLOKcrFqP5rzz7LmB12UMA6YAzyeh7H/nk8W5kuBkZIeJt0O/IAGRMRi0q2+2/Ogi3rnCZiZWXXI62FVx5AhQ8ITh80q5+mnn2brrbde8b7S00Y8TaTl1f1nCiBpekQMKXV8e+xhmZlV3IIFCxg0aBCDBg3iM5/5DJtsssmK9x999NEqx15wwQUsWrSo0WsOGzaswUSchQsXcswxx7D55pszYMAA9thjDx555JEmt33KlCn84x//aPJ5bU17fIZlZlZxvXr1YsaMGQCMHj2abt26ceqpp5Y89oILLuA73/kOXbt2Xa3P/P73v0+/fv14/vnnWWuttXjppZd4+umnm3ydKVOm0K1bN3bdddfVak9TLFu2jA4dGhse0DQ1VbAkBXB+RPwovz8V6BYRo1u1YSU46cKs/Zs0aRKnnnoqS5cuZejQoYwZM4bf//73vPbaawwfPpwNNtiAyZMnc9xxx/HYY4/x4YcfMmLECM4666xGr/3iiy/yyCOPMGHCBNZaK90M22yzzdhss82YN28e++23H089ldLmzjvvPBYuXMjo0aO58MILueyyy+jYsSPbbLMN55xzDpdddhkdOnTgmmuu4aKLLmLTTTfl6KOP5q233mLDDTfkiiuuYNNNN+Woo45inXXW4ZlnnuHll1/miiuu4Morr2Tq1KnstNNOjB8/HoC77rqLM888kyVLlrD55ptzxRVX0K1bN/r27cvRRx/NXXfdxQknnMCbb765Sluuv/761fp911TBIg05/7qkX0fE2009WVLHiFhahXaZ2Rpm8eLFHHXUUUyaNIktt9ySI488kjFjxnDyySdz/vnnM3nyZDbYYAMAfvnLX7L++uuzbNky9tprL2bOnMnAgQMbvP7s2bMZNGhQk3sp55xzDnPnzqVz586888479OzZk2OPPXaVHuH+++/PkUceyciRI7n88ss56aSTuPXWWwH497//zb333svEiRPZf//9eeihhxg3bhxDhw5lxowZ9OnTh7PPPpt77rmHddddl9/85jecf/75nHHGGUCaEPzggw8CsPHGG6/SltVVa8+wlpLmOZ1Sd4ekz0malOOYJknaNG8fL+l8SZOB30iaJamnkgWSjszHXS1p7wZim66WdGDR502QdECLfGsza3OWLVtGv3792HLLlDswcuRI7r///pLH/ulPf2L77bdn8ODBzJ49mzlz5lStXQMHDuTwww/nmmuuoWPH0n2SqVOncthhhwFwxBFHrCgwkIqZJLbbbjs22mgjtttuO9Zaay0GDBjAvHnzePjhh5kzZw677bYbgwYN4sorr+Tll1cOnP72t7/dpLY0Ra0VLIBLgMMl9aiz/WLgqogYCEwALizatyWwd76V+BCwGyl+6SXgP/IxOwMPszK2aXvg20XXGQd8FyB/9q7AHcUNkDRK0jRJ05YtercS39XM2qh11123rOPmzp3Leeedx6RJk5g5cyZf+9rXyoqXGjBgAE8++STLly//xL6OHTuusr34erfffjvHH38806dPZ4cddmDp0sZvKhXPiercuTMAa6211orXhfdLly4lIvjSl77EjBkzmDFjBnPmzOGPf/zjiuOKfy/NaUtDau2WIBHxnqSrgJOAD4t27cLKUNqrgf9XtO/GohT3B4A9SHOpxgCjJG0C/CsiFuZi9InYpoi4T9Ilkj6dP+fPdW8vOunCrOW09jPcxYsXM2/ePF544QW22GILrr76avbcc08Aunfvzvvvv88GG2zAe++9x7rrrkuPHj144403+Nvf/sawYcMavf7mm2/OkCFDOPPMM/n5z3+OJJ5//nnmzJnDvvvuy5tvvsmCBQvo1q0bt912G/vssw/Lly/nlVdeYfjw4ey+++5ce+21LFy4kO7du/Pee++tuPauu+7K9ddfzxFHHMGECRPYfffdy/7eO++8M8cff/yK771o0SLmz5+/oqdZUF9bevbsWfZn1VVzBSu7AHgcuKKBY4oLRvHE4PuB44FNgdOBg4ERpEIGDcc2XQ0cDhwCHN1QA510Yda+denShSuuuIJvfvObKwZdHHvssQCMGjWKr371q/Tu3ZvJkyczePBgBgwYwGabbcZuu+1W9meMGzeOH/3oR2yxxRZ07dqVXr16ce6559KpUyfOOOMMdtppJ/r168dWW20FpNuU3/nOd3j33XeJCE455RR69uzJ/vvvz4gRI/jLX/7CRRddxIUXXsjRRx/Nueeeu2LQRbk23HBDxo8fz6GHHsqSJSnJ7uyzz/5EwaqvLaujpiYOS1oYEd3y6/9HKhyXR8RoSRNJPamrJR0FHBgRB0saD9wWETcVXec54L2IGCLpv4ATgBMi4i+SfkuKbfofSd/N1y8sMbIR8Cjwz4jYqaG2euKwWWWVmmRqtW1Nmjj8P8AGRe9PAr4raSZwBPDDBs59hJXp7Q8AmwCFp471xjZFxBvA0zTcszMzsyqoqVuChd5Vfv0G0LXo/TzSelV1zzmqxLYjil7/g6LCnZcQKR5v+t+FF5K6kpYqua6ZX8HM7BN22mmnFbfXCq6++mq22267VmpR21RTBas1SdobuJw0cdlDAM1aQUS0y8T25sQt1brmPI6q5VuCSFomaYakpyTdmHtA9R17lKSLm/tZEXFPRGwaERdIGp1TNsyshXTp0oUFCxY06y86a1sKCzh26dKlSefVeg/rw4gYBGkiL3AscH6rtihzNJNZZfXp04f58+c3edE/a5u6dOlCnz59mnROrResYg8AAyWtT7p1txmwCBgVETOLD5S0P/BTYG1gAXB4RLwhaTRpuPtm+ecFEXFhPud04EjgFeAtYHpLfCkzSzp16tSk5dSt/anpW4IFkjoCXwVmAWcBT+TEi/8LXFXilAeBnSNiMHA98JOifVsBXwF2BM6U1EnSDqQh9INJk4aH1tMOJ12YmVVJrfew1pE0I79+APgjacj6NwAi4l5JvUrEOPUBbpDUm9TLmlu07/aIWAIskfQmsBEpvumWiFgEkOd8fYKTLszMqqfWC9aKZ1gFKj2EqG7xuIg02m+ipGHA6KJ9xWNLl7Hyd+QCZGbWimq9YJVyPyk+6Re5GL2d8weLj+kBvJpfjyzzmuMlnUP6ne0P/L6hExzNZGZWWe2xYI0GrsiJF4soXZBGAzdKepWU0N7gk9yIeFzSDcAMUmjuAw0db2ZmlVdTWYK1xFmCZmZN116zBM3MbA1SswVL0sGSQtJWzTz/IEnbNLD/2MJqxGZm1vpq+RnWoaT5VIew6ii/ch0E3AZ8Yq1qSR0j4rLVaVxLJF20BKdpmFlbUZM9LEndSMvcf49UsJA0TNJtRcdcnNfFQtI5kuZIminpPEm7AgcA5+Ysws0lTZH0K0n3AT8szguU9ANJj0l6UtKfG8osNDOz6qjVHtZBwN8j4jlJ/5K0fX0H5qimg4GtIiIk9YyId/Lk3xULO+Zh7z0jYs/8fnTRZW6OiD/k7WeTCuVFVfheZmZWj5rsYZFuB16fX1+f39fnPdIy9+MkfZ001L0+N9SzfVtJD0iaRZrjNaDUQY5mMjOrnprrYUnqRVqocVtJAXQgpVBMZNUC3AUgIpZK2hHYi3T78ARKLPSYfVDP9vHAQRHxZL7NOKzUQY5mMjOrnporWMAI4KqIOKawIT93AthGUmdSsdoLeDA/7+oaEXfkZe9fyMe+D3Qv8zO7A69L6kTqYb3ayPFOujAzq7BaLFiHAufU2fZn4DDgT8BM4HngibyvO/AXSV0AAafk7dcDf5B0EqkINuRnpFDdl0mJ8OUWOjMzqxAnXVSJky7MzJrOSRdmZlbzXLDMzKwmtOlnWJKWkZ4ZibQ21QkR8Y9GzlkYEd0aOWYcaT2sT6RcVIqTLszMKqtNFyyKFmiU9BXg18Ceq3vRiPj+6l7DzMxaVi3dElwP+HfhjaQf57ikmZLOqnuwpLUkXSpptqTbJN0haUTeN0XSkPx6YdE5IySNz6/HSxojabKklyTtKelySU8XjjEzs5bT1ntY60iaQZpX1Zs84VfSl4H+wI6k24UTJe0REfcXnft1oC+wHfBp4Gng8iZ+/qfyZx4A/JWUX/h94DFJgyJiRvHBkkYBowA6rLdhEz/KzMwa0tZ7WB9GxKCI2ArYB7hKKfTvy/nPE8DjwFakAlZsd+DGiFgeEf8EJjfj8/8aadz/LOCNiJgVEcuB2aRiuIqIGBsRQyJiSIeuPZrxcWZmVp+23sNaISKmStoA2JDUq/p1RPy+gVNU7qWLXneps29J/rm86HXhfc387szM2oOa+Us3L9TYAVgA3An8QtKEiFgoaRPg44h4s+iUB4GRkq4kFblhwLUlLv2GpK2BZ0mp7u9Xor2OZjIzq6y2XrAKz7Ag9ZhGRsQy4K5cZKbmZUEWAt8BigvWn0l5gk8Bz5GilUpFqJ9GWsjxlXxsg0PizcysdbTraCZJ3XIPrBfwKLBbfp5VdY5mMjNruoaimdp6D2t13SapJ7A28IuWKlZmZlZ5bapgFSVbdAKWAlcCF+SReU0WEcMq17ryUjTMzKw62lTBYtVki0+TBkn0AM5szUblofTljjoEHM1kZlZpbXYeVh7xNwo4QUkHSecWpVscAyBpWE6uuEnSM5Im5AKDpHmSfiVpal66fntJd0p6UdKx+ZhukiZJelzSLEkH5u19c6rFpaS5Xp8ttE3SBvma/tvczKyFtLUe1ioi4iVJa5GSKg4E3o2IoXlV4Yck3ZUPHQwMAF4DHiIlUjyY970SEbtI+i1pqfvdSPOtZgOXAYuBgyPivTzP62FJE/O5nwe+GxH/B0ASkjYCJgI/jYi7i9vrpAszs+pp0wUrK9yK+zIwsJAHSLpV2B/4CHg0IuYD5GHwfVlZsArFZxbQLSLeB96XtDgPyPgA+JWkPUgTgjcBNsrnvBwRDxe1pRMwCTg+Iu6r29CIGAuMBejcu3/7HX5pZtYK2nTBkrQZaVmRN0mF68SIuLPOMcNYNYViGat+r8bSKg4nTSzeISI+ljSPlYkXH9Rp0lJgOvAV4BMFy8zMqqfNFixJG5Ju2V0cESHpTuA4SffmwrIl8GoFPqoH8Ga+5nDgcw0cG8DRwI2STouIc+o70EkXZmaV1dYKViHZojCs/Wrg/LxvHOlW3+N5UMVbwEEV+MwJwF8lTQNmAM80dHBELJN0SD7nvYi4tAJtMDOzRrTrpIvW5KQLM7Omayjpos0OazczMyvmgmVmZjWhrT3DalRRfFPBQcC1EbFrI+dNAU6NiNW+T5dHEg6JiLfrO8ZJF2ZmlVVzBYui+KYiDRYrMzOrfe3ilqCkhflnvTFNdY4fk6OaZks6q2j7PElnFcU0bZW395J0l6QnJP2eJuYKmpnZ6qvFgrWOpBn5zy0l9g8GTga2ATYjRTHVdXoehTIQ2FPSwKJ9b0fE9sAY4NS87UzgwYgYTErO2LRUwySNyoVw2rJFpdaKNDOz5qrFgvVhRAzKfw4usf/RiJiflySZQZq7Vde3JD0OPEHKINymaN/N+ef0onP3AK4BiIjbgX+XalhEjI2IIRExpEPXHk37VmZm1qBafIbVmIZimpDUj9RzGhoR/5Y0npVRTMXn1z23SRPWnHRhZlZZtdjDWl3rkTIC383J618t45z7SZmDSPoq8KnqNc/MzEppjz2sBkXEk5KeIC0v8hJpOZLGnAVcl28j3gf8bxWbaGZmJTiaqUoczWRm1nSOZjIzs5rXpm8JFqVadASeBkZGxKIyzx0EbBwRd1ShXaOBhRFxXn3HOOnCzKyy2noPqzCEfVvSysLHlnOSpI7AIGDfKrbNzMxaUJvuYdXxADBQ0vrA5aRJwYuAURExM/d6NibNnXob2J00yXh34NfA1hT1iiQ9BewXEfMk/Yw0CvCVfO70iDhP0g+AUcDawAvAEeX28MzMrLLaeg8LWNFj+irp9uBZwBMRMRD4v8BVRYfuABwYEYcBZwA35B7aDQ1cewjwDVJCxteB4od9N0fE0Ij4AumW5PcaaaeTLszMqqSt97AKKxBD6mH9EXiEVGCIiHtzzl8hVmJiRHzYxM/YHfhL4TxJfy3at62ks4GeQDfgzoYuFBFjgbEAnXv39/BLM7MKausF6xPJ7KXCbFmZQvFBA9dayqo9ykK6RUNBtuOBg/LcraOAYQ0ca2ZmVdTWC1YphdSJX0gaRgqrfa9EHXsf6F70fh6wH4Ck7YF+efuDwO8l/Zr0+/ga8Ie8rzvwuqRO+TNfLbeRjmYyM6usmniGVcdoYIikmcA5wMh6jpsMbJNT3b8N/BlYP99iPA54DiAiHiMlsD9JCr6dBhQeQP2MdAvybuCZanwZMzMrj5MuAEndImKhpK6kHtyoiHh8da7ppAszs6ZrKOmiFm8JVsNYSduQnmtdubrFyszMKq9mCpak04HDSMt+LAeOiYhHKnT5AyKiW4WuZWZmVVATBUvSLqQBE9tHxBJJG5Am87ZZjmYyM6usWhl00Zs0GnAJQES8DfSRdDOApAMlfShpbUldJL2Ut28u6e+Spkt6QNJWeXs/SVMlPSbpF8UfJOnHeftMSWflbX0lPS3pD5JmS7pL0jot+QswM1vT1UrBugv4rKTnJF0qaU/gcVI6BcB/AE8BQ4GdSCP7IE3iPTEidiCtMnxp3v47YExEDAX+WfgQSV8G+gM7krIId5C0R97dH7gkIgYA75AnLxdz0oWZWfXUxC3BPIJvB1JhGg7cAJwGvCBpa1KBOR/YA+gAPCCpG7ArcGPRHK3O+edurCw4VwO/ya+/nP88kd93IxWq/wXmRsSMvH06KbOwbjuddGFmViU1UbAAImIZMAWYImkWaf7VA6SMwY+Be0jJFB1Ivam1gHfqJmUUX7LENgG/jojfr7JR6gssKdq0DPAtQTOzFlQTBUvS54HlEfF83jQIeJk0Z+oq4KqIeEtSL+AzwOyICElzJX0zIm7MkU4DI+JJ4CHgEOAaUoJFwZ2kBI0JuVe3CakYNpmTLszMKqusgiVp/Yj4V7Ub04BuwEWSepIyAV8gLfvxAbARqXABzATejJWzoQ8Hxkj6KdAJuJ6UaPFD4FpJPyQlYAAQEXflW4xT823EhcB3SD0qMzNrRWUlXUh6HpgBXAH8Lco5aQ3npAszs6ZrKOmi3FGCW5IGExxBGujwK0lbVqqBZmZmjSmrYEVyd0QcCnyfNODhUUn35Um9ZmZmVVXuM6xepGc5RwBvACeSEs4HATeycqmOuuc1OU5J0s+B+yPiHkknA2MrsSy9pNHAwog4rwLXGg/cFhE31XeMky7MzCqr3FGCU0nzlQ6KiPlF26dJuqzUCc2NU4qIM4renkwaybdaBUtSTYyGNDOz+jV6S1BSB1Jv4hd1ihUAEfGbEqdB8+OUxksaIekkYGNgsqTJkg7Ia1vNkPSspLn5+B3yrcnpku6U1Dtvn5Kftd1HGhVY/J1+kOOXnpT057ysSOGzL5T0D0kvSRqRt0vSxZLmSLod+HTjv1ozM6ukRgtWnrD7hWZcu7lxSoXPvRB4DRgeEcMjYmJEDMoTgZ8EzlNaCfgiYESOX7oc+GXRZXpGxJ4R8T912nZzRAyNiC8ATwPfK9rXG9id1Ds8J287GPg8sB3wA1KCxic4msnMrHrKvVU2Q9JE0vOqDwobI+Lm+k5oTpxSOQ2R9BPgw4i4RNK2wLbA3XneVAfg9aLDb6jnMttKOhvoSZrjdWfRvlsjYjkwR9JGedsewHW5eL8m6d56vrOjmczMqqTcgrU+sAD4YtG2IC0pX69mxCk1SNJewDdJBQRSlNLsiKhvpOIH9WwfT3oe96Sko4BhRfuKI5hU9LpJBchJF2ZmlVVuwRoXEQ8Vb5C0W0MnNCdOqcRl3ge6A29L+hwpbX2fiPgw738W2FDSLhExNd8i3DIiSl2rWHfg9Xz84cCrjRx/P3CMpKtIz6+GA9c2co6ZmVVQuQXrImD7MrYVa26cUrGxwN8kvU7qqfUCbsm3/16LiH3zwIgLJfXI3+cCShe/Yj8jPTN7GZhFKmANuYXUu5wFPAfc18jxZmZWYQ1GM+Wh6buShpf/tmjXesDBedCCleBoJjOzpmsomqmxHtbapJ5SR1bthbwHjKhM88zMzBpXbvjt5yLi5ao1QjqYNIBj64h4plqfkz9rELBxRNyR3x8AbBMR5zR4YhN17t0/eo+8oJKXbJecpGFmxVanh1XQWdJY0iq7K86JiC/We0bTHAo8SFqjanTxDkkd8mjDShkEDAHuAIiIiaSYKTMza8PKLVg3ApcB46jw2lB5KfvdSCPvJgKjJQ0DziTNqRqU51tdDOwJzCVNeL48Im7Kc73OJ926fBs4KiJelzSFNLBiOGm+1ffy+58D60jaHfg1aeXgIRFxQs4IfI9U0D4D/CR/RjfgL8CnSOtq/TQi/lLJ34OZmTWs3IK1NCLGVKkNBwF/j4jnJP1LUmHk4Y7AthExN48E7EtKmvg0KZ3i8qKkiwPzEPlvk5Iujs7X6BgRO0raFzgzIvaWdAa5QAHkeVjFCkkXW5EK6E3AYtIgk/dyJuLDkibWHdkoaRRpJCQd1tuwIr8cMzNLyi1Yf5X0f0jDu1dMrK3QKsSHkoaiQ1oR+FDgduDRiJibt+8O3JgTKP4paXLe/nkaTrooTGyeTip45SiVdCHgV5L2IKXOb0Iamv/P4hOddGFmVj3lFqyR+eePi7YFsNnqfHieNPxFUlRSkApOkJ4vFadUqMTphe0NJV0Uiusyyv+upZIuDgc2BHaIiI8lzQO6lHk9MzOrgLL+Eo+IkutdVcAIUuLFMYUNOV199zrHPQiMlHQlqXAMIyVNNCfpopCe0RQ9SJObP5Y0HPhcYyc4msnMrLLKXcDxyFLbI+Kq1fz8Q1mZiF7wZ+A44MU62/Yipbs/Rxo88W5EfNSMpIvJwGmSZpAGXZRjAum26DRgBlDVofdmZvZJ5c7DuqjobRdS8Xg8Ilps8rCkbjkBvhfwKLBbRPyzsfNai5MuzMyabrXnYUXEiXUu2IO0AnFLui3nEq4N/KItFyszM6u8RhdwrMcioH8lG9KYiBgG7EMKy/1ZXv33DklbVuszJS2s1rXNzKxpyn2G9VdWrgfVAdga+FO1GlVPG0QaVn9lRByStw0iDS9/riXbUo5Zr75L39Nub+1mtHmOZjKzcpU71Pu8otdLgZcjYn4V2tOQ4cDHEXFZYUNEzJDUTdIk6qRQSOoL/I00wnBX0ppXB0bEh5J+QJrguzZp2ZMjImKRpH6k0Ycdgb8XPsdJF2Zmra+sW4IRcR9pZFx30l/aH1WzUfXYljQBuK5CCsX2pKL2P7k3Bum25SURMQB4B/hG3n5zRAzNy6M8TYptAvgdMCYihrLqpOCGPmMFSaMkTZM0bdmid1fnu5qZWR1lFSxJ3yKNzPsm8C3gkTycvC0opFDMBO5hZQoFwNyImJFfF6ddbCvpAUmzSJOCB+TtuwHX5dfFg0oa+owVImJsRAyJiCEduvaoxHczM7Os3FuCpwNDI+JNAEkbkv7ivqlaDSthNqXX4GoohaI4tWIZKegWYDxwUEQ8mbMEhxUdV2qcv5MuzMxaWbkFa61CscoW0PwRhs11L6mX84OI+AOApKGk1IkmpVCQbm2+npMxDic93wJ4iLTEyTV5e4GTLszMWlm5Refvku6UdFTukdxOXk+qpeRk9IOBL0l6UdJs0tpZdwBDcgrF4ZSXQvEzUlrG3XWO/yFwvKTHSEWqYEIzPsPMzCqowaQLSVsAG0XEQ5K+Tsr4E/BvYEJEvFjvyWs4J12YmTVdQ0kXjfWwLiCFxRIRN0fEf0bEKaRezQWVbKSZmVlDGitYfSNiZt2NETGN8teXMjMzW22NDbpoaCTcOg3sa5MkLYyIbnlS8a4RcW0jx/cFbouIbZv6WU66KI+TLsysXI31sB7LqRCrkPQ9Sk/irRV9gcNauxFmZla+xnpYJwO3SDqclQVqCCnS6OAqtqvazgG2zmtiXUnKKLwaWDfvPyEi/lF8gqQHgBMLE5ElPQQcV+qWqZmZVV6DBSsi3gB2zXOPCrfFbo+Ie6vesuo6DTg1IvYDkNQV+FJELJbUn5R2UXeUyjjgKODknBDfuW6xkjSKlFFIh/U2rO43MDNbw5S7HtZk0kq97VUn4OKc/r4MKLVkyY2kZU1+DBxNSstYRUSMBcYCdO7dv/GVMc3MrGzlJl20d6cAbwBfID3XW1z3gJzmfjdwIClPseQ8gQInXZiZVdaaWrDeJ8UzFfQA5kfEckkjSWt+lTIO+CvwQET8q8ptNDOzIi2dB9hWzASWSnpS0inApcBISQ+Tbgd+UOqkiJgOvAdc0WItNTMzYA3rYUVEt/zzY2CvOrsHFr3+73zcPFYONkHSxqQif1dVG2pmZp+wpvawmkzSkaTA3NMjYnlrt8fMbE3T4gVL0hRJX6mz7WRJl7Z0WxoiaXzxIpURcVVEfDYibmzNdpmZrala45bgdaQ1p+4s2nYI8ONWaEvVOJqp7XD8k1n70Bq3BG8C9pPUGVbk9W0MdJU0VdLjkm6U1C3vnyfprLx9lqSt8vZ1JV0u6TFJT0g6MG8fJ2lG/vOWpDMlDZN0W6EBki7O63ohaQdJ90mantf86l23wZLOyJ/zlKSxklTl35GZmdXR4gUrIhYAjwL75E2HAJOA04G9I2J7YBrwn0WnvZ23jwFOzdtOB+6NiKHAcOBcSetGxPcjYhBpvtQCSkzwLcgrDl8EjIiIHYDLgV+WOPTiiBiaQ3DXAfar53qjJE2TNG3Zoncb+U2YmVlTtNagi8JtQfLPucA2wEM5328kqy5Df3P+OZ2Vy5p8GTgtHz+FlCy/KYCkLqRkihMi4uUG2vF50ijAu/N1fgr0KXHccEmPSJoFfBEYUOpiETE2IoZExJAOXXuUOsTMzJqptYa13wqcL2l7Uo/lCeDuiDi0nuOX5J/LWNlmAd+IiGdLHH8ZcHNE3JPfL2XV4lxYNkXA7IjYpb6G5uJ3KTAkIl6RNJqGl10xM7MqaJWCFRELJU0h3YK7DngYuETSFhHxQg6j7RMRzzVwmTuBEyWdGBEhaXBEPCHpeKB7RJxTdOzLwDb5uVkX0hysB4FngQ0l7RIRU/Mtwi0jYnbRuYXi9HZ+rjaC9ByuQY5mMjOrrNach3UdKbvv+oh4i5SEfp2kmaQCtlUj5/+CFFo7U9JT+T2kZ1zbFQ28ODYiXgH+REq4mEDq0RERH5EK0G8kPQnMAHYt/pCIeAf4AzCL1DN8rPlf2czMmksRDhWvhiFDhsS0adNauxlmZjVF0vSIKBku7qQLMzOrCTVbsCSFpKuL3nfM865ua+i8Bq53bI5fMjOzNqiWw28/ALaVtE5EfAh8CXi1uReLiMsq1jKcdLEmcZKGWcuo2R5W9jeg8LfFoaSBHECDSRgXSjojv/6KpPslrSVptKRT8/YtJN2Tlx95XNLmSs7NaRezJH27hb+rmdkardYL1vXAIXmu1EBSmnpBySQM4DTg25KGAxcC3y2Rvj4BuCQivkAaNfg68HVgEGlk4975eqvEODnpwsysemq6YEXETFLyxaHAHXV2l0zCiIhFwA+Au0mRSy8WnySpO7BJRNySP2NxPmd34LqIWBYRbwD3AUPrtMdJF2ZmVVLLz7AKJgLnAcOAXkXbG0rC2I6UM7hxiX31Bds68NbMrBW1h4J1OfBuRMySNKxoe31JGJ8DfgQMBu6QdGtErLiVGBHvSZov6aCIuDWnY3QA7geOkXQlsD6wBw0sieKkCzOzyqrpW4IAETE/In5XYtcnkjDysiB/BE6NiNeA7wHj8jOwYkcAJ+XUjX8AnwFuISVlPAncC/wkIv5ZlS9lZmaf4KSLKnHShZlZ0znpwszMap4LlpmZ1YT2MOjiEySdDhxGWj9rOXBM8cCKMq9xALBNnWVKyuakizWHky7MWka7K1iSdiEtYb99RCyRtAGwdlOvExETSUPmzcysDWiPtwR7A29HxBKAiHg7Il6TNE/SbyQ9mv9sASBpf0mP5PimeyRtlLcfJeni/Hp8jnT6h6SXJI1otW9nZraGao8F6y7gs5Kek3SppD2L9r0XETsCFwMX5G0PAjtHxGBS1NNP6rlub1LaxX5AyduEjmYyM6uedndLMCIWStoB+A9ShuANkk7Lu68r+vnb/LpPPqY36dbh3HoufWvOHJxT6IWV+OyxwFiAzr37e76AmVkFtbuCBRARy0j5gVMkzQJGFnYVH5Z/XgScHxETc1LG6Houu6TodaMxTU66MDOrrHZ3S1DS5yX1L9o0CHg5v/520c+p+XUPVq6jNRIzM2uT2mMPqxtwkaSewFLgBWAU6dlTZ0mPkAr1ofn40cCNkl4FHgb6tXSDzcyscWtMNJOkecCQiHi7JT7P0UxmZk3naCYzM6t5a0zBioi+DfWuJPXNqe7F20ZLOjXPwxqRt62f52x9t9ptNjOzldrjM6yqkdSDtM7W2Ii4oqFjHc1ktcTxUlYL1pgeVgV0A/4GXBsRY1q7MWZmaxoXrPKdDzwYEb+t7wAnXZiZVY8L1kr1DZcsbL8XOFDSp+u9QMTYiBgSEUM6dO1R8Qaama3JXLBWWgB8qs629YHCQI3rgTHAHZK6t2TDzMzMgy5WyBmEr0vaKyImSVof2Af4HSmTkIi4IGcO3iJp34j4qL7rOZrJzKyy3MNa1ZHATyXNIN0CPCsiXiw+ICL+C3gFuFqSf39mZi3EPawiETGH3Juqs/2oOu89B8vMrIW5h2BmZjWh3RUsScskzZD0pKTHJe1aoeuOk7RNfj1P0gaVuK6ZmZWnPd4S/DAiBgFI+grwa6B41WEkdchrZpUtIr7flOOddGHWspzW0f61ux5WHesB/waQNEzSZEnXArPytlslTZc0W9KovO2A3EObIelZSXPz9imSSiYIm5lZ9bXHHtY6eZRfF6A38MWifTsC20bE3Pz+6Ij4l6R1gMck/TkiJgITAST9Cbiv5ZpuZmb1aY8Fq/iW4C7AVZK2zfseLSpWACdJOji//izQnzSBGEk/yde6pNwPzr20UQAd1ttwtb6EmZmtqj0WrBUiYmoeHFGoHh8U9kkaBuwN7BIRiyRNIfXKkLQX8E1gjyZ+3lhgLEDn3v3XjJUxzcxaSLsuWJK2AjqQe0119AD+nYvVVsDO+ZzPAZcC+0TEh839bCddmJlVVnssWIVnWAACRkbEMkl1j/s7cKykmcCzwMN5+1FAL1L8EsBrEbFvtRttZmYNU4TvXFXDkCFDYtq0aa3dDDOzmiJpekSUHJHd3oe1m5lZO+GCZWZmNaGmn2FJWkaeBJwdFBHzWqk5q3DShZk1ldM6GlbTBYuiOVd1KY2YUEQsb9kmmZlZNbSrW4KS+kp6WtKlwOPAZyWNkTQtxy+dVXTsPEln5YDcWXloO5K6Sboib5sp6Rt5+5clTc3H3yipW+t8SzOzNVOtF6x1inL/bsnbPg9cFRGDI+Jl4PQ84mQgsKekgUXnvx0R2wNjgFPztp8B70bEdhExELg3Tz7+KbB3Pn4a8J91GyNpVC6O05YtercqX9jMbE3Vrm4JSuoLvBwRDxcd860cmdSRlC24DTAz77s5/5wOfD2/3hs4pHByRPxb0n75vIfy3Ky1gal1G+OkCzOz6qn1glVKcfxSP1LPaWguPOPJ8UvZkvxzGSt/FwLqFhsBd0fEoVVpsZmZNao9Fqxi65EK2LuSNgK+Ckxp5Jy7gBOAkwEkfYqUgnGJpC0i4gVJXYE+EfFcfRdxNJOZWWXV+jOsBkXEk8ATwGzgcuChMk47G/iUpKckPQkMj4i3SJFN1+Uop4eBrarTajMzK8XRTFXiaCYzs6ZzNJOZmdW8NlWwJG0k6VpJL+Wl66cWLbBoZmZrsDYz6CInU9wKXBkRh+VtnwMOKPP8DhGxrHotbBpHM5lZUzmaqWFtqYf1ReCjiLissCEiXo6IiyR1kHSupMdy+sQxkFYNljRZ0rXArPz+Pkl/kvScpHMkHS7p0ZxcsXk+b39Jj0h6QtI9eQQhkkZLulzSlNzLOylv/4WkHxbaJemXhX1mZtYy2lLBGkCKUyrle6T0iaHAUOAHeY4VwI6kNItt8vsvAD8EtgOOALaMiB2BccCJ+ZgHgZ0jYjBwPfCTos/aCvhKvu6ZkjoBfwRGAkhaizSxeELdRjrpwsysetrMLcG6JF0C7A58BLwMDJQ0Iu/uAfTP+x6NiLlFpz4WEa/na7xImlcFKdV9eH7dB7hBUm9SakXx+bdHxBJgiaQ3gY0iYp6kBZIGAxsBT0TEgrptdtKFmVn1tKUe1mxg+8KbiDge2AvYkJQ0cWJEDMp/+kVEoRB9UOc6S4peLy96v5yVBfoi4OKI2A44htLpF7BqAsY40lys75LmdJmZWQtqSz2se4FfSTouIsbkbV3zzzuB4yTdGxEfS9oSeHU1PqtH0fkjyzznFuDnQCfgsMYOdtKFmVlltZmCFREh6SDgt5J+ArxF6j39F3Aj0Bd4PI8mfAs4aDU+bjRwo6RXSakV/Ro+HCLiI0mTgXfa0mhEM7M1hZMuypQHWzwOfDMinm/seCddmJk1nZMuVpOkbYAXgEnlFCszM6u8Na5g5VWJn6qzbbSkUyXtnOdnzcgrF48GiIg5EbEZ0FHSq7m3ZWZmLajNPMNqI64EvhURT0rqQFq9GFhxS/Bg4BVgDxpZpsRJF2a2JqpmWod7Cqv6NPA6QEQsi4g5RfuGA08BYwAv5Ghm1sJcsFb1W+BZSbdIOkZS8fysQ4HrSMPb98sJGGZm1kLWxIJV37DIiIifA0NI6RiHAX8HkLQ2sC9wa0S8BzwCfLnuBRzNZGZWPWviM6wFwKfqbFufHM8UES8CYyT9AXhLUi9gN9Jk41lpGhhdgUXAKg+pHM1kZlY9a1zBioiFkl6XtFdETJK0PrAP8DtJXwPuiDQ5rT8pmukd0u3A70fEdQCS1gXmSuoaEYtKfY6TLszMKmtNvCUIcCTwU0kzSJFQZ+We1RGkZ1gzgKuBw4HOpPT2Fb2piPiAlPi+f8s228xszbXG9bAgzatiZXJ78fZD6jll/RLHfr3S7TIzs/o5mqlKJL0PPNva7aiADYC3W7sRq8nfoW3wd2gb2vp3+FxEbFhqxxrZw2ohz9aXh1VLJE2r9e/h79A2+Du0DbX8HdbUZ1hmZlZjXLDMzKwmuGBVz9jWbkCFtIfv4e/QNvg7tA01+x086MLMzGqCe1hmZlYTXLDMzKwmuGBVgaR9JD0r6QVJp7V2e5pK0mclTc6LWM6W9MPWblNzSeog6QlJt7V2W5pDUk9JN0l6Jv/z2KW129RUkk7J/x49Jem6OqsgtEmSLpf0ZvFir5LWl3S3pOfzz7qZpG1OPd/j3Pzv08y8MkXPVmxik7hgVVhe+PES4KvANsChkrZp3VY12VLgRxGxNbAzcHwNfoeCHwJPt3YjVsPvgL9HxFbAF6ix7yJpE+AkYEhEbAt0AOpLlGlLxpMyRoudBkyKiP7ApPy+rRvPJ7/H3cC2ETEQeA7475ZuVHO5YFXejsALEfFSRHwEXA8c2MptapKIeD0iHs+v3yf9JblJ67aq6ST1Ab4GjGvttjSHpPVIq1v/ESAiPoqId1q1Uc3TEVhHUkfSSgevtXJ7GhUR9wP/qrP5QNKq5OSfB7Vkm5qj1PeIiLsiYml++zDQp8Ub1kwuWJW3CfBK0fv51OBf9gWS+gKDSWuA1ZoLgJ8Ay1u5Hc21GfAWcEW+rTkurxRQMyLiVeA84H9Jq3m/GxF3tW6rmm2jiCisSP46aYXyWnc08LfWbkS5XLAqTyW21eTcAUndgD8DJ+eFK2uGpP2ANyNiemu3ZTV0BLYHxkTEYOADauM21Ar5Oc+BQD9gY2BdSd9p3VYZgKTTSbf/J7R2W8rlglV584HPFr3vQw3cAqlLUidSsZoQETe3dnuaYTfgAEnzSLdlvyjpmtZtUpPNB+ZHRKF3exOpgNWSvYG5EfFWRHwM3Azs2sptaq43JPUGyD/fbOX2NJukkcB+wOFRQ5NxXbAq7zGgv6R+ktYmPWCe2MptahKlZZX/CDwdEee3dnuaIyL+OyL6RERf0j+DeyOipv7PPiL+Cbwi6fN5017AnFZsUnP8L7CzpK7536u9qLGBI0UmAiPz65HAX1qxLc0maR/gv4AD6luAtq1ywaqw/DDzBOBO0n+Yf4qI2a3bqibbjbSY5Rclzch/9m3tRq2hTgQmSJoJDAJ+1brNaZrcO7wJeByYRfo7p81HA0m6DpgKfF7SfEnfA84BviTpeeBL+X2bVs/3uBjoDtyd/9u+rFUb2QSOZjIzs5rgHpaZmdUEFywzM6sJLlhmZlYTXLDMzKwmuGCZmVlNcMEyM7Oa4IJlZmY14f8Dv65InaV4iugAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "dff.plot.barh(x = \"Country\", y = \"Total_Customers\");"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7b426a07-ac6c-402a-b061-41547b4c8846",
   "metadata": {},
   "source": [
    "#### SQLite3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "4e9c5b21-ca99-46a3-84a3-8f6dc276cbc7",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "9c3eb2d1-21a9-420c-b5c3-4bab63bad9dc",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = sqlite3.connect(\"sqlite.db\") # connection step I"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "88053cd6-a9da-4ebf-b7ac-eba3e36307a8",
   "metadata": {},
   "outputs": [],
   "source": [
    "c = conn.cursor() # connection step II"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "287f6042-36f4-4710-a065-a5109c4d36d2",
   "metadata": {},
   "outputs": [],
   "source": [
    "ab = pd.read_sql(\"SELECT COUNT(CustomerID) AS Total_Customers, Country FROM Customers GROUP BY Country;\", conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "871b6189-de09-4bcd-8537-4af1ee82c74a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.frame.DataFrame"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(ab)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "2516c286-cb09-4ec9-bb10-a924145a866e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x7f2cdc87a260>"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c.execute(\"SELECT CustomerName FROM Customers LIMIT 5;\") # getting the query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "8cc0a8c7-c0b0-411d-a95a-77abda1a0018",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('Alfreds Futterkiste',)"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c.fetchone() # getting one result of the query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "5d5db1a9-a754-40fb-8f58-2a1fa84357af",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('Ana Trujillo Emparedados y helados',),\n",
       " ('Antonio Moreno Taquería',),\n",
       " ('Around the Horn',),\n",
       " ('Berglunds snabbköp',)]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c.fetchall() # getting all results of the query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "b3c81a5e-116b-47c9-8a12-f6fb5fb51117",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('Alfreds Futterkiste',)\n",
      "('Ana Trujillo Emparedados y helados',)\n",
      "('Antonio Moreno Taquería',)\n",
      "('Around the Horn',)\n",
      "('Berglunds snabbköp',)\n"
     ]
    }
   ],
   "source": [
    "for row in c.execute(\"SELECT CustomerName FROM Customers LIMIT 5;\"):\n",
    "                     print(row) # another way "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "3f4a60a3-9d57-4d80-8f85-2b14649b548a",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.close() # closing the connection"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aae9feac-cc59-4f96-b8c9-951e81ac7620",
   "metadata": {},
   "source": [
    "#### SQLite Exercises"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4b7efe0f-4294-4790-9331-c8a466d9c942",
   "metadata": {},
   "source": [
    "How many customers are in the Orders?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "5ab93436-f879-486c-a2f4-aa31d9d13c00",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Customer_Number</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>196</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(196,)]"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT COUNT(CustomerID) as Customer_Number \n",
    "FROM Orders;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0c1edeb9-e700-438d-8c64-2dc1f73986e2",
   "metadata": {},
   "source": [
    "**How many unique customers are in the Orders?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "ed47d1e1-5a63-4cbf-b221-cb90e192904c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Unique_Number_of_Customers</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>74</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(74,)]"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT COUNT(DISTINCT CustomerID) as Unique_Number_of_Customers \n",
    "FROM ORDERS;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f4893316-cb45-459a-b4a4-ec6438ea5178",
   "metadata": {},
   "source": [
    "**How many customers from each country are in the Customers table?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "ae3108de-5d76-4110-adbf-f5d7673f6123",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>COUNT(CustomerID)</th>\n",
       "        <th>Country</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Argentina</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Austria</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Belgium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9</td>\n",
       "        <td>Brazil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Canada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Denmark</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Finland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11</td>\n",
       "        <td>France</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11</td>\n",
       "        <td>Germany</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Ireland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Italy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>Mexico</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Norway</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Poland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Portugal</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>Spain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Sweden</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Switzerland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7</td>\n",
       "        <td>UK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>13</td>\n",
       "        <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>Venezuela</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(3, 'Argentina'),\n",
       " (2, 'Austria'),\n",
       " (2, 'Belgium'),\n",
       " (9, 'Brazil'),\n",
       " (3, 'Canada'),\n",
       " (2, 'Denmark'),\n",
       " (2, 'Finland'),\n",
       " (11, 'France'),\n",
       " (11, 'Germany'),\n",
       " (1, 'Ireland'),\n",
       " (3, 'Italy'),\n",
       " (5, 'Mexico'),\n",
       " (1, 'Norway'),\n",
       " (1, 'Poland'),\n",
       " (2, 'Portugal'),\n",
       " (5, 'Spain'),\n",
       " (2, 'Sweden'),\n",
       " (2, 'Switzerland'),\n",
       " (7, 'UK'),\n",
       " (13, 'USA'),\n",
       " (4, 'Venezuela')]"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT COUNT(CustomerID), Country \n",
    "FROM Customers \n",
    "GROUP BY Country;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ba45c314-6dd1-48c5-afa8-21ef787dbbc8",
   "metadata": {},
   "source": [
    "**Finding the customers whose country is Brazil or USA.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "6dfb4e6c-c183-40f5-a34a-dc56490a2a67",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>CustomerID</th>\n",
       "        <th>CustomerName</th>\n",
       "        <th>ContactName</th>\n",
       "        <th>Address</th>\n",
       "        <th>City</th>\n",
       "        <th>PostalCode</th>\n",
       "        <th>Country</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>15</td>\n",
       "        <td>Comércio Mineiro</td>\n",
       "        <td>Pedro Afonso</td>\n",
       "        <td>Av. dos Lusíadas, 23</td>\n",
       "        <td>São Paulo</td>\n",
       "        <td>05432-043</td>\n",
       "        <td>Brazil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>21</td>\n",
       "        <td>Familia Arquibaldo</td>\n",
       "        <td>Aria Cruz</td>\n",
       "        <td>Rua Orós, 92</td>\n",
       "        <td>São Paulo</td>\n",
       "        <td>05442-030</td>\n",
       "        <td>Brazil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>31</td>\n",
       "        <td>Gourmet Lanchonetes</td>\n",
       "        <td>André Fonseca</td>\n",
       "        <td>Av. Brasil, 442</td>\n",
       "        <td>Campinas</td>\n",
       "        <td>04876-786</td>\n",
       "        <td>Brazil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>32</td>\n",
       "        <td>Great Lakes Food Market</td>\n",
       "        <td>Howard Snyder</td>\n",
       "        <td>2732 Baker Blvd.</td>\n",
       "        <td>Eugene</td>\n",
       "        <td>97403</td>\n",
       "        <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>34</td>\n",
       "        <td>Hanari Carnes</td>\n",
       "        <td>Mario Pontes</td>\n",
       "        <td>Rua do Paço, 67</td>\n",
       "        <td>Rio de Janeiro</td>\n",
       "        <td>05454-876</td>\n",
       "        <td>Brazil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>36</td>\n",
       "        <td>Hungry Coyote Import Store</td>\n",
       "        <td>Yoshi Latimer</td>\n",
       "        <td>City Center Plaza 516 Main St.</td>\n",
       "        <td>Elgin</td>\n",
       "        <td>97827</td>\n",
       "        <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>43</td>\n",
       "        <td>Lazy K Kountry Store</td>\n",
       "        <td>John Steel</td>\n",
       "        <td>12 Orchestra Terrace</td>\n",
       "        <td>Walla Walla</td>\n",
       "        <td>99362</td>\n",
       "        <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>45</td>\n",
       "        <td>Let&#x27;s Stop N Shop</td>\n",
       "        <td>Jaime Yorres</td>\n",
       "        <td>87 Polk St. Suite 5</td>\n",
       "        <td>San Francisco</td>\n",
       "        <td>94117</td>\n",
       "        <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>48</td>\n",
       "        <td>Lonesome Pine Restaurant</td>\n",
       "        <td>Fran Wilson</td>\n",
       "        <td>89 Chiaroscuro Rd.</td>\n",
       "        <td>Portland</td>\n",
       "        <td>97219</td>\n",
       "        <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>55</td>\n",
       "        <td>Old World Delicatessen</td>\n",
       "        <td>Rene Phillips</td>\n",
       "        <td>2743 Bering St.</td>\n",
       "        <td>Anchorage</td>\n",
       "        <td>99508</td>\n",
       "        <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>61</td>\n",
       "        <td>Que Delícia</td>\n",
       "        <td>Bernardo Batista</td>\n",
       "        <td>Rua da Panificadora, 12</td>\n",
       "        <td>Rio de Janeiro</td>\n",
       "        <td>02389-673</td>\n",
       "        <td>Brazil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>62</td>\n",
       "        <td>Queen Cozinha</td>\n",
       "        <td>Lúcia Carvalho</td>\n",
       "        <td>Alameda dos Canàrios, 891</td>\n",
       "        <td>São Paulo</td>\n",
       "        <td>05487-020</td>\n",
       "        <td>Brazil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>65</td>\n",
       "        <td>Rattlesnake Canyon Grocery</td>\n",
       "        <td>Paula Wilson</td>\n",
       "        <td>2817 Milton Dr.</td>\n",
       "        <td>Albuquerque</td>\n",
       "        <td>87110</td>\n",
       "        <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>67</td>\n",
       "        <td>Ricardo Adocicados</td>\n",
       "        <td>Janete Limeira</td>\n",
       "        <td>Av. Copacabana, 267</td>\n",
       "        <td>Rio de Janeiro</td>\n",
       "        <td>02389-890</td>\n",
       "        <td>Brazil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71</td>\n",
       "        <td>Save-a-lot Markets</td>\n",
       "        <td>Jose Pavarotti</td>\n",
       "        <td>187 Suffolk Ln.</td>\n",
       "        <td>Boise</td>\n",
       "        <td>83720</td>\n",
       "        <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>75</td>\n",
       "        <td>Split Rail Beer &amp; Ale</td>\n",
       "        <td>Art Braunschweiger</td>\n",
       "        <td>P.O. Box 555</td>\n",
       "        <td>Lander</td>\n",
       "        <td>82520</td>\n",
       "        <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>77</td>\n",
       "        <td>The Big Cheese</td>\n",
       "        <td>Liz Nixon</td>\n",
       "        <td>89 Jefferson Way Suite 2</td>\n",
       "        <td>Portland</td>\n",
       "        <td>97201</td>\n",
       "        <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>78</td>\n",
       "        <td>The Cracker Box</td>\n",
       "        <td>Liu Wong</td>\n",
       "        <td>55 Grizzly Peak Rd.</td>\n",
       "        <td>Butte</td>\n",
       "        <td>59801</td>\n",
       "        <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>81</td>\n",
       "        <td>Tradição Hipermercados</td>\n",
       "        <td>Anabela Domingues</td>\n",
       "        <td>Av. Inês de Castro, 414</td>\n",
       "        <td>São Paulo</td>\n",
       "        <td>05634-030</td>\n",
       "        <td>Brazil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>82</td>\n",
       "        <td>Trail&#x27;s Head Gourmet Provisioners</td>\n",
       "        <td>Helvetius Nagy</td>\n",
       "        <td>722 DaVinci Blvd.</td>\n",
       "        <td>Kirkland</td>\n",
       "        <td>98034</td>\n",
       "        <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>88</td>\n",
       "        <td>Wellington Importadora</td>\n",
       "        <td>Paula Parente</td>\n",
       "        <td>Rua do Mercado, 12</td>\n",
       "        <td>Resende</td>\n",
       "        <td>08737-363</td>\n",
       "        <td>Brazil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>89</td>\n",
       "        <td>White Clover Markets</td>\n",
       "        <td>Karl Jablonski</td>\n",
       "        <td>305 - 14th Ave. S. Suite 3B</td>\n",
       "        <td>Seattle</td>\n",
       "        <td>98128</td>\n",
       "        <td>USA</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(15, 'Comércio Mineiro', 'Pedro Afonso', 'Av. dos Lusíadas, 23', 'São Paulo', '05432-043', 'Brazil'),\n",
       " (21, 'Familia Arquibaldo', 'Aria Cruz', 'Rua Orós, 92', 'São Paulo', '05442-030', 'Brazil'),\n",
       " (31, 'Gourmet Lanchonetes', 'André Fonseca', 'Av. Brasil, 442', 'Campinas', '04876-786', 'Brazil'),\n",
       " (32, 'Great Lakes Food Market', 'Howard Snyder', '2732 Baker Blvd.', 'Eugene', '97403', 'USA'),\n",
       " (34, 'Hanari Carnes', 'Mario Pontes', 'Rua do Paço, 67', 'Rio de Janeiro', '05454-876', 'Brazil'),\n",
       " (36, 'Hungry Coyote Import Store', 'Yoshi Latimer', 'City Center Plaza 516 Main St.', 'Elgin', '97827', 'USA'),\n",
       " (43, 'Lazy K Kountry Store', 'John Steel', '12 Orchestra Terrace', 'Walla Walla', '99362', 'USA'),\n",
       " (45, \"Let's Stop N Shop\", 'Jaime Yorres', '87 Polk St. Suite 5', 'San Francisco', '94117', 'USA'),\n",
       " (48, 'Lonesome Pine Restaurant', 'Fran Wilson', '89 Chiaroscuro Rd.', 'Portland', '97219', 'USA'),\n",
       " (55, 'Old World Delicatessen', 'Rene Phillips', '2743 Bering St.', 'Anchorage', '99508', 'USA'),\n",
       " (61, 'Que Delícia', 'Bernardo Batista', 'Rua da Panificadora, 12', 'Rio de Janeiro', '02389-673', 'Brazil'),\n",
       " (62, 'Queen Cozinha', 'Lúcia Carvalho', 'Alameda dos Canàrios, 891', 'São Paulo', '05487-020', 'Brazil'),\n",
       " (65, 'Rattlesnake Canyon Grocery', 'Paula Wilson', '2817 Milton Dr.', 'Albuquerque', '87110', 'USA'),\n",
       " (67, 'Ricardo Adocicados', 'Janete Limeira', 'Av. Copacabana, 267', 'Rio de Janeiro', '02389-890', 'Brazil'),\n",
       " (71, 'Save-a-lot Markets', 'Jose Pavarotti', '187 Suffolk Ln.', 'Boise', '83720', 'USA'),\n",
       " (75, 'Split Rail Beer & Ale', 'Art Braunschweiger', 'P.O. Box 555', 'Lander', '82520', 'USA'),\n",
       " (77, 'The Big Cheese', 'Liz Nixon', '89 Jefferson Way Suite 2', 'Portland', '97201', 'USA'),\n",
       " (78, 'The Cracker Box', 'Liu Wong', '55 Grizzly Peak Rd.', 'Butte', '59801', 'USA'),\n",
       " (81, 'Tradição Hipermercados', 'Anabela Domingues', 'Av. Inês de Castro, 414', 'São Paulo', '05634-030', 'Brazil'),\n",
       " (82, \"Trail's Head Gourmet Provisioners\", 'Helvetius Nagy', '722 DaVinci Blvd.', 'Kirkland', '98034', 'USA'),\n",
       " (88, 'Wellington Importadora', 'Paula Parente', 'Rua do Mercado, 12', 'Resende', '08737-363', 'Brazil'),\n",
       " (89, 'White Clover Markets', 'Karl Jablonski', '305 - 14th Ave. S. Suite 3B', 'Seattle', '98128', 'USA')]"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * \n",
    "FROM Customers \n",
    "WHERE Country IN (\"Brazil\" , \"USA\");"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f8e2e4c9-48fa-4e75-a560-65eee82cd97d",
   "metadata": {},
   "source": [
    "**What are the names of the customer(s) living in Germany-Berlin?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "73fb9a1f-2dea-4b13-9543-5358950e5fd3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>CustomerName</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Alfreds Futterkiste</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Alfreds Futterkiste',)]"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT CustomerName \n",
    "FROM Customers \n",
    "WHERE Country = \"Germany\" AND City= \"Berlin\";"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "168bda3e-52fc-4a64-9eb8-58c2df60ebe2",
   "metadata": {},
   "source": [
    "**What are the names and prices of the products whose price is between 40 and 90?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "30c98a36-baa2-451e-8365-c09eff587f0f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>ProductName</th>\n",
       "        <th>Price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Northwoods Cranberry Sauce</td>\n",
       "        <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Genen Shouyu</td>\n",
       "        <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Carnarvon Tigers</td>\n",
       "        <td>62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Teatime Chocolate Biscuits</td>\n",
       "        <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sir Rodney&#x27;s Marmalade</td>\n",
       "        <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Guaraná Fantástica</td>\n",
       "        <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Schoggi Schokolade</td>\n",
       "        <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Rössle Sauerkraut</td>\n",
       "        <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Geitost</td>\n",
       "        <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Boston Crab Meat</td>\n",
       "        <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ipoh Coffee</td>\n",
       "        <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Røgede sild</td>\n",
       "        <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Zaanse koeken</td>\n",
       "        <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Manjimup Dried Apples</td>\n",
       "        <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ravioli Angelo</td>\n",
       "        <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Raclette Courdavault</td>\n",
       "        <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sirop d&#x27;érable</td>\n",
       "        <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Tarte au sucre</td>\n",
       "        <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Vegie-spread</td>\n",
       "        <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Louisiana Fiery Hot Pepper Sauce</td>\n",
       "        <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Scottish Longbreads</td>\n",
       "        <td>45</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Northwoods Cranberry Sauce', 40),\n",
       " ('Genen Shouyu', 43),\n",
       " ('Carnarvon Tigers', 62),\n",
       " ('Teatime Chocolate Biscuits', 43),\n",
       " (\"Sir Rodney's Marmalade\", 81),\n",
       " ('Guaraná Fantástica', 43),\n",
       " ('Schoggi Schokolade', 43),\n",
       " ('Rössle Sauerkraut', 45),\n",
       " ('Geitost', 43),\n",
       " ('Boston Crab Meat', 43),\n",
       " ('Ipoh Coffee', 46),\n",
       " ('Røgede sild', 43),\n",
       " ('Zaanse koeken', 43),\n",
       " ('Manjimup Dried Apples', 53),\n",
       " ('Ravioli Angelo', 43),\n",
       " ('Raclette Courdavault', 55),\n",
       " (\"Sirop d'érable\", 43),\n",
       " ('Tarte au sucre', 49),\n",
       " ('Vegie-spread', 43),\n",
       " ('Louisiana Fiery Hot Pepper Sauce', 43),\n",
       " ('Scottish Longbreads', 45)]"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT ProductName, Price \n",
    "FROM Products \n",
    "WHERE PRICE BETWEEN 40 AND 90;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e04411bd-02b1-4d21-8f2f-034490b40566",
   "metadata": {},
   "source": [
    "**What are the names of the CategoryIDs in the Products table?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "e7bbb4cc-871e-4347-84ff-20d8da63f27c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Category_ID</th>\n",
       "        <th>Category_Name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Beverages</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Beverages</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Condiments</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Condiments</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Condiments</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Condiments</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7</td>\n",
       "        <td>Produce</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Condiments</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6</td>\n",
       "        <td>Meat/Poultry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>Seafood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>Dairy Products</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>Dairy Products</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>Seafood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7</td>\n",
       "        <td>Produce</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Condiments</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Confections</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6</td>\n",
       "        <td>Meat/Poultry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>Seafood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Confections</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Confections</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Confections</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>Grains/Cereals</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>Grains/Cereals</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Beverages</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Confections</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Confections</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Confections</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7</td>\n",
       "        <td>Produce</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6</td>\n",
       "        <td>Meat/Poultry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>Seafood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>Dairy Products</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>Dairy Products</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>Dairy Products</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Beverages</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Beverages</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>Seafood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>Seafood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Beverages</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Beverages</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>Seafood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>Seafood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>Grains/Cereals</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Beverages</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Condiments</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>Seafood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>Seafood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Confections</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Confections</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Confections</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Confections</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7</td>\n",
       "        <td>Produce</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>Grains/Cereals</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6</td>\n",
       "        <td>Meat/Poultry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6</td>\n",
       "        <td>Meat/Poultry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6</td>\n",
       "        <td>Meat/Poultry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>Grains/Cereals</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>Grains/Cereals</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>Seafood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>Dairy Products</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>Dairy Products</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Condiments</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Confections</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Condiments</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>Grains/Cereals</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Condiments</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Condiments</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Beverages</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Confections</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>Dairy Products</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Beverages</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>Dairy Products</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>Dairy Products</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>Seafood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7</td>\n",
       "        <td>Produce</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Beverages</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Beverages</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Condiments</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'Beverages'),\n",
       " (1, 'Beverages'),\n",
       " (2, 'Condiments'),\n",
       " (2, 'Condiments'),\n",
       " (2, 'Condiments'),\n",
       " (2, 'Condiments'),\n",
       " (7, 'Produce'),\n",
       " (2, 'Condiments'),\n",
       " (6, 'Meat/Poultry'),\n",
       " (8, 'Seafood'),\n",
       " (4, 'Dairy Products'),\n",
       " (4, 'Dairy Products'),\n",
       " (8, 'Seafood'),\n",
       " (7, 'Produce'),\n",
       " (2, 'Condiments'),\n",
       " (3, 'Confections'),\n",
       " (6, 'Meat/Poultry'),\n",
       " (8, 'Seafood'),\n",
       " (3, 'Confections'),\n",
       " (3, 'Confections'),\n",
       " (3, 'Confections'),\n",
       " (5, 'Grains/Cereals'),\n",
       " (5, 'Grains/Cereals'),\n",
       " (1, 'Beverages'),\n",
       " (3, 'Confections'),\n",
       " (3, 'Confections'),\n",
       " (3, 'Confections'),\n",
       " (7, 'Produce'),\n",
       " (6, 'Meat/Poultry'),\n",
       " (8, 'Seafood'),\n",
       " (4, 'Dairy Products'),\n",
       " (4, 'Dairy Products'),\n",
       " (4, 'Dairy Products'),\n",
       " (1, 'Beverages'),\n",
       " (1, 'Beverages'),\n",
       " (8, 'Seafood'),\n",
       " (8, 'Seafood'),\n",
       " (1, 'Beverages'),\n",
       " (1, 'Beverages'),\n",
       " (8, 'Seafood'),\n",
       " (8, 'Seafood'),\n",
       " (5, 'Grains/Cereals'),\n",
       " (1, 'Beverages'),\n",
       " (2, 'Condiments'),\n",
       " (8, 'Seafood'),\n",
       " (8, 'Seafood'),\n",
       " (3, 'Confections'),\n",
       " (3, 'Confections'),\n",
       " (3, 'Confections'),\n",
       " (3, 'Confections'),\n",
       " (7, 'Produce'),\n",
       " (5, 'Grains/Cereals'),\n",
       " (6, 'Meat/Poultry'),\n",
       " (6, 'Meat/Poultry'),\n",
       " (6, 'Meat/Poultry'),\n",
       " (5, 'Grains/Cereals'),\n",
       " (5, 'Grains/Cereals'),\n",
       " (8, 'Seafood'),\n",
       " (4, 'Dairy Products'),\n",
       " (4, 'Dairy Products'),\n",
       " (2, 'Condiments'),\n",
       " (3, 'Confections'),\n",
       " (2, 'Condiments'),\n",
       " (5, 'Grains/Cereals'),\n",
       " (2, 'Condiments'),\n",
       " (2, 'Condiments'),\n",
       " (1, 'Beverages'),\n",
       " (3, 'Confections'),\n",
       " (4, 'Dairy Products'),\n",
       " (1, 'Beverages'),\n",
       " (4, 'Dairy Products'),\n",
       " (4, 'Dairy Products'),\n",
       " (8, 'Seafood'),\n",
       " (7, 'Produce'),\n",
       " (1, 'Beverages'),\n",
       " (1, 'Beverages'),\n",
       " (2, 'Condiments')]"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT PR.CategoryID AS Category_ID, CA.CategoryName AS Category_Name\n",
    "FROM Products AS PR\n",
    "LEFT JOIN Categories AS CA ON\n",
    "PR.CategoryID = CA.CategoryID;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "12800b4f-1842-4125-bb3e-1944941e4a5b",
   "metadata": {},
   "source": [
    "**What is the mean of the beverages products?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "ece469ae-fadf-4099-a1ea-9fa591f174c0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Mean</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>42.75</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(42.75,)]"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT AVG(Price) as Mean\n",
    "FROM Products \n",
    "LEFT JOIN Categories on Products.CategoryID = categories.CategoryID \n",
    "WHERE CategoryName = \"Beverages\";"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d108dd46-cbad-4d01-b060-cbc948251f57",
   "metadata": {},
   "source": [
    "**Finding the total earnings earned by customers living in the USA.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "275f4e87-2c1f-4741-8b80-7f809bf0af5f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Total</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3762</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(3762,)]"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT SUM(p.Price) as Total\n",
    "FROM Orders o \n",
    "LEFT JOIN OrderDetails od on o.OrderID = od.OrderID \n",
    "LEFT JOIN Products p on od.ProductID = p.ProductID \n",
    "LEFT JOIN Customers cu on o.CustomerID = cu.CustomerID where cu.Country = \"USA\";"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7951a35c-62cf-4ee3-a659-5f30b2ff95af",
   "metadata": {},
   "source": [
    "**Finding the names of the CategoryIDs in the Products table and showing the mean product price per category.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "1a3dcf56-7a1f-4330-bd00-a7fffeffefd6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Mean</th>\n",
       "        <th>Categories</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>42.75</td>\n",
       "        <td>Beverages</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>28.25</td>\n",
       "        <td>Condiments</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>33.76923076923077</td>\n",
       "        <td>Confections</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>74.9</td>\n",
       "        <td>Dairy Products</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>23.571428571428573</td>\n",
       "        <td>Grains/Cereals</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>55.166666666666664</td>\n",
       "        <td>Meat/Poultry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>32.2</td>\n",
       "        <td>Produce</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>26.5</td>\n",
       "        <td>Seafood</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(42.75, 'Beverages'),\n",
       " (28.25, 'Condiments'),\n",
       " (33.76923076923077, 'Confections'),\n",
       " (74.9, 'Dairy Products'),\n",
       " (23.571428571428573, 'Grains/Cereals'),\n",
       " (55.166666666666664, 'Meat/Poultry'),\n",
       " (32.2, 'Produce'),\n",
       " (26.5, 'Seafood')]"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT AVG(Price) as Mean, CategoryName as Categories \n",
    "FROM Products \n",
    "LEFT JOIN Categories on Products.CategoryID = Categories.CategoryID \n",
    "GROUP BY CategoryName;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "781fa39c-22e7-4f2f-8315-892e873af55d",
   "metadata": {},
   "source": [
    "**Listing the employees according to their total sales.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "38e91d9e-c589-4e14-a40c-bf49f0b2fe6f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>FirstName</th>\n",
       "        <th>TotalSales</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Margaret</td>\n",
       "        <td>5515</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nancy</td>\n",
       "        <td>4353</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Janet</td>\n",
       "        <td>3048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Michael</td>\n",
       "        <td>2981</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Laura</td>\n",
       "        <td>2380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Robert</td>\n",
       "        <td>1852</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Andrew</td>\n",
       "        <td>1550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Steven</td>\n",
       "        <td>1408</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Anne</td>\n",
       "        <td>520</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Margaret', 5515),\n",
       " ('Nancy', 4353),\n",
       " ('Janet', 3048),\n",
       " ('Michael', 2981),\n",
       " ('Laura', 2380),\n",
       " ('Robert', 1852),\n",
       " ('Andrew', 1550),\n",
       " ('Steven', 1408),\n",
       " ('Anne', 520)]"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT em.FirstName, SUM(p.Price) TotalSales \n",
    "FROM Orders o \n",
    "LEFT JOIN OrderDetails od on o.OrderID = od.OrderID \n",
    "LEFT JOIN Products p on od.ProductID = p.ProductID \n",
    "LEFT JOIN Employees em on o.EmployeeID = em.EmployeeID \n",
    "GROUP BY o.EmployeeID \n",
    "ORDER BY TotalSales DESC;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "840bf37b-341a-4550-a733-aa600bbe1be7",
   "metadata": {},
   "source": [
    "**What are the average prices for orders from Germany according to categories?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "ead3780c-b2ee-4464-b0af-9df2fd2d8343",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>CategoryName</th>\n",
       "        <th>Mean</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Beverages</td>\n",
       "        <td>20.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Condiments</td>\n",
       "        <td>34.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Confections</td>\n",
       "        <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Dairy Products</td>\n",
       "        <td>151.64705882352942</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Grains/Cereals</td>\n",
       "        <td>22.142857142857142</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Meat/Poultry</td>\n",
       "        <td>30.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Produce</td>\n",
       "        <td>30.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Seafood</td>\n",
       "        <td>27.09090909090909</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Beverages', 20.5),\n",
       " ('Condiments', 34.0),\n",
       " ('Confections', 40.0),\n",
       " ('Dairy Products', 151.64705882352942),\n",
       " ('Grains/Cereals', 22.142857142857142),\n",
       " ('Meat/Poultry', 30.2),\n",
       " ('Produce', 30.75),\n",
       " ('Seafood', 27.09090909090909)]"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT ca.CategoryName, AVG(p.Price)  Mean\n",
    "FROM Orders o \n",
    "LEFT JOIN OrderDetails od on o.OrderID = od.OrderID \n",
    "LEFT JOIN Products p on od.ProductID = p.ProductID \n",
    "LEFT JOIN Categories ca on p.CategoryID = ca.CategoryID \n",
    "LEFT JOIN Customers cu on o.CustomerID = cu.CustomerID \n",
    "WHERE cu.Country = \"Germany\" GROUP BY CategoryName;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5d7e9533-3111-44e0-ac35-e9e7026e9c90",
   "metadata": {},
   "source": [
    "**What is the average price of orders placed in June, July, and August?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "5ab41e39-2419-441c-a7be-98be38724705",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Mean</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>39.71891891891892</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(39.71891891891892,)]"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT AVG(Products.Price) Mean\n",
    "FROM Products \n",
    "LEFT JOIN OrderDetails ON OrderDetails.ProductID = Products.ProductID \n",
    "LEFT JOIN Orders ON OrderDetails.OrderID = Orders.OrderID \n",
    "WHERE OrderDate LIKE \"%07%\" OR OrderDate LIKE \"%08%\" OR OrderDate LIKE \"%09%\";"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8fbc8aaf-e216-430d-aa3d-5e4bdc716678",
   "metadata": {},
   "source": [
    "**Finding the maximum of customers' orders for 1997 and listing them in descending order with the names of the customers.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "a50d08c1-f312-425c-96af-977271957350",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>CustomerName</th>\n",
       "        <th>MAX</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Gourmet Lanchonetes</td>\n",
       "        <td>435</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Simons bistro</td>\n",
       "        <td>263</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mère Paillarde</td>\n",
       "        <td>263</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Save-a-lot Markets</td>\n",
       "        <td>123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Eastern Connection</td>\n",
       "        <td>123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Wellington Importadora</td>\n",
       "        <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ernst Handel</td>\n",
       "        <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bottom-Dollar Marketse</td>\n",
       "        <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>La maison d&#x27;Asie</td>\n",
       "        <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Folies gourmandes</td>\n",
       "        <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Reggiani Caseifici</td>\n",
       "        <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Queen Cozinha</td>\n",
       "        <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Wartian Herkku</td>\n",
       "        <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Toms Spezialitäten</td>\n",
       "        <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Rattlesnake Canyon Grocery</td>\n",
       "        <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Que Delícia</td>\n",
       "        <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>QUICK-Stop</td>\n",
       "        <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Old World Delicatessen</td>\n",
       "        <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Hungry Owl All-Night Grocers</td>\n",
       "        <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Hungry Coyote Import Store</td>\n",
       "        <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Familia Arquibaldo</td>\n",
       "        <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Princesa Isabel Vinhoss</td>\n",
       "        <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Galería del gastrónomo</td>\n",
       "        <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Blondel père et fils</td>\n",
       "        <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Richter Supermarkt</td>\n",
       "        <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ottilies Käseladen</td>\n",
       "        <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Consolidated Holdings</td>\n",
       "        <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Split Rail Beer &amp; Ale</td>\n",
       "        <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Magazzini Alimentari Riuniti</td>\n",
       "        <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Franchi S.p.A.</td>\n",
       "        <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Piccolo und mehr</td>\n",
       "        <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Océano Atlántico Ltda.</td>\n",
       "        <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Folk och fä HB</td>\n",
       "        <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LINO-Delicateses</td>\n",
       "        <td>10</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Gourmet Lanchonetes', 435),\n",
       " ('Simons bistro', 263),\n",
       " ('Mère Paillarde', 263),\n",
       " ('Save-a-lot Markets', 123),\n",
       " ('Eastern Connection', 123),\n",
       " ('Wellington Importadora', 97),\n",
       " ('Ernst Handel', 55),\n",
       " ('Bottom-Dollar Marketse', 55),\n",
       " (\"La maison d'Asie\", 49),\n",
       " ('Folies gourmandes', 49),\n",
       " ('Reggiani Caseifici', 45),\n",
       " ('Queen Cozinha', 45),\n",
       " ('Wartian Herkku', 43),\n",
       " ('Toms Spezialitäten', 43),\n",
       " ('Rattlesnake Canyon Grocery', 43),\n",
       " ('Que Delícia', 43),\n",
       " ('QUICK-Stop', 43),\n",
       " ('Old World Delicatessen', 43),\n",
       " ('Hungry Owl All-Night Grocers', 43),\n",
       " ('Hungry Coyote Import Store', 43),\n",
       " ('Familia Arquibaldo', 43),\n",
       " ('Princesa Isabel Vinhoss', 38),\n",
       " ('Galería del gastrónomo', 38),\n",
       " ('Blondel père et fils', 38),\n",
       " ('Richter Supermarkt', 36),\n",
       " ('Ottilies Käseladen', 36),\n",
       " ('Consolidated Holdings', 34),\n",
       " ('Split Rail Beer & Ale', 31),\n",
       " ('Magazzini Alimentari Riuniti', 31),\n",
       " ('Franchi S.p.A.', 31),\n",
       " ('Piccolo und mehr', 23),\n",
       " ('Océano Atlántico Ltda.', 23),\n",
       " ('Folk och fä HB', 21),\n",
       " ('LINO-Delicateses', 10)]"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT CustomerName, MAX(Price) MAX \n",
    "FROM Products \n",
    "LEFT JOIN OrderDetails ON Products.ProductID = OrderDetails.ProductID \n",
    "LEFT JOIN Orders ON OrderDetails.OrderID = Orders.OrderID \n",
    "LEFT JOIN Customers ON Customers.CustomerID = Orders.CustomerID \n",
    "WHERE OrderDate LIKE \"%1997%\" \n",
    "GROUP BY CustomerName ORDER BY MAX DESC;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4c9d41ba-cb72-4e94-8da3-285d16f47a11",
   "metadata": {},
   "source": [
    "**Listing the employees who received orders with the order year 1997 according to the number of orders received.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "308d8a8d-7302-4c23-9124-9ef694344c15",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///sqlite.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>n</th>\n",
       "        <th>FirstName</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>13</td>\n",
       "        <td>Janet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9</td>\n",
       "        <td>Margaret</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>Laura</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>Andrew</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Robert</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Nancy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Michael</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Anne</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(13, 'Janet'),\n",
       " (9, 'Margaret'),\n",
       " (8, 'Laura'),\n",
       " (4, 'Andrew'),\n",
       " (3, 'Robert'),\n",
       " (3, 'Nancy'),\n",
       " (3, 'Michael'),\n",
       " (1, 'Anne')]"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT COUNT(*) n, em.FirstName \n",
    "FROM Orders \n",
    "LEFT JOIN Employees em on Orders.EmployeeID = em.EmployeeID \n",
    "WHERE OrderDate like \"%1997%\" \n",
    "GROUP BY em.FirstName \n",
    "ORDER by n DESC;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a072468e-285d-43ef-a1c8-a7503b90af11",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "886c1d62-a842-4e6b-9ea7-2d76df3ad840",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3243edda-d62e-4daa-b97d-3f128ad62c52",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1f541415-9e00-430c-b81c-3474b6f19f3f",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}