GH_jupyter-labs-eda-sql-coursera_v10.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<center>\n",
    "    <img src=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/module_2/%20https:/cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/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",
    "\n",
    "### Stephane Dedieu, May 2022 <br> Rev. June 12th, 2022. \n",
    "\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-2022-01-01\" target=\"_blank\">Spacex DataSet</a>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Store the dataset in database table\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Installed MySQL Server and MySQL Workbench**\n",
    "Spacex Dataset was stored in a Table in MySQL database  'spacex_database'\n",
    "\n",
    "How to import a csv file in a MySQL Table. \n",
    "https://www.youtube.com/watch?v=vzYFZXI43hM\n",
    "\n",
    "Date was changed from European format to YYYY-MM-DD. Compatible with MySQL Table Date format.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style>.container { width:90% !important; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "from IPython.display import display, HTML\n",
    "display(HTML(\"<style>.container { width:90% !important; }</style>\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We import and check initial csv file. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df=pd.read_csv('C:\\Users\\stefo\\Desktop\\Spacex.csv')\n",
    "df=pd.read_csv('C:/Users/stefo/Desktop/Spacex.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Date                 object\n",
       "Time (UTC)           object\n",
       "Booster_Version      object\n",
       "Launch_Site          object\n",
       "Payload              object\n",
       "PAYLOAD_MASS__KG_     int64\n",
       "Orbit                object\n",
       "Customer             object\n",
       "Mission_Outcome      object\n",
       "Landing _Outcome     object\n",
       "dtype: object"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe\n",
    "df.shape\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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 (UTC)</th>\n",
       "      <th>Booster_Version</th>\n",
       "      <th>Launch_Site</th>\n",
       "      <th>Payload</th>\n",
       "      <th>PAYLOAD_MASS__KG_</th>\n",
       "      <th>Orbit</th>\n",
       "      <th>Customer</th>\n",
       "      <th>Mission_Outcome</th>\n",
       "      <th>Landing _Outcome</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>04-06-2010</td>\n",
       "      <td>18:45:00</td>\n",
       "      <td>F9 v1.0  B0003</td>\n",
       "      <td>CCAFS LC-40</td>\n",
       "      <td>Dragon Spacecraft Qualification Unit</td>\n",
       "      <td>0</td>\n",
       "      <td>LEO</td>\n",
       "      <td>SpaceX</td>\n",
       "      <td>Success</td>\n",
       "      <td>Failure (parachute)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>08-12-2010</td>\n",
       "      <td>15:43:00</td>\n",
       "      <td>F9 v1.0  B0004</td>\n",
       "      <td>CCAFS LC-40</td>\n",
       "      <td>Dragon demo flight C1, two CubeSats, barrel of...</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",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date Time (UTC) Booster_Version  Launch_Site  \\\n",
       "0  04-06-2010   18:45:00  F9 v1.0  B0003  CCAFS LC-40   \n",
       "1  08-12-2010   15:43:00  F9 v1.0  B0004  CCAFS LC-40   \n",
       "\n",
       "                                             Payload  PAYLOAD_MASS__KG_  \\\n",
       "0               Dragon Spacecraft Qualification Unit                  0   \n",
       "1  Dragon demo flight C1, two CubeSats, barrel of...                  0   \n",
       "\n",
       "       Orbit         Customer Mission_Outcome     Landing _Outcome  \n",
       "0        LEO           SpaceX         Success  Failure (parachute)  \n",
       "1  LEO (ISS)  NASA (COTS) NRO         Success  Failure (parachute)  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#!pip install sqlalchemy==1.3.9\n",
    "#!pip install ibm_db_sa\n",
    "#-------\n",
    "#!pip install pymysql\n",
    "#!pip install ipython-sql\n",
    "#!pip install mysqlclient\n",
    "#!pip install mysql-connector-python"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "import mysql.connector\n",
    "import pymysql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext 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"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### <span style=\"color:brown\"> Connecting to my local MySQL database. </span>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn=pymysql.connect(host='localhost',\n",
    "                     port=int(3306),\n",
    "                     user='root',\n",
    "                     passwd=\"########\",\n",
    "                     db='spacex_database'  )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " <span style=\"color:brown\">  I experienced issues with %sql requests. \\\n",
    "I decided to go with **\"pd.read_sql_query\"** which works very well with connection 'conn'  </span>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We check Table 'spacex_v5'  in 'spacex_database'. (It is turned into a dataframe.) "
   ]
  },
  {
   "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>id</th>\n",
       "      <th>Date</th>\n",
       "      <th>Time (UTC)</th>\n",
       "      <th>Booster_Version</th>\n",
       "      <th>Launch_Site</th>\n",
       "      <th>Payload</th>\n",
       "      <th>PAYLOAD_MASS__KG_</th>\n",
       "      <th>Orbit</th>\n",
       "      <th>Customer</th>\n",
       "      <th>Mission_Outcome</th>\n",
       "      <th>Landing_Outcome</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2010-04-06</td>\n",
       "      <td>0 days 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>2</td>\n",
       "      <td>2010-08-12</td>\n",
       "      <td>0 days 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>3</td>\n",
       "      <td>2012-05-22</td>\n",
       "      <td>0 days 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>4</td>\n",
       "      <td>2012-10-08</td>\n",
       "      <td>0 days 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>5</td>\n",
       "      <td>2013-03-01</td>\n",
       "      <td>0 days 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": [
       "   id        Date      Time (UTC) Booster_Version  Launch_Site  \\\n",
       "0   1  2010-04-06 0 days 18:45:00  F9 v1.0  B0003  CCAFS LC-40   \n",
       "1   2  2010-08-12 0 days 15:43:00  F9 v1.0  B0004  CCAFS LC-40   \n",
       "2   3  2012-05-22 0 days 07:44:00  F9 v1.0  B0005  CCAFS LC-40   \n",
       "3   4  2012-10-08 0 days 00:35:00  F9 v1.0  B0006  CCAFS LC-40   \n",
       "4   5  2013-03-01 0 days 15:10:00  F9 v1.0  B0007  CCAFS LC-40   \n",
       "\n",
       "                                             Payload  PAYLOAD_MASS__KG_  \\\n",
       "0               Dragon Spacecraft Qualification Unit                  0   \n",
       "1  Dragon demo flight C1, two CubeSats, barrel of...                  0   \n",
       "2                              Dragon demo flight C2                525   \n",
       "3                                       SpaceX CRS-1                500   \n",
       "4                                       SpaceX CRS-2                677   \n",
       "\n",
       "       Orbit         Customer Mission_Outcome      Landing_Outcome  \n",
       "0        LEO           SpaceX         Success  Failure (parachute)  \n",
       "1  LEO (ISS)  NASA (COTS) NRO         Success  Failure (parachute)  \n",
       "2  LEO (ISS)      NASA (COTS)         Success           No attempt  \n",
       "3  LEO (ISS)       NASA (CRS)         Success           No attempt  \n",
       "4  LEO (ISS)       NASA (CRS)         Success           No attempt  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=pd.read_sql_query(\"Select * from spacex_v11 \",conn)    \n",
    "df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "id                             int64\n",
      "Date                          object\n",
      "Time (UTC)           timedelta64[ns]\n",
      "Booster_Version               object\n",
      "Launch_Site                   object\n",
      "Payload                       object\n",
      "PAYLOAD_MASS__KG_              int64\n",
      "Orbit                         object\n",
      "Customer                      object\n",
      "Mission_Outcome               object\n",
      "Landing_Outcome               object\n",
      "dtype: object\n",
      "(101, 11)\n"
     ]
    }
   ],
   "source": [
    "print(df.dtypes)\n",
    "print(df.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<span style=\"color:brown\">  <b> Time format is not that great, but it has no impact on results in coming tasks. </b> </span>\n",
    "\n",
    "<span style=\"color:brown\"> <b> My MySQL environment works fine for testing SQL queries. </b> </span>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n"
   ]
  },
  {
   "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": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    Launch_Site\n",
      "0   CCAFS LC-40\n",
      "1   VAFB SLC-4E\n",
      "2    KSC LC-39A\n",
      "3  CCAFS SLC-40\n"
     ]
    }
   ],
   "source": [
    "df_unique_launchsites=pd.read_sql_query(\"Select distinct Launch_Site  from spacex_v11 \",conn) \n",
    "print(df_unique_launchsites)"
   ]
  },
  {
   "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": 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>id</th>\n",
       "      <th>Date</th>\n",
       "      <th>Time (UTC)</th>\n",
       "      <th>Booster_Version</th>\n",
       "      <th>Launch_Site</th>\n",
       "      <th>Payload</th>\n",
       "      <th>PAYLOAD_MASS__KG_</th>\n",
       "      <th>Orbit</th>\n",
       "      <th>Customer</th>\n",
       "      <th>Mission_Outcome</th>\n",
       "      <th>Landing_Outcome</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2010-04-06</td>\n",
       "      <td>0 days 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>2</td>\n",
       "      <td>2010-08-12</td>\n",
       "      <td>0 days 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>3</td>\n",
       "      <td>2012-05-22</td>\n",
       "      <td>0 days 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>4</td>\n",
       "      <td>2012-10-08</td>\n",
       "      <td>0 days 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>5</td>\n",
       "      <td>2013-03-01</td>\n",
       "      <td>0 days 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": [
       "   id        Date      Time (UTC) Booster_Version  Launch_Site  \\\n",
       "0   1  2010-04-06 0 days 18:45:00  F9 v1.0  B0003  CCAFS LC-40   \n",
       "1   2  2010-08-12 0 days 15:43:00  F9 v1.0  B0004  CCAFS LC-40   \n",
       "2   3  2012-05-22 0 days 07:44:00  F9 v1.0  B0005  CCAFS LC-40   \n",
       "3   4  2012-10-08 0 days 00:35:00  F9 v1.0  B0006  CCAFS LC-40   \n",
       "4   5  2013-03-01 0 days 15:10:00  F9 v1.0  B0007  CCAFS LC-40   \n",
       "\n",
       "                                             Payload  PAYLOAD_MASS__KG_  \\\n",
       "0               Dragon Spacecraft Qualification Unit                  0   \n",
       "1  Dragon demo flight C1, two CubeSats, barrel of...                  0   \n",
       "2                              Dragon demo flight C2                525   \n",
       "3                                       SpaceX CRS-1                500   \n",
       "4                                       SpaceX CRS-2                677   \n",
       "\n",
       "       Orbit         Customer Mission_Outcome      Landing_Outcome  \n",
       "0        LEO           SpaceX         Success  Failure (parachute)  \n",
       "1  LEO (ISS)  NASA (COTS) NRO         Success  Failure (parachute)  \n",
       "2  LEO (ISS)      NASA (COTS)         Success           No attempt  \n",
       "3  LEO (ISS)       NASA (CRS)         Success           No attempt  \n",
       "4  LEO (ISS)       NASA (CRS)         Success           No attempt  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_launchsites_CCA5=pd.read_sql_query(\"Select * from spacex_v11 where Launch_Site Like 'CCA%' Limit 5\",conn) \n",
    "df_launchsites_CCA5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    Launch_Site\n",
      "0   CCAFS LC-40\n",
      "1  CCAFS SLC-40\n"
     ]
    }
   ],
   "source": [
    "df_launchsites_CCA5=pd.read_sql_query(\"Select distinct Launch_Site from spacex_v11 where Launch_Site Like 'CCA%' Limit 5\",conn) \n",
    "print(df_launchsites_CCA5)"
   ]
  },
  {
   "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": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   id        Date      Time (UTC) Booster_Version  Launch_Site       Payload  \\\n",
      "0   4  2012-10-08 0 days 00:35:00  F9 v1.0  B0006  CCAFS LC-40  SpaceX CRS-1   \n",
      "1   5  2013-03-01 0 days 15:10:00  F9 v1.0  B0007  CCAFS LC-40  SpaceX CRS-2   \n",
      "\n",
      "   PAYLOAD_MASS__KG_      Orbit    Customer Mission_Outcome Landing_Outcome  \n",
      "0                500  LEO (ISS)  NASA (CRS)         Success      No attempt  \n",
      "1                677  LEO (ISS)  NASA (CRS)         Success      No attempt  \n",
      "----\n",
      "Total payload mass, customer= NASA (CRS): 45596  kg\n"
     ]
    }
   ],
   "source": [
    "#  For validation purposes... sum in df_NASA_CRS  'PAYLOAD_MASS__KG_'   column \n",
    "df_NASA_CRS=pd.read_sql_query(\"Select * from spacex_v11  where Customer='NASA (CRS)'\",conn) \n",
    "print(df_NASA_CRS.head(2))\n",
    "print('----')\n",
    "print('Total payload mass, customer= NASA (CRS):', df_NASA_CRS['PAYLOAD_MASS__KG_'].sum(),' kg')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   Total payload mass (kg) NASA CRS\n",
      "0                           45596.0\n"
     ]
    }
   ],
   "source": [
    "#  Based on SQL only...\n",
    "sql_nasa_crs_mass= \"\"\" Select sum(PAYLOAD_MASS__KG_) as 'Total payload mass (kg) NASA CRS' \n",
    "                    from spacex_v11 \n",
    "                    where Customer='NASA (CRS)' \"\"\"\n",
    "payload_NASA_CRS=pd.read_sql_query(sql_nasa_crs_mass,conn) \n",
    "print(payload_NASA_CRS)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "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": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   avg mass (kg)\n",
      "0         2928.4\n"
     ]
    }
   ],
   "source": [
    "payload_F9v11=pd.read_sql_query(\"Select avg(PAYLOAD_MASS__KG_) as 'avg mass (kg)' from spacex_v11 where Booster_Version='F9 v1.1'\",conn) \n",
    "print(payload_F9v11)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "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": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    Launch_Site\n",
      "0   CCAFS LC-40\n",
      "1   VAFB SLC-4E\n",
      "2    KSC LC-39A\n",
      "3  CCAFS SLC-40\n"
     ]
    }
   ],
   "source": [
    "df_unique_launchsites=pd.read_sql_query(\"Select distinct Launch_Site  from spacex_v11 \",conn) \n",
    "print(df_unique_launchsites)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           Landing_Outcome\n",
      "0      Failure (parachute)\n",
      "1               No attempt\n",
      "2     Uncontrolled (ocean)\n",
      "3       Controlled (ocean)\n",
      "4     Failure (drone ship)\n",
      "5   Precluded (drone ship)\n",
      "6     Success (ground pad)\n",
      "7     Success (drone ship)\n",
      "8                  Success\n",
      "9                  Failure\n",
      "10             No attempt \n"
     ]
    }
   ],
   "source": [
    "df_unique_Landing_Outcome=pd.read_sql_query(\"Select distinct Landing_Outcome from spacex_v11 \",conn) \n",
    "print(df_unique_Landing_Outcome)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    min(Date)\n",
      "0  2015-12-22\n"
     ]
    }
   ],
   "source": [
    "min_Date_success_landing=pd.read_sql_query(\"select min(Date) from spacex_v11 where Landing_Outcome = 'Success (ground pad)'\",conn) \n",
    "print(min_Date_success_landing)"
   ]
  },
  {
   "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": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "#  sql query \n",
    "q_boost_succ= \"\"\" select  Booster_Version from spacex_v11 where Landing_Outcome = 'Success (drone ship)' \n",
    "                    and PAYLOAD_MASS__KG_ > 4000 \n",
    "                    and PAYLOAD_MASS__KG_ < 6000 \"\"\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  Booster_Version\n",
      "0     F9 FT B1022\n",
      "1     F9 FT B1026\n",
      "2  F9 FT  B1021.2\n",
      "3  F9 FT  B1031.2\n"
     ]
    }
   ],
   "source": [
    "Booster_success_landing=pd.read_sql_query(q_boost_succ,conn)\n",
    "\n",
    "print(Booster_success_landing)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "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": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   Successful_Missions  Failed_Missions\n",
      "0                  100                1\n"
     ]
    }
   ],
   "source": [
    "#  sql query \n",
    "qsf= \"\"\"Select (Select Count(Mission_Outcome) from spacex_v11 where Mission_Outcome like '%Success%')\n",
    "        as Successful_Missions,\n",
    "        (Select Count(Mission_Outcome) from spacex_v11\n",
    "        where Mission_Outcome like '%Failure%') as Failed_Missions \"\"\"\n",
    "\n",
    "\n",
    "success_failure= pd.read_sql_query(qsf,conn) \n",
    "print(success_failure)"
   ]
  },
  {
   "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": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   Booster_Version\n",
      "0   F9 v1.0  B0003\n",
      "1   F9 v1.0  B0004\n",
      "2   F9 v1.0  B0005\n",
      "3   F9 v1.0  B0006\n",
      "4   F9 v1.0  B0007\n",
      "..             ...\n",
      "92    F9 B5B1062.1\n",
      "93   F9 B5B1061.1 \n",
      "94    F9 B5B1063.1\n",
      "95  F9 B5 B1049.7 \n",
      "96  F9 B5 B1058.4 \n",
      "\n",
      "[97 rows x 1 columns]\n"
     ]
    }
   ],
   "source": [
    "# preliminary query on disctinct booster versions  \n",
    "count_booster= pd.read_sql_query(\"Select distinct Booster_Version from spacex_v11\",conn) \n",
    "print(count_booster)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "#  sql query \n",
    "qboost= \"\"\"Select distinct Booster_Version, max(PAYLOAD_MASS__KG_) as max_payload_mass \n",
    "from spacex_v11\n",
    "group by Booster_Version \n",
    "order by max_payload_mass  desc\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>Booster_Version</th>\n",
       "      <th>max_payload_mass</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>F9 B5 B1049.4</td>\n",
       "      <td>15600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>F9 B5 B1060.2</td>\n",
       "      <td>15600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>F9 B5 B1048.4</td>\n",
       "      <td>15600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>F9 B5 B1048.5</td>\n",
       "      <td>15600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>F9 B5 B1056.4</td>\n",
       "      <td>15600</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Booster_Version  max_payload_mass\n",
       "0   F9 B5 B1049.4             15600\n",
       "1  F9 B5 B1060.2              15600\n",
       "2   F9 B5 B1048.4             15600\n",
       "3   F9 B5 B1048.5             15600\n",
       "4   F9 B5 B1056.4             15600"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "boost_max_load= pd.read_sql_query(qboost,conn) \n",
    "boost_max_load.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "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": 27,
   "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>id</th>\n",
       "      <th>Date</th>\n",
       "      <th>Time (UTC)</th>\n",
       "      <th>Booster_Version</th>\n",
       "      <th>Launch_Site</th>\n",
       "      <th>Payload</th>\n",
       "      <th>PAYLOAD_MASS__KG_</th>\n",
       "      <th>Orbit</th>\n",
       "      <th>Customer</th>\n",
       "      <th>Mission_Outcome</th>\n",
       "      <th>Landing_Outcome</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2010-04-06</td>\n",
       "      <td>0 days 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>2</td>\n",
       "      <td>2010-08-12</td>\n",
       "      <td>0 days 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",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id        Date      Time (UTC) Booster_Version  Launch_Site  \\\n",
       "0   1  2010-04-06 0 days 18:45:00  F9 v1.0  B0003  CCAFS LC-40   \n",
       "1   2  2010-08-12 0 days 15:43:00  F9 v1.0  B0004  CCAFS LC-40   \n",
       "\n",
       "                                             Payload  PAYLOAD_MASS__KG_  \\\n",
       "0               Dragon Spacecraft Qualification Unit                  0   \n",
       "1  Dragon demo flight C1, two CubeSats, barrel of...                  0   \n",
       "\n",
       "       Orbit         Customer Mission_Outcome      Landing_Outcome  \n",
       "0        LEO           SpaceX         Success  Failure (parachute)  \n",
       "1  LEO (ISS)  NASA (COTS) NRO         Success  Failure (parachute)  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "#  sql query \n",
    "q_failed_landing= \"\"\" Select Date, Booster_Version, Launch_Site, Landing_Outcome \n",
    "                   from spacex_v11\n",
    "                   where Landing_Outcome = 'Failure (drone ship)'\n",
    "                   and Date like '%2015%'   \"\"\"\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>Booster_Version</th>\n",
       "      <th>Launch_Site</th>\n",
       "      <th>Landing_Outcome</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015-01-10</td>\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>2015-04-14</td>\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": [
       "         Date Booster_Version  Launch_Site       Landing_Outcome\n",
       "0  2015-01-10   F9 v1.1 B1012  CCAFS LC-40  Failure (drone ship)\n",
       "1  2015-04-14   F9 v1.1 B1015  CCAFS LC-40  Failure (drone ship)"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fail_drone= pd.read_sql_query(q_failed_landing,conn) \n",
    "fail_drone.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "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": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "#  sql query \n",
    "q_count_landing= \"\"\" Select Landing_Outcome, count(*) as count_landings  \n",
    "                   from spacex_v11                \n",
    "                   where Date between '2010-06-04' and '2017-03-20'\n",
    "                   group by Landing_Outcome\n",
    "                   order by count_landings desc  \"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>Landing_Outcome</th>\n",
       "      <th>count_landings</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>Failure (drone ship)</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Success (drone ship)</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Controlled (ocean)</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Success (ground pad)</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Uncontrolled (ocean)</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Failure (parachute)</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Precluded (drone ship)</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Landing_Outcome  count_landings\n",
       "0              No attempt              10\n",
       "1    Failure (drone ship)               5\n",
       "2    Success (drone ship)               5\n",
       "3      Controlled (ocean)               3\n",
       "4    Success (ground pad)               3\n",
       "5    Uncontrolled (ocean)               2\n",
       "6     Failure (parachute)               1\n",
       "7  Precluded (drone ship)               1"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "count_landing= pd.read_sql_query(q_count_landing,conn) \n",
    "count_landing.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "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-2022-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-2022-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-2022-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-2022-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-2022-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 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}