05-eda-sql-sqllite.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style=\"text-align:center\">\n",
    "    <a href=\"https://skills.network\" target=\"_blank\">\n",
    "    <img src=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/assets/logos/SN_web_lightmode.png\" width=\"200\" alt=\"Skills Network Logo\">\n",
    "    </a>\n",
    "</p>\n",
    "\n",
    "<h1 align=center><font size = 5>Assignment: SQL Notebook for Peer Assignment</font></h1>\n",
    "\n",
    "Estimated time needed: **60** minutes.\n",
    "\n",
    "## Introduction\n",
    "Using this Python notebook you will:\n",
    "\n",
    "1.  Understand the Spacex DataSet\n",
    "2.  Load the dataset  into the corresponding table in a Db2 database\n",
    "3.  Execute SQL queries to answer assignment questions \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Overview of the DataSet\n",
    "\n",
    "SpaceX has gained worldwide attention for a series of historic milestones. \n",
    "\n",
    "It is the only private company ever to return a spacecraft from low-earth orbit, which it first accomplished in December 2010.\n",
    "SpaceX advertises Falcon 9 rocket launches on its website with a cost of 62 million dollars wheras other providers cost upward of 165 million dollars each, much of the savings is because Space X can reuse the first stage. \n",
    "\n",
    "\n",
    "Therefore if we can determine if the first stage will land, we can determine the cost of a launch. \n",
    "\n",
    "This information can be used if an alternate company wants to bid against SpaceX for a rocket launch.\n",
    "\n",
    "This dataset includes a record for each payload carried during a SpaceX mission into outer space.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Download the datasets\n",
    "\n",
    "This assignment requires you to load the spacex dataset.\n",
    "\n",
    "In many cases the dataset to be analyzed is available as a .CSV (comma separated values) file, perhaps on the internet. Click on the link below to download and save the dataset (.CSV file):\n",
    "\n",
    " <a href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/module_2/data/Spacex.csv\" target=\"_blank\">Spacex DataSet</a>\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 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[31m51.0 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=1159121 sha256=661eb6173cded403bfe8255ed4c7f52803078b05940209eabdbfbc9ac5795340\n",
      "  Stored in directory: /home/jupyterlab/.cache/pip/wheels/03/71/13/010faf12246f72dc76b4150e6e599d13a85b4435e06fb9e51f\n",
      "Successfully built sqlalchemy\n",
      "Installing collected packages: sqlalchemy\n",
      "  Attempting uninstall: sqlalchemy\n",
      "    Found existing installation: SQLAlchemy 1.3.24\n",
      "    Uninstalling SQLAlchemy-1.3.24:\n",
      "      Successfully uninstalled SQLAlchemy-1.3.24\n",
      "Successfully installed sqlalchemy-1.3.9\n"
     ]
    }
   ],
   "source": [
    "!pip install sqlalchemy==1.3.9\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Connect to the database\n",
    "\n",
    "Let us first load the SQL extension and establish a connection with the database\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: ipython-sql in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (0.3.9)\n",
      "Requirement already satisfied: prettytable in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from ipython-sql) (3.7.0)\n",
      "Requirement already satisfied: ipython>=1.0 in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from ipython-sql) (7.33.0)\n",
      "Requirement already satisfied: sqlalchemy>=0.6.7 in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from ipython-sql) (1.3.9)\n",
      "Requirement already satisfied: sqlparse in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from ipython-sql) (0.4.4)\n",
      "Requirement already satisfied: six in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from ipython-sql) (1.16.0)\n",
      "Requirement already satisfied: ipython-genutils>=0.1.0 in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from ipython-sql) (0.2.0)\n",
      "Requirement already satisfied: setuptools>=18.5 in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (67.7.2)\n",
      "Requirement already satisfied: jedi>=0.16 in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (0.18.2)\n",
      "Requirement already satisfied: decorator in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (5.1.1)\n",
      "Requirement already satisfied: pickleshare in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (0.7.5)\n",
      "Requirement already satisfied: traitlets>=4.2 in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (5.9.0)\n",
      "Requirement already satisfied: prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0 in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (3.0.38)\n",
      "Requirement already satisfied: pygments in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (2.15.1)\n",
      "Requirement already satisfied: backcall in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (0.2.0)\n",
      "Requirement already satisfied: matplotlib-inline in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (0.1.6)\n",
      "Requirement already satisfied: pexpect>4.3 in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (4.8.0)\n",
      "Requirement already satisfied: importlib-metadata in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from prettytable->ipython-sql) (4.11.4)\n",
      "Requirement already satisfied: wcwidth in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from prettytable->ipython-sql) (0.2.6)\n",
      "Requirement already satisfied: parso<0.9.0,>=0.8.0 in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from jedi>=0.16->ipython>=1.0->ipython-sql) (0.8.3)\n",
      "Requirement already satisfied: ptyprocess>=0.5 in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from pexpect>4.3->ipython>=1.0->ipython-sql) (0.7.0)\n",
      "Requirement already satisfied: zipp>=0.5 in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from importlib-metadata->prettytable->ipython-sql) (3.15.0)\n",
      "Requirement already satisfied: typing-extensions>=3.6.4 in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (from importlib-metadata->prettytable->ipython-sql) (4.5.0)\n",
      "Done\n"
     ]
    }
   ],
   "source": [
    "#Please uncomment and execute the code below if you are working locally.\n",
    "\n",
    "!pip install ipython-sql\n",
    "print(\"Done\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import csv, sqlite3\n",
    "\n",
    "con = sqlite3.connect(\"my_data1.db\")\n",
    "cur = con.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done\n"
     ]
    }
   ],
   "source": [
    "!pip install -q pandas==1.1.5\n",
    "print(\"Done\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: @my_data1.db'"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql sqlite:///my_data1.db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/jupyterlab/conda/envs/python/lib/python3.7/site-packages/pandas/core/generic.py:2882: UserWarning: The spaces in these column names will not be changed. In pandas versions < 0.14, spaces were converted to underscores.\n",
      "  both result in 0.1234 being formatted as 0.12.\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_csv(\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/module_2/data/Spacex.csv\")\n",
    "df.to_sql(\"SPACEXTBL\", con, if_exists='replace', index=False,method=\"multi\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Note:This below code is added to remove blank rows from table**\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "(sqlite3.OperationalError) table SPACEXTABLE already exists\n",
      "[SQL: create table SPACEXTABLE as select * from SPACEXTBL where Date is not null]\n",
      "(Background on this error at: http://sqlalche.me/e/e3q8)\n"
     ]
    }
   ],
   "source": [
    "%sql create table SPACEXTABLE as select * from SPACEXTBL where Date is not null"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Date</th>\n",
       "            <th>Time (UTC)</th>\n",
       "            <th>Booster_Version</th>\n",
       "            <th>Launch_Site</th>\n",
       "            <th>Payload</th>\n",
       "            <th>PAYLOAD_MASS__KG_</th>\n",
       "            <th>Orbit</th>\n",
       "            <th>Customer</th>\n",
       "            <th>Mission_Outcome</th>\n",
       "            <th>Landing_Outcome</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>2010-06-04</td>\n",
       "            <td>18:45:00</td>\n",
       "            <td>F9 v1.0  B0003</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "            <td>Dragon Spacecraft Qualification Unit</td>\n",
       "            <td>0</td>\n",
       "            <td>LEO</td>\n",
       "            <td>SpaceX</td>\n",
       "            <td>Success</td>\n",
       "            <td>Failure (parachute)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2010-12-08</td>\n",
       "            <td>15:43:00</td>\n",
       "            <td>F9 v1.0  B0004</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "            <td>Dragon demo flight C1, two CubeSats, barrel of Brouere cheese</td>\n",
       "            <td>0</td>\n",
       "            <td>LEO (ISS)</td>\n",
       "            <td>NASA (COTS) NRO</td>\n",
       "            <td>Success</td>\n",
       "            <td>Failure (parachute)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2012-05-22</td>\n",
       "            <td>7:44:00</td>\n",
       "            <td>F9 v1.0  B0005</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "            <td>Dragon demo flight C2</td>\n",
       "            <td>525</td>\n",
       "            <td>LEO (ISS)</td>\n",
       "            <td>NASA (COTS)</td>\n",
       "            <td>Success</td>\n",
       "            <td>No attempt</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2012-10-08</td>\n",
       "            <td>0:35:00</td>\n",
       "            <td>F9 v1.0  B0006</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "            <td>SpaceX CRS-1</td>\n",
       "            <td>500</td>\n",
       "            <td>LEO (ISS)</td>\n",
       "            <td>NASA (CRS)</td>\n",
       "            <td>Success</td>\n",
       "            <td>No attempt</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2013-03-01</td>\n",
       "            <td>15:10:00</td>\n",
       "            <td>F9 v1.0  B0007</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "            <td>SpaceX CRS-2</td>\n",
       "            <td>677</td>\n",
       "            <td>LEO (ISS)</td>\n",
       "            <td>NASA (CRS)</td>\n",
       "            <td>Success</td>\n",
       "            <td>No attempt</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('2010-06-04', '18:45:00', 'F9 v1.0  B0003', 'CCAFS LC-40', 'Dragon Spacecraft Qualification Unit', 0, 'LEO', 'SpaceX', 'Success', 'Failure (parachute)'),\n",
       " ('2010-12-08', '15:43:00', 'F9 v1.0  B0004', 'CCAFS LC-40', 'Dragon demo flight C1, two CubeSats, barrel of Brouere cheese', 0, 'LEO (ISS)', 'NASA (COTS) NRO', 'Success', 'Failure (parachute)'),\n",
       " ('2012-05-22', '7:44:00', 'F9 v1.0  B0005', 'CCAFS LC-40', 'Dragon demo flight C2', 525, 'LEO (ISS)', 'NASA (COTS)', 'Success', 'No attempt'),\n",
       " ('2012-10-08', '0:35:00', 'F9 v1.0  B0006', 'CCAFS LC-40', 'SpaceX CRS-1', 500, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'No attempt'),\n",
       " ('2013-03-01', '15:10:00', 'F9 v1.0  B0007', 'CCAFS LC-40', 'SpaceX CRS-2', 677, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'No attempt')]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM SPACEXTABLE LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Tasks\n",
    "\n",
    "Now write and execute SQL queries to solve the assignment tasks.\n",
    "\n",
    "**Note: If the column names are in mixed case enclose it in double quotes\n",
    "   For Example \"Landing_Outcome\"**\n",
    "\n",
    "### Task 1\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "##### Display the names of the unique launch sites  in the space mission\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>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": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT DISTINCT Launch_Site FROM SPACEXTABLE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Booster_Version</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>F9 v1.0  B0003</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 v1.0  B0004</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 v1.0  B0005</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 v1.0  B0006</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 v1.0  B0007</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 v1.1  B1003</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 v1.1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 v1.1 B1011</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 v1.1 B1010</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 v1.1 B1012</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 v1.1 B1013</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 v1.1 B1014</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 v1.1 B1015</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 v1.1 B1016</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 v1.1 B1018</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT B1019</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 v1.1 B1017</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT B1020</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT B1021.1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT B1022</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT B1023.1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT B1024</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT B1025.1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT B1026</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT B1029.1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT B1031.1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT B1030</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT  B1021.2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT B1032.1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT B1034</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT B1035.1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT  B1029.2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT B1036.1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT B1037</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B4 B1039.1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT B1038.1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B4 B1040.1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B4 B1041.1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT  B1031.2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B4 B1042.1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT  B1035.2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT  B1036.2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B4 B1043.1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT  B1032.2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT  B1038.2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B4 B1044</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B4  B1041.2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B4  B1039.2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B4 B1045.1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5  B1046.1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B4  B1043.2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B4  B1040.2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B4 B1045.2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5B1047.1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5B1048.1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1046.2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5B1049.1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1048.2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1047.2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1046.3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5B1050</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5B1054</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1049.2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1048.3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5B1051.1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5B1056.1 </td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1049.3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1051.2 </td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1056.2 </td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1047.3 </td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1048.4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5B1059.1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1056.3 </td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1049.4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1046.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 B1059.2</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 B5B1058.1 </td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1049.5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1059.3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5B1060.1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1058.2 </td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1051.5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1049.6</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1059.4</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 B5B1062.1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5B1061.1 </td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5B1063.1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1049.7 </td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1058.4 </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('F9 v1.0  B0003',),\n",
       " ('F9 v1.0  B0004',),\n",
       " ('F9 v1.0  B0005',),\n",
       " ('F9 v1.0  B0006',),\n",
       " ('F9 v1.0  B0007',),\n",
       " ('F9 v1.1  B1003',),\n",
       " ('F9 v1.1',),\n",
       " ('F9 v1.1 B1011',),\n",
       " ('F9 v1.1 B1010',),\n",
       " ('F9 v1.1 B1012',),\n",
       " ('F9 v1.1 B1013',),\n",
       " ('F9 v1.1 B1014',),\n",
       " ('F9 v1.1 B1015',),\n",
       " ('F9 v1.1 B1016',),\n",
       " ('F9 v1.1 B1018',),\n",
       " ('F9 FT B1019',),\n",
       " ('F9 v1.1 B1017',),\n",
       " ('F9 FT B1020',),\n",
       " ('F9 FT B1021.1',),\n",
       " ('F9 FT B1022',),\n",
       " ('F9 FT B1023.1',),\n",
       " ('F9 FT B1024',),\n",
       " ('F9 FT B1025.1',),\n",
       " ('F9 FT B1026',),\n",
       " ('F9 FT B1029.1',),\n",
       " ('F9 FT B1031.1',),\n",
       " ('F9 FT B1030',),\n",
       " ('F9 FT  B1021.2',),\n",
       " ('F9 FT B1032.1',),\n",
       " ('F9 FT B1034',),\n",
       " ('F9 FT B1035.1',),\n",
       " ('F9 FT  B1029.2',),\n",
       " ('F9 FT B1036.1',),\n",
       " ('F9 FT B1037',),\n",
       " ('F9 B4 B1039.1',),\n",
       " ('F9 FT B1038.1',),\n",
       " ('F9 B4 B1040.1',),\n",
       " ('F9 B4 B1041.1',),\n",
       " ('F9 FT  B1031.2',),\n",
       " ('F9 B4 B1042.1',),\n",
       " ('F9 FT  B1035.2',),\n",
       " ('F9 FT  B1036.2',),\n",
       " ('F9 B4 B1043.1',),\n",
       " ('F9 FT  B1032.2',),\n",
       " ('F9 FT  B1038.2',),\n",
       " ('F9 B4 B1044',),\n",
       " ('F9 B4  B1041.2',),\n",
       " ('F9 B4  B1039.2',),\n",
       " ('F9 B4 B1045.1',),\n",
       " ('F9 B5  B1046.1',),\n",
       " ('F9 B4  B1043.2',),\n",
       " ('F9 B4  B1040.2',),\n",
       " ('F9 B4 B1045.2',),\n",
       " ('F9 B5B1047.1',),\n",
       " ('F9 B5B1048.1',),\n",
       " ('F9 B5 B1046.2',),\n",
       " ('F9 B5B1049.1',),\n",
       " ('F9 B5 B1048.2',),\n",
       " ('F9 B5 B1047.2',),\n",
       " ('F9 B5 B1046.3',),\n",
       " ('F9 B5B1050',),\n",
       " ('F9 B5B1054',),\n",
       " ('F9 B5 B1049.2',),\n",
       " ('F9 B5 B1048.3',),\n",
       " ('F9 B5B1051.1',),\n",
       " ('F9 B5B1056.1 ',),\n",
       " ('F9 B5 B1049.3',),\n",
       " ('F9 B5 B1051.2 ',),\n",
       " ('F9 B5 B1056.2 ',),\n",
       " ('F9 B5 B1047.3 ',),\n",
       " ('F9 B5 B1048.4',),\n",
       " ('F9 B5B1059.1',),\n",
       " ('F9 B5 B1056.3 ',),\n",
       " ('F9 B5 B1049.4',),\n",
       " ('F9 B5 B1046.4',),\n",
       " ('F9 B5 B1051.3',),\n",
       " ('F9 B5 B1056.4',),\n",
       " ('F9 B5 B1059.2',),\n",
       " ('F9 B5 B1048.5',),\n",
       " ('F9 B5 B1051.4',),\n",
       " ('F9 B5B1058.1 ',),\n",
       " ('F9 B5 B1049.5',),\n",
       " ('F9 B5 B1059.3',),\n",
       " ('F9 B5B1060.1',),\n",
       " ('F9 B5 B1058.2 ',),\n",
       " ('F9 B5 B1051.5',),\n",
       " ('F9 B5 B1049.6',),\n",
       " ('F9 B5 B1059.4',),\n",
       " ('F9 B5 B1060.2 ',),\n",
       " ('F9 B5 B1058.3 ',),\n",
       " ('F9 B5 B1051.6',),\n",
       " ('F9 B5 B1060.3',),\n",
       " ('F9 B5B1062.1',),\n",
       " ('F9 B5B1061.1 ',),\n",
       " ('F9 B5B1063.1',),\n",
       " ('F9 B5 B1049.7 ',),\n",
       " ('F9 B5 B1058.4 ',)]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT DISTINCT Booster_Version FROM SPACEXTABLE;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "### Task 2\n",
    "\n",
    "\n",
    "#####  Display 5 records where launch sites begin with the string 'CCA' \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Date</th>\n",
       "            <th>Time (UTC)</th>\n",
       "            <th>Booster_Version</th>\n",
       "            <th>Launch_Site</th>\n",
       "            <th>Payload</th>\n",
       "            <th>PAYLOAD_MASS__KG_</th>\n",
       "            <th>Orbit</th>\n",
       "            <th>Customer</th>\n",
       "            <th>Mission_Outcome</th>\n",
       "            <th>Landing_Outcome</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>2010-06-04</td>\n",
       "            <td>18:45:00</td>\n",
       "            <td>F9 v1.0  B0003</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "            <td>Dragon Spacecraft Qualification Unit</td>\n",
       "            <td>0</td>\n",
       "            <td>LEO</td>\n",
       "            <td>SpaceX</td>\n",
       "            <td>Success</td>\n",
       "            <td>Failure (parachute)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2010-12-08</td>\n",
       "            <td>15:43:00</td>\n",
       "            <td>F9 v1.0  B0004</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "            <td>Dragon demo flight C1, two CubeSats, barrel of Brouere cheese</td>\n",
       "            <td>0</td>\n",
       "            <td>LEO (ISS)</td>\n",
       "            <td>NASA (COTS) NRO</td>\n",
       "            <td>Success</td>\n",
       "            <td>Failure (parachute)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2012-05-22</td>\n",
       "            <td>7:44:00</td>\n",
       "            <td>F9 v1.0  B0005</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "            <td>Dragon demo flight C2</td>\n",
       "            <td>525</td>\n",
       "            <td>LEO (ISS)</td>\n",
       "            <td>NASA (COTS)</td>\n",
       "            <td>Success</td>\n",
       "            <td>No attempt</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2012-10-08</td>\n",
       "            <td>0:35:00</td>\n",
       "            <td>F9 v1.0  B0006</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "            <td>SpaceX CRS-1</td>\n",
       "            <td>500</td>\n",
       "            <td>LEO (ISS)</td>\n",
       "            <td>NASA (CRS)</td>\n",
       "            <td>Success</td>\n",
       "            <td>No attempt</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2013-03-01</td>\n",
       "            <td>15:10:00</td>\n",
       "            <td>F9 v1.0  B0007</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "            <td>SpaceX CRS-2</td>\n",
       "            <td>677</td>\n",
       "            <td>LEO (ISS)</td>\n",
       "            <td>NASA (CRS)</td>\n",
       "            <td>Success</td>\n",
       "            <td>No attempt</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('2010-06-04', '18:45:00', 'F9 v1.0  B0003', 'CCAFS LC-40', 'Dragon Spacecraft Qualification Unit', 0, 'LEO', 'SpaceX', 'Success', 'Failure (parachute)'),\n",
       " ('2010-12-08', '15:43:00', 'F9 v1.0  B0004', 'CCAFS LC-40', 'Dragon demo flight C1, two CubeSats, barrel of Brouere cheese', 0, 'LEO (ISS)', 'NASA (COTS) NRO', 'Success', 'Failure (parachute)'),\n",
       " ('2012-05-22', '7:44:00', 'F9 v1.0  B0005', 'CCAFS LC-40', 'Dragon demo flight C2', 525, 'LEO (ISS)', 'NASA (COTS)', 'Success', 'No attempt'),\n",
       " ('2012-10-08', '0:35:00', 'F9 v1.0  B0006', 'CCAFS LC-40', 'SpaceX CRS-1', 500, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'No attempt'),\n",
       " ('2013-03-01', '15:10:00', 'F9 v1.0  B0007', 'CCAFS LC-40', 'SpaceX CRS-2', 677, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'No attempt')]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM SPACEXTABLE WHERE Launch_Site LIKE 'cca%' LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 3\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "##### Display the total payload mass carried by boosters launched by NASA (CRS)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Total Payload Mass By NASA(CRS)</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>45596</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(45596,)]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT SUM(PAYLOAD_MASS__KG_) AS 'Total Payload Mass By NASA(CRS)' FROM SPACEXTABLE WHERE Customer = 'NASA (CRS)';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 4\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "##### Display average payload mass carried by booster version F9 v1.1\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Average Paload Mass By F9 v1.1</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>2534.6666666666665</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2534.6666666666665,)]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT AVG(PAYLOAD_MASS__KG_) AS 'Average Paload Mass By F9 v1.1' FROM SPACEXTABLE WHERE Booster_Version LIKE 'F9 v1.1%';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 5\n",
    "\n",
    "##### List the date when the first succesful landing outcome in ground pad was acheived.\n",
    "\n",
    "\n",
    "_Hint:Use min function_ \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>MIN(Date)</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>2010-06-04</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('2010-06-04',)]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT MIN(Date) FROM SPACEXTABLE;"
   ]
  },
  {
   "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": 16,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Booster_Version</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>F9 v1.1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 v1.1 B1011</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 v1.1 B1014</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 v1.1 B1016</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT B1020</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT B1022</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT B1026</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT B1030</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT  B1021.2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT B1032.1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B4 B1040.1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT  B1031.2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT  B1032.2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B4  B1040.2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1046.2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1047.2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1048.3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1051.2 </td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5B1060.1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1058.2 </td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5B1062.1</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('F9 v1.1',),\n",
       " ('F9 v1.1 B1011',),\n",
       " ('F9 v1.1 B1014',),\n",
       " ('F9 v1.1 B1016',),\n",
       " ('F9 FT B1020',),\n",
       " ('F9 FT B1022',),\n",
       " ('F9 FT B1026',),\n",
       " ('F9 FT B1030',),\n",
       " ('F9 FT  B1021.2',),\n",
       " ('F9 FT B1032.1',),\n",
       " ('F9 B4 B1040.1',),\n",
       " ('F9 FT  B1031.2',),\n",
       " ('F9 FT  B1032.2',),\n",
       " ('F9 B4  B1040.2',),\n",
       " ('F9 B5 B1046.2',),\n",
       " ('F9 B5 B1047.2',),\n",
       " ('F9 B5 B1048.3',),\n",
       " ('F9 B5 B1051.2 ',),\n",
       " ('F9 B5B1060.1',),\n",
       " ('F9 B5 B1058.2 ',),\n",
       " ('F9 B5B1062.1',)]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT Booster_Version FROM SPACEXTABLE WHERE Mission_Outcome = 'Success' AND PAYLOAD_MASS__KG_ > 4000 AND PAYLOAD_MASS__KG_ < 6000;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 7\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "##### List the total number of successful and failure mission outcomes\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Total of Success Missions</th>\n",
       "            <th>Total of Failure Missions</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>98</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(98, 1)]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT SUM(Mission_Outcome = 'Success') AS 'Total of Success Missions', SUM(Mission_Outcome LIKE 'Failure%') AS 'Total of Failure Missions' FROM SPACEXTABLE;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 8\n",
    "\n",
    "\n",
    "\n",
    "##### List the   names of the booster_versions which have carried the maximum payload mass. Use a subquery\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Booster_Version</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1048.4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1049.4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1051.3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1056.4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1048.5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1051.4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1049.5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1060.2 </td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1058.3 </td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1051.6</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1060.3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1049.7 </td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('F9 B5 B1048.4',),\n",
       " ('F9 B5 B1049.4',),\n",
       " ('F9 B5 B1051.3',),\n",
       " ('F9 B5 B1056.4',),\n",
       " ('F9 B5 B1048.5',),\n",
       " ('F9 B5 B1051.4',),\n",
       " ('F9 B5 B1049.5',),\n",
       " ('F9 B5 B1060.2 ',),\n",
       " ('F9 B5 B1058.3 ',),\n",
       " ('F9 B5 B1051.6',),\n",
       " ('F9 B5 B1060.3',),\n",
       " ('F9 B5 B1049.7 ',)]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT Booster_Version FROM SPACEXTABLE WHERE PAYLOAD_MASS__KG_ = (SELECT MAX(PAYLOAD_MASS__KG_) FROM SPACEXTABLE);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 9\n",
    "\n",
    "\n",
    "##### List the records which will display the month names, failure landing_outcomes in drone ship ,booster versions, launch_site for the months in year 2015.\n",
    "\n",
    "**Note: SQLLite does not support monthnames. So you need to use  substr(Date, 6,2) as month to get the months and substr(Date,0,5)='2015' for year.**\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Month</th>\n",
       "            <th>Landing_Outcome</th>\n",
       "            <th>Booster_Version</th>\n",
       "            <th>Launch_Site</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>01</td>\n",
       "            <td>Failure (drone ship)</td>\n",
       "            <td>F9 v1.1 B1012</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>02</td>\n",
       "            <td>Controlled (ocean)</td>\n",
       "            <td>F9 v1.1 B1013</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>03</td>\n",
       "            <td>No attempt</td>\n",
       "            <td>F9 v1.1 B1014</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>04</td>\n",
       "            <td>Failure (drone ship)</td>\n",
       "            <td>F9 v1.1 B1015</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>04</td>\n",
       "            <td>No attempt</td>\n",
       "            <td>F9 v1.1 B1016</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>06</td>\n",
       "            <td>Precluded (drone ship)</td>\n",
       "            <td>F9 v1.1 B1018</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>12</td>\n",
       "            <td>Success (ground pad)</td>\n",
       "            <td>F9 FT B1019</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('01', 'Failure (drone ship)', 'F9 v1.1 B1012', 'CCAFS LC-40'),\n",
       " ('02', 'Controlled (ocean)', 'F9 v1.1 B1013', 'CCAFS LC-40'),\n",
       " ('03', 'No attempt', 'F9 v1.1 B1014', 'CCAFS LC-40'),\n",
       " ('04', 'Failure (drone ship)', 'F9 v1.1 B1015', 'CCAFS LC-40'),\n",
       " ('04', 'No attempt', 'F9 v1.1 B1016', 'CCAFS LC-40'),\n",
       " ('06', 'Precluded (drone ship)', 'F9 v1.1 B1018', 'CCAFS LC-40'),\n",
       " ('12', 'Success (ground pad)', 'F9 FT B1019', 'CCAFS LC-40')]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT substr(Date, 6, 2) AS Month, Landing_Outcome, Booster_Version, Launch_Site FROM SPACEX WHERE substr(Date, 0, 5) = '2015';"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Month_Name</th>\n",
       "            <th>Landing_Outcome</th>\n",
       "            <th>Booster_Version</th>\n",
       "            <th>Launch_Site</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>January</td>\n",
       "            <td>Failure (drone ship)</td>\n",
       "            <td>F9 v1.1 B1012</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>February</td>\n",
       "            <td>Controlled (ocean)</td>\n",
       "            <td>F9 v1.1 B1013</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>March</td>\n",
       "            <td>No attempt</td>\n",
       "            <td>F9 v1.1 B1014</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>April</td>\n",
       "            <td>Failure (drone ship)</td>\n",
       "            <td>F9 v1.1 B1015</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>April</td>\n",
       "            <td>No attempt</td>\n",
       "            <td>F9 v1.1 B1016</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>June</td>\n",
       "            <td>Precluded (drone ship)</td>\n",
       "            <td>F9 v1.1 B1018</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>December</td>\n",
       "            <td>Success (ground pad)</td>\n",
       "            <td>F9 FT B1019</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('January', 'Failure (drone ship)', 'F9 v1.1 B1012', 'CCAFS LC-40'),\n",
       " ('February', 'Controlled (ocean)', 'F9 v1.1 B1013', 'CCAFS LC-40'),\n",
       " ('March', 'No attempt', 'F9 v1.1 B1014', 'CCAFS LC-40'),\n",
       " ('April', 'Failure (drone ship)', 'F9 v1.1 B1015', 'CCAFS LC-40'),\n",
       " ('April', 'No attempt', 'F9 v1.1 B1016', 'CCAFS LC-40'),\n",
       " ('June', 'Precluded (drone ship)', 'F9 v1.1 B1018', 'CCAFS LC-40'),\n",
       " ('December', 'Success (ground pad)', 'F9 FT B1019', 'CCAFS LC-40')]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql SELECT\n",
    "       CASE substr(Date, 6, 2)\n",
    "           WHEN '01' THEN 'January'\n",
    "           WHEN '02' THEN 'February'\n",
    "           WHEN '03' THEN 'March'\n",
    "           WHEN '04' THEN 'April'\n",
    "           WHEN '05' THEN 'May'\n",
    "           WHEN '06' THEN 'June'\n",
    "           WHEN '07' THEN 'July'\n",
    "           WHEN '08' THEN 'August'\n",
    "           WHEN '09' THEN 'September'\n",
    "           WHEN '10' THEN 'October'\n",
    "           WHEN '11' THEN 'November'\n",
    "           WHEN '12' THEN 'December'\n",
    "       END AS Month_Name,\n",
    "       Landing_Outcome,\n",
    "       Booster_Version,\n",
    "       Launch_Site\n",
    "FROM SPACEXTABLE\n",
    "WHERE substr(Date, 0, 5) = '2015';\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Task 10\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "##### Rank the count of landing outcomes (such as Failure (drone ship) or Success (ground pad)) between the date 2010-06-04 and 2017-03-20, in descending order.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Landing_Outcome</th>\n",
       "            <th>count</th>\n",
       "            <th>Rank</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>No attempt</td>\n",
       "            <td>10</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Success (drone ship)</td>\n",
       "            <td>5</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Failure (drone ship)</td>\n",
       "            <td>5</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Success (ground pad)</td>\n",
       "            <td>3</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Controlled (ocean)</td>\n",
       "            <td>3</td>\n",
       "            <td>5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Uncontrolled (ocean)</td>\n",
       "            <td>2</td>\n",
       "            <td>6</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Failure (parachute)</td>\n",
       "            <td>2</td>\n",
       "            <td>7</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Precluded (drone ship)</td>\n",
       "            <td>1</td>\n",
       "            <td>8</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('No attempt', 10, 1),\n",
       " ('Success (drone ship)', 5, 2),\n",
       " ('Failure (drone ship)', 5, 3),\n",
       " ('Success (ground pad)', 3, 4),\n",
       " ('Controlled (ocean)', 3, 5),\n",
       " ('Uncontrolled (ocean)', 2, 6),\n",
       " ('Failure (parachute)', 2, 7),\n",
       " ('Precluded (drone ship)', 1, 8)]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT Landing_Outcome,\n",
    "       COUNT(*) AS count,\n",
    "       ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS Rank\n",
    "FROM SPACEXTABLE\n",
    "WHERE Date BETWEEN '2010-06-04' AND '2017-03-20'\n",
    "GROUP BY Landing_Outcome\n",
    "ORDER BY count DESC;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reference Links\n",
    "\n",
    "* <a href =\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Labs_Coursera_V5/labs/Lab%20-%20String%20Patterns%20-%20Sorting%20-%20Grouping/instructional-labs.md.html?origin=www.coursera.org\">Hands-on Lab : String Patterns, Sorting and Grouping</a>  \n",
    "\n",
    "*  <a  href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Labs_Coursera_V5/labs/Lab%20-%20Built-in%20functions%20/Hands-on_Lab__Built-in_Functions.md.html?origin=www.coursera.org\">Hands-on Lab: Built-in functions</a>\n",
    "\n",
    "*  <a  href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Labs_Coursera_V5/labs/Lab%20-%20Sub-queries%20and%20Nested%20SELECTs%20/instructional-labs.md.html?origin=www.coursera.org\">Hands-on Lab : Sub-queries and Nested SELECT Statements</a>\n",
    "\n",
    "*   <a href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Module%205/DB0201EN-Week3-1-3-SQLmagic.ipynb\">Hands-on Tutorial: Accessing Databases with SQL magic</a>\n",
    "\n",
    "*  <a href= \"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Module%205/DB0201EN-Week3-1-4-Analyzing.ipynb\">Hands-on Lab: Analyzing a real World Data Set</a>\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Author(s)\n",
    "\n",
    "<h4> Lakshmi Holla </h4>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Other Contributors\n",
    "\n",
    "<h4> Rav Ahuja </h4>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Change log\n",
    "| Date | Version | Changed by | Change Description |\n",
    "|------|--------|--------|---------|\n",
    "| 2021-07-09 | 0.2 |Lakshmi Holla | Changes made in magic sql|\n",
    "| 2021-05-20 | 0.1 |Lakshmi Holla | Created Initial Version |\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## <h3 align=\"center\"> © IBM Corporation 2021. All rights reserved. <h3/>\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python",
   "language": "python",
   "name": "conda-env-python-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}