Applied Data Science Capston Week#2-SQL Coursera-OK.ipynb

{
    "cells": [
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": [
                "\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",
                "2.  Change the PAYLOAD_MASS\\_\\_KG\\_  datatype  to INTEGER.\n",
                "\n",
                "<img src = \"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/module_2/images/spacexload2.png\">\n"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": [
                "**Changes to be considered when having DB2 instance with the new UI having Go to UI screen**\n",
                "\n",
                "*   Refer to this insruction in this <a href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Labs_Coursera_V5/labs/Lab%20-%20Sign%20up%20for%20IBM%20Cloud%20-%20Create%20Db2%20service%20instance%20-%20Get%20started%20with%20the%20Db2%20console/instructional-labs.md.html?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDS0321ENSkillsNetwork26802033-2021-01-01\">link</a> for viewing  the new  Go to UI screen.\n",
                "\n",
                "*   Later click on **Data link(below SQL)**  in the Go to UI screen  and click on **Load Data** tab.\n",
                "\n",
                "*   Later browse for the downloaded spacex file.\n",
                "\n",
                "<img src=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/module_2/images/browsefile.png\" width=\"800\"/>\n",
                "\n",
                "*   Once done select the schema andload the file.\n",
                "\n",
                " <img src=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/module_2/images/spacexload3.png\" width=\"800\"/>\n"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": 36,
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": [
                        "Requirement already satisfied: sqlalchemy==1.3.9 in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (1.3.9)\n",
                        "Requirement already satisfied: ibm_db_sa in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (0.3.6)\n",
                        "Requirement already satisfied: sqlalchemy>=0.7.3 in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ibm_db_sa) (1.3.9)\n",
                        "Requirement already satisfied: ibm-db>=2.0.0 in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ibm_db_sa) (3.0.4)\n",
                        "Requirement already satisfied: ipython-sql in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (0.4.0)\n",
                        "Requirement already satisfied: sqlalchemy>=0.6.7 in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ipython-sql) (1.3.9)\n",
                        "Requirement already satisfied: ipython>=1.0 in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ipython-sql) (7.22.0)\n",
                        "Requirement already satisfied: prettytable<1 in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ipython-sql) (0.7.2)\n",
                        "Requirement already satisfied: ipython-genutils>=0.1.0 in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ipython-sql) (0.2.0)\n",
                        "Requirement already satisfied: six in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ipython-sql) (1.15.0)\n",
                        "Requirement already satisfied: sqlparse in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ipython-sql) (0.4.1)\n",
                        "Requirement already satisfied: traitlets>=4.2 in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (5.0.5)\n",
                        "Requirement already satisfied: prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0 in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (3.0.17)\n",
                        "Requirement already satisfied: backcall in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (0.2.0)\n",
                        "Requirement already satisfied: decorator in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (5.0.9)\n",
                        "Requirement already satisfied: pygments in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (2.9.0)\n",
                        "Requirement already satisfied: setuptools>=18.5 in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (52.0.0.post20210125)\n",
                        "Requirement already satisfied: jedi>=0.16 in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (0.17.2)\n",
                        "Requirement already satisfied: pickleshare in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (0.7.5)\n",
                        "Requirement already satisfied: pexpect>4.3 in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (4.8.0)\n",
                        "Requirement already satisfied: parso<0.8.0,>=0.7.0 in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from jedi>=0.16->ipython>=1.0->ipython-sql) (0.7.0)\n",
                        "Requirement already satisfied: ptyprocess>=0.5 in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from pexpect>4.3->ipython>=1.0->ipython-sql) (0.7.0)\n",
                        "Requirement already satisfied: wcwidth in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0->ipython>=1.0->ipython-sql) (0.2.5)\n"
                    ]
                }
            ],
            "source": [
                "!pip install sqlalchemy==1.3.9\n",
                "!pip install ibm_db_sa\n",
                "!pip install ipython-sql"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": [
                "### Connect to the database\n",
                "\n",
                "Let us first load the SQL extension and establish a connection with the database\n"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": 37,
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": [
                        "The sql extension is already loaded. To reload it, use:\n",
                        "  %reload_ext sql\n"
                    ]
                }
            ],
            "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": null,
            "metadata": {},
            "outputs": [],
            "source": []
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {},
            "outputs": [],
            "source": []
        },
        {
            "cell_type": "code",
            "execution_count": 38,
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": [
                        "DB2/LINUXX8664\n"
                    ]
                }
            ],
            "source": [
                "%sql ibm_db_sa://sdk38546:cwn1%40l380qx5qb3k@dashdb-txn-sbox-yp-lon02-07.services.eu-gb.bluemix.net:50000/BLUDB"
            ]
        },
        {
            "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": 39,
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": [
                        " * ibm_db_sa://sdk38546:***@dashdb-txn-sbox-yp-lon02-07.services.eu-gb.bluemix.net:50000/BLUDB\n",
                        "Done.\n"
                    ]
                },
                {
                    "data": {
                        "text/html": [
                            "<table>\n",
                            "    <tr>\n",
                            "        <th>launch_site</th>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "        <td>CCAFS LC-40</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "        <td>CCAFS SLC-40</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "        <td>KSC LC-39A</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "        <td>VAFB SLC-4E</td>\n",
                            "    </tr>\n",
                            "</table>"
                        ],
                        "text/plain": [
                            "[('CCAFS LC-40',), ('CCAFS SLC-40',), ('KSC LC-39A',), ('VAFB SLC-4E',)]"
                        ]
                    },
                    "execution_count": 39,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": [
                "%sql select distinct(LAUNCH_SITE) from SPACEXTBL"
            ]
        },
        {
            "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": 40,
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": [
                        " * ibm_db_sa://sdk38546:***@dashdb-txn-sbox-yp-lon02-07.services.eu-gb.bluemix.net:50000/BLUDB\n",
                        "Done.\n"
                    ]
                },
                {
                    "data": {
                        "text/html": [
                            "<table>\n",
                            "    <tr>\n",
                            "        <th>DATE</th>\n",
                            "        <th>time__utc_</th>\n",
                            "        <th>booster_version</th>\n",
                            "        <th>launch_site</th>\n",
                            "        <th>payload</th>\n",
                            "        <th>payload_mass__kg_</th>\n",
                            "        <th>orbit</th>\n",
                            "        <th>customer</th>\n",
                            "        <th>mission_outcome</th>\n",
                            "        <th>landing__outcome</th>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "        <td>2010-06-04</td>\n",
                            "        <td>18:45:00</td>\n",
                            "        <td>F9 v1.0  B0003</td>\n",
                            "        <td>CCAFS LC-40</td>\n",
                            "        <td>Dragon Spacecraft Qualification Unit</td>\n",
                            "        <td>0</td>\n",
                            "        <td>LEO</td>\n",
                            "        <td>SpaceX</td>\n",
                            "        <td>Success</td>\n",
                            "        <td>Failure (parachute)</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "        <td>2010-12-08</td>\n",
                            "        <td>15:43:00</td>\n",
                            "        <td>F9 v1.0  B0004</td>\n",
                            "        <td>CCAFS LC-40</td>\n",
                            "        <td>Dragon demo flight C1, two CubeSats, barrel of Brouere cheese</td>\n",
                            "        <td>0</td>\n",
                            "        <td>LEO (ISS)</td>\n",
                            "        <td>NASA (COTS) NRO</td>\n",
                            "        <td>Success</td>\n",
                            "        <td>Failure (parachute)</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "        <td>2012-05-22</td>\n",
                            "        <td>07:44:00</td>\n",
                            "        <td>F9 v1.0  B0005</td>\n",
                            "        <td>CCAFS LC-40</td>\n",
                            "        <td>Dragon demo flight C2</td>\n",
                            "        <td>525</td>\n",
                            "        <td>LEO (ISS)</td>\n",
                            "        <td>NASA (COTS)</td>\n",
                            "        <td>Success</td>\n",
                            "        <td>No attempt</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "        <td>2012-10-08</td>\n",
                            "        <td>00:35:00</td>\n",
                            "        <td>F9 v1.0  B0006</td>\n",
                            "        <td>CCAFS LC-40</td>\n",
                            "        <td>SpaceX CRS-1</td>\n",
                            "        <td>500</td>\n",
                            "        <td>LEO (ISS)</td>\n",
                            "        <td>NASA (CRS)</td>\n",
                            "        <td>Success</td>\n",
                            "        <td>No attempt</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "        <td>2013-03-01</td>\n",
                            "        <td>15:10:00</td>\n",
                            "        <td>F9 v1.0  B0007</td>\n",
                            "        <td>CCAFS LC-40</td>\n",
                            "        <td>SpaceX CRS-2</td>\n",
                            "        <td>677</td>\n",
                            "        <td>LEO (ISS)</td>\n",
                            "        <td>NASA (CRS)</td>\n",
                            "        <td>Success</td>\n",
                            "        <td>No attempt</td>\n",
                            "    </tr>\n",
                            "</table>"
                        ],
                        "text/plain": [
                            "[(datetime.date(2010, 6, 4), datetime.time(18, 45), 'F9 v1.0  B0003', 'CCAFS LC-40', 'Dragon Spacecraft Qualification Unit', 0, 'LEO', 'SpaceX', 'Success', 'Failure (parachute)'),\n",
                            " (datetime.date(2010, 12, 8), datetime.time(15, 43), 'F9 v1.0  B0004', 'CCAFS LC-40', 'Dragon demo flight C1, two CubeSats, barrel of Brouere cheese', 0, 'LEO (ISS)', 'NASA (COTS) NRO', 'Success', 'Failure (parachute)'),\n",
                            " (datetime.date(2012, 5, 22), datetime.time(7, 44), 'F9 v1.0  B0005', 'CCAFS LC-40', 'Dragon demo flight C2', 525, 'LEO (ISS)', 'NASA (COTS)', 'Success', 'No attempt'),\n",
                            " (datetime.date(2012, 10, 8), datetime.time(0, 35), 'F9 v1.0  B0006', 'CCAFS LC-40', 'SpaceX CRS-1', 500, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'No attempt'),\n",
                            " (datetime.date(2013, 3, 1), datetime.time(15, 10), 'F9 v1.0  B0007', 'CCAFS LC-40', 'SpaceX CRS-2', 677, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'No attempt')]"
                        ]
                    },
                    "execution_count": 40,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": [
                "%sql select * from SPACEXTBL where LAUNCH_SITE like 'CCA%' limit 5"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": [
                "### Task 3\n",
                "\n",
                "##### Display the total payload mass carried by boosters launched by NASA (CRS)\n"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": 41,
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": [
                        " * ibm_db_sa://sdk38546:***@dashdb-txn-sbox-yp-lon02-07.services.eu-gb.bluemix.net:50000/BLUDB\n",
                        "Done.\n"
                    ]
                },
                {
                    "data": {
                        "text/html": [
                            "<table>\n",
                            "    <tr>\n",
                            "        <th>1</th>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "        <td>45596</td>\n",
                            "    </tr>\n",
                            "</table>"
                        ],
                        "text/plain": [
                            "[(45596,)]"
                        ]
                    },
                    "execution_count": 41,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": [
                "%sql select sum(PAYLOAD_MASS__KG_) from SPACEXTBL where CUSTOMER = 'NASA (CRS)'"
            ]
        },
        {
            "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": 42,
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": [
                        " * ibm_db_sa://sdk38546:***@dashdb-txn-sbox-yp-lon02-07.services.eu-gb.bluemix.net:50000/BLUDB\n",
                        "Done.\n"
                    ]
                },
                {
                    "data": {
                        "text/html": [
                            "<table>\n",
                            "    <tr>\n",
                            "        <th>1</th>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "        <td>2928.400000</td>\n",
                            "    </tr>\n",
                            "</table>"
                        ],
                        "text/plain": [
                            "[(Decimal('2928.400000'),)]"
                        ]
                    },
                    "execution_count": 42,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": [
                "%sql select avg(PAYLOAD_MASS__KG_) from SPACEXTBL where BOOSTER_VERSION = 'F9 v1.1'"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": [
                "### Task 5\n",
                "\n",
                "##### List the date when the first succesful landing outcome in ground pad was acheived.\n",
                "\n",
                "*Hint:Use min function*\n"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": 43,
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": [
                        " * ibm_db_sa://sdk38546:***@dashdb-txn-sbox-yp-lon02-07.services.eu-gb.bluemix.net:50000/BLUDB\n",
                        "Done.\n"
                    ]
                },
                {
                    "data": {
                        "text/html": [
                            "<table>\n",
                            "    <tr>\n",
                            "        <th>1</th>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "        <td>2015-12-22</td>\n",
                            "    </tr>\n",
                            "</table>"
                        ],
                        "text/plain": [
                            "[(datetime.date(2015, 12, 22),)]"
                        ]
                    },
                    "execution_count": 43,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": [
                "%sql select min(DATE) from SPACEXTBL where Landing__Outcome = 'Success (ground pad)'"
            ]
        },
        {
            "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": 44,
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": [
                        " * ibm_db_sa://sdk38546:***@dashdb-txn-sbox-yp-lon02-07.services.eu-gb.bluemix.net:50000/BLUDB\n",
                        "Done.\n"
                    ]
                },
                {
                    "data": {
                        "text/html": [
                            "<table>\n",
                            "    <tr>\n",
                            "        <th>booster_version</th>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "        <td>F9 FT B1022</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "        <td>F9 FT B1026</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "        <td>F9 FT  B1021.2</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "        <td>F9 FT  B1031.2</td>\n",
                            "    </tr>\n",
                            "</table>"
                        ],
                        "text/plain": [
                            "[('F9 FT B1022',), ('F9 FT B1026',), ('F9 FT  B1021.2',), ('F9 FT  B1031.2',)]"
                        ]
                    },
                    "execution_count": 44,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": [
                "%sql select BOOSTER_VERSION from SPACEXTBL where Landing__Outcome = 'Success (drone ship)' and PAYLOAD_MASS__KG_ > 4000 and PAYLOAD_MASS__KG_ < 6000"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": [
                "### Task 7\n",
                "\n",
                "##### List the total number of successful and failure mission outcomes\n"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": 45,
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": [
                        " * ibm_db_sa://sdk38546:***@dashdb-txn-sbox-yp-lon02-07.services.eu-gb.bluemix.net:50000/BLUDB\n",
                        "Done.\n"
                    ]
                },
                {
                    "data": {
                        "text/html": [
                            "<table>\n",
                            "    <tr>\n",
                            "        <th>1</th>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "        <td>100</td>\n",
                            "    </tr>\n",
                            "</table>"
                        ],
                        "text/plain": [
                            "[(Decimal('100'),)]"
                        ]
                    },
                    "execution_count": 45,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": [
                "%sql select count(MISSION_OUTCOME) from SPACEXTBL where MISSION_OUTCOME = 'Success' or MISSION_OUTCOME = 'Failure (in flight)'"
            ]
        },
        {
            "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": 46,
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": [
                        " * ibm_db_sa://sdk38546:***@dashdb-txn-sbox-yp-lon02-07.services.eu-gb.bluemix.net:50000/BLUDB\n",
                        "Done.\n"
                    ]
                },
                {
                    "data": {
                        "text/html": [
                            "<table>\n",
                            "    <tr>\n",
                            "        <th>booster_version</th>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "        <td>F9 B5 B1048.4</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "        <td>F9 B5 B1049.4</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "        <td>F9 B5 B1051.3</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "        <td>F9 B5 B1056.4</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "        <td>F9 B5 B1048.5</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "        <td>F9 B5 B1051.4</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "        <td>F9 B5 B1049.5</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "        <td>F9 B5 B1060.2</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "        <td>F9 B5 B1058.3</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "        <td>F9 B5 B1051.6</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "        <td>F9 B5 B1060.3</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "        <td>F9 B5 B1049.7</td>\n",
                            "    </tr>\n",
                            "</table>"
                        ],
                        "text/plain": [
                            "[('F9 B5 B1048.4',),\n",
                            " ('F9 B5 B1049.4',),\n",
                            " ('F9 B5 B1051.3',),\n",
                            " ('F9 B5 B1056.4',),\n",
                            " ('F9 B5 B1048.5',),\n",
                            " ('F9 B5 B1051.4',),\n",
                            " ('F9 B5 B1049.5',),\n",
                            " ('F9 B5 B1060.2',),\n",
                            " ('F9 B5 B1058.3',),\n",
                            " ('F9 B5 B1051.6',),\n",
                            " ('F9 B5 B1060.3',),\n",
                            " ('F9 B5 B1049.7',)]"
                        ]
                    },
                    "execution_count": 46,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": [
                "%sql select BOOSTER_VERSION from SPACEXTBL where PAYLOAD_MASS__KG_ = (select max(PAYLOAD_MASS__KG_) from SPACEXTBL)"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": [
                "### Task 9\n",
                "\n",
                "##### List the records which will display the month names, failure landing_outcomes in drone ship ,booster versions, launch_site for the months in year 2015\n"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": 47,
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": [
                        " * ibm_db_sa://sdk38546:***@dashdb-txn-sbox-yp-lon02-07.services.eu-gb.bluemix.net:50000/BLUDB\n",
                        "(ibm_db_dbi.ProgrammingError) ibm_db_dbi::ProgrammingError: SQLNumResultCols failed: [IBM][CLI Driver][DB2/LINUXX8664] SQL0104N  An unexpected token \"EXTRACT(MONTH, select\" was found following \"SELECT \".  Expected tokens may include:  \"<space>\".  SQLSTATE=42601 SQLCODE=-104\n",
                        "[SQL: SELECT EXTRACT(MONTH, select min(DATE) from SPACEXTBL where Landing__Outcome = 'Success (ground pad)' )]\n",
                        "(Background on this error at: http://sqlalche.me/e/f405)\n"
                    ]
                }
            ],
            "source": [
                "%sql SELECT EXTRACT(MONTH, select min(DATE) from SPACEXTBL where Landing__Outcome = 'Success (ground pad)')"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": [
                "### Task 10\n",
                "\n",
                "##### Rank the  count of  successful landing_outcomes between the date 2010-06-04 and 2017-03-20 in descending order.\n"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": 48,
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": [
                        " * ibm_db_sa://sdk38546:***@dashdb-txn-sbox-yp-lon02-07.services.eu-gb.bluemix.net:50000/BLUDB\n",
                        "Done.\n"
                    ]
                },
                {
                    "data": {
                        "text/html": [
                            "<table>\n",
                            "    <tr>\n",
                            "        <th>DATE</th>\n",
                            "        <th>time__utc_</th>\n",
                            "        <th>booster_version</th>\n",
                            "        <th>launch_site</th>\n",
                            "        <th>payload</th>\n",
                            "        <th>payload_mass__kg_</th>\n",
                            "        <th>orbit</th>\n",
                            "        <th>customer</th>\n",
                            "        <th>mission_outcome</th>\n",
                            "        <th>landing__outcome</th>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "        <td>2017-02-19</td>\n",
                            "        <td>14:39:00</td>\n",
                            "        <td>F9 FT B1031.1</td>\n",
                            "        <td>KSC LC-39A</td>\n",
                            "        <td>SpaceX CRS-10</td>\n",
                            "        <td>2490</td>\n",
                            "        <td>LEO (ISS)</td>\n",
                            "        <td>NASA (CRS)</td>\n",
                            "        <td>Success</td>\n",
                            "        <td>Success (ground pad)</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "        <td>2017-01-14</td>\n",
                            "        <td>17:54:00</td>\n",
                            "        <td>F9 FT B1029.1</td>\n",
                            "        <td>VAFB SLC-4E</td>\n",
                            "        <td>Iridium NEXT 1</td>\n",
                            "        <td>9600</td>\n",
                            "        <td>Polar LEO</td>\n",
                            "        <td>Iridium Communications</td>\n",
                            "        <td>Success</td>\n",
                            "        <td>Success (drone ship)</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "        <td>2016-08-14</td>\n",
                            "        <td>05:26:00</td>\n",
                            "        <td>F9 FT B1026</td>\n",
                            "        <td>CCAFS LC-40</td>\n",
                            "        <td>JCSAT-16</td>\n",
                            "        <td>4600</td>\n",
                            "        <td>GTO</td>\n",
                            "        <td>SKY Perfect JSAT Group</td>\n",
                            "        <td>Success</td>\n",
                            "        <td>Success (drone ship)</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "        <td>2016-07-18</td>\n",
                            "        <td>04:45:00</td>\n",
                            "        <td>F9 FT B1025.1</td>\n",
                            "        <td>CCAFS LC-40</td>\n",
                            "        <td>SpaceX CRS-9</td>\n",
                            "        <td>2257</td>\n",
                            "        <td>LEO (ISS)</td>\n",
                            "        <td>NASA (CRS)</td>\n",
                            "        <td>Success</td>\n",
                            "        <td>Success (ground pad)</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "        <td>2016-05-27</td>\n",
                            "        <td>21:39:00</td>\n",
                            "        <td>F9 FT B1023.1</td>\n",
                            "        <td>CCAFS LC-40</td>\n",
                            "        <td>Thaicom 8</td>\n",
                            "        <td>3100</td>\n",
                            "        <td>GTO</td>\n",
                            "        <td>Thaicom</td>\n",
                            "        <td>Success</td>\n",
                            "        <td>Success (drone ship)</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "        <td>2016-05-06</td>\n",
                            "        <td>05:21:00</td>\n",
                            "        <td>F9 FT B1022</td>\n",
                            "        <td>CCAFS LC-40</td>\n",
                            "        <td>JCSAT-14</td>\n",
                            "        <td>4696</td>\n",
                            "        <td>GTO</td>\n",
                            "        <td>SKY Perfect JSAT Group</td>\n",
                            "        <td>Success</td>\n",
                            "        <td>Success (drone ship)</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "        <td>2016-04-08</td>\n",
                            "        <td>20:43:00</td>\n",
                            "        <td>F9 FT B1021.1</td>\n",
                            "        <td>CCAFS LC-40</td>\n",
                            "        <td>SpaceX CRS-8</td>\n",
                            "        <td>3136</td>\n",
                            "        <td>LEO (ISS)</td>\n",
                            "        <td>NASA (CRS)</td>\n",
                            "        <td>Success</td>\n",
                            "        <td>Success (drone ship)</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "        <td>2015-12-22</td>\n",
                            "        <td>01:29:00</td>\n",
                            "        <td>F9 FT B1019</td>\n",
                            "        <td>CCAFS LC-40</td>\n",
                            "        <td>OG2 Mission 2  11 Orbcomm-OG2 satellites</td>\n",
                            "        <td>2034</td>\n",
                            "        <td>LEO</td>\n",
                            "        <td>Orbcomm</td>\n",
                            "        <td>Success</td>\n",
                            "        <td>Success (ground pad)</td>\n",
                            "    </tr>\n",
                            "</table>"
                        ],
                        "text/plain": [
                            "[(datetime.date(2017, 2, 19), datetime.time(14, 39), 'F9 FT B1031.1', 'KSC LC-39A', 'SpaceX CRS-10', 2490, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'Success (ground pad)'),\n",
                            " (datetime.date(2017, 1, 14), datetime.time(17, 54), 'F9 FT B1029.1', 'VAFB SLC-4E', 'Iridium NEXT 1', 9600, 'Polar LEO', 'Iridium Communications', 'Success', 'Success (drone ship)'),\n",
                            " (datetime.date(2016, 8, 14), datetime.time(5, 26), 'F9 FT B1026', 'CCAFS LC-40', 'JCSAT-16', 4600, 'GTO', 'SKY Perfect JSAT Group', 'Success', 'Success (drone ship)'),\n",
                            " (datetime.date(2016, 7, 18), datetime.time(4, 45), 'F9 FT B1025.1', 'CCAFS LC-40', 'SpaceX CRS-9', 2257, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'Success (ground pad)'),\n",
                            " (datetime.date(2016, 5, 27), datetime.time(21, 39), 'F9 FT B1023.1', 'CCAFS LC-40', 'Thaicom 8', 3100, 'GTO', 'Thaicom', 'Success', 'Success (drone ship)'),\n",
                            " (datetime.date(2016, 5, 6), datetime.time(5, 21), 'F9 FT B1022', 'CCAFS LC-40', 'JCSAT-14', 4696, 'GTO', 'SKY Perfect JSAT Group', 'Success', 'Success (drone ship)'),\n",
                            " (datetime.date(2016, 4, 8), datetime.time(20, 43), 'F9 FT B1021.1', 'CCAFS LC-40', 'SpaceX CRS-8', 3136, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'Success (drone ship)'),\n",
                            " (datetime.date(2015, 12, 22), datetime.time(1, 29), 'F9 FT B1019', 'CCAFS LC-40', 'OG2 Mission 2  11 Orbcomm-OG2 satellites', 2034, 'LEO', 'Orbcomm', 'Success', 'Success (ground pad)')]"
                        ]
                    },
                    "execution_count": 48,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": [
                "%sql select * from SPACEXTBL where Landing__Outcome like 'Success%' and (DATE between '2010-06-04' and '2017-03-20') order by date desc"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": [
                "### Reference Links\n",
                "\n",
                "*   <a href =\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/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-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.8",
            "language": "python",
            "name": "python3"
        },
        "language_info": {
            "codemirror_mode": {
                "name": "ipython",
                "version": 3
            },
            "file_extension": ".py",
            "mimetype": "text/x-python",
            "name": "python",
            "nbconvert_exporter": "python",
            "pygments_lexer": "ipython3",
            "version": "3.8.10"
        }
    },
    "nbformat": 4,
    "nbformat_minor": 4
}