T-SQL windows copy.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os, urllib\n",
    "import pandas as pd\n",
    "import pyodbc\n",
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# This is what makes the magic functions work:\n",
    "\n",
    "%load_ext sql\n",
    "\n",
    "# %%sql to create a multi-line sql block that outputs a dataframe\n",
    "# %sql to execute a single line of sql that can go straight into a python variable.\n",
    "# Be sure to install the ipython-sql for this to work. "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create the connection string"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The obdc driver must be installed to make this work. The credentials reference a windows virtual machine running within a linux host computer."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = create_engine('mssql+pyodbc://Windows-10\\SQLEXPRESS/GBD?driver=SQL+Server')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['SQL Server',\n",
       " 'SQL Server Native Client RDA 11.0',\n",
       " 'ODBC Driver 17 for SQL Server',\n",
       " 'Microsoft Access Driver (*.mdb, *.accdb)',\n",
       " 'Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)',\n",
       " 'Microsoft Access Text Driver (*.txt, *.csv)']"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pyodbc.drivers()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connected!\n"
     ]
    }
   ],
   "source": [
    "conn = engine.connect()\n",
    "print('Connected!')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'MS Access Database': 'Microsoft Access Driver (*.mdb, *.accdb)',\n",
       " 'Excel Files': 'Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)'}"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pyodbc.dataSources()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>cause</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>297</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>298</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>302</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>321</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>322</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>328</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>329</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>332</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>337</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>338</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>339</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>340</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>341</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>342</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>345</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>346</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>347</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>350</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>351</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>352</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>353</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>357</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>358</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>359</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>360</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>365</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>366</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>380</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>387</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>391</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>393</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>400</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>408</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>411</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>414</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>417</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>423</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>426</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>429</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>432</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>435</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>438</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>441</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>444</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>447</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>450</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>453</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>456</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>459</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>462</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>465</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>468</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>471</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>474</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>477</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>480</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>483</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>484</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>485</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>486</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>487</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>490</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>492</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>493</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>494</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>498</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>499</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>500</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>501</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>502</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>503</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>504</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>509</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>510</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>515</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>516</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>520</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>521</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>529</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>530</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>531</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>532</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>533</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>534</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>535</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>541</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>543</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>544</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>545</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>546</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>554</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>557</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>560</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>561</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>572</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>587</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>588</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>589</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>594</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>603</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>613</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>619</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>627</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>639</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>641</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>665</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>668</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>686</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>689</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>695</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>697</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>698</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>699</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>700</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>704</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>708</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>709</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>712</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>716</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>718</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>724</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>729</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>842</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>843</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>854</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>935</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>945</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>958</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>959</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>980</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>992</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>1022</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>1023</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(297,),\n",
       " (298,),\n",
       " (302,),\n",
       " (321,),\n",
       " (322,),\n",
       " (328,),\n",
       " (329,),\n",
       " (332,),\n",
       " (337,),\n",
       " (338,),\n",
       " (339,),\n",
       " (340,),\n",
       " (341,),\n",
       " (342,),\n",
       " (345,),\n",
       " (346,),\n",
       " (347,),\n",
       " (350,),\n",
       " (351,),\n",
       " (352,),\n",
       " (353,),\n",
       " (357,),\n",
       " (358,),\n",
       " (359,),\n",
       " (360,),\n",
       " (365,),\n",
       " (366,),\n",
       " (380,),\n",
       " (387,),\n",
       " (391,),\n",
       " (393,),\n",
       " (400,),\n",
       " (408,),\n",
       " (411,),\n",
       " (414,),\n",
       " (417,),\n",
       " (423,),\n",
       " (426,),\n",
       " (429,),\n",
       " (432,),\n",
       " (435,),\n",
       " (438,),\n",
       " (441,),\n",
       " (444,),\n",
       " (447,),\n",
       " (450,),\n",
       " (453,),\n",
       " (456,),\n",
       " (459,),\n",
       " (462,),\n",
       " (465,),\n",
       " (468,),\n",
       " (471,),\n",
       " (474,),\n",
       " (477,),\n",
       " (480,),\n",
       " (483,),\n",
       " (484,),\n",
       " (485,),\n",
       " (486,),\n",
       " (487,),\n",
       " (490,),\n",
       " (492,),\n",
       " (493,),\n",
       " (494,),\n",
       " (498,),\n",
       " (499,),\n",
       " (500,),\n",
       " (501,),\n",
       " (502,),\n",
       " (503,),\n",
       " (504,),\n",
       " (509,),\n",
       " (510,),\n",
       " (515,),\n",
       " (516,),\n",
       " (520,),\n",
       " (521,),\n",
       " (529,),\n",
       " (530,),\n",
       " (531,),\n",
       " (532,),\n",
       " (533,),\n",
       " (534,),\n",
       " (535,),\n",
       " (541,),\n",
       " (543,),\n",
       " (544,),\n",
       " (545,),\n",
       " (546,),\n",
       " (554,),\n",
       " (557,),\n",
       " (560,),\n",
       " (561,),\n",
       " (572,),\n",
       " (587,),\n",
       " (588,),\n",
       " (589,),\n",
       " (594,),\n",
       " (603,),\n",
       " (613,),\n",
       " (619,),\n",
       " (627,),\n",
       " (639,),\n",
       " (641,),\n",
       " (665,),\n",
       " (668,),\n",
       " (686,),\n",
       " (689,),\n",
       " (695,),\n",
       " (697,),\n",
       " (698,),\n",
       " (699,),\n",
       " (700,),\n",
       " (704,),\n",
       " (708,),\n",
       " (709,),\n",
       " (712,),\n",
       " (716,),\n",
       " (718,),\n",
       " (724,),\n",
       " (729,),\n",
       " (842,),\n",
       " (843,),\n",
       " (854,),\n",
       " (935,),\n",
       " (945,),\n",
       " (958,),\n",
       " (959,),\n",
       " (980,),\n",
       " (992,),\n",
       " (1022,),\n",
       " (1023,)]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql mssql+pyodbc://Windows-10\\SQLEXPRESS/GBD?driver=SQL+Server\n",
    "\n",
    "SELECT DISTINCT(cause) AS cause\n",
    "FROM causes\n",
    "ORDER BY cause ASC"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Select the top 10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mssql+pyodbc://Windows-10\\SQLEXPRESS/GBD?driver=SQL+Server\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>measure</th>\n",
       "            <th>location</th>\n",
       "            <th>sex</th>\n",
       "            <th>age</th>\n",
       "            <th>cause</th>\n",
       "            <th>metric</th>\n",
       "            <th>year</th>\n",
       "            <th>val</th>\n",
       "            <th>upper</th>\n",
       "            <th>lower</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "            <td>51</td>\n",
       "            <td>1</td>\n",
       "            <td>28</td>\n",
       "            <td>520</td>\n",
       "            <td>1</td>\n",
       "            <td>2010</td>\n",
       "            <td>0.0</td>\n",
       "            <td>0.0</td>\n",
       "            <td>0.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "            <td>51</td>\n",
       "            <td>2</td>\n",
       "            <td>28</td>\n",
       "            <td>520</td>\n",
       "            <td>1</td>\n",
       "            <td>2010</td>\n",
       "            <td>0.0</td>\n",
       "            <td>0.0</td>\n",
       "            <td>0.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "            <td>51</td>\n",
       "            <td>1</td>\n",
       "            <td>30</td>\n",
       "            <td>520</td>\n",
       "            <td>1</td>\n",
       "            <td>2010</td>\n",
       "            <td>1.7984070777893066</td>\n",
       "            <td>4.949845314025879</td>\n",
       "            <td>1.3105658292770386</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "            <td>51</td>\n",
       "            <td>2</td>\n",
       "            <td>30</td>\n",
       "            <td>520</td>\n",
       "            <td>1</td>\n",
       "            <td>2010</td>\n",
       "            <td>2.7558300495147705</td>\n",
       "            <td>6.83079719543457</td>\n",
       "            <td>1.7773444652557373</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "            <td>51</td>\n",
       "            <td>1</td>\n",
       "            <td>31</td>\n",
       "            <td>520</td>\n",
       "            <td>1</td>\n",
       "            <td>2010</td>\n",
       "            <td>1.2737950086593628</td>\n",
       "            <td>3.0925118923187256</td>\n",
       "            <td>0.9484407901763916</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "            <td>51</td>\n",
       "            <td>2</td>\n",
       "            <td>31</td>\n",
       "            <td>520</td>\n",
       "            <td>1</td>\n",
       "            <td>2010</td>\n",
       "            <td>2.868278980255127</td>\n",
       "            <td>7.565248966217041</td>\n",
       "            <td>1.8214061260223389</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "            <td>51</td>\n",
       "            <td>1</td>\n",
       "            <td>32</td>\n",
       "            <td>520</td>\n",
       "            <td>1</td>\n",
       "            <td>2010</td>\n",
       "            <td>1.0159968137741089</td>\n",
       "            <td>1.2909469604492188</td>\n",
       "            <td>0.6816396713256836</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "            <td>51</td>\n",
       "            <td>2</td>\n",
       "            <td>32</td>\n",
       "            <td>520</td>\n",
       "            <td>1</td>\n",
       "            <td>2010</td>\n",
       "            <td>1.8282191753387451</td>\n",
       "            <td>3.8931028842926025</td>\n",
       "            <td>1.143686056137085</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "            <td>51</td>\n",
       "            <td>1</td>\n",
       "            <td>37</td>\n",
       "            <td>520</td>\n",
       "            <td>1</td>\n",
       "            <td>2010</td>\n",
       "            <td>27.653823852539062</td>\n",
       "            <td>73.91763305664062</td>\n",
       "            <td>22.006675720214844</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "            <td>51</td>\n",
       "            <td>2</td>\n",
       "            <td>37</td>\n",
       "            <td>520</td>\n",
       "            <td>1</td>\n",
       "            <td>2010</td>\n",
       "            <td>22.19183921813965</td>\n",
       "            <td>56.39546203613281</td>\n",
       "            <td>15.032441139221191</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 51, 1, 28, 520, 1, 2010, 0.0, 0.0, 0.0),\n",
       " (1, 51, 2, 28, 520, 1, 2010, 0.0, 0.0, 0.0),\n",
       " (1, 51, 1, 30, 520, 1, 2010, 1.7984070777893066, 4.949845314025879, 1.3105658292770386),\n",
       " (1, 51, 2, 30, 520, 1, 2010, 2.7558300495147705, 6.83079719543457, 1.7773444652557373),\n",
       " (1, 51, 1, 31, 520, 1, 2010, 1.2737950086593628, 3.0925118923187256, 0.9484407901763916),\n",
       " (1, 51, 2, 31, 520, 1, 2010, 2.868278980255127, 7.565248966217041, 1.8214061260223389),\n",
       " (1, 51, 1, 32, 520, 1, 2010, 1.0159968137741089, 1.2909469604492188, 0.6816396713256836),\n",
       " (1, 51, 2, 32, 520, 1, 2010, 1.8282191753387451, 3.8931028842926025, 1.143686056137085),\n",
       " (1, 51, 1, 37, 520, 1, 2010, 27.653823852539062, 73.91763305664062, 22.006675720214844),\n",
       " (1, 51, 2, 37, 520, 1, 2010, 22.19183921813965, 56.39546203613281, 15.032441139221191)]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "\n",
    "SELECT TOP 10 * \n",
    "FROM causes\n",
    "\n",
    "#diff"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Use GROUP BY to aggregate data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT sex, sum(val)\n",
    "FROM causes\n",
    "GROUP BY sex"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Filter using WHERE and a CONDITIONAL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT TOP 10 location, val\n",
    "FROM causes\n",
    "WHERE val < 10 AND val > 8\n",
    "ORDER BY val DESC"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Perform a LEFT JOIN on multiple fields"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT TOP 10 cau.*, cause_name, age_group_name, sex_label\n",
    "FROM causes cau\n",
    "LEFT JOIN cause_id cid ON cid.cause_id = cau.cause\n",
    "LEFT JOIN age_id age ON age.age_group_id = cau.age\n",
    "LEFT JOIN sex_id sex ON sex.sex_id = cau.sex"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Use BETWEEN to limit the number of CONDITIONAL statements"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "UsageError: Cell magic `%%sql` not found.\n"
     ]
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT TOP 10 cid.cause_name, sum(val) as val\n",
    "FROM causes AS cau\n",
    "LEFT JOIN cause_id AS cid ON cid.cause_id = cau.cause\n",
    "LEFT JOIN age_id AS age ON age.age_group_id = cau.age\n",
    "LEFT JOIN sex_id AS sex ON sex.sex_id = cau.sex\n",
    "WHERE cau.year = 2019 AND age.age_group_id BETWEEN 2 AND 21\n",
    "GROUP BY cid.cause_name\n",
    "ORDER BY val DESC\n",
    "\n",
    "# The 'AS' clauses are not necessary, but they make the code more readable\n",
    "# The BETWEEN statement is inclusive, so 2 and 21 are included in the query. "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Use PARENTHESES in CONDITIONAL statements"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT TOP 50 cau.*, cause_name, age_group_name, sex_label\n",
    "FROM causes cau\n",
    "LEFT JOIN cause_id cid ON cid.cause_id = cau.cause\n",
    "LEFT JOIN age_id age ON age.age_group_id = cau.age\n",
    "LEFT JOIN sex_id sex ON sex.sex_id = cau.sex\n",
    "WHERE (cause = 520 OR cause = 3) AND age = 3"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this case, the parentheses change the logic so that the OR operator is evaluated before the AND operator. Without them, AND would be evaluated before OR, in effect meaning that cause = 3 and age = 3 would be grouped together, and cause = 520 would be evaluated alone. \n",
    "\n",
    "<a href='https://learn.microsoft.com/en-us/sql/t-sql/language-elements/operator-precedence-transact-sql?view=sql-server-ver16'>Here</a> is the order of operations for T-SQL."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create a VIEW"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "UsageError: Cell magic `%%sql` not found.\n"
     ]
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "CREATE VIEW temp_view\n",
    "AS\n",
    "SELECT TOP 10 * \n",
    "FROM causes"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Alter a VIEW"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "ALTER VIEW temp_view\n",
    "AS\n",
    "SELECT TOP 50 * \n",
    "FROM causes"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This effectively overwrites the view."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### DROP a VIEW"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "DROP VIEW temp_view"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### CREATE a TABLE using SQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "CREATE TABLE temp_table \n",
    "(\n",
    "\tcolumn1 varchar(10),\n",
    "\tcolumn2 varchar(10),\n",
    "\tcolumn3 float\n",
    ")\n",
    "\n",
    "# column names are followed by data types"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### INSERT VALUES INTO TABLE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "INSERT INTO temp_table (column1, column2, column3)\n",
    "VALUES \n",
    "\t('a','b', 1.1),\n",
    "    ('c','d', 2.2),\n",
    "    ('e','f', 3.3)\n",
    "\n",
    "# make sure that the text has quotes"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### CREATE a TABLE from an existing TABLE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT TOP 10 *\n",
    "INTO temporary_table\n",
    "FROM causes\n",
    "\n",
    "#diff"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### INSERT values from one table INTO another"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Environment variable $DATABASE_URL not set, and no connect string given.\n",
      "Connection info needed in SQLAlchemy format, example:\n",
      "               postgresql://username:password@hostname/dbname\n",
      "               or an existing connection: dict_keys([])\n"
     ]
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "INSERT INTO causes\n",
    "SELECT * FROM causes_for_insertion"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### CREATE COLUMNS using ALTER TABLE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "ALTER TABLE temporary_table\n",
    "ADD rounded float, \n",
    "    times3 float, \n",
    "    over2 float"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add values to COLUMNS using UPDATE and SET"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "UPDATE temporary_table\n",
    "SET rounded = ROUND(VAL,2), \n",
    "\ttimes3 = val*3, \n",
    "\tover2 = val/2"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### DROP COLUMNS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "ALTER TABLE temporary_table\n",
    "DROP COLUMN rounded, \n",
    "            times3, \n",
    "            over2"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### DROP TABLE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "UsageError: Cell magic `%%sql` not found.\n"
     ]
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "DROP TABLE temp_table;\n",
    "DROP TABLE temporary_table\n",
    "\n",
    "# contains 2 separate commands separated by semi-colon"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Replace NULL values using UPDATE and SET"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "UsageError: Cell magic `%%sql` not found.\n"
     ]
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "UPDATE temporary_table\n",
    "SET measure_id = 0\n",
    "WHERE measure_id IS NULL"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### DELETE table values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "DELETE FROM temporary_table\n",
    "WHERE measure_id = 0"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Use LIKE to find numbers that are approximate to the given value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT *\n",
    "FROM causes\n",
    "WHERE val LIKE 345"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Use an ALIAS column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT AVG(val) AS average\n",
    "FROM causes\n",
    "\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Use a SUBQUERY"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Additional Differences between Standard SQL and T-SQL\n",
    "\n",
    "https://learnsql.com/blog/14-differences-sql-vs-tsql/"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Naming Conventions\n",
    "\n",
    "In T-SQL, brackets can be used to designate tables, views, and columns with spacces or special characters. Quotation marks can also be used as is done in standard SQL."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql \n",
    "\n",
    "SELECT [Cause ID]\n",
    "FROM cause_hier"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Returns\n",
    "\n",
    "T-SQL can return values without a reference to a table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Environment variable $DATABASE_URL not set, and no connect string given.\n",
      "Connection info needed in SQLAlchemy format, example:\n",
      "               postgresql://username:password@hostname/dbname\n",
      "               or an existing connection: dict_keys([])\n"
     ]
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT 27/9"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### LIMITS\n",
    "\n",
    "T-SQL, uses a different syntax to limit the number of rows displayed. Either 'TOP' can be used, as was exemplified earlier, or this syntax below. OFFSET is required for the query to run. It designates the number of rows to skip before displaying the results. ORDER BY is also required."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT *\n",
    "FROM causes\n",
    "ORDER BY cause OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "-- equivalent syntax using top:\n",
    "\n",
    "SELECT TOP 10 *\n",
    "FROM causes"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### IDENTITY Column\n",
    "\n",
    "SQL can be used to create columns that are automatically populated with values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "--Standard SQL syntax\n",
    "\n",
    "CREATE TABLE testTable (id DECIMAL GENERATED ALWAYS AS IDENTITY)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "--T-SQL syntax\n",
    "\n",
    "CREATE TABLE testTable (id INTEGER IDENTITY)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "HighPython",
   "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.10.9"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}