4. Space-X EDA Using SQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style=\"text-align:center\">\n",
    " \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",
    "\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",
    "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?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDS0321ENSkillsNetwork26802033-2022-01-01\" target=\"_blank\">Spacex DataSet</a>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Store the dataset in database table\n",
    "\n",
    "**it is highly recommended to manually load the table using the database console LOAD tool in DB2**.\n",
    "\n",
    "<img src = \"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/module_2/images/spacexload.png\">\n",
    "\n",
    "Now open the Db2 console, open the LOAD tool, Select / Drag the .CSV file for the  dataset, Next create a New Table, and then follow the steps on-screen instructions to load the data. Name the new table as follows:\n",
    "\n",
    "**SPACEXDATASET**\n",
    "\n",
    "**Follow these steps while using old DB2 UI which is having Open Console Screen**\n",
    "\n",
    "**Note:While loading Spacex dataset, ensure that detect datatypes is disabled. Later click on the pencil icon(edit option).**\n",
    "\n",
    "1.  Change the Date Format by manually typing DD-MM-YYYY and timestamp format as DD-MM-YYYY HH\\:MM:SS\n",
    "\n",
    "2.  Change the PAYLOAD_MASS\\_\\_KG\\_  datatype  to INTEGER.\n",
    "\n",
    "<img src = \"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/module_2/images/spacexload2.png\">\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Changes to be considered when having DB2 instance with the new UI having Go to UI screen**\n",
    "\n",
    "*   Refer to this insruction in this <a href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Labs_Coursera_V5/labs/Lab%20-%20Sign%20up%20for%20IBM%20Cloud%20-%20Create%20Db2%20service%20instance%20-%20Get%20started%20with%20the%20Db2%20console/instructional-labs.md.html?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDS0321ENSkillsNetwork26802033-2022-01-01\">link</a> for viewing  the new  Go to UI screen.\n",
    "\n",
    "*   Later click on **Data link(below SQL)**  in the Go to UI screen  and click on **Load Data** tab.\n",
    "\n",
    "*   Later browse for the downloaded spacex file.\n",
    "\n",
    "<img src=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/module_2/images/browsefile.png\" width=\"800\"/>\n",
    "\n",
    "*   Once done select the schema andload the file.\n",
    "\n",
    " <img src=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/module_2/images/spacexload3.png\" width=\"800\"/>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "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[31m56.2 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m00:01\u001b[0m00: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=1159122 sha256=b202fc9fde079277e7372c3db291b04dad6050acf4c36e0f28b09e954065cc95\n",
      "  Stored in directory: /home/jupyterlab/.cache/pip/wheels/ef/95/ac/c232f83b415900c26553c64266e1a2b2863bc63e7a5d606c7e\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": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv, sqlite3\n",
    "\n",
    "con = sqlite3.connect(\"my_data1.db\")\n",
    "cur = con.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip install -q pandas==1.1.5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: @my_data1.db'"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql sqlite:///my_data1.db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "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": {},
   "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",
    "##### Display the names of the unique launch sites  in the space mission\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>Launch_Sites</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": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT DISTINCT LAUNCH_SITE as \"Launch_Sites\" FROM SPACEXTBL;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 2\n",
    "\n",
    "##### Display 5 records where launch sites begin with the string 'CCA'\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "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>04-06-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</td>\n",
       "            <td>LEO</td>\n",
       "            <td>SpaceX</td>\n",
       "            <td>Success</td>\n",
       "            <td>Failure (parachute)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>08-12-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</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>07: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>08-10-2012</td>\n",
       "            <td>00: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>01-03-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</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": [
       "[('04-06-2010', '18:45:00', 'F9 v1.0  B0003', 'CCAFS LC-40', 'Dragon Spacecraft Qualification Unit', 0, 'LEO', 'SpaceX', 'Success', 'Failure (parachute)'),\n",
       " ('08-12-2010', '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",
       " ('22-05-2012', '07:44:00', 'F9 v1.0  B0005', 'CCAFS LC-40', 'Dragon demo flight C2', 525, 'LEO (ISS)', 'NASA (COTS)', 'Success', 'No attempt'),\n",
       " ('08-10-2012', '00:35:00', 'F9 v1.0  B0006', 'CCAFS LC-40', 'SpaceX CRS-1', 500, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'No attempt'),\n",
       " ('01-03-2013', '15:10:00', 'F9 v1.0  B0007', 'CCAFS LC-40', 'SpaceX CRS-2', 677, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'No attempt')]"
      ]
     },
     "execution_count": 72,
     "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",
    "##### Display the total payload mass carried by boosters launched by NASA (CRS)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>Total Payload Mass(Kgs)</th>\n",
       "            <th>Customer</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>45596</td>\n",
       "            <td>NASA (CRS)</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(45596, 'NASA (CRS)')]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT SUM(PAYLOAD_MASS__KG_) as \"Total Payload Mass(Kgs)\", Customer FROM 'SPACEXTBL' WHERE Customer = 'NASA (CRS)';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 4\n",
    "\n",
    "##### Display average payload mass carried by booster version F9 v1.1\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>Payload Mass Kgs</th>\n",
       "            <th>Customer</th>\n",
       "            <th>Booster_Version</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>2534.6666666666665</td>\n",
       "            <td>MDA</td>\n",
       "            <td>F9 v1.1  B1003</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2534.6666666666665, 'MDA', 'F9 v1.1  B1003')]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT AVG(PAYLOAD_MASS__KG_) as \"Payload Mass Kgs\", Customer, Booster_Version 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",
    "*Hint:Use min function*\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>01-05-2017</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('01-05-2017',)]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT MIN(DATE) FROM 'SPACEXTBL' WHERE \"Landing _Outcome\" = \"Success (ground pad)\";\n"
   ]
  },
  {
   "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": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# %sql SELECT * FROM 'SPACEXTBL'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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",
       "            <th>Payload</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>F9 FT B1022</td>\n",
       "            <td>JCSAT-14</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT B1026</td>\n",
       "            <td>JCSAT-16</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT  B1021.2</td>\n",
       "            <td>SES-10</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT  B1031.2</td>\n",
       "            <td>SES-11 / EchoStar 105</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('F9 FT B1022', 'JCSAT-14'),\n",
       " ('F9 FT B1026', 'JCSAT-16'),\n",
       " ('F9 FT  B1021.2', 'SES-10'),\n",
       " ('F9 FT  B1031.2', 'SES-11 / EchoStar 105')]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT DISTINCT Booster_Version, Payload FROM SPACEXTBL WHERE \"Landing _Outcome\" = \"Success (drone ship)\" AND PAYLOAD_MASS__KG_ > 4000 AND PAYLOAD_MASS__KG_ < 6000;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 7\n",
    "\n",
    "##### List the total number of successful and failure mission outcomes\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>Total</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": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT \"Mission_Outcome\", COUNT(\"Mission_Outcome\") as Total FROM SPACEXTBL GROUP BY \"Mission_Outcome\";"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 8\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": 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>Booster_Version</th>\n",
       "            <th>Payload</th>\n",
       "            <th>PAYLOAD_MASS__KG_</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1048.4</td>\n",
       "            <td>Starlink 1 v1.0, SpaceX CRS-19 </td>\n",
       "            <td>15600</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1049.4</td>\n",
       "            <td>Starlink 2 v1.0, Crew Dragon in-flight abort test </td>\n",
       "            <td>15600</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1051.3</td>\n",
       "            <td>Starlink 3 v1.0, Starlink 4 v1.0 </td>\n",
       "            <td>15600</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1056.4</td>\n",
       "            <td>Starlink 4 v1.0, SpaceX CRS-20</td>\n",
       "            <td>15600</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1048.5</td>\n",
       "            <td>Starlink 5 v1.0, Starlink 6 v1.0 </td>\n",
       "            <td>15600</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1051.4</td>\n",
       "            <td>Starlink 6 v1.0, Crew Dragon Demo-2 </td>\n",
       "            <td>15600</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1049.5</td>\n",
       "            <td>Starlink 7 v1.0, Starlink 8 v1.0</td>\n",
       "            <td>15600</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1060.2 </td>\n",
       "            <td>Starlink 11 v1.0, Starlink 12 v1.0 </td>\n",
       "            <td>15600</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1058.3 </td>\n",
       "            <td>Starlink 12 v1.0, Starlink 13 v1.0 </td>\n",
       "            <td>15600</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1051.6</td>\n",
       "            <td>Starlink 13 v1.0, Starlink 14 v1.0 </td>\n",
       "            <td>15600</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1060.3</td>\n",
       "            <td>Starlink 14 v1.0, GPS III-04  </td>\n",
       "            <td>15600</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1049.7 </td>\n",
       "            <td>Starlink 15 v1.0, SpaceX CRS-21</td>\n",
       "            <td>15600</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('F9 B5 B1048.4', 'Starlink 1 v1.0, SpaceX CRS-19 ', 15600),\n",
       " ('F9 B5 B1049.4', 'Starlink 2 v1.0, Crew Dragon in-flight abort test ', 15600),\n",
       " ('F9 B5 B1051.3', 'Starlink 3 v1.0, Starlink 4 v1.0 ', 15600),\n",
       " ('F9 B5 B1056.4', 'Starlink 4 v1.0, SpaceX CRS-20', 15600),\n",
       " ('F9 B5 B1048.5', 'Starlink 5 v1.0, Starlink 6 v1.0 ', 15600),\n",
       " ('F9 B5 B1051.4', 'Starlink 6 v1.0, Crew Dragon Demo-2 ', 15600),\n",
       " ('F9 B5 B1049.5', 'Starlink 7 v1.0, Starlink 8 v1.0', 15600),\n",
       " ('F9 B5 B1060.2 ', 'Starlink 11 v1.0, Starlink 12 v1.0 ', 15600),\n",
       " ('F9 B5 B1058.3 ', 'Starlink 12 v1.0, Starlink 13 v1.0 ', 15600),\n",
       " ('F9 B5 B1051.6', 'Starlink 13 v1.0, Starlink 14 v1.0 ', 15600),\n",
       " ('F9 B5 B1060.3', 'Starlink 14 v1.0, GPS III-04  ', 15600),\n",
       " ('F9 B5 B1049.7 ', 'Starlink 15 v1.0, SpaceX CRS-21', 15600)]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT \"Booster_Version\",Payload, \"PAYLOAD_MASS__KG_\" FROM SPACEXTBL WHERE \"PAYLOAD_MASS__KG_\" = (SELECT MAX(\"PAYLOAD_MASS__KG_\") FROM SPACEXTBL);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 9\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": 68,
   "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>substr(Date,7,4)</th>\n",
       "            <th>substr(Date, 4, 2)</th>\n",
       "            <th>Booster_Version</th>\n",
       "            <th>Launch_Site</th>\n",
       "            <th>Payload</th>\n",
       "            <th>PAYLOAD_MASS__KG_</th>\n",
       "            <th>Mission_Outcome</th>\n",
       "            <th>Landing _Outcome</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>2015</td>\n",
       "            <td>01</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>Success</td>\n",
       "            <td>Failure (drone ship)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2015</td>\n",
       "            <td>04</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>Success</td>\n",
       "            <td>Failure (drone ship)</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('2015', '01', 'F9 v1.1 B1012', 'CCAFS LC-40', 'SpaceX CRS-5', 2395, 'Success', 'Failure (drone ship)'),\n",
       " ('2015', '04', 'F9 v1.1 B1015', 'CCAFS LC-40', 'SpaceX CRS-6', 1898, 'Success', 'Failure (drone ship)')]"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT substr(Date,7,4), substr(Date, 4, 2),\"Booster_Version\", \"Launch_Site\", Payload, \"PAYLOAD_MASS__KG_\", \"Mission_Outcome\", \"Landing _Outcome\" FROM SPACEXTBL WHERE substr(Date,7,4)='2015' AND \"Landing _Outcome\" = 'Failure (drone ship)';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 10\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": 74,
   "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>19-02-2017</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",
       "            <td>18-10-2020</td>\n",
       "            <td>12:25:57</td>\n",
       "            <td>F9 B5 B1051.6</td>\n",
       "            <td>KSC LC-39A</td>\n",
       "            <td>Starlink 13 v1.0, Starlink 14 v1.0 </td>\n",
       "            <td>15600</td>\n",
       "            <td>LEO</td>\n",
       "            <td>SpaceX</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</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>18-07-2016</td>\n",
       "            <td>04: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",
       "            <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</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>17-12-2019</td>\n",
       "            <td>00: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</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>16-11-2020</td>\n",
       "            <td>00:27:00</td>\n",
       "            <td>F9 B5B1061.1 </td>\n",
       "            <td>KSC LC-39A</td>\n",
       "            <td>Crew-1, Sentinel-6 Michael Freilich </td>\n",
       "            <td>12500</td>\n",
       "            <td>LEO (ISS)</td>\n",
       "            <td>NASA (CCP)</td>\n",
       "            <td>Success</td>\n",
       "            <td>Success</td>\n",
       "        </tr>\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</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>15-11-2018</td>\n",
       "            <td>20:46:00</td>\n",
       "            <td>F9 B5 B1047.2</td>\n",
       "            <td>KSC LC-39A</td>\n",
       "            <td>Es hail 2</td>\n",
       "            <td>5300</td>\n",
       "            <td>GTO</td>\n",
       "            <td>Es hailSat</td>\n",
       "            <td>Success</td>\n",
       "            <td>Success</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>14-08-2017</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",
       "            <td>14-08-2016</td>\n",
       "            <td>05: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",
       "            <td>14-01-2017</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",
       "            <td>13-06-2020</td>\n",
       "            <td>09: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</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>12-06-2019</td>\n",
       "            <td>14:17:00</td>\n",
       "            <td>F9 B5 B1051.2 </td>\n",
       "            <td>VAFB SLC-4E</td>\n",
       "            <td>RADARSAT Constellation, SpaceX CRS-18 </td>\n",
       "            <td>4200</td>\n",
       "            <td>SSO</td>\n",
       "            <td>Canadian Space Agency (CSA)</td>\n",
       "            <td>Success</td>\n",
       "            <td>Success</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</td>\n",
       "            <td>LEO</td>\n",
       "            <td>SpaceX</td>\n",
       "            <td>Success</td>\n",
       "            <td>Success</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>11-10-2017</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",
       "            <td>11-05-2018</td>\n",
       "            <td>20:14:00</td>\n",
       "            <td>F9 B5  B1046.1</td>\n",
       "            <td>KSC LC-39A</td>\n",
       "            <td>Bangabandhu-1</td>\n",
       "            <td>3600</td>\n",
       "            <td>GTO</td>\n",
       "            <td>Thales-Alenia/BTRC</td>\n",
       "            <td>Success</td>\n",
       "            <td>Success (drone ship)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>11-01-2019</td>\n",
       "            <td>15:31:00</td>\n",
       "            <td>F9 B5 B1049.2</td>\n",
       "            <td>VAFB SLC-4E</td>\n",
       "            <td>Iridium NEXT-8</td>\n",
       "            <td>9600</td>\n",
       "            <td>Polar LEO</td>\n",
       "            <td>Iridium Communications</td>\n",
       "            <td>Success</td>\n",
       "            <td>Success</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>10-09-2018</td>\n",
       "            <td>04: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</td>\n",
       "            <td>GTO</td>\n",
       "            <td>Telesat</td>\n",
       "            <td>Success</td>\n",
       "            <td>Success</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>09-10-2017</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",
       "            <td>08-10-2018</td>\n",
       "            <td>02:22:00</td>\n",
       "            <td>F9 B5 B1048.2</td>\n",
       "            <td>VAFB SLC-4E</td>\n",
       "            <td>SAOCOM 1A</td>\n",
       "            <td>3000</td>\n",
       "            <td>SSO</td>\n",
       "            <td>CONAE</td>\n",
       "            <td>Success</td>\n",
       "            <td>Success</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>08-04-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</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>08-01-2018</td>\n",
       "            <td>01: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",
       "            <td>07-09-2017</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",
       "            <td>07-08-2020</td>\n",
       "            <td>05:12:00</td>\n",
       "            <td>F9 B5 B1051.5</td>\n",
       "            <td>KSC LC-39A</td>\n",
       "            <td>Starlink 9 v1.0, SXRS-1, Starlink 10 v1.0 </td>\n",
       "            <td>14932</td>\n",
       "            <td>LEO</td>\n",
       "            <td>SpaceX, Spaceflight Industries (BlackSky), Planet Labs</td>\n",
       "            <td>Success</td>\n",
       "            <td>Success</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>07-08-2018</td>\n",
       "            <td>05:18:00</td>\n",
       "            <td>F9 B5 B1046.2</td>\n",
       "            <td>CCAFS SLC-40</td>\n",
       "            <td>Merah Putih </td>\n",
       "            <td>5800</td>\n",
       "            <td>GTO</td>\n",
       "            <td>Telkom Indonesia</td>\n",
       "            <td>Success</td>\n",
       "            <td>Success</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>07-03-2020</td>\n",
       "            <td>04: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</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>07-01-2020</td>\n",
       "            <td>02: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</td>\n",
       "            <td>LEO</td>\n",
       "            <td>SpaceX</td>\n",
       "            <td>Success</td>\n",
       "            <td>Success</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>06-12-2020</td>\n",
       "            <td>16:17:08</td>\n",
       "            <td>F9 B5 B1058.4 </td>\n",
       "            <td>KSC LC-39A</td>\n",
       "            <td>SpaceX CRS-21</td>\n",
       "            <td>2972</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-10-2020</td>\n",
       "            <td>11:29:34</td>\n",
       "            <td>F9 B5 B1058.3 </td>\n",
       "            <td>KSC LC-39A</td>\n",
       "            <td>Starlink 12 v1.0, Starlink 13 v1.0 </td>\n",
       "            <td>15600</td>\n",
       "            <td>LEO</td>\n",
       "            <td>SpaceX</td>\n",
       "            <td>Success</td>\n",
       "            <td>Success</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>06-05-2016</td>\n",
       "            <td>05: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",
       "            <td>05-12-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</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>05-11-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</td>\n",
       "            <td>MEO</td>\n",
       "            <td>USSF</td>\n",
       "            <td>Success</td>\n",
       "            <td>Success</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>04-06-2020</td>\n",
       "            <td>01: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</td>\n",
       "            <td>LEO</td>\n",
       "            <td>SpaceX, Planet Labs</td>\n",
       "            <td>Success</td>\n",
       "            <td>Success</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('19-02-2017', '14:39:00', 'F9 FT B1031.1', 'KSC LC-39A', 'SpaceX CRS-10', 2490, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'Success (ground pad)'),\n",
       " ('18-10-2020', '12:25:57', 'F9 B5 B1051.6', 'KSC LC-39A', 'Starlink 13 v1.0, Starlink 14 v1.0 ', 15600, 'LEO', 'SpaceX', '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, 'LEO', 'SpaceX, Planet Labs, PlanetIQ', 'Success', 'Success'),\n",
       " ('18-07-2016', '04:45:00', 'F9 FT B1025.1', 'CCAFS LC-40', 'SpaceX CRS-9', 2257, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'Success (ground pad)'),\n",
       " ('18-04-2018', '22:51:00', 'F9 B4 B1045.1', 'CCAFS SLC-40', 'Transiting Exoplanet Survey Satellite (TESS)', 362, 'HEO', 'NASA (LSP)', 'Success', 'Success (drone ship)'),\n",
       " ('17-12-2019', '00:10:00', 'F9 B5 B1056.3 ', 'CCAFS SLC-40', 'JCSat-18 / Kacific 1, Starlink 2 v1.0 ', 6956, 'GTO', 'Sky Perfect JSAT, Kacific 1', 'Success', 'Success'),\n",
       " ('16-11-2020', '00:27:00', 'F9 B5B1061.1 ', 'KSC LC-39A', 'Crew-1, Sentinel-6 Michael Freilich ', 12500, 'LEO (ISS)', 'NASA (CCP)', 'Success', 'Success'),\n",
       " ('15-12-2017', '15:36:00', 'F9 FT  B1035.2', 'CCAFS SLC-40', 'SpaceX CRS-13', 2205, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'Success (ground pad)'),\n",
       " ('15-11-2018', '20:46:00', 'F9 B5 B1047.2', 'KSC LC-39A', 'Es hail 2', 5300, 'GTO', 'Es hailSat', 'Success', 'Success'),\n",
       " ('14-08-2017', '16:31:00', 'F9 B4 B1039.1', 'KSC LC-39A', 'SpaceX CRS-12', 3310, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'Success (ground pad)'),\n",
       " ('14-08-2016', '05:26:00', 'F9 FT B1026', 'CCAFS LC-40', 'JCSAT-16', 4600, 'GTO', 'SKY Perfect JSAT Group', 'Success', 'Success (drone ship)'),\n",
       " ('14-01-2017', '17:54:00', 'F9 FT B1029.1', 'VAFB SLC-4E', 'Iridium NEXT 1', 9600, 'Polar LEO', 'Iridium Communications', 'Success', 'Success (drone ship)'),\n",
       " ('13-06-2020', '09:21:00', 'F9 B5 B1059.3', 'CCAFS SLC-40', 'Starlink 8 v1.0, SkySats-16, -17, -18, GPS III-03 ', 15410, 'LEO', 'SpaceX, Planet Labs', 'Success', 'Success'),\n",
       " ('12-06-2019', '14:17:00', 'F9 B5 B1051.2 ', 'VAFB SLC-4E', 'RADARSAT Constellation, SpaceX CRS-18 ', 4200, 'SSO', 'Canadian Space Agency (CSA)', 'Success', 'Success'),\n",
       " ('11-11-2019', '14:56:00', 'F9 B5 B1048.4', 'CCAFS SLC-40', 'Starlink 1 v1.0, SpaceX CRS-19 ', 15600, 'LEO', 'SpaceX', 'Success', 'Success'),\n",
       " ('11-10-2017', '22:53:00', 'F9 FT  B1031.2', 'KSC LC-39A', 'SES-11 / EchoStar 105', 5200, 'GTO', 'SES EchoStar', 'Success', 'Success (drone ship)'),\n",
       " ('11-05-2018', '20:14:00', 'F9 B5  B1046.1', 'KSC LC-39A', 'Bangabandhu-1', 3600, 'GTO', 'Thales-Alenia/BTRC', 'Success', 'Success (drone ship)'),\n",
       " ('11-01-2019', '15:31:00', 'F9 B5 B1049.2', 'VAFB SLC-4E', 'Iridium NEXT-8', 9600, 'Polar LEO', 'Iridium Communications', 'Success', 'Success'),\n",
       " ('10-09-2018', '04:45:00', 'F9 B5B1049.1', 'CCAFS SLC-40', 'Telstar 18V / Apstar-5C', 7060, 'GTO', 'Telesat', 'Success', 'Success'),\n",
       " ('09-10-2017', '12:37:00', 'F9 B4 B1041.1', 'VAFB SLC-4E', 'Iridium NEXT 3', 9600, 'Polar LEO', 'Iridium Communications', 'Success', 'Success (drone ship)'),\n",
       " ('08-10-2018', '02:22:00', 'F9 B5 B1048.2', 'VAFB SLC-4E', 'SAOCOM 1A', 3000, 'SSO', 'CONAE', 'Success', 'Success'),\n",
       " ('08-04-2016', '20:43:00', 'F9 FT B1021.1', 'CCAFS LC-40', 'SpaceX CRS-8', 3136, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'Success (drone ship)'),\n",
       " ('08-01-2018', '01:00:00', 'F9 B4 B1043.1', 'CCAFS SLC-40', 'Zuma', 5000, 'LEO', 'Northrop Grumman', 'Success (payload status unclear)', 'Success (ground pad)'),\n",
       " ('07-09-2017', '14:00:00', 'F9 B4 B1040.1', 'KSC LC-39A', 'Boeing X-37B OTV-5', 4990, 'LEO', 'U.S. Air Force', 'Success', 'Success (ground pad)'),\n",
       " ('07-08-2020', '05:12:00', 'F9 B5 B1051.5', 'KSC LC-39A', 'Starlink 9 v1.0, SXRS-1, Starlink 10 v1.0 ', 14932, 'LEO', 'SpaceX, Spaceflight Industries (BlackSky), Planet Labs', 'Success', 'Success'),\n",
       " ('07-08-2018', '05:18:00', 'F9 B5 B1046.2', 'CCAFS SLC-40', 'Merah Putih ', 5800, 'GTO', 'Telkom Indonesia', 'Success', 'Success'),\n",
       " ('07-03-2020', '04:50:00', 'F9 B5 B1059.2', 'CCAFS SLC-40', 'SpaceX CRS-20, Starlink 5 v1.0 ', 1977, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'Success'),\n",
       " ('07-01-2020', '02:33:00', 'F9 B5 B1049.4', 'CCAFS SLC-40', 'Starlink 2 v1.0, Crew Dragon in-flight abort test ', 15600, 'LEO', 'SpaceX', 'Success', 'Success'),\n",
       " ('06-12-2020', '16:17:08', 'F9 B5 B1058.4 ', 'KSC LC-39A', 'SpaceX CRS-21', 2972, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'Success'),\n",
       " ('06-10-2020', '11:29:34', 'F9 B5 B1058.3 ', 'KSC LC-39A', 'Starlink 12 v1.0, Starlink 13 v1.0 ', 15600, 'LEO', 'SpaceX', 'Success', 'Success'),\n",
       " ('06-05-2016', '05:21:00', 'F9 FT B1022', 'CCAFS LC-40', 'JCSAT-14', 4696, 'GTO', 'SKY Perfect JSAT Group', 'Success', 'Success (drone ship)'),\n",
       " ('05-12-2019', '17:29:00', 'F9 B5B1059.1', 'CCAFS SLC-40', 'SpaceX CRS-19, JCSat-18 / Kacific 1 ', 2617, 'LEO (ISS)', 'NASA (CRS), Kacific 1', 'Success', 'Success'),\n",
       " ('05-11-2020', '23:24:23', 'F9 B5B1062.1', 'CCAFS SLC-40', 'GPS III-04 , Crew-1', 4311, 'MEO', 'USSF', 'Success', 'Success'),\n",
       " ('04-06-2020', '01:25:00', 'F9 B5 B1049.5', 'CCAFS SLC-40', 'Starlink 7 v1.0, Starlink 8 v1.0', 15600, 'LEO', 'SpaceX, Planet Labs', 'Success', 'Success')]"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM SPACEXTBL WHERE \"Landing _Outcome\" LIKE 'Success%' AND (Date BETWEEN '04-06-2010' AND '20-03-2017') ORDER BY Date DESC;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "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": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT DISTINCT Launch_Site FROM SPACEXTBL;"
   ]
  },
  {
   "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?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDS0321ENSkillsNetwork26802033-2022-01-01&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?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDS0321ENSkillsNetwork26802033-2022-01-01&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?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDS0321ENSkillsNetwork26802033-2022-01-01&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?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDS0321ENSkillsNetwork26802033-2022-01-01\">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?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDS0321ENSkillsNetwork26802033-2022-01-01\">Hands-on Lab: Analyzing a real World Data Set</a>\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
}