jupyter-labs-eda-sql-coursera_sqllite (2).ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<center>\n",
    "    <img src=\"https://gitlab.com/ibm/skills-network/courses/placeholder101/-/raw/master/labs/module%201/images/IDSNlogo.png\"  width=\"300\" alt=\"cognitiveclass.ai logo\"  />\n",
    "</center>\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-2021-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-2021-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": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: sqlalchemy==1.3.9 in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (1.3.9)\n",
      "Collecting ipython-sql\n",
      "  Downloading ipython_sql-0.4.0-py3-none-any.whl (19 kB)\n",
      "Requirement already satisfied: sqlalchemy>=0.6.7 in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ipython-sql) (1.3.9)\n",
      "Requirement already satisfied: six in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ipython-sql) (1.15.0)\n",
      "Collecting prettytable<1\n",
      "  Downloading prettytable-0.7.2.zip (28 kB)\n",
      "Requirement already satisfied: ipython>=1.0 in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ipython-sql) (7.22.0)\n",
      "Collecting sqlparse\n",
      "  Downloading sqlparse-0.4.2-py3-none-any.whl (42 kB)\n",
      "\u001b[K     |████████████████████████████████| 42 kB 3.0 MB/s  eta 0:00:01\n",
      "\u001b[?25hRequirement already satisfied: ipython-genutils>=0.1.0 in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ipython-sql) (0.2.0)\n",
      "Requirement already satisfied: jedi>=0.16 in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (0.17.2)\n",
      "Requirement already satisfied: pexpect>4.3 in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (4.8.0)\n",
      "Requirement already satisfied: pygments in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (2.9.0)\n",
      "Requirement already satisfied: prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0 in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (3.0.17)\n",
      "Requirement already satisfied: decorator in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (5.0.9)\n",
      "Requirement already satisfied: backcall in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (0.2.0)\n",
      "Requirement already satisfied: setuptools>=18.5 in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (52.0.0.post20211006)\n",
      "Requirement already satisfied: traitlets>=4.2 in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (5.0.5)\n",
      "Requirement already satisfied: pickleshare in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (0.7.5)\n",
      "Requirement already satisfied: parso<0.8.0,>=0.7.0 in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from jedi>=0.16->ipython>=1.0->ipython-sql) (0.7.0)\n",
      "Requirement already satisfied: ptyprocess>=0.5 in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from pexpect>4.3->ipython>=1.0->ipython-sql) (0.7.0)\n",
      "Requirement already satisfied: wcwidth in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0->ipython>=1.0->ipython-sql) (0.2.5)\n",
      "Building wheels for collected packages: prettytable\n",
      "  Building wheel for prettytable (setup.py) ... \u001b[?25ldone\n",
      "\u001b[?25h  Created wheel for prettytable: filename=prettytable-0.7.2-py3-none-any.whl size=13700 sha256=8ac0d8cb918e90b1138f5dfda9588ccf7fe6e7ee5b2ac1eb4a60ad380070d6fa\n",
      "  Stored in directory: /tmp/wsuser/.cache/pip/wheels/48/6d/77/9517cb933af254f51a446f1a5ec9c2be3e45f17384940bce68\n",
      "Successfully built prettytable\n",
      "Installing collected packages: sqlparse, prettytable, ipython-sql\n",
      "Successfully installed ipython-sql-0.4.0 prettytable-0.7.2 sqlparse-0.4.2\n"
     ]
    }
   ],
   "source": [
    "!pip install sqlalchemy==1.3.9\n",
    "!pip install ipython-sql"
   ]
  },
  {
   "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": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv, sqlite3\n",
    "\n",
    "con = sqlite3.connect(\"my_data1.db\")\n",
    "cur = con.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[31mERROR: pip's dependency resolver does not currently take into account all the packages that are installed. This behaviour is the source of the following dependency conflicts.\r\n",
      "sparkmagic 0.18.0 requires nose, which is not installed.\r\n",
      "hdijupyterutils 0.18.0 requires jupyter>=1, which is not installed.\r\n",
      "hdijupyterutils 0.18.0 requires nose, which is not installed.\u001b[0m\r\n"
     ]
    }
   ],
   "source": [
    "!pip install -q pandas==1.1.5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql sqlite:///my_data1.db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages/pandas/core/generic.py:2779: UserWarning: The spaces in these column names will not be changed. In pandas versions < 0.14, spaces were converted to underscores.\n",
      "  name       (index) object 'falcon' 'parrot' 'lion' 'monkey'\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": 74,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Launch_Site</th>\n",
       "    </tr>\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",
       "</table>"
      ],
      "text/plain": [
       "[('CCAFS LC-40',), ('VAFB SLC-4E',), ('KSC LC-39A',), ('CCAFS SLC-40',)]"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select distinct \"Launch_Site\" 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": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\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",
       "    <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",
       "</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": 14,
     "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": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>sum(&quot;PAYLOAD_MASS__KG_&quot;)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>45596</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(45596,)]"
      ]
     },
     "execution_count": 15,
     "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",
    "##### Display average payload mass carried by booster version F9 v1.1\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>avg(&quot;PAYLOAD_MASS__KG_&quot;)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2928.4</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2928.4,)]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select avg(\"PAYLOAD_MASS__KG_\") from SPACEXTBL where \"Booster_Version\"=\"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": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>min(&quot;Date&quot;)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>01-05-2017</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('01-05-2017',)]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select min(\"Date\") from SPACEXTBL where \"Landing _Outcome\" like \"Success (ground pad)\""
   ]
  },
  {
   "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": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Booster_Version</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>F9 FT B1022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>F9 FT B1026</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>F9 FT  B1021.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>F9 FT  B1031.2</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('F9 FT B1022',), ('F9 FT B1026',), ('F9 FT  B1021.2',), ('F9 FT  B1031.2',)]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select \"Booster_Version\" from SPACEXTBL where \"Landing _Outcome\" like \"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": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>count(*)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>61</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(61,)]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select count(*) from SPACEXTBL where  \"Landing _Outcome\" like \"Success%\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>count(*)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(10,)]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select count(*) from SPACEXTBL where  \"Landing _Outcome\" like \"Failure%\""
   ]
  },
  {
   "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": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Booster_Version</th>\n",
       "    </tr>\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",
       "</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": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select distinct \"Booster_Version\" 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": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\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",
       "    <tr>\n",
       "        <td>10-01-2015</td>\n",
       "        <td>09:47:00</td>\n",
       "        <td>F9 v1.1 B1012</td>\n",
       "        <td>CCAFS LC-40</td>\n",
       "        <td>SpaceX CRS-5</td>\n",
       "        <td>2395</td>\n",
       "        <td>LEO (ISS)</td>\n",
       "        <td>NASA (CRS)</td>\n",
       "        <td>Success</td>\n",
       "        <td>Failure (drone ship)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11-02-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</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>02-03-2015</td>\n",
       "        <td>03:50:00</td>\n",
       "        <td>F9 v1.1 B1014</td>\n",
       "        <td>CCAFS LC-40</td>\n",
       "        <td>ABS-3A Eutelsat 115 West B</td>\n",
       "        <td>4159</td>\n",
       "        <td>GTO</td>\n",
       "        <td>ABS Eutelsat</td>\n",
       "        <td>Success</td>\n",
       "        <td>No attempt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <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</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</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</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>01:29:00</td>\n",
       "        <td>F9 FT B1019</td>\n",
       "        <td>CCAFS LC-40</td>\n",
       "        <td>OG2 Mission 2  11 Orbcomm-OG2 satellites</td>\n",
       "        <td>2034</td>\n",
       "        <td>LEO</td>\n",
       "        <td>Orbcomm</td>\n",
       "        <td>Success</td>\n",
       "        <td>Success (ground pad)</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('10-01-2015', '09:47:00', 'F9 v1.1 B1012', 'CCAFS LC-40', 'SpaceX CRS-5', 2395, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'Failure (drone ship)'),\n",
       " ('11-02-2015', '23:03:00', 'F9 v1.1 B1013', 'CCAFS LC-40', 'DSCOVR', 570, 'HEO', 'U.S. Air Force NASA NOAA', 'Success', 'Controlled (ocean)'),\n",
       " ('02-03-2015', '03:50:00', 'F9 v1.1 B1014', 'CCAFS LC-40', 'ABS-3A Eutelsat 115 West B', 4159, 'GTO', 'ABS Eutelsat', 'Success', 'No attempt'),\n",
       " ('14-04-2015', '20:10:00', 'F9 v1.1 B1015', 'CCAFS LC-40', 'SpaceX CRS-6', 1898, '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, '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, 'LEO (ISS)', 'NASA (CRS)', 'Failure (in flight)', 'Precluded (drone ship)'),\n",
       " ('22-12-2015', '01:29:00', 'F9 FT B1019', 'CCAFS LC-40', 'OG2 Mission 2  11 Orbcomm-OG2 satellites', 2034, 'LEO', 'Orbcomm', 'Success', 'Success (ground pad)')]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select * from SPACEXTBL where \"Date\" like \"%2015\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\"Landing _Outcome\" like \"Success\" &"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Date(&quot;Date&quot;)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,),\n",
       " (None,)]"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select \"Date\" from SPACEXTBL Order by \"Date\" desc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "(sqlite3.OperationalError) near \"select\": syntax error\n",
      "[SQL: select * from SPACEXTBL where \"Date\" > \"%2010\" (select * from SPACEXBTL where \"Date\" < \"%2017\" )]\n",
      "(Background on this error at: http://sqlalche.me/e/e3q8)\n"
     ]
    }
   ],
   "source": [
    "%sql select * from SPACEXTBL where \"Date\" > \"%2010\" (select * from SPACEXBTL where \"Date\" < \"%2017\")"
   ]
  },
  {
   "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": 69,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\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",
       "    <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</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>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</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>30-05-2020</td>\n",
       "        <td>19:22:00</td>\n",
       "        <td>F9 B5B1058.1 </td>\n",
       "        <td>KSC LC-39A</td>\n",
       "        <td>Crew Dragon Demo-2, Starlink 7 v1.0 </td>\n",
       "        <td>12530</td>\n",
       "        <td>LEO (ISS)</td>\n",
       "        <td>NASA (CCDev)</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</td>\n",
       "        <td>LEO</td>\n",
       "        <td>SpaceX</td>\n",
       "        <td>Success</td>\n",
       "        <td>Success</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>25-11-2020</td>\n",
       "        <td>02: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</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</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>25-07-2018</td>\n",
       "        <td>11:39:00</td>\n",
       "        <td>F9 B5B1048.1</td>\n",
       "        <td>VAFB SLC-4E</td>\n",
       "        <td>Iridium NEXT-7</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>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</td>\n",
       "        <td>LEO</td>\n",
       "        <td>SpaceX</td>\n",
       "        <td>Success</td>\n",
       "        <td>Success</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>24-05-2019</td>\n",
       "        <td>02: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</td>\n",
       "        <td>LEO</td>\n",
       "        <td>SpaceX</td>\n",
       "        <td>Success</td>\n",
       "        <td>Success</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>22-07-2018</td>\n",
       "        <td>05:50:00</td>\n",
       "        <td>F9 B5B1047.1</td>\n",
       "        <td>CCAFS SLC-40</td>\n",
       "        <td>Telstar 19V</td>\n",
       "        <td>7075</td>\n",
       "        <td>GTO</td>\n",
       "        <td>Telesat</td>\n",
       "        <td>Success</td>\n",
       "        <td>Success</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>22-04-2020</td>\n",
       "        <td>19:30:00</td>\n",
       "        <td>F9 B5 B1051.4</td>\n",
       "        <td>KSC LC-39A</td>\n",
       "        <td>Starlink 6 v1.0, Crew Dragon Demo-2 </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>22-02-2019</td>\n",
       "        <td>01: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</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>21-11-2020</td>\n",
       "        <td>17:17:08</td>\n",
       "        <td>F9 B5B1063.1</td>\n",
       "        <td>VAFB SLC-4E</td>\n",
       "        <td>Sentinel-6 Michael Freilich, Starlink 15 v1.0 </td>\n",
       "        <td>1192</td>\n",
       "        <td>LEO</td>\n",
       "        <td>NASA / NOAA / ESA / EUMETSAT</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</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-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>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-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>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-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>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>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>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",
       "    <tr>\n",
       "        <td>04-05-2019</td>\n",
       "        <td>06: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</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>03-12-2018</td>\n",
       "        <td>18:34:05</td>\n",
       "        <td>F9 B5 B1046.3</td>\n",
       "        <td>VAFB SLC-4E</td>\n",
       "        <td>SSO-A </td>\n",
       "        <td>4000</td>\n",
       "        <td>SSO</td>\n",
       "        <td>Spaceflight Industries</td>\n",
       "        <td>Success</td>\n",
       "        <td>Success</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>03-09-2020</td>\n",
       "        <td>12:46:14</td>\n",
       "        <td>F9 B5 B1060.2 </td>\n",
       "        <td>KSC LC-39A</td>\n",
       "        <td>Starlink 11 v1.0, Starlink 12 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>02-03-2019</td>\n",
       "        <td>07:49:00</td>\n",
       "        <td>F9 B5B1051.1</td>\n",
       "        <td>KSC LC-39A</td>\n",
       "        <td>Crew Dragon Demo-1, SpaceX CRS-17 </td>\n",
       "        <td>12055</td>\n",
       "        <td>LEO (ISS)</td>\n",
       "        <td>NASA (CCD) </td>\n",
       "        <td>Success</td>\n",
       "        <td>Success</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('30-08-2020', '23:18:00', 'F9 B5 B1059.4', 'CCAFS SLC-40', 'SAOCOM 1B, GNOMES 1, Tyvak-0172', 3130, 'SSO', 'CONAE, PlanetIQ, SpaceX', 'Success', 'Success'),\n",
       " ('30-06-2020', '20:10:46', 'F9 B5B1060.1', 'CCAFS SLC-40', 'GPS III-03, ANASIS-II', 4311, 'MEO', 'U.S. Space Force', 'Success', 'Success'),\n",
       " ('30-05-2020', '19:22:00', 'F9 B5B1058.1 ', 'KSC LC-39A', 'Crew Dragon Demo-2, Starlink 7 v1.0 ', 12530, 'LEO (ISS)', 'NASA (CCDev)', '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, 'LEO', 'SpaceX', 'Success', 'Success'),\n",
       " ('25-11-2020', '02:13:00', 'F9 B5 B1049.7 ', 'CCAFS SLC-40', 'Starlink 15 v1.0, SpaceX CRS-21', 15600, 'LEO', 'SpaceX', 'Success', 'Success'),\n",
       " ('25-07-2019', '22:01:00', 'F9 B5 B1056.2 ', 'CCAFS SLC-40', 'SpaceX CRS-18, AMOS-17 ', 2268, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'Success'),\n",
       " ('25-07-2018', '11:39:00', 'F9 B5B1048.1', 'VAFB SLC-4E', 'Iridium NEXT-7', 9600, 'Polar LEO', 'Iridium Communications', 'Success', 'Success'),\n",
       " ('24-10-2020', '15:31:34', 'F9 B5 B1060.3', 'CCAFS SLC-40', 'Starlink 14 v1.0, GPS III-04  ', 15600, 'LEO', 'SpaceX', 'Success', 'Success'),\n",
       " ('24-05-2019', '02:30:00', 'F9 B5 B1049.3', 'CCAFS SLC-40', 'Starlink v0.9, RADARSAT Constellation', 13620, 'LEO', 'SpaceX', 'Success', 'Success'),\n",
       " ('22-07-2018', '05:50:00', 'F9 B5B1047.1', 'CCAFS SLC-40', 'Telstar 19V', 7075, 'GTO', 'Telesat', 'Success', 'Success'),\n",
       " ('22-04-2020', '19:30:00', 'F9 B5 B1051.4', 'KSC LC-39A', 'Starlink 6 v1.0, Crew Dragon Demo-2 ', 15600, 'LEO', 'SpaceX', 'Success', 'Success'),\n",
       " ('22-02-2019', '01:45:00', 'F9 B5 B1048.3', 'CCAFS SLC-40', 'Nusantara Satu, Beresheet Moon lander, S5', 4850, 'GTO', 'PSN, SpaceIL / IAI', 'Success', 'Success'),\n",
       " ('21-11-2020', '17:17:08', 'F9 B5B1063.1', 'VAFB SLC-4E', 'Sentinel-6 Michael Freilich, Starlink 15 v1.0 ', 1192, 'LEO', 'NASA / NOAA / ESA / EUMETSAT', 'Success', 'Success'),\n",
       " ('20-07-2020', '21:30:00', 'F9 B5 B1058.2 ', 'CCAFS SLC-40', 'ANASIS-II, Starlink 9 v1.0', 5500, 'GTO', 'Republic of Korea Army, Spaceflight Industries (BlackSky)', 'Success', 'Success'),\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",
       " ('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-11-2018', '20:46:00', 'F9 B5 B1047.2', 'KSC LC-39A', 'Es hail 2', 5300, 'GTO', 'Es hailSat', 'Success', 'Success'),\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-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",
       " ('08-10-2018', '02:22:00', 'F9 B5 B1048.2', 'VAFB SLC-4E', 'SAOCOM 1A', 3000, 'SSO', 'CONAE', 'Success', 'Success'),\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",
       " ('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'),\n",
       " ('04-05-2019', '06:48:00', 'F9 B5B1056.1 ', 'CCAFS SLC-40', 'SpaceX CRS-17, Starlink v0.9', 2495, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'Success'),\n",
       " ('03-12-2018', '18:34:05', 'F9 B5 B1046.3', 'VAFB SLC-4E', 'SSO-A ', 4000, 'SSO', 'Spaceflight Industries', 'Success', 'Success'),\n",
       " ('03-09-2020', '12:46:14', 'F9 B5 B1060.2 ', 'KSC LC-39A', 'Starlink 11 v1.0, Starlink 12 v1.0 ', 15600, 'LEO', 'SpaceX', 'Success', 'Success'),\n",
       " ('02-03-2019', '07:49:00', 'F9 B5B1051.1', 'KSC LC-39A', 'Crew Dragon Demo-1, SpaceX CRS-17 ', 12055, 'LEO (ISS)', 'NASA (CCD) ', 'Success', 'Success')]"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select * from SPACEXTBL where \"Landing _Outcome\" like \"Success\" Order by \"Date\" desc"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reference Links\n",
    "\n",
    "*   <a href =\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Labs_Coursera_V5/labs/Lab%20-%20String%20Patterns%20-%20Sorting%20-%20Grouping/instructional-labs.md.html?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDS0321ENSkillsNetwork26802033-2021-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-2021-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-2021-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-2021-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-2021-01-01\">Hands-on Lab: Analyzing a real World Data Set</a>\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",
    "\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 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}