SQL Querying in SQLite.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SQL Querying in SQLite\n",
    "\n",
    "> The purpose of this Jupyter notebook is setting up a connection to your database file (.db) and querying it using SQL. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pip install csv-to-sqlite"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv_to_sqlite"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Error on table c: \n",
      " [Errno 2] No such file or directory: 'c'\n",
      "Error on table i: \n",
      " [Errno 2] No such file or directory: 'i'\n",
      "Error on table t: \n",
      " [Errno 2] No such file or directory: 't'\n",
      "Error on table y: \n",
      " [Errno 2] No such file or directory: 'y'\n",
      "Error on table .: \n",
      " [Errno 21] Is a directory: '.'\n",
      "Error on table c: \n",
      " [Errno 2] No such file or directory: 'c'\n",
      "Error on table s: \n",
      " [Errno 2] No such file or directory: 's'\n",
      "Error on table v: \n",
      " [Errno 2] No such file or directory: 'v'\n",
      "Written 0 rows into 8 tables in 0.002 seconds\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "options=csv_to_sqlite.CsvOptions()\n",
    "csv_to_sqlite.write_csv(\"city.csv\", \"output.sqlite\", options)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "ename": "DatabaseError",
     "evalue": "Execution failed on sql 'SELECT * FROM city': no such table: city",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mOperationalError\u001b[0m                          Traceback (most recent call last)",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.7/site-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, *args, **kwargs)\u001b[0m\n\u001b[1;32m   1594\u001b[0m             \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1595\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[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1596\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: city",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[0;31mDatabaseError\u001b[0m                             Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-25-1ccb3af1ce6a>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0msqlite3\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      2\u001b[0m \u001b[0mcon\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0msqlite3\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mconnect\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'output.sqlite'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 3\u001b[0;31m \u001b[0mfilter_data\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mpd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread_sql_query\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"\"\"SELECT * FROM city\"\"\"\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0mcon\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~/opt/anaconda3/lib/python3.7/site-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~/opt/anaconda3/lib/python3.7/site-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   1643\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1644\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-> 1645\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   1646\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   1647\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.7/site-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, *args, **kwargs)\u001b[0m\n\u001b[1;32m   1608\u001b[0m                 \u001b[0;34m\"Execution failed on sql '{sql}': {exc}\"\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mformat\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msql\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mexc\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1609\u001b[0m             )\n\u001b[0;32m-> 1610\u001b[0;31m             \u001b[0mraise_with_traceback\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mex\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   1611\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1612\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;32m~/opt/anaconda3/lib/python3.7/site-packages/pandas/compat/__init__.py\u001b[0m in \u001b[0;36mraise_with_traceback\u001b[0;34m(exc, traceback)\u001b[0m\n\u001b[1;32m     45\u001b[0m     \u001b[0;32mif\u001b[0m \u001b[0mtraceback\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0mEllipsis\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     46\u001b[0m         \u001b[0m_\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0m_\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtraceback\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0msys\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexc_info\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 47\u001b[0;31m     \u001b[0;32mraise\u001b[0m \u001b[0mexc\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mwith_traceback\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtraceback\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     48\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     49\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.7/site-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, *args, **kwargs)\u001b[0m\n\u001b[1;32m   1593\u001b[0m                 \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[1;32m   1594\u001b[0m             \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1595\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[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1596\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[1;32m   1597\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mException\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0mexc\u001b[0m\u001b[0;34m:\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 * FROM city': no such table: city"
     ]
    }
   ],
   "source": [
    "import sqlite3\n",
    "con=sqlite3.connect('output.sqlite')\n",
    "filter_data=pd.read_sql_query(\"\"\"SELECT * FROM city\"\"\",con)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "UsageError: Line magic function `%` not found.\n"
     ]
    }
   ],
   "source": [
    "% sqlite> .schema cities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "UsageError: Line magic function `%sql` not found.\n"
     ]
    }
   ],
   "source": [
    "%sql select * from cities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'/Users/chenguo/Desktop/IBM-ML Python/IBM_Data Engineering/connect to local db'"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import os\n",
    "os.getcwd()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql sqlite:///test.db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Environment variable $DATABASE_URL not set, and no connect string given.\n",
      "Connection info needed in SQLAlchemy format, example:\n",
      "               postgresql://username:password@hostname/dbname\n",
      "               or an existing connection: dict_keys([])\n"
     ]
    }
   ],
   "source": [
    "%%sql\n",
    "sqlite3 test.db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "ename": "SyntaxError",
     "evalue": "invalid syntax (<ipython-input-40-60d752146166>, line 2)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;36m  File \u001b[0;32m\"<ipython-input-40-60d752146166>\"\u001b[0;36m, line \u001b[0;32m2\u001b[0m\n\u001b[0;31m    create table tbl1(one text, two int);\u001b[0m\n\u001b[0m               ^\u001b[0m\n\u001b[0;31mSyntaxError\u001b[0m\u001b[0;31m:\u001b[0m invalid syntax\n"
     ]
    }
   ],
   "source": [
    "%sql\n",
    "create table tbl1(one text, two int);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Import Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3 as sql\n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Make the Connection to your data set"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a variable to the name of your data set, which should be in the same folder\n",
    "# as this notebook\n",
    "\n",
    "database = 'cities.db'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Make the connection. \n",
    "\n",
    "connection = sql.connect(database)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create Your Query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql_query = '''SELECT * FROM city'''"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create a data frame that takes 2 inputs, your database connection and the query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_sql_query(sql_query, connection)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Let's see if this works."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Abilene</td>\n",
       "      <td>115930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Akron</td>\n",
       "      <td>217074</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Albany</td>\n",
       "      <td>93994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Albuquerque</td>\n",
       "      <td>448607</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Alexandria</td>\n",
       "      <td>128283</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Allentown</td>\n",
       "      <td>106632</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Amarillo</td>\n",
       "      <td>173627</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Anaheim</td>\n",
       "      <td>328014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Anchorage</td>\n",
       "      <td>260283</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Ann Arbor</td>\n",
       "      <td>114024</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          name  population\n",
       "0      Abilene      115930\n",
       "1        Akron      217074\n",
       "2       Albany       93994\n",
       "3  Albuquerque      448607\n",
       "4   Alexandria      128283\n",
       "5    Allentown      106632\n",
       "6     Amarillo      173627\n",
       "7      Anaheim      328014\n",
       "8    Anchorage      260283\n",
       "9    Ann Arbor      114024"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Use the following template for querying"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "sql_query = '''\n",
    "SELECT \n",
    "FROM \n",
    "'''\n",
    "\n",
    "pd.read_sql_query(sql_query, connection)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Find the top 10 cities with the greatest population"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>New York</td>\n",
       "      <td>8008278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>3694820</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Chicago</td>\n",
       "      <td>2896016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Houston</td>\n",
       "      <td>1953631</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Philadelphia</td>\n",
       "      <td>1517550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Phoenix</td>\n",
       "      <td>1321045</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>San Diego</td>\n",
       "      <td>1223400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Dallas</td>\n",
       "      <td>1188580</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>San Antonio</td>\n",
       "      <td>1144646</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Detroit</td>\n",
       "      <td>951270</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           name  population\n",
       "0      New York     8008278\n",
       "1   Los Angeles     3694820\n",
       "2       Chicago     2896016\n",
       "3       Houston     1953631\n",
       "4  Philadelphia     1517550\n",
       "5       Phoenix     1321045\n",
       "6     San Diego     1223400\n",
       "7        Dallas     1188580\n",
       "8   San Antonio     1144646\n",
       "9       Detroit      951270"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = '''\n",
    "SELECT name, population\n",
    "FROM city\n",
    "ORDER BY population DESC\n",
    "LIMIT 10;\n",
    "'''\n",
    "\n",
    "df2 = pd.read_sql_query(sql_query, connection)\n",
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Export your Query to Excel or CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df2.to_csv('top10_cities.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting ipython-sql\n",
      "  Downloading ipython_sql-0.4.0-py3-none-any.whl (19 kB)\n",
      "Requirement already satisfied: ipython>=1.0 in /Users/chenguo/opt/anaconda3/lib/python3.7/site-packages (from ipython-sql) (7.16.1)\n",
      "Requirement already satisfied: six in /Users/chenguo/opt/anaconda3/lib/python3.7/site-packages (from ipython-sql) (1.15.0)\n",
      "Requirement already satisfied: ipython-genutils>=0.1.0 in /Users/chenguo/opt/anaconda3/lib/python3.7/site-packages (from ipython-sql) (0.2.0)\n",
      "Collecting sqlparse\n",
      "  Downloading sqlparse-0.4.2-py3-none-any.whl (42 kB)\n",
      "     |████████████████████████████████| 42 kB 332 kB/s            \n",
      "\u001b[?25hRequirement already satisfied: sqlalchemy>=0.6.7 in /Users/chenguo/opt/anaconda3/lib/python3.7/site-packages (from ipython-sql) (1.3.9)\n",
      "Collecting prettytable<1\n",
      "  Downloading prettytable-0.7.2.zip (28 kB)\n",
      "  Preparing metadata (setup.py) ... \u001b[?25ldone\n",
      "\u001b[?25hRequirement already satisfied: pygments in /Users/chenguo/opt/anaconda3/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (2.5.2)\n",
      "Requirement already satisfied: backcall in /Users/chenguo/opt/anaconda3/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (0.1.0)\n",
      "Requirement already satisfied: appnope in /Users/chenguo/opt/anaconda3/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (0.1.0)\n",
      "Requirement already satisfied: traitlets>=4.2 in /Users/chenguo/opt/anaconda3/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (4.3.3)\n",
      "Requirement already satisfied: setuptools>=18.5 in /Users/chenguo/opt/anaconda3/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (60.1.0)\n",
      "Requirement already satisfied: jedi>=0.10 in /Users/chenguo/opt/anaconda3/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (0.14.1)\n",
      "Requirement already satisfied: pickleshare in /Users/chenguo/opt/anaconda3/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (0.7.5)\n",
      "Requirement already satisfied: prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0 in /Users/chenguo/opt/anaconda3/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (3.0.3)\n",
      "Requirement already satisfied: pexpect in /Users/chenguo/opt/anaconda3/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (4.8.0)\n",
      "Requirement already satisfied: decorator in /Users/chenguo/opt/anaconda3/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (4.4.1)\n",
      "Requirement already satisfied: parso>=0.5.0 in /Users/chenguo/opt/anaconda3/lib/python3.7/site-packages (from jedi>=0.10->ipython>=1.0->ipython-sql) (0.5.2)\n",
      "Requirement already satisfied: wcwidth in /Users/chenguo/opt/anaconda3/lib/python3.7/site-packages (from prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0->ipython>=1.0->ipython-sql) (0.1.8)\n",
      "Requirement already satisfied: ptyprocess>=0.5 in /Users/chenguo/opt/anaconda3/lib/python3.7/site-packages (from pexpect->ipython>=1.0->ipython-sql) (0.6.0)\n",
      "Building wheels for collected packages: prettytable\n",
      "  Building wheel for prettytable (setup.py) ... \u001b[?25ldone\n",
      "\u001b[?25h  Created wheel for prettytable: filename=prettytable-0.7.2-py3-none-any.whl size=13714 sha256=71276e1f2797d29a325d4c73a9df96e054ed2e4d85f0a818916dabea3828a238\n",
      "  Stored in directory: /Users/chenguo/Library/Caches/pip/wheels/b2/7f/f6/f180315b584f00445045ff1699b550fa895d09471337ce21c6\n",
      "Successfully built prettytable\n",
      "Installing collected packages: sqlparse, prettytable, ipython-sql\n",
      "Successfully installed ipython-sql-0.4.0 prettytable-0.7.2 sqlparse-0.4.2\n",
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    }
   ],
   "source": [
    "pip install ipython-sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "%load_ext sql\n",
    "%sql sqlite:////Users/chenguo/Desktop/IBM-ML Python/Interview questions/Alooba/cities.db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'/Users/chenguo/Desktop/IBM-ML Python/Interview questions/Alooba'"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import os\n",
    "os.getcwd()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:////Users/chenguo/Desktop/IBM-ML\n",
      "(sqlite3.OperationalError) no such table: cities\n",
      "[SQL: select * from cities]\n",
      "(Background on this error at: http://sqlalche.me/e/e3q8)\n"
     ]
    }
   ],
   "source": [
    "%sql select * from cities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:////Users/chenguo/Desktop/IBM-ML\n",
      "(sqlite3.OperationalError) near \".\": syntax error\n",
      "[SQL: .tables]\n",
      "(Background on this error at: http://sqlalche.me/e/e3q8)\n"
     ]
    }
   ],
   "source": [
    "%sql .tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "%load_ext sql\n",
    "%sql sqlite:///cities.db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   sqlite:////Users/chenguo/Desktop/IBM-ML\n",
      " * sqlite:///cities.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>city</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('city',)]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT\n",
    "    name\n",
    "FROM\n",
    "    sqlite_master\n",
    "WHERE\n",
    "    type='table'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   sqlite:////Users/chenguo/Desktop/IBM-ML\n",
      " * sqlite:///cities.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>population</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Abilene</td>\n",
       "        <td>115930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Akron</td>\n",
       "        <td>217074</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Albany</td>\n",
       "        <td>93994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Albuquerque</td>\n",
       "        <td>448607</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Alexandria</td>\n",
       "        <td>128283</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Allentown</td>\n",
       "        <td>106632</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Amarillo</td>\n",
       "        <td>173627</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Anaheim</td>\n",
       "        <td>328014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Anchorage</td>\n",
       "        <td>260283</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ann Arbor</td>\n",
       "        <td>114024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Arden-Arcade</td>\n",
       "        <td>92040</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Arlington</td>\n",
       "        <td>332969</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Arlington</td>\n",
       "        <td>174838</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Arvada</td>\n",
       "        <td>102153</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Athens-Clarke County</td>\n",
       "        <td>101489</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Atlanta</td>\n",
       "        <td>416474</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Augusta-Richmond County</td>\n",
       "        <td>199775</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Aurora</td>\n",
       "        <td>276393</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Aurora</td>\n",
       "        <td>142990</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Austin</td>\n",
       "        <td>656562</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bakersfield</td>\n",
       "        <td>247057</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Baltimore</td>\n",
       "        <td>651154</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Baton Rouge</td>\n",
       "        <td>227818</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Beaumont</td>\n",
       "        <td>113866</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bellevue</td>\n",
       "        <td>109569</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Berkeley</td>\n",
       "        <td>102743</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Billings</td>\n",
       "        <td>92988</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Birmingham</td>\n",
       "        <td>242820</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Boise City</td>\n",
       "        <td>185787</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Boston</td>\n",
       "        <td>589141</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Boulder</td>\n",
       "        <td>91238</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bridgeport</td>\n",
       "        <td>139529</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brockton</td>\n",
       "        <td>93653</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brownsville</td>\n",
       "        <td>139722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Buffalo</td>\n",
       "        <td>292648</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Burbank</td>\n",
       "        <td>100316</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Cambridge</td>\n",
       "        <td>101355</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Cape Coral</td>\n",
       "        <td>102286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Carrollton</td>\n",
       "        <td>109576</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Carson</td>\n",
       "        <td>89089</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Cary</td>\n",
       "        <td>91213</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Cedar Rapids</td>\n",
       "        <td>120758</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Chandler</td>\n",
       "        <td>176581</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Charleston</td>\n",
       "        <td>89063</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Charlotte</td>\n",
       "        <td>540828</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Chattanooga</td>\n",
       "        <td>155554</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Chesapeake</td>\n",
       "        <td>199184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Chicago</td>\n",
       "        <td>2896016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Chula Vista</td>\n",
       "        <td>173556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Cincinnati</td>\n",
       "        <td>331285</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Citrus Heights</td>\n",
       "        <td>103455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Clarksville</td>\n",
       "        <td>108787</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Clearwater</td>\n",
       "        <td>99936</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Cleveland</td>\n",
       "        <td>478403</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Colorado Springs</td>\n",
       "        <td>360890</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Columbia</td>\n",
       "        <td>116278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Columbus</td>\n",
       "        <td>711470</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Columbus</td>\n",
       "        <td>186291</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Compton</td>\n",
       "        <td>92864</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Concord</td>\n",
       "        <td>121780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Coral Springs</td>\n",
       "        <td>117549</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Corona</td>\n",
       "        <td>124966</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Corpus Christi</td>\n",
       "        <td>277454</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Costa Mesa</td>\n",
       "        <td>108724</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Dallas</td>\n",
       "        <td>1188580</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Daly City</td>\n",
       "        <td>103621</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Davenport</td>\n",
       "        <td>98256</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Dayton</td>\n",
       "        <td>166179</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Denver</td>\n",
       "        <td>554636</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Des Moines</td>\n",
       "        <td>198682</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Detroit</td>\n",
       "        <td>951270</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Downey</td>\n",
       "        <td>107323</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Durham</td>\n",
       "        <td>187035</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>East Los Angeles</td>\n",
       "        <td>126379</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>El Cajon</td>\n",
       "        <td>94578</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>El Monte</td>\n",
       "        <td>115965</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>El Paso</td>\n",
       "        <td>563662</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Elgin</td>\n",
       "        <td>89408</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Elizabeth</td>\n",
       "        <td>120568</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Erie</td>\n",
       "        <td>103717</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Escondido</td>\n",
       "        <td>133559</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Eugene</td>\n",
       "        <td>137893</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Evansville</td>\n",
       "        <td>121582</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Fairfield</td>\n",
       "        <td>92256</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Fall River</td>\n",
       "        <td>90555</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Fayetteville</td>\n",
       "        <td>121015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Flint</td>\n",
       "        <td>124943</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Fontana</td>\n",
       "        <td>128929</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Fort Collins</td>\n",
       "        <td>118652</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Fort Lauderdale</td>\n",
       "        <td>152397</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Fort Wayne</td>\n",
       "        <td>205727</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Fort Worth</td>\n",
       "        <td>534694</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Fremont</td>\n",
       "        <td>203413</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Fresno</td>\n",
       "        <td>427652</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Fullerton</td>\n",
       "        <td>126003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Gainesville</td>\n",
       "        <td>92291</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Garden Grove</td>\n",
       "        <td>165196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Garland</td>\n",
       "        <td>215768</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Gary</td>\n",
       "        <td>102746</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Gilbert</td>\n",
       "        <td>109697</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Glendale</td>\n",
       "        <td>218812</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Glendale</td>\n",
       "        <td>194973</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Grand Prairie</td>\n",
       "        <td>127427</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Grand Rapids</td>\n",
       "        <td>197800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Green Bay</td>\n",
       "        <td>102313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Greensboro</td>\n",
       "        <td>223891</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Hampton</td>\n",
       "        <td>146437</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Hartford</td>\n",
       "        <td>121578</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Hayward</td>\n",
       "        <td>140030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Henderson</td>\n",
       "        <td>175381</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Hialeah</td>\n",
       "        <td>226419</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Hollywood</td>\n",
       "        <td>139357</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Honolulu</td>\n",
       "        <td>371657</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Houston</td>\n",
       "        <td>1953631</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Huntington Beach</td>\n",
       "        <td>189594</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Huntsville</td>\n",
       "        <td>158216</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Independence</td>\n",
       "        <td>113288</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Indianapolis</td>\n",
       "        <td>791926</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Inglewood</td>\n",
       "        <td>112580</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Irvine</td>\n",
       "        <td>143072</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Irving</td>\n",
       "        <td>191615</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jackson</td>\n",
       "        <td>184256</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jacksonville</td>\n",
       "        <td>735167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jersey City</td>\n",
       "        <td>240055</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Joliet</td>\n",
       "        <td>106221</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Kansas City</td>\n",
       "        <td>441545</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Kansas City</td>\n",
       "        <td>146866</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Kenosha</td>\n",
       "        <td>89447</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Knoxville</td>\n",
       "        <td>173890</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Lafayette</td>\n",
       "        <td>110257</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Lakewood</td>\n",
       "        <td>144126</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Lancaster</td>\n",
       "        <td>118718</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Lansing</td>\n",
       "        <td>119128</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Laredo</td>\n",
       "        <td>176576</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Las Vegas</td>\n",
       "        <td>478434</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Lexington-Fayette</td>\n",
       "        <td>260512</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Lincoln</td>\n",
       "        <td>225581</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Little Rock</td>\n",
       "        <td>183133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Livonia</td>\n",
       "        <td>100545</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Long Beach</td>\n",
       "        <td>461522</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Los Angeles</td>\n",
       "        <td>3694820</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Louisville</td>\n",
       "        <td>256231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Lowell</td>\n",
       "        <td>105167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Lubbock</td>\n",
       "        <td>199564</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Macon</td>\n",
       "        <td>113336</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Madison</td>\n",
       "        <td>208054</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Manchester</td>\n",
       "        <td>107006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>McAllen</td>\n",
       "        <td>106414</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Memphis</td>\n",
       "        <td>650100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mesa</td>\n",
       "        <td>396375</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mesquite</td>\n",
       "        <td>124523</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Metairie</td>\n",
       "        <td>149428</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Miami</td>\n",
       "        <td>362470</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Miami Beach</td>\n",
       "        <td>97855</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Midland</td>\n",
       "        <td>98293</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Milwaukee</td>\n",
       "        <td>596974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Minneapolis</td>\n",
       "        <td>382618</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mission Viejo</td>\n",
       "        <td>98049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mobile</td>\n",
       "        <td>198915</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Modesto</td>\n",
       "        <td>188856</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Montgomery</td>\n",
       "        <td>201568</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Moreno Valley</td>\n",
       "        <td>142381</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Naperville</td>\n",
       "        <td>128358</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nashville-Davidson</td>\n",
       "        <td>569891</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>New Bedford</td>\n",
       "        <td>94780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>New Haven</td>\n",
       "        <td>123626</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>New Orleans</td>\n",
       "        <td>484674</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>New York</td>\n",
       "        <td>8008278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Newark</td>\n",
       "        <td>273546</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Newport News</td>\n",
       "        <td>180150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Norfolk</td>\n",
       "        <td>234403</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Norman</td>\n",
       "        <td>94193</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>North Las Vegas</td>\n",
       "        <td>115488</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Norwalk</td>\n",
       "        <td>103298</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Oakland</td>\n",
       "        <td>399484</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Oceanside</td>\n",
       "        <td>161029</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Odessa</td>\n",
       "        <td>89293</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Oklahoma City</td>\n",
       "        <td>506132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Omaha</td>\n",
       "        <td>390007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ontario</td>\n",
       "        <td>158007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Orange</td>\n",
       "        <td>128821</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Orlando</td>\n",
       "        <td>185951</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Overland Park</td>\n",
       "        <td>149080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Oxnard</td>\n",
       "        <td>170358</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Palmdale</td>\n",
       "        <td>116670</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Paradise</td>\n",
       "        <td>124682</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Pasadena</td>\n",
       "        <td>141674</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Pasadena</td>\n",
       "        <td>133936</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Paterson</td>\n",
       "        <td>149222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Pembroke Pines</td>\n",
       "        <td>137427</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Peoria</td>\n",
       "        <td>112936</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Peoria</td>\n",
       "        <td>108364</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Philadelphia</td>\n",
       "        <td>1517550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Phoenix</td>\n",
       "        <td>1321045</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Pittsburgh</td>\n",
       "        <td>334563</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Plano</td>\n",
       "        <td>222030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Pomona</td>\n",
       "        <td>149473</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Portland</td>\n",
       "        <td>529121</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Portsmouth</td>\n",
       "        <td>100565</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Providence</td>\n",
       "        <td>173618</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Provo</td>\n",
       "        <td>105166</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Pueblo</td>\n",
       "        <td>102121</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Raleigh</td>\n",
       "        <td>276093</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Rancho Cucamonga</td>\n",
       "        <td>127743</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Reno</td>\n",
       "        <td>180480</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Richmond</td>\n",
       "        <td>197790</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Richmond</td>\n",
       "        <td>94100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Riverside</td>\n",
       "        <td>255166</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Roanoke</td>\n",
       "        <td>93357</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Rochester</td>\n",
       "        <td>219773</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Rockford</td>\n",
       "        <td>150115</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sacramento</td>\n",
       "        <td>407018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Saint Louis</td>\n",
       "        <td>348189</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Saint Paul</td>\n",
       "        <td>287151</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Saint Petersburg</td>\n",
       "        <td>248232</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Salem</td>\n",
       "        <td>136924</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Salinas</td>\n",
       "        <td>151060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Salt Lake City</td>\n",
       "        <td>181743</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>San Antonio</td>\n",
       "        <td>1144646</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>San Bernardino</td>\n",
       "        <td>185401</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>San Buenaventura</td>\n",
       "        <td>100916</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>San Diego</td>\n",
       "        <td>1223400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>San Francisco</td>\n",
       "        <td>776733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>San Jose</td>\n",
       "        <td>894943</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>San Mateo</td>\n",
       "        <td>91799</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sandy</td>\n",
       "        <td>101853</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Santa Ana</td>\n",
       "        <td>337977</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Santa Clara</td>\n",
       "        <td>102361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Santa Clarita</td>\n",
       "        <td>151088</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Santa Monica</td>\n",
       "        <td>91084</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Santa Rosa</td>\n",
       "        <td>147595</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Savannah</td>\n",
       "        <td>131510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Scottsdale</td>\n",
       "        <td>202705</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Seattle</td>\n",
       "        <td>563374</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Shreveport</td>\n",
       "        <td>200145</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Simi Valley</td>\n",
       "        <td>111351</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sioux Falls</td>\n",
       "        <td>123975</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>South Bend</td>\n",
       "        <td>107789</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spokane</td>\n",
       "        <td>195629</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Springfield</td>\n",
       "        <td>152082</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Springfield</td>\n",
       "        <td>151580</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Springfield</td>\n",
       "        <td>111454</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Stamford</td>\n",
       "        <td>117083</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sterling Heights</td>\n",
       "        <td>124471</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Stockton</td>\n",
       "        <td>243771</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sunnyvale</td>\n",
       "        <td>131760</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sunrise Manor</td>\n",
       "        <td>95362</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Syracuse</td>\n",
       "        <td>147306</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Tacoma</td>\n",
       "        <td>193556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Tallahassee</td>\n",
       "        <td>150624</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Tampa</td>\n",
       "        <td>303447</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Tempe</td>\n",
       "        <td>158625</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Thousand Oaks</td>\n",
       "        <td>117005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Toledo</td>\n",
       "        <td>313619</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Topeka</td>\n",
       "        <td>122377</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Torrance</td>\n",
       "        <td>137946</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Tucson</td>\n",
       "        <td>486699</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Tulsa</td>\n",
       "        <td>393049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Vallejo</td>\n",
       "        <td>116760</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Vancouver</td>\n",
       "        <td>143560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Virginia Beach</td>\n",
       "        <td>425257</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Visalia</td>\n",
       "        <td>91762</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Waco</td>\n",
       "        <td>113726</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Warren</td>\n",
       "        <td>138247</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Washington</td>\n",
       "        <td>572059</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Waterbury</td>\n",
       "        <td>107271</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>West Covina</td>\n",
       "        <td>105080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>West Valley City</td>\n",
       "        <td>108896</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Westminster</td>\n",
       "        <td>100940</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Wichita</td>\n",
       "        <td>344284</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Wichita Falls</td>\n",
       "        <td>104197</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Winston-Salem</td>\n",
       "        <td>185776</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Worcester</td>\n",
       "        <td>172648</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Yonkers</td>\n",
       "        <td>196086</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Abilene', 115930),\n",
       " ('Akron', 217074),\n",
       " ('Albany', 93994),\n",
       " ('Albuquerque', 448607),\n",
       " ('Alexandria', 128283),\n",
       " ('Allentown', 106632),\n",
       " ('Amarillo', 173627),\n",
       " ('Anaheim', 328014),\n",
       " ('Anchorage', 260283),\n",
       " ('Ann Arbor', 114024),\n",
       " ('Arden-Arcade', 92040),\n",
       " ('Arlington', 332969),\n",
       " ('Arlington', 174838),\n",
       " ('Arvada', 102153),\n",
       " ('Athens-Clarke County', 101489),\n",
       " ('Atlanta', 416474),\n",
       " ('Augusta-Richmond County', 199775),\n",
       " ('Aurora', 276393),\n",
       " ('Aurora', 142990),\n",
       " ('Austin', 656562),\n",
       " ('Bakersfield', 247057),\n",
       " ('Baltimore', 651154),\n",
       " ('Baton Rouge', 227818),\n",
       " ('Beaumont', 113866),\n",
       " ('Bellevue', 109569),\n",
       " ('Berkeley', 102743),\n",
       " ('Billings', 92988),\n",
       " ('Birmingham', 242820),\n",
       " ('Boise City', 185787),\n",
       " ('Boston', 589141),\n",
       " ('Boulder', 91238),\n",
       " ('Bridgeport', 139529),\n",
       " ('Brockton', 93653),\n",
       " ('Brownsville', 139722),\n",
       " ('Buffalo', 292648),\n",
       " ('Burbank', 100316),\n",
       " ('Cambridge', 101355),\n",
       " ('Cape Coral', 102286),\n",
       " ('Carrollton', 109576),\n",
       " ('Carson', 89089),\n",
       " ('Cary', 91213),\n",
       " ('Cedar Rapids', 120758),\n",
       " ('Chandler', 176581),\n",
       " ('Charleston', 89063),\n",
       " ('Charlotte', 540828),\n",
       " ('Chattanooga', 155554),\n",
       " ('Chesapeake', 199184),\n",
       " ('Chicago', 2896016),\n",
       " ('Chula Vista', 173556),\n",
       " ('Cincinnati', 331285),\n",
       " ('Citrus Heights', 103455),\n",
       " ('Clarksville', 108787),\n",
       " ('Clearwater', 99936),\n",
       " ('Cleveland', 478403),\n",
       " ('Colorado Springs', 360890),\n",
       " ('Columbia', 116278),\n",
       " ('Columbus', 711470),\n",
       " ('Columbus', 186291),\n",
       " ('Compton', 92864),\n",
       " ('Concord', 121780),\n",
       " ('Coral Springs', 117549),\n",
       " ('Corona', 124966),\n",
       " ('Corpus Christi', 277454),\n",
       " ('Costa Mesa', 108724),\n",
       " ('Dallas', 1188580),\n",
       " ('Daly City', 103621),\n",
       " ('Davenport', 98256),\n",
       " ('Dayton', 166179),\n",
       " ('Denver', 554636),\n",
       " ('Des Moines', 198682),\n",
       " ('Detroit', 951270),\n",
       " ('Downey', 107323),\n",
       " ('Durham', 187035),\n",
       " ('East Los Angeles', 126379),\n",
       " ('El Cajon', 94578),\n",
       " ('El Monte', 115965),\n",
       " ('El Paso', 563662),\n",
       " ('Elgin', 89408),\n",
       " ('Elizabeth', 120568),\n",
       " ('Erie', 103717),\n",
       " ('Escondido', 133559),\n",
       " ('Eugene', 137893),\n",
       " ('Evansville', 121582),\n",
       " ('Fairfield', 92256),\n",
       " ('Fall River', 90555),\n",
       " ('Fayetteville', 121015),\n",
       " ('Flint', 124943),\n",
       " ('Fontana', 128929),\n",
       " ('Fort Collins', 118652),\n",
       " ('Fort Lauderdale', 152397),\n",
       " ('Fort Wayne', 205727),\n",
       " ('Fort Worth', 534694),\n",
       " ('Fremont', 203413),\n",
       " ('Fresno', 427652),\n",
       " ('Fullerton', 126003),\n",
       " ('Gainesville', 92291),\n",
       " ('Garden Grove', 165196),\n",
       " ('Garland', 215768),\n",
       " ('Gary', 102746),\n",
       " ('Gilbert', 109697),\n",
       " ('Glendale', 218812),\n",
       " ('Glendale', 194973),\n",
       " ('Grand Prairie', 127427),\n",
       " ('Grand Rapids', 197800),\n",
       " ('Green Bay', 102313),\n",
       " ('Greensboro', 223891),\n",
       " ('Hampton', 146437),\n",
       " ('Hartford', 121578),\n",
       " ('Hayward', 140030),\n",
       " ('Henderson', 175381),\n",
       " ('Hialeah', 226419),\n",
       " ('Hollywood', 139357),\n",
       " ('Honolulu', 371657),\n",
       " ('Houston', 1953631),\n",
       " ('Huntington Beach', 189594),\n",
       " ('Huntsville', 158216),\n",
       " ('Independence', 113288),\n",
       " ('Indianapolis', 791926),\n",
       " ('Inglewood', 112580),\n",
       " ('Irvine', 143072),\n",
       " ('Irving', 191615),\n",
       " ('Jackson', 184256),\n",
       " ('Jacksonville', 735167),\n",
       " ('Jersey City', 240055),\n",
       " ('Joliet', 106221),\n",
       " ('Kansas City', 441545),\n",
       " ('Kansas City', 146866),\n",
       " ('Kenosha', 89447),\n",
       " ('Knoxville', 173890),\n",
       " ('Lafayette', 110257),\n",
       " ('Lakewood', 144126),\n",
       " ('Lancaster', 118718),\n",
       " ('Lansing', 119128),\n",
       " ('Laredo', 176576),\n",
       " ('Las Vegas', 478434),\n",
       " ('Lexington-Fayette', 260512),\n",
       " ('Lincoln', 225581),\n",
       " ('Little Rock', 183133),\n",
       " ('Livonia', 100545),\n",
       " ('Long Beach', 461522),\n",
       " ('Los Angeles', 3694820),\n",
       " ('Louisville', 256231),\n",
       " ('Lowell', 105167),\n",
       " ('Lubbock', 199564),\n",
       " ('Macon', 113336),\n",
       " ('Madison', 208054),\n",
       " ('Manchester', 107006),\n",
       " ('McAllen', 106414),\n",
       " ('Memphis', 650100),\n",
       " ('Mesa', 396375),\n",
       " ('Mesquite', 124523),\n",
       " ('Metairie', 149428),\n",
       " ('Miami', 362470),\n",
       " ('Miami Beach', 97855),\n",
       " ('Midland', 98293),\n",
       " ('Milwaukee', 596974),\n",
       " ('Minneapolis', 382618),\n",
       " ('Mission Viejo', 98049),\n",
       " ('Mobile', 198915),\n",
       " ('Modesto', 188856),\n",
       " ('Montgomery', 201568),\n",
       " ('Moreno Valley', 142381),\n",
       " ('Naperville', 128358),\n",
       " ('Nashville-Davidson', 569891),\n",
       " ('New Bedford', 94780),\n",
       " ('New Haven', 123626),\n",
       " ('New Orleans', 484674),\n",
       " ('New York', 8008278),\n",
       " ('Newark', 273546),\n",
       " ('Newport News', 180150),\n",
       " ('Norfolk', 234403),\n",
       " ('Norman', 94193),\n",
       " ('North Las Vegas', 115488),\n",
       " ('Norwalk', 103298),\n",
       " ('Oakland', 399484),\n",
       " ('Oceanside', 161029),\n",
       " ('Odessa', 89293),\n",
       " ('Oklahoma City', 506132),\n",
       " ('Omaha', 390007),\n",
       " ('Ontario', 158007),\n",
       " ('Orange', 128821),\n",
       " ('Orlando', 185951),\n",
       " ('Overland Park', 149080),\n",
       " ('Oxnard', 170358),\n",
       " ('Palmdale', 116670),\n",
       " ('Paradise', 124682),\n",
       " ('Pasadena', 141674),\n",
       " ('Pasadena', 133936),\n",
       " ('Paterson', 149222),\n",
       " ('Pembroke Pines', 137427),\n",
       " ('Peoria', 112936),\n",
       " ('Peoria', 108364),\n",
       " ('Philadelphia', 1517550),\n",
       " ('Phoenix', 1321045),\n",
       " ('Pittsburgh', 334563),\n",
       " ('Plano', 222030),\n",
       " ('Pomona', 149473),\n",
       " ('Portland', 529121),\n",
       " ('Portsmouth', 100565),\n",
       " ('Providence', 173618),\n",
       " ('Provo', 105166),\n",
       " ('Pueblo', 102121),\n",
       " ('Raleigh', 276093),\n",
       " ('Rancho Cucamonga', 127743),\n",
       " ('Reno', 180480),\n",
       " ('Richmond', 197790),\n",
       " ('Richmond', 94100),\n",
       " ('Riverside', 255166),\n",
       " ('Roanoke', 93357),\n",
       " ('Rochester', 219773),\n",
       " ('Rockford', 150115),\n",
       " ('Sacramento', 407018),\n",
       " ('Saint Louis', 348189),\n",
       " ('Saint Paul', 287151),\n",
       " ('Saint Petersburg', 248232),\n",
       " ('Salem', 136924),\n",
       " ('Salinas', 151060),\n",
       " ('Salt Lake City', 181743),\n",
       " ('San Antonio', 1144646),\n",
       " ('San Bernardino', 185401),\n",
       " ('San Buenaventura', 100916),\n",
       " ('San Diego', 1223400),\n",
       " ('San Francisco', 776733),\n",
       " ('San Jose', 894943),\n",
       " ('San Mateo', 91799),\n",
       " ('Sandy', 101853),\n",
       " ('Santa Ana', 337977),\n",
       " ('Santa Clara', 102361),\n",
       " ('Santa Clarita', 151088),\n",
       " ('Santa Monica', 91084),\n",
       " ('Santa Rosa', 147595),\n",
       " ('Savannah', 131510),\n",
       " ('Scottsdale', 202705),\n",
       " ('Seattle', 563374),\n",
       " ('Shreveport', 200145),\n",
       " ('Simi Valley', 111351),\n",
       " ('Sioux Falls', 123975),\n",
       " ('South Bend', 107789),\n",
       " ('Spokane', 195629),\n",
       " ('Springfield', 152082),\n",
       " ('Springfield', 151580),\n",
       " ('Springfield', 111454),\n",
       " ('Stamford', 117083),\n",
       " ('Sterling Heights', 124471),\n",
       " ('Stockton', 243771),\n",
       " ('Sunnyvale', 131760),\n",
       " ('Sunrise Manor', 95362),\n",
       " ('Syracuse', 147306),\n",
       " ('Tacoma', 193556),\n",
       " ('Tallahassee', 150624),\n",
       " ('Tampa', 303447),\n",
       " ('Tempe', 158625),\n",
       " ('Thousand Oaks', 117005),\n",
       " ('Toledo', 313619),\n",
       " ('Topeka', 122377),\n",
       " ('Torrance', 137946),\n",
       " ('Tucson', 486699),\n",
       " ('Tulsa', 393049),\n",
       " ('Vallejo', 116760),\n",
       " ('Vancouver', 143560),\n",
       " ('Virginia Beach', 425257),\n",
       " ('Visalia', 91762),\n",
       " ('Waco', 113726),\n",
       " ('Warren', 138247),\n",
       " ('Washington', 572059),\n",
       " ('Waterbury', 107271),\n",
       " ('West Covina', 105080),\n",
       " ('West Valley City', 108896),\n",
       " ('Westminster', 100940),\n",
       " ('Wichita', 344284),\n",
       " ('Wichita Falls', 104197),\n",
       " ('Winston-Salem', 185776),\n",
       " ('Worcester', 172648),\n",
       " ('Yonkers', 196086)]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select * from city"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}