9-2_Intro_SQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Lecture 9-2\n",
    "\n",
    "# Intro to SQL\n",
    "\n",
    "## Week 9 Wednesday\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": 1,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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": 4,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import inspect\n",
    "insp = inspect(engine) # creates an inspector"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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": 8,
   "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": 9,
   "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": 9,
     "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": 10,
   "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 FirstName                Title  ReportsTo  \\\n",
       "0           3   Peacock      Jane  Sales Support Agent        2.0   \n",
       "1           6  Mitchell   Michael           IT Manager        1.0   \n",
       "2           7      King    Robert             IT Staff        6.0   \n",
       "3           8  Callahan     Laura             IT Staff        6.0   \n",
       "4           5   Johnson     Steve  Sales Support Agent        2.0   \n",
       "5           1     Adams    Andrew      General Manager        NaN   \n",
       "6           2   Edwards     Nancy        Sales Manager        1.0   \n",
       "7           4      Park  Margaret  Sales Support Agent        2.0   \n",
       "\n",
       "             BirthDate             HireDate                      Address  \\\n",
       "0  1973-08-29 00:00:00  2002-04-01 00:00:00                1111 6 Ave SW   \n",
       "1  1973-07-01 00:00:00  2003-10-17 00:00:00         5827 Bowness Road NW   \n",
       "2  1970-05-29 00:00:00  2004-01-02 00:00:00  590 Columbia Boulevard West   \n",
       "3  1968-01-09 00:00:00  2004-03-04 00:00:00                  923 7 ST NW   \n",
       "4  1965-03-03 00:00:00  2003-10-17 00:00:00                 7727B 41 Ave   \n",
       "5  1962-02-18 00:00:00  2002-08-14 00:00:00          11120 Jasper Ave NW   \n",
       "6  1958-12-08 00:00:00  2002-05-01 00:00:00                 825 8 Ave SW   \n",
       "7  1947-09-19 00:00:00  2003-05-03 00:00:00             683 10 Street SW   \n",
       "\n",
       "         City State Country PostalCode              Phone                Fax  \\\n",
       "0     Calgary    AB  Canada    T2P 5M5  +1 (403) 262-3443  +1 (403) 262-6712   \n",
       "1     Calgary    AB  Canada    T3B 0C5  +1 (403) 246-9887  +1 (403) 246-9899   \n",
       "2  Lethbridge    AB  Canada    T1K 5N8  +1 (403) 456-9986  +1 (403) 456-8485   \n",
       "3  Lethbridge    AB  Canada    T1H 1Y8  +1 (403) 467-3351  +1 (403) 467-8772   \n",
       "4     Calgary    AB  Canada    T3B 1Y7   1 (780) 836-9987   1 (780) 836-9543   \n",
       "5    Edmonton    AB  Canada    T5K 2N1  +1 (780) 428-9482  +1 (780) 428-3457   \n",
       "6     Calgary    AB  Canada    T2P 2T3  +1 (403) 262-3443  +1 (403) 262-3322   \n",
       "7     Calgary    AB  Canada    T2P 5G3  +1 (403) 263-4423  +1 (403) 263-4289   \n",
       "\n",
       "                      Email  \n",
       "0      jane@chinookcorp.com  \n",
       "1   michael@chinookcorp.com  \n",
       "2    robert@chinookcorp.com  \n",
       "3     laura@chinookcorp.com  \n",
       "4     steve@chinookcorp.com  \n",
       "5    andrew@chinookcorp.com  \n",
       "6     nancy@chinookcorp.com  \n",
       "7  margaret@chinookcorp.com  "
      ]
     },
     "execution_count": 10,
     "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": 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>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 FirstName     Title  ReportsTo            BirthDate  \\\n",
       "0           8  Callahan     Laura  IT Staff          6  1968-01-09 00:00:00   \n",
       "1           7      King    Robert  IT Staff          6  1970-05-29 00:00:00   \n",
       "\n",
       "              HireDate                      Address        City State Country  \\\n",
       "0  2004-03-04 00:00:00                  923 7 ST NW  Lethbridge    AB  Canada   \n",
       "1  2004-01-02 00:00:00  590 Columbia Boulevard West  Lethbridge    AB  Canada   \n",
       "\n",
       "  PostalCode              Phone                Fax                   Email  \n",
       "0    T1H 1Y8  +1 (403) 467-3351  +1 (403) 467-8772   laura@chinookcorp.com  \n",
       "1    T1K 5N8  +1 (403) 456-9986  +1 (403) 456-8485  robert@chinookcorp.com  "
      ]
     },
     "execution_count": 11,
     "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": 12,
   "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": 12,
     "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": 13,
   "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": 13,
     "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": 14,
   "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                                  Title  ArtistId  ArtistId  \\\n",
       "0        1  For Those About To Rock We Salute You         1         1   \n",
       "1        2                      Balls to the Wall         2         2   \n",
       "2        3                      Restless and Wild         2         2   \n",
       "3        4                      Let There Be Rock         1         1   \n",
       "4        5                               Big Ones         3         3   \n",
       "5        6                     Jagged Little Pill         4         4   \n",
       "6        7                               Facelift         5         5   \n",
       "7        8                         Warner 25 Anos         6         6   \n",
       "8        9         Plays Metallica By Four Cellos         7         7   \n",
       "9       10                             Audioslave         8         8   \n",
       "\n",
       "                   Name  \n",
       "0                 AC/DC  \n",
       "1                Accept  \n",
       "2                Accept  \n",
       "3                 AC/DC  \n",
       "4             Aerosmith  \n",
       "5     Alanis Morissette  \n",
       "6       Alice In Chains  \n",
       "7  Antônio Carlos Jobim  \n",
       "8          Apocalyptica  \n",
       "9            Audioslave  "
      ]
     },
     "execution_count": 14,
     "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": 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>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": 15,
     "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": 16,
   "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": 16,
     "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": 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>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": 17,
     "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": 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",
       "    </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": 18,
     "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": 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>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": 19,
     "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": 20,
   "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": 20,
     "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": 21,
   "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": 21,
     "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": 22,
   "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          InvoiceDate           BillingAddress  \\\n",
       "0          1           2  2009-01-01 00:00:00  Theodor-Heuss-Straße 34   \n",
       "1          2           4  2009-01-02 00:00:00         Ullevålsveien 14   \n",
       "2          3           8  2009-01-03 00:00:00          Grétrystraat 63   \n",
       "3          4          14  2009-01-06 00:00:00           8210 111 ST NW   \n",
       "4          5          23  2009-01-11 00:00:00          69 Salem Street   \n",
       "\n",
       "  BillingCity BillingState BillingCountry BillingPostalCode  Total  \n",
       "0   Stuttgart         None        Germany             70174   1.98  \n",
       "1        Oslo         None         Norway              0171   3.96  \n",
       "2    Brussels         None        Belgium              1000   5.94  \n",
       "3    Edmonton           AB         Canada           T6G 2C7   8.91  \n",
       "4      Boston           MA            USA              2113  13.86  "
      ]
     },
     "execution_count": 22,
     "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": 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>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": 23,
     "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": 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>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  AlbumId  MediaTypeId  \\\n",
       "0        1  For Those About To Rock (We Salute You)        1            1   \n",
       "1        2                        Balls to the Wall        2            2   \n",
       "2        3                          Fast As a Shark        3            2   \n",
       "3        4                        Restless and Wild        3            2   \n",
       "4        5                     Princess of the Dawn        3            2   \n",
       "5        6                    Put The Finger On You        1            1   \n",
       "\n",
       "   GenreId                                           Composer  Milliseconds  \\\n",
       "0        1          Angus Young, Malcolm Young, Brian Johnson        343719   \n",
       "1        1                                               None        342562   \n",
       "2        1  F. Baltes, S. Kaufman, U. Dirkscneider & W. Ho...        230619   \n",
       "3        1  F. Baltes, R.A. Smith-Diesel, S. Kaufman, U. D...        252051   \n",
       "4        1                         Deaffy & R.A. Smith-Diesel        375418   \n",
       "5        1          Angus Young, Malcolm Young, Brian Johnson        205662   \n",
       "\n",
       "      Bytes  UnitPrice  \n",
       "0  11170334       0.99  \n",
       "1   5510424       0.99  \n",
       "2   3990994       0.99  \n",
       "3   4331779       0.99  \n",
       "4   6290521       0.99  \n",
       "5   6713451       0.99  "
      ]
     },
     "execution_count": 24,
     "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": 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>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     LastName  \\\n",
       "0           1       Luís    Gonçalves   \n",
       "1           2     Leonie       Köhler   \n",
       "2           3   François     Tremblay   \n",
       "3           4      Bjørn       Hansen   \n",
       "4           5  František  Wichterlová   \n",
       "\n",
       "                                            Company  \\\n",
       "0  Embraer - Empresa Brasileira de Aeronáutica S.A.   \n",
       "1                                              None   \n",
       "2                                              None   \n",
       "3                                              None   \n",
       "4                                  JetBrains s.r.o.   \n",
       "\n",
       "                           Address                 City State         Country  \\\n",
       "0  Av. Brigadeiro Faria Lima, 2170  São José dos Campos    SP          Brazil   \n",
       "1          Theodor-Heuss-Straße 34            Stuttgart  None         Germany   \n",
       "2                1498 rue Bélanger             Montréal    QC          Canada   \n",
       "3                 Ullevålsveien 14                 Oslo  None          Norway   \n",
       "4                    Klanova 9/506               Prague  None  Czech Republic   \n",
       "\n",
       "  PostalCode               Phone                 Fax  \\\n",
       "0  12227-000  +55 (12) 3923-5555  +55 (12) 3923-5566   \n",
       "1      70174    +49 0711 2842222                None   \n",
       "2    H2G 1A7   +1 (514) 721-4711                None   \n",
       "3       0171     +47 22 44 22 22                None   \n",
       "4      14700    +420 2 4172 5555    +420 2 4172 5555   \n",
       "\n",
       "                      Email  SupportRepId  \n",
       "0      luisg@embraer.com.br             3  \n",
       "1     leonekohler@surfeu.de             5  \n",
       "2       ftremblay@gmail.com             3  \n",
       "3     bjorn.hansen@yahoo.no             4  \n",
       "4  frantisekw@jetbrains.com             4  "
      ]
     },
     "execution_count": 25,
     "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": 26,
   "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>InvoiceId</th>\n",
       "      <th>InvoiceDate</th>\n",
       "      <th>BillingCountry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Luís</td>\n",
       "      <td>Gonçalves</td>\n",
       "      <td>98</td>\n",
       "      <td>2010-03-11 00:00:00</td>\n",
       "      <td>Brazil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Luís</td>\n",
       "      <td>Gonçalves</td>\n",
       "      <td>121</td>\n",
       "      <td>2010-06-13 00:00:00</td>\n",
       "      <td>Brazil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Luís</td>\n",
       "      <td>Gonçalves</td>\n",
       "      <td>143</td>\n",
       "      <td>2010-09-15 00:00:00</td>\n",
       "      <td>Brazil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Luís</td>\n",
       "      <td>Gonçalves</td>\n",
       "      <td>195</td>\n",
       "      <td>2011-05-06 00:00:00</td>\n",
       "      <td>Brazil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Luís</td>\n",
       "      <td>Gonçalves</td>\n",
       "      <td>316</td>\n",
       "      <td>2012-10-27 00:00:00</td>\n",
       "      <td>Brazil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Luís</td>\n",
       "      <td>Gonçalves</td>\n",
       "      <td>327</td>\n",
       "      <td>2012-12-07 00:00:00</td>\n",
       "      <td>Brazil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Luís</td>\n",
       "      <td>Gonçalves</td>\n",
       "      <td>382</td>\n",
       "      <td>2013-08-07 00:00:00</td>\n",
       "      <td>Brazil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Eduardo</td>\n",
       "      <td>Martins</td>\n",
       "      <td>25</td>\n",
       "      <td>2009-04-09 00:00:00</td>\n",
       "      <td>Brazil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Eduardo</td>\n",
       "      <td>Martins</td>\n",
       "      <td>154</td>\n",
       "      <td>2010-11-14 00:00:00</td>\n",
       "      <td>Brazil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Eduardo</td>\n",
       "      <td>Martins</td>\n",
       "      <td>177</td>\n",
       "      <td>2011-02-16 00:00:00</td>\n",
       "      <td>Brazil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Eduardo</td>\n",
       "      <td>Martins</td>\n",
       "      <td>199</td>\n",
       "      <td>2011-05-21 00:00:00</td>\n",
       "      <td>Brazil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Eduardo</td>\n",
       "      <td>Martins</td>\n",
       "      <td>251</td>\n",
       "      <td>2012-01-09 00:00:00</td>\n",
       "      <td>Brazil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Eduardo</td>\n",
       "      <td>Martins</td>\n",
       "      <td>372</td>\n",
       "      <td>2013-07-02 00:00:00</td>\n",
       "      <td>Brazil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Eduardo</td>\n",
       "      <td>Martins</td>\n",
       "      <td>383</td>\n",
       "      <td>2013-08-12 00:00:00</td>\n",
       "      <td>Brazil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Alexandre</td>\n",
       "      <td>Rocha</td>\n",
       "      <td>57</td>\n",
       "      <td>2009-09-06 00:00:00</td>\n",
       "      <td>Brazil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Alexandre</td>\n",
       "      <td>Rocha</td>\n",
       "      <td>68</td>\n",
       "      <td>2009-10-17 00:00:00</td>\n",
       "      <td>Brazil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Alexandre</td>\n",
       "      <td>Rocha</td>\n",
       "      <td>123</td>\n",
       "      <td>2010-06-17 00:00:00</td>\n",
       "      <td>Brazil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Alexandre</td>\n",
       "      <td>Rocha</td>\n",
       "      <td>252</td>\n",
       "      <td>2012-01-22 00:00:00</td>\n",
       "      <td>Brazil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Alexandre</td>\n",
       "      <td>Rocha</td>\n",
       "      <td>275</td>\n",
       "      <td>2012-04-25 00:00:00</td>\n",
       "      <td>Brazil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Alexandre</td>\n",
       "      <td>Rocha</td>\n",
       "      <td>297</td>\n",
       "      <td>2012-07-28 00:00:00</td>\n",
       "      <td>Brazil</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    FirstName   LastName  InvoiceId          InvoiceDate BillingCountry\n",
       "0        Luís  Gonçalves         98  2010-03-11 00:00:00         Brazil\n",
       "1        Luís  Gonçalves        121  2010-06-13 00:00:00         Brazil\n",
       "2        Luís  Gonçalves        143  2010-09-15 00:00:00         Brazil\n",
       "3        Luís  Gonçalves        195  2011-05-06 00:00:00         Brazil\n",
       "4        Luís  Gonçalves        316  2012-10-27 00:00:00         Brazil\n",
       "5        Luís  Gonçalves        327  2012-12-07 00:00:00         Brazil\n",
       "6        Luís  Gonçalves        382  2013-08-07 00:00:00         Brazil\n",
       "7     Eduardo    Martins         25  2009-04-09 00:00:00         Brazil\n",
       "8     Eduardo    Martins        154  2010-11-14 00:00:00         Brazil\n",
       "9     Eduardo    Martins        177  2011-02-16 00:00:00         Brazil\n",
       "10    Eduardo    Martins        199  2011-05-21 00:00:00         Brazil\n",
       "11    Eduardo    Martins        251  2012-01-09 00:00:00         Brazil\n",
       "12    Eduardo    Martins        372  2013-07-02 00:00:00         Brazil\n",
       "13    Eduardo    Martins        383  2013-08-12 00:00:00         Brazil\n",
       "14  Alexandre      Rocha         57  2009-09-06 00:00:00         Brazil\n",
       "15  Alexandre      Rocha         68  2009-10-17 00:00:00         Brazil\n",
       "16  Alexandre      Rocha        123  2010-06-17 00:00:00         Brazil\n",
       "17  Alexandre      Rocha        252  2012-01-22 00:00:00         Brazil\n",
       "18  Alexandre      Rocha        275  2012-04-25 00:00:00         Brazil\n",
       "19  Alexandre      Rocha        297  2012-07-28 00:00:00         Brazil"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "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": 27,
   "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>BillingCountry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Germany</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Norway</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Belgium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Canada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>France</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Ireland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Australia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Chile</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>India</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Brazil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Portugal</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Netherlands</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Spain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Sweden</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Czech Republic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Finland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Denmark</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Italy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Poland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Austria</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Hungary</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Argentina</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    BillingCountry\n",
       "0          Germany\n",
       "1           Norway\n",
       "2          Belgium\n",
       "3           Canada\n",
       "4              USA\n",
       "5           France\n",
       "6          Ireland\n",
       "7   United Kingdom\n",
       "8        Australia\n",
       "9            Chile\n",
       "10           India\n",
       "11          Brazil\n",
       "12        Portugal\n",
       "13     Netherlands\n",
       "14           Spain\n",
       "15          Sweden\n",
       "16  Czech Republic\n",
       "17         Finland\n",
       "18         Denmark\n",
       "19           Italy\n",
       "20          Poland\n",
       "21         Austria\n",
       "22         Hungary\n",
       "23       Argentina"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "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": 28,
   "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>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>Jane</td>\n",
       "      <td>Peacock</td>\n",
       "      <td>6</td>\n",
       "      <td>37</td>\n",
       "      <td>2009-01-19 00:00:00</td>\n",
       "      <td>Berger Straße 10</td>\n",
       "      <td>Frankfurt</td>\n",
       "      <td>None</td>\n",
       "      <td>Germany</td>\n",
       "      <td>60316</td>\n",
       "      <td>0.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jane</td>\n",
       "      <td>Peacock</td>\n",
       "      <td>7</td>\n",
       "      <td>38</td>\n",
       "      <td>2009-02-01 00:00:00</td>\n",
       "      <td>Barbarossastraße 19</td>\n",
       "      <td>Berlin</td>\n",
       "      <td>None</td>\n",
       "      <td>Germany</td>\n",
       "      <td>10779</td>\n",
       "      <td>1.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Jane</td>\n",
       "      <td>Peacock</td>\n",
       "      <td>9</td>\n",
       "      <td>42</td>\n",
       "      <td>2009-02-02 00:00:00</td>\n",
       "      <td>9, Place Louis Barthou</td>\n",
       "      <td>Bordeaux</td>\n",
       "      <td>None</td>\n",
       "      <td>France</td>\n",
       "      <td>33000</td>\n",
       "      <td>3.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Jane</td>\n",
       "      <td>Peacock</td>\n",
       "      <td>10</td>\n",
       "      <td>46</td>\n",
       "      <td>2009-02-03 00:00:00</td>\n",
       "      <td>3 Chatham Street</td>\n",
       "      <td>Dublin</td>\n",
       "      <td>Dublin</td>\n",
       "      <td>Ireland</td>\n",
       "      <td>None</td>\n",
       "      <td>5.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Jane</td>\n",
       "      <td>Peacock</td>\n",
       "      <td>11</td>\n",
       "      <td>52</td>\n",
       "      <td>2009-02-06 00:00:00</td>\n",
       "      <td>202 Hoxton Street</td>\n",
       "      <td>London</td>\n",
       "      <td>None</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>N1 5LH</td>\n",
       "      <td>8.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>407</th>\n",
       "      <td>Steve</td>\n",
       "      <td>Johnson</td>\n",
       "      <td>398</td>\n",
       "      <td>41</td>\n",
       "      <td>2013-10-21 00:00:00</td>\n",
       "      <td>11, Place Bellecour</td>\n",
       "      <td>Lyon</td>\n",
       "      <td>None</td>\n",
       "      <td>France</td>\n",
       "      <td>69002</td>\n",
       "      <td>0.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>408</th>\n",
       "      <td>Steve</td>\n",
       "      <td>Johnson</td>\n",
       "      <td>402</td>\n",
       "      <td>50</td>\n",
       "      <td>2013-11-05 00:00:00</td>\n",
       "      <td>C/ San Bernardo 85</td>\n",
       "      <td>Madrid</td>\n",
       "      <td>None</td>\n",
       "      <td>Spain</td>\n",
       "      <td>28015</td>\n",
       "      <td>5.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>409</th>\n",
       "      <td>Steve</td>\n",
       "      <td>Johnson</td>\n",
       "      <td>404</td>\n",
       "      <td>6</td>\n",
       "      <td>2013-11-13 00:00:00</td>\n",
       "      <td>Rilská 3174/6</td>\n",
       "      <td>Prague</td>\n",
       "      <td>None</td>\n",
       "      <td>Czech Republic</td>\n",
       "      <td>14300</td>\n",
       "      <td>25.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>410</th>\n",
       "      <td>Steve</td>\n",
       "      <td>Johnson</td>\n",
       "      <td>406</td>\n",
       "      <td>21</td>\n",
       "      <td>2013-12-04 00:00:00</td>\n",
       "      <td>801 W 4th Street</td>\n",
       "      <td>Reno</td>\n",
       "      <td>NV</td>\n",
       "      <td>USA</td>\n",
       "      <td>89503</td>\n",
       "      <td>1.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>411</th>\n",
       "      <td>Steve</td>\n",
       "      <td>Johnson</td>\n",
       "      <td>408</td>\n",
       "      <td>25</td>\n",
       "      <td>2013-12-05 00:00:00</td>\n",
       "      <td>319 N. Frances Street</td>\n",
       "      <td>Madison</td>\n",
       "      <td>WI</td>\n",
       "      <td>USA</td>\n",
       "      <td>53703</td>\n",
       "      <td>3.96</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>412 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    FirstName LastName  InvoiceId  CustomerId          InvoiceDate  \\\n",
       "0        Jane  Peacock          6          37  2009-01-19 00:00:00   \n",
       "1        Jane  Peacock          7          38  2009-02-01 00:00:00   \n",
       "2        Jane  Peacock          9          42  2009-02-02 00:00:00   \n",
       "3        Jane  Peacock         10          46  2009-02-03 00:00:00   \n",
       "4        Jane  Peacock         11          52  2009-02-06 00:00:00   \n",
       "..        ...      ...        ...         ...                  ...   \n",
       "407     Steve  Johnson        398          41  2013-10-21 00:00:00   \n",
       "408     Steve  Johnson        402          50  2013-11-05 00:00:00   \n",
       "409     Steve  Johnson        404           6  2013-11-13 00:00:00   \n",
       "410     Steve  Johnson        406          21  2013-12-04 00:00:00   \n",
       "411     Steve  Johnson        408          25  2013-12-05 00:00:00   \n",
       "\n",
       "             BillingAddress BillingCity BillingState  BillingCountry  \\\n",
       "0          Berger Straße 10   Frankfurt         None         Germany   \n",
       "1       Barbarossastraße 19      Berlin         None         Germany   \n",
       "2    9, Place Louis Barthou    Bordeaux         None          France   \n",
       "3          3 Chatham Street      Dublin       Dublin         Ireland   \n",
       "4         202 Hoxton Street      London         None  United Kingdom   \n",
       "..                      ...         ...          ...             ...   \n",
       "407     11, Place Bellecour        Lyon         None          France   \n",
       "408      C/ San Bernardo 85      Madrid         None           Spain   \n",
       "409           Rilská 3174/6      Prague         None  Czech Republic   \n",
       "410        801 W 4th Street        Reno           NV             USA   \n",
       "411   319 N. Frances Street     Madison           WI             USA   \n",
       "\n",
       "    BillingPostalCode  Total  \n",
       "0               60316   0.99  \n",
       "1               10779   1.98  \n",
       "2               33000   3.96  \n",
       "3                None   5.94  \n",
       "4              N1 5LH   8.91  \n",
       "..                ...    ...  \n",
       "407             69002   0.99  \n",
       "408             28015   5.94  \n",
       "409             14300  25.86  \n",
       "410             89503   1.98  \n",
       "411             53703   3.96  \n",
       "\n",
       "[412 rows x 11 columns]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "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": 29,
   "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>InvoiceId</th>\n",
       "      <th>Total</th>\n",
       "      <th>employee first</th>\n",
       "      <th>employee last</th>\n",
       "      <th>customer first</th>\n",
       "      <th>customer last</th>\n",
       "      <th>Country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1.98</td>\n",
       "      <td>Steve</td>\n",
       "      <td>Johnson</td>\n",
       "      <td>Leonie</td>\n",
       "      <td>Köhler</td>\n",
       "      <td>Germany</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>3.96</td>\n",
       "      <td>Margaret</td>\n",
       "      <td>Park</td>\n",
       "      <td>Bjørn</td>\n",
       "      <td>Hansen</td>\n",
       "      <td>Norway</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>5.94</td>\n",
       "      <td>Margaret</td>\n",
       "      <td>Park</td>\n",
       "      <td>Daan</td>\n",
       "      <td>Peeters</td>\n",
       "      <td>Belgium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>8.91</td>\n",
       "      <td>Steve</td>\n",
       "      <td>Johnson</td>\n",
       "      <td>Mark</td>\n",
       "      <td>Philips</td>\n",
       "      <td>Canada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>13.86</td>\n",
       "      <td>Margaret</td>\n",
       "      <td>Park</td>\n",
       "      <td>John</td>\n",
       "      <td>Gordon</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>407</th>\n",
       "      <td>408</td>\n",
       "      <td>3.96</td>\n",
       "      <td>Steve</td>\n",
       "      <td>Johnson</td>\n",
       "      <td>Victor</td>\n",
       "      <td>Stevens</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>408</th>\n",
       "      <td>409</td>\n",
       "      <td>5.94</td>\n",
       "      <td>Jane</td>\n",
       "      <td>Peacock</td>\n",
       "      <td>Robert</td>\n",
       "      <td>Brown</td>\n",
       "      <td>Canada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>409</th>\n",
       "      <td>410</td>\n",
       "      <td>8.91</td>\n",
       "      <td>Margaret</td>\n",
       "      <td>Park</td>\n",
       "      <td>Madalena</td>\n",
       "      <td>Sampaio</td>\n",
       "      <td>Portugal</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>410</th>\n",
       "      <td>411</td>\n",
       "      <td>13.86</td>\n",
       "      <td>Jane</td>\n",
       "      <td>Peacock</td>\n",
       "      <td>Terhi</td>\n",
       "      <td>Hämäläinen</td>\n",
       "      <td>Finland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>411</th>\n",
       "      <td>412</td>\n",
       "      <td>1.99</td>\n",
       "      <td>Jane</td>\n",
       "      <td>Peacock</td>\n",
       "      <td>Manoj</td>\n",
       "      <td>Pareek</td>\n",
       "      <td>India</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>412 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     InvoiceId  Total employee first employee last customer first  \\\n",
       "0            1   1.98          Steve       Johnson         Leonie   \n",
       "1            2   3.96       Margaret          Park          Bjørn   \n",
       "2            3   5.94       Margaret          Park           Daan   \n",
       "3            4   8.91          Steve       Johnson           Mark   \n",
       "4            5  13.86       Margaret          Park           John   \n",
       "..         ...    ...            ...           ...            ...   \n",
       "407        408   3.96          Steve       Johnson         Victor   \n",
       "408        409   5.94           Jane       Peacock         Robert   \n",
       "409        410   8.91       Margaret          Park       Madalena   \n",
       "410        411  13.86           Jane       Peacock          Terhi   \n",
       "411        412   1.99           Jane       Peacock          Manoj   \n",
       "\n",
       "    customer last   Country  \n",
       "0          Köhler   Germany  \n",
       "1          Hansen    Norway  \n",
       "2         Peeters   Belgium  \n",
       "3         Philips    Canada  \n",
       "4          Gordon       USA  \n",
       "..            ...       ...  \n",
       "407       Stevens       USA  \n",
       "408         Brown    Canada  \n",
       "409       Sampaio  Portugal  \n",
       "410    Hämäläinen   Finland  \n",
       "411        Pareek     India  \n",
       "\n",
       "[412 rows x 7 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "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": 30,
   "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>InvoiceId</th>\n",
       "      <th>InvoiceDate</th>\n",
       "      <th>Total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>167</td>\n",
       "      <td>2011-01-02 00:00:00</td>\n",
       "      <td>0.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>168</td>\n",
       "      <td>2011-01-15 00:00:00</td>\n",
       "      <td>1.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>169</td>\n",
       "      <td>2011-01-15 00:00:00</td>\n",
       "      <td>1.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>170</td>\n",
       "      <td>2011-01-16 00:00:00</td>\n",
       "      <td>3.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>171</td>\n",
       "      <td>2011-01-17 00:00:00</td>\n",
       "      <td>5.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>245</td>\n",
       "      <td>2011-12-22 00:00:00</td>\n",
       "      <td>1.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>246</td>\n",
       "      <td>2011-12-22 00:00:00</td>\n",
       "      <td>1.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>247</td>\n",
       "      <td>2011-12-23 00:00:00</td>\n",
       "      <td>3.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>248</td>\n",
       "      <td>2011-12-24 00:00:00</td>\n",
       "      <td>5.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>249</td>\n",
       "      <td>2011-12-27 00:00:00</td>\n",
       "      <td>8.91</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>83 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    InvoiceId          InvoiceDate  Total\n",
       "0         167  2011-01-02 00:00:00   0.99\n",
       "1         168  2011-01-15 00:00:00   1.98\n",
       "2         169  2011-01-15 00:00:00   1.98\n",
       "3         170  2011-01-16 00:00:00   3.96\n",
       "4         171  2011-01-17 00:00:00   5.94\n",
       "..        ...                  ...    ...\n",
       "78        245  2011-12-22 00:00:00   1.98\n",
       "79        246  2011-12-22 00:00:00   1.98\n",
       "80        247  2011-12-23 00:00:00   3.96\n",
       "81        248  2011-12-24 00:00:00   5.94\n",
       "82        249  2011-12-27 00:00:00   8.91\n",
       "\n",
       "[83 rows x 3 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "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": 31,
   "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>count</th>\n",
       "      <th>sum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>83</td>\n",
       "      <td>469.58</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count     sum\n",
       "0     83  469.58"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "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": 32,
   "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>InvoiceDate</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2011-01-02 00:00:00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2011-01-15 00:00:00</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2011-01-16 00:00:00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2011-01-17 00:00:00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2011-01-20 00:00:00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>2011-12-09 00:00:00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>2011-12-22 00:00:00</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>2011-12-23 00:00:00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>2011-12-24 00:00:00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>2011-12-27 00:00:00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>71 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            InvoiceDate  count\n",
       "0   2011-01-02 00:00:00      1\n",
       "1   2011-01-15 00:00:00      2\n",
       "2   2011-01-16 00:00:00      1\n",
       "3   2011-01-17 00:00:00      1\n",
       "4   2011-01-20 00:00:00      1\n",
       "..                  ...    ...\n",
       "66  2011-12-09 00:00:00      1\n",
       "67  2011-12-22 00:00:00      2\n",
       "68  2011-12-23 00:00:00      1\n",
       "69  2011-12-24 00:00:00      1\n",
       "70  2011-12-27 00:00:00      1\n",
       "\n",
       "[71 rows x 2 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "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": 33,
   "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>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",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>20</td>\n",
       "      <td>0.99</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>3</td>\n",
       "      <td>24</td>\n",
       "      <td>0.99</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>3</td>\n",
       "      <td>28</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\n",
       "7              8          3       20       0.99         1\n",
       "8              9          3       24       0.99         1\n",
       "9             10          3       28       0.99         1"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "command = '''\n",
    "SELECT *\n",
    "FROM invoiceline\n",
    "LIMIT 10;\n",
    "'''\n",
    "pd.read_sql_query(text(command), conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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>InvoiceId</th>\n",
       "      <th>Count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>19</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>26</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>33</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>407</th>\n",
       "      <td>384</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>408</th>\n",
       "      <td>391</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>409</th>\n",
       "      <td>398</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>410</th>\n",
       "      <td>405</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>411</th>\n",
       "      <td>412</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>412 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     InvoiceId  Count\n",
       "0            5     14\n",
       "1           12     14\n",
       "2           19     14\n",
       "3           26     14\n",
       "4           33     14\n",
       "..         ...    ...\n",
       "407        384      1\n",
       "408        391      1\n",
       "409        398      1\n",
       "410        405      1\n",
       "411        412      1\n",
       "\n",
       "[412 rows x 2 columns]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "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": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Max</th>\n",
       "      <th>Min</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Max  Min\n",
       "0   14    1"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "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": 36,
   "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>count</th>\n",
       "      <th>HowMany</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6</td>\n",
       "      <td>59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9</td>\n",
       "      <td>59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>14</td>\n",
       "      <td>59</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count  HowMany\n",
       "0      1       59\n",
       "1      2      117\n",
       "2      4       59\n",
       "3      6       59\n",
       "4      9       59\n",
       "5     14       59"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "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": 37,
   "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>InvoiceLineId</th>\n",
       "      <th>InvoiceId</th>\n",
       "      <th>TrackId</th>\n",
       "      <th>UnitPrice</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>track</th>\n",
       "      <th>artist</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",
       "      <td>Balls to the Wall</td>\n",
       "      <td>Accept</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",
       "      <td>Restless and Wild</td>\n",
       "      <td>Accept</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",
       "      <td>Put The Finger On You</td>\n",
       "      <td>AC/DC</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",
       "      <td>Inject The Venom</td>\n",
       "      <td>AC/DC</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",
       "      <td>Evil Walks</td>\n",
       "      <td>AC/DC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2235</th>\n",
       "      <td>2236</td>\n",
       "      <td>411</td>\n",
       "      <td>3136</td>\n",
       "      <td>0.99</td>\n",
       "      <td>1</td>\n",
       "      <td>Looking For Love</td>\n",
       "      <td>Lenny Kravitz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2236</th>\n",
       "      <td>2237</td>\n",
       "      <td>411</td>\n",
       "      <td>3145</td>\n",
       "      <td>0.99</td>\n",
       "      <td>1</td>\n",
       "      <td>Sweet Lady Luck</td>\n",
       "      <td>Lenny Kravitz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2237</th>\n",
       "      <td>2238</td>\n",
       "      <td>411</td>\n",
       "      <td>3154</td>\n",
       "      <td>0.99</td>\n",
       "      <td>1</td>\n",
       "      <td>Feirinha da Pavuna/Luz do Repente/Bagaço da La...</td>\n",
       "      <td>Zeca Pagodinho</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2238</th>\n",
       "      <td>2239</td>\n",
       "      <td>411</td>\n",
       "      <td>3163</td>\n",
       "      <td>0.99</td>\n",
       "      <td>1</td>\n",
       "      <td>Samba pras moças</td>\n",
       "      <td>Zeca Pagodinho</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2239</th>\n",
       "      <td>2240</td>\n",
       "      <td>412</td>\n",
       "      <td>3177</td>\n",
       "      <td>1.99</td>\n",
       "      <td>1</td>\n",
       "      <td>Hot Girl</td>\n",
       "      <td>The Office</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2240 rows × 7 columns</p>\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",
       "...             ...        ...      ...        ...       ...   \n",
       "2235           2236        411     3136       0.99         1   \n",
       "2236           2237        411     3145       0.99         1   \n",
       "2237           2238        411     3154       0.99         1   \n",
       "2238           2239        411     3163       0.99         1   \n",
       "2239           2240        412     3177       1.99         1   \n",
       "\n",
       "                                                  track          artist  \n",
       "0                                     Balls to the Wall          Accept  \n",
       "1                                     Restless and Wild          Accept  \n",
       "2                                 Put The Finger On You           AC/DC  \n",
       "3                                      Inject The Venom           AC/DC  \n",
       "4                                            Evil Walks           AC/DC  \n",
       "...                                                 ...             ...  \n",
       "2235                                   Looking For Love   Lenny Kravitz  \n",
       "2236                                    Sweet Lady Luck   Lenny Kravitz  \n",
       "2237  Feirinha da Pavuna/Luz do Repente/Bagaço da La...  Zeca Pagodinho  \n",
       "2238                                   Samba pras moças  Zeca Pagodinho  \n",
       "2239                                           Hot Girl      The Office  \n",
       "\n",
       "[2240 rows x 7 columns]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "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": 38,
   "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 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.18"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}