Section 06 Subqueries/24 MySQL Subquery.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "f813ba0f-442a-4a76-9600-07adacbf7307",
   "metadata": {
    "tags": [],
    "toc-hr-collapsed": true
   },
   "source": [
    "## **[MySQL Subquery](https://www.mysqltutorial.org/mysql-subquery/)**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4bf77cec-8e27-486c-b7b5-1496f4fd4da0",
   "metadata": {},
   "source": [
    "Use the MySQL subquery to write complex queries and explain the correlated subquery concept."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "68dbc639-bc5e-4729-b61d-fc915e8b9fb0",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import re\n",
    "import matplotlib.pyplot as plt\n",
    "import matplotlib.image as mpimg\n",
    "from IPython.display import Image, SVG\n",
    "\n",
    "from sqlalchemy_utils import database_exists, create_database\n",
    "from sqlalchemy import create_engine, inspect, MetaData, text\n",
    "from sqlalchemy_schemadisplay import create_schema_graph\n",
    "import pymysql\n",
    "\n",
    "pd.set_option(\n",
    "    'display.max_columns', None,\n",
    "    'expand_frame_repr', True,\n",
    "    'display.max_colwidth', None,\n",
    "    'display.max_rows', 10,\n",
    ")\n",
    "\n",
    "pd.set_option('display.width', 65)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "b065dcfd-06cd-48cc-a385-cfb4c1c39bcc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# connect to the classicmodels database\n",
    "connect_args={'ssl':{'fake_flag_to_enable_tls': True}}\n",
    "\n",
    "engine = create_engine('mysql+pymysql://namlq:abc123@localhost/classicmodels',\n",
    "                       connect_args=connect_args, echo=False\n",
    "                         )\n",
    "inspector = inspect(engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ab6d1bb8-5dd6-4aeb-83a8-d59b9e10af94",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Introduction to the MySQL Subquery"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "5c2a53d9-b6e0-445c-9523-f87361d58834",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/gif": "R0lGODdhiwGTAPcAAAAAAAB/vwB5drb////gnL///67znlRUVEgAdGYAAP//tv/W1Wa2//TMhP+2ZgAAZgB/1fStP2KtngB5hDqQ25A6AP//22IAAPTzcQAAP9v//wCr6gBmtgA6kHS//9LznrZmADeJiNuQOtvBygCjkYo3APTRWf/bkPTziJxIAP//v///4NKJJJzg/+CcSNK4fTcAAAA3WToAADrB9AAAdDe4lwAAJIrRngAAOv+/dHQAAAB0v5Db/5CVvwBhcQCV4PTlX0gAAOD//4qOdgCOi7arv66jdgBInGZ/vwAASP//9P//6mJ5dh+YFPTzkWbW/2LMnvTzlx+oX65hAAB/ytTznm/VnkmYFJDr/wB5fVBQNNTGLzp/vzd5dorgnpLlnv/r4PTgi0ic4L90AEgASEmYL3QAdHQASNv/trS4FEnGim+YFHRIAPTzdDpmtkhIdKmpqUicnJKoFPTVSGa2tvTzih+4dN/f37bW1a7MhDo6Ovr6+oqtnnR0ALTznjdhcZA6Zh+YL2Y6kDqVyr//4PT09Nu2ZnScnJxISEhInNnZ2R+YSJCQZuXklLa2Zu/v72KtcZDBysnJydu2tpycSHR0dHp6euTk5NLMhDqVv2KjkX9/f2bB4K6tPzp/ylVVN9KtcYSEhLm5uayscP+/v2K4i7S0tFlZWTe4kdv/29vW1TrB6mar6m9vb7b/9DeOfbbByjqQtmpqajeOdmRkZDrB4Dd5hLb/tpLGiu/um7b/6re2dgCr4L/gnNLgi0nGX5DB1Tqr6ry7eWKjdjp/1WBfPtvr4K7zkToAOmI3WTeJcTe4iwBIdK7zccbFgGVlQpC2/9DPhoqJiJCQ28/Pzzc3PwCOfaKhadv/9L+/vzo6ZjcAJDd5fWarv+nplwCji4q4fdLzlwCVyoq4hB+oLzejka7zl8TExLbr4Io3P2arygB0nJLVnvTznv///wAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACwAAAAAiwGTAAAI/wDdCRRIIAgAADQKDFzIsKFDhgUPHhHysKLFixgzatzIsaPHjyBDrkhxEMCOiiM9hFyJkQAChSwdRgQwMSbGHABUusuRpMVDlzA38vTpzkVCm0iTKl3KtGnDMSfdjYzaMKVTlkCZDhVoNGhTqwNd1GyYdaMKHToFQr3Ktq3bt0hVnCHqrixDsHA32o2LduHaq3sJkGmR1WUvgzndnUWokMAbNkcF5hgrOWHhl4p1MK77mEYfqi6o5h1NurTFqWQxAx1Z6aAYgYvXpfBwWeHio44hfw4ruq7BnpwhBwXqkpLrugiMA3ideXPuyDsp77SsGvPtxp13c+09cDLFgWdpV/8vkNWqWCEF026H+LK2+YnpkSvEa7q+fdKLTQ50n2Ji+JGv4SReYy+9h14QKpU320LhTUceguw9GKBlQUxYgIHxLRRahPypdF58Cqq3kHcMolVbbYrNJRhd6y1E3HhZyUXYYFItuNd9OObI1mIndajWDiGeaJ2KNFoV2JAzsijfikvS+KJtRCq5oYvtwYgkk0ZiJpOWsKloZWOIAdDTjSR2Rx2BD5Y0ppYbusCcjnDGydSKPhYFJGYpnRjmmjC1+aZkNeXJJUFVoqmnmoQNGt13lZEH456J9nmSmygtGBZ8X8pHpVeZqaSCGQWsJaRXZX1KCCJKyqnqqjdBJyiaYI3/IUaQme6npamoRphipBxm+uSmDDX4aag91kooTLimOmJiOwHXYFdY2lhoQ1vh5GxfXWXpaFBjHJIIo6yGK+5d/X133rMJjQQfggDulBO6F0q77UDdfhtsXzmc2auh42l7Y7M+WesTvP5yWS+4DuUHnQsHxQHqSIktBty/vpV0kIcNg9rcxFwKOO7HIKtV0lgMA+BwvK0tB5tmzLCBsckaS+yTXR6T9dvMiv6qs3WaccwpQWFeXFTGtvWMc1A1y0nKzxpRHPLTUF9EH0dOq7o0SFVHzZvWXHft0NQbTem1RmKPvV8Q0Jmtdshgt4Q202ufnXbcdNdt991Qw4H33nyb/6Z3300dAPjghAdeuFKCH6744h4lzvhKjj8u+eQLRU45R5ZfrvnhmW9+Ueeeh4436KI3RHrpqJt9eururM7660+7jrrssNceLu2i42777nDq7rnvvAdfH/CaEy/88XAZT7nyyDfvFPOSQ+/89EhJz7j11GcPEvaKc6/99xp5X7j44Jf/EPmDo2/++gKp37f77JcP/97zx699/Xfjb//0+tfd//7I+1/cBAjA4BFQbQcsoO0S6LU7HOAOCowg5oJXiFAc4IKWuIQEN2gRBmptExcMYSv2wMESMsSDUFNECFcoCRO6sHW8E8UKQ/i3F3IQhU+jxgwvmA0blhCHIduDLP9meIpH+PCGwVMELUJ4inMcEYkUNAUcTGHEJ24QiFbM4u20yMXZdfGLv7ObEnoQgDL+QAPuWEAZ1xiADQhEjWdciBrZ2MaBLAEJZYTAANJIxzLOoCIOKAkHGAeCg3QAjQJRQAIYAEYY0m2MAXiCQMAQDD7qsSFwROQbA3BJhoCBC5dcAisWMoI6agQEg2SJBSpAgZA5AAc8aIgiGbmSVbZyfVi0ySfdyBA1dlKOAYjjQHy5x4VAUpIOKSUvM4LKmNjSlQ8oJlOeicu6ZbKXnJTmMIOpSUtq0x2fpAIWHqLMjTSTISIQ5EAUeRBYJjIBFgPAIBVJB3gC4JYCSedBUqn/SD0AAAeM+Gcs3RHIg+DTI/osySHdcQIZHISWA3HAA24Bz2i6g50CfWc855mAehp0IQnlZwL8CdCMErQkB01f3T5ZRmRu8pfbFOZLv0lMcpqSmamM6C1XOUieCsQCgJAmNd9pURG4k6A7rQBHKaBIDvg0kIxsKEQ/ItFvXnSRDHmlNvaogGn41B1AFSorF6LIoh7VAUldalOfCoCoymCqgMulTeYYAHFuko2dvOY28brHmibzphg550OqqsiUDmSoV72lVB1C2EX6FJVfdYcIFgqSqsoSqwuBKlkTYNifjnWdnBXIYhvSWAY81qlKHchku/k+vJWyruP0KzBl6k1s/8KUlIC9iGA9azGLFvKhDEHsLN9Jy1X2dgCzfCYIOoCG0ArEspW1KEOGm9mjDuS3bQ3uZ4nLXbBW4LjJHetym4tP6KoUb5B0o2xj2s31gpMLdv3rMgObU+9S1rzsrK9wMTvcVd43muFt5XjtWRLpUtXAoIWreTcrz4Xsl5b9rcB/ketY8TKXwAdBcGv3Vk738pG27j2mTeer2/pSV7IaFoGBH8zdE6uYwqa1cCoqANeFFLK+F1lwdyOqYZCueLtXhTBWXQzgCgu4AzOu8fisyUsRe1ivM23IJ0M5yoGUUyMvPmxqTwpgQSByud0Ec4K5+9VAFjnGR9YAdE/gBhs3WP8jOg5yVhGsAC8LRMzXpayc5VxmAJxZuYdcc5uXbE02IpOua+Qlosu4gUXf9I551OaVM2Lce2oUAB0whEUbakjWYpSjQhayITUNY0CjkdOYDnN24Yxg7Ga4mDpGtZ4vDeruYjTTf7bwqR2aas41MmSKnPWvITfsjwWys8VuXLKXfV5mOzt/z462/6RNbQRW+9pdk2tMitDHJyzape54bQD+yMc6vpbcbCnom7E97bpxG90zhTQyy1nKP87xjNwed152y+61aZsl77btHrntxjHadcoDmCMEXEFGfcOF3/1Wnd0CDsxLlhPh7rijOOnKCS74MccofSdJAwpLetozpRD/T2hatXxLdR/U5aBt50AjbpN/r4Ti27wkwWt7TF92YxBUyITDB5vdxRZ2rUpVpDsbelB+GzWWTH+udF8ZS82O1upv9W4qw0rzmk+cjvbOpsZjm0139JyTkQgAL8gIb+3mdLXhHSQqC6taPe92qKvdczMju1q+H5LuXUfc19uO6GXW9OwQwEMAVsH2igCeoLk+cnMhat7dnhi6YB7u4xtbXt+WRMmBV7a7hz5TbscR42M/fOMfgtECl9rCk+exNC2PWcgX8wTI4EHeW/9q3vtZmvkNPUtsHhKcxxuPkjT4OBGuetJrt8YBdseA8Zn3O+t3u9VfZSw+u8oad/8iWRb+//YGL/Bwc9P8bqw3z1c/WOmyOciAbu7SZXDQ8Odz/i83qe1FO2hBX9TO0ids4rcRxAcSxldbkEZu4kZuzdd2DSFraBR9A+ZRluZg31WBkhVym4VjEDgQHchQvCaAA5gRDgRBJXRiMTFaI1gaFRRCGcRBKMgSeLaCpAFCKzRCGxSDlaV/NJgXKrRDLdSDQogUMrRDNTSEXtMOSriETNiETviEUBiFUjiFVFiFVniFVugMWrCFXKgFu4CFYBiGYoiEYliGZniGaJiGTZgLz9CFWvAJjaCGcjiGQziHdniHeGiG0VAMXPgJwpCHgAiFd/RLY5R4ZddhfaRedHRoiSiDnf/mZr22YyBRadaVEXGGEZDERmc0R5I0aQ+xhCYAAz6whFEwBAJwikTwAe3QAKfYigJAAkrIiqnIhKzoiq+4hE7ABKc4AQawirZ4ijVwhlNwEDGgiks4jABQjEuIARcgAWiIAiVwEDZwA0voDaNwDaMQh08YARnQi4E4h4UoTXf0A6pwiHXkXsQkb7VlE1RHWpUoiR7RVM8lgu1nVRsxaXOUfrnVEMc4jUpYigIABUoYBuXgi7zohLJojLEoAAfZhGHQBQfpBJrAhC9wi2gYAf7YhBhJjU3IjM5ohsw4iu0QAcpohdzojd+ohu5QBPGVcUhAjuaoiLdVUzvnYdFlVZf/GBPV5w6TwFoWkZMZgY+R5okOoYQRAAAi2Q4PCYtNyIoNSYsCMItL6JQo+Y+mKJBPWJFMeYYn6YRdiYcsUJLtAAoKSYVfmZJpGG7iVG+7VFOIOJMxuY5YpoEZaDELlVCPiGpwhWr1BXPehWwqB1IZ5ghTR5cXIZSwRZT82A7DGAJTGZVlaZBVuZBSuZBPOZBdkAVeAIVaaYUsUBKOqYSfaTHKOJoloYyheBAfCYowcBBJOZKgqYTQGJpOaJq02Q6jmQGd0I1GGZtoCYbm9wSmZwxcMANueY5lV3EDh5y3ZRFPB4Iv12NxhoKaRVC0hHWMpIOShX/3B3UyYFHYGZQ3/6VGQTcDiskQ7cCMGamUXXCKWGmZk+mLlSmZTcmQ8amEnUmFLOCPoXibZ7mE/6mEHsmER/mREWCgAOCMoeiMA1qb/AkDobmf1BiKvFmg7bCgv4mFfMR4nKR4xtmIi3ZJ6YgEdhWi9shydadJcTadtRdZWvZ2h6SCKJpPh4R30eR3PtkQQgkBkfADsLCP6BmK6+mLp6iZC+mKDZmQUImkvUiVnGmRUjibSxiWChmgI8mbHdmMSwiNrymbJZCUVIqhTSilolmMZIqb3cilUyqWGTqF71ULSMAJngAMkXScMklTbGR4yYkRl2dgK9pjJ6aDm3dml0V5hCp1FNZ5J4pbev8KAejgCeoApAtxoTCApRRZpJvppPU5n5r6mJd5qVsZhQ16pShppVY6qqO6jBdAmyeZqgKqpUbZjaPaqqsKoJbaplLokuJADLqABECHBXYql6V3fjbpEH0qTX9qVYFaezF3XIi1Y6Ule77XYzo6npzEcIMgqQNBqUNKiqYIi536mJxqnw6ZmZuZlVAqqrBKqrYan6e6rq6anhcQT2laArf5qqtJq/kqq/NqMbeKq1DoksGEDWS0cXGJjmKHfMJqEXinZ8laqC/aEN/XEDvZkw2rATY6ABPLETs6APlGYvyonhzphJ0ZrpRZliYLkO/ZhPkZpfa6plX6r+yapauppmP/WgKryYRUuoRkeaZU6rP1mrMAW4VmR0b6qEfBirAWx03F6hDPGXWyR1qAyqzVeQLQgKj8906p5AB3yZ0ENX/g6X6DdpjWirSMdhFK2JhTyZQqS5+birLkWq4ROZFL2LJSKKGU6p8y+645a6EXygex6o0m8AevKpIkqYp4259G+aAViqWDO7RuupL6lklJC6Jll4COBrIUa5j7N2fa5Gq/J1ohqEkf6F0yp1qGqU8PQJi3N7oWkYlrtIkJq60C0ZuvWYvueaSuyJS424ok0LunuJW5uItVabd365vt6pW3iowl4bitmYwKmZrQu6XRCADraZs6exC66bzEGJmQ24Rk/5i23fq95Fu+5luG4UupXXq+7Nu+7suE6fu+8ju/7hu/9Hu/+Auw9pu//Nu/ebi//hvAAmyGADzABnzAkSuEUWgEvwgFwLuyFQmMRwqLERyMZoiMbIrBChmvCDy/BdwODGzBnmoAw4uVnVmRwViLqcjAAiDCYbiRXjm+HNzB7/vBIVyfB8nAsFiKRvqQvFiLE2AOptjCZmiqMkvD/mvDRAyVB9mZPtyLuaiZuFsK7bnEUYi9ohlPpanFqii9Qiu9r3mUB0GbzCiNI4vEcqjELkyfOkyfKuuUw/AKWTALVvyEiQuhBLq3MpuqfnugvamgMOCMNosC6XCfaIy+dQiFLP/ciilsn1GcqeT6xgwJDgLwDaa4xkzos2JpxPE5qjabyV8Ks8x4r4ecxon8hDe8pME7wu0gyROQBwKACpesrvtaqnrcyfC6rqrKqrzJvEJbymeoxjhsAAw8i0/cDo/spACJyapay8mrkXucy79cxv6KkmW8vsBMhwqsyHUsmSXcykPQwxBpAMo8y1CoyTHrrtFcs6F8s7/chCxwxNmsoafshKnMyhU5iydMxOXczfD8oHqrzrjctwmqhCYAuDP7uBiQDMY4BWw6z2EozEwMxbpowRG8xP3MzNk7xtAs0EzIvNoruM8rltJbkiXtvRBNz9uc0iztwfXc0jB9vh8c0zT/3aYzXdM4/Y03ndM8fYc73dNAnZYvHdREPYcAnItPWYqvTK4l+4vgaotYCbzpWtRPCARl8AVLCARXYAVLuAVSUAVKmAZNMNZczYRz0ARf3YRWjdUEWtC5OtTg/JS5SASYwNS3aLJU+c0ma75nyr5rndVb3dVpnQZprdVlHdbkcNVqrdigLM9KWMBGYKRKONd13cR3Hbes3MZ7Xb59fb5/rYSGLdhV0AZrcNhAwA5LWAdy8Aulvdhsnb2kDL9w3Q4voJkovJRO2tSfmtuX/alTCMYbDQCh2aADyoyQ0K/D3a8lkZTc2AzzWq9J2dltHU9uLYWf3Q6hrYReXQXZ7YRr/50GduDaTkihhtwOBVyRUFDMvtAFNcDbT73b5KrZmG2Wbu3HuAnQ8vqRxX0BvIm3XkrKGFkNvYgB0jCzMAyFYhxP7zyF3b3dqt0EgfDaoj0Hi+AHVSiyUVjArCjLDAnL7e3URNqKB5nXTGCkwOvboLy+6EzcWjrKBh3I1Avg1Y2vaYvNeNjgad0OWzDWTVDhSqja4d3dLjvj4DvbD7kMTFAK3DAOAenebvuYvsvKF67LNB64LM6gsOrJL6uR4+vQHzDDd4jjYM2EWh3e2B3YQF6F0CjcGT7buWgNtnAMTCDHXuDkeG2fxayKm/2E8Tqr/KrfLZ7l6yrdVmoC23ADO/8bhQluMQtu3YGt3TnOhNut4zw+1j4+hWve6AU8vEQQDqYoxXb93pNJ4gH55JjezqBMm1TaoBh65TGuvPEJjcqw5YpO3URehaR92NvdBrgg6YOd43MQ4VSI4QE72wBJwfZp5/NNn/n8AXu+jW590KKJ39CojMOI5YCes17e0dA+voFI2GBN2mrQDuIO2lcw7uWuhOkuheTd5ittz/wMmcpui6KOzBUt1aEKhSc9pcgrvceN7VX+qq75zFlq49/O4+Nu7givhMEu4eA+hZ8Z20v401RdhWKazdDo2OY92xUPhts+z37r7j3Y8VxpvWfc0xRP8irvhCm/8i6/8e/+8jIpX+wxP/M2P/Ecf/Mv3/I6X9Q83/NB/fNAj/I5P/QdL/RGj9NIn/Q0HRAAOw==\n",
      "text/plain": [
       "<IPython.core.display.Image object>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Image('../images/mysql-subquery.gif')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "792cc32a-d8c6-45a4-a567-276cc8e774a4",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Using a MySQL subquery in the WHERE clause"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "10c7011a-4dae-40ab-8958-1103dd533d45",
   "metadata": {},
   "source": [
    "- MySQL subquery with comparison operators"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "ca567070-f655-45da-97bd-eeb79cafbc42",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customerNumber</th>\n",
       "      <th>checkNumber</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>141</td>\n",
       "      <td>JE105477</td>\n",
       "      <td>120166.58</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customerNumber checkNumber     amount\n",
       "0             141    JE105477  120166.58"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "string = '''\n",
    "SELECT customerNumber, checkNumber, amount\n",
    "FROM payments\n",
    "WHERE amount = (SELECT MAX(amount) FROM payments)\n",
    ";'''\n",
    "\n",
    "pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "5195feb3-6d0a-440b-b3ff-2a278245105c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customerNumber</th>\n",
       "      <th>checkNumber</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>141</td>\n",
       "      <td>JE105477</td>\n",
       "      <td>120166.58</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customerNumber checkNumber     amount\n",
       "0             141    JE105477  120166.58"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(pd\n",
    " .read_sql_table('payments', engine)\n",
    " .nlargest(n=1, columns='amount')\n",
    " [['customerNumber', 'checkNumber', 'amount']]\n",
    " .reset_index(drop=True)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "1571b616-abc1-43e5-9ec3-77f9928e8a7d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customerNumber</th>\n",
       "      <th>checkNumber</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>141</td>\n",
       "      <td>JE105477</td>\n",
       "      <td>120166.58</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customerNumber checkNumber     amount\n",
       "0             141    JE105477  120166.58"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "string = '''\n",
    "SELECT customerNumber, checkNumber, amount\n",
    "FROM payments\n",
    "ORDER BY amount DESC\n",
    "LIMIT 1\n",
    ";'''\n",
    "\n",
    "pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "ad611f1b-d1fa-4522-a814-b5eb36932d67",
   "metadata": {},
   "source": [
    "# OperationalError\n",
    "string = '''\n",
    "SELECT\n",
    "    customerNumber,\n",
    "    checkNumber,\n",
    "    MAX(amount) AS amount\n",
    "FROM\n",
    "    payments\n",
    ";'''\n",
    "\n",
    "pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e363d982-b85f-4cc6-b4cc-55aa8da72b9d",
   "metadata": {},
   "source": [
    "- MySQL subquery with `IN` and `NOT IN` operators"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dc78e032-2b9a-4f67-913c-b7fd446d671a",
   "metadata": {},
   "source": [
    "Find the customers who have not placed any orders as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "d160a20d-b3e2-4b41-8bb9-91d63905b20b",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "SELECT customerName\n",
    "FROM customers\n",
    "WHERE customerNumber NOT IN (\n",
    "        SELECT DISTINCT customerNumber\n",
    "        FROM orders)\n",
    ";'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "9da04f12-0692-4eef-b112-37daaeaffa80",
   "metadata": {},
   "outputs": [],
   "source": [
    "cusnum_orders = (\n",
    "    pd.read_sql_table('orders', engine, columns=['customerNumber'])\n",
    "    ['customerNumber']\n",
    "    .unique()\n",
    ")\n",
    "\n",
    "df2 = (\n",
    "    pd.read_sql_table('customers', engine)\n",
    "    .query(\"customerNumber not in @cusnum_orders\")\n",
    "    [['customerName']]\n",
    "    .reset_index(drop=True)    \n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "d65de448-750d-48ec-8cf2-92de93835752",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ba1d58b5-5184-4cd0-b097-e21326517a2c",
   "metadata": {
    "tags": []
   },
   "source": [
    "### MySQL subquery in the `FROM` clause"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "985742cf-d054-4b39-8604-013ec1da9745",
   "metadata": {},
   "source": [
    "Find the maximum, minimum, and average number of items in sale orders from a standalone subquery:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "4ae040ba-b939-426d-a6a2-e87416d7f347",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>max_items</th>\n",
       "      <th>min_items</th>\n",
       "      <th>floor_avg_items</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   max_items  min_items  floor_avg_items\n",
       "0         18          1              9.0"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "string = '''\n",
    "SELECT\n",
    "    MAX(items) max_items,\n",
    "    MIN(items) min_items,\n",
    "    FLOOR(AVG(items)) floor_avg_items\n",
    "FROM\n",
    "    (SELECT\n",
    "        #orderNumber, \n",
    "        COUNT(orderNumber) AS items\n",
    "    FROM orderdetails\n",
    "    GROUP BY orderNumber) AS lineitems \n",
    "    # Every derived table must have its own alias\n",
    ";'''\n",
    "pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "537cd468-c748-48c9-bad9-31c80ecba60b",
   "metadata": {},
   "outputs": [],
   "source": [
    "def floor_avg(s):\n",
    "    return np.floor(s.mean())\n",
    "\n",
    "def convert_int(df):\n",
    "    return (df\n",
    "            .assign(max_items = df.max_items.astype(int),\n",
    "                    min_items = df.min_items.astype(int))\n",
    "           )\n",
    "\n",
    "lineitems = pd.DataFrame(\n",
    "    pd.read_sql_table('orderdetails', engine, columns=['orderNumber'])\n",
    "    ['orderNumber']\n",
    "    .value_counts()\n",
    "    .rename('items')\n",
    ")\n",
    "\n",
    "lineitems = (\n",
    "    lineitems\n",
    "    .agg(max_items = pd.NamedAgg(column='items', aggfunc='max'), \n",
    "         min_items = pd.NamedAgg(column='items', aggfunc='min'),\n",
    "         floor_avg_items = pd.NamedAgg(column='items', aggfunc=floor_avg))\n",
    "    .T\n",
    "    .pipe(convert_int)\n",
    "    .reset_index(drop=True)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "e2f91671-ae2a-4e80-89b7-0df908c66cbc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>max_items</th>\n",
       "      <th>min_items</th>\n",
       "      <th>floor_avg_items</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   max_items  min_items  floor_avg_items\n",
       "0         18          1              9.0"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lineitems"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ed5f60ce-75c1-408e-ac1e-c31f99af21f0",
   "metadata": {},
   "source": [
    "- MySQL correlated subquery: a subquery that uses data from the outer query."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a27ba9bb-2b4d-4c7e-afdd-6ca6be416fdc",
   "metadata": {},
   "source": [
    "Outer or inner table's aliases result in different results:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "a030203f-39e7-4626-884a-bbfa1bd49f0d",
   "metadata": {},
   "outputs": [],
   "source": [
    "string0 = '''\n",
    "SELECT productLine, productName, buyPrice\n",
    "FROM products p1\n",
    "WHERE buyPrice > (\n",
    "    SELECT AVG(buyPrice)\n",
    "    FROM products p2 # alias here is not nessesary\n",
    "    WHERE p1.productLine = p2.productLine)\n",
    "ORDER BY productLine, productName\n",
    ";'''\n",
    "\n",
    "string1 = '''\n",
    "SELECT productLine, productName, buyPrice\n",
    "FROM products\n",
    "WHERE buyPrice > (\n",
    "    SELECT AVG(buyPrice)\n",
    "    FROM products p2\n",
    "    WHERE p2.productLine = productLine)\n",
    "ORDER BY productLine, productName\n",
    ";'''\n",
    "\n",
    "df0 = pd.read_sql(string0, engine)\n",
    "df1 = pd.read_sql(string1, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "de5b4602-1757-46fa-9a9f-b74dd83d7da4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(55, 3)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>productLine</th>\n",
       "      <th>productName</th>\n",
       "      <th>buyPrice</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Classic Cars</td>\n",
       "      <td>1952 Alpine Renault 1300</td>\n",
       "      <td>98.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Classic Cars</td>\n",
       "      <td>1952 Citroen-15CV</td>\n",
       "      <td>72.82</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    productLine               productName  buyPrice\n",
       "0  Classic Cars  1952 Alpine Renault 1300     98.58\n",
       "1  Classic Cars         1952 Citroen-15CV     72.82"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(df0.shape)\n",
    "df0.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "4a5549cd-ecfc-4598-b9cf-3dcc364fb3a8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(54, 3)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>productLine</th>\n",
       "      <th>productName</th>\n",
       "      <th>buyPrice</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Classic Cars</td>\n",
       "      <td>1948 Porsche Type 356 Roadster</td>\n",
       "      <td>62.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Classic Cars</td>\n",
       "      <td>1952 Alpine Renault 1300</td>\n",
       "      <td>98.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Classic Cars</td>\n",
       "      <td>1952 Citroen-15CV</td>\n",
       "      <td>72.82</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    productLine                     productName  buyPrice\n",
       "0  Classic Cars  1948 Porsche Type 356 Roadster     62.16\n",
       "1  Classic Cars        1952 Alpine Renault 1300     98.58\n",
       "2  Classic Cars               1952 Citroen-15CV     72.82"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(df1.shape)\n",
    "df1.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "c2f402ba-6f91-487e-af92-f44e427c2fc5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>productLine</th>\n",
       "      <th>productName</th>\n",
       "      <th>buyPrice</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Motorcycles</td>\n",
       "      <td>1969 Harley Davidson Ultimate Chopper</td>\n",
       "      <td>48.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Classic Cars</td>\n",
       "      <td>1952 Alpine Renault 1300</td>\n",
       "      <td>98.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Motorcycles</td>\n",
       "      <td>1996 Moto Guzzi 1100i</td>\n",
       "      <td>68.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Motorcycles</td>\n",
       "      <td>2003 Harley-Davidson Eagle Drag Bike</td>\n",
       "      <td>91.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Classic Cars</td>\n",
       "      <td>1972 Alfa Romeo GTA</td>\n",
       "      <td>85.68</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    productLine                            productName  \\\n",
       "0   Motorcycles  1969 Harley Davidson Ultimate Chopper   \n",
       "1  Classic Cars               1952 Alpine Renault 1300   \n",
       "2   Motorcycles                  1996 Moto Guzzi 1100i   \n",
       "3   Motorcycles   2003 Harley-Davidson Eagle Drag Bike   \n",
       "4  Classic Cars                    1972 Alfa Romeo GTA   \n",
       "\n",
       "   buyPrice  \n",
       "0     48.81  \n",
       "1     98.58  \n",
       "2     68.99  \n",
       "3     91.02  \n",
       "4     85.68  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products = pd.read_sql_table(\n",
    "    'products', engine, columns=['productLine', 'productName', 'buyPrice'])\n",
    "products.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "10d8a079-ebcf-4f0a-920d-f9c1200ed58f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>buyPrice_mean</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>productLine</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Classic Cars</th>\n",
       "      <td>64.446316</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Motorcycles</th>\n",
       "      <td>50.685385</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Planes</th>\n",
       "      <td>49.629167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ships</th>\n",
       "      <td>47.007778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Trains</th>\n",
       "      <td>43.923333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Trucks and Buses</th>\n",
       "      <td>56.329091</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Vintage Cars</th>\n",
       "      <td>46.066250</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  buyPrice_mean\n",
       "productLine                    \n",
       "Classic Cars          64.446316\n",
       "Motorcycles           50.685385\n",
       "Planes                49.629167\n",
       "Ships                 47.007778\n",
       "Trains                43.923333\n",
       "Trucks and Buses      56.329091\n",
       "Vintage Cars          46.066250"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(products\n",
    " .groupby('productLine')\n",
    " .agg(buyPrice_mean = pd.NamedAgg(\n",
    "     column='buyPrice', aggfunc='mean'))\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "8cb2e7e6-3a6b-4fbd-adc5-f64c6cbbeb9b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = (\n",
    "    products\n",
    "    .merge(\n",
    "        products\n",
    "        .groupby('productLine')\n",
    "        .agg(buyPrice_mean = pd.NamedAgg(\n",
    "            column='buyPrice', aggfunc = 'mean')),\n",
    "        left_on='productLine',\n",
    "        right_index=True,\n",
    "    )\n",
    "    .query('buyPrice > buyPrice_mean')\n",
    "    [['productLine', 'productName', 'buyPrice']]\n",
    "    .sort_values(['productLine', 'productName'],\n",
    "                 key=lambda col: col.str.lower())\n",
    "    .reset_index(drop=True)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "4eafa89b-38bd-4eb6-8aa0-1ec1772c690a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df0.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "56e5427a-dc44-43d9-ae73-c8501489d28b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>productLine</th>\n",
       "      <th>productName</th>\n",
       "      <th>buyPrice</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Classic Cars</td>\n",
       "      <td>1952 Alpine Renault 1300</td>\n",
       "      <td>98.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Classic Cars</td>\n",
       "      <td>1952 Citroen-15CV</td>\n",
       "      <td>72.82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Classic Cars</td>\n",
       "      <td>1956 Porsche 356A Coupe</td>\n",
       "      <td>98.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Classic Cars</td>\n",
       "      <td>1957 Corvette Convertible</td>\n",
       "      <td>69.93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Classic Cars</td>\n",
       "      <td>1962 LanciaA Delta 16V</td>\n",
       "      <td>103.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>Vintage Cars</td>\n",
       "      <td>1932 Model A Ford J-Coupe</td>\n",
       "      <td>58.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>Vintage Cars</td>\n",
       "      <td>1936 Chrysler Airflow</td>\n",
       "      <td>57.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>Vintage Cars</td>\n",
       "      <td>1937 Lincoln Berline</td>\n",
       "      <td>60.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>Vintage Cars</td>\n",
       "      <td>1940 Ford Delivery Sedan</td>\n",
       "      <td>48.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>Vintage Cars</td>\n",
       "      <td>1941 Chevrolet Special Deluxe Cabriolet</td>\n",
       "      <td>64.58</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>55 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     productLine                              productName  \\\n",
       "0   Classic Cars                 1952 Alpine Renault 1300   \n",
       "1   Classic Cars                        1952 Citroen-15CV   \n",
       "2   Classic Cars                  1956 Porsche 356A Coupe   \n",
       "3   Classic Cars                1957 Corvette Convertible   \n",
       "4   Classic Cars                   1962 LanciaA Delta 16V   \n",
       "..           ...                                      ...   \n",
       "50  Vintage Cars                1932 Model A Ford J-Coupe   \n",
       "51  Vintage Cars                    1936 Chrysler Airflow   \n",
       "52  Vintage Cars                     1937 Lincoln Berline   \n",
       "53  Vintage Cars                 1940 Ford Delivery Sedan   \n",
       "54  Vintage Cars  1941 Chevrolet Special Deluxe Cabriolet   \n",
       "\n",
       "    buyPrice  \n",
       "0      98.58  \n",
       "1      72.82  \n",
       "2      98.30  \n",
       "3      69.93  \n",
       "4     103.42  \n",
       "..       ...  \n",
       "50     58.48  \n",
       "51     57.46  \n",
       "52     60.62  \n",
       "53     48.64  \n",
       "54     64.58  \n",
       "\n",
       "[55 rows x 3 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df0"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7d43d9f7-dd36-4a39-b417-ad609bcd5726",
   "metadata": {},
   "source": [
    "- MySQL subquery with `EXISTS` and `NOT EXISTS`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "56a3f75e-8ac7-40bb-b336-7008d80e8b87",
   "metadata": {},
   "source": [
    "Find sales orders whose total values are greater than 60K"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "9f4f3c4f-3fe9-41cc-8b57-cceb28ea0c30",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>orderNumber</th>\n",
       "      <th>total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10165</td>\n",
       "      <td>67392.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10310</td>\n",
       "      <td>61234.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10287</td>\n",
       "      <td>61402.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   orderNumber     total\n",
       "0        10165  67392.85\n",
       "1        10310  61234.67\n",
       "2        10287  61402.00"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "string1 = '''\n",
    "SELECT\n",
    "    orderNumber,\n",
    "    SUM(priceEach * quantityOrdered) total\n",
    "FROM orderdetails\n",
    "INNER JOIN orders USING (orderNumber)\n",
    "GROUP BY orderNumber\n",
    "HAVING SUM(priceEach * quantityOrdered) > 60000\n",
    ";'''\n",
    "\n",
    "string2 = '''\n",
    "SELECT\n",
    "    orderNumber,\n",
    "    SUM(priceEach * quantityOrdered) total\n",
    "FROM orderdetails\n",
    "GROUP BY orderNumber\n",
    "HAVING total > 60000\n",
    ";'''\n",
    "\n",
    "pd.read_sql(string1, engine)\n",
    "# pd.read_sql(string2, engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "df2d895e-9ba3-455b-b1b9-7772ce6c745e",
   "metadata": {},
   "source": [
    "Find customers who placed at least one sales order with the total value greater than 60K"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "cbf4667c-49af-4a6f-a285-b7aab93e3b2e",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "# EXPLAIN\n",
    "SELECT customerNumber, customerName\n",
    "FROM customers\n",
    "WHERE EXISTS (\n",
    "    SELECT orderNumber, SUM(priceEach * quantityOrdered) total\n",
    "    FROM orderdetails\n",
    "    INNER JOIN orders USING (orderNumber)\n",
    "    WHERE customerNumber = customers.customerNumber\n",
    "    GROUP BY orderNumber\n",
    "    HAVING total > 60000)\n",
    ";'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "8c535f62-caad-4416-8d56-1b0feb81afb1",
   "metadata": {},
   "outputs": [],
   "source": [
    "customers = pd.read_sql_table(\n",
    "    'customers', engine, columns=['customerNumber', 'customerName'])\n",
    "\n",
    "orderdetails = (\n",
    "    pd.read_sql_table('orderdetails', engine)\n",
    "    .merge(pd.read_sql_table('orders', engine),\n",
    "           on='orderNumber',\n",
    "           how='inner')\n",
    "    .assign(total = lambda df: df.priceEach * df.quantityOrdered)\n",
    "    .groupby(['orderNumber', 'customerNumber'], as_index=False)\n",
    "    .agg({'total': 'sum'})\n",
    "    .query('total > 60000')\n",
    ")\n",
    "\n",
    "df2 = (\n",
    "    customers\n",
    "    .merge(orderdetails, on='customerNumber', how='inner')\n",
    "    [['customerNumber', 'customerName']]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "11196a40-9009-49dd-856b-3d373cb8d9ff",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "942914c4-9281-4cec-aba3-442e1074f7c4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customerNumber</th>\n",
       "      <th>customerName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>148</td>\n",
       "      <td>Dragon Souveniers, Ltd.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>259</td>\n",
       "      <td>Toms Spezialitäten, Ltd</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>298</td>\n",
       "      <td>Vida Sport, Ltd</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customerNumber             customerName\n",
       "0             148  Dragon Souveniers, Ltd.\n",
       "1             259  Toms Spezialitäten, Ltd\n",
       "2             298          Vida Sport, Ltd"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  }
 ],
 "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.15"
  },
  "toc-autonumbering": true,
  "toc-showmarkdowntxt": false,
  "toc-showtags": false
 },
 "nbformat": 4,
 "nbformat_minor": 5
}