Section 04 Joining Tables/14 MySQL Alias.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "087044b4-e243-4d00-9be8-3661aa30da20",
   "metadata": {
    "tags": [],
    "toc-hr-collapsed": true
   },
   "source": [
    "## **[MySQL Alias](https://www.mysqltutorial.org/mysql-alias/)**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e6a65d37-c676-4757-8a0c-fb30d3ee25fb",
   "metadata": {},
   "source": [
    "Use MySQL alias to improve the readability of the queries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "36336e18-84c2-4ba4-a604-e0b2bd73b212",
   "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",
    "    # 'max_rows', 10, \n",
    "    'display.max_colwidth', None,\n",
    "    'display.max_rows', 10,\n",
    "    # 'precision', 2,\n",
    "    # 'width', 45\n",
    ")\n",
    "\n",
    "pd.set_option('display.width', 65)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "9ce93916-4dab-4083-a952-39e401e30ff2",
   "metadata": {},
   "outputs": [],
   "source": [
    "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": "7d88cde9-5e29-4013-93a4-ff1f6b7512eb",
   "metadata": {
    "tags": []
   },
   "source": [
    "### MySQL alias for columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "645b5cd4-e5dd-4a0b-94cf-d902c52d90b9",
   "metadata": {},
   "source": [
    "syntax:"
   ]
  },
  {
   "cell_type": "raw",
   "id": "82d7ea4c-f6e2-465c-a713-ae19aa956dda",
   "metadata": {},
   "source": [
    "SELECT \n",
    "   [column_1 | expression] AS descriptive_name\n",
    "FROM table_name;"
   ]
  },
  {
   "cell_type": "raw",
   "id": "fd9c1242-c40c-44ef-a30c-8fb4e09e7709",
   "metadata": {},
   "source": [
    "SELECT \n",
    "   [column_1 | expression] AS `descriptive name`\n",
    "FROM \n",
    "   table_name;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "14ec8444-0fb1-44a6-bbde-60d26834037f",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "SELECT\n",
    "    CONCAT_WS(', ', firstName, lastName) AS `Full Name`\n",
    "FROM employees\n",
    "ORDER BY `Full Name`\n",
    ";'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "9031aeea-72df-4270-8d2c-bc3945d721ab",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = (\n",
    "    pd.read_sql_table('employees', engine)\n",
    "    .assign(**{'Full Name': lambda df_: df_.firstName.str.cat(\n",
    "        df_.lastName, sep=', ', na_rep='-')})\n",
    "    # .assign(FullName = lambda df_: df_.firstName.str.cat(\n",
    "    #     df_.lastName, sep=', ', na_rep='-'))\n",
    "    .sort_values(\n",
    "        by='Full Name', ignore_index=True)\n",
    "    [['Full Name']]\n",
    ") "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "3b4d92b5-8773-4dc6-bf38-9d78ac719588",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "76e8a1cc-9b1b-4f07-9e04-6ea93e7ad51a",
   "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>Full Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Andy, Fixter</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Anthony, Bow</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Barry, Jones</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Diane, Murphy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Foon Yue, Tseng</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Full Name\n",
       "0     Andy, Fixter\n",
       "1     Anthony, Bow\n",
       "2     Barry, Jones\n",
       "3    Diane, Murphy\n",
       "4  Foon Yue, Tseng"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "edd825bf-0937-4e46-9bd6-01c9ca374029",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "SELECT\n",
    "    orderNumber `Order no.`,\n",
    "    SUM(priceEach * quantityOrdered) total\n",
    "FROM orderdetails\n",
    "GROUP BY `Order no.`\n",
    "HAVING total > 60000;\n",
    "'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "7f589a10-21b9-4e0c-bf0a-c8b147bd4a08",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = (\n",
    "    pd.read_sql_table('orderdetails', engine)\n",
    "    .assign(total = lambda df_: df_.priceEach * df_.quantityOrdered)\n",
    "    .groupby('orderNumber')\n",
    "    .agg({'total': 'sum'})\n",
    "    .query('total > 60000')\n",
    "    .reset_index(drop=False)\n",
    "    .rename(columns = {'orderNumber': 'Order no.'})\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "9f10febf-9a69-4837-ba37-109b23c3c177",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "23209075-f6a2-4d6b-9c59-d5806d6abc5d",
   "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>Order no.</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>10287</td>\n",
       "      <td>61402.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10310</td>\n",
       "      <td>61234.67</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Order no.     total\n",
       "0      10165  67392.85\n",
       "1      10287  61402.00\n",
       "2      10310  61234.67"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4c123521-1bee-49b8-88f5-483452c9763a",
   "metadata": {
    "tags": []
   },
   "source": [
    "### MySQL alias for tables"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "109b133d-1eae-4023-b74d-b4845bd31a85",
   "metadata": {},
   "source": [
    "syntax: **table_name AS table_alias**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "1ad339f4-9f8b-4c5f-b80f-53f3641c62a6",
   "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>firstName</th>\n",
       "      <th>lastName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Andy</td>\n",
       "      <td>Fixter</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Anthony</td>\n",
       "      <td>Bow</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Barry</td>\n",
       "      <td>Jones</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Diane</td>\n",
       "      <td>Murphy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Foon Yue</td>\n",
       "      <td>Tseng</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  firstName lastName\n",
       "0      Andy   Fixter\n",
       "1   Anthony      Bow\n",
       "2     Barry    Jones\n",
       "3     Diane   Murphy\n",
       "4  Foon Yue    Tseng"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "string = '''\n",
    "SELECT e.firstName, e.lastName\n",
    "FROM employees e\n",
    "ORDER BY e.firstName\n",
    ";'''\n",
    "\n",
    "pd.read_sql(string, engine).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "d1fd12b1-26f7-4a81-8714-c70e679883bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "SELECT\n",
    "    customerName,\n",
    "    COUNT(o.customerNumber) total\n",
    "FROM customers c\n",
    "INNER JOIN orders o ON c.customerNumber = o.customerNumber\n",
    "GROUP BY customerName\n",
    "ORDER BY \n",
    "    total DESC, \n",
    "    customerName ASC\n",
    ";'''\n",
    "\n",
    "# same as\n",
    "string1 = '''\n",
    "SELECT\n",
    "    customers.customerName,\n",
    "    COUNT(orders.orderNumber) total\n",
    "FROM\n",
    "    customers\n",
    "INNER JOIN  \n",
    "    orders ON customers.customerNumber = orders.customerNumber\n",
    "GROUP BY customerName\n",
    "ORDER BY\n",
    "    total DESC,\n",
    "    customerName ASC\n",
    ";'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "d8b34721-2d7b-4862-99f0-7c49188ed0aa",
   "metadata": {},
   "outputs": [],
   "source": [
    "def key_upper(s):\n",
    "    if s.dtypes == 'O':\n",
    "        return s.str.upper()\n",
    "    else:\n",
    "        return s\n",
    "\n",
    "df2 = (\n",
    "    pd.read_sql_table('customers', engine)\n",
    "    .merge(pd.read_sql_table('orders', engine),\n",
    "           on='customerNumber', how='inner')\n",
    "    .groupby('customerName')\n",
    "    .agg(total = pd.NamedAgg(\n",
    "        column='customerNumber', aggfunc='size'))\n",
    "    .sort_values(\n",
    "        by=['total', 'customerName'],\n",
    "        ascending=[False, True],\n",
    "        key = key_upper)\n",
    "    .reset_index()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "b00f76ec-4a21-4c6e-b4f4-be02ca8e4814",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "0f682f7a-4b05-48c9-8fa8-b5c00bd8160e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customerName</th>\n",
       "      <th>total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Euro+ Shopping Channel</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mini Gifts Distributors Ltd.</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Australian Collectors, Co.</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Danish Wholesale Imports</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Down Under Souveniers, Inc</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   customerName  total\n",
       "0        Euro+ Shopping Channel     26\n",
       "1  Mini Gifts Distributors Ltd.     17\n",
       "2    Australian Collectors, Co.      5\n",
       "3      Danish Wholesale Imports      5\n",
       "4    Down Under Souveniers, Inc      5"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head()"
   ]
  }
 ],
 "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
}