jupyter-labs-eda-sql-coursera_sqllite.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style=\"text-align:center\">\n",
    "    <a href=\"https://skills.network\" target=\"_blank\">\n",
    "    <img src=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/assets/logos/SN_web_lightmode.png\" width=\"200\" alt=\"Skills Network Logo\">\n",
    "    </a>\n",
    "</p>\n",
    "\n",
    "<h1 align=center><font size = 5>Assignment: SQL Notebook for Peer Assignment</font></h1>\n",
    "\n",
    "Estimated time needed: **60** minutes.\n",
    "\n",
    "## Introduction\n",
    "Using this Python notebook you will:\n",
    "\n",
    "1.  Understand the Spacex DataSet\n",
    "2.  Load the dataset  into the corresponding table in a Db2 database\n",
    "3.  Execute SQL queries to answer assignment questions \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Overview of the DataSet\n",
    "\n",
    "SpaceX has gained worldwide attention for a series of historic milestones. \n",
    "\n",
    "It is the only private company ever to return a spacecraft from low-earth orbit, which it first accomplished in December 2010.\n",
    "SpaceX advertises Falcon 9 rocket launches on its website with a cost of 62 million dollars wheras other providers cost upward of 165 million dollars each, much of the savings is because Space X can reuse the first stage. \n",
    "\n",
    "\n",
    "Therefore if we can determine if the first stage will land, we can determine the cost of a launch. \n",
    "\n",
    "This information can be used if an alternate company wants to bid against SpaceX for a rocket launch.\n",
    "\n",
    "This dataset includes a record for each payload carried during a SpaceX mission into outer space.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Download the datasets\n",
    "\n",
    "This assignment requires you to load the spacex dataset.\n",
    "\n",
    "In many cases the dataset to be analyzed is available as a .CSV (comma separated values) file, perhaps on the internet. Click on the link below to download and save the dataset (.CSV file):\n",
    "\n",
    " <a href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/module_2/data/Spacex.csv\" target=\"_blank\">Spacex DataSet</a>\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting sqlalchemy==1.3.9\n",
      "  Using cached SQLAlchemy-1.3.9-cp311-cp311-linux_x86_64.whl\n",
      "Installing collected packages: sqlalchemy\n",
      "  Attempting uninstall: sqlalchemy\n",
      "    Found existing installation: SQLAlchemy 2.0.32\n",
      "    Uninstalling SQLAlchemy-2.0.32:\n",
      "      Successfully uninstalled SQLAlchemy-2.0.32\n",
      "\u001b[31mERROR: pip's dependency resolver does not currently take into account all the packages that are installed. This behaviour is the source of the following dependency conflicts.\n",
      "ipython-sql 0.5.0 requires sqlalchemy>=2.0, but you have sqlalchemy 1.3.9 which is incompatible.\n",
      "jupyterhub 4.1.5 requires SQLAlchemy>=1.4, but you have sqlalchemy 1.3.9 which is incompatible.\u001b[0m\u001b[31m\n",
      "\u001b[0mSuccessfully installed sqlalchemy-1.3.9\n"
     ]
    }
   ],
   "source": [
    "!pip install sqlalchemy==1.3.9\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Connect to the database\n",
    "\n",
    "Let us first load the SQL extension and establish a connection with the database\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: ipython-sql in /opt/conda/lib/python3.11/site-packages (0.5.0)\n",
      "Requirement already satisfied: prettytable in /opt/conda/lib/python3.11/site-packages (from ipython-sql) (3.11.0)\n",
      "Requirement already satisfied: ipython in /opt/conda/lib/python3.11/site-packages (from ipython-sql) (8.22.2)\n",
      "Collecting sqlalchemy>=2.0 (from ipython-sql)\n",
      "  Using cached SQLAlchemy-2.0.32-cp311-cp311-manylinux_2_17_x86_64.manylinux2014_x86_64.whl.metadata (9.6 kB)\n",
      "Requirement already satisfied: sqlparse in /opt/conda/lib/python3.11/site-packages (from ipython-sql) (0.5.1)\n",
      "Requirement already satisfied: six in /opt/conda/lib/python3.11/site-packages (from ipython-sql) (1.16.0)\n",
      "Requirement already satisfied: ipython-genutils in /opt/conda/lib/python3.11/site-packages (from ipython-sql) (0.2.0)\n",
      "Requirement already satisfied: typing-extensions>=4.6.0 in /opt/conda/lib/python3.11/site-packages (from sqlalchemy>=2.0->ipython-sql) (4.11.0)\n",
      "Requirement already satisfied: greenlet!=0.4.17 in /opt/conda/lib/python3.11/site-packages (from sqlalchemy>=2.0->ipython-sql) (3.0.3)\n",
      "Requirement already satisfied: decorator in /opt/conda/lib/python3.11/site-packages (from ipython->ipython-sql) (5.1.1)\n",
      "Requirement already satisfied: jedi>=0.16 in /opt/conda/lib/python3.11/site-packages (from ipython->ipython-sql) (0.19.1)\n",
      "Requirement already satisfied: matplotlib-inline in /opt/conda/lib/python3.11/site-packages (from ipython->ipython-sql) (0.1.7)\n",
      "Requirement already satisfied: prompt-toolkit<3.1.0,>=3.0.41 in /opt/conda/lib/python3.11/site-packages (from ipython->ipython-sql) (3.0.42)\n",
      "Requirement already satisfied: pygments>=2.4.0 in /opt/conda/lib/python3.11/site-packages (from ipython->ipython-sql) (2.18.0)\n",
      "Requirement already satisfied: stack-data in /opt/conda/lib/python3.11/site-packages (from ipython->ipython-sql) (0.6.2)\n",
      "Requirement already satisfied: traitlets>=5.13.0 in /opt/conda/lib/python3.11/site-packages (from ipython->ipython-sql) (5.14.3)\n",
      "Requirement already satisfied: pexpect>4.3 in /opt/conda/lib/python3.11/site-packages (from ipython->ipython-sql) (4.9.0)\n",
      "Requirement already satisfied: wcwidth in /opt/conda/lib/python3.11/site-packages (from prettytable->ipython-sql) (0.2.13)\n",
      "Requirement already satisfied: parso<0.9.0,>=0.8.3 in /opt/conda/lib/python3.11/site-packages (from jedi>=0.16->ipython->ipython-sql) (0.8.4)\n",
      "Requirement already satisfied: ptyprocess>=0.5 in /opt/conda/lib/python3.11/site-packages (from pexpect>4.3->ipython->ipython-sql) (0.7.0)\n",
      "Requirement already satisfied: executing>=1.2.0 in /opt/conda/lib/python3.11/site-packages (from stack-data->ipython->ipython-sql) (2.0.1)\n",
      "Requirement already satisfied: asttokens>=2.1.0 in /opt/conda/lib/python3.11/site-packages (from stack-data->ipython->ipython-sql) (2.4.1)\n",
      "Requirement already satisfied: pure-eval in /opt/conda/lib/python3.11/site-packages (from stack-data->ipython->ipython-sql) (0.2.2)\n",
      "Using cached SQLAlchemy-2.0.32-cp311-cp311-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (3.2 MB)\n",
      "Installing collected packages: sqlalchemy\n",
      "  Attempting uninstall: sqlalchemy\n",
      "    Found existing installation: SQLAlchemy 1.3.9\n",
      "    Uninstalling SQLAlchemy-1.3.9:\n",
      "      Successfully uninstalled SQLAlchemy-1.3.9\n",
      "Successfully installed sqlalchemy-2.0.32\n"
     ]
    }
   ],
   "source": [
    "!pip install ipython-sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The sql extension is already loaded. To reload it, use:\n",
      "  %reload_ext sql\n"
     ]
    }
   ],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv, sqlite3\n",
    "\n",
    "con = sqlite3.connect(\"my_data1.db\")\n",
    "cur = con.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip install -q pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql sqlite:///my_data1.db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "101"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_csv(\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/module_2/data/Spacex.csv\")\n",
    "df.to_sql(\"SPACEXTBL\", con, if_exists='replace', index=False,method=\"multi\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>Time (UTC)</th>\n",
       "      <th>Booster_Version</th>\n",
       "      <th>Launch_Site</th>\n",
       "      <th>Payload</th>\n",
       "      <th>PAYLOAD_MASS__KG_</th>\n",
       "      <th>Orbit</th>\n",
       "      <th>Customer</th>\n",
       "      <th>Mission_Outcome</th>\n",
       "      <th>Landing_Outcome</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2010-06-04</td>\n",
       "      <td>18:45:00</td>\n",
       "      <td>F9 v1.0  B0003</td>\n",
       "      <td>CCAFS LC-40</td>\n",
       "      <td>Dragon Spacecraft Qualification Unit</td>\n",
       "      <td>0</td>\n",
       "      <td>LEO</td>\n",
       "      <td>SpaceX</td>\n",
       "      <td>Success</td>\n",
       "      <td>Failure (parachute)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2010-12-08</td>\n",
       "      <td>15:43:00</td>\n",
       "      <td>F9 v1.0  B0004</td>\n",
       "      <td>CCAFS LC-40</td>\n",
       "      <td>Dragon demo flight C1, two CubeSats, barrel of...</td>\n",
       "      <td>0</td>\n",
       "      <td>LEO (ISS)</td>\n",
       "      <td>NASA (COTS) NRO</td>\n",
       "      <td>Success</td>\n",
       "      <td>Failure (parachute)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2012-05-22</td>\n",
       "      <td>7:44:00</td>\n",
       "      <td>F9 v1.0  B0005</td>\n",
       "      <td>CCAFS LC-40</td>\n",
       "      <td>Dragon demo flight C2</td>\n",
       "      <td>525</td>\n",
       "      <td>LEO (ISS)</td>\n",
       "      <td>NASA (COTS)</td>\n",
       "      <td>Success</td>\n",
       "      <td>No attempt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2012-10-08</td>\n",
       "      <td>0:35:00</td>\n",
       "      <td>F9 v1.0  B0006</td>\n",
       "      <td>CCAFS LC-40</td>\n",
       "      <td>SpaceX CRS-1</td>\n",
       "      <td>500</td>\n",
       "      <td>LEO (ISS)</td>\n",
       "      <td>NASA (CRS)</td>\n",
       "      <td>Success</td>\n",
       "      <td>No attempt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2013-03-01</td>\n",
       "      <td>15:10:00</td>\n",
       "      <td>F9 v1.0  B0007</td>\n",
       "      <td>CCAFS LC-40</td>\n",
       "      <td>SpaceX CRS-2</td>\n",
       "      <td>677</td>\n",
       "      <td>LEO (ISS)</td>\n",
       "      <td>NASA (CRS)</td>\n",
       "      <td>Success</td>\n",
       "      <td>No attempt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2013-09-29</td>\n",
       "      <td>16:00:00</td>\n",
       "      <td>F9 v1.1  B1003</td>\n",
       "      <td>VAFB SLC-4E</td>\n",
       "      <td>CASSIOPE</td>\n",
       "      <td>500</td>\n",
       "      <td>Polar LEO</td>\n",
       "      <td>MDA</td>\n",
       "      <td>Success</td>\n",
       "      <td>Uncontrolled (ocean)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2013-12-03</td>\n",
       "      <td>22:41:00</td>\n",
       "      <td>F9 v1.1</td>\n",
       "      <td>CCAFS LC-40</td>\n",
       "      <td>SES-8</td>\n",
       "      <td>3170</td>\n",
       "      <td>GTO</td>\n",
       "      <td>SES</td>\n",
       "      <td>Success</td>\n",
       "      <td>No attempt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2014-01-06</td>\n",
       "      <td>22:06:00</td>\n",
       "      <td>F9 v1.1</td>\n",
       "      <td>CCAFS LC-40</td>\n",
       "      <td>Thaicom 6</td>\n",
       "      <td>3325</td>\n",
       "      <td>GTO</td>\n",
       "      <td>Thaicom</td>\n",
       "      <td>Success</td>\n",
       "      <td>No attempt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2014-04-18</td>\n",
       "      <td>19:25:00</td>\n",
       "      <td>F9 v1.1</td>\n",
       "      <td>CCAFS LC-40</td>\n",
       "      <td>SpaceX CRS-3</td>\n",
       "      <td>2296</td>\n",
       "      <td>LEO (ISS)</td>\n",
       "      <td>NASA (CRS)</td>\n",
       "      <td>Success</td>\n",
       "      <td>Controlled (ocean)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2014-07-14</td>\n",
       "      <td>15:15:00</td>\n",
       "      <td>F9 v1.1</td>\n",
       "      <td>CCAFS LC-40</td>\n",
       "      <td>OG2 Mission 1  6 Orbcomm-OG2 satellites</td>\n",
       "      <td>1316</td>\n",
       "      <td>LEO</td>\n",
       "      <td>Orbcomm</td>\n",
       "      <td>Success</td>\n",
       "      <td>Controlled (ocean)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2014-08-05</td>\n",
       "      <td>8:00:00</td>\n",
       "      <td>F9 v1.1</td>\n",
       "      <td>CCAFS LC-40</td>\n",
       "      <td>AsiaSat 8</td>\n",
       "      <td>4535</td>\n",
       "      <td>GTO</td>\n",
       "      <td>AsiaSat</td>\n",
       "      <td>Success</td>\n",
       "      <td>No attempt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2014-09-07</td>\n",
       "      <td>5:00:00</td>\n",
       "      <td>F9 v1.1 B1011</td>\n",
       "      <td>CCAFS LC-40</td>\n",
       "      <td>AsiaSat 6</td>\n",
       "      <td>4428</td>\n",
       "      <td>GTO</td>\n",
       "      <td>AsiaSat</td>\n",
       "      <td>Success</td>\n",
       "      <td>No attempt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2014-09-21</td>\n",
       "      <td>5:52:00</td>\n",
       "      <td>F9 v1.1 B1010</td>\n",
       "      <td>CCAFS LC-40</td>\n",
       "      <td>SpaceX CRS-4</td>\n",
       "      <td>2216</td>\n",
       "      <td>LEO (ISS)</td>\n",
       "      <td>NASA (CRS)</td>\n",
       "      <td>Success</td>\n",
       "      <td>Uncontrolled (ocean)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2015-01-10</td>\n",
       "      <td>9:47:00</td>\n",
       "      <td>F9 v1.1 B1012</td>\n",
       "      <td>CCAFS LC-40</td>\n",
       "      <td>SpaceX CRS-5</td>\n",
       "      <td>2395</td>\n",
       "      <td>LEO (ISS)</td>\n",
       "      <td>NASA (CRS)</td>\n",
       "      <td>Success</td>\n",
       "      <td>Failure (drone ship)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2015-02-11</td>\n",
       "      <td>23:03:00</td>\n",
       "      <td>F9 v1.1 B1013</td>\n",
       "      <td>CCAFS LC-40</td>\n",
       "      <td>DSCOVR</td>\n",
       "      <td>570</td>\n",
       "      <td>HEO</td>\n",
       "      <td>U.S. Air Force NASA NOAA</td>\n",
       "      <td>Success</td>\n",
       "      <td>Controlled (ocean)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2015-03-02</td>\n",
       "      <td>3:50:00</td>\n",
       "      <td>F9 v1.1 B1014</td>\n",
       "      <td>CCAFS LC-40</td>\n",
       "      <td>ABS-3A Eutelsat 115 West B</td>\n",
       "      <td>4159</td>\n",
       "      <td>GTO</td>\n",
       "      <td>ABS Eutelsat</td>\n",
       "      <td>Success</td>\n",
       "      <td>No attempt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2015-04-14</td>\n",
       "      <td>20:10:00</td>\n",
       "      <td>F9 v1.1 B1015</td>\n",
       "      <td>CCAFS LC-40</td>\n",
       "      <td>SpaceX CRS-6</td>\n",
       "      <td>1898</td>\n",
       "      <td>LEO (ISS)</td>\n",
       "      <td>NASA (CRS)</td>\n",
       "      <td>Success</td>\n",
       "      <td>Failure (drone ship)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2015-04-27</td>\n",
       "      <td>23:03:00</td>\n",
       "      <td>F9 v1.1 B1016</td>\n",
       "      <td>CCAFS LC-40</td>\n",
       "      <td>Turkmen 52 / MonacoSAT</td>\n",
       "      <td>4707</td>\n",
       "      <td>GTO</td>\n",
       "      <td>Turkmenistan National Space Agency</td>\n",
       "      <td>Success</td>\n",
       "      <td>No attempt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2015-06-28</td>\n",
       "      <td>14:21:00</td>\n",
       "      <td>F9 v1.1 B1018</td>\n",
       "      <td>CCAFS LC-40</td>\n",
       "      <td>SpaceX CRS-7</td>\n",
       "      <td>1952</td>\n",
       "      <td>LEO (ISS)</td>\n",
       "      <td>NASA (CRS)</td>\n",
       "      <td>Failure (in flight)</td>\n",
       "      <td>Precluded (drone ship)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2015-12-22</td>\n",
       "      <td>1:29:00</td>\n",
       "      <td>F9 FT B1019</td>\n",
       "      <td>CCAFS LC-40</td>\n",
       "      <td>OG2 Mission 2  11 Orbcomm-OG2 satellites</td>\n",
       "      <td>2034</td>\n",
       "      <td>LEO</td>\n",
       "      <td>Orbcomm</td>\n",
       "      <td>Success</td>\n",
       "      <td>Success (ground pad)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2016-01-17</td>\n",
       "      <td>18:42:00</td>\n",
       "      <td>F9 v1.1 B1017</td>\n",
       "      <td>VAFB SLC-4E</td>\n",
       "      <td>Jason-3</td>\n",
       "      <td>553</td>\n",
       "      <td>LEO</td>\n",
       "      <td>NASA (LSP) NOAA CNES</td>\n",
       "      <td>Success</td>\n",
       "      <td>Failure (drone ship)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2016-03-04</td>\n",
       "      <td>23:35:00</td>\n",
       "      <td>F9 FT B1020</td>\n",
       "      <td>CCAFS LC-40</td>\n",
       "      <td>SES-9</td>\n",
       "      <td>5271</td>\n",
       "      <td>GTO</td>\n",
       "      <td>SES</td>\n",
       "      <td>Success</td>\n",
       "      <td>Failure (drone ship)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2016-04-08</td>\n",
       "      <td>20:43:00</td>\n",
       "      <td>F9 FT B1021.1</td>\n",
       "      <td>CCAFS LC-40</td>\n",
       "      <td>SpaceX CRS-8</td>\n",
       "      <td>3136</td>\n",
       "      <td>LEO (ISS)</td>\n",
       "      <td>NASA (CRS)</td>\n",
       "      <td>Success</td>\n",
       "      <td>Success (drone ship)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>2016-05-06</td>\n",
       "      <td>5:21:00</td>\n",
       "      <td>F9 FT B1022</td>\n",
       "      <td>CCAFS LC-40</td>\n",
       "      <td>JCSAT-14</td>\n",
       "      <td>4696</td>\n",
       "      <td>GTO</td>\n",
       "      <td>SKY Perfect JSAT Group</td>\n",
       "      <td>Success</td>\n",
       "      <td>Success (drone ship)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>2016-05-27</td>\n",
       "      <td>21:39:00</td>\n",
       "      <td>F9 FT B1023.1</td>\n",
       "      <td>CCAFS LC-40</td>\n",
       "      <td>Thaicom 8</td>\n",
       "      <td>3100</td>\n",
       "      <td>GTO</td>\n",
       "      <td>Thaicom</td>\n",
       "      <td>Success</td>\n",
       "      <td>Success (drone ship)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>2016-06-15</td>\n",
       "      <td>14:29:00</td>\n",
       "      <td>F9 FT B1024</td>\n",
       "      <td>CCAFS LC-40</td>\n",
       "      <td>ABS-2A Eutelsat 117 West B</td>\n",
       "      <td>3600</td>\n",
       "      <td>GTO</td>\n",
       "      <td>ABS Eutelsat</td>\n",
       "      <td>Success</td>\n",
       "      <td>Failure (drone ship)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>2016-07-18</td>\n",
       "      <td>4:45:00</td>\n",
       "      <td>F9 FT B1025.1</td>\n",
       "      <td>CCAFS LC-40</td>\n",
       "      <td>SpaceX CRS-9</td>\n",
       "      <td>2257</td>\n",
       "      <td>LEO (ISS)</td>\n",
       "      <td>NASA (CRS)</td>\n",
       "      <td>Success</td>\n",
       "      <td>Success (ground pad)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>2016-08-14</td>\n",
       "      <td>5:26:00</td>\n",
       "      <td>F9 FT B1026</td>\n",
       "      <td>CCAFS LC-40</td>\n",
       "      <td>JCSAT-16</td>\n",
       "      <td>4600</td>\n",
       "      <td>GTO</td>\n",
       "      <td>SKY Perfect JSAT Group</td>\n",
       "      <td>Success</td>\n",
       "      <td>Success (drone ship)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>2017-01-14</td>\n",
       "      <td>17:54:00</td>\n",
       "      <td>F9 FT B1029.1</td>\n",
       "      <td>VAFB SLC-4E</td>\n",
       "      <td>Iridium NEXT 1</td>\n",
       "      <td>9600</td>\n",
       "      <td>Polar LEO</td>\n",
       "      <td>Iridium Communications</td>\n",
       "      <td>Success</td>\n",
       "      <td>Success (drone ship)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>2017-02-19</td>\n",
       "      <td>14:39:00</td>\n",
       "      <td>F9 FT B1031.1</td>\n",
       "      <td>KSC LC-39A</td>\n",
       "      <td>SpaceX CRS-10</td>\n",
       "      <td>2490</td>\n",
       "      <td>LEO (ISS)</td>\n",
       "      <td>NASA (CRS)</td>\n",
       "      <td>Success</td>\n",
       "      <td>Success (ground pad)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>2017-03-16</td>\n",
       "      <td>6:00:00</td>\n",
       "      <td>F9 FT B1030</td>\n",
       "      <td>KSC LC-39A</td>\n",
       "      <td>EchoStar 23</td>\n",
       "      <td>5600</td>\n",
       "      <td>GTO</td>\n",
       "      <td>EchoStar</td>\n",
       "      <td>Success</td>\n",
       "      <td>No attempt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>2017-03-30</td>\n",
       "      <td>22:27:00</td>\n",
       "      <td>F9 FT  B1021.2</td>\n",
       "      <td>KSC LC-39A</td>\n",
       "      <td>SES-10</td>\n",
       "      <td>5300</td>\n",
       "      <td>GTO</td>\n",
       "      <td>SES</td>\n",
       "      <td>Success</td>\n",
       "      <td>Success (drone ship)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>2017-05-01</td>\n",
       "      <td>11:15:00</td>\n",
       "      <td>F9 FT B1032.1</td>\n",
       "      <td>KSC LC-39A</td>\n",
       "      <td>NROL-76</td>\n",
       "      <td>5300</td>\n",
       "      <td>LEO</td>\n",
       "      <td>NRO</td>\n",
       "      <td>Success</td>\n",
       "      <td>Success (ground pad)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>2017-05-15</td>\n",
       "      <td>23:21:00</td>\n",
       "      <td>F9 FT B1034</td>\n",
       "      <td>KSC LC-39A</td>\n",
       "      <td>Inmarsat-5 F4</td>\n",
       "      <td>6070</td>\n",
       "      <td>GTO</td>\n",
       "      <td>Inmarsat</td>\n",
       "      <td>Success</td>\n",
       "      <td>No attempt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>2017-06-03</td>\n",
       "      <td>21:07:00</td>\n",
       "      <td>F9 FT B1035.1</td>\n",
       "      <td>KSC LC-39A</td>\n",
       "      <td>SpaceX CRS-11</td>\n",
       "      <td>2708</td>\n",
       "      <td>LEO (ISS)</td>\n",
       "      <td>NASA (CRS)</td>\n",
       "      <td>Success</td>\n",
       "      <td>Success (ground pad)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>2017-06-23</td>\n",
       "      <td>19:10:00</td>\n",
       "      <td>F9 FT  B1029.2</td>\n",
       "      <td>KSC LC-39A</td>\n",
       "      <td>BulgariaSat-1</td>\n",
       "      <td>3669</td>\n",
       "      <td>GTO</td>\n",
       "      <td>Bulsatcom</td>\n",
       "      <td>Success</td>\n",
       "      <td>Success (drone ship)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>2017-06-25</td>\n",
       "      <td>20:25:00</td>\n",
       "      <td>F9 FT B1036.1</td>\n",
       "      <td>VAFB SLC-4E</td>\n",
       "      <td>Iridium NEXT 2</td>\n",
       "      <td>9600</td>\n",
       "      <td>LEO</td>\n",
       "      <td>Iridium Communications</td>\n",
       "      <td>Success</td>\n",
       "      <td>Success (drone ship)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>2017-07-05</td>\n",
       "      <td>23:38:00</td>\n",
       "      <td>F9 FT B1037</td>\n",
       "      <td>KSC LC-39A</td>\n",
       "      <td>Intelsat 35e</td>\n",
       "      <td>6761</td>\n",
       "      <td>GTO</td>\n",
       "      <td>Intelsat</td>\n",
       "      <td>Success</td>\n",
       "      <td>No attempt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>2017-08-14</td>\n",
       "      <td>16:31:00</td>\n",
       "      <td>F9 B4 B1039.1</td>\n",
       "      <td>KSC LC-39A</td>\n",
       "      <td>SpaceX CRS-12</td>\n",
       "      <td>3310</td>\n",
       "      <td>LEO (ISS)</td>\n",
       "      <td>NASA (CRS)</td>\n",
       "      <td>Success</td>\n",
       "      <td>Success (ground pad)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>2017-08-24</td>\n",
       "      <td>18:51:00</td>\n",
       "      <td>F9 FT B1038.1</td>\n",
       "      <td>VAFB SLC-4E</td>\n",
       "      <td>Formosat-5</td>\n",
       "      <td>475</td>\n",
       "      <td>SSO</td>\n",
       "      <td>NSPO</td>\n",
       "      <td>Success</td>\n",
       "      <td>Success (drone ship)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>2017-09-07</td>\n",
       "      <td>14:00:00</td>\n",
       "      <td>F9 B4 B1040.1</td>\n",
       "      <td>KSC LC-39A</td>\n",
       "      <td>Boeing X-37B OTV-5</td>\n",
       "      <td>4990</td>\n",
       "      <td>LEO</td>\n",
       "      <td>U.S. Air Force</td>\n",
       "      <td>Success</td>\n",
       "      <td>Success (ground pad)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>2017-10-09</td>\n",
       "      <td>12:37:00</td>\n",
       "      <td>F9 B4 B1041.1</td>\n",
       "      <td>VAFB SLC-4E</td>\n",
       "      <td>Iridium NEXT 3</td>\n",
       "      <td>9600</td>\n",
       "      <td>Polar LEO</td>\n",
       "      <td>Iridium Communications</td>\n",
       "      <td>Success</td>\n",
       "      <td>Success (drone ship)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>2017-10-11</td>\n",
       "      <td>22:53:00</td>\n",
       "      <td>F9 FT  B1031.2</td>\n",
       "      <td>KSC LC-39A</td>\n",
       "      <td>SES-11 / EchoStar 105</td>\n",
       "      <td>5200</td>\n",
       "      <td>GTO</td>\n",
       "      <td>SES EchoStar</td>\n",
       "      <td>Success</td>\n",
       "      <td>Success (drone ship)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>2017-10-30</td>\n",
       "      <td>19:34:00</td>\n",
       "      <td>F9 B4 B1042.1</td>\n",
       "      <td>KSC LC-39A</td>\n",
       "      <td>Koreasat 5A</td>\n",
       "      <td>3500</td>\n",
       "      <td>GTO</td>\n",
       "      <td>KT Corporation</td>\n",
       "      <td>Success</td>\n",
       "      <td>Success (drone ship)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>2017-12-15</td>\n",
       "      <td>15:36:00</td>\n",
       "      <td>F9 FT  B1035.2</td>\n",
       "      <td>CCAFS SLC-40</td>\n",
       "      <td>SpaceX CRS-13</td>\n",
       "      <td>2205</td>\n",
       "      <td>LEO (ISS)</td>\n",
       "      <td>NASA (CRS)</td>\n",
       "      <td>Success</td>\n",
       "      <td>Success (ground pad)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>2017-12-23</td>\n",
       "      <td>1:27:00</td>\n",
       "      <td>F9 FT  B1036.2</td>\n",
       "      <td>VAFB SLC-4E</td>\n",
       "      <td>Iridium NEXT 4</td>\n",
       "      <td>9600</td>\n",
       "      <td>Polar LEO</td>\n",
       "      <td>Iridium Communications</td>\n",
       "      <td>Success</td>\n",
       "      <td>Controlled (ocean)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>2018-01-08</td>\n",
       "      <td>1:00:00</td>\n",
       "      <td>F9 B4 B1043.1</td>\n",
       "      <td>CCAFS SLC-40</td>\n",
       "      <td>Zuma</td>\n",
       "      <td>5000</td>\n",
       "      <td>LEO</td>\n",
       "      <td>Northrop Grumman</td>\n",
       "      <td>Success (payload status unclear)</td>\n",
       "      <td>Success (ground pad)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>21:25:00</td>\n",
       "      <td>F9 FT  B1032.2</td>\n",
       "      <td>CCAFS SLC-40</td>\n",
       "      <td>GovSat-1 / SES-16</td>\n",
       "      <td>4230</td>\n",
       "      <td>GTO</td>\n",
       "      <td>SES</td>\n",
       "      <td>Success</td>\n",
       "      <td>Controlled (ocean)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>2018-02-22</td>\n",
       "      <td>14:17:00</td>\n",
       "      <td>F9 FT  B1038.2</td>\n",
       "      <td>VAFB SLC-4E</td>\n",
       "      <td>Paz  Tintin A &amp; B</td>\n",
       "      <td>2150</td>\n",
       "      <td>SSO</td>\n",
       "      <td>Hisdesat exactEarth SpaceX</td>\n",
       "      <td>Success</td>\n",
       "      <td>No attempt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>2018-03-06</td>\n",
       "      <td>5:33:00</td>\n",
       "      <td>F9 B4 B1044</td>\n",
       "      <td>CCAFS SLC-40</td>\n",
       "      <td>Hispasat 30W-6  PODSat</td>\n",
       "      <td>6092</td>\n",
       "      <td>GTO</td>\n",
       "      <td>Hispasat  NovaWurks</td>\n",
       "      <td>Success</td>\n",
       "      <td>No attempt</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Date Time (UTC) Booster_Version   Launch_Site  \\\n",
       "0   2010-06-04   18:45:00  F9 v1.0  B0003   CCAFS LC-40   \n",
       "1   2010-12-08   15:43:00  F9 v1.0  B0004   CCAFS LC-40   \n",
       "2   2012-05-22    7:44:00  F9 v1.0  B0005   CCAFS LC-40   \n",
       "3   2012-10-08    0:35:00  F9 v1.0  B0006   CCAFS LC-40   \n",
       "4   2013-03-01   15:10:00  F9 v1.0  B0007   CCAFS LC-40   \n",
       "5   2013-09-29   16:00:00  F9 v1.1  B1003   VAFB SLC-4E   \n",
       "6   2013-12-03   22:41:00         F9 v1.1   CCAFS LC-40   \n",
       "7   2014-01-06   22:06:00         F9 v1.1   CCAFS LC-40   \n",
       "8   2014-04-18   19:25:00         F9 v1.1   CCAFS LC-40   \n",
       "9   2014-07-14   15:15:00         F9 v1.1   CCAFS LC-40   \n",
       "10  2014-08-05    8:00:00         F9 v1.1   CCAFS LC-40   \n",
       "11  2014-09-07    5:00:00   F9 v1.1 B1011   CCAFS LC-40   \n",
       "12  2014-09-21    5:52:00   F9 v1.1 B1010   CCAFS LC-40   \n",
       "13  2015-01-10    9:47:00   F9 v1.1 B1012   CCAFS LC-40   \n",
       "14  2015-02-11   23:03:00   F9 v1.1 B1013   CCAFS LC-40   \n",
       "15  2015-03-02    3:50:00   F9 v1.1 B1014   CCAFS LC-40   \n",
       "16  2015-04-14   20:10:00   F9 v1.1 B1015   CCAFS LC-40   \n",
       "17  2015-04-27   23:03:00   F9 v1.1 B1016   CCAFS LC-40   \n",
       "18  2015-06-28   14:21:00   F9 v1.1 B1018   CCAFS LC-40   \n",
       "19  2015-12-22    1:29:00     F9 FT B1019   CCAFS LC-40   \n",
       "20  2016-01-17   18:42:00   F9 v1.1 B1017   VAFB SLC-4E   \n",
       "21  2016-03-04   23:35:00     F9 FT B1020   CCAFS LC-40   \n",
       "22  2016-04-08   20:43:00   F9 FT B1021.1   CCAFS LC-40   \n",
       "23  2016-05-06    5:21:00     F9 FT B1022   CCAFS LC-40   \n",
       "24  2016-05-27   21:39:00   F9 FT B1023.1   CCAFS LC-40   \n",
       "25  2016-06-15   14:29:00     F9 FT B1024   CCAFS LC-40   \n",
       "26  2016-07-18    4:45:00   F9 FT B1025.1   CCAFS LC-40   \n",
       "27  2016-08-14    5:26:00     F9 FT B1026   CCAFS LC-40   \n",
       "28  2017-01-14   17:54:00   F9 FT B1029.1   VAFB SLC-4E   \n",
       "29  2017-02-19   14:39:00   F9 FT B1031.1    KSC LC-39A   \n",
       "30  2017-03-16    6:00:00     F9 FT B1030    KSC LC-39A   \n",
       "31  2017-03-30   22:27:00  F9 FT  B1021.2    KSC LC-39A   \n",
       "32  2017-05-01   11:15:00   F9 FT B1032.1    KSC LC-39A   \n",
       "33  2017-05-15   23:21:00     F9 FT B1034    KSC LC-39A   \n",
       "34  2017-06-03   21:07:00   F9 FT B1035.1    KSC LC-39A   \n",
       "35  2017-06-23   19:10:00  F9 FT  B1029.2    KSC LC-39A   \n",
       "36  2017-06-25   20:25:00   F9 FT B1036.1   VAFB SLC-4E   \n",
       "37  2017-07-05   23:38:00     F9 FT B1037    KSC LC-39A   \n",
       "38  2017-08-14   16:31:00   F9 B4 B1039.1    KSC LC-39A   \n",
       "39  2017-08-24   18:51:00   F9 FT B1038.1   VAFB SLC-4E   \n",
       "40  2017-09-07   14:00:00   F9 B4 B1040.1    KSC LC-39A   \n",
       "41  2017-10-09   12:37:00   F9 B4 B1041.1   VAFB SLC-4E   \n",
       "42  2017-10-11   22:53:00  F9 FT  B1031.2    KSC LC-39A   \n",
       "43  2017-10-30   19:34:00   F9 B4 B1042.1    KSC LC-39A   \n",
       "44  2017-12-15   15:36:00  F9 FT  B1035.2  CCAFS SLC-40   \n",
       "45  2017-12-23    1:27:00  F9 FT  B1036.2   VAFB SLC-4E   \n",
       "46  2018-01-08    1:00:00   F9 B4 B1043.1  CCAFS SLC-40   \n",
       "47  2018-01-31   21:25:00  F9 FT  B1032.2  CCAFS SLC-40   \n",
       "48  2018-02-22   14:17:00  F9 FT  B1038.2   VAFB SLC-4E   \n",
       "49  2018-03-06    5:33:00     F9 B4 B1044  CCAFS SLC-40   \n",
       "\n",
       "                                              Payload  PAYLOAD_MASS__KG_  \\\n",
       "0                Dragon Spacecraft Qualification Unit                  0   \n",
       "1   Dragon demo flight C1, two CubeSats, barrel of...                  0   \n",
       "2                               Dragon demo flight C2                525   \n",
       "3                                        SpaceX CRS-1                500   \n",
       "4                                        SpaceX CRS-2                677   \n",
       "5                                            CASSIOPE                500   \n",
       "6                                               SES-8               3170   \n",
       "7                                           Thaicom 6               3325   \n",
       "8                                        SpaceX CRS-3               2296   \n",
       "9             OG2 Mission 1  6 Orbcomm-OG2 satellites               1316   \n",
       "10                                          AsiaSat 8               4535   \n",
       "11                                          AsiaSat 6               4428   \n",
       "12                                       SpaceX CRS-4               2216   \n",
       "13                                       SpaceX CRS-5               2395   \n",
       "14                                             DSCOVR                570   \n",
       "15                         ABS-3A Eutelsat 115 West B               4159   \n",
       "16                                       SpaceX CRS-6               1898   \n",
       "17                             Turkmen 52 / MonacoSAT               4707   \n",
       "18                                       SpaceX CRS-7               1952   \n",
       "19           OG2 Mission 2  11 Orbcomm-OG2 satellites               2034   \n",
       "20                                            Jason-3                553   \n",
       "21                                              SES-9               5271   \n",
       "22                                       SpaceX CRS-8               3136   \n",
       "23                                           JCSAT-14               4696   \n",
       "24                                          Thaicom 8               3100   \n",
       "25                         ABS-2A Eutelsat 117 West B               3600   \n",
       "26                                       SpaceX CRS-9               2257   \n",
       "27                                           JCSAT-16               4600   \n",
       "28                                     Iridium NEXT 1               9600   \n",
       "29                                      SpaceX CRS-10               2490   \n",
       "30                                        EchoStar 23               5600   \n",
       "31                                             SES-10               5300   \n",
       "32                                            NROL-76               5300   \n",
       "33                                      Inmarsat-5 F4               6070   \n",
       "34                                      SpaceX CRS-11               2708   \n",
       "35                                      BulgariaSat-1               3669   \n",
       "36                                     Iridium NEXT 2               9600   \n",
       "37                                       Intelsat 35e               6761   \n",
       "38                                      SpaceX CRS-12               3310   \n",
       "39                                         Formosat-5                475   \n",
       "40                                 Boeing X-37B OTV-5               4990   \n",
       "41                                     Iridium NEXT 3               9600   \n",
       "42                              SES-11 / EchoStar 105               5200   \n",
       "43                                        Koreasat 5A               3500   \n",
       "44                                      SpaceX CRS-13               2205   \n",
       "45                                     Iridium NEXT 4               9600   \n",
       "46                                               Zuma               5000   \n",
       "47                                  GovSat-1 / SES-16               4230   \n",
       "48                                  Paz  Tintin A & B               2150   \n",
       "49                             Hispasat 30W-6  PODSat               6092   \n",
       "\n",
       "        Orbit                            Customer  \\\n",
       "0         LEO                              SpaceX   \n",
       "1   LEO (ISS)                     NASA (COTS) NRO   \n",
       "2   LEO (ISS)                         NASA (COTS)   \n",
       "3   LEO (ISS)                          NASA (CRS)   \n",
       "4   LEO (ISS)                          NASA (CRS)   \n",
       "5   Polar LEO                                 MDA   \n",
       "6         GTO                                 SES   \n",
       "7         GTO                             Thaicom   \n",
       "8   LEO (ISS)                          NASA (CRS)   \n",
       "9         LEO                             Orbcomm   \n",
       "10        GTO                             AsiaSat   \n",
       "11        GTO                             AsiaSat   \n",
       "12  LEO (ISS)                          NASA (CRS)   \n",
       "13  LEO (ISS)                          NASA (CRS)   \n",
       "14        HEO            U.S. Air Force NASA NOAA   \n",
       "15        GTO                        ABS Eutelsat   \n",
       "16  LEO (ISS)                          NASA (CRS)   \n",
       "17        GTO  Turkmenistan National Space Agency   \n",
       "18  LEO (ISS)                          NASA (CRS)   \n",
       "19        LEO                             Orbcomm   \n",
       "20        LEO                NASA (LSP) NOAA CNES   \n",
       "21        GTO                                 SES   \n",
       "22  LEO (ISS)                          NASA (CRS)   \n",
       "23        GTO              SKY Perfect JSAT Group   \n",
       "24        GTO                             Thaicom   \n",
       "25        GTO                        ABS Eutelsat   \n",
       "26  LEO (ISS)                          NASA (CRS)   \n",
       "27        GTO              SKY Perfect JSAT Group   \n",
       "28  Polar LEO              Iridium Communications   \n",
       "29  LEO (ISS)                          NASA (CRS)   \n",
       "30        GTO                            EchoStar   \n",
       "31        GTO                                 SES   \n",
       "32        LEO                                 NRO   \n",
       "33        GTO                            Inmarsat   \n",
       "34  LEO (ISS)                          NASA (CRS)   \n",
       "35        GTO                           Bulsatcom   \n",
       "36        LEO              Iridium Communications   \n",
       "37        GTO                            Intelsat   \n",
       "38  LEO (ISS)                          NASA (CRS)   \n",
       "39        SSO                                NSPO   \n",
       "40        LEO                      U.S. Air Force   \n",
       "41  Polar LEO              Iridium Communications   \n",
       "42        GTO                        SES EchoStar   \n",
       "43        GTO                      KT Corporation   \n",
       "44  LEO (ISS)                          NASA (CRS)   \n",
       "45  Polar LEO              Iridium Communications   \n",
       "46        LEO                    Northrop Grumman   \n",
       "47        GTO                                 SES   \n",
       "48        SSO          Hisdesat exactEarth SpaceX   \n",
       "49        GTO                 Hispasat  NovaWurks   \n",
       "\n",
       "                     Mission_Outcome         Landing_Outcome  \n",
       "0                            Success     Failure (parachute)  \n",
       "1                            Success     Failure (parachute)  \n",
       "2                            Success              No attempt  \n",
       "3                            Success              No attempt  \n",
       "4                            Success              No attempt  \n",
       "5                            Success    Uncontrolled (ocean)  \n",
       "6                            Success              No attempt  \n",
       "7                            Success              No attempt  \n",
       "8                            Success      Controlled (ocean)  \n",
       "9                            Success      Controlled (ocean)  \n",
       "10                           Success              No attempt  \n",
       "11                           Success              No attempt  \n",
       "12                           Success    Uncontrolled (ocean)  \n",
       "13                           Success    Failure (drone ship)  \n",
       "14                           Success      Controlled (ocean)  \n",
       "15                           Success              No attempt  \n",
       "16                           Success    Failure (drone ship)  \n",
       "17                           Success              No attempt  \n",
       "18               Failure (in flight)  Precluded (drone ship)  \n",
       "19                           Success    Success (ground pad)  \n",
       "20                           Success    Failure (drone ship)  \n",
       "21                           Success    Failure (drone ship)  \n",
       "22                           Success    Success (drone ship)  \n",
       "23                           Success    Success (drone ship)  \n",
       "24                           Success    Success (drone ship)  \n",
       "25                           Success    Failure (drone ship)  \n",
       "26                           Success    Success (ground pad)  \n",
       "27                           Success    Success (drone ship)  \n",
       "28                           Success    Success (drone ship)  \n",
       "29                           Success    Success (ground pad)  \n",
       "30                           Success              No attempt  \n",
       "31                           Success    Success (drone ship)  \n",
       "32                           Success    Success (ground pad)  \n",
       "33                           Success              No attempt  \n",
       "34                           Success    Success (ground pad)  \n",
       "35                           Success    Success (drone ship)  \n",
       "36                           Success    Success (drone ship)  \n",
       "37                           Success              No attempt  \n",
       "38                           Success    Success (ground pad)  \n",
       "39                           Success    Success (drone ship)  \n",
       "40                           Success    Success (ground pad)  \n",
       "41                           Success    Success (drone ship)  \n",
       "42                           Success    Success (drone ship)  \n",
       "43                           Success    Success (drone ship)  \n",
       "44                           Success    Success (ground pad)  \n",
       "45                           Success      Controlled (ocean)  \n",
       "46  Success (payload status unclear)    Success (ground pad)  \n",
       "47                           Success      Controlled (ocean)  \n",
       "48                           Success              No attempt  \n",
       "49                           Success              No attempt  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(50)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Note:This below code is added to remove blank rows from table**\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#DROP THE TABLE IF EXISTS\n",
    "\n",
    "%sql DROP TABLE IF EXISTS SPACEXTABLE;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "(sqlite3.OperationalError) table SPACEXTABLE already exists\n",
      "[SQL: create table SPACEXTABLE as select * from SPACEXTBL where Date is not null]\n",
      "(Background on this error at: https://sqlalche.me/e/20/e3q8)\n"
     ]
    }
   ],
   "source": [
    "%sql create table SPACEXTABLE as select * from SPACEXTBL where Date is not null"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Tasks\n",
    "\n",
    "Now write and execute SQL queries to solve the assignment tasks.\n",
    "\n",
    "**Note: If the column names are in mixed case enclose it in double quotes\n",
    "   For Example \"Landing_Outcome\"**\n",
    "\n",
    "### Task 1\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "##### Display the names of the unique launch sites  in the space mission\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Launch_Site</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>CCAFS LC-40</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>VAFB SLC-4E</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>KSC LC-39A</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>CCAFS SLC-40</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('CCAFS LC-40',), ('VAFB SLC-4E',), ('KSC LC-39A',), ('CCAFS SLC-40',)]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "Select Distinct Launch_Site from SPACEXTABLE;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "### Task 2\n",
    "\n",
    "\n",
    "#####  Display 5 records where launch sites begin with the string 'CCA' \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Date</th>\n",
       "            <th>Time (UTC)</th>\n",
       "            <th>Booster_Version</th>\n",
       "            <th>Launch_Site</th>\n",
       "            <th>Payload</th>\n",
       "            <th>PAYLOAD_MASS__KG_</th>\n",
       "            <th>Orbit</th>\n",
       "            <th>Customer</th>\n",
       "            <th>Mission_Outcome</th>\n",
       "            <th>Landing_Outcome</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>2010-06-04</td>\n",
       "            <td>18:45:00</td>\n",
       "            <td>F9 v1.0  B0003</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "            <td>Dragon Spacecraft Qualification Unit</td>\n",
       "            <td>0</td>\n",
       "            <td>LEO</td>\n",
       "            <td>SpaceX</td>\n",
       "            <td>Success</td>\n",
       "            <td>Failure (parachute)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2010-12-08</td>\n",
       "            <td>15:43:00</td>\n",
       "            <td>F9 v1.0  B0004</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "            <td>Dragon demo flight C1, two CubeSats, barrel of Brouere cheese</td>\n",
       "            <td>0</td>\n",
       "            <td>LEO (ISS)</td>\n",
       "            <td>NASA (COTS) NRO</td>\n",
       "            <td>Success</td>\n",
       "            <td>Failure (parachute)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2012-05-22</td>\n",
       "            <td>7:44:00</td>\n",
       "            <td>F9 v1.0  B0005</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "            <td>Dragon demo flight C2</td>\n",
       "            <td>525</td>\n",
       "            <td>LEO (ISS)</td>\n",
       "            <td>NASA (COTS)</td>\n",
       "            <td>Success</td>\n",
       "            <td>No attempt</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2012-10-08</td>\n",
       "            <td>0:35:00</td>\n",
       "            <td>F9 v1.0  B0006</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "            <td>SpaceX CRS-1</td>\n",
       "            <td>500</td>\n",
       "            <td>LEO (ISS)</td>\n",
       "            <td>NASA (CRS)</td>\n",
       "            <td>Success</td>\n",
       "            <td>No attempt</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2013-03-01</td>\n",
       "            <td>15:10:00</td>\n",
       "            <td>F9 v1.0  B0007</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "            <td>SpaceX CRS-2</td>\n",
       "            <td>677</td>\n",
       "            <td>LEO (ISS)</td>\n",
       "            <td>NASA (CRS)</td>\n",
       "            <td>Success</td>\n",
       "            <td>No attempt</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('2010-06-04', '18:45:00', 'F9 v1.0  B0003', 'CCAFS LC-40', 'Dragon Spacecraft Qualification Unit', 0, 'LEO', 'SpaceX', 'Success', 'Failure (parachute)'),\n",
       " ('2010-12-08', '15:43:00', 'F9 v1.0  B0004', 'CCAFS LC-40', 'Dragon demo flight C1, two CubeSats, barrel of Brouere cheese', 0, 'LEO (ISS)', 'NASA (COTS) NRO', 'Success', 'Failure (parachute)'),\n",
       " ('2012-05-22', '7:44:00', 'F9 v1.0  B0005', 'CCAFS LC-40', 'Dragon demo flight C2', 525, 'LEO (ISS)', 'NASA (COTS)', 'Success', 'No attempt'),\n",
       " ('2012-10-08', '0:35:00', 'F9 v1.0  B0006', 'CCAFS LC-40', 'SpaceX CRS-1', 500, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'No attempt'),\n",
       " ('2013-03-01', '15:10:00', 'F9 v1.0  B0007', 'CCAFS LC-40', 'SpaceX CRS-2', 677, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'No attempt')]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "Select * from SPACEXTBL where Launch_Site like 'CCA%' limit 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 3\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "##### Display the total payload mass carried by boosters launched by NASA (CRS)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>SUM(PAYLOAD_MASS__KG_)</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>111268</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(111268,)]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "select SUM(PAYLOAD_MASS__KG_) from SPACEXTBL where Payload like '%CRS%';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 4\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "##### Display average payload mass carried by booster version F9 v1.1\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>AVG(PAYLOAD_MASS__KG_)</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>2534.6666666666665</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2534.6666666666665,)]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select AVG(PAYLOAD_MASS__KG_) from SPACEXTBL where Booster_Version like 'F9 v1.1%';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 5\n",
    "\n",
    "##### List the date when the first succesful landing outcome in ground pad was acheived.\n",
    "\n",
    "\n",
    "_Hint:Use min function_ \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>min(Date)</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>2010-06-04</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('2010-06-04',)]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "select min(Date) from SPACEXTBL where Mission_Outcome = 'Success';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 6\n",
    "\n",
    "##### List the names of the boosters which have success in drone ship and have payload mass greater than 4000 but less than 6000\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Booster_Version</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>F9 v1.1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 v1.1 B1011</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 v1.1 B1014</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 v1.1 B1016</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT B1020</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT B1022</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT B1026</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT B1030</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT  B1021.2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT B1032.1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B4 B1040.1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT  B1031.2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B4 B1043.1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT  B1032.2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B4  B1040.2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1046.2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1047.2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1046.3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5B1054</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1048.3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1051.2 </td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5B1060.1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1058.2 </td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5B1062.1</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('F9 v1.1',),\n",
       " ('F9 v1.1 B1011',),\n",
       " ('F9 v1.1 B1014',),\n",
       " ('F9 v1.1 B1016',),\n",
       " ('F9 FT B1020',),\n",
       " ('F9 FT B1022',),\n",
       " ('F9 FT B1026',),\n",
       " ('F9 FT B1030',),\n",
       " ('F9 FT  B1021.2',),\n",
       " ('F9 FT B1032.1',),\n",
       " ('F9 B4 B1040.1',),\n",
       " ('F9 FT  B1031.2',),\n",
       " ('F9 B4 B1043.1',),\n",
       " ('F9 FT  B1032.2',),\n",
       " ('F9 B4  B1040.2',),\n",
       " ('F9 B5 B1046.2',),\n",
       " ('F9 B5 B1047.2',),\n",
       " ('F9 B5 B1046.3',),\n",
       " ('F9 B5B1054',),\n",
       " ('F9 B5 B1048.3',),\n",
       " ('F9 B5 B1051.2 ',),\n",
       " ('F9 B5B1060.1',),\n",
       " ('F9 B5 B1058.2 ',),\n",
       " ('F9 B5B1062.1',)]"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "select distinct(Booster_Version) from SPACEXTBL \n",
    "    where PAYLOAD_MASS__KG_ between 4000 and 6000;\n",
    "/*(4000 < PAYLOAD_MASS__KG_ < 6000) limit 3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 7\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "##### List the total number of successful and failure mission outcomes\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Mission_Outcome</th>\n",
       "            <th>Count (Mission_Outcome)</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Failure (in flight)</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Success</td>\n",
       "            <td>98</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Success </td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Success (payload status unclear)</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Failure (in flight)', 1),\n",
       " ('Success', 98),\n",
       " ('Success ', 1),\n",
       " ('Success (payload status unclear)', 1)]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "Select  Mission_Outcome, Count (Mission_Outcome) from SPACEXTBL group by Mission_Outcome ;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 8\n",
    "\n",
    "\n",
    "\n",
    "##### List the   names of the booster_versions which have carried the maximum payload mass. Use a subquery\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Booster_Version</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1048.4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1048.5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1049.4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1049.5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1049.7 </td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1051.3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1051.4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1051.6</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1056.4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1058.3 </td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1060.2 </td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1060.3</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('F9 B5 B1048.4',),\n",
       " ('F9 B5 B1048.5',),\n",
       " ('F9 B5 B1049.4',),\n",
       " ('F9 B5 B1049.5',),\n",
       " ('F9 B5 B1049.7 ',),\n",
       " ('F9 B5 B1051.3',),\n",
       " ('F9 B5 B1051.4',),\n",
       " ('F9 B5 B1051.6',),\n",
       " ('F9 B5 B1056.4',),\n",
       " ('F9 B5 B1058.3 ',),\n",
       " ('F9 B5 B1060.2 ',),\n",
       " ('F9 B5 B1060.3',)]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "select Booster_Version from SPACEXTBL \n",
    "where PAYLOAD_MASS__KG_ = (select max(PAYLOAD_MASS__KG_) from SPACEXTBL)\n",
    "group by Booster_Version;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 9\n",
    "\n",
    "\n",
    "##### List the records which will display the month names, failure landing_outcomes in drone ship ,booster versions, launch_site for the months in year 2015.\n",
    "\n",
    "**Note: SQLLite does not support monthnames. So you need to use  substr(Date, 6,2) as month to get the months and substr(Date,0,5)='2015' for year.**\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>month</th>\n",
       "            <th>Mission_Outcome</th>\n",
       "            <th>Booster_Version</th>\n",
       "            <th>Launch_Site</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>01</td>\n",
       "            <td>Success</td>\n",
       "            <td>F9 v1.1 B1012</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>02</td>\n",
       "            <td>Success</td>\n",
       "            <td>F9 v1.1 B1013</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>03</td>\n",
       "            <td>Success</td>\n",
       "            <td>F9 v1.1 B1014</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>04</td>\n",
       "            <td>Success</td>\n",
       "            <td>F9 v1.1 B1015</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>04</td>\n",
       "            <td>Success</td>\n",
       "            <td>F9 v1.1 B1016</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>06</td>\n",
       "            <td>Failure (in flight)</td>\n",
       "            <td>F9 v1.1 B1018</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>12</td>\n",
       "            <td>Success</td>\n",
       "            <td>F9 FT B1019</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('01', 'Success', 'F9 v1.1 B1012', 'CCAFS LC-40'),\n",
       " ('02', 'Success', 'F9 v1.1 B1013', 'CCAFS LC-40'),\n",
       " ('03', 'Success', 'F9 v1.1 B1014', 'CCAFS LC-40'),\n",
       " ('04', 'Success', 'F9 v1.1 B1015', 'CCAFS LC-40'),\n",
       " ('04', 'Success', 'F9 v1.1 B1016', 'CCAFS LC-40'),\n",
       " ('06', 'Failure (in flight)', 'F9 v1.1 B1018', 'CCAFS LC-40'),\n",
       " ('12', 'Success', 'F9 FT B1019', 'CCAFS LC-40')]"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select substr(Date, 6,2) as month, Mission_Outcome, Booster_Version, Launch_Site from SPACEXTBL\n",
    "where substr(Date,0,5)='2015' ;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 10\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "##### Rank the count of landing outcomes (such as Failure (drone ship) or Success (ground pad)) between the date 2010-06-04 and 2017-03-20, in descending order.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Mission_Outcome</th>\n",
       "            <th>COUNT_LAUNCHES</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Success</td>\n",
       "            <td>30</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Failure (in flight)</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Success', 30), ('Failure (in flight)', 1)]"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT Mission_Outcome, COUNT(*) AS COUNT_LAUNCHES \n",
    "    FROM SPACEXTBL \n",
    "    WHERE DATE BETWEEN '2010-06-04' AND '2017-03-20' \n",
    "    GROUP BY Mission_Outcome ORDER BY COUNT_LAUNCHES DESC;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reference Links\n",
    "\n",
    "* <a href =\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Labs_Coursera_V5/labs/Lab%20-%20String%20Patterns%20-%20Sorting%20-%20Grouping/instructional-labs.md.html?origin=www.coursera.org\">Hands-on Lab : String Patterns, Sorting and Grouping</a>  \n",
    "\n",
    "*  <a  href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Labs_Coursera_V5/labs/Lab%20-%20Built-in%20functions%20/Hands-on_Lab__Built-in_Functions.md.html?origin=www.coursera.org\">Hands-on Lab: Built-in functions</a>\n",
    "\n",
    "*  <a  href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Labs_Coursera_V5/labs/Lab%20-%20Sub-queries%20and%20Nested%20SELECTs%20/instructional-labs.md.html?origin=www.coursera.org\">Hands-on Lab : Sub-queries and Nested SELECT Statements</a>\n",
    "\n",
    "*   <a href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Module%205/DB0201EN-Week3-1-3-SQLmagic.ipynb\">Hands-on Tutorial: Accessing Databases with SQL magic</a>\n",
    "\n",
    "*  <a href= \"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Module%205/DB0201EN-Week3-1-4-Analyzing.ipynb\">Hands-on Lab: Analyzing a real World Data Set</a>\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Author(s)\n",
    "\n",
    "<h4> Lakshmi Holla </h4>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Other Contributors\n",
    "\n",
    "<h4> Rav Ahuja </h4>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<!--\n",
    "## Change log\n",
    "| Date | Version | Changed by | Change Description |\n",
    "|------|--------|--------|---------|\n",
    "| 2024-07-10 | 1.1 |Anita Verma | Changed Version|\n",
    "| 2021-07-09 | 0.2 |Lakshmi Holla | Changes made in magic sql|\n",
    "| 2021-05-20 | 0.1 |Lakshmi Holla | Created Initial Version |\n",
    "-->\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## <h3 align=\"center\"> © IBM Corporation 2021. All rights reserved. <h3/>\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.9"
  },
  "prev_pub_hash": "a5fa84525a820d08c9ec9c9cccf7d39455745aa2be6c2efdd18931346283d2d9"
 },
 "nbformat": 4,
 "nbformat_minor": 4
}