exploratory-data-analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<center>\n",
    "    <img src=\"https://gitlab.com/ibm/skills-network/courses/placeholder101/-/raw/master/labs/module%201/images/IDSNlogo.png\"  width=\"300\" alt=\"cognitiveclass.ai logo\"  />\n",
    "</center>\n",
    "\n",
    "<h1 align=center><font size = 5>Assignment: SQL Notebook for Peer Assignment</font></h1>\n",
    "\n",
    "Estimated time needed: **60** minutes.\n",
    "\n",
    "## Introduction\n",
    "\n",
    "Using this Python notebook you will:\n",
    "\n",
    "1.  Understand the Spacex DataSet\n",
    "2.  Load the dataset  into the corresponding table in a Db2 database\n",
    "3.  Execute SQL queries to answer assignment questions\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Overview of the DataSet\n",
    "\n",
    "SpaceX has gained worldwide attention for a series of historic milestones.\n",
    "\n",
    "It is the only private company ever to return a spacecraft from low-earth orbit, which it first accomplished in December 2010.\n",
    "SpaceX advertises Falcon 9 rocket launches on its website with a cost of 62 million dollars wheras other providers cost upward of 165 million dollars each, much of the savings is because Space X can reuse the first stage.\n",
    "\n",
    "Therefore if we can determine if the first stage will land, we can determine the cost of a launch.\n",
    "\n",
    "This information can be used if an alternate company wants to bid against SpaceX for a rocket launch.\n",
    "\n",
    "This dataset includes a record for each payload carried during a SpaceX mission into outer space.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Download the datasets\n",
    "\n",
    "This assignment requires you to load the spacex dataset.\n",
    "\n",
    "In many cases the dataset to be analyzed is available as a .CSV (comma separated values) file, perhaps on the internet. Click on the link below to download and save the dataset (.CSV file):\n",
    "\n",
    "<a href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/module_2/data/Spacex.csv?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDS0321ENSkillsNetwork26802033-2021-01-01\" target=\"_blank\">Spacex DataSet</a>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Store the dataset in database table\n",
    "\n",
    "**it is highly recommended to manually load the table using the database console LOAD tool in DB2**.\n",
    "\n",
    "<img src = \"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/module_2/images/spacexload.png\">\n",
    "\n",
    "Now open the Db2 console, open the LOAD tool, Select / Drag the .CSV file for the  dataset, Next create a New Table, and then follow the steps on-screen instructions to load the data. Name the new table as follows:\n",
    "\n",
    "**SPACEXDATASET**\n",
    "\n",
    "**Follow these steps while using old DB2 UI which is having Open Console Screen**\n",
    "\n",
    "**Note:While loading Spacex dataset, ensure that detect datatypes is disabled. Later click on the pencil icon(edit option).**\n",
    "\n",
    "1.  Change the Date Format by manually typing DD-MM-YYYY and timestamp format as DD-MM-YYYY HH\\:MM:SS.\n",
    "\n",
    "    Here you should place the cursor at Date field and manually type as DD-MM-YYYY.\n",
    "\n",
    "2.  Change the PAYLOAD_MASS\\_\\_KG\\_  datatype  to INTEGER.\n",
    "\n",
    "<img src = \"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/module_2/images/spacexload2.png\">\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Changes to be considered when having DB2 instance with the new UI having Go to UI screen**\n",
    "\n",
    "*   Refer to this insruction in this <a href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Labs_Coursera_V5/labs/Lab%20-%20Sign%20up%20for%20IBM%20Cloud%20-%20Create%20Db2%20service%20instance%20-%20Get%20started%20with%20the%20Db2%20console/instructional-labs.md.html?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDS0321ENSkillsNetwork26802033-2021-01-01\">link</a> for viewing  the new  Go to UI screen.\n",
    "\n",
    "*   Later click on **Data link(below SQL)**  in the Go to UI screen  and click on **Load Data** tab.\n",
    "\n",
    "*   Later browse for the downloaded spacex file.\n",
    "\n",
    "<img src=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/module_2/images/browsefile.png\" width=\"800\"/>\n",
    "\n",
    "*   Once done select the schema andload the file.\n",
    "\n",
    " <img src=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/module_2/images/spacexload3.png\" width=\"800\"/>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/opt/conda/envs/Python-3.7-OpenCE/lib/python3.7/site-packages/secretstorage/dhcrypto.py:16: CryptographyDeprecationWarning: int_from_bytes is deprecated, use int.from_bytes instead\n",
      "  from cryptography.utils import int_from_bytes\n",
      "/opt/conda/envs/Python-3.7-OpenCE/lib/python3.7/site-packages/secretstorage/util.py:25: CryptographyDeprecationWarning: int_from_bytes is deprecated, use int.from_bytes instead\n",
      "  from cryptography.utils import int_from_bytes\n",
      "Collecting sqlalchemy==1.3.9\n",
      "  Downloading SQLAlchemy-1.3.9.tar.gz (6.0 MB)\n",
      "\u001b[K     |████████████████████████████████| 6.0 MB 11.0 MB/s eta 0:00:01\n",
      "\u001b[?25hBuilding wheels for collected packages: sqlalchemy\n",
      "  Building wheel for sqlalchemy (setup.py) ... \u001b[?25ldone\n",
      "\u001b[?25h  Created wheel for sqlalchemy: filename=SQLAlchemy-1.3.9-cp37-cp37m-linux_x86_64.whl size=1207776 sha256=9cdcf6fe0133622f10e8c33f739a27fec972926c163f252f9911e73f8c0f81e0\n",
      "  Stored in directory: /tmp/wsuser/.cache/pip/wheels/03/71/13/010faf12246f72dc76b4150e6e599d13a85b4435e06fb9e51f\n",
      "Successfully built sqlalchemy\n",
      "Installing collected packages: sqlalchemy\n",
      "  Attempting uninstall: sqlalchemy\n",
      "    Found existing installation: SQLAlchemy 1.4.22\n",
      "    Uninstalling SQLAlchemy-1.4.22:\n",
      "      Successfully uninstalled SQLAlchemy-1.4.22\n",
      "Successfully installed sqlalchemy-1.3.9\n",
      "/opt/conda/envs/Python-3.7-OpenCE/lib/python3.7/site-packages/secretstorage/dhcrypto.py:16: CryptographyDeprecationWarning: int_from_bytes is deprecated, use int.from_bytes instead\n",
      "  from cryptography.utils import int_from_bytes\n",
      "/opt/conda/envs/Python-3.7-OpenCE/lib/python3.7/site-packages/secretstorage/util.py:25: CryptographyDeprecationWarning: int_from_bytes is deprecated, use int.from_bytes instead\n",
      "  from cryptography.utils import int_from_bytes\n",
      "Requirement already satisfied: ibm_db_sa in /opt/conda/envs/Python-3.7-OpenCE/lib/python3.7/site-packages (0.3.7)\n",
      "Requirement already satisfied: sqlalchemy>=0.7.3 in /opt/conda/envs/Python-3.7-OpenCE/lib/python3.7/site-packages (from ibm_db_sa) (1.3.9)\n",
      "Requirement already satisfied: ibm-db>=2.0.0 in /opt/conda/envs/Python-3.7-OpenCE/lib/python3.7/site-packages (from ibm_db_sa) (3.0.4)\n",
      "/opt/conda/envs/Python-3.7-OpenCE/lib/python3.7/site-packages/secretstorage/dhcrypto.py:16: CryptographyDeprecationWarning: int_from_bytes is deprecated, use int.from_bytes instead\n",
      "  from cryptography.utils import int_from_bytes\n",
      "/opt/conda/envs/Python-3.7-OpenCE/lib/python3.7/site-packages/secretstorage/util.py:25: CryptographyDeprecationWarning: int_from_bytes is deprecated, use int.from_bytes instead\n",
      "  from cryptography.utils import int_from_bytes\n",
      "Collecting ipython-sql\n",
      "  Downloading ipython_sql-0.4.0-py3-none-any.whl (19 kB)\n",
      "Requirement already satisfied: ipython>=1.0 in /opt/conda/envs/Python-3.7-OpenCE/lib/python3.7/site-packages (from ipython-sql) (7.27.0)\n",
      "Collecting prettytable<1\n",
      "  Downloading prettytable-0.7.2.tar.bz2 (21 kB)\n",
      "Requirement already satisfied: ipython-genutils>=0.1.0 in /opt/conda/envs/Python-3.7-OpenCE/lib/python3.7/site-packages (from ipython-sql) (0.2.0)\n",
      "Collecting sqlparse\n",
      "  Downloading sqlparse-0.4.2-py3-none-any.whl (42 kB)\n",
      "\u001b[K     |████████████████████████████████| 42 kB 2.0 MB/s  eta 0:00:01\n",
      "\u001b[?25hRequirement already satisfied: six in /opt/conda/envs/Python-3.7-OpenCE/lib/python3.7/site-packages (from ipython-sql) (1.15.0)\n",
      "Requirement already satisfied: sqlalchemy>=0.6.7 in /opt/conda/envs/Python-3.7-OpenCE/lib/python3.7/site-packages (from ipython-sql) (1.3.9)\n",
      "Requirement already satisfied: pexpect>4.3; sys_platform != \"win32\" in /opt/conda/envs/Python-3.7-OpenCE/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (4.8.0)\n",
      "Requirement already satisfied: setuptools>=18.5 in /opt/conda/envs/Python-3.7-OpenCE/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (47.3.1.post20200622)\n",
      "Requirement already satisfied: pickleshare in /opt/conda/envs/Python-3.7-OpenCE/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (0.7.5)\n",
      "Requirement already satisfied: backcall in /opt/conda/envs/Python-3.7-OpenCE/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (0.2.0)\n",
      "Requirement already satisfied: matplotlib-inline in /opt/conda/envs/Python-3.7-OpenCE/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (0.1.2)\n",
      "Requirement already satisfied: prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0 in /opt/conda/envs/Python-3.7-OpenCE/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (3.0.20)\n",
      "Requirement already satisfied: decorator in /opt/conda/envs/Python-3.7-OpenCE/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (4.4.2)\n",
      "Requirement already satisfied: traitlets>=4.2 in /opt/conda/envs/Python-3.7-OpenCE/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (4.3.3)\n",
      "Requirement already satisfied: pygments in /opt/conda/envs/Python-3.7-OpenCE/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (2.6.1)\n",
      "Requirement already satisfied: jedi>=0.16 in /opt/conda/envs/Python-3.7-OpenCE/lib/python3.7/site-packages (from ipython>=1.0->ipython-sql) (0.17.1)\n",
      "Requirement already satisfied: ptyprocess>=0.5 in /opt/conda/envs/Python-3.7-OpenCE/lib/python3.7/site-packages (from pexpect>4.3; sys_platform != \"win32\"->ipython>=1.0->ipython-sql) (0.6.0)\n",
      "Requirement already satisfied: wcwidth in /opt/conda/envs/Python-3.7-OpenCE/lib/python3.7/site-packages (from prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0->ipython>=1.0->ipython-sql) (0.2.4)\n",
      "Requirement already satisfied: parso<0.8.0,>=0.7.0 in /opt/conda/envs/Python-3.7-OpenCE/lib/python3.7/site-packages (from jedi>=0.16->ipython>=1.0->ipython-sql) (0.7.0)\n",
      "Building wheels for collected packages: prettytable\n",
      "  Building wheel for prettytable (setup.py) ... \u001b[?25ldone\n",
      "\u001b[?25h  Created wheel for prettytable: filename=prettytable-0.7.2-py3-none-any.whl size=13700 sha256=94c69a1deaa75735026b8d1199ca9938e609717d0dfd495f8111f42a180a2b2f\n",
      "  Stored in directory: /tmp/wsuser/.cache/pip/wheels/8c/76/0b/eb9eb3da7e2335e3577e3f96a0ae9f74f206e26457bd1a2bc8\n",
      "Successfully built prettytable\n",
      "Installing collected packages: prettytable, sqlparse, ipython-sql\n",
      "Successfully installed ipython-sql-0.4.0 prettytable-0.7.2 sqlparse-0.4.2\n"
     ]
    }
   ],
   "source": [
    "# !pip install sqlalchemy==1.3.9\n",
    "# !pip install ibm_db_sa\n",
    "# !pip install ipython-sql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Connect to the database\n",
    "\n",
    "Let us first load the SQL extension and establish a connection with the database\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**DB2 magic in case of old UI service credentials.**\n",
    "\n",
    "In the next cell enter your db2 connection string. Recall you created Service Credentials for your Db2 instance before. From the **uri** field of your Db2 service credentials copy everything after db2:// (except the double quote at the end) and paste it in the cell below after ibm_db_sa://\n",
    "\n",
    "<img src =\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/FinalModule_edX/images/URI.jpg\">\n",
    "\n",
    "in the following format\n",
    "\n",
    "**%sql ibm_db_sa://my-username:my-password\\@my-hostname:my-port/my-db-name**\n",
    "\n",
    "**DB2 magic in case of new UI service credentials.**\n",
    "\n",
    "<img src =\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/module_2/images/servicecredentials.png\" width=600>  \n",
    "\n",
    "*   Use the following format.\n",
    "\n",
    "*   Add security=SSL at the end\n",
    "\n",
    "**%sql ibm_db_sa://my-username:my-password\\@my-hostname:my-port/my-db-name?security=SSL**\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "ibm_db_sa_u = os.getenv('ibm_db_sa_u')\n",
    "ibm_db_sa_pw = os.getenv('ibm_db_sa_pw')\n",
    "ibm_db_sa_db = os.getenv('ibm_db_sa_db')\n",
    "conn_text = f'ibm_db_sa://{ibm_db_sa_u}:{ibm_db_sa_pw}@815fa4db-dc03-4c70-869a-a9cc13f33084.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:30367/{ibm_db_sa_db}?security=SSL'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql conn_text"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Tasks\n",
    "\n",
    "Now write and execute SQL queries to solve the assignment tasks.\n",
    "\n",
    "### Task 1\n",
    "\n",
    "##### Display the names of the unique launch sites  in the space mission\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://cpr13394:***@815fa4db-dc03-4c70-869a-a9cc13f33084.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:30367/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>launch_site</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CCAFS LC-40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CCAFS SLC-40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>KSC LC-39A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>VAFB SLC-4E</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('CCAFS LC-40',), ('CCAFS SLC-40',), ('KSC LC-39A',), ('VAFB SLC-4E',)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT UNIQUE launch_site FROM SPACEXDATASET;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 2\n",
    "\n",
    "##### Display 5 records where launch sites begin with the string 'KSC'\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://cpr13394:***@815fa4db-dc03-4c70-869a-a9cc13f33084.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:30367/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>DATE</th>\n",
       "        <th>Time (UTC)</th>\n",
       "        <th>booster_version</th>\n",
       "        <th>launch_site</th>\n",
       "        <th>payload</th>\n",
       "        <th>payload_mass__kg_</th>\n",
       "        <th>orbit</th>\n",
       "        <th>customer</th>\n",
       "        <th>mission_outcome</th>\n",
       "        <th>Landing _Outcome</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-02-19</td>\n",
       "        <td>14:39:00</td>\n",
       "        <td>F9 FT B1031.1</td>\n",
       "        <td>KSC LC-39A</td>\n",
       "        <td>SpaceX CRS-10</td>\n",
       "        <td>2490</td>\n",
       "        <td>LEO (ISS)</td>\n",
       "        <td>NASA (CRS)</td>\n",
       "        <td>Success</td>\n",
       "        <td>Success (ground pad)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-03-16</td>\n",
       "        <td>06:00:00</td>\n",
       "        <td>F9 FT B1030</td>\n",
       "        <td>KSC LC-39A</td>\n",
       "        <td>EchoStar 23</td>\n",
       "        <td>5600</td>\n",
       "        <td>GTO</td>\n",
       "        <td>EchoStar</td>\n",
       "        <td>Success</td>\n",
       "        <td>No attempt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-03-30</td>\n",
       "        <td>22:27:00</td>\n",
       "        <td>F9 FT  B1021.2</td>\n",
       "        <td>KSC LC-39A</td>\n",
       "        <td>SES-10</td>\n",
       "        <td>5300</td>\n",
       "        <td>GTO</td>\n",
       "        <td>SES</td>\n",
       "        <td>Success</td>\n",
       "        <td>Success (drone ship)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-05-01</td>\n",
       "        <td>11:15:00</td>\n",
       "        <td>F9 FT B1032.1</td>\n",
       "        <td>KSC LC-39A</td>\n",
       "        <td>NROL-76</td>\n",
       "        <td>5300</td>\n",
       "        <td>LEO</td>\n",
       "        <td>NRO</td>\n",
       "        <td>Success</td>\n",
       "        <td>Success (ground pad)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-05-15</td>\n",
       "        <td>23:21:00</td>\n",
       "        <td>F9 FT B1034</td>\n",
       "        <td>KSC LC-39A</td>\n",
       "        <td>Inmarsat-5 F4</td>\n",
       "        <td>6070</td>\n",
       "        <td>GTO</td>\n",
       "        <td>Inmarsat</td>\n",
       "        <td>Success</td>\n",
       "        <td>No attempt</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.date(2017, 2, 19), datetime.time(14, 39), 'F9 FT B1031.1', 'KSC LC-39A', 'SpaceX CRS-10', 2490, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'Success (ground pad)'),\n",
       " (datetime.date(2017, 3, 16), datetime.time(6, 0), 'F9 FT B1030', 'KSC LC-39A', 'EchoStar 23', 5600, 'GTO', 'EchoStar', 'Success', 'No attempt'),\n",
       " (datetime.date(2017, 3, 30), datetime.time(22, 27), 'F9 FT  B1021.2', 'KSC LC-39A', 'SES-10', 5300, 'GTO', 'SES', 'Success', 'Success (drone ship)'),\n",
       " (datetime.date(2017, 5, 1), datetime.time(11, 15), 'F9 FT B1032.1', 'KSC LC-39A', 'NROL-76', 5300, 'LEO', 'NRO', 'Success', 'Success (ground pad)'),\n",
       " (datetime.date(2017, 5, 15), datetime.time(23, 21), 'F9 FT B1034', 'KSC LC-39A', 'Inmarsat-5 F4', 6070, 'GTO', 'Inmarsat', 'Success', 'No attempt')]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT *\n",
    "FROM SPACEXDATASET\n",
    "WHERE launch_site \n",
    "LIKE 'KSC%' LIMIT 5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 3\n",
    "\n",
    "##### Display the total payload mass carried by boosters launched by NASA (CRS)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://cpr13394:***@815fa4db-dc03-4c70-869a-a9cc13f33084.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:30367/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>1</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>45596</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(45596,)]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT SUM (payload_mass__kg_) \n",
    "FROM SPACEXDATASET\n",
    "WHERE customer='NASA (CRS)'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://cpr13394:***@815fa4db-dc03-4c70-869a-a9cc13f33084.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:30367/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>DATE</th>\n",
       "        <th>Time (UTC)</th>\n",
       "        <th>booster_version</th>\n",
       "        <th>launch_site</th>\n",
       "        <th>payload</th>\n",
       "        <th>payload_mass__kg_</th>\n",
       "        <th>orbit</th>\n",
       "        <th>customer</th>\n",
       "        <th>mission_outcome</th>\n",
       "        <th>Landing _Outcome</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>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>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>2012-10-08</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>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",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.date(2010, 6, 4), datetime.time(18, 45), 'F9 v1.0  B0003', 'CCAFS LC-40', 'Dragon Spacecraft Qualification Unit', 0, 'LEO', 'SpaceX', 'Success', 'Failure (parachute)'),\n",
       " (datetime.date(2010, 12, 8), datetime.time(15, 43), '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",
       " (datetime.date(2012, 5, 22), datetime.time(7, 44), 'F9 v1.0  B0005', 'CCAFS LC-40', 'Dragon demo flight C2', 525, 'LEO (ISS)', 'NASA (COTS)', 'Success', 'No attempt'),\n",
       " (datetime.date(2012, 10, 8), datetime.time(0, 35), 'F9 v1.0  B0006', 'CCAFS LC-40', 'SpaceX CRS-1', 500, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'No attempt'),\n",
       " (datetime.date(2013, 3, 1), datetime.time(15, 10), 'F9 v1.0  B0007', 'CCAFS LC-40', 'SpaceX CRS-2', 677, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'No attempt')]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT *\n",
    "FROM SPACEXDATASET\n",
    "LIMIT 5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 4\n",
    "\n",
    "##### Display average payload mass carried by booster version F9 v1.1\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://cpr13394:***@815fa4db-dc03-4c70-869a-a9cc13f33084.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:30367/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>1</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2928</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2928,)]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT AVG (payload_mass__kg_) \n",
    "FROM SPACEXDATASET\n",
    "WHERE booster_version='F9 v1.1'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://cpr13394:***@815fa4db-dc03-4c70-869a-a9cc13f33084.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:30367/bludb\n",
      "(ibm_db_dbi.ProgrammingError) ibm_db_dbi::ProgrammingError: SQLNumResultCols failed: [IBM][CLI Driver][DB2/LINUXX8664] SQL0206N  \"LANDING_OUTCOME\" is not valid in the context where it is used.  SQLSTATE=42703 SQLCODE=-206\n",
      "[SQL: SELECT Landing_Outcome FROM SPACEXDATASET]\n",
      "(Background on this error at: http://sqlalche.me/e/f405)\n"
     ]
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT Landing_Outcome FROM SPACEXDATASET"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 5\n",
    "\n",
    "##### List the date where the first succesful landing outcome in drone ship was acheived.\n",
    "\n",
    "*Hint:Use min function*\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://cpr13394:***@815fa4db-dc03-4c70-869a-a9cc13f33084.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:30367/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>1</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2013-12-03</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.date(2013, 12, 3),)]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT MIN(DATE) \n",
    "FROM SPACEXDATASET\n",
    "WHERE booster_version='F9 v1.1'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 6\n",
    "\n",
    "##### List the names of the boosters which have success in ground pad  and have payload mass greater than 4000 but less than 6000\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://cpr13394:***@815fa4db-dc03-4c70-869a-a9cc13f33084.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:30367/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>booster_version</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>F9 v1.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>F9 v1.1 B1011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>F9 v1.1 B1014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>F9 v1.1 B1016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>F9 FT B1020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>F9 FT B1022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>F9 FT B1026</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>F9 FT B1030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>F9 FT  B1021.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>F9 FT B1032.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>F9 B4 B1040.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>F9 FT  B1031.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>F9 FT  B1032.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>F9 B4  B1040.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>F9 B5 B1046.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>F9 B5 B1047.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>F9 B5 B1046.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>F9 B5B1054</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>F9 B5 B1048.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>F9 B5 B1051.2 </td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>F9 B5B1060.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>F9 B5 B1058.2 </td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>F9 B5B1062.1</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('F9 v1.1',),\n",
       " ('F9 v1.1 B1011',),\n",
       " ('F9 v1.1 B1014',),\n",
       " ('F9 v1.1 B1016',),\n",
       " ('F9 FT B1020',),\n",
       " ('F9 FT B1022',),\n",
       " ('F9 FT B1026',),\n",
       " ('F9 FT B1030',),\n",
       " ('F9 FT  B1021.2',),\n",
       " ('F9 FT B1032.1',),\n",
       " ('F9 B4 B1040.1',),\n",
       " ('F9 FT  B1031.2',),\n",
       " ('F9 FT  B1032.2',),\n",
       " ('F9 B4  B1040.2',),\n",
       " ('F9 B5 B1046.2',),\n",
       " ('F9 B5 B1047.2',),\n",
       " ('F9 B5 B1046.3',),\n",
       " ('F9 B5B1054',),\n",
       " ('F9 B5 B1048.3',),\n",
       " ('F9 B5 B1051.2 ',),\n",
       " ('F9 B5B1060.1',),\n",
       " ('F9 B5 B1058.2 ',),\n",
       " ('F9 B5B1062.1',)]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT booster_version\n",
    "FROM SPACEXDATASET\n",
    "WHERE mission_outcome='Success' AND payload_mass__kg_ BETWEEN 4000 AND 6000"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 7\n",
    "\n",
    "##### List the total number of successful and failure mission outcomes\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://cpr13394:***@815fa4db-dc03-4c70-869a-a9cc13f33084.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:30367/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>mission_outcome</th>\n",
       "        <th>counts</th>\n",
       "    </tr>\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",
       "</table>"
      ],
      "text/plain": [
       "[('Failure (in flight)', 1),\n",
       " ('Success', 99),\n",
       " ('Success (payload status unclear)', 1)]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT mission_outcome, COUNT AS Counts\n",
    "FROM SPACEXDATASET\n",
    "GROUP BY mission_outcome"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 8\n",
    "\n",
    "##### List the   names of the booster_versions which have carried the maximum payload mass. Use a subquery\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://cpr13394:***@815fa4db-dc03-4c70-869a-a9cc13f33084.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:30367/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>x</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>15600</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(15600,)]"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "--SELECT booster_versions\n",
    "--FROM SPACEXDATASET\n",
    "--WHERE payload_mass__kg_ IN\n",
    "SELECT MAX(payload_mass__kg_) AS X\n",
    "FROM SPACEXDATASET\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 9\n",
    "\n",
    "##### List the records which will display the month names, succesful landing_outcomes in ground pad ,booster versions, launch_site for the months in year 2017\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://cpr13394:***@815fa4db-dc03-4c70-869a-a9cc13f33084.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:30367/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>DATE</th>\n",
       "        <th>mission_outcome</th>\n",
       "        <th>booster_version</th>\n",
       "        <th>launch_site</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-01-14</td>\n",
       "        <td>Success</td>\n",
       "        <td>F9 FT B1029.1</td>\n",
       "        <td>VAFB SLC-4E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-02-19</td>\n",
       "        <td>Success</td>\n",
       "        <td>F9 FT B1031.1</td>\n",
       "        <td>KSC LC-39A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-03-16</td>\n",
       "        <td>Success</td>\n",
       "        <td>F9 FT B1030</td>\n",
       "        <td>KSC LC-39A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-03-30</td>\n",
       "        <td>Success</td>\n",
       "        <td>F9 FT  B1021.2</td>\n",
       "        <td>KSC LC-39A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-05-01</td>\n",
       "        <td>Success</td>\n",
       "        <td>F9 FT B1032.1</td>\n",
       "        <td>KSC LC-39A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-05-15</td>\n",
       "        <td>Success</td>\n",
       "        <td>F9 FT B1034</td>\n",
       "        <td>KSC LC-39A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-06-03</td>\n",
       "        <td>Success</td>\n",
       "        <td>F9 FT B1035.1</td>\n",
       "        <td>KSC LC-39A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-06-23</td>\n",
       "        <td>Success</td>\n",
       "        <td>F9 FT  B1029.2</td>\n",
       "        <td>KSC LC-39A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-06-25</td>\n",
       "        <td>Success</td>\n",
       "        <td>F9 FT B1036.1</td>\n",
       "        <td>VAFB SLC-4E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-07-05</td>\n",
       "        <td>Success</td>\n",
       "        <td>F9 FT B1037</td>\n",
       "        <td>KSC LC-39A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-08-14</td>\n",
       "        <td>Success</td>\n",
       "        <td>F9 B4 B1039.1</td>\n",
       "        <td>KSC LC-39A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-08-24</td>\n",
       "        <td>Success</td>\n",
       "        <td>F9 FT B1038.1</td>\n",
       "        <td>VAFB SLC-4E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-09-07</td>\n",
       "        <td>Success</td>\n",
       "        <td>F9 B4 B1040.1</td>\n",
       "        <td>KSC LC-39A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-10-09</td>\n",
       "        <td>Success</td>\n",
       "        <td>F9 B4 B1041.1</td>\n",
       "        <td>VAFB SLC-4E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-10-11</td>\n",
       "        <td>Success</td>\n",
       "        <td>F9 FT  B1031.2</td>\n",
       "        <td>KSC LC-39A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-10-30</td>\n",
       "        <td>Success</td>\n",
       "        <td>F9 B4 B1042.1</td>\n",
       "        <td>KSC LC-39A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-12-15</td>\n",
       "        <td>Success</td>\n",
       "        <td>F9 FT  B1035.2</td>\n",
       "        <td>CCAFS SLC-40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-12-23</td>\n",
       "        <td>Success</td>\n",
       "        <td>F9 FT  B1036.2</td>\n",
       "        <td>VAFB SLC-4E</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.date(2017, 1, 14), 'Success', 'F9 FT B1029.1', 'VAFB SLC-4E'),\n",
       " (datetime.date(2017, 2, 19), 'Success', 'F9 FT B1031.1', 'KSC LC-39A'),\n",
       " (datetime.date(2017, 3, 16), 'Success', 'F9 FT B1030', 'KSC LC-39A'),\n",
       " (datetime.date(2017, 3, 30), 'Success', 'F9 FT  B1021.2', 'KSC LC-39A'),\n",
       " (datetime.date(2017, 5, 1), 'Success', 'F9 FT B1032.1', 'KSC LC-39A'),\n",
       " (datetime.date(2017, 5, 15), 'Success', 'F9 FT B1034', 'KSC LC-39A'),\n",
       " (datetime.date(2017, 6, 3), 'Success', 'F9 FT B1035.1', 'KSC LC-39A'),\n",
       " (datetime.date(2017, 6, 23), 'Success', 'F9 FT  B1029.2', 'KSC LC-39A'),\n",
       " (datetime.date(2017, 6, 25), 'Success', 'F9 FT B1036.1', 'VAFB SLC-4E'),\n",
       " (datetime.date(2017, 7, 5), 'Success', 'F9 FT B1037', 'KSC LC-39A'),\n",
       " (datetime.date(2017, 8, 14), 'Success', 'F9 B4 B1039.1', 'KSC LC-39A'),\n",
       " (datetime.date(2017, 8, 24), 'Success', 'F9 FT B1038.1', 'VAFB SLC-4E'),\n",
       " (datetime.date(2017, 9, 7), 'Success', 'F9 B4 B1040.1', 'KSC LC-39A'),\n",
       " (datetime.date(2017, 10, 9), 'Success', 'F9 B4 B1041.1', 'VAFB SLC-4E'),\n",
       " (datetime.date(2017, 10, 11), 'Success', 'F9 FT  B1031.2', 'KSC LC-39A'),\n",
       " (datetime.date(2017, 10, 30), 'Success', 'F9 B4 B1042.1', 'KSC LC-39A'),\n",
       " (datetime.date(2017, 12, 15), 'Success', 'F9 FT  B1035.2', 'CCAFS SLC-40'),\n",
       " (datetime.date(2017, 12, 23), 'Success', 'F9 FT  B1036.2', 'VAFB SLC-4E')]"
      ]
     },
     "execution_count": 176,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT DATE,mission_outcome,booster_version,launch_site\n",
    "FROM (SELECT *\n",
    "FROM SPACEXDATASET\n",
    "WHERE DATE BETWEEN '2017-01-01' AND '2017-12-31')\n",
    "--DATENAME(month, DATE)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://cpr13394:***@815fa4db-dc03-4c70-869a-a9cc13f33084.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:30367/bludb\n",
      "(ibm_db_dbi.ProgrammingError) ibm_db_dbi::ProgrammingError: SQLNumResultCols failed: [IBM][CLI Driver][DB2/LINUXX8664] SQL0104N  An unexpected token \"END-OF-STATEMENT\" was found following \"year,\\'12:10:30.123\\')\".  Expected tokens may include:  \"<table_expr>\".  SQLSTATE=42601 SQLCODE=-104\n",
      "[SQL: --DECLARE @t time = '12:10:30.123'\n",
      "SELECT DATENAME (year,'12:10:30.123')]\n",
      "(Background on this error at: http://sqlalche.me/e/f405)\n"
     ]
    }
   ],
   "source": [
    "%%sql\n",
    "--DECLARE @t time = '12:10:30.123'\n",
    "SELECT DATENAME (year,'12:10:30.123')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 10\n",
    "\n",
    "##### Rank the  count of  successful landing_outcomes between the date 2010-06-04 and 2017-03-20 in descending order.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * ibm_db_sa://cpr13394:***@815fa4db-dc03-4c70-869a-a9cc13f33084.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:30367/bludb\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>mission_outcome</th>\n",
       "        <th>counts</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Success</td>\n",
       "        <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Failure (in flight)</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Success', 30), ('Failure (in flight)', 1)]"
      ]
     },
     "execution_count": 146,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT * FROM(SELECT mission_outcome, COUNT AS Counts\n",
    "FROM (SELECT mission_outcome FROM SPACEXDATASET WHERE DATE BETWEEN '2010-06-04' AND '2017-03-20')\n",
    "GROUP BY mission_outcome)\n",
    "ORDER BY counts 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/Module%203/LAB-String_Patterns_Sorting_Grouping.md.html?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDS0321ENSkillsNetwork26802033-2021-01-01\">Hands-on Lab : String Patterns, Sorting and Grouping</a>\n",
    "\n",
    "*   <a  href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Labs_Coursera_V5/labs/Lab%20-%20Built-in%20functions%20/Hands-on_Lab__Built-in_Functions.md.html?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDS0321ENSkillsNetwork26802033-2021-01-01\">Hands-on Lab: Built-in functions</a>\n",
    "\n",
    "*   <a  href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Labs_Coursera_V5/labs/Lab%20-%20Sub-queries%20and%20Nested%20SELECTs%20/instructional-labs.md.html?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDS0321ENSkillsNetwork26802033-2021-01-01\">Hands-on Lab : Sub-queries and Nested SELECT Statements</a>\n",
    "\n",
    "*   <a href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Module%205/DB0201EN-Week3-1-3-SQLmagic.ipynb?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDS0321ENSkillsNetwork26802033-2021-01-01\">Hands-on Tutorial: Accessing Databases with SQL magic</a>\n",
    "\n",
    "*   <a href= \"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Module%205/DB0201EN-Week3-1-4-Analyzing.ipynb?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDS0321ENSkillsNetwork26802033-2021-01-01\">Hands-on Lab: Analyzing a real World Data Set</a>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Author(s)\n",
    "\n",
    "<h4> Lakshmi Holla </h4>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Other Contributors\n",
    "\n",
    "<h4> Rav Ahuja </h4>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Change log\n",
    "\n",
    "| Date       | Version | Changed by    | Change Description        |\n",
    "| ---------- | ------- | ------------- | ------------------------- |\n",
    "| 2021-10-12 | 0.4     | Lakshmi Holla | Changed markdown          |\n",
    "| 2021-08-24 | 0.3     | Lakshmi Holla | Added library update      |\n",
    "| 2021-07-09 | 0.2     | Lakshmi Holla | Changes made in magic sql |\n",
    "| 2021-05-20 | 0.1     | Lakshmi Holla | Created Initial Version   |\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## <h3 align=\"center\"> © IBM Corporation 2021. All rights reserved. <h3/>\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}