Getting_Started/SQL_Basics_in_Jupyter/SQL_Basics_in_Jupyter_SQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<header>\n",
    "   <p  style='font-size:36px;font-family:Arial; color:#F0F0F0; background-color: #00233c; padding-left: 20pt; padding-top: 20pt;padding-bottom: 10pt; padding-right: 20pt;'>\n",
    "       Vantage SQL Plugin Basics\n",
    "  <br>\n",
    "       <img id=\"teradata-logo\" src=\"https://storage.googleapis.com/clearscape_analytics_demo_data/DEMO_Logo/teradata.svg\" alt=\"Teradata\" style=\"width: 125px; height: auto; margin-top: 20pt;\">\n",
    "    </p>\n",
    "</header>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Welcome to this introductory guide. This guide will walk you through your first SQL queries.  A more extensive notebook with more commands can be found online in the Teradata Vantage Modules for Jupyter <a href = 'https://teradata.github.io/jupyterextensions/#/'>Here.</a></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>For help on available \"magic\" commands in the Teradata SQL plugin, run the following cell:</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%help"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Object Browser</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Included with the plugin is a browser that allows you to view the databases and tables on your ClearScape Analytics Experience platform.  Note that if you haven't run any demos or haven't used the data dictionary, you will only see system tables.</p>\n",
    "\n",
    "<ol style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Select <b>File/New Launcher</b> or if there is a launcher t to a launcher (new or existing)</li>\n",
    "<li>Click Navigator</li>\n",
    "<li>Select database profile</li>\n",
    "</ol>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Introduction to SQL</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>This page is a Jupyter Notebook. Looking at the top-right corner, you can see that it is running a *Teradata SQL* kernel. Other kernel include R and Python. This means that any command on this notebook will have to be a SQL statement.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The first step with any SQL editor or Jupyter Notebook is to connect to a Teradata system.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>If this notebook is being run from the Vantage LIVE image, three connections should already be set up; representing connections to Transcend Production, Transcend AWS, and Vantage LIVE (Azure).  The below command will list configured connections</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%lsconnect"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%connect local, hidewarnings=true"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>You are connected if the last command returned `Success:... connection established`</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The most simple SQL you can run is to calculate a formula, for example 2+2. Click on the cell below and press Shift-Return to execute its content. The result is saved in the Teradata directory mentioned above with the exact path below which can be accessed/downloaded from the file browser on the left.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT 2+5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now, let's run your first real SQL query. Click in the cell below and press Shift+Return to execute it. It will retrieve ten rows from a table named DBC.TablesV</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT TOP 5 * FROM DBC.TablesV;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    This is the simplest high-level SQL query. <b>SELECT</b> is a SQL keyword that starts the first section on defining the columns we wan to extract. <b>FROM</b> is a SQL keyword that starts the source data section. <b>DBC.TablesV</b> is the table we want to extract all the columns.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    Instead of \"shift+Enter\", one can also click on the <b>Execute</b> button (triangular Play button in the top toolbar) will run the SQL query on our Vantage environment</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>After a few seconds you will find the results in the result window. You can select any cell, type Ctrl-C to copy the data, and then Ctrl-P in Excel to paste the data for further analysis (filtering, pivoting, etc.).</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The results include the following columns:</p>\n",
    "\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>DatabaseName, name of the database a table is in</li>\n",
    "<li>TableName, name of the table</li>\n",
    "<li>Version</li>\n",
    "<li>TableKind</li>\n",
    "<li>ProtectionType</li>\n",
    "<li>JournalFlag</li>\n",
    "<li>CreatorName</li>\n",
    "<li>RequestText</li>\n",
    "<li>CommentString</li>\n",
    "<li>ParentCount</li>\n",
    "<li>ChildCount</li>\n",
    "<li>...</li>\n",
    "</ul>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>To select specific columns, we define them in the <b>SELECT</b> statement delimitated by a comma. For readability, we will enter each one on a new line and use a nice tabular form:</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT TOP 5 TableName, DatabaseName FROM DBC.TablesV;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Ordering</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can order the data by Name:</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT TOP 5 TableName, DatabaseName FROM DBC.TablesV ORDER BY TableName;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We added here the SQL keyword <b>ORDER BY</b> followed by the column on which we want to order the data.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>To order in descending order, we add the SQL keyword <b>DESC</b> next to the column name:</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT TOP 5 TableName, DatabaseName FROM DBC.TablesV ORDER BY TableName DESC;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can order using multiple columns, for example:</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT\n",
    "    TOP 5 *\n",
    "FROM\n",
    "    DBC.TablesV\n",
    "ORDER BY DatabaseName DESC, TableName "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Filtering</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can filter for specific value with the SQL keyword <b>WHERE</b>. For example:</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT TOP 5 * FROM DBC.TablesV\n",
    "WHERE TableKind = 'T'\n",
    "ORDER BY DatabaseName DESC, TableName "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We use the SQL keyword **AND** and **OR** with parenthesis to define any logic. For example, to select only the CreatorName of DBC OR SYSADM:</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT TOP 5 * FROM DBC.TablesV\n",
    "WHERE (TableKind = 'T' AND CreatorName = 'DBC')\n",
    "  OR (TableKind = 'T' AND CreatorName = 'SYSADM')\n",
    "ORDER BY DatabaseName DESC, TableName "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Aggregation</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can aggregate multiple rows together in a similar way as pivot tables work in Excel. For example, to count how many object (tables, views, macros, stored procedures, functions, etc) we have with the same name in Vantage, we would do:</p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT TOP 5 DatabaseName, count(*) as ObjectCount FROM DBC.TablesV\n",
    "GROUP BY DatabaseName  \n",
    "ORDER BY DatabaseName"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the <b>SELECT</b> example below, we use a sub query to aggregate object size within a database, then we use functions to get the minimum and maximum object size and count for each database.  (The counts are different from above because views and macros take no space).</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>There are multiple aggregation functions:</p>\n",
    "\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>SUM(X) to sum all the X values</li>\n",
    "<li>AVG(X) for the average of X</li>\n",
    "<li>MIN(X) for the smallest X value</li>\n",
    "<li>MAX(X) for the largest X value</li>\n",
    "</ul>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>For example:</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT\n",
    "    TOP 5 databasename,\n",
    "    MIN(TableBytes)  AS MinSize,\n",
    "    MAX(TableBytes)  AS MaxSize,\n",
    "    COUNT(*) AS #Objects_Using_Space\n",
    "FROM (\n",
    "    SELECT\n",
    "        DatabaseName,\n",
    "        TableName,\n",
    "        Sum(CurrentPerm) AS TableBytes   \n",
    "        FROM DBC.TablesizeV\n",
    "        GROUP By DatabaseName, Tablename\n",
    "    ) AS Subtotals \n",
    "GROUP BY DatabaseName order by DatabaseName;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>For courageous readers, you can also try the following aggregation functions:</p>\n",
    "\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<li>STDDEV_POP(X) and STDDEV_SAMP(X) to measure the standard deviation of the X distribution (for the full population, and for a sampled population)\n",
    "<li>VAR_POP(X) and VAR_SAMP(X) to measure the variance of the X distribution (for the full population, and for a sampled population)\n",
    "<li>COVAR_POP(X,Y) and COVAR_SAMP(X,Y) to measure the covariance between X and Y (for the full population, and for a sampled population)\n",
    "<li>CORR(X,Y) to measure the correlation between the columns X and Y\n",
    "<li>KURTOSIS(X) to measure the kurtosis of the X distribution **minus** 3 (the tailed ness of the distribution, it should be around 0 for a Normal/Gaussian distribution)\n",
    "<li>SKEW(X) to measure the skewness of the X distribution (the asymmetry of the distribution, it should be around 0 for a Normal/Gaussian distribution) \n",
    "</ul>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<footer style=\"padding-bottom:35px; background:#f9f9f9; border-bottom:3px solid #00233C\">\n",
    "    <div style=\"float:left;margin-top:14px\">ClearScape Analytics™</div>\n",
    "    <div style=\"float:right;\">\n",
    "        <div style=\"float:left; margin-top:14px\">\n",
    "            Copyright © Teradata Corporation - 2023. All Rights Reserved\n",
    "        </div>\n",
    "    </div>\n",
    "</footer>"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Teradata SQL",
   "language": "Teradata SQL",
   "name": "teradatasql"
  },
  "language_info": {
   "codemirror_mode": "Teradata SQL",
   "file_extension": ".tdrs",
   "mimetype": "application/vnd.teradata.resultset",
   "name": "Teradata SQL",
   "nbconvert_exporter": "",
   "pygments_lexer": "",
   "version": "16.20"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}