notebooks/SimpleQueries.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Explore schemas and query tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import datajoint as dj"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[2022-09-20 15:57:48,832][INFO]: Connecting dimitri@db.ust-data-sci.net:3306\n",
      "[2022-09-20 15:57:49,638][INFO]: Connected dimitri@db.ust-data-sci.net:3306\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['shared_sales',\n",
       " 'dimitri_enroll',\n",
       " 'dimitri_language2',\n",
       " 'dimitri_university',\n",
       " 'dimitri_language',\n",
       " 'dimitri_test',\n",
       " 'shared_sakila']"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dj.list_schemas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "schema = dj.Schema('shared_sales')\n",
    "#schema = dj.Schema('shared_sakila')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create classes for working with tables\n",
    "schema.spawn_missing_classes()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/svg+xml": [
       "<svg xmlns=\"http://www.w3.org/2000/svg\" xmlns:xlink=\"http://www.w3.org/1999/xlink\" width=\"311pt\" height=\"185pt\" viewBox=\"0.00 0.00 310.50 185.00\">\n",
       "<g id=\"graph0\" class=\"graph\" transform=\"scale(1 1) rotate(0) translate(4 181)\">\n",
       "<polygon fill=\"white\" stroke=\"none\" points=\"-4,4 -4,-181 306.5,-181 306.5,4 -4,4\"/>\n",
       "<!-- Product -->\n",
       "<g id=\"node1\" class=\"node\">\n",
       "<title>Product</title>\n",
       "<g id=\"a_node1\"><a xlink:title=\"product_number       \r",
       "------------------------------\r",
       "product_name         \r",
       "product_description=&quot;&quot; \r",
       "retail_price         \r",
       "quantity_on_hand     \r",
       "→ Category\r",
       "\">\n",
       "<polygon fill=\"#00ff00\" fill-opacity=\"0.188235\" stroke=\"#00ff00\" stroke-opacity=\"0.188235\" points=\"229,-106 171,-106 171,-71 229,-71 229,-106\"/>\n",
       "<text text-anchor=\"start\" x=\"179\" y=\"-86.4\" font-family=\"arial\" text-decoration=\"underline\" font-size=\"12.00\" fill=\"darkgreen\">Product</text>\n",
       "</a>\n",
       "</g>\n",
       "</g>\n",
       "<!-- Order.Item -->\n",
       "<g id=\"node4\" class=\"node\">\n",
       "<title>Order.Item</title>\n",
       "<g id=\"a_node4\"><a xlink:title=\"→ Order\r",
       "→ Product\r",
       "------------------------------\r",
       "quoted_price         \r",
       "quantity_ordered=0   \r",
       "\">\n",
       "<polygon fill=\"none\" stroke=\"none\" points=\"153.5,-27 90.5,-27 90.5,-8 153.5,-8 153.5,-27\"/>\n",
       "<text text-anchor=\"middle\" x=\"122\" y=\"-15\" font-family=\"arial\" font-size=\"10.00\">Order.Item</text>\n",
       "</a>\n",
       "</g>\n",
       "</g>\n",
       "<!-- Product&#45;&gt;Order.Item -->\n",
       "<g id=\"edge1\" class=\"edge\">\n",
       "<title>Product-&gt;Order.Item</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" stroke-width=\"0.75\" stroke-opacity=\"0.250980\" d=\"M181.12,-70.8C165.61,-57.08 144.25,-38.19 131.91,-27.27\"/>\n",
       "</g>\n",
       "<!-- ProductVendor -->\n",
       "<g id=\"node6\" class=\"node\">\n",
       "<title>ProductVendor</title>\n",
       "<g id=\"a_node6\"><a xlink:title=\"→ Product\r",
       "→ Vendor\r",
       "------------------------------\r",
       "wholesale_price      \r",
       "days_to_deliver      \r",
       "\">\n",
       "<polygon fill=\"#00ff00\" fill-opacity=\"0.188235\" stroke=\"#00ff00\" stroke-opacity=\"0.188235\" points=\"292,-35 196,-35 196,0 292,0 292,-35\"/>\n",
       "<text text-anchor=\"middle\" x=\"244\" y=\"-14.4\" font-family=\"arial\" font-size=\"12.00\" fill=\"darkgreen\">ProductVendor</text>\n",
       "</a>\n",
       "</g>\n",
       "</g>\n",
       "<!-- Product&#45;&gt;ProductVendor -->\n",
       "<g id=\"edge2\" class=\"edge\">\n",
       "<title>Product-&gt;ProductVendor</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" stroke-width=\"0.75\" stroke-opacity=\"0.250980\" d=\"M210.65,-70.8C217.57,-59.95 226.55,-45.87 233.45,-35.05\"/>\n",
       "</g>\n",
       "<!-- Customer -->\n",
       "<g id=\"node2\" class=\"node\">\n",
       "<title>Customer</title>\n",
       "<g id=\"a_node2\"><a xlink:title=\"customer_id          \r",
       "------------------------------\r",
       "customer_first_name  \r",
       "customer_last_name   \r",
       "customer_street_address=&quot;&quot; \r",
       "customer_city=&quot;&quot;     \r",
       "customer_state=&quot;&quot;    \r",
       "customer_zipcode=&quot;&quot;  \r",
       "customer_area_code=null \r",
       "customer_phone=null  \r",
       "INDEX (customer_area_code)\r",
       "INDEX (customer_zipcode)\r",
       "\">\n",
       "<polygon fill=\"#00ff00\" fill-opacity=\"0.188235\" stroke=\"#00ff00\" stroke-opacity=\"0.188235\" points=\"68,-177 0,-177 0,-142 68,-142 68,-177\"/>\n",
       "<text text-anchor=\"start\" x=\"8\" y=\"-157.4\" font-family=\"arial\" text-decoration=\"underline\" font-size=\"12.00\" fill=\"darkgreen\">Customer</text>\n",
       "</a>\n",
       "</g>\n",
       "</g>\n",
       "<!-- Order -->\n",
       "<g id=\"node8\" class=\"node\">\n",
       "<title>Order</title>\n",
       "<g id=\"a_node8\"><a xlink:title=\"order_number         \r",
       "------------------------------\r",
       "order_date           \r",
       "ship_date            \r",
       "→ Customer\r",
       "→ Employee\r",
       "\">\n",
       "<polygon fill=\"#00ff00\" fill-opacity=\"0.188235\" stroke=\"#00ff00\" stroke-opacity=\"0.188235\" points=\"145.5,-106 98.5,-106 98.5,-71 145.5,-71 145.5,-106\"/>\n",
       "<text text-anchor=\"start\" x=\"106.5\" y=\"-86.4\" font-family=\"arial\" text-decoration=\"underline\" font-size=\"12.00\" fill=\"darkgreen\">Order</text>\n",
       "</a>\n",
       "</g>\n",
       "</g>\n",
       "<!-- Customer&#45;&gt;Order -->\n",
       "<g id=\"edge3\" class=\"edge\">\n",
       "<title>Customer-&gt;Order</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" stroke-width=\"0.75\" stroke-dasharray=\"5,2\" stroke-opacity=\"0.250980\" d=\"M55.3,-141.8C69.14,-130.95 87.1,-116.87 100.89,-106.05\"/>\n",
       "</g>\n",
       "<!-- Category -->\n",
       "<g id=\"node3\" class=\"node\">\n",
       "<title>Category</title>\n",
       "<g id=\"a_node3\"><a xlink:title=\"category_id=0        \r",
       "------------------------------\r",
       "category_description=&quot;&quot; \r",
       "\">\n",
       "<polygon fill=\"#00ff00\" fill-opacity=\"0.188235\" stroke=\"#00ff00\" stroke-opacity=\"0.188235\" points=\"240.5,-177 175.5,-177 175.5,-142 240.5,-142 240.5,-177\"/>\n",
       "<text text-anchor=\"start\" x=\"183.5\" y=\"-157.4\" font-family=\"arial\" text-decoration=\"underline\" font-size=\"12.00\" fill=\"darkgreen\">Category</text>\n",
       "</a>\n",
       "</g>\n",
       "</g>\n",
       "<!-- Category&#45;&gt;Product -->\n",
       "<g id=\"edge4\" class=\"edge\">\n",
       "<title>Category-&gt;Product</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" stroke-width=\"0.75\" stroke-dasharray=\"5,2\" stroke-opacity=\"0.250980\" d=\"M206.06,-141.8C204.81,-130.95 203.17,-116.87 201.92,-106.05\"/>\n",
       "</g>\n",
       "<!-- Employee -->\n",
       "<g id=\"node5\" class=\"node\">\n",
       "<title>Employee</title>\n",
       "<g id=\"a_node5\"><a xlink:title=\"employee_id          \r",
       "------------------------------\r",
       "employee_first_name  \r",
       "employee_last_name   \r",
       "employee_street_address=&quot;&quot; \r",
       "employee_city=&quot;&quot;     \r",
       "employee_state=&quot;&quot;    \r",
       "employee_zipcode=&quot;&quot;  \r",
       "employee_area_code=null \r",
       "employee_phone=&quot;&quot;    \r",
       "employee_birthdate=null \r",
       "INDEX (employee_area_code)\r",
       "INDEX (employee_zipcode)\r",
       "\">\n",
       "<polygon fill=\"#00ff00\" fill-opacity=\"0.188235\" stroke=\"#00ff00\" stroke-opacity=\"0.188235\" points=\"157.5,-177 86.5,-177 86.5,-142 157.5,-142 157.5,-177\"/>\n",
       "<text text-anchor=\"start\" x=\"94.5\" y=\"-157.4\" font-family=\"arial\" text-decoration=\"underline\" font-size=\"12.00\" fill=\"darkgreen\">Employee</text>\n",
       "</a>\n",
       "</g>\n",
       "</g>\n",
       "<!-- Employee&#45;&gt;Order -->\n",
       "<g id=\"edge5\" class=\"edge\">\n",
       "<title>Employee-&gt;Order</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" stroke-width=\"0.75\" stroke-dasharray=\"5,2\" stroke-opacity=\"0.250980\" d=\"M122,-141.8C122,-130.95 122,-116.87 122,-106.05\"/>\n",
       "</g>\n",
       "<!-- Vendor -->\n",
       "<g id=\"node7\" class=\"node\">\n",
       "<title>Vendor</title>\n",
       "<g id=\"a_node7\"><a xlink:title=\"vendor_id            \r",
       "------------------------------\r",
       "vendor_name          \r",
       "vendor_street_address \r",
       "vendor_city          \r",
       "vendor_state         \r",
       "vendor_zipcode       \r",
       "vendor_phone         \r",
       "vendor_fax=&quot;&quot;        \r",
       "vendor_webpage=''    \r",
       "vendor_email=&quot;&quot;      \r",
       "\">\n",
       "<polygon fill=\"#00ff00\" fill-opacity=\"0.188235\" stroke=\"#00ff00\" stroke-opacity=\"0.188235\" points=\"302.5,-106 247.5,-106 247.5,-71 302.5,-71 302.5,-106\"/>\n",
       "<text text-anchor=\"start\" x=\"255.5\" y=\"-86.4\" font-family=\"arial\" text-decoration=\"underline\" font-size=\"12.00\" fill=\"darkgreen\">Vendor</text>\n",
       "</a>\n",
       "</g>\n",
       "</g>\n",
       "<!-- Vendor&#45;&gt;ProductVendor -->\n",
       "<g id=\"edge6\" class=\"edge\">\n",
       "<title>Vendor-&gt;ProductVendor</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" stroke-width=\"0.75\" stroke-opacity=\"0.250980\" d=\"M267.5,-70.8C262.62,-59.95 256.3,-45.87 251.44,-35.05\"/>\n",
       "</g>\n",
       "<!-- Order&#45;&gt;Order.Item -->\n",
       "<g id=\"edge7\" class=\"edge\">\n",
       "<title>Order-&gt;Order.Item</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" stroke-width=\"0.75\" stroke-opacity=\"0.250980\" d=\"M122,-70.8C122,-57.08 122,-38.19 122,-27.27\"/>\n",
       "</g>\n",
       "</g>\n",
       "</svg>"
      ],
      "text/plain": [
       "<datajoint.diagram.Diagram at 0x7ff758d1a290>"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dj.Diagram(schema)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "    \n",
       "    <style type=\"text/css\">\n",
       "        .Relation{\n",
       "            border-collapse:collapse;\n",
       "        }\n",
       "        .Relation th{\n",
       "            background: #A0A0A0; color: #ffffff; padding:4px; border:#f0e0e0 1px solid;\n",
       "            font-weight: normal; font-family: monospace; font-size: 100%;\n",
       "        }\n",
       "        .Relation td{\n",
       "            padding:4px; border:#f0e0e0 1px solid; font-size:100%;\n",
       "        }\n",
       "        .Relation tr:nth-child(odd){\n",
       "            background: #ffffff;\n",
       "        }\n",
       "        .Relation tr:nth-child(even){\n",
       "            background: #f3f1ff;\n",
       "        }\n",
       "        /* Tooltip container */\n",
       "        .djtooltip {\n",
       "        }\n",
       "        /* Tooltip text */\n",
       "        .djtooltip .djtooltiptext {\n",
       "            visibility: hidden;\n",
       "            width: 120px;\n",
       "            background-color: black;\n",
       "            color: #fff;\n",
       "            text-align: center;\n",
       "            padding: 5px 0;\n",
       "            border-radius: 6px;\n",
       "            /* Position the tooltip text - see examples below! */\n",
       "            position: absolute;\n",
       "            z-index: 1;\n",
       "        }\n",
       "        #primary {\n",
       "            font-weight: bold;\n",
       "            color: black;\n",
       "        }\n",
       "        #nonprimary {\n",
       "            font-weight: normal;\n",
       "            color: white;\n",
       "        }\n",
       "\n",
       "        /* Show the tooltip text when you mouse over the tooltip container */\n",
       "        .djtooltip:hover .djtooltiptext {\n",
       "            visibility: visible;\n",
       "        }\n",
       "    </style>\n",
       "    \n",
       "    <b></b>\n",
       "        <div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "        <table border=\"1\" class=\"Relation\">\n",
       "            <thead> <tr style=\"text-align: right;\"> <th> <div class=\"djtooltip\">\n",
       "                            <p id=\"primary\">employee_id</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div></th><th><div class=\"djtooltip\">\n",
       "                            <p id=\"nonprimary\">employee_first_name</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div></th><th><div class=\"djtooltip\">\n",
       "                            <p id=\"nonprimary\">employee_last_name</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div></th><th><div class=\"djtooltip\">\n",
       "                            <p id=\"nonprimary\">employee_street_address</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div></th><th><div class=\"djtooltip\">\n",
       "                            <p id=\"nonprimary\">employee_city</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div></th><th><div class=\"djtooltip\">\n",
       "                            <p id=\"nonprimary\">employee_state</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div></th><th><div class=\"djtooltip\">\n",
       "                            <p id=\"nonprimary\">employee_zipcode</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div></th><th><div class=\"djtooltip\">\n",
       "                            <p id=\"nonprimary\">employee_area_code</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div></th><th><div class=\"djtooltip\">\n",
       "                            <p id=\"nonprimary\">employee_phone</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div></th><th><div class=\"djtooltip\">\n",
       "                            <p id=\"nonprimary\">employee_birthdate</p>\n",
       "                            <span class=\"djtooltiptext\"></span>\n",
       "                        </div> </th> </tr> </thead>\n",
       "            <tbody> <tr> <td>701</td>\n",
       "<td>Ann</td>\n",
       "<td>Patterson</td>\n",
       "<td>16 Maple Lane</td>\n",
       "<td>Auburn</td>\n",
       "<td>WA</td>\n",
       "<td>98002</td>\n",
       "<td>253</td>\n",
       "<td>555-2591</td>\n",
       "<td>1958-03-18</td></tr><tr><td>702</td>\n",
       "<td>Mary</td>\n",
       "<td>Thompson</td>\n",
       "<td>122 Spring River Drive</td>\n",
       "<td>Duvall</td>\n",
       "<td>WA</td>\n",
       "<td>98019</td>\n",
       "<td>425</td>\n",
       "<td>555-2516</td>\n",
       "<td>1990-06-21</td></tr><tr><td>703</td>\n",
       "<td>Matt</td>\n",
       "<td>Berg</td>\n",
       "<td>908 W. Capital Way</td>\n",
       "<td>Tacoma</td>\n",
       "<td>WA</td>\n",
       "<td>98413</td>\n",
       "<td>253</td>\n",
       "<td>555-2581</td>\n",
       "<td>1964-11-02</td></tr><tr><td>704</td>\n",
       "<td>Carol</td>\n",
       "<td>Viescas</td>\n",
       "<td>722 Moss Bay Blvd.</td>\n",
       "<td>Kirkland</td>\n",
       "<td>WA</td>\n",
       "<td>98033</td>\n",
       "<td>425</td>\n",
       "<td>555-2576</td>\n",
       "<td>1987-12-23</td></tr><tr><td>705</td>\n",
       "<td>Kirk</td>\n",
       "<td>DeGrasse</td>\n",
       "<td>455 West Palm Ave</td>\n",
       "<td>San Antonio</td>\n",
       "<td>TX</td>\n",
       "<td>78284</td>\n",
       "<td>210</td>\n",
       "<td>5552311</td>\n",
       "<td>1985-10-05</td></tr><tr><td>706</td>\n",
       "<td>David</td>\n",
       "<td>Viescas</td>\n",
       "<td>16679 NE 42nd Court</td>\n",
       "<td>Redmond</td>\n",
       "<td>WA</td>\n",
       "<td>98052</td>\n",
       "<td>425</td>\n",
       "<td>555-2661</td>\n",
       "<td>1968-01-01</td></tr><tr><td>707</td>\n",
       "<td>Kathryn</td>\n",
       "<td>Patterson</td>\n",
       "<td>554 E. Wilshire\n",
       "Apt. 2A</td>\n",
       "<td>Seattle</td>\n",
       "<td>WA</td>\n",
       "<td>98105</td>\n",
       "<td>206</td>\n",
       "<td>555-2697</td>\n",
       "<td>1982-12-12</td></tr><tr><td>708</td>\n",
       "<td>Susan</td>\n",
       "<td>McLain</td>\n",
       "<td>511 Lenora Ave</td>\n",
       "<td>Bellevue</td>\n",
       "<td>WA</td>\n",
       "<td>98006</td>\n",
       "<td>425</td>\n",
       "<td>555-2301</td>\n",
       "<td>1967-10-15</td> </tr> </tbody>\n",
       "        </table>\n",
       "        \n",
       "        <p>Total: 8</p></div>\n",
       "        "
      ],
      "text/plain": [
       "*employee_id   employee_first employee_last_ employee_stree employee_city  employee_state employee_zipco employee_area_ employee_phone employee_birth\n",
       "+------------+ +------------+ +------------+ +------------+ +------------+ +------------+ +------------+ +------------+ +------------+ +------------+\n",
       "701            Ann            Patterson      16 Maple Lane  Auburn         WA             98002          253            555-2591       1958-03-18    \n",
       "702            Mary           Thompson       122 Spring Riv Duvall         WA             98019          425            555-2516       1990-06-21    \n",
       "703            Matt           Berg           908 W. Capital Tacoma         WA             98413          253            555-2581       1964-11-02    \n",
       "704            Carol          Viescas        722 Moss Bay B Kirkland       WA             98033          425            555-2576       1987-12-23    \n",
       "705            Kirk           DeGrasse       455 West Palm  San Antonio    TX             78284          210            5552311        1985-10-05    \n",
       "706            David          Viescas        16679 NE 42nd  Redmond        WA             98052          425            555-2661       1968-01-01    \n",
       "707            Kathryn        Patterson      554 E. Wilshir Seattle        WA             98105          206            555-2697       1982-12-12    \n",
       "708            Susan          McLain         511 Lenora Ave Bellevue       WA             98006          425            555-2301       1967-10-15    \n",
       " (Total: 8)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# query a table through its class\n",
    "Employee()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Enable SQL Magic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "with open('cred.json') as f:\n",
    "    creds = json.load(f)\n",
    "\n",
    "connection_string = \"mysql://{user}:{password}@{host}\".format(**creds)\n",
    "\n",
    "import pymysql \n",
    "pymysql.install_as_MySQLdb()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql\n",
    "%config SqlMagic.autocommit=True\n",
    "%sql $connection_string"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SHOW SCHEMAS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "USE shared_sales"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SHOW TABLES"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SHOW CREATE TABLE category;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "-- show all customers from Texas \n",
    "\n",
    "SELECT *\n",
    "FROM customer\n",
    "WHERE customer_state = \"TX\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "-- show the names and addresses of all Texas customers sorted by last name \n",
    "\n",
    "SELECT CONCAT(customer_first_name, ' ', customer_last_name) AS full_name, \n",
    "    customer_street_address AS address\n",
    "  FROM customer\n",
    "  WHERE customer_state = \"TX\"\n",
    "  ORDER BY customer_last_name;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```sql\n",
    "-- show the names and addresses of all Texas customers sorted by last name \n",
    "\n",
    "SELECT CONCAT(customer_first_name, ' ', customer_last_name) AS full_name, \n",
    "    customer_street_address AS address\n",
    "  FROM customer\n",
    "  WHERE customer_state = \"TX\"\n",
    "  ORDER BY customer_last_name;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "-- show the name of the most expensive product \n",
    "-- A bad query.\n",
    "\n",
    "SELECT product_name, max(retail_price) as max_price\n",
    "FROM product"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "-- show the name of the most expensive product \n",
    "\n",
    "SELECT product_name, retail_price\n",
    "FROM product\n",
    "ORDER BY retail_price DESC\n",
    "LIMIT 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "-- show all products above 10 in quantity and discount them by 10%\n",
    "\n",
    "SELECT product_number, product_name, retail_price * 0.9 as retail_price\n",
    "FROM product\n",
    "WHERE quantity_on_hand > 10\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "-- show all orders\n",
    "\n",
    "SELECT * FROM `order`;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SHOW CREATE TABLE `order`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Same queries in DataJoint"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# show all customers from Texas \n",
    "Customer() & 'customer_state = \"TX\"'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Customer() & {'customer_state': \"TX\"}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# show the names and addresses of all Texas customers sorted by last name \n",
    "\n",
    "texas_customers = Customer() &  {'customer_state': \"TX\"}\n",
    "\n",
    "q = texas_customers.proj('customer_first_name', 'customer_last_name')\n",
    "\n",
    "q.fetch(order_by='customer_last_name', as_dict=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# show the name of the most expensive product \n",
    "Product().proj('product_name', 'retail_price').fetch(\n",
    "    'product_name', limit=1, order_by='retail_price DESC')[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# show all products above 10 in quantity and discount them by 10%\n",
    "(Product() & 'quantity_on_hand > 10').proj(\n",
    "    'product_name', retail_price='retail_price * 0.9')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Queries with subqueries in restriction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dj.Diagram(schema)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Give me all orders for Texas customers\n",
    "\n",
    "texas_customers = Customer() & {'customer_state': 'TX'}\n",
    "Order & texas_customers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT customer_id \n",
    "FROM customer\n",
    "WHERE customer_state = \"TX\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "-- orders placed by Texas customers\n",
    "\n",
    "SELECT * FROM `order`\n",
    "WHERE customer_id IN (\n",
    "    SELECT customer_id \n",
    "    FROM customer\n",
    "    WHERE customer_state = \"TX\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Product()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql \n",
    "-- select all orders containing product_number 1\n",
    "\n",
    "SELECT * FROM `order` WHERE order_number IN (\n",
    "SELECT order_number FROM `order__item` WHERE product_number = 1)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Order & (Order.Item & 'product_number=1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Show all customers who have never placed an order"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT * FROM customer WHERE customer_id NOT IN \n",
    "(SELECT customer_id FROM `order`)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Customer - Order"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql \n",
    "-- products with the word \"Bike\" in their names\n",
    "SELECT * FROM product WHERE product_name LIKE \"%Bike%\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "benv",
   "language": "python",
   "name": "benv"
  },
  "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.10.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}