how-sql-work-in-python-intro-and-usecases.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "_cell_guid": "b1076dfc-b9ad-4769-8c92-a6c4dae69d19",
    "_kg_hide-input": false,
    "_uuid": "8f2839f25d086af736a60e9eeb907d3b93b6e0e5"
   },
   "source": [
    "## How SQL Work - Understanding the implementation\n",
    "![Imgur](https://i.imgur.com/g55maqS.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_cell_guid": "79c7e3d0-c299-4dcb-8224-4455121ee9b0",
    "_uuid": "d629ff2d2480ee46fbb7e2d37f6b5fab8052498a"
   },
   "source": [
    "# &#128209; Contents\n",
    "<ul>\n",
    "<li>1. Introduction</li>\n",
    "<ul>\n",
    "    <li>1.1 Defination Of SQL </li>\n",
    "    <li>1.2 Relational Databases</li>\n",
    "    <li>1.3 Connection Database </li>\n",
    "</ul>\n",
    "<li>2. Implementation </li>\n",
    "<ul>\n",
    "    <li>2.1 Import Libraries </li>\n",
    "    <li>2.2 Database connection code </li>\n",
    "    <li>2.3  Initial exploration of the Dataset </li>\n",
    "    <li>2.4 Overview of Analysis </li>\n",
    "    <li>2.5 SQL Code Implementation</li>\n",
    "</ul>\n",
    "</ul>\n",
    "\n",
    "<br>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "be60f20cfe7f03ed20ed56b8c656e17ee92c9e3e"
   },
   "source": [
    "# &#128253; 1. Introduction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "_kg_hide-input": true,
    "_uuid": "80275340b43464d8c72d4e4dd038178017f1e21f"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style>\n",
       "@import url('https://fonts.googleapis.com/css?family=Ewert|Roboto&effect=3d|ice|');\n",
       "body {background-color: gainsboro;} \n",
       "a {color: #37c9e1; font-family: 'Roboto';} \n",
       "h1 {color: #37c9e1; font-family: 'Orbitron'; text-shadow: 4px 4px 4px #aaa;} \n",
       "h2, h3 {color: slategray; font-family: 'Orbitron'; text-shadow: 4px 4px 4px #aaa;}\n",
       "h4 {color: #818286; font-family: 'Roboto';}\n",
       "span {font-family:'Roboto'; color:black; text-shadow: 5px 5px 5px #aaa;}  \n",
       "div.output_area pre{font-family:'Roboto'; font-size:110%; color:lightblue;}      \n",
       "</style"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%html\n",
    "<style>\n",
    "@import url('https://fonts.googleapis.com/css?family=Ewert|Roboto&effect=3d|ice|');\n",
    "body {background-color: gainsboro;} \n",
    "a {color: #37c9e1; font-family: 'Roboto';} \n",
    "h1 {color: #37c9e1; font-family: 'Orbitron'; text-shadow: 4px 4px 4px #aaa;} \n",
    "h2, h3 {color: slategray; font-family: 'Orbitron'; text-shadow: 4px 4px 4px #aaa;}\n",
    "h4 {color: #818286; font-family: 'Roboto';}\n",
    "span {font-family:'Roboto'; color:black; text-shadow: 5px 5px 5px #aaa;}  \n",
    "div.output_area pre{font-family:'Roboto'; font-size:110%; color:lightblue;}      \n",
    "</style"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "1d070447c3ea30c7eaa5837e64e83b6c04642687"
   },
   "source": [
    "## &#128203; 1.1  Defination of SQL:\n",
    "SQL is a conceptual language for working with data stored in databases. In our case, SQLite is the specific implementation. Most SQL languges share all of the capabilities in this doc. The differences are usually in performance and advances analytical funcionalities (and sometimes bugs of course). Eventually, we will use SQL lunguage to write queries that would pull data from the DB, manipulate it, sort it, and extract it.\n",
    "\n",
    "![Imgur](https://i.imgur.com/3V5m8s5.jpg)\n",
    "\n",
    "The most important component of the DB is its tables - that's where all the data stored. Usually the data would be devided to many tables, and not stored all in one place (so designing the data stracture propely is very important). Most of this script would handle how to work with tables. Other than tables, there are some other very useful concepts/features that we won't talk about:\n",
    "\n",
    "* table creation\n",
    "\n",
    "* inserting / updating data in the DB\n",
    "\n",
    "* functions - gets a value as an input, and returns manipulation of that value (for example function that remove white spaces)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "748137c8cd0bd0c8cc3ef493363b09e0b635150a"
   },
   "source": [
    "## &#128206; 1.2  Relational Databases:\n",
    "A relational database is, simply, a database that stores related information across multiple tables and allows you to query information in more than one table at the same time.\n",
    "\n",
    "It's easier to understand how this works by thinking through an example. Imagine you're a business and you want to keep track of your sales information. You could set up a spreadsheet in Excel with all of the information you want to keep track of as separate columns: Order number, date, amount due, shipment tracking number, customer name, customer address, and customer phone number.\n",
    "\n",
    "![Imgur](https://i.imgur.com/QhbmFgq.png)\n",
    " \n",
    " Sales data in Excel &#128070;\n",
    " \n",
    "![Imgur](https://i.imgur.com/KCj0fP8.png)\n",
    "\n",
    "Sales data in a relational database &#128070;\n",
    "\n",
    "With a relational database, like the one in the above diagram, you avoid all of these issues. You could set up two tables, one for orders and one for customers. The 'customers' table would include a unique ID number for each customer, along with the name, address and phone number we were already tracking. The 'orders' table would include your order number, date, amount due, tracking number and, instead of a separate field for each item of customer data, it would have a column for the customer ID. This enables us to pull up all of the customer info for any given order, but we only have to store it once in our database rather than listing it out again for every single order."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "e96443da7116f4d1dcbb51fa0c4194bc1eb8008f"
   },
   "source": [
    "## &#128268; 1.3  Connection Database:\n",
    "The basic structure of the query is very simple: You define what you want to see after the SELECT, * means all possible columns You choose the table after the FROM You add the conditions for the data you want to use from the table(s) after the WHERE\n",
    "![Imgur](https://i.imgur.com/gSEqsbo.jpg)\n",
    "The stracture, and the order of the sections matter, while spaces, new lines, capital words and indentation are there to make the code easier to read."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_kg_hide-input": false,
    "_uuid": "507b172cefbc843e25947f7a18d5ca8902ada876"
   },
   "source": [
    "# &#128187; Implementation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "b0be4e71572c6d63a68f787613d9c0fa683233f2"
   },
   "source": [
    "## &#128229; 2.1  Import Libraries\n",
    "We will start by importing the Python libraries we will be using in this analysis. These libraries include:\n",
    "\n",
    "* sqllite3 for interacting with a local relational database; \n",
    "\n",
    "* pandas  for data ingestion ."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "_kg_hide-input": false,
    "_uuid": "eb9ba6e83be7408e0e5fb3b2e13534a1394c4b77"
   },
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "80de429f8e10baf46ab3fecc3768f5bc6ac4523b"
   },
   "source": [
    "## &#128268; 2.2 Database Connection Code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "_kg_hide-input": true,
    "_uuid": "c34f3126fb64ba91ca47edffe3788f31e9163597"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Conection SuccessFull <sqlite3.Connection object at 0x7f51d66f3570>\n"
     ]
    }
   ],
   "source": [
    "path = \"../input/\" \n",
    "database = path + 'hubway.db'\n",
    "\n",
    "conn = sqlite3.connect(database)\n",
    "tables = pd.read_sql(\"\"\"SELECT *\n",
    "                        FROM sqlite_master\n",
    "                        WHERE type='table';\"\"\", conn)\n",
    "print(\"Conection SuccessFull\",conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "5bd98f4c49129218dc73a62c1032f572d509bcba"
   },
   "source": [
    "## &#128197; 2.3 Initial exploration of the Dataset\n",
    "Let's start by taking a look at our database. The database has two tables, trips and stations. To begin with, we'll just look at the trips table, which contains the following columns:\n",
    "\n",
    "*` id `— A unique integer that serves as a reference for each trip.\n",
    "\n",
    "*`duration` — The duration of the trip, measured in seconds.\n",
    "\n",
    "*`start_date` — The date and time the trip began.\n",
    "\n",
    "*`start_station` — An integer that corresponds to the id column in the stations table for the station the trip started.\n",
    "\n",
    "*`end_date` — The date and time the trip ended.\n",
    "\n",
    "*` end_station` — The 'id' of the station the trip ended.\n",
    "\n",
    "*` bike_number` — Hubway's unique identifier for the bike used on the trip.\n",
    "\n",
    "*`sub_type` — The subscription type of the user. \"Registered\" for users with a membership, \"Casual\" for users without a membership.\n",
    "\n",
    "*`zip_code`— The zip code of the user (only available for registered members).\n",
    "\n",
    "*`birth_date` — The birth year of the user (only available for registered members).\n",
    "\n",
    "*`gender` — The gender of the user (only available for registered members)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "c8770fb88bcc53d0178409331b67331f5ec275c8"
   },
   "source": [
    "Now, we will need to read the dataset using the commands below.\n",
    "\n",
    "Note: Make sure Database connection before you run the data load and read code below.\n",
    "\n",
    "df is a variable pointing to a pandas data frame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "_kg_hide-input": true,
    "_uuid": "5400cff89752e5c48ef7e0e9dea5eab0d429bb9e"
   },
   "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>id</th>\n",
       "      <th>duration</th>\n",
       "      <th>start_date</th>\n",
       "      <th>start_station</th>\n",
       "      <th>end_date</th>\n",
       "      <th>end_station</th>\n",
       "      <th>bike_number</th>\n",
       "      <th>sub_type</th>\n",
       "      <th>zip_code</th>\n",
       "      <th>birth_date</th>\n",
       "      <th>gender</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>2011-07-28 10:12:00</td>\n",
       "      <td>23</td>\n",
       "      <td>2011-07-28 10:12:00</td>\n",
       "      <td>23</td>\n",
       "      <td>B00468</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'97217</td>\n",
       "      <td>1976</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>220</td>\n",
       "      <td>2011-07-28 10:21:00</td>\n",
       "      <td>23</td>\n",
       "      <td>2011-07-28 10:25:00</td>\n",
       "      <td>23</td>\n",
       "      <td>B00554</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02215</td>\n",
       "      <td>1966</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>56</td>\n",
       "      <td>2011-07-28 10:33:00</td>\n",
       "      <td>23</td>\n",
       "      <td>2011-07-28 10:34:00</td>\n",
       "      <td>23</td>\n",
       "      <td>B00456</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02108</td>\n",
       "      <td>1943</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>64</td>\n",
       "      <td>2011-07-28 10:35:00</td>\n",
       "      <td>23</td>\n",
       "      <td>2011-07-28 10:36:00</td>\n",
       "      <td>23</td>\n",
       "      <td>B00554</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02116</td>\n",
       "      <td>1981</td>\n",
       "      <td>Female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>12</td>\n",
       "      <td>2011-07-28 10:37:00</td>\n",
       "      <td>23</td>\n",
       "      <td>2011-07-28 10:37:00</td>\n",
       "      <td>23</td>\n",
       "      <td>B00554</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'97214</td>\n",
       "      <td>1983</td>\n",
       "      <td>Female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>19</td>\n",
       "      <td>2011-07-28 10:39:00</td>\n",
       "      <td>23</td>\n",
       "      <td>2011-07-28 10:39:00</td>\n",
       "      <td>23</td>\n",
       "      <td>B00456</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02021</td>\n",
       "      <td>1951</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>24</td>\n",
       "      <td>2011-07-28 10:47:00</td>\n",
       "      <td>23</td>\n",
       "      <td>2011-07-28 10:47:00</td>\n",
       "      <td>23</td>\n",
       "      <td>B00554</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02140</td>\n",
       "      <td>1971</td>\n",
       "      <td>Female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>7</td>\n",
       "      <td>2011-07-28 10:48:00</td>\n",
       "      <td>23</td>\n",
       "      <td>2011-07-28 10:48:00</td>\n",
       "      <td>23</td>\n",
       "      <td>B00554</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02140</td>\n",
       "      <td>1971</td>\n",
       "      <td>Female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>8</td>\n",
       "      <td>2011-07-28 11:01:00</td>\n",
       "      <td>23</td>\n",
       "      <td>2011-07-28 11:01:00</td>\n",
       "      <td>23</td>\n",
       "      <td>B00554</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'97214</td>\n",
       "      <td>1983</td>\n",
       "      <td>Female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>1108</td>\n",
       "      <td>2011-07-28 11:55:00</td>\n",
       "      <td>47</td>\n",
       "      <td>2011-07-28 12:13:00</td>\n",
       "      <td>40</td>\n",
       "      <td>B00550</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'01867</td>\n",
       "      <td>1994</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>11</td>\n",
       "      <td>1055</td>\n",
       "      <td>2011-07-28 11:55:00</td>\n",
       "      <td>47</td>\n",
       "      <td>2011-07-28 12:13:00</td>\n",
       "      <td>40</td>\n",
       "      <td>B00580</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'01867</td>\n",
       "      <td>1956</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>12</td>\n",
       "      <td>1042</td>\n",
       "      <td>2011-07-28 11:55:00</td>\n",
       "      <td>47</td>\n",
       "      <td>2011-07-28 12:12:00</td>\n",
       "      <td>40</td>\n",
       "      <td>B00539</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'01867</td>\n",
       "      <td>1959</td>\n",
       "      <td>Female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>13</td>\n",
       "      <td>994</td>\n",
       "      <td>2011-07-28 12:00:00</td>\n",
       "      <td>40</td>\n",
       "      <td>2011-07-28 12:16:00</td>\n",
       "      <td>47</td>\n",
       "      <td>B00368</td>\n",
       "      <td>Casual</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>14</td>\n",
       "      <td>15</td>\n",
       "      <td>2011-07-28 12:00:00</td>\n",
       "      <td>22</td>\n",
       "      <td>2011-07-28 12:00:00</td>\n",
       "      <td>22</td>\n",
       "      <td>B00442</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02446</td>\n",
       "      <td>1982</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>15</td>\n",
       "      <td>952</td>\n",
       "      <td>2011-07-28 12:00:00</td>\n",
       "      <td>40</td>\n",
       "      <td>2011-07-28 12:16:00</td>\n",
       "      <td>23</td>\n",
       "      <td>B00556</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02128</td>\n",
       "      <td>1944</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>16</td>\n",
       "      <td>32</td>\n",
       "      <td>2011-07-28 12:00:00</td>\n",
       "      <td>22</td>\n",
       "      <td>2011-07-28 12:01:00</td>\n",
       "      <td>22</td>\n",
       "      <td>B00316</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02115</td>\n",
       "      <td>1960</td>\n",
       "      <td>Female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>17</td>\n",
       "      <td>1261</td>\n",
       "      <td>2011-07-28 12:00:00</td>\n",
       "      <td>22</td>\n",
       "      <td>2011-07-28 12:21:00</td>\n",
       "      <td>45</td>\n",
       "      <td>B00454</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02492</td>\n",
       "      <td>1975</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>18</td>\n",
       "      <td>7</td>\n",
       "      <td>2011-07-28 12:00:00</td>\n",
       "      <td>22</td>\n",
       "      <td>2011-07-28 12:00:00</td>\n",
       "      <td>22</td>\n",
       "      <td>B00404</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02446</td>\n",
       "      <td>1956</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>19</td>\n",
       "      <td>1020</td>\n",
       "      <td>2011-07-28 12:01:00</td>\n",
       "      <td>38</td>\n",
       "      <td>2011-07-28 12:18:00</td>\n",
       "      <td>36</td>\n",
       "      <td>B00147</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02118</td>\n",
       "      <td>1987</td>\n",
       "      <td>Female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>20</td>\n",
       "      <td>1264</td>\n",
       "      <td>2011-07-28 12:01:00</td>\n",
       "      <td>38</td>\n",
       "      <td>2011-07-28 12:22:00</td>\n",
       "      <td>44</td>\n",
       "      <td>B00165</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02139</td>\n",
       "      <td>1985</td>\n",
       "      <td>Female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>21</td>\n",
       "      <td>1043</td>\n",
       "      <td>2011-07-28 12:01:00</td>\n",
       "      <td>38</td>\n",
       "      <td>2011-07-28 12:18:00</td>\n",
       "      <td>44</td>\n",
       "      <td>B00527</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02351</td>\n",
       "      <td>1978</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>22</td>\n",
       "      <td>969</td>\n",
       "      <td>2011-07-28 12:01:00</td>\n",
       "      <td>22</td>\n",
       "      <td>2011-07-28 12:17:00</td>\n",
       "      <td>44</td>\n",
       "      <td>B00487</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02143</td>\n",
       "      <td>1971</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>23</td>\n",
       "      <td>1582</td>\n",
       "      <td>2011-07-28 12:01:00</td>\n",
       "      <td>38</td>\n",
       "      <td>2011-07-28 12:27:00</td>\n",
       "      <td>10</td>\n",
       "      <td>B00093</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02130</td>\n",
       "      <td>1980</td>\n",
       "      <td>Female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>24</td>\n",
       "      <td>620</td>\n",
       "      <td>2011-07-28 12:01:00</td>\n",
       "      <td>22</td>\n",
       "      <td>2011-07-28 12:12:00</td>\n",
       "      <td>20</td>\n",
       "      <td>B00375</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02474</td>\n",
       "      <td>1955</td>\n",
       "      <td>Female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>25</td>\n",
       "      <td>7</td>\n",
       "      <td>2011-07-28 12:02:00</td>\n",
       "      <td>22</td>\n",
       "      <td>2011-07-28 12:02:00</td>\n",
       "      <td>22</td>\n",
       "      <td>B00275</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02140</td>\n",
       "      <td>1971</td>\n",
       "      <td>Female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>26</td>\n",
       "      <td>771</td>\n",
       "      <td>2011-07-28 12:02:00</td>\n",
       "      <td>38</td>\n",
       "      <td>2011-07-28 12:15:00</td>\n",
       "      <td>42</td>\n",
       "      <td>B00279</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02108</td>\n",
       "      <td>1943</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>27</td>\n",
       "      <td>1222</td>\n",
       "      <td>2011-07-28 12:02:00</td>\n",
       "      <td>38</td>\n",
       "      <td>2011-07-28 12:22:00</td>\n",
       "      <td>44</td>\n",
       "      <td>B00074</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02143</td>\n",
       "      <td>1972</td>\n",
       "      <td>Female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>28</td>\n",
       "      <td>4</td>\n",
       "      <td>2011-07-28 12:02:00</td>\n",
       "      <td>22</td>\n",
       "      <td>2011-07-28 12:02:00</td>\n",
       "      <td>22</td>\n",
       "      <td>B00137</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'01776</td>\n",
       "      <td>1949</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>29</td>\n",
       "      <td>5</td>\n",
       "      <td>2011-07-28 12:02:00</td>\n",
       "      <td>22</td>\n",
       "      <td>2011-07-28 12:02:00</td>\n",
       "      <td>22</td>\n",
       "      <td>B00082</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'01880</td>\n",
       "      <td>1968</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>30</td>\n",
       "      <td>824</td>\n",
       "      <td>2011-07-28 12:02:00</td>\n",
       "      <td>22</td>\n",
       "      <td>2011-07-28 12:16:00</td>\n",
       "      <td>38</td>\n",
       "      <td>B00460</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02131</td>\n",
       "      <td>1967</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569971</th>\n",
       "      <td>1578995</td>\n",
       "      <td>360</td>\n",
       "      <td>2013-11-30 20:17:00</td>\n",
       "      <td>80</td>\n",
       "      <td>2013-11-30 20:23:00</td>\n",
       "      <td>135</td>\n",
       "      <td>T01149</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02139</td>\n",
       "      <td></td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569972</th>\n",
       "      <td>1578996</td>\n",
       "      <td>540</td>\n",
       "      <td>2013-11-30 20:17:00</td>\n",
       "      <td>76</td>\n",
       "      <td>2013-11-30 20:26:00</td>\n",
       "      <td>91</td>\n",
       "      <td>T01268</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02139</td>\n",
       "      <td></td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569973</th>\n",
       "      <td>1578997</td>\n",
       "      <td>300</td>\n",
       "      <td>2013-11-30 20:23:00</td>\n",
       "      <td>136</td>\n",
       "      <td>2013-11-30 20:28:00</td>\n",
       "      <td>67</td>\n",
       "      <td>T01352</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02142</td>\n",
       "      <td></td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569974</th>\n",
       "      <td>1578998</td>\n",
       "      <td>420</td>\n",
       "      <td>2013-11-30 20:23:00</td>\n",
       "      <td>76</td>\n",
       "      <td>2013-11-30 20:30:00</td>\n",
       "      <td>80</td>\n",
       "      <td>T01185</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02215</td>\n",
       "      <td></td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569975</th>\n",
       "      <td>1579000</td>\n",
       "      <td>300</td>\n",
       "      <td>2013-11-30 20:33:00</td>\n",
       "      <td>133</td>\n",
       "      <td>2013-11-30 20:38:00</td>\n",
       "      <td>74</td>\n",
       "      <td>T01217</td>\n",
       "      <td>Casual</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569976</th>\n",
       "      <td>1579001</td>\n",
       "      <td>1200</td>\n",
       "      <td>2013-11-30 20:46:00</td>\n",
       "      <td>137</td>\n",
       "      <td>2013-11-30 21:06:00</td>\n",
       "      <td>80</td>\n",
       "      <td>T01146</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02171</td>\n",
       "      <td></td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569977</th>\n",
       "      <td>1579002</td>\n",
       "      <td>360</td>\n",
       "      <td>2013-11-30 20:50:00</td>\n",
       "      <td>136</td>\n",
       "      <td>2013-11-30 20:56:00</td>\n",
       "      <td>67</td>\n",
       "      <td>T01328</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02142</td>\n",
       "      <td></td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569978</th>\n",
       "      <td>1579003</td>\n",
       "      <td>300</td>\n",
       "      <td>2013-11-30 20:59:00</td>\n",
       "      <td>68</td>\n",
       "      <td>2013-11-30 21:04:00</td>\n",
       "      <td>95</td>\n",
       "      <td>T01182</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02445</td>\n",
       "      <td></td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569979</th>\n",
       "      <td>1579004</td>\n",
       "      <td>300</td>\n",
       "      <td>2013-11-30 21:01:00</td>\n",
       "      <td>84</td>\n",
       "      <td>2013-11-30 21:06:00</td>\n",
       "      <td>128</td>\n",
       "      <td>B01468</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02142</td>\n",
       "      <td></td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569980</th>\n",
       "      <td>1579005</td>\n",
       "      <td>960</td>\n",
       "      <td>2013-11-30 21:07:00</td>\n",
       "      <td>68</td>\n",
       "      <td>2013-11-30 21:23:00</td>\n",
       "      <td>74</td>\n",
       "      <td>T01439</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02215</td>\n",
       "      <td></td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569981</th>\n",
       "      <td>1579006</td>\n",
       "      <td>180</td>\n",
       "      <td>2013-11-30 21:10:00</td>\n",
       "      <td>72</td>\n",
       "      <td>2013-11-30 21:13:00</td>\n",
       "      <td>91</td>\n",
       "      <td>T01351</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02142</td>\n",
       "      <td></td>\n",
       "      <td>Female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569982</th>\n",
       "      <td>1579007</td>\n",
       "      <td>420</td>\n",
       "      <td>2013-11-30 21:19:00</td>\n",
       "      <td>96</td>\n",
       "      <td>2013-11-30 21:26:00</td>\n",
       "      <td>76</td>\n",
       "      <td>T01175</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02139</td>\n",
       "      <td></td>\n",
       "      <td>Female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569983</th>\n",
       "      <td>1579008</td>\n",
       "      <td>300</td>\n",
       "      <td>2013-11-30 21:41:00</td>\n",
       "      <td>67</td>\n",
       "      <td>2013-11-30 21:46:00</td>\n",
       "      <td>68</td>\n",
       "      <td>T01343</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02139</td>\n",
       "      <td></td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569984</th>\n",
       "      <td>1579009</td>\n",
       "      <td>300</td>\n",
       "      <td>2013-11-30 21:47:00</td>\n",
       "      <td>137</td>\n",
       "      <td>2013-11-30 21:52:00</td>\n",
       "      <td>133</td>\n",
       "      <td>B01489</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02452</td>\n",
       "      <td></td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569985</th>\n",
       "      <td>1579010</td>\n",
       "      <td>780</td>\n",
       "      <td>2013-11-30 21:54:00</td>\n",
       "      <td>129</td>\n",
       "      <td>2013-11-30 22:07:00</td>\n",
       "      <td>91</td>\n",
       "      <td>T01350</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02139</td>\n",
       "      <td></td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569986</th>\n",
       "      <td>1579011</td>\n",
       "      <td>540</td>\n",
       "      <td>2013-11-30 21:55:00</td>\n",
       "      <td>76</td>\n",
       "      <td>2013-11-30 22:04:00</td>\n",
       "      <td>68</td>\n",
       "      <td>T01175</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02139</td>\n",
       "      <td></td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569987</th>\n",
       "      <td>1579012</td>\n",
       "      <td>1440</td>\n",
       "      <td>2013-11-30 22:15:00</td>\n",
       "      <td>130</td>\n",
       "      <td>2013-11-30 22:39:00</td>\n",
       "      <td>68</td>\n",
       "      <td>B01480</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02139</td>\n",
       "      <td></td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569988</th>\n",
       "      <td>1579013</td>\n",
       "      <td>480</td>\n",
       "      <td>2013-11-30 22:25:00</td>\n",
       "      <td>137</td>\n",
       "      <td>2013-11-30 22:33:00</td>\n",
       "      <td>74</td>\n",
       "      <td>T01308</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02140</td>\n",
       "      <td></td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569989</th>\n",
       "      <td>1579014</td>\n",
       "      <td>420</td>\n",
       "      <td>2013-11-30 22:25:00</td>\n",
       "      <td>137</td>\n",
       "      <td>2013-11-30 22:32:00</td>\n",
       "      <td>74</td>\n",
       "      <td>T01043</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02139</td>\n",
       "      <td></td>\n",
       "      <td>Female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569990</th>\n",
       "      <td>1579015</td>\n",
       "      <td>360</td>\n",
       "      <td>2013-11-30 22:51:00</td>\n",
       "      <td>67</td>\n",
       "      <td>2013-11-30 22:57:00</td>\n",
       "      <td>68</td>\n",
       "      <td>T01352</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02139</td>\n",
       "      <td></td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569991</th>\n",
       "      <td>1579016</td>\n",
       "      <td>300</td>\n",
       "      <td>2013-11-30 22:53:00</td>\n",
       "      <td>74</td>\n",
       "      <td>2013-11-30 22:58:00</td>\n",
       "      <td>87</td>\n",
       "      <td>T01217</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02138</td>\n",
       "      <td></td>\n",
       "      <td>Female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569992</th>\n",
       "      <td>1579017</td>\n",
       "      <td>120</td>\n",
       "      <td>2013-11-30 23:07:00</td>\n",
       "      <td>128</td>\n",
       "      <td>2013-11-30 23:09:00</td>\n",
       "      <td>136</td>\n",
       "      <td>B01468</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02142</td>\n",
       "      <td></td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569993</th>\n",
       "      <td>1579018</td>\n",
       "      <td>900</td>\n",
       "      <td>2013-11-30 23:17:00</td>\n",
       "      <td>76</td>\n",
       "      <td>2013-11-30 23:32:00</td>\n",
       "      <td>96</td>\n",
       "      <td>B00585</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02131</td>\n",
       "      <td></td>\n",
       "      <td>Female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569994</th>\n",
       "      <td>1579019</td>\n",
       "      <td>240</td>\n",
       "      <td>2013-11-30 23:24:00</td>\n",
       "      <td>134</td>\n",
       "      <td>2013-11-30 23:28:00</td>\n",
       "      <td>139</td>\n",
       "      <td>T01195</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02478</td>\n",
       "      <td></td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569995</th>\n",
       "      <td>1579020</td>\n",
       "      <td>360</td>\n",
       "      <td>2013-11-30 23:24:00</td>\n",
       "      <td>129</td>\n",
       "      <td>2013-11-30 23:30:00</td>\n",
       "      <td>133</td>\n",
       "      <td>T01069</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02138</td>\n",
       "      <td></td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569996</th>\n",
       "      <td>1579021</td>\n",
       "      <td>720</td>\n",
       "      <td>2013-11-30 23:30:00</td>\n",
       "      <td>130</td>\n",
       "      <td>2013-11-30 23:42:00</td>\n",
       "      <td>90</td>\n",
       "      <td>T01341</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02141</td>\n",
       "      <td></td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569997</th>\n",
       "      <td>1579022</td>\n",
       "      <td>480</td>\n",
       "      <td>2013-11-30 23:32:00</td>\n",
       "      <td>67</td>\n",
       "      <td>2013-11-30 23:40:00</td>\n",
       "      <td>88</td>\n",
       "      <td>T01328</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02143</td>\n",
       "      <td></td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569998</th>\n",
       "      <td>1579023</td>\n",
       "      <td>540</td>\n",
       "      <td>2013-11-30 23:32:00</td>\n",
       "      <td>137</td>\n",
       "      <td>2013-11-30 23:41:00</td>\n",
       "      <td>133</td>\n",
       "      <td>T01310</td>\n",
       "      <td>Casual</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569999</th>\n",
       "      <td>1579024</td>\n",
       "      <td>120</td>\n",
       "      <td>2013-11-30 23:36:00</td>\n",
       "      <td>76</td>\n",
       "      <td>2013-11-30 23:38:00</td>\n",
       "      <td>68</td>\n",
       "      <td>B01478</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02141</td>\n",
       "      <td></td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1570000</th>\n",
       "      <td>1579025</td>\n",
       "      <td>420</td>\n",
       "      <td>2013-11-30 23:39:00</td>\n",
       "      <td>68</td>\n",
       "      <td>2013-11-30 23:46:00</td>\n",
       "      <td>95</td>\n",
       "      <td>T01352</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02143</td>\n",
       "      <td></td>\n",
       "      <td>Female\\n</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1570001 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              id  duration    ...    birth_date    gender\n",
       "0              1         9    ...          1976      Male\n",
       "1              2       220    ...          1966      Male\n",
       "2              3        56    ...          1943      Male\n",
       "3              4        64    ...          1981    Female\n",
       "4              5        12    ...          1983    Female\n",
       "5              6        19    ...          1951      Male\n",
       "6              7        24    ...          1971    Female\n",
       "7              8         7    ...          1971    Female\n",
       "8              9         8    ...          1983    Female\n",
       "9             10      1108    ...          1994      Male\n",
       "10            11      1055    ...          1956      Male\n",
       "11            12      1042    ...          1959    Female\n",
       "12            13       994    ...                        \n",
       "13            14        15    ...          1982      Male\n",
       "14            15       952    ...          1944      Male\n",
       "15            16        32    ...          1960    Female\n",
       "16            17      1261    ...          1975      Male\n",
       "17            18         7    ...          1956      Male\n",
       "18            19      1020    ...          1987    Female\n",
       "19            20      1264    ...          1985    Female\n",
       "20            21      1043    ...          1978      Male\n",
       "21            22       969    ...          1971      Male\n",
       "22            23      1582    ...          1980    Female\n",
       "23            24       620    ...          1955    Female\n",
       "24            25         7    ...          1971    Female\n",
       "25            26       771    ...          1943      Male\n",
       "26            27      1222    ...          1972    Female\n",
       "27            28         4    ...          1949      Male\n",
       "28            29         5    ...          1968      Male\n",
       "29            30       824    ...          1967      Male\n",
       "...          ...       ...    ...           ...       ...\n",
       "1569971  1578995       360    ...                    Male\n",
       "1569972  1578996       540    ...                    Male\n",
       "1569973  1578997       300    ...                    Male\n",
       "1569974  1578998       420    ...                    Male\n",
       "1569975  1579000       300    ...                        \n",
       "1569976  1579001      1200    ...                    Male\n",
       "1569977  1579002       360    ...                    Male\n",
       "1569978  1579003       300    ...                    Male\n",
       "1569979  1579004       300    ...                    Male\n",
       "1569980  1579005       960    ...                    Male\n",
       "1569981  1579006       180    ...                  Female\n",
       "1569982  1579007       420    ...                  Female\n",
       "1569983  1579008       300    ...                    Male\n",
       "1569984  1579009       300    ...                    Male\n",
       "1569985  1579010       780    ...                    Male\n",
       "1569986  1579011       540    ...                    Male\n",
       "1569987  1579012      1440    ...                    Male\n",
       "1569988  1579013       480    ...                    Male\n",
       "1569989  1579014       420    ...                  Female\n",
       "1569990  1579015       360    ...                    Male\n",
       "1569991  1579016       300    ...                  Female\n",
       "1569992  1579017       120    ...                    Male\n",
       "1569993  1579018       900    ...                  Female\n",
       "1569994  1579019       240    ...                    Male\n",
       "1569995  1579020       360    ...                    Male\n",
       "1569996  1579021       720    ...                    Male\n",
       "1569997  1579022       480    ...                    Male\n",
       "1569998  1579023       540    ...                        \n",
       "1569999  1579024       120    ...                    Male\n",
       "1570000  1579025       420    ...                Female\\n\n",
       "\n",
       "[1570001 rows x 11 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_sql_query(\"SELECT * FROM trips\", conn)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "38907ed5b9c6e29844d76e6468ab401251b4c9b4"
   },
   "source": [
    "## &#128240; 2.4 Overview of Analysis\n",
    "In this Notebook I will  try cover up some Question By SQL Code or Command,Here are some Question:\n",
    "\n",
    "* What was the duration of the longest trip?\n",
    "\n",
    "* How many trips were taken by 'registered' users?\n",
    "\n",
    "* What was the average trip duration?\n",
    "\n",
    "* Do registered or casual users take longer trips?\n",
    "\n",
    "* Which bike was used for the most trips?\n",
    "\n",
    "* What is the average duration of trips by users over the age of 30?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_kg_hide-output": false,
    "_uuid": "a2f56d87a1e488b88d14dd5153d420699f6ae707"
   },
   "source": [
    "## &#128187; 2.5 SQL Code Implementation\n",
    "The SQL code or commands we'll use to answer these questions are:\n",
    "<ul>\n",
    "    <li>1. SELECT</li>\n",
    "    <li>2. LIMIT</li>\n",
    "    <li>3.  ORDER BY</li>\n",
    "    <li>4.WHERE</li>\n",
    "    <li>5.COUNT</li>\n",
    "    <li>6.Aggregate Functions(SUM,AVG,MIN,MAX)</li>\n",
    "    <li>7.GROUP BY</li>\n",
    "    <li>8. Arithmetic Operators</li>\n",
    "    <li>9. JOIN</li>\n",
    "    </ul>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "d83fd7cc2efd6698b6881f83f8ce2892d3b99542"
   },
   "source": [
    "# &#128505; 1. SELECT \n",
    "SELECT will be the foundation of almost every query you write - it tells the database which columns you want to see. You can either specify columns by name (separated by commas) or use the wildcard `*` to return every column in the table. In addition to the columns you want to retrieve, you also have to tell the database which table to get them from. To do this you use the keyword `FROM` followed by the name of the table. For example, if we wanted to see the start_date and bike_number for every trip in the trips table, we could use the following query:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "_kg_hide-input": true,
    "_uuid": "6b1e781a2e52b53035c3b7cca0a25cfe78129513"
   },
   "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>start_date</th>\n",
       "      <th>bike_number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2011-07-28 10:12:00</td>\n",
       "      <td>B00468</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2011-07-28 10:21:00</td>\n",
       "      <td>B00554</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2011-07-28 10:33:00</td>\n",
       "      <td>B00456</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2011-07-28 10:35:00</td>\n",
       "      <td>B00554</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2011-07-28 10:37:00</td>\n",
       "      <td>B00554</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2011-07-28 10:39:00</td>\n",
       "      <td>B00456</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2011-07-28 10:47:00</td>\n",
       "      <td>B00554</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2011-07-28 10:48:00</td>\n",
       "      <td>B00554</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2011-07-28 11:01:00</td>\n",
       "      <td>B00554</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2011-07-28 11:55:00</td>\n",
       "      <td>B00550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2011-07-28 11:55:00</td>\n",
       "      <td>B00580</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2011-07-28 11:55:00</td>\n",
       "      <td>B00539</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2011-07-28 12:00:00</td>\n",
       "      <td>B00368</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2011-07-28 12:00:00</td>\n",
       "      <td>B00442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2011-07-28 12:00:00</td>\n",
       "      <td>B00556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2011-07-28 12:00:00</td>\n",
       "      <td>B00316</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2011-07-28 12:00:00</td>\n",
       "      <td>B00454</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2011-07-28 12:00:00</td>\n",
       "      <td>B00404</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2011-07-28 12:01:00</td>\n",
       "      <td>B00147</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2011-07-28 12:01:00</td>\n",
       "      <td>B00165</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2011-07-28 12:01:00</td>\n",
       "      <td>B00527</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2011-07-28 12:01:00</td>\n",
       "      <td>B00487</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2011-07-28 12:01:00</td>\n",
       "      <td>B00093</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>2011-07-28 12:01:00</td>\n",
       "      <td>B00375</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>2011-07-28 12:02:00</td>\n",
       "      <td>B00275</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>2011-07-28 12:02:00</td>\n",
       "      <td>B00279</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>2011-07-28 12:02:00</td>\n",
       "      <td>B00074</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>2011-07-28 12:02:00</td>\n",
       "      <td>B00137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>2011-07-28 12:02:00</td>\n",
       "      <td>B00082</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>2011-07-28 12:02:00</td>\n",
       "      <td>B00460</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569971</th>\n",
       "      <td>2013-11-30 20:17:00</td>\n",
       "      <td>T01149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569972</th>\n",
       "      <td>2013-11-30 20:17:00</td>\n",
       "      <td>T01268</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569973</th>\n",
       "      <td>2013-11-30 20:23:00</td>\n",
       "      <td>T01352</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569974</th>\n",
       "      <td>2013-11-30 20:23:00</td>\n",
       "      <td>T01185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569975</th>\n",
       "      <td>2013-11-30 20:33:00</td>\n",
       "      <td>T01217</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569976</th>\n",
       "      <td>2013-11-30 20:46:00</td>\n",
       "      <td>T01146</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569977</th>\n",
       "      <td>2013-11-30 20:50:00</td>\n",
       "      <td>T01328</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569978</th>\n",
       "      <td>2013-11-30 20:59:00</td>\n",
       "      <td>T01182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569979</th>\n",
       "      <td>2013-11-30 21:01:00</td>\n",
       "      <td>B01468</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569980</th>\n",
       "      <td>2013-11-30 21:07:00</td>\n",
       "      <td>T01439</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569981</th>\n",
       "      <td>2013-11-30 21:10:00</td>\n",
       "      <td>T01351</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569982</th>\n",
       "      <td>2013-11-30 21:19:00</td>\n",
       "      <td>T01175</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569983</th>\n",
       "      <td>2013-11-30 21:41:00</td>\n",
       "      <td>T01343</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569984</th>\n",
       "      <td>2013-11-30 21:47:00</td>\n",
       "      <td>B01489</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569985</th>\n",
       "      <td>2013-11-30 21:54:00</td>\n",
       "      <td>T01350</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569986</th>\n",
       "      <td>2013-11-30 21:55:00</td>\n",
       "      <td>T01175</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569987</th>\n",
       "      <td>2013-11-30 22:15:00</td>\n",
       "      <td>B01480</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569988</th>\n",
       "      <td>2013-11-30 22:25:00</td>\n",
       "      <td>T01308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569989</th>\n",
       "      <td>2013-11-30 22:25:00</td>\n",
       "      <td>T01043</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569990</th>\n",
       "      <td>2013-11-30 22:51:00</td>\n",
       "      <td>T01352</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569991</th>\n",
       "      <td>2013-11-30 22:53:00</td>\n",
       "      <td>T01217</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569992</th>\n",
       "      <td>2013-11-30 23:07:00</td>\n",
       "      <td>B01468</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569993</th>\n",
       "      <td>2013-11-30 23:17:00</td>\n",
       "      <td>B00585</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569994</th>\n",
       "      <td>2013-11-30 23:24:00</td>\n",
       "      <td>T01195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569995</th>\n",
       "      <td>2013-11-30 23:24:00</td>\n",
       "      <td>T01069</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569996</th>\n",
       "      <td>2013-11-30 23:30:00</td>\n",
       "      <td>T01341</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569997</th>\n",
       "      <td>2013-11-30 23:32:00</td>\n",
       "      <td>T01328</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569998</th>\n",
       "      <td>2013-11-30 23:32:00</td>\n",
       "      <td>T01310</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569999</th>\n",
       "      <td>2013-11-30 23:36:00</td>\n",
       "      <td>B01478</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1570000</th>\n",
       "      <td>2013-11-30 23:39:00</td>\n",
       "      <td>T01352</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1570001 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  start_date bike_number\n",
       "0        2011-07-28 10:12:00      B00468\n",
       "1        2011-07-28 10:21:00      B00554\n",
       "2        2011-07-28 10:33:00      B00456\n",
       "3        2011-07-28 10:35:00      B00554\n",
       "4        2011-07-28 10:37:00      B00554\n",
       "5        2011-07-28 10:39:00      B00456\n",
       "6        2011-07-28 10:47:00      B00554\n",
       "7        2011-07-28 10:48:00      B00554\n",
       "8        2011-07-28 11:01:00      B00554\n",
       "9        2011-07-28 11:55:00      B00550\n",
       "10       2011-07-28 11:55:00      B00580\n",
       "11       2011-07-28 11:55:00      B00539\n",
       "12       2011-07-28 12:00:00      B00368\n",
       "13       2011-07-28 12:00:00      B00442\n",
       "14       2011-07-28 12:00:00      B00556\n",
       "15       2011-07-28 12:00:00      B00316\n",
       "16       2011-07-28 12:00:00      B00454\n",
       "17       2011-07-28 12:00:00      B00404\n",
       "18       2011-07-28 12:01:00      B00147\n",
       "19       2011-07-28 12:01:00      B00165\n",
       "20       2011-07-28 12:01:00      B00527\n",
       "21       2011-07-28 12:01:00      B00487\n",
       "22       2011-07-28 12:01:00      B00093\n",
       "23       2011-07-28 12:01:00      B00375\n",
       "24       2011-07-28 12:02:00      B00275\n",
       "25       2011-07-28 12:02:00      B00279\n",
       "26       2011-07-28 12:02:00      B00074\n",
       "27       2011-07-28 12:02:00      B00137\n",
       "28       2011-07-28 12:02:00      B00082\n",
       "29       2011-07-28 12:02:00      B00460\n",
       "...                      ...         ...\n",
       "1569971  2013-11-30 20:17:00      T01149\n",
       "1569972  2013-11-30 20:17:00      T01268\n",
       "1569973  2013-11-30 20:23:00      T01352\n",
       "1569974  2013-11-30 20:23:00      T01185\n",
       "1569975  2013-11-30 20:33:00      T01217\n",
       "1569976  2013-11-30 20:46:00      T01146\n",
       "1569977  2013-11-30 20:50:00      T01328\n",
       "1569978  2013-11-30 20:59:00      T01182\n",
       "1569979  2013-11-30 21:01:00      B01468\n",
       "1569980  2013-11-30 21:07:00      T01439\n",
       "1569981  2013-11-30 21:10:00      T01351\n",
       "1569982  2013-11-30 21:19:00      T01175\n",
       "1569983  2013-11-30 21:41:00      T01343\n",
       "1569984  2013-11-30 21:47:00      B01489\n",
       "1569985  2013-11-30 21:54:00      T01350\n",
       "1569986  2013-11-30 21:55:00      T01175\n",
       "1569987  2013-11-30 22:15:00      B01480\n",
       "1569988  2013-11-30 22:25:00      T01308\n",
       "1569989  2013-11-30 22:25:00      T01043\n",
       "1569990  2013-11-30 22:51:00      T01352\n",
       "1569991  2013-11-30 22:53:00      T01217\n",
       "1569992  2013-11-30 23:07:00      B01468\n",
       "1569993  2013-11-30 23:17:00      B00585\n",
       "1569994  2013-11-30 23:24:00      T01195\n",
       "1569995  2013-11-30 23:24:00      T01069\n",
       "1569996  2013-11-30 23:30:00      T01341\n",
       "1569997  2013-11-30 23:32:00      T01328\n",
       "1569998  2013-11-30 23:32:00      T01310\n",
       "1569999  2013-11-30 23:36:00      B01478\n",
       "1570000  2013-11-30 23:39:00      T01352\n",
       "\n",
       "[1570001 rows x 2 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\"SELECT start_date, bike_number FROM trips;\",conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "01ad314149732c9c4458484232239052e2c18b28"
   },
   "source": [
    "### &#128681; Note:\n",
    "In this example, we started with the SELECT command so that the database knows we want it to find us some data. Then we told the database we were interested in the start_date and bike_number columns. Finally we used FROM to let the database know that the columns we want to see are part of the trips table.\n",
    "\n",
    "One important thing to be aware of when writing SQL queries is that the language requires every query to end with a semicolon (;)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "00a5cfece600bbee5692ea109dbfc2f6702936a7"
   },
   "source": [
    "# &#128208; 2. LIMIT \n",
    "The next command we need to know before we start to run queries on our Hubway database is LIMIT. LIMIT simply tells the database how many rows you want it to return. The SELECT query we looked at in the previous section would return the requested information for every row in the trips table, but sometimes that could mean a lot of data and you might not want all of it. If, instead, we wanted to see the start_date and bike_number for the first five trips in the database, we could add LIMIT to our query as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "_kg_hide-input": true,
    "_uuid": "2d33ef5904a9e06008fc59f722260f744c23a98a"
   },
   "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>start_date</th>\n",
       "      <th>bike_number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2011-07-28 10:12:00</td>\n",
       "      <td>B00468</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2011-07-28 10:21:00</td>\n",
       "      <td>B00554</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2011-07-28 10:33:00</td>\n",
       "      <td>B00456</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2011-07-28 10:35:00</td>\n",
       "      <td>B00554</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2011-07-28 10:37:00</td>\n",
       "      <td>B00554</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            start_date bike_number\n",
       "0  2011-07-28 10:12:00      B00468\n",
       "1  2011-07-28 10:21:00      B00554\n",
       "2  2011-07-28 10:33:00      B00456\n",
       "3  2011-07-28 10:35:00      B00554\n",
       "4  2011-07-28 10:37:00      B00554"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\"SELECT start_date, bike_number FROM trips LIMIT 5;\",conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "f0f8e3fc6f6920f78f2042812cff24415bfa8f1f"
   },
   "source": [
    "We simply added the LIMIT command and then a number representing the number of rows we want to be returned. In this instance we used 5, but you can replace that with any number to get the appropriate amount of data for the project you're working on.\n",
    "\n",
    "We will use LIMIT a lot in our queries on the Hubway database in this notebook - the trips table contains over a 1.5 million rows of data and we certainly don't need to display all of those to answer the questions we're going to be working on.\n",
    "\n",
    "Let's run our first query on the Hubway database. First we will store our query as a string and then use the function we defined earlier to run it on the database. Take a look at the following example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "_kg_hide-input": true,
    "_uuid": "a9170d58aeaad32f3d8cfac8ae9bffe2fb020969"
   },
   "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>id</th>\n",
       "      <th>duration</th>\n",
       "      <th>start_date</th>\n",
       "      <th>start_station</th>\n",
       "      <th>end_date</th>\n",
       "      <th>end_station</th>\n",
       "      <th>bike_number</th>\n",
       "      <th>sub_type</th>\n",
       "      <th>zip_code</th>\n",
       "      <th>birth_date</th>\n",
       "      <th>gender</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>2011-07-28 10:12:00</td>\n",
       "      <td>23</td>\n",
       "      <td>2011-07-28 10:12:00</td>\n",
       "      <td>23</td>\n",
       "      <td>B00468</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'97217</td>\n",
       "      <td>1976.0</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>220</td>\n",
       "      <td>2011-07-28 10:21:00</td>\n",
       "      <td>23</td>\n",
       "      <td>2011-07-28 10:25:00</td>\n",
       "      <td>23</td>\n",
       "      <td>B00554</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02215</td>\n",
       "      <td>1966.0</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>56</td>\n",
       "      <td>2011-07-28 10:33:00</td>\n",
       "      <td>23</td>\n",
       "      <td>2011-07-28 10:34:00</td>\n",
       "      <td>23</td>\n",
       "      <td>B00456</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02108</td>\n",
       "      <td>1943.0</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>64</td>\n",
       "      <td>2011-07-28 10:35:00</td>\n",
       "      <td>23</td>\n",
       "      <td>2011-07-28 10:36:00</td>\n",
       "      <td>23</td>\n",
       "      <td>B00554</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02116</td>\n",
       "      <td>1981.0</td>\n",
       "      <td>Female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>12</td>\n",
       "      <td>2011-07-28 10:37:00</td>\n",
       "      <td>23</td>\n",
       "      <td>2011-07-28 10:37:00</td>\n",
       "      <td>23</td>\n",
       "      <td>B00554</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'97214</td>\n",
       "      <td>1983.0</td>\n",
       "      <td>Female</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id  duration           start_date   ...    zip_code birth_date  gender\n",
       "0   1         9  2011-07-28 10:12:00   ...      '97217     1976.0    Male\n",
       "1   2       220  2011-07-28 10:21:00   ...      '02215     1966.0    Male\n",
       "2   3        56  2011-07-28 10:33:00   ...      '02108     1943.0    Male\n",
       "3   4        64  2011-07-28 10:35:00   ...      '02116     1981.0  Female\n",
       "4   5        12  2011-07-28 10:37:00   ...      '97214     1983.0  Female\n",
       "\n",
       "[5 rows x 11 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df =pd.read_sql_query(\"SELECT * FROM trips LIMIT 5;\",conn) \n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "401aef972027c8bdb0a78e36897340d47ddd57e5"
   },
   "source": [
    "This query uses`*` as a wildcard instead of specifying columns to return. This means the `SELECT` command has given us every column in the trips table. We also used the`LIMIT` function to restrict the output to the first five rows of the table.\n",
    "\n",
    "The previous example returned every column in the trips table. If we were only interested in the duration and start_date columns, we could replace the wildcard with the column names as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "_kg_hide-input": true,
    "_uuid": "9e766066ae0d94262d82c485da86c77a02a1c303"
   },
   "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>duration</th>\n",
       "      <th>start_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9</td>\n",
       "      <td>2011-07-28 10:12:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>220</td>\n",
       "      <td>2011-07-28 10:21:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>56</td>\n",
       "      <td>2011-07-28 10:33:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>64</td>\n",
       "      <td>2011-07-28 10:35:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12</td>\n",
       "      <td>2011-07-28 10:37:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   duration           start_date\n",
       "0         9  2011-07-28 10:12:00\n",
       "1       220  2011-07-28 10:21:00\n",
       "2        56  2011-07-28 10:33:00\n",
       "3        64  2011-07-28 10:35:00\n",
       "4        12  2011-07-28 10:37:00"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=pd.read_sql_query(\"SELECT duration, start_date FROM trips LIMIT 5\",conn)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "00700a095a41f736f0bccb302852bfa196b0005d"
   },
   "source": [
    "# &#128225; 3. ORDER BY\n",
    "The final command we need to know before we can answer the first of our questions is `ORDER BY`. This command allows you to sort the database on a given column. To use it, you simply specify the name of the column you would like to sort on. By default, `ORDER BY` sorts in ascending order. If you would like to specify which order the database should be sorted, you can add the keyword `ASC` for ascending order or `DESC` for descending order. For example, if we wanted to sort the trips table from the shortest duration to the longest we could add the following line to our query:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "_kg_hide-input": true,
    "_uuid": "8d04e2c6ab963f4639f626f640f0dc67087165ed"
   },
   "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>duration</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569971</th>\n",
       "      <td>9974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569972</th>\n",
       "      <td>9974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569973</th>\n",
       "      <td>9976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569974</th>\n",
       "      <td>9978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569975</th>\n",
       "      <td>9980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569976</th>\n",
       "      <td>9980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569977</th>\n",
       "      <td>9981</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569978</th>\n",
       "      <td>9982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569979</th>\n",
       "      <td>9984</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569980</th>\n",
       "      <td>9985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569981</th>\n",
       "      <td>9985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569982</th>\n",
       "      <td>9986</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569983</th>\n",
       "      <td>9986</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569984</th>\n",
       "      <td>9987</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569985</th>\n",
       "      <td>9988</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569986</th>\n",
       "      <td>9989</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569987</th>\n",
       "      <td>9991</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569988</th>\n",
       "      <td>9992</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569989</th>\n",
       "      <td>9993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569990</th>\n",
       "      <td>9994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569991</th>\n",
       "      <td>9994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569992</th>\n",
       "      <td>9994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569993</th>\n",
       "      <td>9995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569994</th>\n",
       "      <td>9995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569995</th>\n",
       "      <td>9996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569996</th>\n",
       "      <td>9996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569997</th>\n",
       "      <td>9997</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569998</th>\n",
       "      <td>9998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1569999</th>\n",
       "      <td>9998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1570000</th>\n",
       "      <td>9999</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1570001 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         duration\n",
       "0               0\n",
       "1               0\n",
       "2               0\n",
       "3               0\n",
       "4               0\n",
       "5               0\n",
       "6               0\n",
       "7               0\n",
       "8               0\n",
       "9               0\n",
       "10              0\n",
       "11              0\n",
       "12              0\n",
       "13              0\n",
       "14              0\n",
       "15              0\n",
       "16              0\n",
       "17              0\n",
       "18              0\n",
       "19              0\n",
       "20              0\n",
       "21              0\n",
       "22              0\n",
       "23              0\n",
       "24              0\n",
       "25              0\n",
       "26              0\n",
       "27              0\n",
       "28              0\n",
       "29              0\n",
       "...           ...\n",
       "1569971      9974\n",
       "1569972      9974\n",
       "1569973      9976\n",
       "1569974      9978\n",
       "1569975      9980\n",
       "1569976      9980\n",
       "1569977      9981\n",
       "1569978      9982\n",
       "1569979      9984\n",
       "1569980      9985\n",
       "1569981      9985\n",
       "1569982      9986\n",
       "1569983      9986\n",
       "1569984      9987\n",
       "1569985      9988\n",
       "1569986      9989\n",
       "1569987      9991\n",
       "1569988      9992\n",
       "1569989      9993\n",
       "1569990      9994\n",
       "1569991      9994\n",
       "1569992      9994\n",
       "1569993      9995\n",
       "1569994      9995\n",
       "1569995      9996\n",
       "1569996      9996\n",
       "1569997      9997\n",
       "1569998      9998\n",
       "1569999      9998\n",
       "1570000      9999\n",
       "\n",
       "[1570001 rows x 1 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=pd.read_sql_query(\"SELECT duration FROM trips ORDER BY duration ASC\",conn)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "d32f60e766e33b01525b7bca9e46374e7bca19cc"
   },
   "source": [
    "**3.1** With the `SELECT`, `LIMIT` and `ORDER BY` commands in our repertoire, we can now attempt to answer our first question: **What was the duration of the longest trip?**\n",
    "\n",
    "To answer this question, it's helpful to break it down into sections and identify which commands we will need to address each part. First we need to pull the information from the duration column of the trips table. Then, to find which trip is the longest, we can sort the duration column in descending order. Here's how you might work this through to come up with a query that will get the information you're looking for:\n",
    "\n",
    "* Use `SELECT` to retrieve the duration column FROM the trips table.\n",
    "\n",
    "* Use `ORDER BY` to sort the duration column and use the `DESC` keyword to specify that you want to sort in descending order.\n",
    "\n",
    "* Use `LIMIT` to restrict the output to 1 row.\n",
    "\n",
    "Let's go ahead and run this query and find out how long the longest trip lasted."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "_kg_hide-input": true,
    "_uuid": "7859cfb1d30baebf7019959ada73cde1928626a2"
   },
   "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>duration</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9999</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   duration\n",
       "0      9999"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=pd.read_sql_query(\"SELECT duration FROM trips ORDER BY duration DESC LIMIT 1;\",conn)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "61b7d4f00cbf164e16a35ceba08375dcd1094d5f"
   },
   "source": [
    "Now we know that the longest trip lasted 9999 seconds, or a little over 166 minutes. With a maximum value of 9999, however, we don't know whether this is really the length of the longest trip or if the database was only set up to allow a four digit number.\n",
    "* Let's try running the same query as before, but adjust the `LIMIT` to return the 10 highest durations and see if that's the case:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "_kg_hide-input": true,
    "_uuid": "936b2b098397bf909a6f7f84e7ee0b3d892b36ec"
   },
   "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>duration</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9997</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>9996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>9995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>9995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9994</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   duration\n",
       "0      9999\n",
       "1      9998\n",
       "2      9998\n",
       "3      9997\n",
       "4      9996\n",
       "5      9996\n",
       "6      9995\n",
       "7      9995\n",
       "8      9994\n",
       "9      9994"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=pd.read_sql_query(\"SELECT duration FROM trips ORDER BY duration DESC LIMIT 10\",conn)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "c9bfc80b28a531c255210ca15c237dbda17a5ae4"
   },
   "source": [
    "### &#128681; Note:\n",
    "What we see here is that there aren't a whole bunch of trips at 9999, so it doesn't look like we're cutting off the top end of our durations, but it's still difficult to tell whether that's the real length of the trip or just the maximum allowed value. Hubway charges additional fees for rides over 30 minutes (somebody keeping a bike for 9999 seconds would have to pay an extra $25 in fees) so it's plausible that they decided 4 digits would be sufficient to track the majority of rides."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "dccab28f7e371c6fcdfa9931602c0dff85987aa4"
   },
   "source": [
    "# &#128640; 4. WHERE"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "31e4667a4b83ed26d28c3879cc2a8674fe4625d9"
   },
   "source": [
    "The previous commands are great for pulling out sorted information for particular columns, but what if you already have a specific subset of the data you want to look at? That's where `WHERE` comes in. The `WHERE` command allows you to use a logical operator to specify which rows should be returned. For example you could use the following command to return every trip taken with bike `B00400`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "_kg_hide-input": true,
    "_uuid": "c3813963c6ccaecec5199737f6518064fd26e531"
   },
   "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>bike_number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>B00400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B00400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>B00400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>B00400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>B00400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>B00400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>B00400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>B00400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>B00400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>B00400</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  bike_number\n",
       "0      B00400\n",
       "1      B00400\n",
       "2      B00400\n",
       "3      B00400\n",
       "4      B00400\n",
       "5      B00400\n",
       "6      B00400\n",
       "7      B00400\n",
       "8      B00400\n",
       "9      B00400"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\"SELECT bike_number from trips  WHERE bike_number = 'B00400' LIMIT 10 \",conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "8bd37613bb2889929abec161875dae914ae32f60"
   },
   "source": [
    "### &#128681; Note:\n",
    "You'll also notice that we use quote marks in this query. That's because the bike_number is stored as a string. If the column contained numeric data types the quote marks would not be necessary."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "35f091b227c824d81f8306a63e30f83a8e960d38"
   },
   "source": [
    "**4.1** Let's write a query that uses WHERE to return every column in the trips table for each row with a duration longer than 9990 seconds: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "_kg_hide-input": true,
    "_uuid": "1b5b3abcbc69fe6c6013024a17f36d3cfc19b608"
   },
   "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>id</th>\n",
       "      <th>duration</th>\n",
       "      <th>start_date</th>\n",
       "      <th>start_station</th>\n",
       "      <th>end_date</th>\n",
       "      <th>end_station</th>\n",
       "      <th>bike_number</th>\n",
       "      <th>sub_type</th>\n",
       "      <th>zip_code</th>\n",
       "      <th>birth_date</th>\n",
       "      <th>gender</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4768</td>\n",
       "      <td>9994</td>\n",
       "      <td>2011-08-03 17:16:00</td>\n",
       "      <td>22</td>\n",
       "      <td>2011-08-03 20:03:00</td>\n",
       "      <td>24</td>\n",
       "      <td>B00002</td>\n",
       "      <td>Casual</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8448</td>\n",
       "      <td>9991</td>\n",
       "      <td>2011-08-06 13:02:00</td>\n",
       "      <td>52</td>\n",
       "      <td>2011-08-06 15:48:00</td>\n",
       "      <td>24</td>\n",
       "      <td>B00174</td>\n",
       "      <td>Casual</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11341</td>\n",
       "      <td>9998</td>\n",
       "      <td>2011-08-09 10:42:00</td>\n",
       "      <td>40</td>\n",
       "      <td>2011-08-09 13:29:00</td>\n",
       "      <td>42</td>\n",
       "      <td>B00513</td>\n",
       "      <td>Casual</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>24455</td>\n",
       "      <td>9995</td>\n",
       "      <td>2011-08-20 12:20:00</td>\n",
       "      <td>52</td>\n",
       "      <td>2011-08-20 15:07:00</td>\n",
       "      <td>17</td>\n",
       "      <td>B00552</td>\n",
       "      <td>Casual</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>55771</td>\n",
       "      <td>9994</td>\n",
       "      <td>2011-09-14 15:44:00</td>\n",
       "      <td>40</td>\n",
       "      <td>2011-09-14 18:30:00</td>\n",
       "      <td>40</td>\n",
       "      <td>B00139</td>\n",
       "      <td>Casual</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>81191</td>\n",
       "      <td>9993</td>\n",
       "      <td>2011-10-03 11:30:00</td>\n",
       "      <td>22</td>\n",
       "      <td>2011-10-03 14:16:00</td>\n",
       "      <td>36</td>\n",
       "      <td>B00474</td>\n",
       "      <td>Casual</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>89335</td>\n",
       "      <td>9997</td>\n",
       "      <td>2011-10-09 02:30:00</td>\n",
       "      <td>60</td>\n",
       "      <td>2011-10-09 05:17:00</td>\n",
       "      <td>45</td>\n",
       "      <td>B00047</td>\n",
       "      <td>Casual</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>124500</td>\n",
       "      <td>9992</td>\n",
       "      <td>2011-11-09 09:08:00</td>\n",
       "      <td>22</td>\n",
       "      <td>2011-11-09 11:55:00</td>\n",
       "      <td>40</td>\n",
       "      <td>B00387</td>\n",
       "      <td>Casual</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>133967</td>\n",
       "      <td>9996</td>\n",
       "      <td>2011-11-19 13:48:00</td>\n",
       "      <td>4</td>\n",
       "      <td>2011-11-19 16:35:00</td>\n",
       "      <td>58</td>\n",
       "      <td>B00238</td>\n",
       "      <td>Casual</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>147451</td>\n",
       "      <td>9996</td>\n",
       "      <td>2012-03-23 14:48:00</td>\n",
       "      <td>35</td>\n",
       "      <td>2012-03-23 17:35:00</td>\n",
       "      <td>33</td>\n",
       "      <td>B00550</td>\n",
       "      <td>Casual</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>315737</td>\n",
       "      <td>9995</td>\n",
       "      <td>2012-07-03 18:28:00</td>\n",
       "      <td>12</td>\n",
       "      <td>2012-07-03 21:15:00</td>\n",
       "      <td>12</td>\n",
       "      <td>B00250</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02120</td>\n",
       "      <td>1964</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>319597</td>\n",
       "      <td>9994</td>\n",
       "      <td>2012-07-05 11:49:00</td>\n",
       "      <td>52</td>\n",
       "      <td>2012-07-05 14:35:00</td>\n",
       "      <td>55</td>\n",
       "      <td>B00237</td>\n",
       "      <td>Casual</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>416523</td>\n",
       "      <td>9998</td>\n",
       "      <td>2012-08-15 12:11:00</td>\n",
       "      <td>54</td>\n",
       "      <td>2012-08-15 14:58:00</td>\n",
       "      <td>80</td>\n",
       "      <td>B00188</td>\n",
       "      <td>Casual</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>541247</td>\n",
       "      <td>9999</td>\n",
       "      <td>2012-09-26 18:34:00</td>\n",
       "      <td>54</td>\n",
       "      <td>2012-09-26 21:21:00</td>\n",
       "      <td>54</td>\n",
       "      <td>T01078</td>\n",
       "      <td>Casual</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        id  duration           start_date   ...    zip_code birth_date  gender\n",
       "0     4768      9994  2011-08-03 17:16:00   ...                               \n",
       "1     8448      9991  2011-08-06 13:02:00   ...                               \n",
       "2    11341      9998  2011-08-09 10:42:00   ...                               \n",
       "3    24455      9995  2011-08-20 12:20:00   ...                               \n",
       "4    55771      9994  2011-09-14 15:44:00   ...                               \n",
       "5    81191      9993  2011-10-03 11:30:00   ...                               \n",
       "6    89335      9997  2011-10-09 02:30:00   ...                               \n",
       "7   124500      9992  2011-11-09 09:08:00   ...                               \n",
       "8   133967      9996  2011-11-19 13:48:00   ...                               \n",
       "9   147451      9996  2012-03-23 14:48:00   ...                               \n",
       "10  315737      9995  2012-07-03 18:28:00   ...      '02120       1964    Male\n",
       "11  319597      9994  2012-07-05 11:49:00   ...                               \n",
       "12  416523      9998  2012-08-15 12:11:00   ...                               \n",
       "13  541247      9999  2012-09-26 18:34:00   ...                               \n",
       "\n",
       "[14 rows x 11 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=pd.read_sql_query(\"SELECT * FROM trips WHERE duration > 9990;\",conn)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "396f8667689ca851e40895995990dcfcff5596c0"
   },
   "source": [
    "### &#128681; Note: \n",
    "As you can see, the query returned 14 different trips, each with a duration of 9990 seconds or more. Something that stands out about this query is that all but one of the results has a `sub_type` of `\"Casual\"`. Perhaps this is an indication that `\"Registered\"` users are more aware of the extra fees for long trips and maybe Hubway could do a better job of conveying their pricing structure to Casual users to help them avoid overage charges."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "934acb9a162b0b891cfacdcfc713a2d100bb7319"
   },
   "source": [
    "**4.2** We can also combine multiple logical tests in our `WHERE` clause using `AND` or `OR`. If, for example, in our previous query we had only wanted to return the trips with a duration over 9990 seconds that also had a `sub_type` of `Registered`, we could use `AND` to specify both conditions. I also recommend using parentheses to separate each logical test. This isn't strictly required for the code to function, but they can make your queries easier to understand as you increase the complexity. Let's run that query now. We already know it should only return one result, so it should be easy to check that we've got it right:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "_kg_hide-input": true,
    "_uuid": "30ea6809cf84624e7a4c088ea5853f286eafe4dc"
   },
   "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>id</th>\n",
       "      <th>duration</th>\n",
       "      <th>start_date</th>\n",
       "      <th>start_station</th>\n",
       "      <th>end_date</th>\n",
       "      <th>end_station</th>\n",
       "      <th>bike_number</th>\n",
       "      <th>sub_type</th>\n",
       "      <th>zip_code</th>\n",
       "      <th>birth_date</th>\n",
       "      <th>gender</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>315737</td>\n",
       "      <td>9995</td>\n",
       "      <td>2012-07-03 18:28:00</td>\n",
       "      <td>12</td>\n",
       "      <td>2012-07-03 21:15:00</td>\n",
       "      <td>12</td>\n",
       "      <td>B00250</td>\n",
       "      <td>Registered</td>\n",
       "      <td>'02120</td>\n",
       "      <td>1964.0</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       id  duration           start_date   ...    zip_code birth_date  gender\n",
       "0  315737      9995  2012-07-03 18:28:00   ...      '02120     1964.0    Male\n",
       "\n",
       "[1 rows x 11 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=pd.read_sql_query(\"SELECT * FROM trips WHERE (duration >= 9990) AND (sub_type = 'Registered')ORDER BY duration DESC;\",conn)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "6302ffda7e62f40eec334fc7ab3762f3aafe52a3"
   },
   "source": [
    "# &#128207; 5. COUNT\n",
    "The next question we set out at the beginning of the notebook is **\"How many trips were taken by `'registered'` users?\" ** To answer it, we could run the same query as above and modify the `WHERE` expression to return all of the rows where `sub_type` is equal to `'Registered'` and then count them up. You could count them up on the screen, however, SQL actually has a built in command to do the work for us, `COUNT`.\n",
    "\n",
    "This allows us to shift the calculation to the database and save us the trouble of writing additional scripts to count up results. To use it, you simply include `COUNT(column_name)` instead of (or in addition to) the columns you want to `SELECT`, like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "_uuid": "4efd1b44f88fb64ddfd6f81163a0ca3cc9df0ee5"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>COUNT(id)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1570001</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   COUNT(id)\n",
       "0    1570001"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\"SELECT COUNT(id) FROM trips \",conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "781b7640ce52a3e88225d50942b1e2599e490564"
   },
   "source": [
    "### &#128681; Note: \n",
    "In this instance, it doesn't matter which column we choose to count because every column should have data for each row in our query, but sometimes a query might have missing (or \"null\") values for some rows. If you're not sure whether a column contains null values you can run your `COUNT` on the id column - the id column is never null, so you can be sure your count won't have missed anything. You can also use `COUNT(1)` or `COUNT(*)` to count up every row in your query."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "5f26bac2c8a72f5c00903de19d01a99205bbdf77"
   },
   "source": [
    "Let's take a look at a query to answer our question. We can use `SELECT COUNT(*)` to count up the total number of columns returned and `WHERE` `sub_type` = \"Registered\" to make sure we only count up the trips taken by Registered users."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "_kg_hide-input": true,
    "_uuid": "260c49c296fd1402b804b94c90d4abd1636aaadb"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>COUNT(*)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1105192</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   COUNT(*)\n",
       "0   1105192"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=pd.read_sql_query(\"SELECT COUNT(*) FROM trips WHERE sub_type = 'Registered';\",conn)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "d044088fb876f35899bcc59783d10a684e777857"
   },
   "source": [
    "This query worked and has returned the answer to our question, but the column heading isn't particularly descriptive. If you want to make your results more readable, you can use AS to give your output an alias (or nickname). If we wanted to re-run the previous query but give our column heading an alias of Total Trips by Registered Users, we could do the following:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "_uuid": "2ab77972e35c447089101fbda55b59c2acf2aa5c"
   },
   "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>Total Trips by Registered Users</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1105192</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Total Trips by Registered Users\n",
       "0                          1105192"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=pd.read_sql_query(' ' ' SELECT COUNT(*) AS \"Total Trips by Registered Users\" FROM trips WHERE sub_type = \"Registered\";' ' ' ,conn)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "6e4a2cdc481b2248f98f48185f702bfe48b9f641"
   },
   "source": [
    "# &#128223; 5. Aggregate Functions(SUM,AVG,MIN,MAX)\n",
    "`COUNT` is not the only trick SQL has in this domain. You can also use `SUM, AVG, MIN` and `MAX` to return the sum, average, minimum and maximum of a column respectively. These, along with `COUNT`, are known as aggregate functions. So to answer our third question,** \"What was the average trip duration?\"**, we can use the `AVG` function on the duration column (and, once again, use AS to give our output column a more descriptive name):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "_kg_hide-input": true,
    "_uuid": "b98c0cf330c5dc40e174938bd7b927cc604317df"
   },
   "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>Average Duration</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>912.409682</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Average Duration\n",
       "0        912.409682"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=pd.read_sql_query(' ' ' SELECT AVG(duration) AS \"Average Duration\" FROM trips;  ' ' ' ,conn )\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "e95e2c4e6563eb7f08cc76ad228c35c68ee7076e"
   },
   "source": [
    "### &#128681; Note:\n",
    "So it turns out that the average trip duration is 912 seconds, which is about 15 minutes. This makes some sense since we know that Hubway charges extra fees for trips over 30 minutes because the service is designed for riders to take short, one-way trips."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "69d4bebfbe3648b047f82d11d5335f239d89b882"
   },
   "source": [
    "But what about our next question, do registered or casual users take longer trips? We already know one way to answer this question - we can run two `SELECT AVG(duration)`` FROM` trips queries with `WHERE `clauses that restrict one to \"Registered\" and one to \"Casual\" users. SQL also includes a way that we can answer this question in a single query, using the GROUP BY command."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "9b434192a44af4211b9f6fc650ea26055f80d957"
   },
   "source": [
    "# &#128218; 6.GROUP BY \n",
    "`GROUP` BY separates the rows into groups based on the contents of a particular column and allows us to perform aggregate functions on each group. To get a better idea of how this works, let's take a look at the gender column. Each row can have one of three possible values in the gender column, \"Male\", \"Female\" or Null (missing - we don't have gender data for casual users). When we use GROUP BY, the database will separate out each of the rows into a different group based on the value in the gender column, in much the same way that you might separate a deck of cards into different suits. You can imagine making two piles, one of all the males, one of all the females.\n",
    "\n",
    "So let's walk through exactly how to write a query to answer our question of whether registered or casual users take longer trips.\n",
    "\n",
    "* As with each of our queries so far, we'll start with `SELECT` to tell the database which information we want to see. In this instance, we'll want `sub_type` and `AVG(duration)`.\n",
    "\n",
    "* We'll also include `GROUP BY` sub_type to separate out our data by subscription type and calculate the averages of registered and casual users separately.\n",
    "\n",
    "Here's what the code looks like when you put it all together:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "_kg_hide-input": true,
    "_uuid": "ba93fa7b502a3c43c7531df2c46925742565f4fb"
   },
   "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>sub_type</th>\n",
       "      <th>Average Duration</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Casual</td>\n",
       "      <td>1519.643897</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Registered</td>\n",
       "      <td>657.026067</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     sub_type  Average Duration\n",
       "0      Casual       1519.643897\n",
       "1  Registered        657.026067"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=pd.read_sql_query(' ' '  SELECT sub_type, AVG(duration) AS \"Average Duration\" FROM trips GROUP BY sub_type; ' ' ',conn)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "d8e2dae52b4708dae865066b8a4c15b6e9b25e1a"
   },
   "source": [
    "Our next question was** which bike was used for the most trips? **and we can answer this using a very similar query. Take a look at the following example and see if you can figure out what each line is doing - we'll go through it step by step afterwards so you can check you got it right:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "_kg_hide-input": true,
    "_uuid": "949ed59965188922fd07058369ff82d0781bf7f8"
   },
   "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>Bike Number</th>\n",
       "      <th>Number of Trips</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>B00490</td>\n",
       "      <td>2120</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Bike Number  Number of Trips\n",
       "0      B00490             2120"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=pd.read_sql_query(' ' ' SELECT bike_number as \"Bike Number\", COUNT(*) AS \"Number of Trips\" FROM trips GROUP BY bike_number ORDER BY COUNT(*) DESC LIMIT 1; ' ' ',conn)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "54e3469a1f13c2757dcadf06c216350778ac8888"
   },
   "source": [
    "### &#128681; Note:\n",
    "As you can see from the output, bike B00490 took the most trips. Let's run through how we got there:\n",
    "\n",
    "* The first line is a SELECT clause to tell the database we want to see the bike_number column and a count of every row. It also uses AS to tell the database to display each column with a more useful name.\n",
    "* The second line uses FROM to specify that the data we're looking for is in the trips table.\n",
    "* The third line is where things start to get a little tricky. We use GROUP BY to tell the COUNT function on line 1 to count up each value for bike_number separately.\n",
    "* On line four we have an ORDER BY clause to sort the table in descending order and make sure our most-used bike is at the top.\n",
    "* Finally we use LIMIT to restrict the output to the first row, which we know will be the bike that was used in the highest number of trips because of how we sorted the data on line four."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "26ec17bfb880eeb14e7f396bff07a0f83d08f921"
   },
   "source": [
    "# &#128291; 7. Arithmetic Operators\n",
    "The last of our questions about this table is a little more tricky than the others. We want to know the **average duration of trips by registered members over the age of 30**. We could just figure out the year in which 30 year olds were born in our heads and then plug it in, but a more elegant solution is to use arithmetic operations directly within our query. SQL allows us to use +, -, * and / to perform an arithmetic operation on an entire column at once."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "_kg_hide-input": true,
    "_uuid": "15e82163f150a109bbd7d06816190138de5a28df"
   },
   "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>AVG(duration)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>923.014685</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   AVG(duration)\n",
       "0     923.014685"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=pd.read_sql_query(' ' ' SELECT AVG(duration) FROM trips WHERE (2017 - birth_date) > 30; ' ' ',conn )\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "72fc7077009d61880182a6ddc7e678a72ed0bc27"
   },
   "source": [
    "# &#128279;  8. JOIN\n",
    "So far we've been looking at queries that only pull data from the trips table, but you'll remember from the introduction that this database contains a second table, stations. The stations table contains information about every station in the Hubway network and includes an id column that is referenced by the trips table.\n",
    "![Imgur](https://i.imgur.com/Nehnebq.png)\n",
    "Before we start to work through some real examples on our dataset, let's take a moment to look back at the hypothetical order tracking database from earlier. In that database we had two tables, `orders` and `customers`, and they were connected by the `customer_id` column. Let's say we wanted to write a query that returned the `order_number` and `name` for every order in the database. If they were both stored in the same table, we could use the following query:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "ff416c22ab8c884c7175baaec8063af147808c97"
   },
   "source": [
    "**```SELECT order_number, name FROM orders;```**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "92f25ed3e27da62025b36614e95a7937c4f422b5"
   },
   "source": [
    "### &#128681; Note:\n",
    "Unfortunately order_number column and the name column are stored in two different tables, so we have to add a few extra steps. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "4df025eb881983f50d7c400cc1e97ac3fbac6479"
   },
   "source": [
    "* Let's take a moment to think through the additional things the database will need to know before it can return the information we want:\n",
    "\n",
    "* Which table is the order_number column in?\n",
    "\n",
    "* Which table is the name column in?\n",
    "\n",
    "* How is the information in the orders table connected to the information in the customers table?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "91ba2876e8bf0bb0e66e21758b564f74470961e4"
   },
   "source": [
    "To answer the first two of these questions, we can include the table names for each column in our `SELECT` command. The way we do this is simply to write the table name and column name separated by a .. For example, instead of `SELECT` `order_number`, name we would write `SELECT` orders.order_number, customers.name. Adding the table names here helps the database to find the columns we're looking for by telling it which table to look in for each.\n",
    "\n",
    "To tell the database how the orders and customers tables are connected, we use `JOIN` and `ON`. `JOIN` specifies which tables should be connected and ON specifies which columns in each table are related. We're going to use an inner join, which means that rows will only be returned where there is a match in our columns specified in `ON`. In this example, we will want to use `JOIN` on whichever table we didn't include in the `FROM` command. So we can either use `FROM` orders `INNER JOIN` customers or `FROM` customers `INNER JOIN` orders. As we discussed earlier, these tables are connected on the `customer_id` column in each table. Therefore, we will want to use `ON` to tell the database that these two columns refer to the same thing like this:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "6cfde38fa3321afdc4c95ef82458f6c1af2887d2"
   },
   "source": [
    "**```ON orders.customer_ID = customers.customer_id```**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "c9ffeff67841fa898349482f2d1a45405126a95b"
   },
   "source": [
    "Once again we use the . to make sure the database knows which table each of these columns is in. So when we put all of this together, we get a query that looks like this:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "bc9d175fc10d1e21f2b649703ac88173b2072471"
   },
   "source": [
    "**```SELECT orders.order_number, customers.name```**\n",
    "\n",
    "**```FROM orders```**\n",
    "\n",
    "**```INNER JOIN customers```**\n",
    "\n",
    "**```ON orders.customer_id = customers.customer_id```**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "f9b81ea0eaf4191dbe2667d5df8fdddaec928f04"
   },
   "source": [
    "This query will return the order number of every order in the database along with the customer name that is associated with each.\n",
    "\n",
    "Moving back to our Hubway database we can now write some queries to see `JOIN` in action, but before we get started we should take a look at the rest of the columns in the stations table. Here's a query to show us the first 5 rows so we can see what the stations table looks like:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "_kg_hide-input": true,
    "_uuid": "efa2bb3824aeb7d8b94e6c300e80bea5c9dd77a7"
   },
   "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>id</th>\n",
       "      <th>station</th>\n",
       "      <th>municipality</th>\n",
       "      <th>lat</th>\n",
       "      <th>lng</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td>Colleges of the Fenway</td>\n",
       "      <td>Boston</td>\n",
       "      <td>42.340021</td>\n",
       "      <td>-71.100812</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>Tremont St. at Berkeley St.</td>\n",
       "      <td>Boston</td>\n",
       "      <td>42.345392</td>\n",
       "      <td>-71.069616</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5</td>\n",
       "      <td>Northeastern U / North Parking Lot</td>\n",
       "      <td>Boston</td>\n",
       "      <td>42.341814</td>\n",
       "      <td>-71.090179</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6</td>\n",
       "      <td>Cambridge St. at Joy St.</td>\n",
       "      <td>Boston</td>\n",
       "      <td>42.361284999999995</td>\n",
       "      <td>-71.06514</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7</td>\n",
       "      <td>Fan Pier</td>\n",
       "      <td>Boston</td>\n",
       "      <td>42.353412</td>\n",
       "      <td>-71.044624</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id     ...             lng\n",
       "0   3     ...      -71.100812\n",
       "1   4     ...      -71.069616\n",
       "2   5     ...      -71.090179\n",
       "3   6     ...       -71.06514\n",
       "4   7     ...      -71.044624\n",
       "\n",
       "[5 rows x 5 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=pd.read_sql_query(' ' ' SELECT * FROM stations LIMIT 5; ' ' ',conn)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "b037478bddb2b7ddae82b343ee3c0112c27f9882"
   },
   "source": [
    "\n",
    "* `id` — A unique identifier for each station (corresponds to the start_station and end_station columns in the trips table)\n",
    "\n",
    "* `station` — The station name\n",
    "\n",
    "* `municipality` — The municipality that the station is in (Boston, Brookline, Cambridge or Somerville)\n",
    "\n",
    "* `lat` — The latitude of the station\n",
    "\n",
    "* `lng` — The longitude of the station\n",
    "\n",
    "* Which stations are most frequently used for round trips?\n",
    "\n",
    "* How many trips start and end in different municipalities?\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "438ebed6cedf61fd3c95d3b1a2ac0e5040ba627e"
   },
   "source": [
    "I'll try to answer some questions in the data, starting with ** which station is the most frequent starting point?** Let's work through it step by step:\n",
    "\n",
    "* First we want to use SELECT to return the station column from the stations table and the COUNT of the number of rows.\n",
    "* Next we specify the tables we want to JOIN and tell the database to connect them ON the start_station column in the trips table and the id column in the stations table.\n",
    "* Then we get into the meat of our query - we GROUP BY the station column in the stations table so that our COUNT will count up the number of trips for each station separately\n",
    "Finally we can ORDER BY our COUNT and LIMIT the output to a manageable number of results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "_kg_hide-input": true,
    "_uuid": "49e5060968e30466959901ad7ccf15dd4ec62012"
   },
   "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>Station</th>\n",
       "      <th>Count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>South Station - 700 Atlantic Ave.</td>\n",
       "      <td>56123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Boston Public Library - 700 Boylston St.</td>\n",
       "      <td>41994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Charles Circle - Charles St. at Cambridge St.</td>\n",
       "      <td>35984</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Beacon St / Mass Ave</td>\n",
       "      <td>35275</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MIT at Mass Ave / Amherst St</td>\n",
       "      <td>33644</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         Station  Count\n",
       "0              South Station - 700 Atlantic Ave.  56123\n",
       "1       Boston Public Library - 700 Boylston St.  41994\n",
       "2  Charles Circle - Charles St. at Cambridge St.  35984\n",
       "3                           Beacon St / Mass Ave  35275\n",
       "4                   MIT at Mass Ave / Amherst St  33644"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=pd.read_sql_query(' ' '  SELECT stations.station AS \"Station\", COUNT(*) AS \"Count\" FROM trips  INNER JOIN stations ON trips.start_station = stations.id GROUP BY stations.station ORDER BY COUNT(*) DESC LIMIT 5; ' ' ',conn)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "95d81fc102eead4832c5e6849461530c0498e758"
   },
   "source": [
    "### &#128681; Note:\n",
    "If you're familiar with Boston, you'll understand why these are the most popular stations. South Station is one of the main commuter rail stations in the city, Charles Street runs along the river close to some nice scenic routes and Boylston and Beacon streets are right downtown near a number of office buildings."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "5825458189adb13921f4f70365aed33266f25519"
   },
   "source": [
    "The next question we'll look at is **which stations are most frequently used for round trips?** We can use much the same query as before. We will `SELECT` the same output columns and `JOIN` the tables in the same way, but this time we'll add a `WHERE` clause to restrict our `COUNT` to trips where the start_station is the same as the end_station."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "_kg_hide-input": true,
    "_uuid": "7ea1765d5672f90016ff71eb61e1390030a1b720"
   },
   "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>Station</th>\n",
       "      <th>Count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>The Esplanade - Beacon St. at Arlington St.</td>\n",
       "      <td>3064</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Charles Circle - Charles St. at Cambridge St.</td>\n",
       "      <td>2739</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Boston Public Library - 700 Boylston St.</td>\n",
       "      <td>2548</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Boylston St. at Arlington St.</td>\n",
       "      <td>2163</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Beacon St / Mass Ave</td>\n",
       "      <td>2144</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         Station  Count\n",
       "0    The Esplanade - Beacon St. at Arlington St.   3064\n",
       "1  Charles Circle - Charles St. at Cambridge St.   2739\n",
       "2       Boston Public Library - 700 Boylston St.   2548\n",
       "3                  Boylston St. at Arlington St.   2163\n",
       "4                           Beacon St / Mass Ave   2144"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=pd.read_sql_query(' ' ' SELECT stations.station AS \"Station\", COUNT(*) AS \"Count\" FROM trips  INNER JOIN stations ON trips.start_station = stations.id WHERE trips.start_station = trips.end_station GROUP BY stations.station ORDER BY COUNT(*) DESC LIMIT 5; ' ' ',conn)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "10132f2fa93b22112bbe86632260a6b7371d0f67"
   },
   "source": [
    "Finally,** how many trips start and end in different municipalities?** This question takes things a step further. We want to know how many trips start and end in a different municipality. To achieve this, we need to `JOIN` the trips table to the stations table twice. Once `ON` the start_station column and then `ON` the end_station column. In order to do this, we have to create an alias for the stations table so that we are able to differentiate between data that relates to the `start_station` and data that relates to the `end_station`. We do this in exactly the same way we've been creating aliases for individual columns to make them display with a more intuitive name, using `AS`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "25b0fca90f6b8c9516e5178ceed31a6fad2f5406"
   },
   "source": [
    "For example we can use the following code to `JOIN` the stations table to the trips table using an alias of 'start'. We can then combine 'start' with our column names using . to refer to data that comes from this specific `JOIN` (rather than the second `JOIN` we will do `ON` the `end_station` column):"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "01d59cb762e1480796e0281485c79a3636bdf442"
   },
   "source": [
    " **INNER JOIN stations AS start ON trips.start_station = start.id**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "f139ed20dd1b942c53bd0436635626765d4b4c1b"
   },
   "source": [
    "Here's what the final query will look like when we run it:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "_kg_hide-input": true,
    "_uuid": "35d92cb5f381a8bca2ba313991cd92d5e2c34f57"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>309748</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Count\n",
       "0  309748"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=pd.read_sql_query(' ' '  SELECT COUNT(trips.id) AS \"Count\" FROM trips INNER JOIN stations AS start ON trips.start_station = start.id INNER JOIN stations AS end ON trips.end_station = end.id WHERE start.municipality <> end.municipality;' ' ',conn)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "9c7f727c5cd2eb9f1bccd5737387ebac32840a00"
   },
   "source": [
    "### &#128681; Note:\n",
    "This shows that about 300k out of 1.5 million trips (or 20%) ended in a different municipality than they started in - further evidence that people mostly use Hubway bicycles for relatively short journeys rather than longer trips between towns."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "c1a371b5313d21131ca97b23393631fb983b21f4"
   },
   "source": [
    "# Motivation \n",
    "# \"work HARD , dream BIG , never GIVE UP\" &#128170; "
   ]
  }
 ],
 "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.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}