spacex-lab3-sql-sqllite.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style=\"text-align:center\">\n",
    "    <a href=\"https://skills.network/?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDS0321ENSkillsNetwork865-2022-01-01\" 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": 1,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting sqlalchemy==1.3.9\n",
      "  Downloading SQLAlchemy-1.3.9.tar.gz (6.0 MB)\n",
      "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m6.0/6.0 MB\u001b[0m \u001b[31m67.1 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m:00:01\u001b[0m0:01\u001b[0m\n",
      "\u001b[?25h  Preparing metadata (setup.py) ... \u001b[?25ldone\n",
      "\u001b[?25hBuilding wheels for collected packages: sqlalchemy\n",
      "  Building wheel for sqlalchemy (setup.py) ... \u001b[?25ldone\n",
      "\u001b[?25h  Created wheel for sqlalchemy: filename=SQLAlchemy-1.3.9-cp37-cp37m-linux_x86_64.whl size=1159121 sha256=1aca00ca6601c320994714b2d88ce1d7ad91ff239dcdbf7be936956a085573a9\n",
      "  Stored in directory: /home/jupyterlab/.cache/pip/wheels/03/71/13/010faf12246f72dc76b4150e6e599d13a85b4435e06fb9e51f\n",
      "Successfully built sqlalchemy\n",
      "Installing collected packages: sqlalchemy\n",
      "  Attempting uninstall: sqlalchemy\n",
      "    Found existing installation: SQLAlchemy 1.3.24\n",
      "    Uninstalling SQLAlchemy-1.3.24:\n",
      "      Successfully uninstalled SQLAlchemy-1.3.24\n",
      "Successfully 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": 1,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import csv, sqlite3\n",
    "\n",
    "con = sqlite3.connect(\"my_data1.db\")\n",
    "cur = con.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "!pip install -q pandas==1.1.5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: @my_data1.db'"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql sqlite:///my_data1.db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/jupyterlab/conda/envs/python/lib/python3.7/site-packages/pandas/core/generic.py:2882: UserWarning: The spaces in these column names will not be changed. In pandas versions < 0.14, spaces were converted to underscores.\n",
      "  both result in 0.1234 being formatted as 0.12.\n"
     ]
    }
   ],
   "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": "markdown",
   "metadata": {
    "tags": []
   },
   "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": 11,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "#%sql select * from SPACEXTBL LI"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "tags": []
   },
   "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": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select distinct(Launch_Site) from SPACEXTBL where Launch_Site!='None' "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Landing_Outcome</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Failure (parachute)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Failure (parachute)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>No attempt</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>No attempt</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>No attempt</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>No attempt</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>No attempt</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Controlled (ocean)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Controlled (ocean)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>No attempt</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>No attempt</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Uncontrolled (ocean)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Failure (drone ship)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Controlled (ocean)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>No attempt</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Failure (drone ship)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>No attempt</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Precluded (drone ship)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Success (ground pad)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Failure (drone ship)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Success (drone ship)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Success (drone ship)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Success (drone ship)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Failure (drone ship)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Success (ground pad)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Success (drone ship)</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Failure (parachute)',),\n",
       " ('Failure (parachute)',),\n",
       " ('No attempt',),\n",
       " ('No attempt',),\n",
       " ('No attempt',),\n",
       " ('No attempt',),\n",
       " ('No attempt',),\n",
       " ('Controlled (ocean)',),\n",
       " ('Controlled (ocean)',),\n",
       " ('No attempt',),\n",
       " ('No attempt',),\n",
       " ('Uncontrolled (ocean)',),\n",
       " ('Failure (drone ship)',),\n",
       " ('Controlled (ocean)',),\n",
       " ('No attempt',),\n",
       " ('Failure (drone ship)',),\n",
       " ('No attempt',),\n",
       " ('Precluded (drone ship)',),\n",
       " ('Success (ground pad)',),\n",
       " ('Failure (drone ship)',),\n",
       " ('Success (drone ship)',),\n",
       " ('Success (drone ship)',),\n",
       " ('Success (drone ship)',),\n",
       " ('Failure (drone ship)',),\n",
       " ('Success (ground pad)',),\n",
       " ('Success (drone ship)',)]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select Landing_Outcome from SPACEXTBL where Launch_Site ='CCAFS LC-40'"
   ]
  },
  {
   "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": 8,
   "metadata": {
    "tags": []
   },
   "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>06/04/2010</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.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>12/08/2010</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.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>22/05/2012</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.0</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>10/08/2012</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.0</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>03/01/2013</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.0</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": [
       "[('06/04/2010', '18:45:00', 'F9 v1.0  B0003', 'CCAFS LC-40', 'Dragon Spacecraft Qualification Unit', 0.0, 'LEO', 'SpaceX', 'Success', 'Failure (parachute)'),\n",
       " ('12/08/2010', '15:43:00', 'F9 v1.0  B0004', 'CCAFS LC-40', 'Dragon demo flight C1, two CubeSats, barrel of Brouere cheese', 0.0, 'LEO (ISS)', 'NASA (COTS) NRO', 'Success', 'Failure (parachute)'),\n",
       " ('22/05/2012', '7:44:00', 'F9 v1.0  B0005', 'CCAFS LC-40', 'Dragon demo flight C2', 525.0, 'LEO (ISS)', 'NASA (COTS)', 'Success', 'No attempt'),\n",
       " ('10/08/2012', '0:35:00', 'F9 v1.0  B0006', 'CCAFS LC-40', 'SpaceX CRS-1', 500.0, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'No attempt'),\n",
       " ('03/01/2013', '15:10:00', 'F9 v1.0  B0007', 'CCAFS LC-40', 'SpaceX CRS-2', 677.0, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'No attempt')]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql 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": 9,
   "metadata": {
    "tags": []
   },
   "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>45596.0</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(45596.0,)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select sum(PAYLOAD_MASS__KG_) from SPACEXTBL where customer = 'NASA (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": 10,
   "metadata": {
    "tags": []
   },
   "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": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql 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": 12,
   "metadata": {
    "tags": []
   },
   "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>01/08/2018</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('01/08/2018',)]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select min(Date) from SPACEXTBL where Landing_Outcome = 'Success (ground pad)'"
   ]
  },
  {
   "cell_type": "raw",
   "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": 13,
   "metadata": {
    "tags": []
   },
   "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 FT B1022</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT B1026</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT  B1021.2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT  B1031.2</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('F9 FT B1022',), ('F9 FT B1026',), ('F9 FT  B1021.2',), ('F9 FT  B1031.2',)]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql select Booster_Version from SPACEXTBL where Landing_Outcome = 'Success (drone ship)' \n",
    "                and PAYLOAD_MASS__KG_ > 4000 and PAYLOAD_MASS__KG_ < 6000"
   ]
  },
  {
   "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": 14,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>NumOfSuccess</th>\n",
       "            <th>NumberOfFailure</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>61</td>\n",
       "            <td>10</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(61, 10)]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql select\n",
    "        (select count(*) from SPACEXTBL where Landing_Outcome like 'Success%') as NumOfSuccess,\n",
    "        (select count(*) from SPACEXTBL where Landing_Outcome like 'Failure%') as  NumberOfFailure;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "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": 15,
   "metadata": {
    "tags": []
   },
   "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 B1049.4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1051.3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1056.4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1048.5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1051.4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1049.5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1060.2 </td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1058.3 </td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1051.6</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1060.3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1049.7 </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('F9 B5 B1048.4',),\n",
       " ('F9 B5 B1049.4',),\n",
       " ('F9 B5 B1051.3',),\n",
       " ('F9 B5 B1056.4',),\n",
       " ('F9 B5 B1048.5',),\n",
       " ('F9 B5 B1051.4',),\n",
       " ('F9 B5 B1049.5',),\n",
       " ('F9 B5 B1060.2 ',),\n",
       " ('F9 B5 B1058.3 ',),\n",
       " ('F9 B5 B1051.6',),\n",
       " ('F9 B5 B1060.3',),\n",
       " ('F9 B5 B1049.7 ',)]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select Booster_Version from SPACEXTBL where PAYLOAD_MASS__KG_ = (select max(PAYLOAD_MASS__KG_) from SPACEXTBL)"
   ]
  },
  {
   "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, 4, 2) as month to get the months and substr(Date,7,4)='2015' for year.**\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "tags": []
   },
   "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>year</th>\n",
       "            <th>Booster_Version</th>\n",
       "            <th>Launch_Site</th>\n",
       "            <th>Landing_Outcome</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>10</td>\n",
       "            <td>2015</td>\n",
       "            <td>F9 v1.1 B1012</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "            <td>Failure (drone ship)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>04</td>\n",
       "            <td>2015</td>\n",
       "            <td>F9 v1.1 B1015</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "            <td>Failure (drone ship)</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('10', '2015', 'F9 v1.1 B1012', 'CCAFS LC-40', 'Failure (drone ship)'),\n",
       " ('04', '2015', 'F9 v1.1 B1015', 'CCAFS LC-40', 'Failure (drone ship)')]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql select substr(Date,4,2) as month , substr(Date,7,4) as year,Booster_Version, Launch_Site, Landing_Outcome from SPACEXTBL where \n",
    "        Landing_Outcome = 'Failure (drone ship)' and year = '2015'\n",
    "        "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 10\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "##### Rank the  count of  successful landing_outcomes between the date 04-06-2010 and 20-03-2017 in descending order.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>count(*)</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>35</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(35,)]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select count(*) from SPACEXTBL where Landing_Outcome like 'Success%' and Date between '04-06-2010' and '20-03-2017'\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>max(PAYLOAD_MASS__KG_)</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>15600.0</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(15600.0,)]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select max(PAYLOAD_MASS__KG_) from SPACEXTBL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Additional EDA kindered by curiosity on why in the predicative modeling only uses data from 3 sites. Therefore I am exporing the data on the site that was not selected and reason why data on Site CCAFS LC-40 was not\n",
    "selected as the training and testing data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    },
    "tags": []
   },
   "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>06/04/2010</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.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>12/08/2010</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.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>22/05/2012</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.0</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>10/08/2012</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.0</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>03/01/2013</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.0</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>12/03/2013</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.0</td>\n",
       "            <td>GTO</td>\n",
       "            <td>SES</td>\n",
       "            <td>Success</td>\n",
       "            <td>No attempt</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>01/06/2014</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.0</td>\n",
       "            <td>GTO</td>\n",
       "            <td>Thaicom</td>\n",
       "            <td>Success</td>\n",
       "            <td>No attempt</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>18/04/2014</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.0</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",
       "            <td>14/07/2014</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.0</td>\n",
       "            <td>LEO</td>\n",
       "            <td>Orbcomm</td>\n",
       "            <td>Success</td>\n",
       "            <td>Controlled (ocean)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>08/05/2014</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.0</td>\n",
       "            <td>GTO</td>\n",
       "            <td>AsiaSat</td>\n",
       "            <td>Success</td>\n",
       "            <td>No attempt</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>09/07/2014</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.0</td>\n",
       "            <td>GTO</td>\n",
       "            <td>AsiaSat</td>\n",
       "            <td>Success</td>\n",
       "            <td>No attempt</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>21/09/2014</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.0</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",
       "            <td>01/10/2015</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.0</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",
       "            <td>02/11/2015</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.0</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",
       "            <td>03/02/2015</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.0</td>\n",
       "            <td>GTO</td>\n",
       "            <td>ABS Eutelsat</td>\n",
       "            <td>Success</td>\n",
       "            <td>No attempt</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>14/04/2015</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.0</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",
       "            <td>27/04/2015</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.0</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",
       "            <td>28/06/2015</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.0</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",
       "            <td>22/12/2015</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.0</td>\n",
       "            <td>LEO</td>\n",
       "            <td>Orbcomm</td>\n",
       "            <td>Success</td>\n",
       "            <td>Success (ground pad)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>03/04/2016</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.0</td>\n",
       "            <td>GTO</td>\n",
       "            <td>SES</td>\n",
       "            <td>Success</td>\n",
       "            <td>Failure (drone ship)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>04/08/2016</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.0</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",
       "            <td>05/06/2016</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.0</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",
       "            <td>27/05/2016</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.0</td>\n",
       "            <td>GTO</td>\n",
       "            <td>Thaicom</td>\n",
       "            <td>Success</td>\n",
       "            <td>Success (drone ship)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>15/06/2016</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.0</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",
       "            <td>18/07/2016</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.0</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",
       "            <td>14/08/2016</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.0</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",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('06/04/2010', '18:45:00', 'F9 v1.0  B0003', 'CCAFS LC-40', 'Dragon Spacecraft Qualification Unit', 0.0, 'LEO', 'SpaceX', 'Success', 'Failure (parachute)'),\n",
       " ('12/08/2010', '15:43:00', 'F9 v1.0  B0004', 'CCAFS LC-40', 'Dragon demo flight C1, two CubeSats, barrel of Brouere cheese', 0.0, 'LEO (ISS)', 'NASA (COTS) NRO', 'Success', 'Failure (parachute)'),\n",
       " ('22/05/2012', '7:44:00', 'F9 v1.0  B0005', 'CCAFS LC-40', 'Dragon demo flight C2', 525.0, 'LEO (ISS)', 'NASA (COTS)', 'Success', 'No attempt'),\n",
       " ('10/08/2012', '0:35:00', 'F9 v1.0  B0006', 'CCAFS LC-40', 'SpaceX CRS-1', 500.0, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'No attempt'),\n",
       " ('03/01/2013', '15:10:00', 'F9 v1.0  B0007', 'CCAFS LC-40', 'SpaceX CRS-2', 677.0, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'No attempt'),\n",
       " ('12/03/2013', '22:41:00', 'F9 v1.1', 'CCAFS LC-40', 'SES-8', 3170.0, 'GTO', 'SES', 'Success', 'No attempt'),\n",
       " ('01/06/2014', '22:06:00', 'F9 v1.1', 'CCAFS LC-40', 'Thaicom 6', 3325.0, 'GTO', 'Thaicom', 'Success', 'No attempt'),\n",
       " ('18/04/2014', '19:25:00', 'F9 v1.1', 'CCAFS LC-40', 'SpaceX CRS-3', 2296.0, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'Controlled (ocean)'),\n",
       " ('14/07/2014', '15:15:00', 'F9 v1.1', 'CCAFS LC-40', 'OG2 Mission 1  6 Orbcomm-OG2 satellites', 1316.0, 'LEO', 'Orbcomm', 'Success', 'Controlled (ocean)'),\n",
       " ('08/05/2014', '8:00:00', 'F9 v1.1', 'CCAFS LC-40', 'AsiaSat 8', 4535.0, 'GTO', 'AsiaSat', 'Success', 'No attempt'),\n",
       " ('09/07/2014', '5:00:00', 'F9 v1.1 B1011', 'CCAFS LC-40', 'AsiaSat 6', 4428.0, 'GTO', 'AsiaSat', 'Success', 'No attempt'),\n",
       " ('21/09/2014', '5:52:00', 'F9 v1.1 B1010', 'CCAFS LC-40', 'SpaceX CRS-4', 2216.0, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'Uncontrolled (ocean)'),\n",
       " ('01/10/2015', '9:47:00', 'F9 v1.1 B1012', 'CCAFS LC-40', 'SpaceX CRS-5', 2395.0, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'Failure (drone ship)'),\n",
       " ('02/11/2015', '23:03:00', 'F9 v1.1 B1013', 'CCAFS LC-40', 'DSCOVR', 570.0, 'HEO', 'U.S. Air Force NASA NOAA', 'Success', 'Controlled (ocean)'),\n",
       " ('03/02/2015', '3:50:00', 'F9 v1.1 B1014', 'CCAFS LC-40', 'ABS-3A Eutelsat 115 West B', 4159.0, 'GTO', 'ABS Eutelsat', 'Success', 'No attempt'),\n",
       " ('14/04/2015', '20:10:00', 'F9 v1.1 B1015', 'CCAFS LC-40', 'SpaceX CRS-6', 1898.0, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'Failure (drone ship)'),\n",
       " ('27/04/2015', '23:03:00', 'F9 v1.1 B1016', 'CCAFS LC-40', 'Turkmen 52 / MonacoSAT', 4707.0, 'GTO', 'Turkmenistan National Space Agency', 'Success', 'No attempt'),\n",
       " ('28/06/2015', '14:21:00', 'F9 v1.1 B1018', 'CCAFS LC-40', 'SpaceX CRS-7', 1952.0, 'LEO (ISS)', 'NASA (CRS)', 'Failure (in flight)', 'Precluded (drone ship)'),\n",
       " ('22/12/2015', '1:29:00', 'F9 FT B1019', 'CCAFS LC-40', 'OG2 Mission 2  11 Orbcomm-OG2 satellites', 2034.0, 'LEO', 'Orbcomm', 'Success', 'Success (ground pad)'),\n",
       " ('03/04/2016', '23:35:00', 'F9 FT B1020', 'CCAFS LC-40', 'SES-9', 5271.0, 'GTO', 'SES', 'Success', 'Failure (drone ship)'),\n",
       " ('04/08/2016', '20:43:00', 'F9 FT B1021.1', 'CCAFS LC-40', 'SpaceX CRS-8', 3136.0, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'Success (drone ship)'),\n",
       " ('05/06/2016', '5:21:00', 'F9 FT B1022', 'CCAFS LC-40', 'JCSAT-14', 4696.0, 'GTO', 'SKY Perfect JSAT Group', 'Success', 'Success (drone ship)'),\n",
       " ('27/05/2016', '21:39:00', 'F9 FT B1023.1', 'CCAFS LC-40', 'Thaicom 8', 3100.0, 'GTO', 'Thaicom', 'Success', 'Success (drone ship)'),\n",
       " ('15/06/2016', '14:29:00', 'F9 FT B1024', 'CCAFS LC-40', 'ABS-2A Eutelsat 117 West B', 3600.0, 'GTO', 'ABS Eutelsat', 'Success', 'Failure (drone ship)'),\n",
       " ('18/07/2016', '4:45:00', 'F9 FT B1025.1', 'CCAFS LC-40', 'SpaceX CRS-9', 2257.0, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'Success (ground pad)'),\n",
       " ('14/08/2016', '5:26:00', 'F9 FT B1026', 'CCAFS LC-40', 'JCSAT-16', 4600.0, 'GTO', 'SKY Perfect JSAT Group', 'Success', 'Success (drone ship)')]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select * from SPACEXTBL where Launch_Site = 'CCAFS LC-40'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "tags": []
   },
   "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>15/12/2017</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.0</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",
       "            <td>01/08/2018</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.0</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",
       "            <td>31/01/2018</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.0</td>\n",
       "            <td>GTO</td>\n",
       "            <td>SES</td>\n",
       "            <td>Success</td>\n",
       "            <td>Controlled (ocean)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>03/06/2018</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.0</td>\n",
       "            <td>GTO</td>\n",
       "            <td>Hispasat  NovaWurks</td>\n",
       "            <td>Success</td>\n",
       "            <td>No attempt</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>04/02/2018</td>\n",
       "            <td>20:30:00</td>\n",
       "            <td>F9 B4  B1039.2</td>\n",
       "            <td>CCAFS SLC-40</td>\n",
       "            <td>SpaceX CRS-14</td>\n",
       "            <td>2647.0</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>18/04/2018</td>\n",
       "            <td>22:51:00</td>\n",
       "            <td>F9 B4 B1045.1</td>\n",
       "            <td>CCAFS SLC-40</td>\n",
       "            <td>Transiting Exoplanet Survey Satellite (TESS)</td>\n",
       "            <td>362.0</td>\n",
       "            <td>HEO</td>\n",
       "            <td>NASA (LSP)</td>\n",
       "            <td>Success</td>\n",
       "            <td>Success (drone ship)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>06/04/2018</td>\n",
       "            <td>4:45:00</td>\n",
       "            <td>F9 B4  B1040.2</td>\n",
       "            <td>CCAFS SLC-40</td>\n",
       "            <td>SES-12</td>\n",
       "            <td>5384.0</td>\n",
       "            <td>GTO</td>\n",
       "            <td>SES</td>\n",
       "            <td>Success</td>\n",
       "            <td>No attempt</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>29/06/2018</td>\n",
       "            <td>9:42:00</td>\n",
       "            <td>F9 B4 B1045.2</td>\n",
       "            <td>CCAFS SLC-40</td>\n",
       "            <td>SpaceX CRS-15</td>\n",
       "            <td>2697.0</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>22/07/2018</td>\n",
       "            <td>5:50:00</td>\n",
       "            <td>F9 B5B1047.1</td>\n",
       "            <td>CCAFS SLC-40</td>\n",
       "            <td>Telstar 19V</td>\n",
       "            <td>7075.0</td>\n",
       "            <td>GTO</td>\n",
       "            <td>Telesat</td>\n",
       "            <td>Success</td>\n",
       "            <td>Success</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>08/07/2018</td>\n",
       "            <td>5:18:00</td>\n",
       "            <td>F9 B5 B1046.2</td>\n",
       "            <td>CCAFS SLC-40</td>\n",
       "            <td>Merah Putih </td>\n",
       "            <td>5800.0</td>\n",
       "            <td>GTO</td>\n",
       "            <td>Telkom Indonesia</td>\n",
       "            <td>Success</td>\n",
       "            <td>Success</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>09/10/2018</td>\n",
       "            <td>4:45:00</td>\n",
       "            <td>F9 B5B1049.1</td>\n",
       "            <td>CCAFS SLC-40</td>\n",
       "            <td>Telstar 18V / Apstar-5C</td>\n",
       "            <td>7060.0</td>\n",
       "            <td>GTO</td>\n",
       "            <td>Telesat</td>\n",
       "            <td>Success</td>\n",
       "            <td>Success</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>12/05/2018</td>\n",
       "            <td>18:16:00</td>\n",
       "            <td>F9 B5B1050</td>\n",
       "            <td>CCAFS SLC-40</td>\n",
       "            <td>SpaceX CRS-16</td>\n",
       "            <td>2500.0</td>\n",
       "            <td>LEO (ISS)</td>\n",
       "            <td>NASA (CRS)</td>\n",
       "            <td>Success</td>\n",
       "            <td>Failure</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>23/12/2018</td>\n",
       "            <td>13:51:00</td>\n",
       "            <td>F9 B5B1054</td>\n",
       "            <td>CCAFS SLC-40</td>\n",
       "            <td>GPS III-01 </td>\n",
       "            <td>4400.0</td>\n",
       "            <td>MEO</td>\n",
       "            <td>USAF</td>\n",
       "            <td>Success </td>\n",
       "            <td>No attempt</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>22/02/2019</td>\n",
       "            <td>1:45:00</td>\n",
       "            <td>F9 B5 B1048.3</td>\n",
       "            <td>CCAFS SLC-40</td>\n",
       "            <td>Nusantara Satu, Beresheet Moon lander, S5</td>\n",
       "            <td>4850.0</td>\n",
       "            <td>GTO</td>\n",
       "            <td>PSN, SpaceIL / IAI</td>\n",
       "            <td>Success</td>\n",
       "            <td>Success</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>05/04/2019</td>\n",
       "            <td>6:48:00</td>\n",
       "            <td>F9 B5B1056.1 </td>\n",
       "            <td>CCAFS SLC-40</td>\n",
       "            <td>SpaceX CRS-17, Starlink v0.9</td>\n",
       "            <td>2495.0</td>\n",
       "            <td>LEO (ISS)</td>\n",
       "            <td>NASA (CRS)</td>\n",
       "            <td>Success</td>\n",
       "            <td>Success</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>24/05/2019</td>\n",
       "            <td>2:30:00</td>\n",
       "            <td>F9 B5 B1049.3</td>\n",
       "            <td>CCAFS SLC-40</td>\n",
       "            <td>Starlink v0.9, RADARSAT Constellation</td>\n",
       "            <td>13620.0</td>\n",
       "            <td>LEO</td>\n",
       "            <td>SpaceX</td>\n",
       "            <td>Success</td>\n",
       "            <td>Success</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>25/07/2019</td>\n",
       "            <td>22:01:00</td>\n",
       "            <td>F9 B5 B1056.2 </td>\n",
       "            <td>CCAFS SLC-40</td>\n",
       "            <td>SpaceX CRS-18, AMOS-17 </td>\n",
       "            <td>2268.0</td>\n",
       "            <td>LEO (ISS)</td>\n",
       "            <td>NASA (CRS)</td>\n",
       "            <td>Success</td>\n",
       "            <td>Success</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>08/06/2019</td>\n",
       "            <td>23:23:00</td>\n",
       "            <td>F9 B5 B1047.3 </td>\n",
       "            <td>CCAFS SLC-40</td>\n",
       "            <td>AMOS-17, Starlink 1 v1.0 </td>\n",
       "            <td>6500.0</td>\n",
       "            <td>GTO</td>\n",
       "            <td>Spacecom</td>\n",
       "            <td>Success</td>\n",
       "            <td>No attempt </td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>11/11/2019</td>\n",
       "            <td>14:56:00</td>\n",
       "            <td>F9 B5 B1048.4</td>\n",
       "            <td>CCAFS SLC-40</td>\n",
       "            <td>Starlink 1 v1.0, SpaceX CRS-19 </td>\n",
       "            <td>15600.0</td>\n",
       "            <td>LEO</td>\n",
       "            <td>SpaceX</td>\n",
       "            <td>Success</td>\n",
       "            <td>Success</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>12/05/2019</td>\n",
       "            <td>17:29:00</td>\n",
       "            <td>F9 B5B1059.1</td>\n",
       "            <td>CCAFS SLC-40</td>\n",
       "            <td>SpaceX CRS-19, JCSat-18 / Kacific 1 </td>\n",
       "            <td>2617.0</td>\n",
       "            <td>LEO (ISS)</td>\n",
       "            <td>NASA (CRS), Kacific 1</td>\n",
       "            <td>Success</td>\n",
       "            <td>Success</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>17/12/2019</td>\n",
       "            <td>0:10:00</td>\n",
       "            <td>F9 B5 B1056.3 </td>\n",
       "            <td>CCAFS SLC-40</td>\n",
       "            <td>JCSat-18 / Kacific 1, Starlink 2 v1.0 </td>\n",
       "            <td>6956.0</td>\n",
       "            <td>GTO</td>\n",
       "            <td>Sky Perfect JSAT, Kacific 1</td>\n",
       "            <td>Success</td>\n",
       "            <td>Success</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>01/07/2020</td>\n",
       "            <td>2:33:00</td>\n",
       "            <td>F9 B5 B1049.4</td>\n",
       "            <td>CCAFS SLC-40</td>\n",
       "            <td>Starlink 2 v1.0, Crew Dragon in-flight abort test </td>\n",
       "            <td>15600.0</td>\n",
       "            <td>LEO</td>\n",
       "            <td>SpaceX</td>\n",
       "            <td>Success</td>\n",
       "            <td>Success</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>29/01/2020</td>\n",
       "            <td>14:07:00</td>\n",
       "            <td>F9 B5 B1051.3</td>\n",
       "            <td>CCAFS SLC-40</td>\n",
       "            <td>Starlink 3 v1.0, Starlink 4 v1.0 </td>\n",
       "            <td>15600.0</td>\n",
       "            <td>LEO</td>\n",
       "            <td>SpaceX</td>\n",
       "            <td>Success</td>\n",
       "            <td>Success</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>17/02/2020</td>\n",
       "            <td>15:05:00</td>\n",
       "            <td>F9 B5 B1056.4</td>\n",
       "            <td>CCAFS SLC-40</td>\n",
       "            <td>Starlink 4 v1.0, SpaceX CRS-20</td>\n",
       "            <td>15600.0</td>\n",
       "            <td>LEO</td>\n",
       "            <td>SpaceX</td>\n",
       "            <td>Success</td>\n",
       "            <td>Failure</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>03/07/2020</td>\n",
       "            <td>4:50:00</td>\n",
       "            <td>F9 B5 B1059.2</td>\n",
       "            <td>CCAFS SLC-40</td>\n",
       "            <td>SpaceX CRS-20, Starlink 5 v1.0 </td>\n",
       "            <td>1977.0</td>\n",
       "            <td>LEO (ISS)</td>\n",
       "            <td>NASA (CRS)</td>\n",
       "            <td>Success</td>\n",
       "            <td>Success</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>06/04/2020</td>\n",
       "            <td>1:25:00</td>\n",
       "            <td>F9 B5 B1049.5</td>\n",
       "            <td>CCAFS SLC-40</td>\n",
       "            <td>Starlink 7 v1.0, Starlink 8 v1.0</td>\n",
       "            <td>15600.0</td>\n",
       "            <td>LEO</td>\n",
       "            <td>SpaceX, Planet Labs</td>\n",
       "            <td>Success</td>\n",
       "            <td>Success</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>13/06/2020</td>\n",
       "            <td>9:21:00</td>\n",
       "            <td>F9 B5 B1059.3</td>\n",
       "            <td>CCAFS SLC-40</td>\n",
       "            <td>Starlink 8 v1.0, SkySats-16, -17, -18, GPS III-03 </td>\n",
       "            <td>15410.0</td>\n",
       "            <td>LEO</td>\n",
       "            <td>SpaceX, Planet Labs</td>\n",
       "            <td>Success</td>\n",
       "            <td>Success</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>30/06/2020</td>\n",
       "            <td>20:10:46</td>\n",
       "            <td>F9 B5B1060.1</td>\n",
       "            <td>CCAFS SLC-40</td>\n",
       "            <td>GPS III-03, ANASIS-II</td>\n",
       "            <td>4311.0</td>\n",
       "            <td>MEO</td>\n",
       "            <td>U.S. Space Force</td>\n",
       "            <td>Success</td>\n",
       "            <td>Success</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>20/07/2020</td>\n",
       "            <td>21:30:00</td>\n",
       "            <td>F9 B5 B1058.2 </td>\n",
       "            <td>CCAFS SLC-40</td>\n",
       "            <td>ANASIS-II, Starlink 9 v1.0</td>\n",
       "            <td>5500.0</td>\n",
       "            <td>GTO</td>\n",
       "            <td>Republic of Korea Army, Spaceflight Industries (BlackSky)</td>\n",
       "            <td>Success</td>\n",
       "            <td>Success</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>18/08/2020</td>\n",
       "            <td>14:31:00</td>\n",
       "            <td>F9 B5 B1049.6</td>\n",
       "            <td>CCAFS SLC-40</td>\n",
       "            <td>Starlink 10 v1.0, SkySat-19, -20, -21, SAOCOM 1B </td>\n",
       "            <td>15440.0</td>\n",
       "            <td>LEO</td>\n",
       "            <td>SpaceX, Planet Labs, PlanetIQ</td>\n",
       "            <td>Success</td>\n",
       "            <td>Success</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>30/08/2020</td>\n",
       "            <td>23:18:00</td>\n",
       "            <td>F9 B5 B1059.4</td>\n",
       "            <td>CCAFS SLC-40</td>\n",
       "            <td>SAOCOM 1B, GNOMES 1, Tyvak-0172</td>\n",
       "            <td>3130.0</td>\n",
       "            <td>SSO</td>\n",
       "            <td>CONAE, PlanetIQ, SpaceX</td>\n",
       "            <td>Success</td>\n",
       "            <td>Success</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>24/10/2020</td>\n",
       "            <td>15:31:34</td>\n",
       "            <td>F9 B5 B1060.3</td>\n",
       "            <td>CCAFS SLC-40</td>\n",
       "            <td>Starlink 14 v1.0, GPS III-04  </td>\n",
       "            <td>15600.0</td>\n",
       "            <td>LEO</td>\n",
       "            <td>SpaceX</td>\n",
       "            <td>Success</td>\n",
       "            <td>Success</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>11/05/2020</td>\n",
       "            <td>23:24:23</td>\n",
       "            <td>F9 B5B1062.1</td>\n",
       "            <td>CCAFS SLC-40</td>\n",
       "            <td>GPS III-04 , Crew-1</td>\n",
       "            <td>4311.0</td>\n",
       "            <td>MEO</td>\n",
       "            <td>USSF</td>\n",
       "            <td>Success</td>\n",
       "            <td>Success</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>25/11/2020</td>\n",
       "            <td>2:13:00</td>\n",
       "            <td>F9 B5 B1049.7 </td>\n",
       "            <td>CCAFS SLC-40</td>\n",
       "            <td>Starlink 15 v1.0, SpaceX CRS-21</td>\n",
       "            <td>15600.0</td>\n",
       "            <td>LEO</td>\n",
       "            <td>SpaceX</td>\n",
       "            <td>Success</td>\n",
       "            <td>Success</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('15/12/2017', '15:36:00', 'F9 FT  B1035.2', 'CCAFS SLC-40', 'SpaceX CRS-13', 2205.0, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'Success (ground pad)'),\n",
       " ('01/08/2018', '1:00:00', 'F9 B4 B1043.1', 'CCAFS SLC-40', 'Zuma', 5000.0, 'LEO', 'Northrop Grumman', 'Success (payload status unclear)', 'Success (ground pad)'),\n",
       " ('31/01/2018', '21:25:00', 'F9 FT  B1032.2', 'CCAFS SLC-40', 'GovSat-1 / SES-16', 4230.0, 'GTO', 'SES', 'Success', 'Controlled (ocean)'),\n",
       " ('03/06/2018', '5:33:00', 'F9 B4 B1044', 'CCAFS SLC-40', 'Hispasat 30W-6  PODSat', 6092.0, 'GTO', 'Hispasat  NovaWurks', 'Success', 'No attempt'),\n",
       " ('04/02/2018', '20:30:00', 'F9 B4  B1039.2', 'CCAFS SLC-40', 'SpaceX CRS-14', 2647.0, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'No attempt'),\n",
       " ('18/04/2018', '22:51:00', 'F9 B4 B1045.1', 'CCAFS SLC-40', 'Transiting Exoplanet Survey Satellite (TESS)', 362.0, 'HEO', 'NASA (LSP)', 'Success', 'Success (drone ship)'),\n",
       " ('06/04/2018', '4:45:00', 'F9 B4  B1040.2', 'CCAFS SLC-40', 'SES-12', 5384.0, 'GTO', 'SES', 'Success', 'No attempt'),\n",
       " ('29/06/2018', '9:42:00', 'F9 B4 B1045.2', 'CCAFS SLC-40', 'SpaceX CRS-15', 2697.0, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'No attempt'),\n",
       " ('22/07/2018', '5:50:00', 'F9 B5B1047.1', 'CCAFS SLC-40', 'Telstar 19V', 7075.0, 'GTO', 'Telesat', 'Success', 'Success'),\n",
       " ('08/07/2018', '5:18:00', 'F9 B5 B1046.2', 'CCAFS SLC-40', 'Merah Putih ', 5800.0, 'GTO', 'Telkom Indonesia', 'Success', 'Success'),\n",
       " ('09/10/2018', '4:45:00', 'F9 B5B1049.1', 'CCAFS SLC-40', 'Telstar 18V / Apstar-5C', 7060.0, 'GTO', 'Telesat', 'Success', 'Success'),\n",
       " ('12/05/2018', '18:16:00', 'F9 B5B1050', 'CCAFS SLC-40', 'SpaceX CRS-16', 2500.0, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'Failure'),\n",
       " ('23/12/2018', '13:51:00', 'F9 B5B1054', 'CCAFS SLC-40', 'GPS III-01 ', 4400.0, 'MEO', 'USAF', 'Success ', 'No attempt'),\n",
       " ('22/02/2019', '1:45:00', 'F9 B5 B1048.3', 'CCAFS SLC-40', 'Nusantara Satu, Beresheet Moon lander, S5', 4850.0, 'GTO', 'PSN, SpaceIL / IAI', 'Success', 'Success'),\n",
       " ('05/04/2019', '6:48:00', 'F9 B5B1056.1 ', 'CCAFS SLC-40', 'SpaceX CRS-17, Starlink v0.9', 2495.0, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'Success'),\n",
       " ('24/05/2019', '2:30:00', 'F9 B5 B1049.3', 'CCAFS SLC-40', 'Starlink v0.9, RADARSAT Constellation', 13620.0, 'LEO', 'SpaceX', 'Success', 'Success'),\n",
       " ('25/07/2019', '22:01:00', 'F9 B5 B1056.2 ', 'CCAFS SLC-40', 'SpaceX CRS-18, AMOS-17 ', 2268.0, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'Success'),\n",
       " ('08/06/2019', '23:23:00', 'F9 B5 B1047.3 ', 'CCAFS SLC-40', 'AMOS-17, Starlink 1 v1.0 ', 6500.0, 'GTO', 'Spacecom', 'Success', 'No attempt '),\n",
       " ('11/11/2019', '14:56:00', 'F9 B5 B1048.4', 'CCAFS SLC-40', 'Starlink 1 v1.0, SpaceX CRS-19 ', 15600.0, 'LEO', 'SpaceX', 'Success', 'Success'),\n",
       " ('12/05/2019', '17:29:00', 'F9 B5B1059.1', 'CCAFS SLC-40', 'SpaceX CRS-19, JCSat-18 / Kacific 1 ', 2617.0, 'LEO (ISS)', 'NASA (CRS), Kacific 1', 'Success', 'Success'),\n",
       " ('17/12/2019', '0:10:00', 'F9 B5 B1056.3 ', 'CCAFS SLC-40', 'JCSat-18 / Kacific 1, Starlink 2 v1.0 ', 6956.0, 'GTO', 'Sky Perfect JSAT, Kacific 1', 'Success', 'Success'),\n",
       " ('01/07/2020', '2:33:00', 'F9 B5 B1049.4', 'CCAFS SLC-40', 'Starlink 2 v1.0, Crew Dragon in-flight abort test ', 15600.0, 'LEO', 'SpaceX', 'Success', 'Success'),\n",
       " ('29/01/2020', '14:07:00', 'F9 B5 B1051.3', 'CCAFS SLC-40', 'Starlink 3 v1.0, Starlink 4 v1.0 ', 15600.0, 'LEO', 'SpaceX', 'Success', 'Success'),\n",
       " ('17/02/2020', '15:05:00', 'F9 B5 B1056.4', 'CCAFS SLC-40', 'Starlink 4 v1.0, SpaceX CRS-20', 15600.0, 'LEO', 'SpaceX', 'Success', 'Failure'),\n",
       " ('03/07/2020', '4:50:00', 'F9 B5 B1059.2', 'CCAFS SLC-40', 'SpaceX CRS-20, Starlink 5 v1.0 ', 1977.0, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'Success'),\n",
       " ('06/04/2020', '1:25:00', 'F9 B5 B1049.5', 'CCAFS SLC-40', 'Starlink 7 v1.0, Starlink 8 v1.0', 15600.0, 'LEO', 'SpaceX, Planet Labs', 'Success', 'Success'),\n",
       " ('13/06/2020', '9:21:00', 'F9 B5 B1059.3', 'CCAFS SLC-40', 'Starlink 8 v1.0, SkySats-16, -17, -18, GPS III-03 ', 15410.0, 'LEO', 'SpaceX, Planet Labs', 'Success', 'Success'),\n",
       " ('30/06/2020', '20:10:46', 'F9 B5B1060.1', 'CCAFS SLC-40', 'GPS III-03, ANASIS-II', 4311.0, 'MEO', 'U.S. Space Force', 'Success', 'Success'),\n",
       " ('20/07/2020', '21:30:00', 'F9 B5 B1058.2 ', 'CCAFS SLC-40', 'ANASIS-II, Starlink 9 v1.0', 5500.0, 'GTO', 'Republic of Korea Army, Spaceflight Industries (BlackSky)', 'Success', 'Success'),\n",
       " ('18/08/2020', '14:31:00', 'F9 B5 B1049.6', 'CCAFS SLC-40', 'Starlink 10 v1.0, SkySat-19, -20, -21, SAOCOM 1B ', 15440.0, 'LEO', 'SpaceX, Planet Labs, PlanetIQ', 'Success', 'Success'),\n",
       " ('30/08/2020', '23:18:00', 'F9 B5 B1059.4', 'CCAFS SLC-40', 'SAOCOM 1B, GNOMES 1, Tyvak-0172', 3130.0, 'SSO', 'CONAE, PlanetIQ, SpaceX', 'Success', 'Success'),\n",
       " ('24/10/2020', '15:31:34', 'F9 B5 B1060.3', 'CCAFS SLC-40', 'Starlink 14 v1.0, GPS III-04  ', 15600.0, 'LEO', 'SpaceX', 'Success', 'Success'),\n",
       " ('11/05/2020', '23:24:23', 'F9 B5B1062.1', 'CCAFS SLC-40', 'GPS III-04 , Crew-1', 4311.0, 'MEO', 'USSF', 'Success', 'Success'),\n",
       " ('25/11/2020', '2:13:00', 'F9 B5 B1049.7 ', 'CCAFS SLC-40', 'Starlink 15 v1.0, SpaceX CRS-21', 15600.0, 'LEO', 'SpaceX', 'Success', 'Success')]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select * from SPACEXTBL where Launch_Site = 'CCAFS SLC-40'"
   ]
  },
  {
   "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": [
    "## Change log\n",
    "| Date | Version | Changed by | Change Description |\n",
    "|------|--------|--------|---------|\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"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## <h3 align=\"center\"> © IBM Corporation 2021. All rights reserved. <h3/>\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python",
   "language": "python",
   "name": "conda-env-python-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}