EDA with SQL.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": "markdown",
   "metadata": {},
   "source": [
    "For this exercise, it was difficult to access IBM's Db2 due to high demand. To complete this exercise, we would make use of the postgre SQL."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: psycopg2 in c:\\users\\hotty\\anaconda3\\lib\\site-packages (2.8.6)\n",
      "Project libraries has been successfully installed!\n"
     ]
    }
   ],
   "source": [
    "# install for connection to postgres database (local)\n",
    "!pip install psycopg2\n",
    "\n",
    "import csv\n",
    "import psycopg2\n",
    "import pandas as pd\n",
    "\n",
    "# install for connection to the database using DB2 (cloud)\n",
    "#!pip install sqlalchemy==1.3.9\n",
    "#!pip install ibm_db_sa\n",
    "#!pip install ipython-sql\n",
    "print('Project libraries has been successfully installed!')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Connect to the database using DB2\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"
   ]
  },
  {
   "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": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(ibm_db_dbi.InterfaceError) ibm_db_dbi::InterfaceError: connect expects a not None dsn value\n",
      "(Background on this error at: http://sqlalche.me/e/rvf5)\n",
      "Connection info needed in SQLAlchemy format, example:\n",
      "               postgresql://username:password@hostname/dbname\n",
      "               or an existing connection: dict_keys([])\n"
     ]
    }
   ],
   "source": [
    "%sql ibm_db_sa://"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Connect to the database using PostgreSQL database\n",
    "\n",
    "We tried to establish connection to the cloud database but experienced some difficulty so we decided to use the postgreSQL database. Let us load the SQL extension and establish a connection with the database using the jupyter notebook."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connection to database is successfully\n"
     ]
    }
   ],
   "source": [
    "# create connection to postgreSQL database\n",
    "conn = psycopg2.connect(\n",
    "    host = 'localhost',\n",
    "    database = 'postgres', \n",
    "    user = 'postgres', \n",
    "    password = 'chuksoo',  \n",
    "    port = '5432')\n",
    "print('Connection to database is successfully')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# function to read from database\n",
    "def read(conn, read_):\n",
    "    print('Read')\n",
    "    cursor = conn.cursor()\n",
    "    cursor.execute(read_)\n",
    "    for row in cursor:\n",
    "        print(f'row = {row}')\n",
    "    print()\n",
    "    \n",
    "# function to create in postgre database     \n",
    "def create(conn, create_):\n",
    "    cursor = conn.cursor() # create cursor object\n",
    "    cursor.execute(create_) # execute query\n",
    "    conn.commit() # commit query to database\n",
    "    print('Table have been created successfull!!!')\n",
    "    #read(conn)\n",
    "    \n",
    "# function to insert in postgre database     \n",
    "def insert(conn, insert_):\n",
    "    cursor = conn.cursor()\n",
    "    cursor.execute(insert_)\n",
    "    conn.commit()\n",
    "    print('Records have been successfully inserted!!!')\n",
    "    #read(conn)\n",
    "    \n",
    "# function to update table\n",
    "def update(conn, update_):\n",
    "    print('Update')\n",
    "    cursor = conn.cursor()\n",
    "    cursor.execute(update_)\n",
    "    conn.commit()\n",
    "    #read(conn)\n",
    "    \n",
    "# function to delete in postgre database\n",
    "def delete(conn, delete_):\n",
    "    print('Delete')\n",
    "    cursor = conn.cursor()\n",
    "    cursor.execute(delete_)\n",
    "    conn.commit()\n",
    "    #read(conn)\n",
    "\n",
    "# close the cursor and connection to the server \n",
    "def close():\n",
    "    cursor.close()\n",
    "    conn.close()   \n",
    "    \n",
    "# function to create pandas dataframe\n",
    "def create_pandas_df(sql_query, database=conn):\n",
    "    table = pd.read_sql_query(sql_query, database)\n",
    "    return table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To begin, we have to create the table in the database using the `CREATE` function. Next we insert the **csv** file by loading the `csv` module. Then we would run the `INSERT` query for each row and then commit the data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create table in postgreSQL database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table have been created successfull!!!\n"
     ]
    }
   ],
   "source": [
    "# create table SpaceX\n",
    "create_ = '''\n",
    "            DROP TABLE IF EXISTS SpaceX;\n",
    "            CREATE TABLE SpaceX\n",
    "                (\n",
    "                    Date DATE NULL,\n",
    "                    Time TIME NULL,\n",
    "                    BoosterVersion VARCHAR(50) NULL,\n",
    "                    LaunchSite VARCHAR(50) NULL,\n",
    "                    Payload VARCHAR(100) NULL,\n",
    "                    PayloadMassKG INT NULL,\n",
    "                    Orbit VARCHAR(50) NULL,\n",
    "                    Customer VARCHAR(100) NULL,\n",
    "                    MissionOutcome VARCHAR(50) NULL,\n",
    "                    LandingOutcome VARCHAR(100) NULL\n",
    "                );\n",
    "            '''\n",
    "create(conn, create_)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we already have the postgreSQL database open, we would have seen that the table have been created by now. Next we load the csv file using the csv module."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CSV file inserted into database successfully!!!\n"
     ]
    }
   ],
   "source": [
    "# loading the csv file\n",
    "cursor = conn.cursor()\n",
    "with open('Spacex.csv', 'r') as f:\n",
    "    reader = csv.reader(f)\n",
    "    next(reader) # Skip the header row.\n",
    "    for row in reader:\n",
    "        cursor.execute(\n",
    "        \"INSERT INTO SpaceX VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)\",\n",
    "        row\n",
    "    )\n",
    "conn.commit()\n",
    "print('CSV file inserted into database successfully!!!')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Read\n",
      "row = (datetime.date(2010, 4, 6), datetime.time(18, 45), 'F9 v1.0  B0003', 'CCAFS LC-40', 'Dragon Spacecraft Qualification Unit', 0, 'LEO', 'SpaceX', 'Success', 'Failure (parachute)')\n",
      "row = (datetime.date(2010, 8, 12), 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",
      "row = (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",
      "row = (datetime.date(2012, 8, 10), datetime.time(0, 35), 'F9 v1.0  B0006', 'CCAFS LC-40', 'SpaceX CRS-1', 500, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'No attempt')\n",
      "row = (datetime.date(2013, 1, 3), datetime.time(15, 10), 'F9 v1.0  B0007', 'CCAFS LC-40', 'SpaceX CRS-2', 677, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'No attempt')\n",
      "row = (datetime.date(2013, 9, 23), datetime.time(16, 0), 'F9 v1.1  B1003', 'VAFB SLC-4E', 'CASSIOPE', 500, 'Polar LEO', 'MDA', 'Success', 'Uncontrolled (ocean)')\n",
      "row = (datetime.date(2013, 3, 12), datetime.time(22, 41), 'F9 v1.1', 'CCAFS LC-40', 'SES-8', 3170, 'GTO', 'SES', 'Success', 'No attempt')\n",
      "row = (datetime.date(2014, 6, 1), datetime.time(22, 6), 'F9 v1.1', 'CCAFS LC-40', 'Thaicom 6', 3325, 'GTO', 'Thaicom', 'Success', 'No attempt')\n",
      "row = (datetime.date(2014, 4, 18), datetime.time(19, 25), 'F9 v1.1', 'CCAFS LC-40', 'SpaceX CRS-3', 2296, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'Controlled (ocean)')\n",
      "row = (datetime.date(2014, 7, 14), datetime.time(15, 15), 'F9 v1.1', 'CCAFS LC-40', 'OG2 Mission 1  6 Orbcomm-OG2 satellites', 1316, 'LEO', 'Orbcomm', 'Success', 'Controlled (ocean)')\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# read to see that data has been uploaded in database\n",
    "read_ = '''\n",
    "        SELECT *\n",
    "        FROM   SpaceX\n",
    "        LIMIT 10\n",
    "        '''\n",
    "read(conn, read_)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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</th>\n",
       "      <th>boosterversion</th>\n",
       "      <th>launchsite</th>\n",
       "      <th>payload</th>\n",
       "      <th>payloadmasskg</th>\n",
       "      <th>orbit</th>\n",
       "      <th>customer</th>\n",
       "      <th>missionoutcome</th>\n",
       "      <th>landingoutcome</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2010-04-06</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-08-12</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>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",
       "      <th>3</th>\n",
       "      <td>2012-08-10</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",
       "      <th>4</th>\n",
       "      <td>2013-01-03</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",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2013-09-23</td>\n",
       "      <td>16:00:00</td>\n",
       "      <td>F9 v1.1  B1003</td>\n",
       "      <td>VAFB SLC-4E</td>\n",
       "      <td>CASSIOPE</td>\n",
       "      <td>500</td>\n",
       "      <td>Polar LEO</td>\n",
       "      <td>MDA</td>\n",
       "      <td>Success</td>\n",
       "      <td>Uncontrolled (ocean)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2013-03-12</td>\n",
       "      <td>22:41:00</td>\n",
       "      <td>F9 v1.1</td>\n",
       "      <td>CCAFS LC-40</td>\n",
       "      <td>SES-8</td>\n",
       "      <td>3170</td>\n",
       "      <td>GTO</td>\n",
       "      <td>SES</td>\n",
       "      <td>Success</td>\n",
       "      <td>No attempt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2014-06-01</td>\n",
       "      <td>22:06:00</td>\n",
       "      <td>F9 v1.1</td>\n",
       "      <td>CCAFS LC-40</td>\n",
       "      <td>Thaicom 6</td>\n",
       "      <td>3325</td>\n",
       "      <td>GTO</td>\n",
       "      <td>Thaicom</td>\n",
       "      <td>Success</td>\n",
       "      <td>No attempt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2014-04-18</td>\n",
       "      <td>19:25:00</td>\n",
       "      <td>F9 v1.1</td>\n",
       "      <td>CCAFS LC-40</td>\n",
       "      <td>SpaceX CRS-3</td>\n",
       "      <td>2296</td>\n",
       "      <td>LEO (ISS)</td>\n",
       "      <td>NASA (CRS)</td>\n",
       "      <td>Success</td>\n",
       "      <td>Controlled (ocean)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2014-07-14</td>\n",
       "      <td>15:15:00</td>\n",
       "      <td>F9 v1.1</td>\n",
       "      <td>CCAFS LC-40</td>\n",
       "      <td>OG2 Mission 1  6 Orbcomm-OG2 satellites</td>\n",
       "      <td>1316</td>\n",
       "      <td>LEO</td>\n",
       "      <td>Orbcomm</td>\n",
       "      <td>Success</td>\n",
       "      <td>Controlled (ocean)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date      time  boosterversion   launchsite  \\\n",
       "0  2010-04-06  18:45:00  F9 v1.0  B0003  CCAFS LC-40   \n",
       "1  2010-08-12  15:43:00  F9 v1.0  B0004  CCAFS LC-40   \n",
       "2  2012-05-22  07:44:00  F9 v1.0  B0005  CCAFS LC-40   \n",
       "3  2012-08-10  00:35:00  F9 v1.0  B0006  CCAFS LC-40   \n",
       "4  2013-01-03  15:10:00  F9 v1.0  B0007  CCAFS LC-40   \n",
       "5  2013-09-23  16:00:00  F9 v1.1  B1003  VAFB SLC-4E   \n",
       "6  2013-03-12  22:41:00         F9 v1.1  CCAFS LC-40   \n",
       "7  2014-06-01  22:06:00         F9 v1.1  CCAFS LC-40   \n",
       "8  2014-04-18  19:25:00         F9 v1.1  CCAFS LC-40   \n",
       "9  2014-07-14  15:15:00         F9 v1.1  CCAFS LC-40   \n",
       "\n",
       "                                             payload  payloadmasskg  \\\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",
       "5                                           CASSIOPE            500   \n",
       "6                                              SES-8           3170   \n",
       "7                                          Thaicom 6           3325   \n",
       "8                                       SpaceX CRS-3           2296   \n",
       "9            OG2 Mission 1  6 Orbcomm-OG2 satellites           1316   \n",
       "\n",
       "       orbit         customer missionoutcome        landingoutcome  \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  \n",
       "5  Polar LEO              MDA        Success  Uncontrolled (ocean)  \n",
       "6        GTO              SES        Success            No attempt  \n",
       "7        GTO          Thaicom        Success            No attempt  \n",
       "8  LEO (ISS)       NASA (CRS)        Success    Controlled (ocean)  \n",
       "9        LEO          Orbcomm        Success    Controlled (ocean)  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# output postgre query in pandas dataframe\n",
    "spacex = create_pandas_df(read_, database=conn)\n",
    "spacex.head(10)"
   ]
  },
  {
   "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": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>launchsite</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>KSC LC-39A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CCAFS LC-40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CCAFS SLC-40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>VAFB SLC-4E</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     launchsite\n",
       "0    KSC LC-39A\n",
       "1   CCAFS LC-40\n",
       "2  CCAFS SLC-40\n",
       "3   VAFB SLC-4E"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "task_1 = '''\n",
    "        SELECT DISTINCT LaunchSite \n",
    "        FROM SpaceX\n",
    "'''\n",
    "create_pandas_df(task_1, database=conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 2\n",
    "\n",
    "##### Display 5 records where launch sites begin with the string 'CCA'\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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</th>\n",
       "      <th>boosterversion</th>\n",
       "      <th>launchsite</th>\n",
       "      <th>payload</th>\n",
       "      <th>payloadmasskg</th>\n",
       "      <th>orbit</th>\n",
       "      <th>customer</th>\n",
       "      <th>missionoutcome</th>\n",
       "      <th>landingoutcome</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2010-04-06</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-08-12</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>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",
       "      <th>3</th>\n",
       "      <td>2012-08-10</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",
       "      <th>4</th>\n",
       "      <td>2013-01-03</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>"
      ],
      "text/plain": [
       "         date      time  boosterversion   launchsite  \\\n",
       "0  2010-04-06  18:45:00  F9 v1.0  B0003  CCAFS LC-40   \n",
       "1  2010-08-12  15:43:00  F9 v1.0  B0004  CCAFS LC-40   \n",
       "2  2012-05-22  07:44:00  F9 v1.0  B0005  CCAFS LC-40   \n",
       "3  2012-08-10  00:35:00  F9 v1.0  B0006  CCAFS LC-40   \n",
       "4  2013-01-03  15:10:00  F9 v1.0  B0007  CCAFS LC-40   \n",
       "\n",
       "                                             payload  payloadmasskg  \\\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 missionoutcome       landingoutcome  \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  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "task_2 = '''\n",
    "        SELECT *\n",
    "        FROM SpaceX\n",
    "        WHERE LaunchSite LIKE 'CCA%'\n",
    "        LIMIT 5\n",
    "        '''\n",
    "create_pandas_df(task_2, database=conn)"
   ]
  },
  {
   "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": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>total_payloadmass</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>45596</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   total_payloadmass\n",
       "0              45596"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "task_3 = '''\n",
    "        SELECT SUM(PayloadMassKG) AS Total_PayloadMass\n",
    "        FROM SpaceX\n",
    "        WHERE Customer LIKE 'NASA (CRS)'\n",
    "        '''\n",
    "create_pandas_df(task_3, database=conn)"
   ]
  },
  {
   "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": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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_payloadmass</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2928.4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   avg_payloadmass\n",
       "0           2928.4"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "task_4 = '''\n",
    "        SELECT AVG(PayloadMassKG) AS Avg_PayloadMass\n",
    "        FROM SpaceX\n",
    "        WHERE BoosterVersion = 'F9 v1.1'\n",
    "        '''\n",
    "create_pandas_df(task_4, database=conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 5\n",
    "\n",
    "##### List the date when the first successful landing outcome in ground pad was acheived.\n",
    "\n",
    "*Hint:Use min function*\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>firstsuccessfull_landing_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>"
      ],
      "text/plain": [
       "  firstsuccessfull_landing_date\n",
       "0                    2015-12-22"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "task_5 = '''\n",
    "        SELECT MIN(Date) AS FirstSuccessfull_landing_date\n",
    "        FROM SpaceX\n",
    "        WHERE LandingOutcome LIKE 'Success (ground pad)'\n",
    "        '''\n",
    "create_pandas_df(task_5, database=conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 6\n",
    "\n",
    "##### List the names of the boosters which have success in drone ship and have payload mass greater than 4000 but less than 6000\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>boosterversion</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>"
      ],
      "text/plain": [
       "   boosterversion\n",
       "0     F9 FT B1022\n",
       "1     F9 FT B1026\n",
       "2  F9 FT  B1021.2\n",
       "3  F9 FT  B1031.2"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "task_6 = '''\n",
    "        SELECT BoosterVersion\n",
    "        FROM SpaceX\n",
    "        WHERE LandingOutcome = 'Success (drone ship)'\n",
    "            AND PayloadMassKG > 4000 \n",
    "            AND PayloadMassKG < 6000\n",
    "        '''\n",
    "create_pandas_df(task_6, database=conn)"
   ]
  },
  {
   "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": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The total number of successful mission outcome is:\n"
     ]
    },
    {
     "data": {
      "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>successoutcome</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>"
      ],
      "text/plain": [
       "   successoutcome\n",
       "0             100"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "The total number of failed mission outcome is:\n"
     ]
    },
    {
     "data": {
      "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>failureoutcome</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>"
      ],
      "text/plain": [
       "   failureoutcome\n",
       "0               1"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "task_7a = '''\n",
    "        SELECT COUNT(MissionOutcome) AS SuccessOutcome\n",
    "        FROM SpaceX\n",
    "        WHERE MissionOutcome LIKE 'Success%'\n",
    "        '''\n",
    "\n",
    "task_7b = '''\n",
    "        SELECT COUNT(MissionOutcome) AS FailureOutcome\n",
    "        FROM SpaceX\n",
    "        WHERE MissionOutcome LIKE 'Failure%'\n",
    "        '''\n",
    "print('The total number of successful mission outcome is:')\n",
    "display(create_pandas_df(task_7a, database=conn))\n",
    "print()\n",
    "print('The total number of failed mission outcome is:')\n",
    "create_pandas_df(task_7b, database=conn)"
   ]
  },
  {
   "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": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>boosterversion</th>\n",
       "      <th>payloadmasskg</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>F9 B5 B1048.4</td>\n",
       "      <td>15600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>F9 B5 B1048.5</td>\n",
       "      <td>15600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>F9 B5 B1049.4</td>\n",
       "      <td>15600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>F9 B5 B1049.5</td>\n",
       "      <td>15600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>F9 B5 B1049.7</td>\n",
       "      <td>15600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>F9 B5 B1051.3</td>\n",
       "      <td>15600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>F9 B5 B1051.4</td>\n",
       "      <td>15600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>F9 B5 B1051.6</td>\n",
       "      <td>15600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>F9 B5 B1056.4</td>\n",
       "      <td>15600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>F9 B5 B1058.3</td>\n",
       "      <td>15600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>F9 B5 B1060.2</td>\n",
       "      <td>15600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>F9 B5 B1060.3</td>\n",
       "      <td>15600</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    boosterversion  payloadmasskg\n",
       "0    F9 B5 B1048.4          15600\n",
       "1    F9 B5 B1048.5          15600\n",
       "2    F9 B5 B1049.4          15600\n",
       "3    F9 B5 B1049.5          15600\n",
       "4   F9 B5 B1049.7           15600\n",
       "5    F9 B5 B1051.3          15600\n",
       "6    F9 B5 B1051.4          15600\n",
       "7    F9 B5 B1051.6          15600\n",
       "8    F9 B5 B1056.4          15600\n",
       "9   F9 B5 B1058.3           15600\n",
       "10  F9 B5 B1060.2           15600\n",
       "11   F9 B5 B1060.3          15600"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "task_8 = '''\n",
    "        SELECT BoosterVersion, PayloadMassKG\n",
    "        FROM SpaceX\n",
    "        WHERE PayloadMassKG = (\n",
    "                                SELECT MAX(PayloadMassKG)\n",
    "                                FROM SpaceX\n",
    "                                )\n",
    "        ORDER BY BoosterVersion\n",
    "        '''\n",
    "create_pandas_df(task_8, database=conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 9\n",
    "\n",
    "##### List the failed landing_outcomes in drone ship, their booster versions, and launch site names for in year 2015\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>boosterversion</th>\n",
       "      <th>launchsite</th>\n",
       "      <th>landingoutcome</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>F9 v1.1 B1012</td>\n",
       "      <td>CCAFS LC-40</td>\n",
       "      <td>Failure (drone ship)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>F9 v1.1 B1015</td>\n",
       "      <td>CCAFS LC-40</td>\n",
       "      <td>Failure (drone ship)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  boosterversion   launchsite        landingoutcome\n",
       "0  F9 v1.1 B1012  CCAFS LC-40  Failure (drone ship)\n",
       "1  F9 v1.1 B1015  CCAFS LC-40  Failure (drone ship)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "task_9 = '''\n",
    "        SELECT BoosterVersion, LaunchSite, LandingOutcome\n",
    "        FROM SpaceX\n",
    "        WHERE LandingOutcome LIKE 'Failure (drone ship)'\n",
    "            AND Date BETWEEN '2015-01-01' AND '2015-12-31'\n",
    "        '''\n",
    "create_pandas_df(task_9, database=conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 10\n",
    "\n",
    "##### Rank the count of landing outcomes (such as Failure (drone ship) or Success (ground pad)) between the date 2010-06-04 and 2017-03-20, in descending order\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>landingoutcome</th>\n",
       "      <th>count</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>6</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>5</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>Precluded (drone ship)</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Failure (parachute)</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           landingoutcome  count\n",
       "0              No attempt     10\n",
       "1    Success (drone ship)      6\n",
       "2    Failure (drone ship)      5\n",
       "3    Success (ground pad)      5\n",
       "4      Controlled (ocean)      3\n",
       "5    Uncontrolled (ocean)      2\n",
       "6  Precluded (drone ship)      1\n",
       "7     Failure (parachute)      1"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "task_10 = '''\n",
    "        SELECT LandingOutcome, COUNT(LandingOutcome)\n",
    "        FROM SpaceX\n",
    "        WHERE DATE BETWEEN '2010-06-04' AND '2017-03-20'\n",
    "        GROUP BY LandingOutcome\n",
    "        ORDER BY COUNT(LandingOutcome) DESC\n",
    "        '''\n",
    "create_pandas_df(task_10, database=conn)"
   ]
  },
  {
   "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?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDS0321ENSkillsNetwork26802033-2021-01-01&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?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDS0321ENSkillsNetwork26802033-2021-01-01&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?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDS0321ENSkillsNetwork26802033-2021-01-01&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?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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}