Final_assignment_SQL.ipynb

{
    "cells": [
        {
            "cell_type": "markdown",
            "id": "1a4572a7-337d-4da8-933e-77d85ab02a73",
            "metadata": {},
            "source": "<p style=\"text-align:center\">\n    <a href=\"https://skills.network/?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDS0321ENSkillsNetwork865-2023-01-01\">\n    <img src=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/assets/logos/SN_web_lightmode.png\" width=\"200\" alt=\"Skills Network Logo\"  />\n    </a>\n</p>\n\n<h1 align=center><font size = 5>Assignment: SQL Notebook for Peer Assignment</font></h1>\n\nEstimated time needed: **60** minutes.\n\n## Introduction\nUsing this Python notebook you will:\n\n1.  Understand the Spacex DataSet\n2.  Load the dataset  into the corresponding table in a Db2 database\n3.  Execute SQL queries to answer assignment questions \n"
        },
        {
            "cell_type": "markdown",
            "id": "f0f042ef-27c0-4a64-94f5-b5f6ff99fa29",
            "metadata": {},
            "source": "## Overview of the DataSet\n\nSpaceX has gained worldwide attention for a series of historic milestones. \n\nIt is the only private company ever to return a spacecraft from low-earth orbit, which it first accomplished in December 2010.\nSpaceX 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\nTherefore if we can determine if the first stage will land, we can determine the cost of a launch. \n\nThis information can be used if an alternate company wants to bid against SpaceX for a rocket launch.\n\nThis dataset includes a record for each payload carried during a SpaceX mission into outer space.\n"
        },
        {
            "cell_type": "markdown",
            "id": "9df33158-7234-4d15-83f0-acab745b6b06",
            "metadata": {},
            "source": "### Download the datasets\n\nThis assignment requires you to load the spacex dataset.\n\nIn 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\" target=\"_blank\">Spacex DataSet</a>\n\n"
        },
        {
            "cell_type": "markdown",
            "id": "7e594554-fe5c-4346-a1f3-38fb3e5c6126",
            "metadata": {},
            "source": "**Navigate to the 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\">link</a> for viewing  the   Go to UI screen. \n\n\n* Later click on **Data link(below SQL)**  in the Go to UI screen  and click on **Load Data** tab.  \n\n\n\n* Later browse for the downloaded spacex file.\n\n\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\n* Once done select the schema andload the file.  \n\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 \n"
        },
        {
            "cell_type": "markdown",
            "id": "10e0e2ed-3414-4c28-ad6e-7e21c1863683",
            "metadata": {},
            "source": "\nIf you are facing a problem in uploading the dataset (which is a csv file), you can follow the steps below to upload the .sql file instead of the CSV file:\n\n* Download the file <a href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/datasets/Spacex%20.sql\">Spacex.sql</a>\n\n* Later click on **SQL** in the  **Go to UI Screen**.\n\n* Use the **From file** option to browse for the **SQL** file and upload it.\n\n<img src=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/module_2/images/sqlfile.png\">\n\n* Once you upload the script,you can use the **Run All** option to run all the queries to insert the data.\n\n<img src=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/module_2/images/runall.png\">\n\n    \n"
        },
        {
            "cell_type": "code",
            "execution_count": 1,
            "id": "cfa51ac3-773f-464f-8613-0f40371d1237",
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": "Collecting ibm_db==3.1.0\n  Using cached ibm_db-3.1.0-cp310-cp310-linux_x86_64.whl\nCollecting ibm_db_sa==0.3.3\n  Using cached ibm_db_sa-0.3.3-py3-none-any.whl\nCollecting sqlalchemy>=0.7.3\n  Using cached SQLAlchemy-2.0.15-cp310-cp310-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (2.7 MB)\nCollecting typing-extensions>=4.2.0\n  Using cached typing_extensions-4.6.3-py3-none-any.whl (31 kB)\nCollecting greenlet!=0.4.17\n  Using cached greenlet-2.0.2-cp310-cp310-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (613 kB)\nInstalling collected packages: ibm_db, typing-extensions, greenlet, sqlalchemy, ibm_db_sa\n  Attempting uninstall: ibm_db\n    Found existing installation: ibm-db 3.1.0\n    Uninstalling ibm-db-3.1.0:\n      Successfully uninstalled ibm-db-3.1.0\n  Attempting uninstall: typing-extensions\n    Found existing installation: typing_extensions 4.6.3\n    Uninstalling typing_extensions-4.6.3:\n      Successfully uninstalled typing_extensions-4.6.3\n  Attempting uninstall: greenlet\n    Found existing installation: greenlet 2.0.2\n    Uninstalling greenlet-2.0.2:\n      Successfully uninstalled greenlet-2.0.2\n  Attempting uninstall: sqlalchemy\n    Found existing installation: SQLAlchemy 1.3.24\n    Uninstalling SQLAlchemy-1.3.24:\n      Successfully uninstalled SQLAlchemy-1.3.24\n  Attempting uninstall: ibm_db_sa\n    Found existing installation: ibm-db-sa 0.3.3\n    Uninstalling ibm-db-sa-0.3.3:\n      Successfully uninstalled ibm-db-sa-0.3.3\nSuccessfully installed greenlet-2.0.2 ibm_db-3.1.0 ibm_db_sa-0.3.3 sqlalchemy-2.0.15 typing-extensions-4.6.3\nCollecting sqlalchemy==1.3.24\n  Using cached SQLAlchemy-1.3.24-cp310-cp310-linux_x86_64.whl\nInstalling collected packages: sqlalchemy\n  Attempting uninstall: sqlalchemy\n    Found existing installation: SQLAlchemy 2.0.15\n    Uninstalling SQLAlchemy-2.0.15:\n      Successfully uninstalled SQLAlchemy-2.0.15\nSuccessfully installed sqlalchemy-1.3.24\nFound existing installation: ipython-sql 0.4.1\nUninstalling ipython-sql-0.4.1:\n  Successfully uninstalled ipython-sql-0.4.1\nCollecting ipython-sql==0.4.1\n  Using cached ipython_sql-0.4.1-py3-none-any.whl (21 kB)\nRequirement already satisfied: sqlalchemy>=0.6.7 in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython-sql==0.4.1) (1.3.24)\nRequirement already satisfied: sqlparse in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython-sql==0.4.1) (0.4.4)\nRequirement already satisfied: six in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython-sql==0.4.1) (1.16.0)\nRequirement already satisfied: ipython>=1.0 in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython-sql==0.4.1) (8.4.0)\nRequirement already satisfied: prettytable<1 in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython-sql==0.4.1) (0.7.2)\nRequirement already satisfied: ipython-genutils>=0.1.0 in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython-sql==0.4.1) (0.2.0)\nRequirement already satisfied: jedi>=0.16 in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython>=1.0->ipython-sql==0.4.1) (0.18.1)\nRequirement already satisfied: backcall in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython>=1.0->ipython-sql==0.4.1) (0.2.0)\nRequirement already satisfied: decorator in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython>=1.0->ipython-sql==0.4.1) (5.1.1)\nRequirement already satisfied: setuptools>=18.5 in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython>=1.0->ipython-sql==0.4.1) (65.6.3)\nRequirement already satisfied: stack-data in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython>=1.0->ipython-sql==0.4.1) (0.2.0)\nRequirement already satisfied: pygments>=2.4.0 in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython>=1.0->ipython-sql==0.4.1) (2.11.2)\nRequirement already satisfied: traitlets>=5 in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython>=1.0->ipython-sql==0.4.1) (5.1.1)\nRequirement already satisfied: matplotlib-inline in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython>=1.0->ipython-sql==0.4.1) (0.1.6)\nRequirement already satisfied: pickleshare in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython>=1.0->ipython-sql==0.4.1) (0.7.5)\nRequirement already satisfied: pexpect>4.3 in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython>=1.0->ipython-sql==0.4.1) (4.8.0)\nRequirement already satisfied: prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0 in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython>=1.0->ipython-sql==0.4.1) (3.0.20)\nRequirement already satisfied: parso<0.9.0,>=0.8.0 in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from jedi>=0.16->ipython>=1.0->ipython-sql==0.4.1) (0.8.3)\nRequirement already satisfied: ptyprocess>=0.5 in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from pexpect>4.3->ipython>=1.0->ipython-sql==0.4.1) (0.7.0)\nRequirement already satisfied: wcwidth in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0->ipython>=1.0->ipython-sql==0.4.1) (0.2.5)\nRequirement already satisfied: pure-eval in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from stack-data->ipython>=1.0->ipython-sql==0.4.1) (0.2.2)\nRequirement already satisfied: asttokens in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from stack-data->ipython>=1.0->ipython-sql==0.4.1) (2.0.5)\nRequirement already satisfied: executing in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from stack-data->ipython>=1.0->ipython-sql==0.4.1) (0.8.3)\nInstalling collected packages: ipython-sql\nSuccessfully installed ipython-sql-0.4.1\n"
                }
            ],
            "source": "!pip install --force-reinstall ibm_db==3.1.0 ibm_db_sa==0.3.3\n!pip install sqlalchemy==1.3.24\n!pip uninstall ipython-sql -y\n!pip install ipython-sql==0.4.1"
        },
        {
            "cell_type": "markdown",
            "id": "c3dba6f4-b072-426f-a321-170cc50d9d54",
            "metadata": {},
            "source": "### Connect to the database\n\nLet us first load the SQL extension and establish a connection with the database\n"
        },
        {
            "cell_type": "code",
            "execution_count": 2,
            "id": "f6d1a362-0ff3-489a-89bf-89e07ae36de6",
            "metadata": {},
            "outputs": [],
            "source": "%load_ext sql"
        },
        {
            "cell_type": "markdown",
            "id": "0d0f926b-bc7f-4fa8-bfb7-1df4c6eef9b1",
            "metadata": {},
            "source": "\n\n\n**DB2 magic in case of  UI service credentials.**\n\n\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,
            "id": "c440ae0e-a4d2-4775-8ce4-da4668ea088e",
            "metadata": {},
            "outputs": [],
            "source": "\n"
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "id": "5e21609d-8105-46f0-b6a7-9aa6569b323b",
            "metadata": {},
            "outputs": [],
            "source": ""
        },
        {
            "cell_type": "code",
            "execution_count": 3,
            "id": "88bdd54b-9c54-4045-a2cc-36150de56071",
            "metadata": {},
            "outputs": [],
            "source": "%sql ibm_db_sa://rgl91840:pNqhppCE1n2ukLRm@815fa4db-dc03-4c70-869a-a9cc13f33084.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:30367/bludb?security=SSL"
        },
        {
            "cell_type": "markdown",
            "id": "b0eba71f-c487-4645-a9db-b38e3de859bd",
            "metadata": {},
            "source": "## Tasks\n\nNow write and execute SQL queries to solve the assignment tasks.\n\n### Task 1\n\n\n\n\n##### Display the names of the unique launch sites  in the space mission\n"
        },
        {
            "cell_type": "code",
            "execution_count": 5,
            "id": "f31edd45-b6bb-4b6b-b0e8-7f5abee8c30a",
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": " * ibm_db_sa://rgl91840:***@815fa4db-dc03-4c70-869a-a9cc13f33084.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:30367/bludb\nDone.\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    <tr>\n        <td>None</td>\n    </tr>\n</table>",
                        "text/plain": "[('CCAFS LC-40',),\n ('CCAFS SLC-40',),\n ('KSC LC-39A',),\n ('VAFB SLC-4E',),\n (None,)]"
                    },
                    "execution_count": 5,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": "%%sql\nSELECT DISTINCT Launch_Site FROM MY_DATA1"
        },
        {
            "cell_type": "markdown",
            "id": "3dcd6095-ce1e-47fe-b980-82ec5c94868b",
            "metadata": {},
            "source": "\n### Task 2\n\n\n#####  Display 5 records where launch sites begin with the string 'CCA' \n"
        },
        {
            "cell_type": "code",
            "execution_count": 6,
            "id": "546fb108-e3e5-4cd0-85c4-59d7b1c3de4e",
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": " * ibm_db_sa://rgl91840:***@815fa4db-dc03-4c70-869a-a9cc13f33084.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:30367/bludb\nDone.\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-04-06</td>\n        <td>18:45:00</td>\n        <td>F9 v1.0  B0003</td>\n        <td>CCAFS LC-40</td>\n        <td>Dragon Spacecraft Qualification Unit</td>\n        <td>0</td>\n        <td>LEO</td>\n        <td>SpaceX</td>\n        <td>Success</td>\n        <td>Failure (parachute)</td>\n    </tr>\n    <tr>\n        <td>2010-08-12</td>\n        <td>15:43:00</td>\n        <td>F9 v1.0  B0004</td>\n        <td>CCAFS LC-40</td>\n        <td>Dragon demo flight C1, two CubeSats, barrel of 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>7: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-08-10</td>\n        <td>0: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-01-03</td>\n        <td>15:10:00</td>\n        <td>F9 v1.0  B0007</td>\n        <td>CCAFS LC-40</td>\n        <td>SpaceX CRS-2</td>\n        <td>677</td>\n        <td>LEO (ISS)</td>\n        <td>NASA (CRS)</td>\n        <td>Success</td>\n        <td>No attempt</td>\n    </tr>\n</table>",
                        "text/plain": "[(datetime.date(2010, 4, 6), '18:45:00', 'F9 v1.0  B0003', 'CCAFS LC-40', 'Dragon Spacecraft Qualification Unit', 0, 'LEO', 'SpaceX', 'Success', 'Failure (parachute)'),\n (datetime.date(2010, 8, 12), '15:43:00', 'F9 v1.0  B0004', 'CCAFS LC-40', 'Dragon demo flight C1, two CubeSats, barrel of Brouere cheese', 0, 'LEO (ISS)', 'NASA (COTS) NRO', 'Success', 'Failure (parachute)'),\n (datetime.date(2012, 5, 22), '7:44:00', 'F9 v1.0  B0005', 'CCAFS LC-40', 'Dragon demo flight C2', 525, 'LEO (ISS)', 'NASA (COTS)', 'Success', 'No attempt'),\n (datetime.date(2012, 8, 10), '0:35:00', 'F9 v1.0  B0006', 'CCAFS LC-40', 'SpaceX CRS-1', 500, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'No attempt'),\n (datetime.date(2013, 1, 3), '15:10:00', 'F9 v1.0  B0007', 'CCAFS LC-40', 'SpaceX CRS-2', 677, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'No attempt')]"
                    },
                    "execution_count": 6,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": "%%sql\nSELECT * FROM MY_DATA1 WHERE Launch_Site LIKE '%CCA%' LIMIT 5"
        },
        {
            "cell_type": "markdown",
            "id": "c71c6965-cf7d-4d67-ac53-a02bfd9989d1",
            "metadata": {},
            "source": "### Task 3\n\n\n\n\n##### Display the total payload mass carried by boosters launched by NASA (CRS)\n"
        },
        {
            "cell_type": "code",
            "execution_count": 8,
            "id": "880f11e0-4aa3-46cd-a6c6-7b5f483544f0",
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": " * ibm_db_sa://rgl91840:***@815fa4db-dc03-4c70-869a-a9cc13f33084.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:30367/bludb\nDone.\n"
                },
                {
                    "data": {
                        "text/html": "<table>\n    <tr>\n        <th>sum_payload_mass_kg</th>\n    </tr>\n    <tr>\n        <td>45596</td>\n    </tr>\n</table>",
                        "text/plain": "[(45596,)]"
                    },
                    "execution_count": 8,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": "%%sql\nSELECT SUM(PAYLOAD_MASS__KG_) AS sum_payload_mass_kg FROM MY_DATA1 WHERE Customer = 'NASA (CRS)'"
        },
        {
            "cell_type": "markdown",
            "id": "390dab14-7504-49dd-80d1-845142f118e9",
            "metadata": {},
            "source": "### Task 4\n\n\n\n\n##### Display average payload mass carried by booster version F9 v1.1\n"
        },
        {
            "cell_type": "code",
            "execution_count": 10,
            "id": "f5556b76-1f7b-4cdf-9802-0ae766b032ff",
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": " * ibm_db_sa://rgl91840:***@815fa4db-dc03-4c70-869a-a9cc13f33084.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:30367/bludb\nDone.\n"
                },
                {
                    "data": {
                        "text/html": "<table>\n    <tr>\n        <th>average_f9_pm</th>\n    </tr>\n    <tr>\n        <td>2928</td>\n    </tr>\n</table>",
                        "text/plain": "[(2928,)]"
                    },
                    "execution_count": 10,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": "%%sql\nSELECT AVG(PAYLOAD_MASS__KG_) AS average_f9_pm FROM MY_DATA1 WHERE Booster_Version = 'F9 v1.1'"
        },
        {
            "cell_type": "markdown",
            "id": "2e1a7ec4-bbd5-403b-9e8d-2772313b32a7",
            "metadata": {},
            "source": "### Task 5\n\n##### List the date when the first successful landing outcome in ground pad was acheived.\n\n\n_Hint:Use min function_ \n"
        },
        {
            "cell_type": "code",
            "execution_count": 11,
            "id": "1cc1dbe7-cb50-4b40-8db3-b4d4cdee01a5",
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": " * ibm_db_sa://rgl91840:***@815fa4db-dc03-4c70-869a-a9cc13f33084.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:30367/bludb\nDone.\n"
                },
                {
                    "data": {
                        "text/html": "<table>\n    <tr>\n        <th>first_successful_ground_pad_landing</th>\n    </tr>\n    <tr>\n        <td>2015-12-22</td>\n    </tr>\n</table>",
                        "text/plain": "[(datetime.date(2015, 12, 22),)]"
                    },
                    "execution_count": 11,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": "%%sql\nSELECT min(Date) AS first_successful_ground_pad_landing FROM MY_DATA1 WHERE Mission_Outcome = 'Success' AND Landing_Outcome LIKE '%ground pad%'"
        },
        {
            "cell_type": "markdown",
            "id": "61b1d91a-b208-4beb-80d4-00624dbe46a8",
            "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": 12,
            "id": "521f4752-62e6-402b-ac53-42b8795dc1c4",
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": " * ibm_db_sa://rgl91840:***@815fa4db-dc03-4c70-869a-a9cc13f33084.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:30367/bludb\nDone.\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>2016-06-05</td>\n        <td>5: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-08-14</td>\n        <td>5: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>2017-03-30</td>\n        <td>22:27:00</td>\n        <td>F9 FT  B1021.2</td>\n        <td>KSC LC-39A</td>\n        <td>SES-10</td>\n        <td>5300</td>\n        <td>GTO</td>\n        <td>SES</td>\n        <td>Success</td>\n        <td>Success (drone ship)</td>\n    </tr>\n    <tr>\n        <td>2017-11-10</td>\n        <td>22:53:00</td>\n        <td>F9 FT  B1031.2</td>\n        <td>KSC LC-39A</td>\n        <td>SES-11 / EchoStar 105</td>\n        <td>5200</td>\n        <td>GTO</td>\n        <td>SES EchoStar</td>\n        <td>Success</td>\n        <td>Success (drone ship)</td>\n    </tr>\n</table>",
                        "text/plain": "[(datetime.date(2016, 6, 5), '5:21:00', 'F9 FT B1022', 'CCAFS LC-40', 'JCSAT-14', 4696, 'GTO', 'SKY Perfect JSAT Group', 'Success', 'Success (drone ship)'),\n (datetime.date(2016, 8, 14), '5:26:00', 'F9 FT B1026', 'CCAFS LC-40', 'JCSAT-16', 4600, 'GTO', 'SKY Perfect JSAT Group', 'Success', 'Success (drone ship)'),\n (datetime.date(2017, 3, 30), '22:27:00', 'F9 FT  B1021.2', 'KSC LC-39A', 'SES-10', 5300, 'GTO', 'SES', 'Success', 'Success (drone ship)'),\n (datetime.date(2017, 11, 10), '22:53:00', 'F9 FT  B1031.2', 'KSC LC-39A', 'SES-11 / EchoStar 105', 5200, 'GTO', 'SES EchoStar', 'Success', 'Success (drone ship)')]"
                    },
                    "execution_count": 12,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": "%%sql\nSELECT * FROM MY_DATA1 WHERE Landing_Outcome = 'Success (drone ship)' AND PAYLOAD_MASS__KG_ BETWEEN 4000 AND 6000"
        },
        {
            "cell_type": "markdown",
            "id": "ceccf431-8329-4443-875f-1505a5c2c083",
            "metadata": {},
            "source": "### Task 7\n\n\n\n\n##### List the total number of successful and failure mission outcomes\n"
        },
        {
            "cell_type": "code",
            "execution_count": 14,
            "id": "f0099236-66c8-4369-ae2d-fc5aae522610",
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": " * ibm_db_sa://rgl91840:***@815fa4db-dc03-4c70-869a-a9cc13f33084.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:30367/bludb\nDone.\n"
                },
                {
                    "data": {
                        "text/html": "<table>\n    <tr>\n        <th>mission_outcome</th>\n        <th>mission_outcome_count</th>\n    </tr>\n    <tr>\n        <td>Failure (in flight)</td>\n        <td>1</td>\n    </tr>\n    <tr>\n        <td>Success</td>\n        <td>99</td>\n    </tr>\n    <tr>\n        <td>Success (payload status unclear)</td>\n        <td>1</td>\n    </tr>\n    <tr>\n        <td>None</td>\n        <td>0</td>\n    </tr>\n</table>",
                        "text/plain": "[('Failure (in flight)', 1),\n ('Success', 99),\n ('Success (payload status unclear)', 1),\n (None, 0)]"
                    },
                    "execution_count": 14,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": "%%sql\nSELECT Mission_outcome, COUNT(Mission_outcome) AS mission_outcome_count FROM MY_DATA1 GROUP BY Mission_outcome"
        },
        {
            "cell_type": "markdown",
            "id": "cffcca0b-2fb7-45a9-afd6-a2af9edcddfa",
            "metadata": {},
            "source": "### Task 8\n\n\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": 15,
            "id": "2cb7b593-2192-47e4-99c5-2bc62ca6ebd5",
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": " * ibm_db_sa://rgl91840:***@815fa4db-dc03-4c70-869a-a9cc13f33084.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:30367/bludb\nDone.\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 B1048.5</td>\n    </tr>\n    <tr>\n        <td>F9 B5 B1049.4</td>\n    </tr>\n    <tr>\n        <td>F9 B5 B1049.5</td>\n    </tr>\n    <tr>\n        <td>F9 B5 B1049.7</td>\n    </tr>\n    <tr>\n        <td>F9 B5 B1051.3</td>\n    </tr>\n    <tr>\n        <td>F9 B5 B1051.4</td>\n    </tr>\n    <tr>\n        <td>F9 B5 B1051.6</td>\n    </tr>\n    <tr>\n        <td>F9 B5 B1056.4</td>\n    </tr>\n    <tr>\n        <td>F9 B5 B1058.3</td>\n    </tr>\n    <tr>\n        <td>F9 B5 B1060.2</td>\n    </tr>\n    <tr>\n        <td>F9 B5 B1060.3</td>\n    </tr>\n</table>",
                        "text/plain": "[('F9 B5 B1048.4',),\n ('F9 B5 B1048.5',),\n ('F9 B5 B1049.4',),\n ('F9 B5 B1049.5',),\n ('F9 B5 B1049.7',),\n ('F9 B5 B1051.3',),\n ('F9 B5 B1051.4',),\n ('F9 B5 B1051.6',),\n ('F9 B5 B1056.4',),\n ('F9 B5 B1058.3',),\n ('F9 B5 B1060.2',),\n ('F9 B5 B1060.3',)]"
                    },
                    "execution_count": 15,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": "%%sql\nSELECT DISTINCT Booster_Version FROM MY_DATA1 WHERE PAYLOAD_MASS__KG_ = (SELECT MAX(PAYLOAD_MASS__KG_) FROM MY_DATA1)"
        },
        {
            "cell_type": "markdown",
            "id": "d81d8af0-6542-4c3b-8713-d2c266a36434",
            "metadata": {},
            "source": "### Task 9\n\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": 17,
            "id": "dd00fec0-d4b5-4fb1-8cd7-f95ebf2f1856",
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": " * ibm_db_sa://rgl91840:***@815fa4db-dc03-4c70-869a-a9cc13f33084.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:30367/bludb\nDone.\n"
                },
                {
                    "data": {
                        "text/html": "<table>\n    <tr>\n        <th>MONTH</th>\n        <th>landing_outcome</th>\n        <th>booster_version</th>\n        <th>launch_site</th>\n    </tr>\n</table>",
                        "text/plain": "[]"
                    },
                    "execution_count": 17,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": "%%sql\nSELECT substr(Date, 4, 2) as month, Landing_outcome, Booster_Version, Launch_Site \nFROM MY_DATA1 \nWHERE substr(Date,7,4)='2015' AND Landing_outcome = 'Failure (drone ship)'"
        },
        {
            "cell_type": "markdown",
            "id": "cbfc7853-46d8-4a89-9468-1170274ec555",
            "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": 24,
            "id": "a1aecee7-7189-4189-9c74-c0808ac34cd6",
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": " * ibm_db_sa://rgl91840:***@815fa4db-dc03-4c70-869a-a9cc13f33084.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:30367/bludb\nDone.\n"
                },
                {
                    "data": {
                        "text/html": "<table>\n    <tr>\n        <th>landing_outcome</th>\n        <th>successful_landings</th>\n    </tr>\n    <tr>\n        <td>No attempt</td>\n        <td>10</td>\n    </tr>\n    <tr>\n        <td>Failure (drone ship)</td>\n        <td>5</td>\n    </tr>\n    <tr>\n        <td>Success (drone ship)</td>\n        <td>5</td>\n    </tr>\n    <tr>\n        <td>Success (ground pad)</td>\n        <td>5</td>\n    </tr>\n    <tr>\n        <td>Controlled (ocean)</td>\n        <td>3</td>\n    </tr>\n    <tr>\n        <td>Failure (parachute)</td>\n        <td>2</td>\n    </tr>\n    <tr>\n        <td>Uncontrolled (ocean)</td>\n        <td>2</td>\n    </tr>\n    <tr>\n        <td>Precluded (drone ship)</td>\n        <td>1</td>\n    </tr>\n</table>",
                        "text/plain": "[('No attempt', 10),\n ('Failure (drone ship)', 5),\n ('Success (drone ship)', 5),\n ('Success (ground pad)', 5),\n ('Controlled (ocean)', 3),\n ('Failure (parachute)', 2),\n ('Uncontrolled (ocean)', 2),\n ('Precluded (drone ship)', 1)]"
                    },
                    "execution_count": 24,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": "%%sql\nSELECT landing_outcome, COUNT(landing_outcome) AS successful_landings \nFROM MY_DATA1 \nWHERE Date BETWEEN '2010-04-06' AND '2017-03-20' GROUP BY landing_outcome ORDER BY successful_landings DESC "
        },
        {
            "cell_type": "markdown",
            "id": "07c75a47-4b2b-4dba-a09d-2890d61169ca",
            "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?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?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?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\">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\">Hands-on Lab: Analyzing a real World Data Set</a>\n\n\n"
        },
        {
            "cell_type": "markdown",
            "id": "76a8e6fb-7dfe-4e28-ab8e-c240920ae1d3",
            "metadata": {},
            "source": "## Author(s)\n\n<h4> Lakshmi Holla </h4>\n"
        },
        {
            "cell_type": "markdown",
            "id": "5b99ecdf-6aa7-4cca-b6fc-bbc01e78f553",
            "metadata": {},
            "source": "## Other Contributors\n\n<h4> Rav Ahuja </h4>\n"
        },
        {
            "cell_type": "markdown",
            "id": "b0f48c22-b195-4751-9638-48a6e6948a22",
            "metadata": {},
            "source": "## Change log\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",
            "id": "04d29bb5-339b-4842-b38c-af6de62d5ad5",
            "metadata": {},
            "source": "## <h3 align=\"center\"> \u00a9 IBM Corporation 2021. All rights reserved. <h3/>\n"
        }
    ],
    "metadata": {
        "kernelspec": {
            "display_name": "Python 3.10",
            "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.10.9"
        }
    },
    "nbformat": 4,
    "nbformat_minor": 4
}