jupyter_labs_eda_sql_coursera_sqllite.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "a6db7f9d-eccd-42cc-8e72-a972b93804eb"
      },
      "source": [
        "<p style=\"text-align:center\">\n",
        "    <a href=\"https://skills.network\" target=\"_blank\">\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",
        "\n",
        "Estimated time needed: **60** minutes.\n",
        "\n",
        "## Introduction\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": {
        "id": "21aa4653-ba36-4b60-bf1c-a94affc4f5ec"
      },
      "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",
        "\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": {
        "id": "f284e791-4a2c-4693-9443-1673a930bc58"
      },
      "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\" target=\"_blank\">Spacex DataSet</a>\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "cc379498-30ef-44a6-a3e6-132d1a1cf4d9",
        "outputId": "766d4d2f-7bbb-4ea7-f8e7-e1da3f31a054"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Collecting sqlalchemy==1.3.9\n",
            "  Using cached SQLAlchemy-1.3.9-cp310-cp310-linux_x86_64.whl\n",
            "Installing collected packages: sqlalchemy\n",
            "  Attempting uninstall: sqlalchemy\n",
            "    Found existing installation: SQLAlchemy 2.0.29\n",
            "    Uninstalling SQLAlchemy-2.0.29:\n",
            "      Successfully uninstalled SQLAlchemy-2.0.29\n",
            "\u001b[31mERROR: pip's dependency resolver does not currently take into account all the packages that are installed. This behaviour is the source of the following dependency conflicts.\n",
            "bigframes 1.2.0 requires sqlalchemy<3.0dev,>=1.4, but you have sqlalchemy 1.3.9 which is incompatible.\n",
            "ipython-sql 0.5.0 requires sqlalchemy>=2.0, but you have sqlalchemy 1.3.9 which is incompatible.\u001b[0m\u001b[31m\n",
            "\u001b[0mSuccessfully installed sqlalchemy-1.3.9\n"
          ]
        }
      ],
      "source": [
        "!pip install sqlalchemy==1.3.9\n"
      ],
      "execution_count": 1
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "bee0e21a-df3b-40c9-813e-d04e7dda35af"
      },
      "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",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "3686e957-c78d-4216-95a1-796f945d5b0f",
        "outputId": "ce79f49e-3658-4a30-b905-a587a7ab30a8"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Requirement already satisfied: ipython-sql in /usr/local/lib/python3.10/dist-packages (0.5.0)\n",
            "Requirement already satisfied: prettytable in /usr/local/lib/python3.10/dist-packages (from ipython-sql) (3.10.0)\n",
            "Requirement already satisfied: ipython in /usr/local/lib/python3.10/dist-packages (from ipython-sql) (7.34.0)\n",
            "Collecting sqlalchemy>=2.0 (from ipython-sql)\n",
            "  Using cached SQLAlchemy-2.0.29-cp310-cp310-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (3.1 MB)\n",
            "Requirement already satisfied: sqlparse in /usr/local/lib/python3.10/dist-packages (from ipython-sql) (0.5.0)\n",
            "Requirement already satisfied: six in /usr/local/lib/python3.10/dist-packages (from ipython-sql) (1.16.0)\n",
            "Requirement already satisfied: ipython-genutils in /usr/local/lib/python3.10/dist-packages (from ipython-sql) (0.2.0)\n",
            "Requirement already satisfied: typing-extensions>=4.6.0 in /usr/local/lib/python3.10/dist-packages (from sqlalchemy>=2.0->ipython-sql) (4.11.0)\n",
            "Requirement already satisfied: greenlet!=0.4.17 in /usr/local/lib/python3.10/dist-packages (from sqlalchemy>=2.0->ipython-sql) (3.0.3)\n",
            "Requirement already satisfied: setuptools>=18.5 in /usr/local/lib/python3.10/dist-packages (from ipython->ipython-sql) (67.7.2)\n",
            "Requirement already satisfied: jedi>=0.16 in /usr/local/lib/python3.10/dist-packages (from ipython->ipython-sql) (0.19.1)\n",
            "Requirement already satisfied: decorator in /usr/local/lib/python3.10/dist-packages (from ipython->ipython-sql) (4.4.2)\n",
            "Requirement already satisfied: pickleshare in /usr/local/lib/python3.10/dist-packages (from ipython->ipython-sql) (0.7.5)\n",
            "Requirement already satisfied: traitlets>=4.2 in /usr/local/lib/python3.10/dist-packages (from ipython->ipython-sql) (5.7.1)\n",
            "Requirement already satisfied: prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0 in /usr/local/lib/python3.10/dist-packages (from ipython->ipython-sql) (3.0.43)\n",
            "Requirement already satisfied: pygments in /usr/local/lib/python3.10/dist-packages (from ipython->ipython-sql) (2.16.1)\n",
            "Requirement already satisfied: backcall in /usr/local/lib/python3.10/dist-packages (from ipython->ipython-sql) (0.2.0)\n",
            "Requirement already satisfied: matplotlib-inline in /usr/local/lib/python3.10/dist-packages (from ipython->ipython-sql) (0.1.7)\n",
            "Requirement already satisfied: pexpect>4.3 in /usr/local/lib/python3.10/dist-packages (from ipython->ipython-sql) (4.9.0)\n",
            "Requirement already satisfied: wcwidth in /usr/local/lib/python3.10/dist-packages (from prettytable->ipython-sql) (0.2.13)\n",
            "Requirement already satisfied: parso<0.9.0,>=0.8.3 in /usr/local/lib/python3.10/dist-packages (from jedi>=0.16->ipython->ipython-sql) (0.8.4)\n",
            "Requirement already satisfied: ptyprocess>=0.5 in /usr/local/lib/python3.10/dist-packages (from pexpect>4.3->ipython->ipython-sql) (0.7.0)\n",
            "Installing collected packages: sqlalchemy\n",
            "  Attempting uninstall: sqlalchemy\n",
            "    Found existing installation: SQLAlchemy 1.3.9\n",
            "    Uninstalling SQLAlchemy-1.3.9:\n",
            "      Successfully uninstalled SQLAlchemy-1.3.9\n",
            "Successfully installed sqlalchemy-2.0.29\n"
          ]
        }
      ],
      "source": [
        "#Please uncomment and execute the code below if you are working locally.\n",
        "\n",
        "!pip install ipython-sql"
      ],
      "execution_count": 2
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "c42877fe-9204-4a2b-a68b-2e2bbae2bfab"
      },
      "outputs": [],
      "source": [
        "%load_ext sql"
      ],
      "execution_count": 3
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "1728fe99-bc73-42f4-a763-6d9f0d038bbb"
      },
      "outputs": [],
      "source": [
        "import csv, sqlite3\n",
        "\n",
        "con = sqlite3.connect(\"my_data1.db\")\n",
        "cur = con.cursor()"
      ],
      "execution_count": 4
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ad07a3da-9da6-4ef1-8cb0-e70c9ecddab7"
      },
      "outputs": [],
      "source": [
        "!pip install -q pandas"
      ],
      "execution_count": 5
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "a746f178-8649-4b08-98a9-a605ccee3bb3"
      },
      "outputs": [],
      "source": [
        "%sql sqlite:///my_data1.db"
      ],
      "execution_count": 6
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "c6a274b9-51dc-494b-8954-c6d55479a2db",
        "outputId": "a6f6e76d-5948-45e6-dd5e-abb2d859fb36"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "101"
            ]
          },
          "metadata": {},
          "execution_count": 7
        }
      ],
      "source": [
        "import pandas as pd\n",
        "df = pd.read_csv(\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/module_2/data/Spacex.csv\")\n",
        "df.to_sql(\"SPACEXTBL\", con, if_exists='replace', index=False,method=\"multi\")"
      ],
      "execution_count": 7
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "83bdb47f-f97a-4165-915e-9d7b60b3f44f"
      },
      "source": [
        "**Note:This below code is added to remove blank rows from table**\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "542091fc-021c-41a2-96a2-6222f9429f30",
        "outputId": "17490888-63ec-4e0f-f8d9-3b4c958afa37"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///my_data1.db\n",
            "(sqlite3.OperationalError) table SPACEXTABLE already exists\n",
            "[SQL: create table SPACEXTABLE as select * from SPACEXTBL where Date is not null]\n",
            "(Background on this error at: https://sqlalche.me/e/20/e3q8)\n"
          ]
        }
      ],
      "source": [
        "%sql create table SPACEXTABLE as select * from SPACEXTBL where Date is not null"
      ],
      "execution_count": 8
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "05cbeacb-d9a6-49fa-8411-eb240e5a8757"
      },
      "source": [
        "## Tasks\n",
        "\n",
        "Now write and execute SQL queries to solve the assignment tasks.\n",
        "\n",
        "**Note: If the column names are in mixed case enclose it in double quotes\n",
        "   For Example \"Landing_Outcome\"**\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",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "35c79e8e-8f45-47b2-8bae-17a803fa6e84",
        "outputId": "944c5173-628c-47a1-a5c6-0fd3a28d80d3"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "CCAFS LC-40\n",
            "VAFB SLC-4E\n",
            "KSC LC-39A\n",
            "CCAFS SLC-40\n"
          ]
        }
      ],
      "source": [
        "cur.execute(\"SELECT DISTINCT Launch_Site FROM SPACEXTABLE;\")\n",
        "\n",
        "results = cur.fetchall()\n",
        "\n",
        "for row in results:\n",
        "    print(row[0])\n"
      ],
      "execution_count": 9
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "315e06fc-2a9a-48c8-8755-14b8e4ff1967"
      },
      "source": [
        "\n",
        "### Task 2\n",
        "\n",
        "\n",
        "#####  Display 5 records where launch sites begin with the string 'CCA'\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "49fdec56-1496-4da2-aafd-150872756fe0",
        "outputId": "e951df48-6b33-4f5f-eb1a-e6ba95896c22"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "('2010-06-04', '18:45:00', 'F9 v1.0  B0003', 'CCAFS LC-40', 'Dragon Spacecraft Qualification Unit', 0, 'LEO', 'SpaceX', 'Success', 'Failure (parachute)')\n",
            "('2010-12-08', '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",
            "('2012-05-22', '7:44:00', 'F9 v1.0  B0005', 'CCAFS LC-40', 'Dragon demo flight C2', 525, 'LEO (ISS)', 'NASA (COTS)', 'Success', 'No attempt')\n",
            "('2012-10-08', '0:35:00', 'F9 v1.0  B0006', 'CCAFS LC-40', 'SpaceX CRS-1', 500, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'No attempt')\n",
            "('2013-03-01', '15:10:00', 'F9 v1.0  B0007', 'CCAFS LC-40', 'SpaceX CRS-2', 677, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'No attempt')\n"
          ]
        }
      ],
      "source": [
        "cur.execute(\"SELECT * FROM SPACEXTABLE WHERE Launch_Site LIKE 'CCA%' LIMIT 5;\")\n",
        "\n",
        "results = cur.fetchall()\n",
        "\n",
        "for row in results:\n",
        "    print(row)"
      ],
      "execution_count": 10
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6fa35ae7-0fc6-475c-8f9a-983feedfc3d8"
      },
      "source": [
        "### Task 3\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "##### Display the total payload mass carried by boosters launched by NASA (CRS)\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "089f0adf-50b3-4169-af41-d988e1d6817b",
        "outputId": "53cff497-11c3-46f8-815e-3240b5fd4912"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Total payload mass carried by boosters launched by NASA (CRS): 45596 kg\n"
          ]
        }
      ],
      "source": [
        "cur.execute(\"SELECT SUM(Payload_mass__kg_) FROM SPACEXTABLE WHERE Customer = 'NASA (CRS)';\")\n",
        "\n",
        "total_payload_mass = cur.fetchone()[0]\n",
        "\n",
        "print(\"Total payload mass carried by boosters launched by NASA (CRS):\", total_payload_mass, \"kg\")"
      ],
      "execution_count": 11
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7c89112e-8f84-4cf0-bca2-0853fe5379eb"
      },
      "source": [
        "### Task 4\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "##### Display average payload mass carried by booster version F9 v1.1\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "a17aa67f-1825-4c3c-b0b1-a2052c6fdb4d",
        "outputId": "d1736d41-b95b-41d6-9c8d-42be701a7970"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Average payload mass carried by booster version F9 v1.1: 2928.4 kg\n"
          ]
        }
      ],
      "source": [
        "cur.execute(\"SELECT AVG(Payload_mass__kg_) FROM SPACEXTABLE WHERE Booster_Version = 'F9 v1.1';\")\n",
        "\n",
        "average_payload_mass = cur.fetchone()[0]\n",
        "\n",
        "print(\"Average payload mass carried by booster version F9 v1.1:\", average_payload_mass, \"kg\")"
      ],
      "execution_count": 12
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "d1ae46c1-a801-4109-9d9e-5dbf9e1afdd5"
      },
      "source": [
        "### Task 5\n",
        "\n",
        "##### List the date when the first succesful landing outcome in ground pad was acheived.\n",
        "\n",
        "\n",
        "_Hint:Use min function_\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "6a28c3dd-18f1-40e5-9920-42e3715ef372",
        "outputId": "072085b2-1c00-4389-b492-ce1089aebc12"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Date of the first successful landing outcome in ground pad: 2015-12-22\n"
          ]
        }
      ],
      "source": [
        "cur.execute(\"SELECT MIN(Date) FROM SPACEXTABLE WHERE Landing_Outcome = 'Success (ground pad)';\")\n",
        "\n",
        "first_successful_landing_date = cur.fetchone()[0]\n",
        "\n",
        "print(\"Date of the first successful landing outcome in ground pad:\", first_successful_landing_date)"
      ],
      "execution_count": 13
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "33174cef-51db-42e6-acb7-d590371c1348"
      },
      "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",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "95f0fc6a-b3ca-4bd6-8a05-1e60b17307d7",
        "outputId": "271d7a00-2036-4e65-e2f0-87143360781f"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "F9 FT B1022\n",
            "F9 FT B1026\n",
            "F9 FT  B1021.2\n",
            "F9 FT  B1031.2\n"
          ]
        }
      ],
      "source": [
        "cur.execute(\"SELECT DISTINCT Booster_Version FROM SPACEXTABLE WHERE Landing_Outcome = 'Success (drone ship)' AND Payload_mass__kg_ > 4000 AND Payload_mass__kg_ < 6000;\")\n",
        "\n",
        "boosters = cur.fetchall()\n",
        "\n",
        "for booster in boosters:\n",
        "    print(booster[0])"
      ],
      "execution_count": 14
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2c82937a-d5df-4746-b3ac-1a5ab177137f"
      },
      "source": [
        "### Task 7\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "##### List the total number of successful and failure mission outcomes\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "6c45be1f-2dfb-4dc4-85dc-68da45059dc4",
        "outputId": "1e0d18ad-7e17-4567-b1c3-401efd272974"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Failure (in flight) : 1\n",
            "Success : 98\n",
            "Success  : 1\n",
            "Success (payload status unclear) : 1\n"
          ]
        }
      ],
      "source": [
        "cur.execute(\"SELECT Mission_Outcome, COUNT(*) AS Count FROM SPACEXTABLE GROUP BY Mission_Outcome;\")\n",
        "\n",
        "results = cur.fetchall()\n",
        "\n",
        "for row in results:\n",
        "    print(row[0], \":\", row[1])"
      ],
      "execution_count": 15
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "e0bfa782-af12-4d6f-b00f-07036c81ebc6"
      },
      "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",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "9a534495-bac1-431d-b43a-f5d552f8ec52",
        "outputId": "679ca5a3-48a7-4217-9fd8-ffba80af9ecc"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "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 \n"
          ]
        }
      ],
      "source": [
        "cur.execute(\"\"\"\n",
        "    SELECT Booster_Version\n",
        "    FROM SPACEXTABLE\n",
        "    WHERE Payload_mass__kg_ = (\n",
        "        SELECT MAX(Payload_mass__kg_)\n",
        "        FROM SPACEXTABLE\n",
        "    );\n",
        "\"\"\")\n",
        "\n",
        "results = cur.fetchall()\n",
        "\n",
        "for row in results:\n",
        "    print(row[0])"
      ],
      "execution_count": 16
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "5ba257fa-d385-4807-a65c-605cdd4a524e"
      },
      "source": [
        "### Task 9\n",
        "\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",
        "\n",
        "**Note: SQLLite does not support monthnames. So you need to use  substr(Date, 6,2) as month to get the months and substr(Date,0,5)='2015' for year.**\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "8e0fa5c8-50ca-49e7-aea9-30d7353aa322",
        "outputId": "58cd51fb-ae02-4127-d626-45f7cd0a7483"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Month: 01 Landing Outcome: Failure (drone ship) Booster Version: F9 v1.1 B1012 Launch Site: CCAFS LC-40\n",
            "Month: 04 Landing Outcome: Failure (drone ship) Booster Version: F9 v1.1 B1015 Launch Site: CCAFS LC-40\n"
          ]
        }
      ],
      "source": [
        "cur.execute(\"\"\"\n",
        "    SELECT SUBSTR(Date, 6, 2) AS Month,\n",
        "           Landing_Outcome,\n",
        "           Booster_Version,\n",
        "           Launch_Site\n",
        "    FROM SPACEXTABLE\n",
        "    WHERE SUBSTR(Date, 0, 5) = '2015'\n",
        "      AND Landing_Outcome = 'Failure (drone ship)';\n",
        "\"\"\")\n",
        "\n",
        "results = cur.fetchall()\n",
        "\n",
        "for row in results:\n",
        "    print(\"Month:\", row[0], \"Landing Outcome:\", row[1], \"Booster Version:\", row[2], \"Launch Site:\", row[3])\n"
      ],
      "execution_count": 17
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "930422fd-8aeb-4f63-8679-024dc3026711"
      },
      "source": [
        "### Task 10\n",
        "\n",
        "\n",
        "\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",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "3dac6fb2-9cde-40b5-b30f-4777e56d5a80",
        "outputId": "05da9474-547e-4c1c-d242-a416aab6d249"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Landing Outcome: No attempt Count: 10\n",
            "Landing Outcome: Success (drone ship) Count: 5\n",
            "Landing Outcome: Failure (drone ship) Count: 5\n",
            "Landing Outcome: Success (ground pad) Count: 3\n",
            "Landing Outcome: Controlled (ocean) Count: 3\n",
            "Landing Outcome: Uncontrolled (ocean) Count: 2\n",
            "Landing Outcome: Failure (parachute) Count: 2\n",
            "Landing Outcome: Precluded (drone ship) Count: 1\n"
          ]
        }
      ],
      "source": [
        "cur.execute(\"\"\"\n",
        "    SELECT Landing_Outcome, COUNT(*) AS Outcome_Count\n",
        "    FROM SPACEXTABLE\n",
        "    WHERE Date BETWEEN '2010-06-04' AND '2017-03-20'\n",
        "    GROUP BY Landing_Outcome\n",
        "    ORDER BY Outcome_Count DESC;\n",
        "\"\"\")\n",
        "\n",
        "results = cur.fetchall()\n",
        "\n",
        "for row in results:\n",
        "    print(\"Landing Outcome:\", row[0], \"Count:\", row[1])\n",
        "\n",
        "# Close the connection\n",
        "con.close()\n"
      ],
      "execution_count": 19
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "832351ea-ef0f-44dd-9ca9-b97419f860b2"
      },
      "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",
      "metadata": {
        "id": "21ce78f2-9c70-4227-aa5c-d0eb2185d6f1"
      },
      "source": [
        "## Author(s)\n",
        "\n",
        "<h4> Lakshmi Holla </h4>\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "333e0967-f03e-4977-876d-c049fb802bd6"
      },
      "source": [
        "## Other Contributors\n",
        "\n",
        "<h4> Rav Ahuja </h4>\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "65b5e2ea-3999-4450-bb25-c59b4f362939"
      },
      "source": [
        "## Change log\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": {
        "id": "0f8b00e0-87ea-4e55-8ae7-758836ea9a86"
      },
      "source": [
        "## <h3 align=\"center\"> © IBM Corporation 2021. All rights reserved. <h3/>\n"
      ]
    }
  ],
  "metadata": {
    "kernelspec": {
      "display_name": "Python",
      "language": "python",
      "name": "conda-env-python-py"
    },
    "language_info": {
      "name": ""
    },
    "colab": {
      "provenance": []
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}