Bản_sao_của_Lab_1_Python_SQL.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Bản sao của Lab_1_Python_SQL.ipynb",
      "provenance": [],
      "collapsed_sections": [
        "0bNRYxjEAIYk",
        "9ihN0pi4A8cf"
      ],
      "toc_visible": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ljiunBcKlJSQ",
        "colab_type": "text"
      },
      "source": [
        "![](https://i.imgur.com/0AUxkXt.png)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "PgjLTSR7lEqz",
        "colab_type": "text"
      },
      "source": [
        "# Lab 1 - Basic Python & SQL Exercise"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "L3YbEdIS5BzQ",
        "colab_type": "text"
      },
      "source": [
        "## Basic SQL\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "O1P87Ntw_1Xr",
        "colab_type": "text"
      },
      "source": [
        "SQL, or Structured Query Language, is a language designed to allow both technical and non-technical users query, manipulate, and transform data from a **relational database**. And due to its simplicity, SQL databases provide safe and scalable storage for millions of websites and mobile applications.\n",
        "\n",
        "A relational database represents a collection of related (two-dimensional) tables. Each of the tables are similar to an Excel spreadsheet, with a fixed number of named columns (the attributes or properties of the table) and any number of rows of data.\n",
        "\n",
        "For example, if the Department of Motor Vehicles had a database, you might find a table containing all the known vehicles that people in the state are driving. This table might need to store the model name, type, number of wheels, and number of doors of each vehicle for example.\n",
        "\n",
        "| Id | Make/Model | # Wheels | # Door | Type |\n",
        "|:---:|:----------------------------:|:---------------:|:-----------:|:--------:|\n",
        "| 1 | Ford Focus | 4 | 4 | Sedan |\n",
        "| 2 | Tesla Roadster | 4 | 2 | Sports |\n",
        "| 3 | Kawakasi Ninja | 2 | 0 | Motorcycle |\n",
        "| 4 | McLaren Formula 1 | 4 | 0 | Race |\n",
        "| 5 | Testla S | 4 | 4 | Sedan |\n",
        "\n",
        "In such a database, you might find additional related tables containing information such as a list of all registered drivers in the state, the types of driving licenses that can be granted, or even driving violations for each driver.\n",
        "\n",
        "By learning SQL, the goal is to learn how to answer specific questions about this data, like *\"What types of vehicles are on the road have less than four wheels?\"*, or *\"How many models of cars does Tesla produce?\"*, to help us make better decisions down the road.\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "UFhsNsY2J1jz",
        "colab_type": "text"
      },
      "source": [
        "### Connect to the database\n",
        "\n",
        "![](https://i.imgur.com/kCaiMK5.png)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2tI-PoQZMKax",
        "colab_type": "text"
      },
      "source": [
        "**Run those cells below**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "NUcv2WLJKEkY",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "import sqlite3\n",
        "conn = sqlite3.connect('chinook.db')"
      ],
      "execution_count": 13,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "3EfkG2icKYAc",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        },
        "outputId": "7935c2f9-3e18-4b13-a676-16dc612cbb4a"
      },
      "source": [
        "import pandas as pd\n",
        "data = pd.read_sql_query('SELECT * FROM albums', conn)\n",
        "data.head() \n",
        "# head(n) is a function that show n first rows of the data, by default n=5"
      ],
      "execution_count": 14,
      "outputs": [
        {
          "output_type": "execute_result",
          "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"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 14
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ipg0wfIqK5E0",
        "colab_type": "text"
      },
      "source": [
        "### Write your first query\n",
        "\n",
        "Given a table of data, the most basic query we could write would be one that selects for a couple columns (properties) of the table with all the rows (instances).\n",
        "\n",
        "```SQL\n",
        "SELECT column, another_column, …\n",
        "FROM mytable;\n",
        "```\n",
        "If we want to retrieve absolutely all the columns of data from a table, we can then use the asterisk (*) shorthand in place of listing all the column names individually.\n",
        "```SQL\n",
        "SELECT * \n",
        "FROM mytable;\n",
        "```"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_q7mJSSQKvnO",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 426
        },
        "outputId": "7820d5c8-2a85-4c46-99f8-ac707d089ce3"
      },
      "source": [
        "# Replace ___ with a query that list all the employees first name and last name\n",
        "q = 'SELECT LastName, FirstName FROM employees'\n",
        "pd.read_sql_query(q, conn)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "error",
          "ename": "DatabaseError",
          "evalue": "ignored",
          "traceback": [
            "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
            "\u001b[0;31mOperationalError\u001b[0m                          Traceback (most recent call last)",
            "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, *args, **kwargs)\u001b[0m\n\u001b[1;32m   1585\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1586\u001b[0;31m             \u001b[0mcur\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1587\u001b[0m             \u001b[0;32mreturn\u001b[0m \u001b[0mcur\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;31mOperationalError\u001b[0m: no such table: employees",
            "\nThe above exception was the direct cause of the following exception:\n",
            "\u001b[0;31mDatabaseError\u001b[0m                             Traceback (most recent call last)",
            "\u001b[0;32m<ipython-input-10-87397742d870>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0;31m# Replace ___ with a query that list all the employees first name and last name\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      2\u001b[0m \u001b[0mq\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m'SELECT LastName, FirstName FROM employees'\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 3\u001b[0;31m \u001b[0mpd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread_sql_query\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mq\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mconn\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
            "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mread_sql_query\u001b[0;34m(sql, con, index_col, coerce_float, params, parse_dates, chunksize)\u001b[0m\n\u001b[1;32m    330\u001b[0m         \u001b[0mcoerce_float\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mcoerce_float\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    331\u001b[0m         \u001b[0mparse_dates\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mparse_dates\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 332\u001b[0;31m         \u001b[0mchunksize\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mchunksize\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    333\u001b[0m     )\n\u001b[1;32m    334\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mread_query\u001b[0;34m(self, sql, index_col, coerce_float, params, parse_dates, chunksize)\u001b[0m\n\u001b[1;32m   1631\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1632\u001b[0m         \u001b[0margs\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0m_convert_params\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msql\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1633\u001b[0;31m         \u001b[0mcursor\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1634\u001b[0m         \u001b[0mcolumns\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m[\u001b[0m\u001b[0mcol_desc\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;32mfor\u001b[0m \u001b[0mcol_desc\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdescription\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1635\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, *args, **kwargs)\u001b[0m\n\u001b[1;32m   1596\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1597\u001b[0m             \u001b[0mex\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mDatabaseError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34mf\"Execution failed on sql '{args[0]}': {exc}\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1598\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mex\u001b[0m \u001b[0;32mfrom\u001b[0m \u001b[0mexc\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1599\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1600\u001b[0m     \u001b[0;34m@\u001b[0m\u001b[0mstaticmethod\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;31mDatabaseError\u001b[0m: Execution failed on sql 'SELECT LastName, FirstName FROM employees': no such table: employees"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "XAcY7HMgL2jl",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 297
        },
        "outputId": "feb1ea91-fa9f-4227-986d-325b5b3d07df"
      },
      "source": [
        "# Find the title of each employee\n",
        "q= 'SELECT LastName, FirstName, Title FROM employees'\n",
        "pd.read_sql_query(q, conn)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>LastName</th>\n",
              "      <th>FirstName</th>\n",
              "      <th>Title</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Adams</td>\n",
              "      <td>Andrew</td>\n",
              "      <td>General Manager</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Edwards</td>\n",
              "      <td>Nancy</td>\n",
              "      <td>Sales Manager</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Peacock</td>\n",
              "      <td>Jane</td>\n",
              "      <td>Sales Support Agent</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Park</td>\n",
              "      <td>Margaret</td>\n",
              "      <td>Sales Support Agent</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Johnson</td>\n",
              "      <td>Steve</td>\n",
              "      <td>Sales Support Agent</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>Mitchell</td>\n",
              "      <td>Michael</td>\n",
              "      <td>IT Manager</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>King</td>\n",
              "      <td>Robert</td>\n",
              "      <td>IT Staff</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>Callahan</td>\n",
              "      <td>Laura</td>\n",
              "      <td>IT Staff</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   LastName FirstName                Title\n",
              "0     Adams    Andrew      General Manager\n",
              "1   Edwards     Nancy        Sales Manager\n",
              "2   Peacock      Jane  Sales Support Agent\n",
              "3      Park  Margaret  Sales Support Agent\n",
              "4   Johnson     Steve  Sales Support Agent\n",
              "5  Mitchell   Michael           IT Manager\n",
              "6      King    Robert             IT Staff\n",
              "7  Callahan     Laura             IT Staff"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 10
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "dWDgWyoKNRzt",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 80
        },
        "outputId": "a5e1f7d8-f1c4-4435-b89a-5b12eec492a2"
      },
      "source": [
        "# Find the name of each artist\n",
        "# Print out how many artist are there in the database\n",
        "q= 'SELECT count(*) FROM artists'\n",
        "pd.read_sql_query(q, conn)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>275</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   count(*)\n",
              "0       275"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 15
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "v67JJSGxVDAL",
        "colab_type": "text"
      },
      "source": [
        "### Queries with contraints\n",
        "\n",
        "In order to filter certain results, we need to use a **WHERE** clause in the query.\n",
        "```\n",
        "SELECT column, another_column, ...\n",
        "FROM mytable\n",
        "WHERE conditions\n",
        "    AND/OR another_condition\n",
        "    AND/OR ...;\n",
        "```\n",
        "\n",
        "Below are some useful operators that you can use for numerical data (ie. integer or floating point)\n",
        "\n",
        "| Operator | Condition | SQL Example |\n",
        "|:--------------:|:---------------:|:----------------------:|\n",
        "| =, !=, < <=, >, >= | Standard numerical operators\t| col_name != 4 |\n",
        "| **BETWEEN** … **AND** …\t| Number is within range of two values (inclusive) |\tcol_name **BETWEEN** 1.5 **AND** 10.5 |\n",
        "| **NOT BETWEEN** … **AND** …\t| Number is not within range of two values (inclusive) |\tcol_name **NOT BETWEEN** 1 **AND** 10 |\n",
        "| **IN** (…)\t| Number exists in a list\t| col_name **IN** (2, 4, 6) |\n",
        "| **NOT IN** (…) |\tNumber does not exist in a list |\tcol_name **NOT IN** (1, 3, 5) |\n",
        "\n",
        "When writing WHERE clauses with columns containing text data, SQL supports a number of useful operators to do things like case-insensitive string comparison and wildcard pattern matching. Below is a few common text-data specific operators:\n",
        "\n",
        "| Operator | Condition | SQL Example |\n",
        "|:--------------:|:---------------:|:----------------------:|\n",
        "| = |\tCase sensitive exact string comparison (notice the single equals) |\tcol_name = \"abc\" |\n",
        "| != or <>\t| Case sensitive exact string inequality comparison\t| col_name != \"abcd\" |\n",
        "| **LIKE**\t| Case insensitive exact string comparison\t| col_name **LIKE** \"ABC\" |\n",
        "| **NOT LIKE**\t| Case insensitive exact string inequality comparison\t| col_name **NOT LIKE** \"ABCD\" |\n",
        "| %\t| Used anywhere in a string to match a sequence of zero or more characters (only with **LIKE** or **NOT LIKE**)\t| col_name **LIKE** \"%AT%\" (matches \"AT\", \"ATTIC\", \"CAT\" or even \"BATS\") |\n",
        "| _\t| Used anywhere in a string to match a single character (only with **LIKE** or **NOT LIKE**)\t| col_name **LIKE** \"AN_\" (matches \"AND\", but not \"AN\") |\n",
        "| **IN** (…) | String exists in a list\t| col_name **IN** (\"A\", \"B\", \"C\") |\n",
        "| **NOT IN** (…) | String does not exist in a list\t| col_name **NOT IN** (\"D\", \"E\", \"F\") |\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "7LECn9puNrJF",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 285
        },
        "outputId": "cb71a307-4059-4585-a734-841c91be0214"
      },
      "source": [
        "pd.read_sql_query('SELECT * FROM invoices;',conn).head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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  "
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 34
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "PhfGraMbYUw4",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 1000
        },
        "outputId": "16b07309-1829-4e3d-bc99-408ddeef1333"
      },
      "source": [
        "# Find all $1 bill or lower\n",
        "q= 'SELECT * FROM invoices WHERE Total <=1'\n",
        "pd.read_sql_query(q,conn)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>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>13</td>\n",
              "      <td>16</td>\n",
              "      <td>2009-02-19 00:00:00</td>\n",
              "      <td>1600 Amphitheatre Parkway</td>\n",
              "      <td>Mountain View</td>\n",
              "      <td>CA</td>\n",
              "      <td>USA</td>\n",
              "      <td>94043-1351</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>20</td>\n",
              "      <td>54</td>\n",
              "      <td>2009-03-22 00:00:00</td>\n",
              "      <td>110 Raeburn Pl</td>\n",
              "      <td>Edinburgh</td>\n",
              "      <td>None</td>\n",
              "      <td>United Kingdom</td>\n",
              "      <td>EH4 1HH</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>27</td>\n",
              "      <td>33</td>\n",
              "      <td>2009-04-22 00:00:00</td>\n",
              "      <td>5112 48 Street</td>\n",
              "      <td>Yellowknife</td>\n",
              "      <td>NT</td>\n",
              "      <td>Canada</td>\n",
              "      <td>X1A 1N6</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>34</td>\n",
              "      <td>12</td>\n",
              "      <td>2009-05-23 00:00:00</td>\n",
              "      <td>Praça Pio X, 119</td>\n",
              "      <td>Rio de Janeiro</td>\n",
              "      <td>RJ</td>\n",
              "      <td>Brazil</td>\n",
              "      <td>20040-020</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>41</td>\n",
              "      <td>50</td>\n",
              "      <td>2009-06-23 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>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>48</td>\n",
              "      <td>29</td>\n",
              "      <td>2009-07-24 00:00:00</td>\n",
              "      <td>796 Dundas Street West</td>\n",
              "      <td>Toronto</td>\n",
              "      <td>ON</td>\n",
              "      <td>Canada</td>\n",
              "      <td>M6J 1V1</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>55</td>\n",
              "      <td>8</td>\n",
              "      <td>2009-08-24 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>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>62</td>\n",
              "      <td>46</td>\n",
              "      <td>2009-09-24 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>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>69</td>\n",
              "      <td>25</td>\n",
              "      <td>2009-10-25 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>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>76</td>\n",
              "      <td>4</td>\n",
              "      <td>2009-11-25 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>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>83</td>\n",
              "      <td>42</td>\n",
              "      <td>2009-12-26 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>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>12</th>\n",
              "      <td>90</td>\n",
              "      <td>21</td>\n",
              "      <td>2010-01-26 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>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>13</th>\n",
              "      <td>104</td>\n",
              "      <td>38</td>\n",
              "      <td>2010-03-29 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>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14</th>\n",
              "      <td>111</td>\n",
              "      <td>17</td>\n",
              "      <td>2010-04-29 00:00:00</td>\n",
              "      <td>1 Microsoft Way</td>\n",
              "      <td>Redmond</td>\n",
              "      <td>WA</td>\n",
              "      <td>USA</td>\n",
              "      <td>98052-8300</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>15</th>\n",
              "      <td>118</td>\n",
              "      <td>55</td>\n",
              "      <td>2010-05-30 00:00:00</td>\n",
              "      <td>421 Bourke Street</td>\n",
              "      <td>Sidney</td>\n",
              "      <td>NSW</td>\n",
              "      <td>Australia</td>\n",
              "      <td>2010</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>16</th>\n",
              "      <td>125</td>\n",
              "      <td>34</td>\n",
              "      <td>2010-06-30 00:00:00</td>\n",
              "      <td>Rua da Assunção 53</td>\n",
              "      <td>Lisbon</td>\n",
              "      <td>None</td>\n",
              "      <td>Portugal</td>\n",
              "      <td>None</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>17</th>\n",
              "      <td>132</td>\n",
              "      <td>13</td>\n",
              "      <td>2010-07-31 00:00:00</td>\n",
              "      <td>Qe 7 Bloco G</td>\n",
              "      <td>Brasília</td>\n",
              "      <td>DF</td>\n",
              "      <td>Brazil</td>\n",
              "      <td>71020-677</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>18</th>\n",
              "      <td>139</td>\n",
              "      <td>51</td>\n",
              "      <td>2010-08-31 00:00:00</td>\n",
              "      <td>Celsiusg. 9</td>\n",
              "      <td>Stockholm</td>\n",
              "      <td>None</td>\n",
              "      <td>Sweden</td>\n",
              "      <td>11230</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>19</th>\n",
              "      <td>146</td>\n",
              "      <td>30</td>\n",
              "      <td>2010-10-01 00:00:00</td>\n",
              "      <td>230 Elgin Street</td>\n",
              "      <td>Ottawa</td>\n",
              "      <td>ON</td>\n",
              "      <td>Canada</td>\n",
              "      <td>K2P 1L7</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>20</th>\n",
              "      <td>153</td>\n",
              "      <td>9</td>\n",
              "      <td>2010-11-01 00:00:00</td>\n",
              "      <td>Sønder Boulevard 51</td>\n",
              "      <td>Copenhagen</td>\n",
              "      <td>None</td>\n",
              "      <td>Denmark</td>\n",
              "      <td>1720</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>21</th>\n",
              "      <td>160</td>\n",
              "      <td>47</td>\n",
              "      <td>2010-12-02 00:00:00</td>\n",
              "      <td>Via Degli Scipioni, 43</td>\n",
              "      <td>Rome</td>\n",
              "      <td>RM</td>\n",
              "      <td>Italy</td>\n",
              "      <td>00192</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>22</th>\n",
              "      <td>167</td>\n",
              "      <td>26</td>\n",
              "      <td>2011-01-02 00:00:00</td>\n",
              "      <td>2211 W Berry Street</td>\n",
              "      <td>Fort Worth</td>\n",
              "      <td>TX</td>\n",
              "      <td>USA</td>\n",
              "      <td>76110</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>23</th>\n",
              "      <td>174</td>\n",
              "      <td>5</td>\n",
              "      <td>2011-02-02 00:00:00</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>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>24</th>\n",
              "      <td>181</td>\n",
              "      <td>43</td>\n",
              "      <td>2011-03-05 00:00:00</td>\n",
              "      <td>68, Rue Jouvence</td>\n",
              "      <td>Dijon</td>\n",
              "      <td>None</td>\n",
              "      <td>France</td>\n",
              "      <td>21000</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>25</th>\n",
              "      <td>188</td>\n",
              "      <td>22</td>\n",
              "      <td>2011-04-05 00:00:00</td>\n",
              "      <td>120 S Orange Ave</td>\n",
              "      <td>Orlando</td>\n",
              "      <td>FL</td>\n",
              "      <td>USA</td>\n",
              "      <td>32801</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>26</th>\n",
              "      <td>195</td>\n",
              "      <td>1</td>\n",
              "      <td>2011-05-06 00:00:00</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>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>27</th>\n",
              "      <td>209</td>\n",
              "      <td>18</td>\n",
              "      <td>2011-07-07 00:00:00</td>\n",
              "      <td>627 Broadway</td>\n",
              "      <td>New York</td>\n",
              "      <td>NY</td>\n",
              "      <td>USA</td>\n",
              "      <td>10012-2612</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>28</th>\n",
              "      <td>216</td>\n",
              "      <td>56</td>\n",
              "      <td>2011-08-07 00:00:00</td>\n",
              "      <td>307 Macacha Güemes</td>\n",
              "      <td>Buenos Aires</td>\n",
              "      <td>None</td>\n",
              "      <td>Argentina</td>\n",
              "      <td>1106</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>29</th>\n",
              "      <td>223</td>\n",
              "      <td>35</td>\n",
              "      <td>2011-09-07 00:00:00</td>\n",
              "      <td>Rua dos Campeões Europeus de Viena, 4350</td>\n",
              "      <td>Porto</td>\n",
              "      <td>None</td>\n",
              "      <td>Portugal</td>\n",
              "      <td>None</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>30</th>\n",
              "      <td>230</td>\n",
              "      <td>14</td>\n",
              "      <td>2011-10-08 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>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>31</th>\n",
              "      <td>237</td>\n",
              "      <td>52</td>\n",
              "      <td>2011-11-08 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>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>32</th>\n",
              "      <td>244</td>\n",
              "      <td>31</td>\n",
              "      <td>2011-12-09 00:00:00</td>\n",
              "      <td>194A Chain Lake Drive</td>\n",
              "      <td>Halifax</td>\n",
              "      <td>NS</td>\n",
              "      <td>Canada</td>\n",
              "      <td>B3S 1C5</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>33</th>\n",
              "      <td>251</td>\n",
              "      <td>10</td>\n",
              "      <td>2012-01-09 00:00:00</td>\n",
              "      <td>Rua Dr. Falcão Filho, 155</td>\n",
              "      <td>São Paulo</td>\n",
              "      <td>SP</td>\n",
              "      <td>Brazil</td>\n",
              "      <td>01007-010</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>34</th>\n",
              "      <td>258</td>\n",
              "      <td>48</td>\n",
              "      <td>2012-02-09 00:00:00</td>\n",
              "      <td>Lijnbaansgracht 120bg</td>\n",
              "      <td>Amsterdam</td>\n",
              "      <td>VV</td>\n",
              "      <td>Netherlands</td>\n",
              "      <td>1016</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>35</th>\n",
              "      <td>265</td>\n",
              "      <td>27</td>\n",
              "      <td>2012-03-11 00:00:00</td>\n",
              "      <td>1033 N Park Ave</td>\n",
              "      <td>Tucson</td>\n",
              "      <td>AZ</td>\n",
              "      <td>USA</td>\n",
              "      <td>85719</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>36</th>\n",
              "      <td>272</td>\n",
              "      <td>6</td>\n",
              "      <td>2012-04-11 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>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>37</th>\n",
              "      <td>279</td>\n",
              "      <td>44</td>\n",
              "      <td>2012-05-12 00:00:00</td>\n",
              "      <td>Porthaninkatu 9</td>\n",
              "      <td>Helsinki</td>\n",
              "      <td>None</td>\n",
              "      <td>Finland</td>\n",
              "      <td>00530</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>38</th>\n",
              "      <td>286</td>\n",
              "      <td>23</td>\n",
              "      <td>2012-06-12 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>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>39</th>\n",
              "      <td>293</td>\n",
              "      <td>2</td>\n",
              "      <td>2012-07-13 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>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>40</th>\n",
              "      <td>300</td>\n",
              "      <td>40</td>\n",
              "      <td>2012-08-13 00:00:00</td>\n",
              "      <td>8, Rue Hanovre</td>\n",
              "      <td>Paris</td>\n",
              "      <td>None</td>\n",
              "      <td>France</td>\n",
              "      <td>75002</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>41</th>\n",
              "      <td>314</td>\n",
              "      <td>57</td>\n",
              "      <td>2012-10-14 00:00:00</td>\n",
              "      <td>Calle Lira, 198</td>\n",
              "      <td>Santiago</td>\n",
              "      <td>None</td>\n",
              "      <td>Chile</td>\n",
              "      <td>None</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>42</th>\n",
              "      <td>321</td>\n",
              "      <td>36</td>\n",
              "      <td>2012-11-14 00:00:00</td>\n",
              "      <td>Tauentzienstraße 8</td>\n",
              "      <td>Berlin</td>\n",
              "      <td>None</td>\n",
              "      <td>Germany</td>\n",
              "      <td>10789</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>43</th>\n",
              "      <td>328</td>\n",
              "      <td>15</td>\n",
              "      <td>2012-12-15 00:00:00</td>\n",
              "      <td>700 W Pender Street</td>\n",
              "      <td>Vancouver</td>\n",
              "      <td>BC</td>\n",
              "      <td>Canada</td>\n",
              "      <td>V6C 1G8</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>44</th>\n",
              "      <td>335</td>\n",
              "      <td>53</td>\n",
              "      <td>2013-01-15 00:00:00</td>\n",
              "      <td>113 Lupus St</td>\n",
              "      <td>London</td>\n",
              "      <td>None</td>\n",
              "      <td>United Kingdom</td>\n",
              "      <td>SW1V 3EN</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>45</th>\n",
              "      <td>342</td>\n",
              "      <td>32</td>\n",
              "      <td>2013-02-15 00:00:00</td>\n",
              "      <td>696 Osborne Street</td>\n",
              "      <td>Winnipeg</td>\n",
              "      <td>MB</td>\n",
              "      <td>Canada</td>\n",
              "      <td>R3L 2B9</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>46</th>\n",
              "      <td>349</td>\n",
              "      <td>11</td>\n",
              "      <td>2013-03-18 00:00:00</td>\n",
              "      <td>Av. Paulista, 2022</td>\n",
              "      <td>São Paulo</td>\n",
              "      <td>SP</td>\n",
              "      <td>Brazil</td>\n",
              "      <td>01310-200</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>47</th>\n",
              "      <td>356</td>\n",
              "      <td>49</td>\n",
              "      <td>2013-04-18 00:00:00</td>\n",
              "      <td>Ordynacka 10</td>\n",
              "      <td>Warsaw</td>\n",
              "      <td>None</td>\n",
              "      <td>Poland</td>\n",
              "      <td>00-358</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>48</th>\n",
              "      <td>363</td>\n",
              "      <td>28</td>\n",
              "      <td>2013-05-19 00:00:00</td>\n",
              "      <td>302 S 700 E</td>\n",
              "      <td>Salt Lake City</td>\n",
              "      <td>UT</td>\n",
              "      <td>USA</td>\n",
              "      <td>84102</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>49</th>\n",
              "      <td>370</td>\n",
              "      <td>7</td>\n",
              "      <td>2013-06-19 00:00:00</td>\n",
              "      <td>Rotenturmstraße 4, 1010 Innere Stadt</td>\n",
              "      <td>Vienne</td>\n",
              "      <td>None</td>\n",
              "      <td>Austria</td>\n",
              "      <td>1010</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>50</th>\n",
              "      <td>377</td>\n",
              "      <td>45</td>\n",
              "      <td>2013-07-20 00:00:00</td>\n",
              "      <td>Erzsébet krt. 58.</td>\n",
              "      <td>Budapest</td>\n",
              "      <td>None</td>\n",
              "      <td>Hungary</td>\n",
              "      <td>H-1073</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>51</th>\n",
              "      <td>384</td>\n",
              "      <td>24</td>\n",
              "      <td>2013-08-20 00:00:00</td>\n",
              "      <td>162 E Superior Street</td>\n",
              "      <td>Chicago</td>\n",
              "      <td>IL</td>\n",
              "      <td>USA</td>\n",
              "      <td>60611</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>52</th>\n",
              "      <td>391</td>\n",
              "      <td>3</td>\n",
              "      <td>2013-09-20 00:00:00</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>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>53</th>\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>54</th>\n",
              "      <td>405</td>\n",
              "      <td>20</td>\n",
              "      <td>2013-11-21 00:00:00</td>\n",
              "      <td>541 Del Medio Avenue</td>\n",
              "      <td>Mountain View</td>\n",
              "      <td>CA</td>\n",
              "      <td>USA</td>\n",
              "      <td>94040-111</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "    InvoiceId  CustomerId  ... BillingPostalCode Total\n",
              "0           6          37  ...             60316  0.99\n",
              "1          13          16  ...        94043-1351  0.99\n",
              "2          20          54  ...           EH4 1HH  0.99\n",
              "3          27          33  ...           X1A 1N6  0.99\n",
              "4          34          12  ...         20040-020  0.99\n",
              "5          41          50  ...             28015  0.99\n",
              "6          48          29  ...           M6J 1V1  0.99\n",
              "7          55           8  ...              1000  0.99\n",
              "8          62          46  ...              None  0.99\n",
              "9          69          25  ...             53703  0.99\n",
              "10         76           4  ...              0171  0.99\n",
              "11         83          42  ...             33000  0.99\n",
              "12         90          21  ...             89503  0.99\n",
              "13        104          38  ...             10779  0.99\n",
              "14        111          17  ...        98052-8300  0.99\n",
              "15        118          55  ...              2010  0.99\n",
              "16        125          34  ...              None  0.99\n",
              "17        132          13  ...         71020-677  0.99\n",
              "18        139          51  ...             11230  0.99\n",
              "19        146          30  ...           K2P 1L7  0.99\n",
              "20        153           9  ...              1720  0.99\n",
              "21        160          47  ...             00192  0.99\n",
              "22        167          26  ...             76110  0.99\n",
              "23        174           5  ...             14700  0.99\n",
              "24        181          43  ...             21000  0.99\n",
              "25        188          22  ...             32801  0.99\n",
              "26        195           1  ...         12227-000  0.99\n",
              "27        209          18  ...        10012-2612  0.99\n",
              "28        216          56  ...              1106  0.99\n",
              "29        223          35  ...              None  0.99\n",
              "30        230          14  ...           T6G 2C7  0.99\n",
              "31        237          52  ...            N1 5LH  0.99\n",
              "32        244          31  ...           B3S 1C5  0.99\n",
              "33        251          10  ...         01007-010  0.99\n",
              "34        258          48  ...              1016  0.99\n",
              "35        265          27  ...             85719  0.99\n",
              "36        272           6  ...             14300  0.99\n",
              "37        279          44  ...             00530  0.99\n",
              "38        286          23  ...              2113  0.99\n",
              "39        293           2  ...             70174  0.99\n",
              "40        300          40  ...             75002  0.99\n",
              "41        314          57  ...              None  0.99\n",
              "42        321          36  ...             10789  0.99\n",
              "43        328          15  ...           V6C 1G8  0.99\n",
              "44        335          53  ...          SW1V 3EN  0.99\n",
              "45        342          32  ...           R3L 2B9  0.99\n",
              "46        349          11  ...         01310-200  0.99\n",
              "47        356          49  ...            00-358  0.99\n",
              "48        363          28  ...             84102  0.99\n",
              "49        370           7  ...              1010  0.99\n",
              "50        377          45  ...            H-1073  0.99\n",
              "51        384          24  ...             60611  0.99\n",
              "52        391           3  ...           H2G 1A7  0.99\n",
              "53        398          41  ...             69002  0.99\n",
              "54        405          20  ...         94040-111  0.99\n",
              "\n",
              "[55 rows x 9 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 16
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "5qk1B3Lta_uC",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 419
        },
        "outputId": "642d3897-0baa-4378-9889-2ebfd9b4c228"
      },
      "source": [
        "# Find all invoice of customers who are in the USA or Canada\n",
        "q='SELECT * FROM invoices WHERE BillingCountry IN(\"USA\", \"Canada\")'\n",
        "pd.read_sql_query(q,conn)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>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>1</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",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>13</td>\n",
              "      <td>16</td>\n",
              "      <td>2009-02-19 00:00:00</td>\n",
              "      <td>1600 Amphitheatre Parkway</td>\n",
              "      <td>Mountain View</td>\n",
              "      <td>CA</td>\n",
              "      <td>USA</td>\n",
              "      <td>94043-1351</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>14</td>\n",
              "      <td>17</td>\n",
              "      <td>2009-03-04 00:00:00</td>\n",
              "      <td>1 Microsoft Way</td>\n",
              "      <td>Redmond</td>\n",
              "      <td>WA</td>\n",
              "      <td>USA</td>\n",
              "      <td>98052-8300</td>\n",
              "      <td>1.98</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>15</td>\n",
              "      <td>19</td>\n",
              "      <td>2009-03-04 00:00:00</td>\n",
              "      <td>1 Infinite Loop</td>\n",
              "      <td>Cupertino</td>\n",
              "      <td>CA</td>\n",
              "      <td>USA</td>\n",
              "      <td>95014</td>\n",
              "      <td>1.98</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",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>142</th>\n",
              "      <td>405</td>\n",
              "      <td>20</td>\n",
              "      <td>2013-11-21 00:00:00</td>\n",
              "      <td>541 Del Medio Avenue</td>\n",
              "      <td>Mountain View</td>\n",
              "      <td>CA</td>\n",
              "      <td>USA</td>\n",
              "      <td>94040-111</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>143</th>\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>144</th>\n",
              "      <td>407</td>\n",
              "      <td>23</td>\n",
              "      <td>2013-12-04 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>1.98</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>145</th>\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",
              "    <tr>\n",
              "      <th>146</th>\n",
              "      <td>409</td>\n",
              "      <td>29</td>\n",
              "      <td>2013-12-06 00:00:00</td>\n",
              "      <td>796 Dundas Street West</td>\n",
              "      <td>Toronto</td>\n",
              "      <td>ON</td>\n",
              "      <td>Canada</td>\n",
              "      <td>M6J 1V1</td>\n",
              "      <td>5.94</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>147 rows × 9 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "     InvoiceId  CustomerId  ... BillingPostalCode  Total\n",
              "0            4          14  ...           T6G 2C7   8.91\n",
              "1            5          23  ...              2113  13.86\n",
              "2           13          16  ...        94043-1351   0.99\n",
              "3           14          17  ...        98052-8300   1.98\n",
              "4           15          19  ...             95014   1.98\n",
              "..         ...         ...  ...               ...    ...\n",
              "142        405          20  ...         94040-111   0.99\n",
              "143        406          21  ...             89503   1.98\n",
              "144        407          23  ...              2113   1.98\n",
              "145        408          25  ...             53703   3.96\n",
              "146        409          29  ...           M6J 1V1   5.94\n",
              "\n",
              "[147 rows x 9 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 20
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "QQEkwmCKdnvs",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 419
        },
        "outputId": "0b42b3da-0dc4-4b8a-c5d3-0bbcb2eeaf46"
      },
      "source": [
        "# Find all invoice in 2011\n",
        "\n",
        "q='SELECT * FROM invoices WHERE InvoiceDate LIKE \"2011%\"'\n",
        "pd.read_sql_query(q,conn)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>167</td>\n",
              "      <td>26</td>\n",
              "      <td>2011-01-02 00:00:00</td>\n",
              "      <td>2211 W Berry Street</td>\n",
              "      <td>Fort Worth</td>\n",
              "      <td>TX</td>\n",
              "      <td>USA</td>\n",
              "      <td>76110</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>168</td>\n",
              "      <td>27</td>\n",
              "      <td>2011-01-15 00:00:00</td>\n",
              "      <td>1033 N Park Ave</td>\n",
              "      <td>Tucson</td>\n",
              "      <td>AZ</td>\n",
              "      <td>USA</td>\n",
              "      <td>85719</td>\n",
              "      <td>1.98</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>169</td>\n",
              "      <td>29</td>\n",
              "      <td>2011-01-15 00:00:00</td>\n",
              "      <td>796 Dundas Street West</td>\n",
              "      <td>Toronto</td>\n",
              "      <td>ON</td>\n",
              "      <td>Canada</td>\n",
              "      <td>M6J 1V1</td>\n",
              "      <td>1.98</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>170</td>\n",
              "      <td>31</td>\n",
              "      <td>2011-01-16 00:00:00</td>\n",
              "      <td>194A Chain Lake Drive</td>\n",
              "      <td>Halifax</td>\n",
              "      <td>NS</td>\n",
              "      <td>Canada</td>\n",
              "      <td>B3S 1C5</td>\n",
              "      <td>3.96</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>171</td>\n",
              "      <td>35</td>\n",
              "      <td>2011-01-17 00:00:00</td>\n",
              "      <td>Rua dos Campeões Europeus de Viena, 4350</td>\n",
              "      <td>Porto</td>\n",
              "      <td>None</td>\n",
              "      <td>Portugal</td>\n",
              "      <td>None</td>\n",
              "      <td>5.94</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",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>78</th>\n",
              "      <td>245</td>\n",
              "      <td>32</td>\n",
              "      <td>2011-12-22 00:00:00</td>\n",
              "      <td>696 Osborne Street</td>\n",
              "      <td>Winnipeg</td>\n",
              "      <td>MB</td>\n",
              "      <td>Canada</td>\n",
              "      <td>R3L 2B9</td>\n",
              "      <td>1.98</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>79</th>\n",
              "      <td>246</td>\n",
              "      <td>34</td>\n",
              "      <td>2011-12-22 00:00:00</td>\n",
              "      <td>Rua da Assunção 53</td>\n",
              "      <td>Lisbon</td>\n",
              "      <td>None</td>\n",
              "      <td>Portugal</td>\n",
              "      <td>None</td>\n",
              "      <td>1.98</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>80</th>\n",
              "      <td>247</td>\n",
              "      <td>36</td>\n",
              "      <td>2011-12-23 00:00:00</td>\n",
              "      <td>Tauentzienstraße 8</td>\n",
              "      <td>Berlin</td>\n",
              "      <td>None</td>\n",
              "      <td>Germany</td>\n",
              "      <td>10789</td>\n",
              "      <td>3.96</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>81</th>\n",
              "      <td>248</td>\n",
              "      <td>40</td>\n",
              "      <td>2011-12-24 00:00:00</td>\n",
              "      <td>8, Rue Hanovre</td>\n",
              "      <td>Paris</td>\n",
              "      <td>None</td>\n",
              "      <td>France</td>\n",
              "      <td>75002</td>\n",
              "      <td>5.94</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>82</th>\n",
              "      <td>249</td>\n",
              "      <td>46</td>\n",
              "      <td>2011-12-27 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>8.91</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>83 rows × 9 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "    InvoiceId  CustomerId  ... BillingPostalCode Total\n",
              "0         167          26  ...             76110  0.99\n",
              "1         168          27  ...             85719  1.98\n",
              "2         169          29  ...           M6J 1V1  1.98\n",
              "3         170          31  ...           B3S 1C5  3.96\n",
              "4         171          35  ...              None  5.94\n",
              "..        ...         ...  ...               ...   ...\n",
              "78        245          32  ...           R3L 2B9  1.98\n",
              "79        246          34  ...              None  1.98\n",
              "80        247          36  ...             10789  3.96\n",
              "81        248          40  ...             75002  5.94\n",
              "82        249          46  ...              None  8.91\n",
              "\n",
              "[83 rows x 9 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 39
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "qvMvKof-eCSF",
        "colab_type": "text"
      },
      "source": [
        "### Filtering and Sorting query results\n",
        "\n",
        "SQL provides a convenient way to discard rows that have a duplicate column value by using the **DISTINCT** keyword:\n",
        "\n",
        "```\n",
        "SELECT DISTINCT column, another_column, …\n",
        "FROM mytable\n",
        "WHERE condition(s);\n",
        "```\n",
        "\n",
        "SQL provides a way to sort your results by a given column in ascending or descending order using the **ORDER BY** clause:\n",
        "\n",
        "```\n",
        "SELECT column, another_column, …\n",
        "FROM mytable\n",
        "WHERE condition(s)\n",
        "ORDER BY column ASC/DESC;\n",
        "```\n",
        "\n",
        "Another clause which is commonly used with the **ORDER BY** clause are the **LIMIT** and **OFFSET** clauses, which are a useful optimization to indicate to the database the subset of the results you care about.\n",
        "The **LIMIT** will reduce the number of rows to return, and the optional **OFFSET** will specify where to begin counting the number rows from.\n",
        "\n",
        "```\n",
        "SELECT column, another_column, …\n",
        "FROM mytable\n",
        "WHERE condition(s)\n",
        "ORDER BY column ASC/DESC\n",
        "LIMIT num_limit OFFSET num_offset;\n",
        "```"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "BeSMm0_hiJ-M",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 793
        },
        "outputId": "d67fc93a-77f9-470b-ef78-3972b78eceec"
      },
      "source": [
        "# Find a list of all countries where customers live in\n",
        "q='SELECT DISTINCT Country FROM customers '\n",
        "pd.read_sql_query(q,conn)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>Country</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Brazil</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Germany</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Canada</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Norway</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Czech Republic</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>Austria</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>Belgium</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>Denmark</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>USA</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>Portugal</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>France</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>Finland</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>12</th>\n",
              "      <td>Hungary</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>13</th>\n",
              "      <td>Ireland</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14</th>\n",
              "      <td>Italy</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>15</th>\n",
              "      <td>Netherlands</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>16</th>\n",
              "      <td>Poland</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>17</th>\n",
              "      <td>Spain</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>18</th>\n",
              "      <td>Sweden</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>19</th>\n",
              "      <td>United Kingdom</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>20</th>\n",
              "      <td>Australia</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>21</th>\n",
              "      <td>Argentina</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>22</th>\n",
              "      <td>Chile</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>23</th>\n",
              "      <td>India</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "           Country\n",
              "0           Brazil\n",
              "1          Germany\n",
              "2           Canada\n",
              "3           Norway\n",
              "4   Czech Republic\n",
              "5          Austria\n",
              "6          Belgium\n",
              "7          Denmark\n",
              "8              USA\n",
              "9         Portugal\n",
              "10          France\n",
              "11         Finland\n",
              "12         Hungary\n",
              "13         Ireland\n",
              "14           Italy\n",
              "15     Netherlands\n",
              "16          Poland\n",
              "17           Spain\n",
              "18          Sweden\n",
              "19  United Kingdom\n",
              "20       Australia\n",
              "21       Argentina\n",
              "22           Chile\n",
              "23           India"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 42
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "dfXBBG4Id10d",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        },
        "outputId": "655a4383-3b3e-4de6-ec04-eda0ade9cb36"
      },
      "source": [
        "# Find the top 5 cheapest bill\n",
        "q = 'SELECT * FROM invoices ORDER BY Total LIMIT 5'\n",
        "pd.read_sql_query (q,conn) "
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>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>13</td>\n",
              "      <td>16</td>\n",
              "      <td>2009-02-19 00:00:00</td>\n",
              "      <td>1600 Amphitheatre Parkway</td>\n",
              "      <td>Mountain View</td>\n",
              "      <td>CA</td>\n",
              "      <td>USA</td>\n",
              "      <td>94043-1351</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>20</td>\n",
              "      <td>54</td>\n",
              "      <td>2009-03-22 00:00:00</td>\n",
              "      <td>110 Raeburn Pl</td>\n",
              "      <td>Edinburgh</td>\n",
              "      <td>None</td>\n",
              "      <td>United Kingdom</td>\n",
              "      <td>EH4 1HH</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>27</td>\n",
              "      <td>33</td>\n",
              "      <td>2009-04-22 00:00:00</td>\n",
              "      <td>5112 48 Street</td>\n",
              "      <td>Yellowknife</td>\n",
              "      <td>NT</td>\n",
              "      <td>Canada</td>\n",
              "      <td>X1A 1N6</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>34</td>\n",
              "      <td>12</td>\n",
              "      <td>2009-05-23 00:00:00</td>\n",
              "      <td>Praça Pio X, 119</td>\n",
              "      <td>Rio de Janeiro</td>\n",
              "      <td>RJ</td>\n",
              "      <td>Brazil</td>\n",
              "      <td>20040-020</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   InvoiceId  CustomerId  ... BillingPostalCode Total\n",
              "0          6          37  ...             60316  0.99\n",
              "1         13          16  ...        94043-1351  0.99\n",
              "2         20          54  ...           EH4 1HH  0.99\n",
              "3         27          33  ...           X1A 1N6  0.99\n",
              "4         34          12  ...         20040-020  0.99\n",
              "\n",
              "[5 rows x 9 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 56
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "0vdzbsP6fRoX",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        },
        "outputId": "ac6f861d-c6c1-4274-b3f5-b3accc25a318"
      },
      "source": [
        "# Find the last 5 invoice\n",
        "q= 'SELECT * FROM invoices ORDER BY InvoiceId DESC LIMIT 5'\n",
        "pd.read_sql_query(q,conn)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>412</td>\n",
              "      <td>58</td>\n",
              "      <td>2013-12-22 00:00:00</td>\n",
              "      <td>12,Community Centre</td>\n",
              "      <td>Delhi</td>\n",
              "      <td>None</td>\n",
              "      <td>India</td>\n",
              "      <td>110017</td>\n",
              "      <td>1.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>411</td>\n",
              "      <td>44</td>\n",
              "      <td>2013-12-14 00:00:00</td>\n",
              "      <td>Porthaninkatu 9</td>\n",
              "      <td>Helsinki</td>\n",
              "      <td>None</td>\n",
              "      <td>Finland</td>\n",
              "      <td>00530</td>\n",
              "      <td>13.86</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>410</td>\n",
              "      <td>35</td>\n",
              "      <td>2013-12-09 00:00:00</td>\n",
              "      <td>Rua dos Campeões Europeus de Viena, 4350</td>\n",
              "      <td>Porto</td>\n",
              "      <td>None</td>\n",
              "      <td>Portugal</td>\n",
              "      <td>None</td>\n",
              "      <td>8.91</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>409</td>\n",
              "      <td>29</td>\n",
              "      <td>2013-12-06 00:00:00</td>\n",
              "      <td>796 Dundas Street West</td>\n",
              "      <td>Toronto</td>\n",
              "      <td>ON</td>\n",
              "      <td>Canada</td>\n",
              "      <td>M6J 1V1</td>\n",
              "      <td>5.94</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\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",
              "</div>"
            ],
            "text/plain": [
              "   InvoiceId  CustomerId  ... BillingPostalCode  Total\n",
              "0        412          58  ...            110017   1.99\n",
              "1        411          44  ...             00530  13.86\n",
              "2        410          35  ...              None   8.91\n",
              "3        409          29  ...           M6J 1V1   5.94\n",
              "4        408          25  ...             53703   3.96\n",
              "\n",
              "[5 rows x 9 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 58
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "4VfbsutBfSQg",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        },
        "outputId": "43311f7a-a91e-49e7-c0aa-53b570e494a2"
      },
      "source": [
        "# Find the next five invoice\n",
        "q= 'SELECT * FROM invoices ORDER BY InvoiceId DESC LIMIT 5 OFFSET 5'\n",
        "pd.read_sql_query(q,conn)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>407</td>\n",
              "      <td>23</td>\n",
              "      <td>2013-12-04 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>1.98</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\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>2</th>\n",
              "      <td>405</td>\n",
              "      <td>20</td>\n",
              "      <td>2013-11-21 00:00:00</td>\n",
              "      <td>541 Del Medio Avenue</td>\n",
              "      <td>Mountain View</td>\n",
              "      <td>CA</td>\n",
              "      <td>USA</td>\n",
              "      <td>94040-111</td>\n",
              "      <td>0.99</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\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>4</th>\n",
              "      <td>403</td>\n",
              "      <td>56</td>\n",
              "      <td>2013-11-08 00:00:00</td>\n",
              "      <td>307 Macacha Güemes</td>\n",
              "      <td>Buenos Aires</td>\n",
              "      <td>None</td>\n",
              "      <td>Argentina</td>\n",
              "      <td>1106</td>\n",
              "      <td>8.91</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   InvoiceId  CustomerId  ... BillingPostalCode  Total\n",
              "0        407          23  ...              2113   1.98\n",
              "1        406          21  ...             89503   1.98\n",
              "2        405          20  ...         94040-111   0.99\n",
              "3        404           6  ...             14300  25.86\n",
              "4        403          56  ...              1106   8.91\n",
              "\n",
              "[5 rows x 9 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 61
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "fwzu4_qxhqkC",
        "colab_type": "text"
      },
      "source": [
        "### Practice simple SELECT query\n",
        "\n",
        "You've done a good job getting to this point! Let's practice writing query that use all keywords we've learned so far."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "X2B9ce79jCv7",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "pd.read_sql_query('___', conn).head()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "goGvMHl9j3LW",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 142
        },
        "outputId": "90e39306-2874-4336-c2f8-7d9356512eb1"
      },
      "source": [
        "# List all cities in Germany where the company has customers\n",
        "q= 'SELECT DISTINCT City FROM Customers WHERE Country = \"Germany\" '\n",
        "pd.read_sql_query(q, conn)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>City</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Stuttgart</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Berlin</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Frankfurt</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "        City\n",
              "0  Stuttgart\n",
              "1     Berlin\n",
              "2  Frankfurt"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 62
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Dx1W_6Pskj0y",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 1000
        },
        "outputId": "93cb774a-a5ee-4c54-d684-9cf4ae95bd64"
      },
      "source": [
        "# Order all customers in the USA by their first name\n",
        "q= 'SELECT * FROM Customers ORDER BY FirstName'\n",
        "pd.read_sql_query(q, conn)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>32</td>\n",
              "      <td>Aaron</td>\n",
              "      <td>Mitchell</td>\n",
              "      <td>None</td>\n",
              "      <td>696 Osborne Street</td>\n",
              "      <td>Winnipeg</td>\n",
              "      <td>MB</td>\n",
              "      <td>Canada</td>\n",
              "      <td>R3L 2B9</td>\n",
              "      <td>+1 (204) 452-6452</td>\n",
              "      <td>None</td>\n",
              "      <td>aaronmitchell@yahoo.ca</td>\n",
              "      <td>4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>11</td>\n",
              "      <td>Alexandre</td>\n",
              "      <td>Rocha</td>\n",
              "      <td>Banco do Brasil S.A.</td>\n",
              "      <td>Av. Paulista, 2022</td>\n",
              "      <td>São Paulo</td>\n",
              "      <td>SP</td>\n",
              "      <td>Brazil</td>\n",
              "      <td>01310-200</td>\n",
              "      <td>+55 (11) 3055-3278</td>\n",
              "      <td>+55 (11) 3055-8131</td>\n",
              "      <td>alero@uol.com.br</td>\n",
              "      <td>5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>7</td>\n",
              "      <td>Astrid</td>\n",
              "      <td>Gruber</td>\n",
              "      <td>None</td>\n",
              "      <td>Rotenturmstraße 4, 1010 Innere Stadt</td>\n",
              "      <td>Vienne</td>\n",
              "      <td>None</td>\n",
              "      <td>Austria</td>\n",
              "      <td>1010</td>\n",
              "      <td>+43 01 5134505</td>\n",
              "      <td>None</td>\n",
              "      <td>astrid.gruber@apple.at</td>\n",
              "      <td>5</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>39</td>\n",
              "      <td>Camille</td>\n",
              "      <td>Bernard</td>\n",
              "      <td>None</td>\n",
              "      <td>4, Rue Milton</td>\n",
              "      <td>Paris</td>\n",
              "      <td>None</td>\n",
              "      <td>France</td>\n",
              "      <td>75009</td>\n",
              "      <td>+33 01 49 70 65 65</td>\n",
              "      <td>None</td>\n",
              "      <td>camille.bernard@yahoo.fr</td>\n",
              "      <td>4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>8</td>\n",
              "      <td>Daan</td>\n",
              "      <td>Peeters</td>\n",
              "      <td>None</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>+32 02 219 03 03</td>\n",
              "      <td>None</td>\n",
              "      <td>daan_peeters@apple.be</td>\n",
              "      <td>4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>20</td>\n",
              "      <td>Dan</td>\n",
              "      <td>Miller</td>\n",
              "      <td>None</td>\n",
              "      <td>541 Del Medio Avenue</td>\n",
              "      <td>Mountain View</td>\n",
              "      <td>CA</td>\n",
              "      <td>USA</td>\n",
              "      <td>94040-111</td>\n",
              "      <td>+1 (650) 644-3358</td>\n",
              "      <td>None</td>\n",
              "      <td>dmiller@comcast.com</td>\n",
              "      <td>4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>56</td>\n",
              "      <td>Diego</td>\n",
              "      <td>Gutiérrez</td>\n",
              "      <td>None</td>\n",
              "      <td>307 Macacha Güemes</td>\n",
              "      <td>Buenos Aires</td>\n",
              "      <td>None</td>\n",
              "      <td>Argentina</td>\n",
              "      <td>1106</td>\n",
              "      <td>+54 (0)11 4311 4333</td>\n",
              "      <td>None</td>\n",
              "      <td>diego.gutierrez@yahoo.ar</td>\n",
              "      <td>4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>40</td>\n",
              "      <td>Dominique</td>\n",
              "      <td>Lefebvre</td>\n",
              "      <td>None</td>\n",
              "      <td>8, Rue Hanovre</td>\n",
              "      <td>Paris</td>\n",
              "      <td>None</td>\n",
              "      <td>France</td>\n",
              "      <td>75002</td>\n",
              "      <td>+33 01 47 42 71 71</td>\n",
              "      <td>None</td>\n",
              "      <td>dominiquelefebvre@gmail.com</td>\n",
              "      <td>4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>10</td>\n",
              "      <td>Eduardo</td>\n",
              "      <td>Martins</td>\n",
              "      <td>Woodstock Discos</td>\n",
              "      <td>Rua Dr. Falcão Filho, 155</td>\n",
              "      <td>São Paulo</td>\n",
              "      <td>SP</td>\n",
              "      <td>Brazil</td>\n",
              "      <td>01007-010</td>\n",
              "      <td>+55 (11) 3033-5446</td>\n",
              "      <td>+55 (11) 3033-4564</td>\n",
              "      <td>eduardo@woodstock.com.br</td>\n",
              "      <td>4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>30</td>\n",
              "      <td>Edward</td>\n",
              "      <td>Francis</td>\n",
              "      <td>None</td>\n",
              "      <td>230 Elgin Street</td>\n",
              "      <td>Ottawa</td>\n",
              "      <td>ON</td>\n",
              "      <td>Canada</td>\n",
              "      <td>K2P 1L7</td>\n",
              "      <td>+1 (613) 234-3322</td>\n",
              "      <td>None</td>\n",
              "      <td>edfrancis@yachoo.ca</td>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>33</td>\n",
              "      <td>Ellie</td>\n",
              "      <td>Sullivan</td>\n",
              "      <td>None</td>\n",
              "      <td>5112 48 Street</td>\n",
              "      <td>Yellowknife</td>\n",
              "      <td>NT</td>\n",
              "      <td>Canada</td>\n",
              "      <td>X1A 1N6</td>\n",
              "      <td>+1 (867) 920-2233</td>\n",
              "      <td>None</td>\n",
              "      <td>ellie.sullivan@shaw.ca</td>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>12</th>\n",
              "      <td>52</td>\n",
              "      <td>Emma</td>\n",
              "      <td>Jones</td>\n",
              "      <td>None</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>+44 020 7707 0707</td>\n",
              "      <td>None</td>\n",
              "      <td>emma_jones@hotmail.com</td>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>13</th>\n",
              "      <td>50</td>\n",
              "      <td>Enrique</td>\n",
              "      <td>Muñoz</td>\n",
              "      <td>None</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>+34 914 454 454</td>\n",
              "      <td>None</td>\n",
              "      <td>enrique_munoz@yahoo.es</td>\n",
              "      <td>5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14</th>\n",
              "      <td>13</td>\n",
              "      <td>Fernanda</td>\n",
              "      <td>Ramos</td>\n",
              "      <td>None</td>\n",
              "      <td>Qe 7 Bloco G</td>\n",
              "      <td>Brasília</td>\n",
              "      <td>DF</td>\n",
              "      <td>Brazil</td>\n",
              "      <td>71020-677</td>\n",
              "      <td>+55 (61) 3363-5547</td>\n",
              "      <td>+55 (61) 3363-7855</td>\n",
              "      <td>fernadaramos4@uol.com.br</td>\n",
              "      <td>4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>15</th>\n",
              "      <td>16</td>\n",
              "      <td>Frank</td>\n",
              "      <td>Harris</td>\n",
              "      <td>Google Inc.</td>\n",
              "      <td>1600 Amphitheatre Parkway</td>\n",
              "      <td>Mountain View</td>\n",
              "      <td>CA</td>\n",
              "      <td>USA</td>\n",
              "      <td>94043-1351</td>\n",
              "      <td>+1 (650) 253-0000</td>\n",
              "      <td>+1 (650) 253-0000</td>\n",
              "      <td>fharris@google.com</td>\n",
              "      <td>4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>16</th>\n",
              "      <td>24</td>\n",
              "      <td>Frank</td>\n",
              "      <td>Ralston</td>\n",
              "      <td>None</td>\n",
              "      <td>162 E Superior Street</td>\n",
              "      <td>Chicago</td>\n",
              "      <td>IL</td>\n",
              "      <td>USA</td>\n",
              "      <td>60611</td>\n",
              "      <td>+1 (312) 332-3232</td>\n",
              "      <td>None</td>\n",
              "      <td>fralston@gmail.com</td>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>17</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",
              "    <tr>\n",
              "      <th>18</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>19</th>\n",
              "      <td>37</td>\n",
              "      <td>Fynn</td>\n",
              "      <td>Zimmermann</td>\n",
              "      <td>None</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>+49 069 40598889</td>\n",
              "      <td>None</td>\n",
              "      <td>fzimmermann@yahoo.de</td>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>20</th>\n",
              "      <td>36</td>\n",
              "      <td>Hannah</td>\n",
              "      <td>Schneider</td>\n",
              "      <td>None</td>\n",
              "      <td>Tauentzienstraße 8</td>\n",
              "      <td>Berlin</td>\n",
              "      <td>None</td>\n",
              "      <td>Germany</td>\n",
              "      <td>10789</td>\n",
              "      <td>+49 030 26550280</td>\n",
              "      <td>None</td>\n",
              "      <td>hannah.schneider@yahoo.de</td>\n",
              "      <td>5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>21</th>\n",
              "      <td>22</td>\n",
              "      <td>Heather</td>\n",
              "      <td>Leacock</td>\n",
              "      <td>None</td>\n",
              "      <td>120 S Orange Ave</td>\n",
              "      <td>Orlando</td>\n",
              "      <td>FL</td>\n",
              "      <td>USA</td>\n",
              "      <td>32801</td>\n",
              "      <td>+1 (407) 999-7788</td>\n",
              "      <td>None</td>\n",
              "      <td>hleacock@gmail.com</td>\n",
              "      <td>4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>22</th>\n",
              "      <td>6</td>\n",
              "      <td>Helena</td>\n",
              "      <td>Holý</td>\n",
              "      <td>None</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>+420 2 4177 0449</td>\n",
              "      <td>None</td>\n",
              "      <td>hholy@gmail.com</td>\n",
              "      <td>5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>23</th>\n",
              "      <td>46</td>\n",
              "      <td>Hugh</td>\n",
              "      <td>O'Reilly</td>\n",
              "      <td>None</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>+353 01 6792424</td>\n",
              "      <td>None</td>\n",
              "      <td>hughoreilly@apple.ie</td>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>24</th>\n",
              "      <td>43</td>\n",
              "      <td>Isabelle</td>\n",
              "      <td>Mercier</td>\n",
              "      <td>None</td>\n",
              "      <td>68, Rue Jouvence</td>\n",
              "      <td>Dijon</td>\n",
              "      <td>None</td>\n",
              "      <td>France</td>\n",
              "      <td>21000</td>\n",
              "      <td>+33 03 80 73 66 99</td>\n",
              "      <td>None</td>\n",
              "      <td>isabelle_mercier@apple.fr</td>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>25</th>\n",
              "      <td>17</td>\n",
              "      <td>Jack</td>\n",
              "      <td>Smith</td>\n",
              "      <td>Microsoft Corporation</td>\n",
              "      <td>1 Microsoft Way</td>\n",
              "      <td>Redmond</td>\n",
              "      <td>WA</td>\n",
              "      <td>USA</td>\n",
              "      <td>98052-8300</td>\n",
              "      <td>+1 (425) 882-8080</td>\n",
              "      <td>+1 (425) 882-8081</td>\n",
              "      <td>jacksmith@microsoft.com</td>\n",
              "      <td>5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>26</th>\n",
              "      <td>15</td>\n",
              "      <td>Jennifer</td>\n",
              "      <td>Peterson</td>\n",
              "      <td>Rogers Canada</td>\n",
              "      <td>700 W Pender Street</td>\n",
              "      <td>Vancouver</td>\n",
              "      <td>BC</td>\n",
              "      <td>Canada</td>\n",
              "      <td>V6C 1G8</td>\n",
              "      <td>+1 (604) 688-2255</td>\n",
              "      <td>+1 (604) 688-8756</td>\n",
              "      <td>jenniferp@rogers.ca</td>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>27</th>\n",
              "      <td>51</td>\n",
              "      <td>Joakim</td>\n",
              "      <td>Johansson</td>\n",
              "      <td>None</td>\n",
              "      <td>Celsiusg. 9</td>\n",
              "      <td>Stockholm</td>\n",
              "      <td>None</td>\n",
              "      <td>Sweden</td>\n",
              "      <td>11230</td>\n",
              "      <td>+46 08-651 52 52</td>\n",
              "      <td>None</td>\n",
              "      <td>joakim.johansson@yahoo.se</td>\n",
              "      <td>5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>28</th>\n",
              "      <td>48</td>\n",
              "      <td>Johannes</td>\n",
              "      <td>Van der Berg</td>\n",
              "      <td>None</td>\n",
              "      <td>Lijnbaansgracht 120bg</td>\n",
              "      <td>Amsterdam</td>\n",
              "      <td>VV</td>\n",
              "      <td>Netherlands</td>\n",
              "      <td>1016</td>\n",
              "      <td>+31 020 6223130</td>\n",
              "      <td>None</td>\n",
              "      <td>johavanderberg@yahoo.nl</td>\n",
              "      <td>5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>29</th>\n",
              "      <td>23</td>\n",
              "      <td>John</td>\n",
              "      <td>Gordon</td>\n",
              "      <td>None</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>+1 (617) 522-1333</td>\n",
              "      <td>None</td>\n",
              "      <td>johngordon22@yahoo.com</td>\n",
              "      <td>4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>30</th>\n",
              "      <td>34</td>\n",
              "      <td>João</td>\n",
              "      <td>Fernandes</td>\n",
              "      <td>None</td>\n",
              "      <td>Rua da Assunção 53</td>\n",
              "      <td>Lisbon</td>\n",
              "      <td>None</td>\n",
              "      <td>Portugal</td>\n",
              "      <td>None</td>\n",
              "      <td>+351 (213) 466-111</td>\n",
              "      <td>None</td>\n",
              "      <td>jfernandes@yahoo.pt</td>\n",
              "      <td>4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>31</th>\n",
              "      <td>28</td>\n",
              "      <td>Julia</td>\n",
              "      <td>Barnett</td>\n",
              "      <td>None</td>\n",
              "      <td>302 S 700 E</td>\n",
              "      <td>Salt Lake City</td>\n",
              "      <td>UT</td>\n",
              "      <td>USA</td>\n",
              "      <td>84102</td>\n",
              "      <td>+1 (801) 531-7272</td>\n",
              "      <td>None</td>\n",
              "      <td>jubarnett@gmail.com</td>\n",
              "      <td>5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>32</th>\n",
              "      <td>9</td>\n",
              "      <td>Kara</td>\n",
              "      <td>Nielsen</td>\n",
              "      <td>None</td>\n",
              "      <td>Sønder Boulevard 51</td>\n",
              "      <td>Copenhagen</td>\n",
              "      <td>None</td>\n",
              "      <td>Denmark</td>\n",
              "      <td>1720</td>\n",
              "      <td>+453 3331 9991</td>\n",
              "      <td>None</td>\n",
              "      <td>kara.nielsen@jubii.dk</td>\n",
              "      <td>4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>33</th>\n",
              "      <td>21</td>\n",
              "      <td>Kathy</td>\n",
              "      <td>Chase</td>\n",
              "      <td>None</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 (775) 223-7665</td>\n",
              "      <td>None</td>\n",
              "      <td>kachase@hotmail.com</td>\n",
              "      <td>5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>34</th>\n",
              "      <td>45</td>\n",
              "      <td>Ladislav</td>\n",
              "      <td>Kovács</td>\n",
              "      <td>None</td>\n",
              "      <td>Erzsébet krt. 58.</td>\n",
              "      <td>Budapest</td>\n",
              "      <td>None</td>\n",
              "      <td>Hungary</td>\n",
              "      <td>H-1073</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>ladislav_kovacs@apple.hu</td>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>35</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>36</th>\n",
              "      <td>47</td>\n",
              "      <td>Lucas</td>\n",
              "      <td>Mancini</td>\n",
              "      <td>None</td>\n",
              "      <td>Via Degli Scipioni, 43</td>\n",
              "      <td>Rome</td>\n",
              "      <td>RM</td>\n",
              "      <td>Italy</td>\n",
              "      <td>00192</td>\n",
              "      <td>+39 06 39733434</td>\n",
              "      <td>None</td>\n",
              "      <td>lucas.mancini@yahoo.it</td>\n",
              "      <td>5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>37</th>\n",
              "      <td>57</td>\n",
              "      <td>Luis</td>\n",
              "      <td>Rojas</td>\n",
              "      <td>None</td>\n",
              "      <td>Calle Lira, 198</td>\n",
              "      <td>Santiago</td>\n",
              "      <td>None</td>\n",
              "      <td>Chile</td>\n",
              "      <td>None</td>\n",
              "      <td>+56 (0)2 635 4444</td>\n",
              "      <td>None</td>\n",
              "      <td>luisrojas@yahoo.cl</td>\n",
              "      <td>5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>38</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>39</th>\n",
              "      <td>35</td>\n",
              "      <td>Madalena</td>\n",
              "      <td>Sampaio</td>\n",
              "      <td>None</td>\n",
              "      <td>Rua dos Campeões Europeus de Viena, 4350</td>\n",
              "      <td>Porto</td>\n",
              "      <td>None</td>\n",
              "      <td>Portugal</td>\n",
              "      <td>None</td>\n",
              "      <td>+351 (225) 022-448</td>\n",
              "      <td>None</td>\n",
              "      <td>masampaio@sapo.pt</td>\n",
              "      <td>4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>40</th>\n",
              "      <td>58</td>\n",
              "      <td>Manoj</td>\n",
              "      <td>Pareek</td>\n",
              "      <td>None</td>\n",
              "      <td>12,Community Centre</td>\n",
              "      <td>Delhi</td>\n",
              "      <td>None</td>\n",
              "      <td>India</td>\n",
              "      <td>110017</td>\n",
              "      <td>+91 0124 39883988</td>\n",
              "      <td>None</td>\n",
              "      <td>manoj.pareek@rediff.com</td>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>41</th>\n",
              "      <td>41</td>\n",
              "      <td>Marc</td>\n",
              "      <td>Dubois</td>\n",
              "      <td>None</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>+33 04 78 30 30 30</td>\n",
              "      <td>None</td>\n",
              "      <td>marc.dubois@hotmail.com</td>\n",
              "      <td>5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>42</th>\n",
              "      <td>14</td>\n",
              "      <td>Mark</td>\n",
              "      <td>Philips</td>\n",
              "      <td>Telus</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>+1 (780) 434-4554</td>\n",
              "      <td>+1 (780) 434-5565</td>\n",
              "      <td>mphilips12@shaw.ca</td>\n",
              "      <td>5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>43</th>\n",
              "      <td>55</td>\n",
              "      <td>Mark</td>\n",
              "      <td>Taylor</td>\n",
              "      <td>None</td>\n",
              "      <td>421 Bourke Street</td>\n",
              "      <td>Sidney</td>\n",
              "      <td>NSW</td>\n",
              "      <td>Australia</td>\n",
              "      <td>2010</td>\n",
              "      <td>+61 (02) 9332 3633</td>\n",
              "      <td>None</td>\n",
              "      <td>mark.taylor@yahoo.au</td>\n",
              "      <td>4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>44</th>\n",
              "      <td>31</td>\n",
              "      <td>Martha</td>\n",
              "      <td>Silk</td>\n",
              "      <td>None</td>\n",
              "      <td>194A Chain Lake Drive</td>\n",
              "      <td>Halifax</td>\n",
              "      <td>NS</td>\n",
              "      <td>Canada</td>\n",
              "      <td>B3S 1C5</td>\n",
              "      <td>+1 (902) 450-0450</td>\n",
              "      <td>None</td>\n",
              "      <td>marthasilk@gmail.com</td>\n",
              "      <td>5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>45</th>\n",
              "      <td>18</td>\n",
              "      <td>Michelle</td>\n",
              "      <td>Brooks</td>\n",
              "      <td>None</td>\n",
              "      <td>627 Broadway</td>\n",
              "      <td>New York</td>\n",
              "      <td>NY</td>\n",
              "      <td>USA</td>\n",
              "      <td>10012-2612</td>\n",
              "      <td>+1 (212) 221-3546</td>\n",
              "      <td>+1 (212) 221-4679</td>\n",
              "      <td>michelleb@aol.com</td>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>46</th>\n",
              "      <td>38</td>\n",
              "      <td>Niklas</td>\n",
              "      <td>Schröder</td>\n",
              "      <td>None</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>+49 030 2141444</td>\n",
              "      <td>None</td>\n",
              "      <td>nschroder@surfeu.de</td>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>47</th>\n",
              "      <td>27</td>\n",
              "      <td>Patrick</td>\n",
              "      <td>Gray</td>\n",
              "      <td>None</td>\n",
              "      <td>1033 N Park Ave</td>\n",
              "      <td>Tucson</td>\n",
              "      <td>AZ</td>\n",
              "      <td>USA</td>\n",
              "      <td>85719</td>\n",
              "      <td>+1 (520) 622-4200</td>\n",
              "      <td>None</td>\n",
              "      <td>patrick.gray@aol.com</td>\n",
              "      <td>4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>48</th>\n",
              "      <td>53</td>\n",
              "      <td>Phil</td>\n",
              "      <td>Hughes</td>\n",
              "      <td>None</td>\n",
              "      <td>113 Lupus St</td>\n",
              "      <td>London</td>\n",
              "      <td>None</td>\n",
              "      <td>United Kingdom</td>\n",
              "      <td>SW1V 3EN</td>\n",
              "      <td>+44 020 7976 5722</td>\n",
              "      <td>None</td>\n",
              "      <td>phil.hughes@gmail.com</td>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>49</th>\n",
              "      <td>59</td>\n",
              "      <td>Puja</td>\n",
              "      <td>Srivastava</td>\n",
              "      <td>None</td>\n",
              "      <td>3,Raj Bhavan Road</td>\n",
              "      <td>Bangalore</td>\n",
              "      <td>None</td>\n",
              "      <td>India</td>\n",
              "      <td>560001</td>\n",
              "      <td>+91 080 22289999</td>\n",
              "      <td>None</td>\n",
              "      <td>puja_srivastava@yahoo.in</td>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>50</th>\n",
              "      <td>26</td>\n",
              "      <td>Richard</td>\n",
              "      <td>Cunningham</td>\n",
              "      <td>None</td>\n",
              "      <td>2211 W Berry Street</td>\n",
              "      <td>Fort Worth</td>\n",
              "      <td>TX</td>\n",
              "      <td>USA</td>\n",
              "      <td>76110</td>\n",
              "      <td>+1 (817) 924-7272</td>\n",
              "      <td>None</td>\n",
              "      <td>ricunningham@hotmail.com</td>\n",
              "      <td>4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>51</th>\n",
              "      <td>29</td>\n",
              "      <td>Robert</td>\n",
              "      <td>Brown</td>\n",
              "      <td>None</td>\n",
              "      <td>796 Dundas Street West</td>\n",
              "      <td>Toronto</td>\n",
              "      <td>ON</td>\n",
              "      <td>Canada</td>\n",
              "      <td>M6J 1V1</td>\n",
              "      <td>+1 (416) 363-8888</td>\n",
              "      <td>None</td>\n",
              "      <td>robbrown@shaw.ca</td>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>52</th>\n",
              "      <td>12</td>\n",
              "      <td>Roberto</td>\n",
              "      <td>Almeida</td>\n",
              "      <td>Riotur</td>\n",
              "      <td>Praça Pio X, 119</td>\n",
              "      <td>Rio de Janeiro</td>\n",
              "      <td>RJ</td>\n",
              "      <td>Brazil</td>\n",
              "      <td>20040-020</td>\n",
              "      <td>+55 (21) 2271-7000</td>\n",
              "      <td>+55 (21) 2271-7070</td>\n",
              "      <td>roberto.almeida@riotur.gov.br</td>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>53</th>\n",
              "      <td>49</td>\n",
              "      <td>Stanisław</td>\n",
              "      <td>Wójcik</td>\n",
              "      <td>None</td>\n",
              "      <td>Ordynacka 10</td>\n",
              "      <td>Warsaw</td>\n",
              "      <td>None</td>\n",
              "      <td>Poland</td>\n",
              "      <td>00-358</td>\n",
              "      <td>+48 22 828 37 39</td>\n",
              "      <td>None</td>\n",
              "      <td>stanisław.wójcik@wp.pl</td>\n",
              "      <td>4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>54</th>\n",
              "      <td>54</td>\n",
              "      <td>Steve</td>\n",
              "      <td>Murray</td>\n",
              "      <td>None</td>\n",
              "      <td>110 Raeburn Pl</td>\n",
              "      <td>Edinburgh</td>\n",
              "      <td>None</td>\n",
              "      <td>United Kingdom</td>\n",
              "      <td>EH4 1HH</td>\n",
              "      <td>+44 0131 315 3300</td>\n",
              "      <td>None</td>\n",
              "      <td>steve.murray@yahoo.uk</td>\n",
              "      <td>5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>55</th>\n",
              "      <td>44</td>\n",
              "      <td>Terhi</td>\n",
              "      <td>Hämäläinen</td>\n",
              "      <td>None</td>\n",
              "      <td>Porthaninkatu 9</td>\n",
              "      <td>Helsinki</td>\n",
              "      <td>None</td>\n",
              "      <td>Finland</td>\n",
              "      <td>00530</td>\n",
              "      <td>+358 09 870 2000</td>\n",
              "      <td>None</td>\n",
              "      <td>terhi.hamalainen@apple.fi</td>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>56</th>\n",
              "      <td>19</td>\n",
              "      <td>Tim</td>\n",
              "      <td>Goyer</td>\n",
              "      <td>Apple Inc.</td>\n",
              "      <td>1 Infinite Loop</td>\n",
              "      <td>Cupertino</td>\n",
              "      <td>CA</td>\n",
              "      <td>USA</td>\n",
              "      <td>95014</td>\n",
              "      <td>+1 (408) 996-1010</td>\n",
              "      <td>+1 (408) 996-1011</td>\n",
              "      <td>tgoyer@apple.com</td>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>57</th>\n",
              "      <td>25</td>\n",
              "      <td>Victor</td>\n",
              "      <td>Stevens</td>\n",
              "      <td>None</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>+1 (608) 257-0597</td>\n",
              "      <td>None</td>\n",
              "      <td>vstevens@yahoo.com</td>\n",
              "      <td>5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>58</th>\n",
              "      <td>42</td>\n",
              "      <td>Wyatt</td>\n",
              "      <td>Girard</td>\n",
              "      <td>None</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>+33 05 56 96 96 96</td>\n",
              "      <td>None</td>\n",
              "      <td>wyatt.girard@yahoo.fr</td>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "    CustomerId  FirstName  ...                          Email SupportRepId\n",
              "0           32      Aaron  ...         aaronmitchell@yahoo.ca            4\n",
              "1           11  Alexandre  ...               alero@uol.com.br            5\n",
              "2            7     Astrid  ...         astrid.gruber@apple.at            5\n",
              "3            4      Bjørn  ...          bjorn.hansen@yahoo.no            4\n",
              "4           39    Camille  ...       camille.bernard@yahoo.fr            4\n",
              "5            8       Daan  ...          daan_peeters@apple.be            4\n",
              "6           20        Dan  ...            dmiller@comcast.com            4\n",
              "7           56      Diego  ...       diego.gutierrez@yahoo.ar            4\n",
              "8           40  Dominique  ...    dominiquelefebvre@gmail.com            4\n",
              "9           10    Eduardo  ...       eduardo@woodstock.com.br            4\n",
              "10          30     Edward  ...            edfrancis@yachoo.ca            3\n",
              "11          33      Ellie  ...         ellie.sullivan@shaw.ca            3\n",
              "12          52       Emma  ...         emma_jones@hotmail.com            3\n",
              "13          50    Enrique  ...         enrique_munoz@yahoo.es            5\n",
              "14          13   Fernanda  ...       fernadaramos4@uol.com.br            4\n",
              "15          16      Frank  ...             fharris@google.com            4\n",
              "16          24      Frank  ...             fralston@gmail.com            3\n",
              "17           5  František  ...       frantisekw@jetbrains.com            4\n",
              "18           3   François  ...            ftremblay@gmail.com            3\n",
              "19          37       Fynn  ...           fzimmermann@yahoo.de            3\n",
              "20          36     Hannah  ...      hannah.schneider@yahoo.de            5\n",
              "21          22    Heather  ...             hleacock@gmail.com            4\n",
              "22           6     Helena  ...                hholy@gmail.com            5\n",
              "23          46       Hugh  ...           hughoreilly@apple.ie            3\n",
              "24          43   Isabelle  ...      isabelle_mercier@apple.fr            3\n",
              "25          17       Jack  ...        jacksmith@microsoft.com            5\n",
              "26          15   Jennifer  ...            jenniferp@rogers.ca            3\n",
              "27          51     Joakim  ...      joakim.johansson@yahoo.se            5\n",
              "28          48   Johannes  ...        johavanderberg@yahoo.nl            5\n",
              "29          23       John  ...         johngordon22@yahoo.com            4\n",
              "30          34       João  ...            jfernandes@yahoo.pt            4\n",
              "31          28      Julia  ...            jubarnett@gmail.com            5\n",
              "32           9       Kara  ...          kara.nielsen@jubii.dk            4\n",
              "33          21      Kathy  ...            kachase@hotmail.com            5\n",
              "34          45   Ladislav  ...       ladislav_kovacs@apple.hu            3\n",
              "35           2     Leonie  ...          leonekohler@surfeu.de            5\n",
              "36          47      Lucas  ...         lucas.mancini@yahoo.it            5\n",
              "37          57       Luis  ...             luisrojas@yahoo.cl            5\n",
              "38           1       Luís  ...           luisg@embraer.com.br            3\n",
              "39          35   Madalena  ...              masampaio@sapo.pt            4\n",
              "40          58      Manoj  ...        manoj.pareek@rediff.com            3\n",
              "41          41       Marc  ...        marc.dubois@hotmail.com            5\n",
              "42          14       Mark  ...             mphilips12@shaw.ca            5\n",
              "43          55       Mark  ...           mark.taylor@yahoo.au            4\n",
              "44          31     Martha  ...           marthasilk@gmail.com            5\n",
              "45          18   Michelle  ...              michelleb@aol.com            3\n",
              "46          38     Niklas  ...            nschroder@surfeu.de            3\n",
              "47          27    Patrick  ...           patrick.gray@aol.com            4\n",
              "48          53       Phil  ...          phil.hughes@gmail.com            3\n",
              "49          59       Puja  ...       puja_srivastava@yahoo.in            3\n",
              "50          26    Richard  ...       ricunningham@hotmail.com            4\n",
              "51          29     Robert  ...               robbrown@shaw.ca            3\n",
              "52          12    Roberto  ...  roberto.almeida@riotur.gov.br            3\n",
              "53          49  Stanisław  ...         stanisław.wójcik@wp.pl            4\n",
              "54          54      Steve  ...          steve.murray@yahoo.uk            5\n",
              "55          44      Terhi  ...      terhi.hamalainen@apple.fi            3\n",
              "56          19        Tim  ...               tgoyer@apple.com            3\n",
              "57          25     Victor  ...             vstevens@yahoo.com            5\n",
              "58          42      Wyatt  ...          wyatt.girard@yahoo.fr            3\n",
              "\n",
              "[59 rows x 13 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 63
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "S91_frJ0hTMm",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 142
        },
        "outputId": "87be5778-c717-4aff-adad-7df172affb5d"
      },
      "source": [
        "# List three most expensive bill in Boston, USA\n",
        "q= 'SELECT * FROM invoices WHERE  BillingCity = \"Boston\" ORDER BY Total DESC LIMIT 3'\n",
        "pd.read_sql_query(q, conn)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>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",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>60</td>\n",
              "      <td>23</td>\n",
              "      <td>2009-09-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>8.91</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>234</td>\n",
              "      <td>23</td>\n",
              "      <td>2011-10-23 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>5.94</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   InvoiceId  CustomerId  ... BillingPostalCode  Total\n",
              "0          5          23  ...              2113  13.86\n",
              "1         60          23  ...              2113   8.91\n",
              "2        234          23  ...              2113   5.94\n",
              "\n",
              "[3 rows x 9 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 68
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "P4C0oAIEhbcA",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 111
        },
        "outputId": "9bcdfeb2-0b71-4ee6-a846-b513f9ff56ac"
      },
      "source": [
        "# List the third and fourth most expensive bill in 2012\n",
        "q= 'SELECT * FROM invoices WHERE  InvoiceDate LIKE \"2012%\" ORDER BY Total DESC LIMIT 2 OFFSET 2 '\n",
        "pd.read_sql_query(q, conn)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>313</td>\n",
              "      <td>43</td>\n",
              "      <td>2012-10-06 00:00:00</td>\n",
              "      <td>68, Rue Jouvence</td>\n",
              "      <td>Dijon</td>\n",
              "      <td>None</td>\n",
              "      <td>France</td>\n",
              "      <td>21000</td>\n",
              "      <td>16.86</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>250</td>\n",
              "      <td>55</td>\n",
              "      <td>2012-01-01 00:00:00</td>\n",
              "      <td>421 Bourke Street</td>\n",
              "      <td>Sidney</td>\n",
              "      <td>NSW</td>\n",
              "      <td>Australia</td>\n",
              "      <td>2010</td>\n",
              "      <td>13.86</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   InvoiceId  CustomerId  ... BillingPostalCode  Total\n",
              "0        313          43  ...             21000  16.86\n",
              "1        250          55  ...              2010  13.86\n",
              "\n",
              "[2 rows x 9 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 70
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "E4tKvuDmh3DF",
        "colab_type": "text"
      },
      "source": [
        "### Multi-table queries with JOINs\n",
        "\n",
        "Up to now, we've been working with a single table, but entity data in the real world is often broken down into pieces and stored across multiple tables.\n",
        "\n",
        "Tables that share information about a single entity need to have a **primary key** that identifies that entity **uniquely** across the database. One common primary key type is an auto-incrementing integer (because they are space efficient), but it can also be a string, hashed value, so long as it is unique.\n",
        "\n",
        "Using the **JOIN** clause in a query, we can combine row data across two separate tables using this unique key. The first of the joins that we will introduce is the **INNER JOIN**.\n",
        "\n",
        "The **INNER JOIN** is a process that matches rows from the first table and the second table which have the same key (as defined by the **ON** constraint) to create a result row with the combined columns from both tables.\n",
        "\n",
        "```\n",
        "SELECT column, another_table_column, …\n",
        "FROM mytable\n",
        "INNER JOIN another_table \n",
        "    ON mytable.id = another_table.id\n",
        "WHERE condition(s)\n",
        "ORDER BY column, … ASC/DESC\n",
        "LIMIT num_limit OFFSET num_offset;\n",
        "```\n",
        "\n",
        "**Example:**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "m8PhTMBoirIB",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 198
        },
        "outputId": "1a756cf1-c297-43d4-c045-2fd6ba856063"
      },
      "source": [
        "# First five rows in table albums\n",
        "pd.read_sql_query('SELECT * FROM albums LIMIT 5;', conn)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 57
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_etMVaOEi37T",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 198
        },
        "outputId": "72973ef4-721b-4b85-c198-ab572e90f455"
      },
      "source": [
        "# First five row in table artists\n",
        "pd.read_sql_query('SELECT * FROM artists LIMIT 5;', conn)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 12
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "QeH5I9ApjA82",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# Find the albums of Aerosmith\n",
        "pd.read_sql_query('''SELECT * FROM \n",
        "                     albums INNER JOIN artists ON albums.ArtistId = artists.ArtistId \n",
        "                     WHERE artists.name=\"Aerosmith\";''', conn)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "HJhMbP4FlAbe",
        "colab_type": "text"
      },
      "source": [
        "**Your turn**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "1ZtteUICuoCZ",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 419
        },
        "outputId": "08f63c7f-e660-472c-8fdd-7f8a2b3b1f09"
      },
      "source": [
        "# List first 10 tracks and its album's title order by track's name\n",
        "pd.read_sql_query('''SELECT tracks.Name, albums.Title\n",
        "FROM tracks INNER JOIN albums ON tracks.AlbumId = albums.AlbumId\n",
        "ORDER BY tracks.Name ''', conn)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>Name</th>\n",
              "      <th>Title</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>\"40\"</td>\n",
              "      <td>War</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>\"?\"</td>\n",
              "      <td>Lost, Season 2</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>\"Eine Kleine Nachtmusik\" Serenade In G, K. 525...</td>\n",
              "      <td>Sir Neville Marriner: A Celebration</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>#1 Zero</td>\n",
              "      <td>Out Of Exile</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>#9 Dream</td>\n",
              "      <td>Instant Karma: The Amnesty International Campa...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3498</th>\n",
              "      <td>É que Nessa Encarnação Eu Nasci Manga</td>\n",
              "      <td>Axé Bahia 2001</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3499</th>\n",
              "      <td>Étude 1, In C Major - Preludio (Presto) - Liszt</td>\n",
              "      <td>Liszt - 12 Études D'Execution Transcendante</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3500</th>\n",
              "      <td>Óculos</td>\n",
              "      <td>Arquivo Os Paralamas Do Sucesso</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3501</th>\n",
              "      <td>Óia Eu Aqui De Novo</td>\n",
              "      <td>As Canções de Eu Tu Eles</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3502</th>\n",
              "      <td>Último Pau-De-Arara</td>\n",
              "      <td>As Canções de Eu Tu Eles</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>3503 rows × 2 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "                                                   Name                                              Title\n",
              "0                                                  \"40\"                                                War\n",
              "1                                                   \"?\"                                     Lost, Season 2\n",
              "2     \"Eine Kleine Nachtmusik\" Serenade In G, K. 525...                Sir Neville Marriner: A Celebration\n",
              "3                                               #1 Zero                                       Out Of Exile\n",
              "4                                              #9 Dream  Instant Karma: The Amnesty International Campa...\n",
              "...                                                 ...                                                ...\n",
              "3498              É que Nessa Encarnação Eu Nasci Manga                                     Axé Bahia 2001\n",
              "3499    Étude 1, In C Major - Preludio (Presto) - Liszt        Liszt - 12 Études D'Execution Transcendante\n",
              "3500                                             Óculos                    Arquivo Os Paralamas Do Sucesso\n",
              "3501                                Óia Eu Aqui De Novo                           As Canções de Eu Tu Eles\n",
              "3502                                Último Pau-De-Arara                           As Canções de Eu Tu Eles\n",
              "\n",
              "[3503 rows x 2 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 71
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "jfV3sLSCvun6",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 297
        },
        "outputId": "72edf579-1ee4-4e31-e799-04b6a6731256"
      },
      "source": [
        "# List all employees and who they report to\n",
        "pd.read_sql_query('''SELECT employees.FirstName, employees.Title FROM employees ''', conn)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>Title</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Andrew</td>\n",
              "      <td>General Manager</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Nancy</td>\n",
              "      <td>Sales Manager</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Jane</td>\n",
              "      <td>Sales Support Agent</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Margaret</td>\n",
              "      <td>Sales Support Agent</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Steve</td>\n",
              "      <td>Sales Support Agent</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>Michael</td>\n",
              "      <td>IT Manager</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>Robert</td>\n",
              "      <td>IT Staff</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>Laura</td>\n",
              "      <td>IT Staff</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "  FirstName                Title\n",
              "0    Andrew      General Manager\n",
              "1     Nancy        Sales Manager\n",
              "2      Jane  Sales Support Agent\n",
              "3  Margaret  Sales Support Agent\n",
              "4     Steve  Sales Support Agent\n",
              "5   Michael           IT Manager\n",
              "6    Robert             IT Staff\n",
              "7     Laura             IT Staff"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 77
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "tL_UWgYYj55T",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        },
        "outputId": "4a6a6472-8fc3-4f7e-da9d-eaf7a3b59283"
      },
      "source": [
        "# List the first five customers order by their supporter's first name in descending order\n",
        "pd.read_sql_query('''SELECT customers.FirstName \n",
        "FROM customers INNER JOIN employees ON customers.SupportRepId = employees.EmployeeId\n",
        "ORDER BY employees.FirstName \n",
        "LIMIT 5 ''', conn)\n"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Luís</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>François</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Roberto</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Jennifer</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Michelle</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "  FirstName\n",
              "0      Luís\n",
              "1  François\n",
              "2   Roberto\n",
              "3  Jennifer\n",
              "4  Michelle"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 79
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6MAT_K1DzWlZ",
        "colab_type": "text"
      },
      "source": [
        "### OUTER JOINS\n",
        "\n",
        "Depending on how you want to analyze the data, the INNER JOIN we used last lesson might not be sufficient because the resulting table only contains data that belongs in both of the tables.\n",
        "\n",
        "If the two tables have asymmetric data, which can easily happen when data is entered in different stages, then we would have to use a LEFT JOIN, RIGHT JOIN or FULL JOIN instead to ensure that the data you need is not left out of the results.\n",
        "\n",
        "```\n",
        "SELECT column, another_column, …\n",
        "FROM mytable\n",
        "INNER/LEFT/RIGHT/FULL JOIN another_table \n",
        "    ON mytable.id = another_table.matching_id\n",
        "WHERE condition(s)\n",
        "ORDER BY column, … ASC/DESC\n",
        "LIMIT num_limit OFFSET num_offset;\n",
        "```\n",
        "\n",
        "Sometimes, it's not possible to avoid **NULL** values. In these case, you can test a column for **NULL** values in a WHERE clause by using either the **IS NULL** or **IS NOT NULL** constraint.\n",
        "\n",
        "```\n",
        "SELECT column, another_column, …\n",
        "FROM mytable\n",
        "WHERE column IS/IS NOT NULL\n",
        "AND/OR another_condition\n",
        "AND/OR …;\n",
        "```"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "I4dCP1Bk31ch",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 419
        },
        "outputId": "5a24b753-2852-432c-ec2d-919a4666a7e7"
      },
      "source": [
        "# Find all tracks that couldn't be sold (null invoice_items)\n",
        "pd.read_sql_query('''SELECT tracks.Name FROM tracks LEFT JOIN invoice_items ON tracks.TrackId = invoice_items.TrackId WHERE invoice_items.TrackId is NULL''', conn)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>Name</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Let's Get It Up</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>C.O.D.</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Let There Be Rock</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Bad Boy Boogie</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Whole Lotta Rosie</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1514</th>\n",
              "      <td>Erlkonig, D.328</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1515</th>\n",
              "      <td>Concerto for Violin, Strings and Continuo in G...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1516</th>\n",
              "      <td>L'orfeo, Act 3, Sinfonia (Orchestra)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1517</th>\n",
              "      <td>Quintet for Horn, Violin, 2 Violas, and Cello ...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1518</th>\n",
              "      <td>Koyaanisqatsi</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>1519 rows × 1 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "                                                   Name\n",
              "0                                       Let's Get It Up\n",
              "1                                                C.O.D.\n",
              "2                                     Let There Be Rock\n",
              "3                                        Bad Boy Boogie\n",
              "4                                     Whole Lotta Rosie\n",
              "...                                                 ...\n",
              "1514                                    Erlkonig, D.328\n",
              "1515  Concerto for Violin, Strings and Continuo in G...\n",
              "1516               L'orfeo, Act 3, Sinfonia (Orchestra)\n",
              "1517  Quintet for Horn, Violin, 2 Violas, and Cello ...\n",
              "1518                                      Koyaanisqatsi\n",
              "\n",
              "[1519 rows x 1 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 81
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "4nNx4WjGuC8A",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        },
        "outputId": "2fec018e-c0c9-408b-df2f-65ad99ee0b02"
      },
      "source": [
        "# List all genres and the distinct media types in each genre\n",
        "pd.read_sql_query('''SELECT DISTINCT media_types.Name FROM media_types INNER JOIN tracks ON media_types.MediaTypeId = tracks.MediaTypeId ''', conn)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>Name</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>MPEG audio file</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Protected AAC audio file</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Protected MPEG-4 video file</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Purchased AAC audio file</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>AAC audio file</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                          Name\n",
              "0              MPEG audio file\n",
              "1     Protected AAC audio file\n",
              "2  Protected MPEG-4 video file\n",
              "3     Purchased AAC audio file\n",
              "4               AAC audio file"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 102
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "xVhmj5x_6xnk",
        "colab_type": "text"
      },
      "source": [
        "### Queries with aggregates\n",
        "\n",
        "SQL also supports the use of aggregate expressions (or functions) that allow you to summarize information about a group of rows of data.\n",
        "\n",
        "Common aggregate functions:\n",
        "\n",
        "| Functions | Description |\n",
        "|:-:|:-:|\n",
        "|**COUNT**(*), **COUNT**(column) | A common function used to counts the number of rows in the group if no column name is specified. Otherwise, count the number of rows in the group with non-NULL values in the specified column.|\n",
        "|**MIN**(column) | Finds the smallest numerical value in the specified column for all rows in the group.|\n",
        "|**MAX**(column) | Finds the largest numerical value in the specified column for all rows in the group.|\n",
        "|**AVG**(column) | Finds the average numerical value in the specified column for all rows in the group.|\n",
        "|**SUM**(column) | Finds the sum of all numerical values in the specified column for the rows in the group.|\n",
        "\n",
        "More docs: [SQLite](http://www.sqlite.org/lang_aggfunc.html), [MySQL](https://dev.mysql.com/doc/refman/5.6/en/group-by-functions.html), [Postgres](http://www.postgresql.org/docs/9.4/static/functions-aggregate.html)\n",
        "\n",
        "In addition to aggregating across all the rows, you can instead apply the aggregate functions to individual groups of data within that group. This would then create as many results as there are unique groups defined as by the  **GROUP BY** clause.\n",
        "\n",
        "```\n",
        "SELECT AGG_FUNC(column_or_expression) AS aggregate_description, …\n",
        "FROM mytable\n",
        "WHERE constraint_expression\n",
        "GROUP BY column;\n",
        "```\n",
        "\n",
        "Our queries are getting fairly complex, but we have nearly introduced all the important parts of a  **SELECT** query. One thing that you might have noticed is that if the **GROUP BY** clause is executed after the **WHERE** clause (which filters the rows which are to be grouped), then how exactly do we filter the grouped rows?\n",
        "\n",
        "Luckily, SQL allows us to do this by adding an additional **HAVING** clause which is used specifically with the **GROUP BY** clause to allow us to filter grouped rows from the result set.\n",
        "\n",
        "```\n",
        "SELECT group_by_column, AGG_FUNC(column_expression) AS aggregate_result_alias, …\n",
        "FROM mytable\n",
        "WHERE condition\n",
        "GROUP BY column\n",
        "HAVING group_condition;\n",
        "```\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "z3QK3EepBI3H",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 80
        },
        "outputId": "97d42534-1fb7-4226-d618-e6a349a6e256"
      },
      "source": [
        "# Find the revenue of 2013\n",
        "pd.read_sql_query('''SELECT sum(Total) FROM invoices WHERE InvoiceDate LIKE \"2013%\" ''', conn)"
      ],
      "execution_count": 16,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>sum(Total)</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>450.58</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   sum(Total)\n",
              "0      450.58"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 16
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "XnlVKXmt1lrb",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 80
        },
        "outputId": "327f8623-993f-4e38-acaa-bbd4b9c1fe6d"
      },
      "source": [
        "# Find the number of tracks belong to the genre Rock\n",
        "pd.read_sql_query('SELECT count(*) FROM tracks INNER JOIN genres ON tracks.GenreId = genres.GenreId WHERE genres.Name = \"Rock\"', conn)"
      ],
      "execution_count": 19,
      "outputs": [
        {
          "output_type": "execute_result",
          "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",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1297</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   count(*)\n",
              "0      1297"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 19
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "3XOtPFH8DorX",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 142
        },
        "outputId": "bcbc450f-31c7-4871-8d2a-f80d08e217db"
      },
      "source": [
        "# Find the number of customers each employee has supported\n",
        "pd.read_sql_query('SELECT SupportRepId, count(CustomerId) FROM customers GROUP BY SupportRepId' , conn)"
      ],
      "execution_count": 26,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>SupportRepId</th>\n",
              "      <th>count(CustomerId)</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>3</td>\n",
              "      <td>21</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>4</td>\n",
              "      <td>20</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>5</td>\n",
              "      <td>18</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   SupportRepId  count(CustomerId)\n",
              "0             3                 21\n",
              "1             4                 20\n",
              "2             5                 18"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 26
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "DtMQ5OWpGXN8",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 134
        },
        "outputId": "ce96ffc0-7e06-4cb6-b904-159ba52d2fb3"
      },
      "source": [
        "# Find the customer who paid the most\n",
        "pd.read_sql_query(''' SELECT * FROM customers INNER JOIN invoices ON customers.CustomerId = invoices.CustomerId WHERE invoices.Total >= (SELECT MAX(Total) FROM invoices) ''', conn)"
      ],
      "execution_count": 30,
      "outputs": [
        {
          "output_type": "execute_result",
          "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",
              "      <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>6</td>\n",
              "      <td>Helena</td>\n",
              "      <td>Holý</td>\n",
              "      <td>None</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>+420 2 4177 0449</td>\n",
              "      <td>None</td>\n",
              "      <td>hholy@gmail.com</td>\n",
              "      <td>5</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",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   CustomerId FirstName LastName  ...  BillingCountry BillingPostalCode  Total\n",
              "0           6    Helena     Holý  ...  Czech Republic             14300  25.86\n",
              "\n",
              "[1 rows x 22 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 30
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "qOFdo1XdQyCq",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 235
        },
        "outputId": "c952e212-b4d9-4167-d15a-c6003fa982c8"
      },
      "source": [
        "# Find the richest artist\n",
        "pd.read_sql_query('''SELECT DISTINCT(artists.Name)\n",
        "FROM artists INNER JOIN albums ON artists.ArtistId = albums.ArtistId\n",
        "INNER JOIN tracks ON albums.AlbumId = tracks.AlbumId\n",
        "INNER JOIN invoice_items ON tracks.TrackId = invoice_items.TrackId\n",
        "WHERE invoice_items.UnitPrice * invoice_items.Quantity >= (\n",
        "  SELECT MAX(invoice_items.UnitPrice * invoice_items.Quantity)\n",
        "  FROM invoice_items)\n",
        " ''', conn)"
      ],
      "execution_count": 44,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>Name</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Battlestar Galactica</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Heroes</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Lost</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>The Office</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Battlestar Galactica (Classic)</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>Aquaman</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                             Name\n",
              "0            Battlestar Galactica\n",
              "1                          Heroes\n",
              "2                            Lost\n",
              "3                      The Office\n",
              "4  Battlestar Galactica (Classic)\n",
              "5                         Aquaman"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 44
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "0bNRYxjEAIYk",
        "colab_type": "text"
      },
      "source": [
        "### Query order of execution\n",
        "\n",
        "Now that we have an idea of all the parts of a query, we can now talk about how they all fit together in the context of a complete query.\n",
        "\n",
        "1. **FROM** and **JOIN**\n",
        "2. **WHERE**\n",
        "3. **GROUP BY**\n",
        "4. **HAVING**\n",
        "5. **SELECT**\n",
        "6. **DISTINCT**\n",
        "7. **ORDER BY**\n",
        "8. **LIMIT** / **OFFSET**\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "9ihN0pi4A8cf",
        "colab_type": "text"
      },
      "source": [
        "### Conclusion\n",
        "\n",
        "Not every query needs to have all the parts we listed above, but a part of why SQL is so flexible is that it allows developers and data analysts to quickly manipulate data without having to write additional code, all just by using the above clauses.\n",
        "\n",
        "Here ends our lessons on **SELECT** queries, congrats of making it this far! "
      ]
    }
  ]
}