9-1_Intro_SQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Lecture 9-1\n",
    "\n",
    "# Intro to SQL\n",
    "\n",
    "## Week 9 Monday\n",
    "\n",
    "## Miles Chen, PhD"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": []
   },
   "source": [
    "# Do not try to set up your own SQL Server\n",
    "\n",
    "There is a big difference between setting up a SQL server and learning how to write a few queries.\n",
    "\n",
    "We will learn how to write a few queries.\n",
    "\n",
    "We will not learn how to set up a server and design a database. Most likely, if you go into the workforce and they list SQL as a requirement, they need someone who can write queries. The person who sets up, designs, and maintains the server will be a database administrator."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": []
   },
   "source": [
    "# Learning more\n",
    "\n",
    "Recommended exercises: <https://www.w3resource.com/sql-exercises/>\n",
    "\n",
    "Another place to just practice SQL queries: <http://sqlfiddle.com>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": []
   },
   "source": [
    "# sqlalchemy"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "sqlalchemy allows us to connect and interact with databases from within Python\n",
    "\n",
    "Most of your SQL experience will be with connecting to a database that already exists. Most data analysts / data scientists are not the data base administrator and this is not a database administration course.\n",
    "\n",
    "You can download the chinook database file from the chinook database github:\n",
    "\n",
    "https://github.com/lerocha/chinook-database/tree/master/ChinookDatabase/DataSources"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create_engine creates a connection to an existing database\n",
    "# I have 'Chinook_sqlite.sqlite' downloaded into my folder, and python\n",
    "# connects to this database\n",
    "engine = create_engine('sqlite:///Chinook_Sqlite.sqlite')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import inspect\n",
    "insp = inspect(engine) # creates an inspector"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Album', 'Artist', 'Customer', 'Employee', 'Genre', 'Invoice', 'InvoiceLine', 'MediaType', 'Playlist', 'PlaylistTrack', 'Track']\n"
     ]
    }
   ],
   "source": [
    "# Use the inspector to get table names\n",
    "# Save the table names to a list: table_names\n",
    "table_names = insp.get_table_names()\n",
    "\n",
    "# Print the table names to the shell\n",
    "print(table_names)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": []
   },
   "source": [
    "## Basics\n",
    "\n",
    "SQL is generally not case sensitive. Convention, however, puts SQL commands in ALL-CAPS and then leaves column and variable names in the same case as they appear in the table.\n",
    "\n",
    "semi-colons are not required to terminate SQL queries, but their usage is recommended.\n",
    "\n",
    "### `SELECT`\n",
    "\n",
    "`SELECT` is used to select variables from a given table. To select all columns, use `*`\n",
    "\n",
    "### `FROM`\n",
    "\n",
    "`FROM` specifies which table to select from."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": []
   },
   "source": [
    "Once you create the database engine with `sqlalchemy`, we can begin executing SQL queries by establishing a connection with the database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   AlbumId                                  Title  ArtistId\n",
      "0        1  For Those About To Rock We Salute You         1\n",
      "1        2                      Balls to the Wall         2\n",
      "2        3                      Restless and Wild         2\n",
      "3        4                      Let There Be Rock         1\n",
      "4        5                               Big Ones         3\n",
      "5        6                     Jagged Little Pill         4\n",
      "6        7                               Facelift         5\n",
      "7        8                         Warner 25 Anos         6\n",
      "8        9         Plays Metallica By Four Cellos         7\n",
      "9       10                             Audioslave         8\n",
      "RMKeyView(['AlbumId', 'Title', 'ArtistId'])\n"
     ]
    }
   ],
   "source": [
    "# Open engine connection\n",
    "con = engine.connect()\n",
    "\n",
    "# Perform query and store results in rs\n",
    "# this will select all columns from the Album table\n",
    "rs = con.execute(text('SELECT * FROM Album;'))\n",
    "\n",
    "# Fetch all results of the query and save to DataFrame\n",
    "df = pd.DataFrame(rs.fetchall())\n",
    "\n",
    "# Close the connection to the engine\n",
    "con.close()\n",
    "\n",
    "# Print head of query results\n",
    "print(df.head(10))\n",
    "print(rs.keys())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": []
   },
   "source": [
    "Instead of having to open and close the engine connection, we can use Python's with statement which will automatically open and close the connection for us"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  FirstName  LastName                Title\n",
      "0    Andrew     Adams      General Manager\n",
      "1     Nancy   Edwards        Sales Manager\n",
      "2      Jane   Peacock  Sales Support Agent\n",
      "3  Margaret      Park  Sales Support Agent\n",
      "4     Steve   Johnson  Sales Support Agent\n",
      "5   Michael  Mitchell           IT Manager\n",
      "6    Robert      King             IT Staff\n",
      "7     Laura  Callahan             IT Staff\n"
     ]
    }
   ],
   "source": [
    "# We can write our SQL command across multiple lines\n",
    "# enclosed in triple quotes\n",
    "command = '''\n",
    "SELECT FirstName, LastName, Title \n",
    "FROM Employee;\n",
    "'''\n",
    "\n",
    "# SELECT chooses the desired columns\n",
    "# FROM indicates the table to query\n",
    "\n",
    "with engine.connect() as con:\n",
    "    rs = con.execute(text(command))\n",
    "    df = pd.DataFrame(rs.fetchall())\n",
    "    df.columns = rs.keys()\n",
    "\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": []
   },
   "source": [
    "Pandas offers functionality to directly query a SQL database using an existing engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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",
       "      <th>Title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Andrew</td>\n",
       "      <td>Adams</td>\n",
       "      <td>General Manager</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Nancy</td>\n",
       "      <td>Edwards</td>\n",
       "      <td>Sales Manager</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Jane</td>\n",
       "      <td>Peacock</td>\n",
       "      <td>Sales Support Agent</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Margaret</td>\n",
       "      <td>Park</td>\n",
       "      <td>Sales Support Agent</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Steve</td>\n",
       "      <td>Johnson</td>\n",
       "      <td>Sales Support Agent</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Michael</td>\n",
       "      <td>Mitchell</td>\n",
       "      <td>IT Manager</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Robert</td>\n",
       "      <td>King</td>\n",
       "      <td>IT Staff</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Laura</td>\n",
       "      <td>Callahan</td>\n",
       "      <td>IT Staff</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  FirstName  LastName                Title\n",
       "0    Andrew     Adams      General Manager\n",
       "1     Nancy   Edwards        Sales Manager\n",
       "2      Jane   Peacock  Sales Support Agent\n",
       "3  Margaret      Park  Sales Support Agent\n",
       "4     Steve   Johnson  Sales Support Agent\n",
       "5   Michael  Mitchell           IT Manager\n",
       "6    Robert      King             IT Staff\n",
       "7     Laura  Callahan             IT Staff"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# we can use the same command as earlier:\n",
    "query = text(command)\n",
    "conn = engine.connect()\n",
    "df = pd.read_sql_query(query, conn)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": []
   },
   "source": [
    "## `ORDER BY`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "ORDER BY is SQL's version of sort\n",
    "\n",
    "<https://www.w3schools.com/sql/sql_orderby.asp>\n",
    "\n",
    "```\n",
    "SELECT column1, column2, ...\n",
    "FROM table_name\n",
    "ORDER BY column1, column2, ... ASC|DESC;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": []
   },
   "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>EmployeeId</th>\n",
       "      <th>LastName</th>\n",
       "      <th>FirstName</th>\n",
       "      <th>Title</th>\n",
       "      <th>ReportsTo</th>\n",
       "      <th>BirthDate</th>\n",
       "      <th>HireDate</th>\n",
       "      <th>Address</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>Country</th>\n",
       "      <th>PostalCode</th>\n",
       "      <th>Phone</th>\n",
       "      <th>Fax</th>\n",
       "      <th>Email</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td>Peacock</td>\n",
       "      <td>Jane</td>\n",
       "      <td>Sales Support Agent</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1973-08-29 00:00:00</td>\n",
       "      <td>2002-04-01 00:00:00</td>\n",
       "      <td>1111 6 Ave SW</td>\n",
       "      <td>Calgary</td>\n",
       "      <td>AB</td>\n",
       "      <td>Canada</td>\n",
       "      <td>T2P 5M5</td>\n",
       "      <td>+1 (403) 262-3443</td>\n",
       "      <td>+1 (403) 262-6712</td>\n",
       "      <td>jane@chinookcorp.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6</td>\n",
       "      <td>Mitchell</td>\n",
       "      <td>Michael</td>\n",
       "      <td>IT Manager</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1973-07-01 00:00:00</td>\n",
       "      <td>2003-10-17 00:00:00</td>\n",
       "      <td>5827 Bowness Road NW</td>\n",
       "      <td>Calgary</td>\n",
       "      <td>AB</td>\n",
       "      <td>Canada</td>\n",
       "      <td>T3B 0C5</td>\n",
       "      <td>+1 (403) 246-9887</td>\n",
       "      <td>+1 (403) 246-9899</td>\n",
       "      <td>michael@chinookcorp.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7</td>\n",
       "      <td>King</td>\n",
       "      <td>Robert</td>\n",
       "      <td>IT Staff</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1970-05-29 00:00:00</td>\n",
       "      <td>2004-01-02 00:00:00</td>\n",
       "      <td>590 Columbia Boulevard West</td>\n",
       "      <td>Lethbridge</td>\n",
       "      <td>AB</td>\n",
       "      <td>Canada</td>\n",
       "      <td>T1K 5N8</td>\n",
       "      <td>+1 (403) 456-9986</td>\n",
       "      <td>+1 (403) 456-8485</td>\n",
       "      <td>robert@chinookcorp.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8</td>\n",
       "      <td>Callahan</td>\n",
       "      <td>Laura</td>\n",
       "      <td>IT Staff</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1968-01-09 00:00:00</td>\n",
       "      <td>2004-03-04 00:00:00</td>\n",
       "      <td>923 7 ST NW</td>\n",
       "      <td>Lethbridge</td>\n",
       "      <td>AB</td>\n",
       "      <td>Canada</td>\n",
       "      <td>T1H 1Y8</td>\n",
       "      <td>+1 (403) 467-3351</td>\n",
       "      <td>+1 (403) 467-8772</td>\n",
       "      <td>laura@chinookcorp.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Johnson</td>\n",
       "      <td>Steve</td>\n",
       "      <td>Sales Support Agent</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1965-03-03 00:00:00</td>\n",
       "      <td>2003-10-17 00:00:00</td>\n",
       "      <td>7727B 41 Ave</td>\n",
       "      <td>Calgary</td>\n",
       "      <td>AB</td>\n",
       "      <td>Canada</td>\n",
       "      <td>T3B 1Y7</td>\n",
       "      <td>1 (780) 836-9987</td>\n",
       "      <td>1 (780) 836-9543</td>\n",
       "      <td>steve@chinookcorp.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>Adams</td>\n",
       "      <td>Andrew</td>\n",
       "      <td>General Manager</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1962-02-18 00:00:00</td>\n",
       "      <td>2002-08-14 00:00:00</td>\n",
       "      <td>11120 Jasper Ave NW</td>\n",
       "      <td>Edmonton</td>\n",
       "      <td>AB</td>\n",
       "      <td>Canada</td>\n",
       "      <td>T5K 2N1</td>\n",
       "      <td>+1 (780) 428-9482</td>\n",
       "      <td>+1 (780) 428-3457</td>\n",
       "      <td>andrew@chinookcorp.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2</td>\n",
       "      <td>Edwards</td>\n",
       "      <td>Nancy</td>\n",
       "      <td>Sales Manager</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1958-12-08 00:00:00</td>\n",
       "      <td>2002-05-01 00:00:00</td>\n",
       "      <td>825 8 Ave SW</td>\n",
       "      <td>Calgary</td>\n",
       "      <td>AB</td>\n",
       "      <td>Canada</td>\n",
       "      <td>T2P 2T3</td>\n",
       "      <td>+1 (403) 262-3443</td>\n",
       "      <td>+1 (403) 262-3322</td>\n",
       "      <td>nancy@chinookcorp.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>4</td>\n",
       "      <td>Park</td>\n",
       "      <td>Margaret</td>\n",
       "      <td>Sales Support Agent</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1947-09-19 00:00:00</td>\n",
       "      <td>2003-05-03 00:00:00</td>\n",
       "      <td>683 10 Street SW</td>\n",
       "      <td>Calgary</td>\n",
       "      <td>AB</td>\n",
       "      <td>Canada</td>\n",
       "      <td>T2P 5G3</td>\n",
       "      <td>+1 (403) 263-4423</td>\n",
       "      <td>+1 (403) 263-4289</td>\n",
       "      <td>margaret@chinookcorp.com</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   EmployeeId  LastName  ...                Fax                     Email\n",
       "0           3   Peacock  ...  +1 (403) 262-6712      jane@chinookcorp.com\n",
       "1           6  Mitchell  ...  +1 (403) 246-9899   michael@chinookcorp.com\n",
       "2           7      King  ...  +1 (403) 456-8485    robert@chinookcorp.com\n",
       "3           8  Callahan  ...  +1 (403) 467-8772     laura@chinookcorp.com\n",
       "4           5   Johnson  ...   1 (780) 836-9543     steve@chinookcorp.com\n",
       "5           1     Adams  ...  +1 (780) 428-3457    andrew@chinookcorp.com\n",
       "6           2   Edwards  ...  +1 (403) 262-3322     nancy@chinookcorp.com\n",
       "7           4      Park  ...  +1 (403) 263-4289  margaret@chinookcorp.com\n",
       "\n",
       "[8 rows x 15 columns]"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "command = '''\n",
    "SELECT * \n",
    "FROM Employee \n",
    "ORDER BY Birthdate DESC;\n",
    "'''\n",
    "pd.read_sql_query(text(command), conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": []
   },
   "source": [
    "## `WHERE`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Filter row selection with WHERE. (similar to using if as a boolean mask)\n",
    "\n",
    "SQL uses single equal sign = for comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>EmployeeId</th>\n",
       "      <th>LastName</th>\n",
       "      <th>FirstName</th>\n",
       "      <th>Title</th>\n",
       "      <th>ReportsTo</th>\n",
       "      <th>BirthDate</th>\n",
       "      <th>HireDate</th>\n",
       "      <th>Address</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>Country</th>\n",
       "      <th>PostalCode</th>\n",
       "      <th>Phone</th>\n",
       "      <th>Fax</th>\n",
       "      <th>Email</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8</td>\n",
       "      <td>Callahan</td>\n",
       "      <td>Laura</td>\n",
       "      <td>IT Staff</td>\n",
       "      <td>6</td>\n",
       "      <td>1968-01-09 00:00:00</td>\n",
       "      <td>2004-03-04 00:00:00</td>\n",
       "      <td>923 7 ST NW</td>\n",
       "      <td>Lethbridge</td>\n",
       "      <td>AB</td>\n",
       "      <td>Canada</td>\n",
       "      <td>T1H 1Y8</td>\n",
       "      <td>+1 (403) 467-3351</td>\n",
       "      <td>+1 (403) 467-8772</td>\n",
       "      <td>laura@chinookcorp.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7</td>\n",
       "      <td>King</td>\n",
       "      <td>Robert</td>\n",
       "      <td>IT Staff</td>\n",
       "      <td>6</td>\n",
       "      <td>1970-05-29 00:00:00</td>\n",
       "      <td>2004-01-02 00:00:00</td>\n",
       "      <td>590 Columbia Boulevard West</td>\n",
       "      <td>Lethbridge</td>\n",
       "      <td>AB</td>\n",
       "      <td>Canada</td>\n",
       "      <td>T1K 5N8</td>\n",
       "      <td>+1 (403) 456-9986</td>\n",
       "      <td>+1 (403) 456-8485</td>\n",
       "      <td>robert@chinookcorp.com</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   EmployeeId  LastName  ...                Fax                   Email\n",
       "0           8  Callahan  ...  +1 (403) 467-8772   laura@chinookcorp.com\n",
       "1           7      King  ...  +1 (403) 456-8485  robert@chinookcorp.com\n",
       "\n",
       "[2 rows x 15 columns]"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "command = '''\n",
    "SELECT * \n",
    "FROM Employee \n",
    "WHERE EmployeeId >= 6 AND Title = 'IT Staff'\n",
    "ORDER BY BirthDate;\n",
    "'''\n",
    "pd.read_sql_query(text(command), conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": []
   },
   "source": [
    "### `JOIN` and `LIMIT`\n",
    "\n",
    "We can look at data across multiple tables using a `JOIN`\n",
    "\n",
    "`LIMIT` acts like \"head()\", and limits the number of entries it returns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>AlbumId</th>\n",
       "      <th>Title</th>\n",
       "      <th>ArtistId</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>For Those About To Rock We Salute You</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Balls to the Wall</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Restless and Wild</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Let There Be Rock</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Big Ones</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>Jagged Little Pill</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>Facelift</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>Warner 25 Anos</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>Plays Metallica By Four Cellos</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>Audioslave</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   AlbumId                                  Title  ArtistId\n",
       "0        1  For Those About To Rock We Salute You         1\n",
       "1        2                      Balls to the Wall         2\n",
       "2        3                      Restless and Wild         2\n",
       "3        4                      Let There Be Rock         1\n",
       "4        5                               Big Ones         3\n",
       "5        6                     Jagged Little Pill         4\n",
       "6        7                               Facelift         5\n",
       "7        8                         Warner 25 Anos         6\n",
       "8        9         Plays Metallica By Four Cellos         7\n",
       "9       10                             Audioslave         8"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "command = '''\n",
    "SELECT * \n",
    "FROM Album\n",
    "LIMIT 10;\n",
    "'''\n",
    "pd.read_sql_query(text(command), conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": []
   },
   "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>ArtistId</th>\n",
       "      <th>Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>AC/DC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Accept</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Aerosmith</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Alanis Morissette</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Alice In Chains</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>Antônio Carlos Jobim</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>Apocalyptica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>Audioslave</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>BackBeat</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>Billy Cobham</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ArtistId                  Name\n",
       "0         1                 AC/DC\n",
       "1         2                Accept\n",
       "2         3             Aerosmith\n",
       "3         4     Alanis Morissette\n",
       "4         5       Alice In Chains\n",
       "5         6  Antônio Carlos Jobim\n",
       "6         7          Apocalyptica\n",
       "7         8            Audioslave\n",
       "8         9              BackBeat\n",
       "9        10          Billy Cobham"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "command = '''\n",
    "SELECT * \n",
    "FROM Artist\n",
    "LIMIT 10;\n",
    "'''\n",
    "pd.read_sql_query(text(command), conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": []
   },
   "source": [
    "`JOIN`\n",
    "\n",
    "`INNER JOIN` is a specific type of join. It keeps only rows where the key exists in both tables. If one table is missing an entry that exists in the other table, the entry will not be returned.\n",
    "\n",
    "When using a `JOIN`, specify the name of the table that is being joined and the columns used to match the rows. Columns are specified with dot notation. `TableName.ColumnName`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "tags": []
   },
   "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>AlbumId</th>\n",
       "      <th>Title</th>\n",
       "      <th>ArtistId</th>\n",
       "      <th>ArtistId</th>\n",
       "      <th>Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>For Those About To Rock We Salute You</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>AC/DC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Balls to the Wall</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>Accept</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Restless and Wild</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>Accept</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Let There Be Rock</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>AC/DC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Big Ones</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>Aerosmith</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>Jagged Little Pill</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>Alanis Morissette</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>Facelift</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>Alice In Chains</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>Warner 25 Anos</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>Antônio Carlos Jobim</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>Plays Metallica By Four Cellos</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>Apocalyptica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>Audioslave</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>Audioslave</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   AlbumId  ...                  Name\n",
       "0        1  ...                 AC/DC\n",
       "1        2  ...                Accept\n",
       "2        3  ...                Accept\n",
       "3        4  ...                 AC/DC\n",
       "4        5  ...             Aerosmith\n",
       "5        6  ...     Alanis Morissette\n",
       "6        7  ...       Alice In Chains\n",
       "7        8  ...  Antônio Carlos Jobim\n",
       "8        9  ...          Apocalyptica\n",
       "9       10  ...            Audioslave\n",
       "\n",
       "[10 rows x 5 columns]"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "command = '''\n",
    "SELECT * \n",
    "FROM Album\n",
    "INNER JOIN Artist ON Album.ArtistId = Artist.ArtistId\n",
    "LIMIT 10;\n",
    "'''\n",
    "pd.read_sql_query(text(command), conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": []
   },
   "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>Album Title</th>\n",
       "      <th>Artist Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>For Those About To Rock We Salute You</td>\n",
       "      <td>AC/DC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Balls to the Wall</td>\n",
       "      <td>Accept</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Restless and Wild</td>\n",
       "      <td>Accept</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Let There Be Rock</td>\n",
       "      <td>AC/DC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Big Ones</td>\n",
       "      <td>Aerosmith</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Jagged Little Pill</td>\n",
       "      <td>Alanis Morissette</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Facelift</td>\n",
       "      <td>Alice In Chains</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Warner 25 Anos</td>\n",
       "      <td>Antônio Carlos Jobim</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Plays Metallica By Four Cellos</td>\n",
       "      <td>Apocalyptica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Audioslave</td>\n",
       "      <td>Audioslave</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             Album Title           Artist Name\n",
       "0  For Those About To Rock We Salute You                 AC/DC\n",
       "1                      Balls to the Wall                Accept\n",
       "2                      Restless and Wild                Accept\n",
       "3                      Let There Be Rock                 AC/DC\n",
       "4                               Big Ones             Aerosmith\n",
       "5                     Jagged Little Pill     Alanis Morissette\n",
       "6                               Facelift       Alice In Chains\n",
       "7                         Warner 25 Anos  Antônio Carlos Jobim\n",
       "8         Plays Metallica By Four Cellos          Apocalyptica\n",
       "9                             Audioslave            Audioslave"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# you can rename columns using `AS`\n",
    "command = '''\n",
    "SELECT Title AS \"Album Title\", Name AS \"Artist Name\"\n",
    "FROM Album\n",
    "INNER JOIN Artist ON Album.ArtistId = Artist.ArtistId\n",
    "LIMIT 10;\n",
    "'''\n",
    "pd.read_sql_query(text(command), conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": []
   },
   "source": [
    "`GROUP BY` can be used to create groups to help calculate summary values\n",
    "\n",
    "`COUNT()` is one function that can be used to calculate summary values. Other summary functions include `SUM()` and `AVG()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": []
   },
   "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>ArtistId</th>\n",
       "      <th>Name</th>\n",
       "      <th>album_count</th>\n",
       "      <th>avg_id</th>\n",
       "      <th>sum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>AC/DC</td>\n",
       "      <td>2</td>\n",
       "      <td>2.500000</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Accept</td>\n",
       "      <td>2</td>\n",
       "      <td>2.500000</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Aerosmith</td>\n",
       "      <td>1</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Alanis Morissette</td>\n",
       "      <td>1</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Alice In Chains</td>\n",
       "      <td>1</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>Antônio Carlos Jobim</td>\n",
       "      <td>2</td>\n",
       "      <td>21.000000</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>Apocalyptica</td>\n",
       "      <td>1</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>Audioslave</td>\n",
       "      <td>3</td>\n",
       "      <td>97.333333</td>\n",
       "      <td>292</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>BackBeat</td>\n",
       "      <td>1</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>Billy Cobham</td>\n",
       "      <td>1</td>\n",
       "      <td>13.000000</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ArtistId                  Name  album_count     avg_id  sum\n",
       "0         1                 AC/DC            2   2.500000    5\n",
       "1         2                Accept            2   2.500000    5\n",
       "2         3             Aerosmith            1   5.000000    5\n",
       "3         4     Alanis Morissette            1   6.000000    6\n",
       "4         5       Alice In Chains            1   7.000000    7\n",
       "5         6  Antônio Carlos Jobim            2  21.000000   42\n",
       "6         7          Apocalyptica            1   9.000000    9\n",
       "7         8            Audioslave            3  97.333333  292\n",
       "8         9              BackBeat            1  12.000000   12\n",
       "9        10          Billy Cobham            1  13.000000   13"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "command = \"\"\"\n",
    "SELECT Artist.ArtistId, Name, COUNT(AlbumId) AS album_count,\n",
    "  AVG(AlbumId) AS avg_id, SUM(AlbumID) as sum\n",
    "FROM Album\n",
    "INNER JOIN Artist ON Album.ArtistId = Artist.ArtistId\n",
    "GROUP BY Artist.ArtistId\n",
    "LIMIT 10;\n",
    "\"\"\"\n",
    "pd.read_sql_query(text(command), conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": []
   },
   "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>AlbumId</th>\n",
       "      <th>Title</th>\n",
       "      <th>ArtistId</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>For Those About To Rock We Salute You</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>Let There Be Rock</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Balls to the Wall</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>Restless and Wild</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Big Ones</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>Jagged Little Pill</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>Facelift</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>Warner 25 Anos</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>34</td>\n",
       "      <td>Chill: Brazil (Disc 2)</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>Plays Metallica By Four Cellos</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10</td>\n",
       "      <td>Audioslave</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>11</td>\n",
       "      <td>Out Of Exile</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>271</td>\n",
       "      <td>Revelations</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>12</td>\n",
       "      <td>BackBeat Soundtrack</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>13</td>\n",
       "      <td>The Best Of Billy Cobham</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    AlbumId                                  Title  ArtistId\n",
       "0         1  For Those About To Rock We Salute You         1\n",
       "1         4                      Let There Be Rock         1\n",
       "2         2                      Balls to the Wall         2\n",
       "3         3                      Restless and Wild         2\n",
       "4         5                               Big Ones         3\n",
       "5         6                     Jagged Little Pill         4\n",
       "6         7                               Facelift         5\n",
       "7         8                         Warner 25 Anos         6\n",
       "8        34                 Chill: Brazil (Disc 2)         6\n",
       "9         9         Plays Metallica By Four Cellos         7\n",
       "10       10                             Audioslave         8\n",
       "11       11                           Out Of Exile         8\n",
       "12      271                            Revelations         8\n",
       "13       12                    BackBeat Soundtrack         9\n",
       "14       13               The Best Of Billy Cobham        10"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# for comparison with previous table\n",
    "command = '''\n",
    "SELECT * \n",
    "FROM Album\n",
    "ORDER BY ArtistId\n",
    "LIMIT 15;\n",
    "'''\n",
    "\n",
    "pd.read_sql_query(text(command), conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": []
   },
   "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>ArtistId</th>\n",
       "      <th>Name</th>\n",
       "      <th>album_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>22</td>\n",
       "      <td>Led Zeppelin</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>50</td>\n",
       "      <td>Metallica</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>58</td>\n",
       "      <td>Deep Purple</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>90</td>\n",
       "      <td>Iron Maiden</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>150</td>\n",
       "      <td>U2</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ArtistId          Name  album_count\n",
       "0        22  Led Zeppelin           14\n",
       "1        50     Metallica           10\n",
       "2        58   Deep Purple           11\n",
       "3        90   Iron Maiden           21\n",
       "4       150            U2           10"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Conditionals on the Group By must be done with 'HAVING'\n",
    "command = '''\n",
    "SELECT Artist.ArtistId, Name, COUNT(AlbumId) AS album_count\n",
    "FROM Album\n",
    "INNER JOIN Artist ON Album.ArtistId = Artist.ArtistId\n",
    "GROUP BY Artist.ArtistId\n",
    "HAVING album_count > 8;\n",
    "'''\n",
    "pd.read_sql_query(text(command), conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": []
   },
   "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>ArtistId</th>\n",
       "      <th>magic number</th>\n",
       "      <th>Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>AC/DC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>Accept</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>Aerosmith</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>Alanis Morissette</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>10</td>\n",
       "      <td>Alice In Chains</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>12</td>\n",
       "      <td>Antônio Carlos Jobim</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>14</td>\n",
       "      <td>Apocalyptica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>16</td>\n",
       "      <td>Audioslave</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>18</td>\n",
       "      <td>BackBeat</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>20</td>\n",
       "      <td>Billy Cobham</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ArtistId  magic number                  Name\n",
       "0         1             2                 AC/DC\n",
       "1         2             4                Accept\n",
       "2         3             6             Aerosmith\n",
       "3         4             8     Alanis Morissette\n",
       "4         5            10       Alice In Chains\n",
       "5         6            12  Antônio Carlos Jobim\n",
       "6         7            14          Apocalyptica\n",
       "7         8            16            Audioslave\n",
       "8         9            18              BackBeat\n",
       "9        10            20          Billy Cobham"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "command = '''\n",
    "SELECT ArtistId, ArtistId * 2 AS \"magic number\", Name\n",
    "From Artist \n",
    "LIMIT 10;\n",
    "'''\n",
    "pd.read_sql_query(text(command), conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": []
   },
   "source": [
    "## Table previews"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>AlbumId</th>\n",
       "      <th>Title</th>\n",
       "      <th>ArtistId</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>For Those About To Rock We Salute You</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Balls to the Wall</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Restless and Wild</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Let There Be Rock</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Big Ones</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   AlbumId                                  Title  ArtistId\n",
       "0        1  For Those About To Rock We Salute You         1\n",
       "1        2                      Balls to the Wall         2\n",
       "2        3                      Restless and Wild         2\n",
       "3        4                      Let There Be Rock         1\n",
       "4        5                               Big Ones         3"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "command = '''\n",
    "SELECT * \n",
    "FROM Album\n",
    "LIMIT 5;\n",
    "'''\n",
    "pd.read_sql_query(text(command), conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "tags": []
   },
   "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>ArtistId</th>\n",
       "      <th>Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>AC/DC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Accept</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Aerosmith</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Alanis Morissette</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Alice In Chains</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ArtistId               Name\n",
       "0         1              AC/DC\n",
       "1         2             Accept\n",
       "2         3          Aerosmith\n",
       "3         4  Alanis Morissette\n",
       "4         5    Alice In Chains"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "command = '''\n",
    "SELECT * \n",
    "FROM Artist\n",
    "LIMIT 5;\n",
    "'''\n",
    "pd.read_sql_query(text(command), conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": []
   },
   "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>InvoiceId</th>\n",
       "      <th>CustomerId</th>\n",
       "      <th>InvoiceDate</th>\n",
       "      <th>BillingAddress</th>\n",
       "      <th>BillingCity</th>\n",
       "      <th>BillingState</th>\n",
       "      <th>BillingCountry</th>\n",
       "      <th>BillingPostalCode</th>\n",
       "      <th>Total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2009-01-01 00:00:00</td>\n",
       "      <td>Theodor-Heuss-Straße 34</td>\n",
       "      <td>Stuttgart</td>\n",
       "      <td>None</td>\n",
       "      <td>Germany</td>\n",
       "      <td>70174</td>\n",
       "      <td>1.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>2009-01-02 00:00:00</td>\n",
       "      <td>Ullevålsveien 14</td>\n",
       "      <td>Oslo</td>\n",
       "      <td>None</td>\n",
       "      <td>Norway</td>\n",
       "      <td>0171</td>\n",
       "      <td>3.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "      <td>2009-01-03 00:00:00</td>\n",
       "      <td>Grétrystraat 63</td>\n",
       "      <td>Brussels</td>\n",
       "      <td>None</td>\n",
       "      <td>Belgium</td>\n",
       "      <td>1000</td>\n",
       "      <td>5.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>14</td>\n",
       "      <td>2009-01-06 00:00:00</td>\n",
       "      <td>8210 111 ST NW</td>\n",
       "      <td>Edmonton</td>\n",
       "      <td>AB</td>\n",
       "      <td>Canada</td>\n",
       "      <td>T6G 2C7</td>\n",
       "      <td>8.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>23</td>\n",
       "      <td>2009-01-11 00:00:00</td>\n",
       "      <td>69 Salem Street</td>\n",
       "      <td>Boston</td>\n",
       "      <td>MA</td>\n",
       "      <td>USA</td>\n",
       "      <td>2113</td>\n",
       "      <td>13.86</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   InvoiceId  CustomerId  ... BillingPostalCode  Total\n",
       "0          1           2  ...             70174   1.98\n",
       "1          2           4  ...              0171   3.96\n",
       "2          3           8  ...              1000   5.94\n",
       "3          4          14  ...           T6G 2C7   8.91\n",
       "4          5          23  ...              2113  13.86\n",
       "\n",
       "[5 rows x 9 columns]"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "command = '''\n",
    "SELECT * \n",
    "FROM Invoice\n",
    "LIMIT 5;\n",
    "'''\n",
    "pd.read_sql_query(text(command), conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "tags": []
   },
   "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>InvoiceLineId</th>\n",
       "      <th>InvoiceId</th>\n",
       "      <th>TrackId</th>\n",
       "      <th>UnitPrice</th>\n",
       "      <th>Quantity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.99</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>0.99</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>0.99</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>0.99</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>0.99</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>12</td>\n",
       "      <td>0.99</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>16</td>\n",
       "      <td>0.99</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   InvoiceLineId  InvoiceId  TrackId  UnitPrice  Quantity\n",
       "0              1          1        2       0.99         1\n",
       "1              2          1        4       0.99         1\n",
       "2              3          2        6       0.99         1\n",
       "3              4          2        8       0.99         1\n",
       "4              5          2       10       0.99         1\n",
       "5              6          2       12       0.99         1\n",
       "6              7          3       16       0.99         1"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "command = '''\n",
    "SELECT * \n",
    "FROM InvoiceLine\n",
    "LIMIT 7;\n",
    "'''\n",
    "pd.read_sql_query(text(command), conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": []
   },
   "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>TrackId</th>\n",
       "      <th>Name</th>\n",
       "      <th>AlbumId</th>\n",
       "      <th>MediaTypeId</th>\n",
       "      <th>GenreId</th>\n",
       "      <th>Composer</th>\n",
       "      <th>Milliseconds</th>\n",
       "      <th>Bytes</th>\n",
       "      <th>UnitPrice</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>For Those About To Rock (We Salute You)</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Angus Young, Malcolm Young, Brian Johnson</td>\n",
       "      <td>343719</td>\n",
       "      <td>11170334</td>\n",
       "      <td>0.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Balls to the Wall</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>342562</td>\n",
       "      <td>5510424</td>\n",
       "      <td>0.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Fast As a Shark</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>F. Baltes, S. Kaufman, U. Dirkscneider &amp; W. Ho...</td>\n",
       "      <td>230619</td>\n",
       "      <td>3990994</td>\n",
       "      <td>0.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Restless and Wild</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>F. Baltes, R.A. Smith-Diesel, S. Kaufman, U. D...</td>\n",
       "      <td>252051</td>\n",
       "      <td>4331779</td>\n",
       "      <td>0.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Princess of the Dawn</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>Deaffy &amp; R.A. Smith-Diesel</td>\n",
       "      <td>375418</td>\n",
       "      <td>6290521</td>\n",
       "      <td>0.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>Put The Finger On You</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Angus Young, Malcolm Young, Brian Johnson</td>\n",
       "      <td>205662</td>\n",
       "      <td>6713451</td>\n",
       "      <td>0.99</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   TrackId                                     Name  ...     Bytes  UnitPrice\n",
       "0        1  For Those About To Rock (We Salute You)  ...  11170334       0.99\n",
       "1        2                        Balls to the Wall  ...   5510424       0.99\n",
       "2        3                          Fast As a Shark  ...   3990994       0.99\n",
       "3        4                        Restless and Wild  ...   4331779       0.99\n",
       "4        5                     Princess of the Dawn  ...   6290521       0.99\n",
       "5        6                    Put The Finger On You  ...   6713451       0.99\n",
       "\n",
       "[6 rows x 9 columns]"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "command = '''\n",
    "SELECT * \n",
    "FROM Track\n",
    "LIMIT 6;\n",
    "'''\n",
    "pd.read_sql_query(text(command), conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "tags": []
   },
   "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>CustomerId</th>\n",
       "      <th>FirstName</th>\n",
       "      <th>LastName</th>\n",
       "      <th>Company</th>\n",
       "      <th>Address</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>Country</th>\n",
       "      <th>PostalCode</th>\n",
       "      <th>Phone</th>\n",
       "      <th>Fax</th>\n",
       "      <th>Email</th>\n",
       "      <th>SupportRepId</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Luís</td>\n",
       "      <td>Gonçalves</td>\n",
       "      <td>Embraer - Empresa Brasileira de Aeronáutica S.A.</td>\n",
       "      <td>Av. Brigadeiro Faria Lima, 2170</td>\n",
       "      <td>São José dos Campos</td>\n",
       "      <td>SP</td>\n",
       "      <td>Brazil</td>\n",
       "      <td>12227-000</td>\n",
       "      <td>+55 (12) 3923-5555</td>\n",
       "      <td>+55 (12) 3923-5566</td>\n",
       "      <td>luisg@embraer.com.br</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Leonie</td>\n",
       "      <td>Köhler</td>\n",
       "      <td>None</td>\n",
       "      <td>Theodor-Heuss-Straße 34</td>\n",
       "      <td>Stuttgart</td>\n",
       "      <td>None</td>\n",
       "      <td>Germany</td>\n",
       "      <td>70174</td>\n",
       "      <td>+49 0711 2842222</td>\n",
       "      <td>None</td>\n",
       "      <td>leonekohler@surfeu.de</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>François</td>\n",
       "      <td>Tremblay</td>\n",
       "      <td>None</td>\n",
       "      <td>1498 rue Bélanger</td>\n",
       "      <td>Montréal</td>\n",
       "      <td>QC</td>\n",
       "      <td>Canada</td>\n",
       "      <td>H2G 1A7</td>\n",
       "      <td>+1 (514) 721-4711</td>\n",
       "      <td>None</td>\n",
       "      <td>ftremblay@gmail.com</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Bjørn</td>\n",
       "      <td>Hansen</td>\n",
       "      <td>None</td>\n",
       "      <td>Ullevålsveien 14</td>\n",
       "      <td>Oslo</td>\n",
       "      <td>None</td>\n",
       "      <td>Norway</td>\n",
       "      <td>0171</td>\n",
       "      <td>+47 22 44 22 22</td>\n",
       "      <td>None</td>\n",
       "      <td>bjorn.hansen@yahoo.no</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>František</td>\n",
       "      <td>Wichterlová</td>\n",
       "      <td>JetBrains s.r.o.</td>\n",
       "      <td>Klanova 9/506</td>\n",
       "      <td>Prague</td>\n",
       "      <td>None</td>\n",
       "      <td>Czech Republic</td>\n",
       "      <td>14700</td>\n",
       "      <td>+420 2 4172 5555</td>\n",
       "      <td>+420 2 4172 5555</td>\n",
       "      <td>frantisekw@jetbrains.com</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CustomerId  FirstName  ...                     Email SupportRepId\n",
       "0           1       Luís  ...      luisg@embraer.com.br            3\n",
       "1           2     Leonie  ...     leonekohler@surfeu.de            5\n",
       "2           3   François  ...       ftremblay@gmail.com            3\n",
       "3           4      Bjørn  ...     bjorn.hansen@yahoo.no            4\n",
       "4           5  František  ...  frantisekw@jetbrains.com            4\n",
       "\n",
       "[5 rows x 13 columns]"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "command = '''\n",
    "SELECT * \n",
    "FROM Customer\n",
    "LIMIT 5;\n",
    "'''\n",
    "pd.read_sql_query(text(command), conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": []
   },
   "source": [
    "# table aliases\n",
    "\n",
    "Provide a query showing the Invoices of customers who are from Brazil. The resultant table should show the customer's full name, Invoice ID, Date of the invoice and billing country."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "command = '''\n",
    "SELECT c.FirstName, c.lastname, \n",
    "    i.invoiceid, i.invoicedate, i.billingcountry    -- selects the desired columns\n",
    "FROM customer AS c                     -- provide an alias to the table, so we dont have to type the full name out\n",
    "    JOIN invoice AS i\n",
    "    ON c.customerid = i.customerid     -- this is how the tables are linked\n",
    "WHERE c.country = 'Brazil'\n",
    "LIMIT 20;                               -- limits how many rows we get back\n",
    "'''\n",
    "pd.read_sql_query(text(command), conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": []
   },
   "source": [
    "## `DISTINCT` \n",
    "\n",
    "Provide a query showing a unique list of billing countries from the Invoice table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "command = '''\n",
    "SELECT DISTINCT billingcountry \n",
    "FROM invoice;\n",
    "'''\n",
    "pd.read_sql_query(text(command), conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": []
   },
   "source": [
    "## Joining three tables\n",
    "\n",
    "The following query shows the invoices associated with each sales agent.\n",
    "\n",
    "The invoice table has no information about employee. But each invoice has a customer and each customer has a support rep (employee). We connect the invoice table with the employee table by connecting them through the customer table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "command = '''\n",
    "SELECT e.firstname, e.lastname,   -- employee first and last name\n",
    "       i.*   -- all columns from invoice table \n",
    "\n",
    "FROM invoice AS i\n",
    "    JOIN customer AS c\n",
    "    ON c.customerid = i.customerid\n",
    "\n",
    "    JOIN employee AS e\n",
    "    ON e.employeeid = c.supportrepid\n",
    "    \n",
    "ORDER BY e.employeeid;\n",
    "'''\n",
    "pd.read_sql_query(text(command), conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": []
   },
   "source": [
    "Following query shows the Invoice Total, Customer name, Country and Sale Agent name for all invoices and customers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "command = '''\n",
    "SELECT i.InvoiceId, i.total,\n",
    "       e.firstname AS 'employee first', \n",
    "       e.lastname AS 'employee last', \n",
    "       c.firstname AS 'customer first', \n",
    "       c.lastname AS 'customer last', \n",
    "       c.country\n",
    "FROM employee AS e\n",
    "        JOIN customer AS c \n",
    "        ON e.employeeid = c.supportrepid\n",
    "        JOIN invoice AS i \n",
    "        ON c.customerid = i.customerid;\n",
    "'''\n",
    "pd.read_sql_query(text(command), conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": []
   },
   "source": [
    "How many Invoices were there in 2011? What are the total sales for that year?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "command = '''\n",
    "SELECT invoiceId, InvoiceDate, total\n",
    "FROM invoice as i\n",
    "WHERE i.invoicedate BETWEEN datetime('2011-01-01') AND datetime('2011-12-31');\n",
    "'''\n",
    "pd.read_sql_query(text(command), conn)  # result has 83 rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "command = '''\n",
    "SELECT invoiceId, InvoiceDate, total\n",
    "FROM invoice as i\n",
    "WHERE i.invoiceID BETWEEN 50 AND 60;\n",
    "'''\n",
    "pd.read_sql_query(text(command), conn)  # result has 83 rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "command = '''\n",
    "SELECT count(i.invoiceId) as 'count',\n",
    "    sum(i.total) as 'sum'\n",
    "FROM invoice as i\n",
    "WHERE i.invoicedate BETWEEN datetime('2011-01-01') AND datetime('2011-12-31');\n",
    "'''\n",
    "pd.read_sql_query(text(command), conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": []
   },
   "source": [
    "Count how many orders were made on each day"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "command = '''\n",
    "SELECT i.InvoiceDate, count(i.invoiceId) as 'count'\n",
    "FROM invoice as i\n",
    "WHERE i.invoicedate BETWEEN datetime('2011-01-01') AND datetime('2011-12-31')\n",
    "GROUP BY i.invoiceDate;\n",
    "'''\n",
    "pd.read_sql_query(text(command), conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": []
   },
   "source": [
    "Looking at the InvoiceLine table, provide a query that COUNTs the number of line items for each Invoice."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "command = '''\n",
    "SELECT *\n",
    "FROM invoiceline\n",
    "LIMIT 10;\n",
    "'''\n",
    "pd.read_sql_query(text(command), conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "command = '''\n",
    "SELECT invoiceid, count(invoicelineid) AS 'Count'\n",
    "FROM invoiceline\n",
    "GROUP BY invoiceid\n",
    "ORDER BY Count DESC;\n",
    "'''\n",
    "pd.read_sql_query(text(command), conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": []
   },
   "source": [
    "Find the invoice with the maximum number of Invoiceline IDs most elegant please\n",
    "\n",
    "CTE Common Table Expression - allows you to query tables that you created as intermediate steps"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "command = '''\n",
    "WITH InvoiceCounts (id, count) \n",
    "AS \n",
    "(  -- an intermediate table that aggregates the invoicelineIDs \n",
    "   -- pretty much the exact same table we generated in previous step\n",
    "    SELECT invoiceid, count(invoicelineid) AS 'Count'\n",
    "    FROM invoiceline\n",
    "    GROUP BY invoiceid\n",
    "    ORDER BY Count DESC\n",
    ")\n",
    "\n",
    "SELECT MAX(count) as Max, MIN(count) as Min\n",
    "FROM InvoiceCounts;\n",
    "'''\n",
    "pd.read_sql_query(text(command), conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "command = '''\n",
    "WITH InvoiceCounts (id, count) \n",
    "AS \n",
    "(  -- an intermediate table that aggregates the invoicelineIDs \n",
    "   -- pretty much the exact same table we generated in previous step\n",
    "    SELECT invoiceid, count(invoicelineid) AS 'Count'\n",
    "    FROM invoiceline\n",
    "    GROUP BY invoiceid\n",
    "    ORDER BY Count DESC\n",
    ")\n",
    "\n",
    "SELECT count, COUNT(id) as \"HowMany\"\n",
    "FROM InvoiceCounts\n",
    "GROUP BY count;\n",
    "'''\n",
    "pd.read_sql_query(text(command), conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": []
   },
   "source": [
    "Provide a query that includes the purchased track name AND artist name with each invoice line item.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "command = '''\n",
    "SELECT i.*, \n",
    "    t.name AS 'track', \n",
    "    ar.name AS 'artist'\n",
    "FROM invoiceline AS i\n",
    "        JOIN track AS t \n",
    "            ON i.trackid = t.trackid     -- i links to t\n",
    "        JOIN album AS al \n",
    "            ON t.albumid = al.albumid    -- t links to al\n",
    "        JOIN artist AS ar \n",
    "            ON al.artistid = ar.artistid;  -- al links to ar\n",
    "'''\n",
    "pd.read_sql_query(text(command), conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Look up \n",
    "# differences betwen LEFT AND RIGHT JOINS\n",
    "# https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "One table is of products\n",
    "Another table is of sales\n",
    "assume productid is the link\n",
    "\n",
    "how do find all the products that do not exist in the sales table?\n",
    "\n",
    "product_table AS p LEFT OUTER JOIN sales_table AS s\n",
    " ON p.productid = s.productid\n",
    " WHERE s.productid IS NULL"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.12.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}