jupyter-labs-eda-sql-coursera_sqllite.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style=\"text-align:center\">\n",
    "    <a href=\"https://skills.network\" target=\"_blank\">\n",
    "    <img src=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/assets/logos/SN_web_lightmode.png\" width=\"200\" alt=\"Skills Network Logo\">\n",
    "    </a>\n",
    "</p>\n",
    "\n",
    "<h1 align=center><font size = 5>Assignment: SQL Notebook for Peer Assignment</font></h1>\n",
    "\n",
    "Estimated time needed: **60** minutes.\n",
    "\n",
    "## Introduction\n",
    "Using this Python notebook you will:\n",
    "\n",
    "1.  Understand the Spacex DataSet\n",
    "2.  Load the dataset  into the corresponding table in a Db2 database\n",
    "3.  Execute SQL queries to answer assignment questions \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Overview of the DataSet\n",
    "\n",
    "SpaceX has gained worldwide attention for a series of historic milestones. \n",
    "\n",
    "It is the only private company ever to return a spacecraft from low-earth orbit, which it first accomplished in December 2010.\n",
    "SpaceX advertises Falcon 9 rocket launches on its website with a cost of 62 million dollars wheras other providers cost upward of 165 million dollars each, much of the savings is because Space X can reuse the first stage. \n",
    "\n",
    "\n",
    "Therefore if we can determine if the first stage will land, we can determine the cost of a launch. \n",
    "\n",
    "This information can be used if an alternate company wants to bid against SpaceX for a rocket launch.\n",
    "\n",
    "This dataset includes a record for each payload carried during a SpaceX mission into outer space.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Download the datasets\n",
    "\n",
    "This assignment requires you to load the spacex dataset.\n",
    "\n",
    "In many cases the dataset to be analyzed is available as a .CSV (comma separated values) file, perhaps on the internet. Click on the link below to download and save the dataset (.CSV file):\n",
    "\n",
    " <a href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/module_2/data/Spacex.csv\" target=\"_blank\">Spacex DataSet</a>\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "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": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Please uncomment and execute the code below if you are working locally.\n",
    "\n",
    "#!pip install ipython-sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There's a new jupysql version available (0.10.10), you're running 0.10.9. To upgrade: pip install jupysql --upgrade\n",
      "Deploy Streamlit apps for free on Ploomber Cloud! Learn more: https://ploomber.io/s/signup\n"
     ]
    }
   ],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv, sqlite3\n",
    "\n",
    "con = sqlite3.connect(\"my_data1.db\")\n",
    "cur = con.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip install -q pandas==1.1.5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Connecting to &#x27;sqlite:///my_data1.db&#x27;</span>"
      ],
      "text/plain": [
       "Connecting to 'sqlite:///my_data1.db'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%sql sqlite:///my_data1.db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "101"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_csv(\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/module_2/data/Spacex.csv\")\n",
    "df.to_sql(\"SPACEXTBL\", con, if_exists='replace', index=False,method=\"multi\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Note:This below code is added to remove blank rows from table**\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;sqlite:///my_data1.db&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'sqlite:///my_data1.db'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "++\n",
       "||\n",
       "++\n",
       "++"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql create table SPACEXTABLE as select * from SPACEXTBL where Date is not null"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Tasks\n",
    "\n",
    "Now write and execute SQL queries to solve the assignment tasks.\n",
    "\n",
    "**Note: If the column names are in mixed case enclose it in double quotes\n",
    "   For Example \"Landing_Outcome\"**\n",
    "\n",
    "### Task 1\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "##### Display the names of the unique launch sites  in the space mission\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;sqlite:///my_data1.db&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'sqlite:///my_data1.db'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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": [
       "+--------------+\n",
       "| Launch_Site  |\n",
       "+--------------+\n",
       "| CCAFS LC-40  |\n",
       "| VAFB SLC-4E  |\n",
       "|  KSC LC-39A  |\n",
       "| CCAFS SLC-40 |\n",
       "+--------------+"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select distinct Launch_Site from SPACEXTABLE "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "### Task 2\n",
    "\n",
    "\n",
    "#####  Display 5 records where launch sites begin with the string 'CCA' \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;sqlite:///my_data1.db&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'sqlite:///my_data1.db'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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": [
       "+------------+------------+-----------------+-------------+---------------------------------------------------------------+-------------------+-----------+-----------------+-----------------+---------------------+\n",
       "|    Date    | Time (UTC) | Booster_Version | Launch_Site |                            Payload                            | PAYLOAD_MASS__KG_ |   Orbit   |     Customer    | Mission_Outcome |   Landing_Outcome   |\n",
       "+------------+------------+-----------------+-------------+---------------------------------------------------------------+-------------------+-----------+-----------------+-----------------+---------------------+\n",
       "| 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     |\n",
       "+------------+------------+-----------------+-------------+---------------------------------------------------------------+-------------------+-----------+-----------------+-----------------+---------------------+"
      ]
     },
     "execution_count": 8,
     "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": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;sqlite:///my_data1.db&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'sqlite:///my_data1.db'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>TOTAL_PAYLOAD_MASS</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>45596</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+--------------------+\n",
       "| TOTAL_PAYLOAD_MASS |\n",
       "+--------------------+\n",
       "|       45596        |\n",
       "+--------------------+"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select sum(PAYLOAD_MASS__KG_) as TOTAL_PAYLOAD_MASS from SPACEXTABLE where Customer = 'NASA (CRS)'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;sqlite:///my_data1.db&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'sqlite:///my_data1.db'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>PAYLOAD_MASS__KG_</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>15600</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+-------------------+\n",
       "| PAYLOAD_MASS__KG_ |\n",
       "+-------------------+\n",
       "|       15600       |\n",
       "+-------------------+"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select payload_mass__kg_ from SPACEXTBL order by payload_mass__kg_ desc LIMIT 1"
   ]
  },
  {
   "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": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;sqlite:///my_data1.db&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'sqlite:///my_data1.db'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>AVERAGE_PAYLOAD_MASS</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>2928.4</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+----------------------+\n",
       "| AVERAGE_PAYLOAD_MASS |\n",
       "+----------------------+\n",
       "|        2928.4        |\n",
       "+----------------------+"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select avg(PAYLOAD_MASS__KG_) as AVERAGE_PAYLOAD_MASS from SPACEXTABLE 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",
    "\n",
    "_Hint:Use min function_ \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;sqlite:///my_data1.db&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'sqlite:///my_data1.db'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Landing_Outcome</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Failure (parachute)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>No attempt</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Uncontrolled (ocean)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Controlled (ocean)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Failure (drone ship)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Precluded (drone ship)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Success (ground pad)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Success (drone ship)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Success</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Failure</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">Truncated to <a href=\"https://jupysql.ploomber.io/en/latest/api/configuration.html#displaylimit\">displaylimit</a> of 10.</span>"
      ],
      "text/plain": [
       "+------------------------+\n",
       "|    Landing_Outcome     |\n",
       "+------------------------+\n",
       "|  Failure (parachute)   |\n",
       "|       No attempt       |\n",
       "|  Uncontrolled (ocean)  |\n",
       "|   Controlled (ocean)   |\n",
       "|  Failure (drone ship)  |\n",
       "| Precluded (drone ship) |\n",
       "|  Success (ground pad)  |\n",
       "|  Success (drone ship)  |\n",
       "|        Success         |\n",
       "|        Failure         |\n",
       "+------------------------+\n",
       "Truncated to displaylimit of 10."
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select distinct Landing_Outcome from SPACEXTABLE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;sqlite:///my_data1.db&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'sqlite:///my_data1.db'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>2015-12-22</td>\n",
       "            <td>1:29:00</td>\n",
       "            <td>F9 FT B1019</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "            <td>OG2 Mission 2  11 Orbcomm-OG2 satellites</td>\n",
       "            <td>2034</td>\n",
       "            <td>LEO</td>\n",
       "            <td>Orbcomm</td>\n",
       "            <td>Success</td>\n",
       "            <td>Success (ground pad)</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+------------+------------+-----------------+-------------+------------------------------------------+-------------------+-------+----------+-----------------+----------------------+\n",
       "|    Date    | Time (UTC) | Booster_Version | Launch_Site |                 Payload                  | PAYLOAD_MASS__KG_ | Orbit | Customer | Mission_Outcome |   Landing_Outcome    |\n",
       "+------------+------------+-----------------+-------------+------------------------------------------+-------------------+-------+----------+-----------------+----------------------+\n",
       "| 2015-12-22 |  1:29:00   |   F9 FT B1019   | CCAFS LC-40 | OG2 Mission 2  11 Orbcomm-OG2 satellites |        2034       |  LEO  | Orbcomm  |     Success     | Success (ground pad) |\n",
       "+------------+------------+-----------------+-------------+------------------------------------------+-------------------+-------+----------+-----------------+----------------------+"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select * from SPACEXTABLE where Landing_Outcome = 'Success (ground pad)' order by Date ASC limit 1"
   ]
  },
  {
   "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": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;sqlite:///my_data1.db&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'sqlite:///my_data1.db'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Booster_Version</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>F9 FT B1022</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT B1026</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT  B1021.2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 FT  B1031.2</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+-----------------+\n",
       "| Booster_Version |\n",
       "+-----------------+\n",
       "|   F9 FT B1022   |\n",
       "|   F9 FT B1026   |\n",
       "|  F9 FT  B1021.2 |\n",
       "|  F9 FT  B1031.2 |\n",
       "+-----------------+"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select distinct Booster_Version from SPACEXTABLE where Landing_Outcome = 'Success (drone ship)' and PAYLOAD_MASS__KG_ between 4000 and 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": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;sqlite:///my_data1.db&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'sqlite:///my_data1.db'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Mission_Outcome</th>\n",
       "            <th>Count</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Failure (in flight)</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Success</td>\n",
       "            <td>98</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Success </td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Success (payload status unclear)</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+----------------------------------+-------+\n",
       "|         Mission_Outcome          | Count |\n",
       "+----------------------------------+-------+\n",
       "|       Failure (in flight)        |   1   |\n",
       "|             Success              |   98  |\n",
       "|             Success              |   1   |\n",
       "| Success (payload status unclear) |   1   |\n",
       "+----------------------------------+-------+"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT Mission_Outcome, COUNT(*) AS Count FROM SPACEXTABLE GROUP BY Mission_Outcome"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;sqlite:///my_data1.db&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'sqlite:///my_data1.db'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Distinct_Count</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+----------------+\n",
       "| Distinct_Count |\n",
       "+----------------+\n",
       "|       4        |\n",
       "+----------------+"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT COUNT(DISTINCT Mission_Outcome) AS Distinct_Count 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": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;sqlite:///my_data1.db&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'sqlite:///my_data1.db'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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",
       "    </tbody>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">Truncated to <a href=\"https://jupysql.ploomber.io/en/latest/api/configuration.html#displaylimit\">displaylimit</a> of 10.</span>"
      ],
      "text/plain": [
       "+-----------------+\n",
       "| Booster_Version |\n",
       "+-----------------+\n",
       "|  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",
       "+-----------------+\n",
       "Truncated to displaylimit of 10."
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql SELECT Booster_Version\n",
    "FROM SPACEXTABLE\n",
    "WHERE PAYLOAD_MASS__KG_ = (\n",
    "    SELECT MAX(PAYLOAD_MASS__KG_)\n",
    "    FROM SPACEXTABLE\n",
    ")"
   ]
  },
  {
   "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": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;sqlite:///my_data1.db&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'sqlite:///my_data1.db'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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",
       "            <th>Date</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",
       "            <td>2015-01-10</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",
       "            <td>2015-04-14</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+------------+----------------------+-----------------+-------------+------------+\n",
       "| Month_Name |   Landing_Outcome    | Booster_Version | Launch_Site |    Date    |\n",
       "+------------+----------------------+-----------------+-------------+------------+\n",
       "|  January   | Failure (drone ship) |  F9 v1.1 B1012  | CCAFS LC-40 | 2015-01-10 |\n",
       "|   April    | Failure (drone ship) |  F9 v1.1 B1015  | CCAFS LC-40 | 2015-04-14 |\n",
       "+------------+----------------------+-----------------+-------------+------------+"
      ]
     },
     "execution_count": 41,
     "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,  -- Extract month (2 digits) and renema it to names of months\n",
    "  Landing_Outcome,\n",
    "  Booster_Version,\n",
    "  Launch_Site,\n",
    "  Date\n",
    "FROM SPACEXTABLE\n",
    "WHERE Landing_Outcome = 'Failure (drone ship)'\n",
    "AND substr(Date, 1, 4) = '2015'  -- Filter for year 2015"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 10\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "##### Rank the count of landing outcomes (such as Failure (drone ship) or Success (ground pad)) between the date 2010-06-04 and 2017-03-20, in descending order.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;sqlite:///my_data1.db&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'sqlite:///my_data1.db'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Landing_Outcome</th>\n",
       "            <th>Count</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Success (drone ship)</td>\n",
       "            <td>5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Failure (drone ship)</td>\n",
       "            <td>5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Success (ground pad)</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+----------------------+-------+\n",
       "|   Landing_Outcome    | Count |\n",
       "+----------------------+-------+\n",
       "| Success (drone ship) |   5   |\n",
       "| Failure (drone ship) |   5   |\n",
       "| Success (ground pad) |   3   |\n",
       "+----------------------+-------+"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql SELECT Landing_Outcome, COUNT(*) AS Count\n",
    "FROM SPACEXTABLE\n",
    "WHERE Landing_Outcome IN ('Success (ground pad)', 'Failure (drone ship)', 'Failure (ocean)', 'Success (drone ship)')\n",
    "  AND DATE BETWEEN '2010-06-04' AND '2017-03-20'\n",
    "GROUP BY Landing_Outcome\n",
    "ORDER BY Count DESC;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reference Links\n",
    "\n",
    "* <a href =\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Labs_Coursera_V5/labs/Lab%20-%20String%20Patterns%20-%20Sorting%20-%20Grouping/instructional-labs.md.html?origin=www.coursera.org\">Hands-on Lab : String Patterns, Sorting and Grouping</a>  \n",
    "\n",
    "*  <a  href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Labs_Coursera_V5/labs/Lab%20-%20Built-in%20functions%20/Hands-on_Lab__Built-in_Functions.md.html?origin=www.coursera.org\">Hands-on Lab: Built-in functions</a>\n",
    "\n",
    "*  <a  href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Labs_Coursera_V5/labs/Lab%20-%20Sub-queries%20and%20Nested%20SELECTs%20/instructional-labs.md.html?origin=www.coursera.org\">Hands-on Lab : Sub-queries and Nested SELECT Statements</a>\n",
    "\n",
    "*   <a href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Module%205/DB0201EN-Week3-1-3-SQLmagic.ipynb\">Hands-on Tutorial: Accessing Databases with SQL magic</a>\n",
    "\n",
    "*  <a href= \"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Module%205/DB0201EN-Week3-1-4-Analyzing.ipynb\">Hands-on Lab: Analyzing a real World Data Set</a>\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Author(s)\n",
    "\n",
    "<h4> Lakshmi Holla </h4>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Other Contributors\n",
    "\n",
    "<h4> Rav Ahuja </h4>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 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": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}