EDA_with_SQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "\n",
    "\n",
    "\n",
    "## Introduction\n",
    "\n",
    "Using this Python notebook we have:\n",
    "\n",
    "1.  Understand the Spacex DataSet\n",
    "2.  Load the dataset  into the corresponding table in a Db2 database\n",
    "3.  Execute SQL queries to answer assignment questions\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Overview of the DataSet\n",
    "\n",
    "SpaceX has gained worldwide attention for a series of historic milestones.\n",
    "\n",
    "It is the only private company ever to return a spacecraft from low-earth orbit, which it first accomplished in December 2010.\n",
    "SpaceX advertises Falcon 9 rocket launches on its website with a cost of 62 million dollars wheras other providers cost upward of 165 million dollars each, much of the savings is because Space X can reuse the first stage.\n",
    "\n",
    "Therefore if we can determine if the first stage will land, we can determine the cost of a launch.\n",
    "\n",
    "This information can be used if an alternate company wants to bid against SpaceX for a rocket launch.\n",
    "\n",
    "This dataset includes a record for each payload carried during a SpaceX mission into outer space.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Download the datasets\n",
    "\n",
    "This assignment requires you to load the spacex dataset.\n",
    "\n",
    "In many cases the dataset to be analyzed is available as a .CSV (comma separated values) file, perhaps on the internet. Click on the link below to download and save the dataset (.CSV file):\n",
    "\n",
    "<a href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/module_2/data/Spacex.csv?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDS0321ENSkillsNetwork26802033-2022-01-01\" target=\"_blank\">Spacex DataSet</a>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: sqlalchemy==1.3.9 in /Users/rajbir/opt/anaconda3/lib/python3.9/site-packages (1.3.9)\r\n"
     ]
    }
   ],
   "source": [
    "!pip install sqlalchemy==1.3.9\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Connect to the database\n",
    "\n",
    "Let us first load the SQL extension and establish a connection with the database\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv, sqlite3\n",
    "\n",
    "con = sqlite3.connect(\"my_data1.db\")\n",
    "cur = con.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip install -q pandas==1.1.5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: @my_data1.db'"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql sqlite:///my_data1.db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n",
      " * sqlite:///my_data1.db\n",
      "Done.\n",
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/rajbir/opt/anaconda3/lib/python3.9/site-packages/pandas/core/generic.py:2605: UserWarning: The spaces in these column names will not be changed. In pandas versions < 0.14, spaces were converted to underscores.\n",
      "  sql.to_sql(\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>name</th>\n",
       "            <th>type</th>\n",
       "            <th>length(type)</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Date</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Time (UTC)</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Booster_Version</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Launch_Site</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Payload</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>PAYLOAD_MASS__KG_</td>\n",
       "            <td>INTEGER</td>\n",
       "            <td>7</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Orbit</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Customer</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Mission_Outcome</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Landing _Outcome</td>\n",
       "            <td>TEXT</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Date', 'TEXT', 4),\n",
       " ('Time (UTC)', 'TEXT', 4),\n",
       " ('Booster_Version', 'TEXT', 4),\n",
       " ('Launch_Site', 'TEXT', 4),\n",
       " ('Payload', 'TEXT', 4),\n",
       " ('PAYLOAD_MASS__KG_', 'INTEGER', 7),\n",
       " ('Orbit', 'TEXT', 4),\n",
       " ('Customer', 'TEXT', 4),\n",
       " ('Mission_Outcome', 'TEXT', 4),\n",
       " ('Landing _Outcome', 'TEXT', 4)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\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\")\n",
    "\n",
    "%sql SELECT name FROM sqlite_master WHERE type='table'\n",
    "%sql SELECT count(name) FROM PRAGMA_TABLE_INFO('SPACEXTBL');\n",
    "%sql SELECT name,type,length(type) FROM PRAGMA_TABLE_INFO('SPACEXTBL');\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "\n",
    "##### Display the names of the unique launch sites  in the space mission\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Launch_Site</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>CCAFS LC-40</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>VAFB SLC-4E</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>KSC LC-39A</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>CCAFS SLC-40</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('CCAFS LC-40',), ('VAFB SLC-4E',), ('KSC LC-39A',), ('CCAFS SLC-40',)]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select distinct Launch_Site from SPACEXTBL \n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "##### Display 5 records where launch sites begin with the string 'CCA'\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Date</th>\n",
       "            <th>Time (UTC)</th>\n",
       "            <th>Booster_Version</th>\n",
       "            <th>Launch_Site</th>\n",
       "            <th>Payload</th>\n",
       "            <th>PAYLOAD_MASS__KG_</th>\n",
       "            <th>Orbit</th>\n",
       "            <th>Customer</th>\n",
       "            <th>Mission_Outcome</th>\n",
       "            <th>Landing _Outcome</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>04-06-2010</td>\n",
       "            <td>18:45:00</td>\n",
       "            <td>F9 v1.0  B0003</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "            <td>Dragon Spacecraft Qualification Unit</td>\n",
       "            <td>0</td>\n",
       "            <td>LEO</td>\n",
       "            <td>SpaceX</td>\n",
       "            <td>Success</td>\n",
       "            <td>Failure (parachute)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>08-12-2010</td>\n",
       "            <td>15:43:00</td>\n",
       "            <td>F9 v1.0  B0004</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "            <td>Dragon demo flight C1, two CubeSats, barrel of Brouere cheese</td>\n",
       "            <td>0</td>\n",
       "            <td>LEO (ISS)</td>\n",
       "            <td>NASA (COTS) NRO</td>\n",
       "            <td>Success</td>\n",
       "            <td>Failure (parachute)</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>22-05-2012</td>\n",
       "            <td>07:44:00</td>\n",
       "            <td>F9 v1.0  B0005</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "            <td>Dragon demo flight C2</td>\n",
       "            <td>525</td>\n",
       "            <td>LEO (ISS)</td>\n",
       "            <td>NASA (COTS)</td>\n",
       "            <td>Success</td>\n",
       "            <td>No attempt</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>08-10-2012</td>\n",
       "            <td>00:35:00</td>\n",
       "            <td>F9 v1.0  B0006</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "            <td>SpaceX CRS-1</td>\n",
       "            <td>500</td>\n",
       "            <td>LEO (ISS)</td>\n",
       "            <td>NASA (CRS)</td>\n",
       "            <td>Success</td>\n",
       "            <td>No attempt</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>01-03-2013</td>\n",
       "            <td>15:10:00</td>\n",
       "            <td>F9 v1.0  B0007</td>\n",
       "            <td>CCAFS LC-40</td>\n",
       "            <td>SpaceX CRS-2</td>\n",
       "            <td>677</td>\n",
       "            <td>LEO (ISS)</td>\n",
       "            <td>NASA (CRS)</td>\n",
       "            <td>Success</td>\n",
       "            <td>No attempt</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('04-06-2010', '18:45:00', 'F9 v1.0  B0003', 'CCAFS LC-40', 'Dragon Spacecraft Qualification Unit', 0, 'LEO', 'SpaceX', 'Success', 'Failure (parachute)'),\n",
       " ('08-12-2010', '15:43:00', 'F9 v1.0  B0004', 'CCAFS LC-40', 'Dragon demo flight C1, two CubeSats, barrel of Brouere cheese', 0, 'LEO (ISS)', 'NASA (COTS) NRO', 'Success', 'Failure (parachute)'),\n",
       " ('22-05-2012', '07:44:00', 'F9 v1.0  B0005', 'CCAFS LC-40', 'Dragon demo flight C2', 525, 'LEO (ISS)', 'NASA (COTS)', 'Success', 'No attempt'),\n",
       " ('08-10-2012', '00:35:00', 'F9 v1.0  B0006', 'CCAFS LC-40', 'SpaceX CRS-1', 500, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'No attempt'),\n",
       " ('01-03-2013', '15:10:00', 'F9 v1.0  B0007', 'CCAFS LC-40', 'SpaceX CRS-2', 677, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'No attempt')]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select * from SPACEXTBL where \"Launch_Site\" like 'CCA%' LIMIT 5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "##### Display the total payload mass carried by boosters launched by NASA (CRS)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>SUM(PAYLOAD_MASS__KG_)</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>45596</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(45596,)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT SUM(PAYLOAD_MASS__KG_) from SPACEXTBL WHERE Customer = 'NASA (CRS)'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "##### Display average payload mass carried by booster version F9 v1.1\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>AVG(PAYLOAD_MASS__KG_)</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>2928.4</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2928.4,)]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT AVG(PAYLOAD_MASS__KG_) from SPACEXTBL WHERE Booster_Version = 'F9 v1.1'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "##### List the date when the first succesful landing outcome in ground pad was acheived.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Landing _Outcome</th>\n",
       "            <th>min([Date])</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Success (ground pad)</td>\n",
       "            <td>01-05-2017</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Success (ground pad)', '01-05-2017')]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select [Landing _Outcome], min([Date]) from SPACEXTBL  where [Landing _Outcome] = 'Success (ground pad)' "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\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": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Booster_Version</th>\n",
       "        </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": [
       "[('F9 FT B1022',), ('F9 FT B1026',), ('F9 FT  B1021.2',), ('F9 FT  B1031.2',)]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select Booster_Version from SPACEXTBL where [Landing _Outcome] = 'Success (drone ship)' and PAYLOAD_MASS__KG_ between 4000 and 6000"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "##### List the total number of successful and failure mission outcomes\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>(ltrim(rtrim(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>99</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Success (payload status unclear)</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Failure (in flight)', 1),\n",
       " ('Success', 99),\n",
       " ('Success (payload status unclear)', 1)]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select distinct(ltrim(rtrim(Mission_Outcome))), count(*) from  SPACEXTBL group by ltrim(rtrim(Mission_Outcome))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "##### List the   names of the booster_versions which have carried the maximum payload mass. Use a subquery\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Booster_Version</th>\n",
       "            <th>PAYLOAD_MASS__KG_</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1048.4</td>\n",
       "            <td>15600</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1049.4</td>\n",
       "            <td>15600</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1051.3</td>\n",
       "            <td>15600</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1056.4</td>\n",
       "            <td>15600</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1048.5</td>\n",
       "            <td>15600</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1051.4</td>\n",
       "            <td>15600</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1049.5</td>\n",
       "            <td>15600</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1060.2 </td>\n",
       "            <td>15600</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1058.3 </td>\n",
       "            <td>15600</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1051.6</td>\n",
       "            <td>15600</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1060.3</td>\n",
       "            <td>15600</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>F9 B5 B1049.7 </td>\n",
       "            <td>15600</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('F9 B5 B1048.4', 15600),\n",
       " ('F9 B5 B1049.4', 15600),\n",
       " ('F9 B5 B1051.3', 15600),\n",
       " ('F9 B5 B1056.4', 15600),\n",
       " ('F9 B5 B1048.5', 15600),\n",
       " ('F9 B5 B1051.4', 15600),\n",
       " ('F9 B5 B1049.5', 15600),\n",
       " ('F9 B5 B1060.2 ', 15600),\n",
       " ('F9 B5 B1058.3 ', 15600),\n",
       " ('F9 B5 B1051.6', 15600),\n",
       " ('F9 B5 B1060.3', 15600),\n",
       " ('F9 B5 B1049.7 ', 15600)]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select Booster_Version, PAYLOAD_MASS__KG_ from SPACEXTBL where PAYLOAD_MASS__KG_ = (select  max(PAYLOAD_MASS__KG_) from SPACEXTBL)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\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.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>substr(Date, 4, 2)</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>04</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>"
      ],
      "text/plain": [
       "[('01', 'Failure (drone ship)', 'F9 v1.1 B1012', 'CCAFS LC-40'),\n",
       " ('04', 'Failure (drone ship)', 'F9 v1.1 B1015', 'CCAFS LC-40')]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select substr(Date, 4, 2), [Landing _Outcome], Booster_Version, Launch_Site from SPACEXTBL where substr(Date,7,4)='2015' and [Landing _Outcome] like '%Fail%'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "##### Rank the  count of  successful landing_outcomes between the date 04-06-2010 and 20-03-2017 in descending order.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Date</th>\n",
       "            <th>count(date)</th>\n",
       "            <th>rank_no</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>06-12-2020</td>\n",
       "            <td>1</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>16-11-2020</td>\n",
       "            <td>1</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>05-11-2020</td>\n",
       "            <td>1</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>18-10-2020</td>\n",
       "            <td>1</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>06-10-2020</td>\n",
       "            <td>1</td>\n",
       "            <td>5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>18-08-2020</td>\n",
       "            <td>1</td>\n",
       "            <td>6</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>07-08-2020</td>\n",
       "            <td>1</td>\n",
       "            <td>7</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>13-06-2020</td>\n",
       "            <td>1</td>\n",
       "            <td>8</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>04-06-2020</td>\n",
       "            <td>1</td>\n",
       "            <td>9</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>07-03-2020</td>\n",
       "            <td>1</td>\n",
       "            <td>10</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>07-01-2020</td>\n",
       "            <td>1</td>\n",
       "            <td>11</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>17-12-2019</td>\n",
       "            <td>1</td>\n",
       "            <td>12</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>05-12-2019</td>\n",
       "            <td>1</td>\n",
       "            <td>13</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>11-11-2019</td>\n",
       "            <td>1</td>\n",
       "            <td>14</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>12-06-2019</td>\n",
       "            <td>1</td>\n",
       "            <td>15</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>11-01-2019</td>\n",
       "            <td>1</td>\n",
       "            <td>16</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>15-11-2018</td>\n",
       "            <td>1</td>\n",
       "            <td>17</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>08-10-2018</td>\n",
       "            <td>1</td>\n",
       "            <td>18</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>10-09-2018</td>\n",
       "            <td>1</td>\n",
       "            <td>19</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>07-08-2018</td>\n",
       "            <td>1</td>\n",
       "            <td>20</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>11-05-2018</td>\n",
       "            <td>1</td>\n",
       "            <td>21</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>18-04-2018</td>\n",
       "            <td>1</td>\n",
       "            <td>22</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>08-01-2018</td>\n",
       "            <td>1</td>\n",
       "            <td>23</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>15-12-2017</td>\n",
       "            <td>1</td>\n",
       "            <td>24</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>11-10-2017</td>\n",
       "            <td>1</td>\n",
       "            <td>25</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>09-10-2017</td>\n",
       "            <td>1</td>\n",
       "            <td>26</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>07-09-2017</td>\n",
       "            <td>1</td>\n",
       "            <td>27</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>14-08-2017</td>\n",
       "            <td>1</td>\n",
       "            <td>28</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>19-02-2017</td>\n",
       "            <td>1</td>\n",
       "            <td>29</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>14-01-2017</td>\n",
       "            <td>1</td>\n",
       "            <td>30</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>14-08-2016</td>\n",
       "            <td>1</td>\n",
       "            <td>31</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>18-07-2016</td>\n",
       "            <td>1</td>\n",
       "            <td>32</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>06-05-2016</td>\n",
       "            <td>1</td>\n",
       "            <td>33</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>08-04-2016</td>\n",
       "            <td>1</td>\n",
       "            <td>34</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('06-12-2020', 1, 1),\n",
       " ('16-11-2020', 1, 2),\n",
       " ('05-11-2020', 1, 3),\n",
       " ('18-10-2020', 1, 4),\n",
       " ('06-10-2020', 1, 5),\n",
       " ('18-08-2020', 1, 6),\n",
       " ('07-08-2020', 1, 7),\n",
       " ('13-06-2020', 1, 8),\n",
       " ('04-06-2020', 1, 9),\n",
       " ('07-03-2020', 1, 10),\n",
       " ('07-01-2020', 1, 11),\n",
       " ('17-12-2019', 1, 12),\n",
       " ('05-12-2019', 1, 13),\n",
       " ('11-11-2019', 1, 14),\n",
       " ('12-06-2019', 1, 15),\n",
       " ('11-01-2019', 1, 16),\n",
       " ('15-11-2018', 1, 17),\n",
       " ('08-10-2018', 1, 18),\n",
       " ('10-09-2018', 1, 19),\n",
       " ('07-08-2018', 1, 20),\n",
       " ('11-05-2018', 1, 21),\n",
       " ('18-04-2018', 1, 22),\n",
       " ('08-01-2018', 1, 23),\n",
       " ('15-12-2017', 1, 24),\n",
       " ('11-10-2017', 1, 25),\n",
       " ('09-10-2017', 1, 26),\n",
       " ('07-09-2017', 1, 27),\n",
       " ('14-08-2017', 1, 28),\n",
       " ('19-02-2017', 1, 29),\n",
       " ('14-01-2017', 1, 30),\n",
       " ('14-08-2016', 1, 31),\n",
       " ('18-07-2016', 1, 32),\n",
       " ('06-05-2016', 1, 33),\n",
       " ('08-04-2016', 1, 34)]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "%sql select date, count(date), rank() over (ORDER BY substr(Date,7,4) desc ,  substr(Date, 4, 2) desc, date desc) rank_no  from SPACEXTBL where (Date between  '04-06-2010' and '20-03-2017')  and ([Landing _Outcome] like '%Success%') group by Date \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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}