part3-eda-sql.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": "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",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-05T13:39:35.503769Z",
     "start_time": "2024-09-05T13:39:35.501191Z"
    }
   },
   "source": [
    "%load_ext sql"
   ],
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The sql extension is already loaded. To reload it, use:\n",
      "  %reload_ext sql\n"
     ]
    }
   ],
   "execution_count": 11
  },
  {
   "cell_type": "code",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-05T13:39:35.534976Z",
     "start_time": "2024-09-05T13:39:35.532211Z"
    }
   },
   "source": [
    "import csv, sqlite3\n",
    "\n",
    "con = sqlite3.connect(\"my_data1.db\")\n",
    "cur = con.cursor()"
   ],
   "outputs": [],
   "execution_count": 12
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-05T13:39:35.545382Z",
     "start_time": "2024-09-05T13:39:35.543310Z"
    }
   },
   "cell_type": "code",
   "source": "%sql sqlite:///my_data1.db",
   "outputs": [],
   "execution_count": 13
  },
  {
   "cell_type": "code",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-09-05T13:39:36.402268Z",
     "start_time": "2024-09-05T13:39:35.553437Z"
    }
   },
   "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\")"
   ],
   "outputs": [
    {
     "data": {
      "text/plain": [
       "101"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 14
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Note:This below code is added to remove blank rows from table**\n"
   ]
  },
  {
   "metadata": {
    "SqlCellData": {
     "data_source_name": "my_data1.db",
     "variableName$1": "df_sql1"
    },
    "ExecuteTime": {
     "end_time": "2024-09-05T13:39:36.431711Z",
     "start_time": "2024-09-05T13:39:36.405670Z"
    }
   },
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "%%sql\n",
    "DROP TABLE IF EXISTS SPACEXTABLE;"
   ],
   "outputs": []
  },
  {
   "metadata": {
    "SqlCellData": {
     "data_source_name": "my_data1.db",
     "variableName$1": "df_sql2"
    },
    "ExecuteTime": {
     "end_time": "2024-09-05T13:39:36.451111Z",
     "start_time": "2024-09-05T13:39:36.432157Z"
    }
   },
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "%%sql\n",
    "create table SPACEXTABLE as \n",
    "    select * from SPACEXTBL where Date is not null;"
   ],
   "outputs": []
  },
  {
   "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"
   ]
  },
  {
   "metadata": {
    "SqlCellData": {
     "data_source_name": "my_data1.db",
     "variableName$1": "df_sql3"
    },
    "ExecuteTime": {
     "end_time": "2024-09-05T13:39:36.479008Z",
     "start_time": "2024-09-05T13:39:36.451565Z"
    }
   },
   "cell_type": "code",
   "source": [
    "%%sql\n",
    "SELECT DISTINCT Launch_Site FROM SPACEXTABLE;"
   ],
   "outputs": [
    {
     "data": {
      "text/plain": [
       "    Launch_Site\n",
       "0   CCAFS LC-40\n",
       "1   VAFB SLC-4E\n",
       "2    KSC LC-39A\n",
       "3  CCAFS SLC-40"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Launch_Site</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CCAFS LC-40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>VAFB SLC-4E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>KSC LC-39A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CCAFS SLC-40</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 15
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "### Task 2\n",
    "\n",
    "\n",
    "#####  Display 5 records where launch sites begin with the string 'CCA' \n"
   ]
  },
  {
   "metadata": {
    "SqlCellData": {
     "data_source_name": "my_data1.db",
     "variableName$1": "df_sql4"
    },
    "ExecuteTime": {
     "end_time": "2024-09-05T13:39:36.545712Z",
     "start_time": "2024-09-05T13:39:36.480873Z"
    }
   },
   "cell_type": "code",
   "source": [
    "%%sql\n",
    "SELECT * \n",
    "FROM SPACEXTABLE WHERE \n",
    "    Launch_Site LIKE 'CCA%' \n",
    "LIMIT 5;"
   ],
   "outputs": [
    {
     "data": {
      "text/plain": [
       "         Date Time (UTC) Booster_Version  Launch_Site  \\\n",
       "0  2010-06-04   18:45:00  F9 v1.0  B0003  CCAFS LC-40   \n",
       "1  2010-12-08   15:43:00  F9 v1.0  B0004  CCAFS LC-40   \n",
       "2  2012-05-22    7:44:00  F9 v1.0  B0005  CCAFS LC-40   \n",
       "3  2012-10-08    0:35:00  F9 v1.0  B0006  CCAFS LC-40   \n",
       "4  2013-03-01   15:10:00  F9 v1.0  B0007  CCAFS LC-40   \n",
       "\n",
       "                                             Payload  PAYLOAD_MASS__KG_  \\\n",
       "0               Dragon Spacecraft Qualification Unit                  0   \n",
       "1  Dragon demo flight C1, two CubeSats, barrel of...                  0   \n",
       "2                              Dragon demo flight C2                525   \n",
       "3                                       SpaceX CRS-1                500   \n",
       "4                                       SpaceX CRS-2                677   \n",
       "\n",
       "       Orbit         Customer Mission_Outcome      Landing_Outcome  \n",
       "0        LEO           SpaceX         Success  Failure (parachute)  \n",
       "1  LEO (ISS)  NASA (COTS) NRO         Success  Failure (parachute)  \n",
       "2  LEO (ISS)      NASA (COTS)         Success           No attempt  \n",
       "3  LEO (ISS)       NASA (CRS)         Success           No attempt  \n",
       "4  LEO (ISS)       NASA (CRS)         Success           No attempt  "
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\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",
       "      <th>0</th>\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",
       "      <th>1</th>\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...</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",
       "      <th>2</th>\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",
       "      <th>3</th>\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",
       "      <th>4</th>\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>\n",
       "</div>"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 16
  },
  {
   "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"
   ]
  },
  {
   "metadata": {
    "SqlCellData": {
     "data_source_name": "my_data1.db",
     "variableName$1": "df_sql5"
    },
    "ExecuteTime": {
     "end_time": "2024-09-05T13:39:36.673029Z",
     "start_time": "2024-09-05T13:39:36.547592Z"
    }
   },
   "cell_type": "code",
   "source": [
    "%%sql\n",
    "SELECT \n",
    "    SUM(PAYLOAD_MASS__KG_) \n",
    "FROM SPACEXTABLE WHERE \n",
    "    Customer LIKE 'NASA%';"
   ],
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   SUM(PAYLOAD_MASS__KG_)\n",
       "0                   99980"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SUM(PAYLOAD_MASS__KG_)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>99980</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 17
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 4\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "##### Display average payload mass carried by booster version F9 v1.1\n"
   ]
  },
  {
   "metadata": {
    "SqlCellData": {
     "data_source_name": "my_data1.db",
     "variableName$1": "df_sql6"
    },
    "ExecuteTime": {
     "end_time": "2024-09-05T13:39:36.867240Z",
     "start_time": "2024-09-05T13:39:36.675463Z"
    }
   },
   "cell_type": "code",
   "source": [
    "%%sql\n",
    "SELECT \n",
    "    AVG(PAYLOAD_MASS__KG_) \n",
    "FROM SPACEXTABLE WHERE\n",
    "    Booster_Version LIKE 'F9 v1.1%';"
   ],
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   AVG(PAYLOAD_MASS__KG_)\n",
       "0             2534.666667"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AVG(PAYLOAD_MASS__KG_)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2534.666667</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 18
  },
  {
   "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"
   ]
  },
  {
   "metadata": {
    "SqlCellData": {
     "data_source_name": "my_data1.db",
     "variableName$1": "df_sql7"
    },
    "ExecuteTime": {
     "end_time": "2024-09-05T13:39:37.043315Z",
     "start_time": "2024-09-05T13:39:36.868993Z"
    }
   },
   "cell_type": "code",
   "source": [
    "%%sql\n",
    "SELECT \n",
    "    MIN(Date) \n",
    "FROM SPACEXTABLE WHERE \n",
    "    Landing_Outcome LIKE 'Success (ground pad)';"
   ],
   "outputs": [
    {
     "data": {
      "text/plain": [
       "    MIN(Date)\n",
       "0  2015-12-22"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>MIN(Date)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015-12-22</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 19
  },
  {
   "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"
   ]
  },
  {
   "metadata": {
    "SqlCellData": {
     "data_source_name": "my_data1.db",
     "variableName$1": "df_sql8"
    },
    "ExecuteTime": {
     "end_time": "2024-09-05T13:39:37.222755Z",
     "start_time": "2024-09-05T13:39:37.045329Z"
    }
   },
   "cell_type": "code",
   "source": [
    "%%sql\n",
    "SELECT \n",
    "    Booster_Version \n",
    "FROM SPACEXTABLE WHERE \n",
    "    Landing_Outcome LIKE 'Success (drone ship)' AND \n",
    "    PAYLOAD_MASS__KG_ > 4000 AND \n",
    "    PAYLOAD_MASS__KG_ < 6000;"
   ],
   "outputs": [
    {
     "data": {
      "text/plain": [
       "  Booster_Version\n",
       "0     F9 FT B1022\n",
       "1     F9 FT B1026\n",
       "2  F9 FT  B1021.2\n",
       "3  F9 FT  B1031.2"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Booster_Version</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>F9 FT B1022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>F9 FT B1026</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>F9 FT  B1021.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>F9 FT  B1031.2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 20
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 7\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "##### List the total number of successful and failure mission outcomes\n"
   ]
  },
  {
   "metadata": {
    "SqlCellData": {
     "data_source_name": "my_data1.db",
     "variableName$1": "df_sql9"
    },
    "ExecuteTime": {
     "end_time": "2024-09-05T13:39:37.356298Z",
     "start_time": "2024-09-05T13:39:37.230205Z"
    }
   },
   "cell_type": "code",
   "source": [
    "%%sql\n",
    "SELECT \n",
    "    Mission_Outcome,\n",
    "    COUNT(*) AS value \n",
    "FROM SPACEXTABLE \n",
    "GROUP BY Mission_Outcome;"
   ],
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                    Mission_Outcome  value\n",
       "0               Failure (in flight)      1\n",
       "1                           Success     98\n",
       "2                          Success       1\n",
       "3  Success (payload status unclear)      1"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Mission_Outcome</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Failure (in flight)</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Success</td>\n",
       "      <td>98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Success</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Success (payload status unclear)</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 21
  },
  {
   "metadata": {
    "SqlCellData": {
     "data_source_name": "my_data1.db",
     "variableName$1": "df_sql15"
    },
    "ExecuteTime": {
     "end_time": "2024-09-05T13:39:37.510773Z",
     "start_time": "2024-09-05T13:39:37.358102Z"
    }
   },
   "cell_type": "code",
   "source": [
    "%%sql\n",
    "SELECT\n",
    "    SUM(CASE WHEN Mission_Outcome LIKE 'Success%' THEN 1 ELSE 0 END) AS Nb_Success,\n",
    "    SUM(CASE WHEN Mission_Outcome LIKE 'Failure%' THEN 1 ELSE 0 END) AS Nb_Failure\n",
    "FROM SPACEXTABLE;"
   ],
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   Nb_Success  Nb_Failure\n",
       "0         100           1"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Nb_Success</th>\n",
       "      <th>Nb_Failure</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 22
  },
  {
   "metadata": {
    "SqlCellData": {
     "data_source_name": "my_data1.db",
     "variableName$1": "df_sql10"
    },
    "ExecuteTime": {
     "end_time": "2024-09-05T13:39:37.637122Z",
     "start_time": "2024-09-05T13:39:37.512532Z"
    }
   },
   "cell_type": "code",
   "source": [
    "%%sql\n",
    "SELECT \n",
    "    count(Mission_Outcome) \n",
    "FROM SPACEXTABLE WHERE \n",
    "    Mission_Outcome LIKE 'Success%';"
   ],
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   count(Mission_Outcome)\n",
       "0                     100"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count(Mission_Outcome)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 23
  },
  {
   "metadata": {
    "SqlCellData": {
     "data_source_name": "my_data1.db",
     "variableName$1": "df_sql11"
    },
    "ExecuteTime": {
     "end_time": "2024-09-05T13:39:37.770736Z",
     "start_time": "2024-09-05T13:39:37.638559Z"
    }
   },
   "cell_type": "code",
   "source": [
    "%%sql\n",
    "SELECT \n",
    "    count(Mission_Outcome) \n",
    "FROM SPACEXTABLE WHERE \n",
    "    Mission_Outcome LIKE 'Failure%';"
   ],
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   count(Mission_Outcome)\n",
       "0                       1"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count(Mission_Outcome)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 24
  },
  {
   "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"
   ]
  },
  {
   "metadata": {
    "SqlCellData": {
     "data_source_name": "my_data1.db",
     "variableName$1": "df_sql12"
    },
    "ExecuteTime": {
     "end_time": "2024-09-05T13:39:37.928275Z",
     "start_time": "2024-09-05T13:39:37.772730Z"
    }
   },
   "cell_type": "code",
   "source": [
    "%%sql\n",
    "SELECT \n",
    "    Booster_Version \n",
    "FROM SPACEXTABLE WHERE \n",
    "    PAYLOAD_MASS__KG_ = (\n",
    "        SELECT MAX(PAYLOAD_MASS__KG_) FROM SPACEXTABLE\n",
    "    );"
   ],
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   Booster_Version\n",
       "0    F9 B5 B1048.4\n",
       "1    F9 B5 B1049.4\n",
       "2    F9 B5 B1051.3\n",
       "3    F9 B5 B1056.4\n",
       "4    F9 B5 B1048.5\n",
       "5    F9 B5 B1051.4\n",
       "6    F9 B5 B1049.5\n",
       "7   F9 B5 B1060.2 \n",
       "8   F9 B5 B1058.3 \n",
       "9    F9 B5 B1051.6\n",
       "10   F9 B5 B1060.3\n",
       "11  F9 B5 B1049.7 "
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Booster_Version</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>F9 B5 B1048.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>F9 B5 B1049.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>F9 B5 B1051.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>F9 B5 B1056.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>F9 B5 B1048.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>F9 B5 B1051.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>F9 B5 B1049.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>F9 B5 B1060.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>F9 B5 B1058.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>F9 B5 B1051.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>F9 B5 B1060.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>F9 B5 B1049.7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 25
  },
  {
   "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"
   ]
  },
  {
   "metadata": {
    "SqlCellData": {
     "data_source_name": "my_data1.db",
     "variableName$1": "df_sql13"
    },
    "ExecuteTime": {
     "end_time": "2024-09-05T13:39:38.067453Z",
     "start_time": "2024-09-05T13:39:37.929956Z"
    }
   },
   "cell_type": "code",
   "source": [
    "%%sql\n",
    "SELECT \n",
    "    substr(Date, 6,2) as month, \n",
    "    Landing_Outcome, \n",
    "    Booster_Version, \n",
    "    Launch_Site \n",
    "FROM SPACEXTABLE WHERE \n",
    "    substr(Date,0,5)='2015' AND \n",
    "    Landing_Outcome LIKE 'Failure (drone ship)';"
   ],
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   month       Landing_Outcome Booster_Version  Launch_Site\n",
       "0      1  Failure (drone ship)   F9 v1.1 B1012  CCAFS LC-40\n",
       "1      4  Failure (drone ship)   F9 v1.1 B1015  CCAFS LC-40"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\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",
       "      <th>0</th>\n",
       "      <td>1</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",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>Failure (drone ship)</td>\n",
       "      <td>F9 v1.1 B1015</td>\n",
       "      <td>CCAFS LC-40</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 26
  },
  {
   "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"
   ]
  },
  {
   "metadata": {
    "SqlCellData": {
     "data_source_name": "my_data1.db",
     "variableName$1": "df_sql14"
    },
    "ExecuteTime": {
     "end_time": "2024-09-05T13:39:38.171830Z",
     "start_time": "2024-09-05T13:39:38.071901Z"
    }
   },
   "cell_type": "code",
   "source": [
    "%%sql\n",
    "SELECT \n",
    "    Landing_Outcome, \n",
    "    COUNT(*) value \n",
    "FROM SPACEXTABLE WHERE \n",
    "    Date BETWEEN '2010-06-04' AND '2017-03-20' \n",
    "GROUP BY \"Landing_Outcome\" ORDER BY value DESC;"
   ],
   "outputs": [
    {
     "data": {
      "text/plain": [
       "          Landing_Outcome  value\n",
       "0              No attempt     10\n",
       "1    Success (drone ship)      5\n",
       "2    Failure (drone ship)      5\n",
       "3    Success (ground pad)      3\n",
       "4      Controlled (ocean)      3\n",
       "5    Uncontrolled (ocean)      2\n",
       "6     Failure (parachute)      2\n",
       "7  Precluded (drone ship)      1"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Landing_Outcome</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>No attempt</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Success (drone ship)</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Failure (drone ship)</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Success (ground pad)</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Controlled (ocean)</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Uncontrolled (ocean)</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Failure (parachute)</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Precluded (drone ship)</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 27
  },
  {
   "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": [
    "<!--\n",
    "## Change log\n",
    "| Date | Version | Changed by | Change Description |\n",
    "|------|--------|--------|---------|\n",
    "| 2024-07-10 | 1.1 |Anita Verma | Changed Version|\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",
    "-->\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## <h3 align=\"center\"> © IBM Corporation 2021. All rights reserved. <h3/>\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10.13"
  },
  "prev_pub_hash": "a5fa84525a820d08c9ec9c9cccf7d39455745aa2be6c2efdd18931346283d2d9"
 },
 "nbformat": 4,
 "nbformat_minor": 4
}