EDA with SQL sqllite lab.ipynb

{
    "cells": [
        {
            "cell_type": "markdown",
            "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-SkillsNetworkCoursesIBMDS0321ENSkillsNetwork26802033-2022-01-01\" 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\nEstimated time needed: **60** minutes.\n\n## Introduction\n\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",
            "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\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",
            "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?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDS0321ENSkillsNetwork26802033-2022-01-01\" target=\"_blank\">Spacex DataSet</a>\n"
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": "### Store the dataset in database table\n\n**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\nNow 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\n1.  Change the Date Format by manually typing DD-MM-YYYY and timestamp format as DD-MM-YYYY HH\\:MM:SS\n\n2.  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-2022-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": 79,
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": "Requirement already satisfied: sqlalchemy==1.3.9 in /home/jupyterlab/conda/envs/python/lib/python3.7/site-packages (1.3.9)\n"
                }
            ],
            "source": "!pip install sqlalchemy==1.3.9"
        },
        {
            "cell_type": "markdown",
            "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": 80,
            "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": "code",
            "execution_count": 81,
            "metadata": {},
            "outputs": [],
            "source": "import csv, sqlite3\n\ncon = sqlite3.connect(\"my_data1.db\")\ncur = con.cursor()"
        },
        {
            "cell_type": "code",
            "execution_count": 82,
            "metadata": {},
            "outputs": [],
            "source": "!pip install -q pandas==1.1.5"
        },
        {
            "cell_type": "code",
            "execution_count": 83,
            "metadata": {},
            "outputs": [
                {
                    "data": {
                        "text/plain": "'Connected: @my_data1.db'"
                    },
                    "execution_count": 83,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": "%sql sqlite:///my_data1.db"
        },
        {
            "cell_type": "code",
            "execution_count": 84,
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": " * sqlite:///my_data1.db\nDone.\n"
                },
                {
                    "data": {
                        "text/plain": "[]"
                    },
                    "execution_count": 84,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": "import pandas as pd\ndf = pd.read_csv(\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/module_2/data/Spacex.csv\")\ndf.to_sql(\"SPACEXTBL\", con, if_exists='replace', index=False,method=\"multi\")\n%sql ALTER TABLE SPACEXTBL RENAME COLUMN 'Landing _Outcome' TO 'Landing_Outcome';"
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": "## Tasks\n\nNow 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\nFor Example \"Landing_Outcome\"**\n\n### Task 1\n\n##### Display the names of the unique launch sites  in the space mission\n"
        },
        {
            "cell_type": "code",
            "execution_count": 85,
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": " * sqlite:///my_data1.db\nDone.\n"
                },
                {
                    "data": {
                        "text/html": "<table>\n    <thead>\n        <tr>\n            <th>Launch_Site</th>\n        </tr>\n    </thead>\n    <tbody>\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    </tbody>\n</table>",
                        "text/plain": "[('CCAFS LC-40',), ('CCAFS SLC-40',), ('KSC LC-39A',), ('VAFB SLC-4E',)]"
                    },
                    "execution_count": 85,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": "%sql SELECT DISTINCT LAUNCH_SITE FROM SPACEXTBL ORDER BY 1;"
        },
        {
            "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": 86,
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": " * sqlite:///my_data1.db\nDone.\n"
                },
                {
                    "data": {
                        "text/html": "<table>\n    <thead>\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    </thead>\n    <tbody>\n        <tr>\n            <td>04-06-2010</td>\n            <td>18:45:00</td>\n            <td>F9 v1.0  B0003</td>\n            <td>CCAFS LC-40</td>\n            <td>Dragon Spacecraft Qualification Unit</td>\n            <td>0</td>\n            <td>LEO</td>\n            <td>SpaceX</td>\n            <td>Success</td>\n            <td>Failure (parachute)</td>\n        </tr>\n        <tr>\n            <td>08-12-2010</td>\n            <td>15:43:00</td>\n            <td>F9 v1.0  B0004</td>\n            <td>CCAFS LC-40</td>\n            <td>Dragon demo flight C1, two CubeSats, barrel of 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>22-05-2012</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>08-10-2012</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>01-03-2013</td>\n            <td>15:10:00</td>\n            <td>F9 v1.0  B0007</td>\n            <td>CCAFS LC-40</td>\n            <td>SpaceX CRS-2</td>\n            <td>677</td>\n            <td>LEO (ISS)</td>\n            <td>NASA (CRS)</td>\n            <td>Success</td>\n            <td>No attempt</td>\n        </tr>\n    </tbody>\n</table>",
                        "text/plain": "[('04-06-2010', '18:45:00', 'F9 v1.0  B0003', 'CCAFS LC-40', 'Dragon Spacecraft Qualification Unit', 0, 'LEO', 'SpaceX', 'Success', 'Failure (parachute)'),\n ('08-12-2010', '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 ('22-05-2012', '07:44:00', 'F9 v1.0  B0005', 'CCAFS LC-40', 'Dragon demo flight C2', 525, 'LEO (ISS)', 'NASA (COTS)', 'Success', 'No attempt'),\n ('08-10-2012', '00:35:00', 'F9 v1.0  B0006', 'CCAFS LC-40', 'SpaceX CRS-1', 500, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'No attempt'),\n ('01-03-2013', '15:10:00', 'F9 v1.0  B0007', 'CCAFS LC-40', 'SpaceX CRS-2', 677, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'No attempt')]"
                    },
                    "execution_count": 86,
                    "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": 87,
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": " * sqlite:///my_data1.db\nDone.\n"
                },
                {
                    "data": {
                        "text/html": "<table>\n    <thead>\n        <tr>\n            <th>TOTAL_PAYLOAD</th>\n        </tr>\n    </thead>\n    <tbody>\n        <tr>\n            <td>111268</td>\n        </tr>\n    </tbody>\n</table>",
                        "text/plain": "[(111268,)]"
                    },
                    "execution_count": 87,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": "%sql SELECT SUM(PAYLOAD_MASS__KG_) AS TOTAL_PAYLOAD FROM SPACEXTBL WHERE PAYLOAD  LIKE '%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": 88,
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": " * sqlite:///my_data1.db\nDone.\n"
                },
                {
                    "data": {
                        "text/html": "<table>\n    <thead>\n        <tr>\n            <th>AVG_PAYLOAD</th>\n        </tr>\n    </thead>\n    <tbody>\n        <tr>\n            <td>2928.4</td>\n        </tr>\n    </tbody>\n</table>",
                        "text/plain": "[(2928.4,)]"
                    },
                    "execution_count": 88,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": "%sql SELECT AVG(PAYLOAD_MASS__KG_) AS AVG_PAYLOAD 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": 89,
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": " * sqlite:///my_data1.db\nDone.\n"
                },
                {
                    "data": {
                        "text/html": "<table>\n    <thead>\n        <tr>\n            <th>FIRST_SUCCESS_GP</th>\n        </tr>\n    </thead>\n    <tbody>\n        <tr>\n            <td>01-05-2017</td>\n        </tr>\n    </tbody>\n</table>",
                        "text/plain": "[('01-05-2017',)]"
                    },
                    "execution_count": 89,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": "%sql SELECT MIN(DATE) AS FIRST_SUCCESS_GP 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": 90,
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": " * sqlite:///my_data1.db\nDone.\n"
                },
                {
                    "data": {
                        "text/html": "<table>\n    <thead>\n        <tr>\n            <th>Booster_Version</th>\n        </tr>\n    </thead>\n    <tbody>\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    </tbody>\n</table>",
                        "text/plain": "[('F9 FT B1022',), ('F9 FT B1026',), ('F9 FT  B1021.2',), ('F9 FT  B1031.2',)]"
                    },
                    "execution_count": 90,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": "%sql SELECT DISTINCT BOOSTER_VERSION FROM SPACEXTBL WHERE PAYLOAD_MASS__KG_ BETWEEN 4000 AND 6000 AND LANDING_OUTCOME = 'Success (drone ship)';"
        },
        {
            "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": 91,
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": " * sqlite:///my_data1.db\nDone.\n"
                },
                {
                    "data": {
                        "text/html": "<table>\n    <thead>\n        <tr>\n            <th>Mission_Outcome</th>\n            <th>QTY</th>\n        </tr>\n    </thead>\n    <tbody>\n        <tr>\n            <td>Failure (in flight)</td>\n            <td>1</td>\n        </tr>\n        <tr>\n            <td>Success</td>\n            <td>98</td>\n        </tr>\n        <tr>\n            <td>Success </td>\n            <td>1</td>\n        </tr>\n        <tr>\n            <td>Success (payload status unclear)</td>\n            <td>1</td>\n        </tr>\n    </tbody>\n</table>",
                        "text/plain": "[('Failure (in flight)', 1),\n ('Success', 98),\n ('Success ', 1),\n ('Success (payload status unclear)', 1)]"
                    },
                    "execution_count": 91,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": "%sql SELECT MISSION_OUTCOME, COUNT(*) AS QTY FROM SPACEXTBL GROUP BY MISSION_OUTCOME ORDER BY MISSION_OUTCOME;"
        },
        {
            "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": 92,
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": " * sqlite:///my_data1.db\nDone.\n"
                },
                {
                    "data": {
                        "text/html": "<table>\n    <thead>\n        <tr>\n            <th>Booster_Version</th>\n        </tr>\n    </thead>\n    <tbody>\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    </tbody>\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": 92,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": "%sql SELECT DISTINCT BOOSTER_VERSION FROM SPACEXTBL WHERE PAYLOAD_MASS__KG_ = (SELECT MAX(PAYLOAD_MASS__KG_) FROM SPACEXTBL) ORDER BY BOOSTER_VERSION;"
        },
        {
            "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\n**Note: SQLLite does not support monthnames. So you need to use  substr(Date, 4, 2) as month to get the months and substr(Date,7,4)='2015' for year.**\n"
        },
        {
            "cell_type": "code",
            "execution_count": 98,
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": " * sqlite:///my_data1.db\nDone.\n"
                },
                {
                    "data": {
                        "text/html": "<table>\n    <thead>\n        <tr>\n            <th>Month</th>\n            <th>Booster_Version</th>\n            <th>Launch_Site</th>\n        </tr>\n    </thead>\n    <tbody>\n        <tr>\n            <td>01</td>\n            <td>F9 v1.1 B1012</td>\n            <td>CCAFS LC-40</td>\n        </tr>\n        <tr>\n            <td>04</td>\n            <td>F9 v1.1 B1015</td>\n            <td>CCAFS LC-40</td>\n        </tr>\n    </tbody>\n</table>",
                        "text/plain": "[('01', 'F9 v1.1 B1012', 'CCAFS LC-40'),\n ('04', 'F9 v1.1 B1015', 'CCAFS LC-40')]"
                    },
                    "execution_count": 98,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": "%sql SELECT substr(Date, 4, 2) AS Month, BOOSTER_VERSION, LAUNCH_SITE FROM SPACEXTBL WHERE LANDING_OUTCOME = 'Failure (drone ship)' AND substr(Date,7,4)='2015' ORDER BY Month;"
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": "### Task 10\n\n##### Rank the  count of  successful landing_outcomes between the date 04-06-2010 and 20-03-2017 in descending order.\n"
        },
        {
            "cell_type": "code",
            "execution_count": 106,
            "metadata": {},
            "outputs": [
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": " * sqlite:///my_data1.db\nDone.\n"
                },
                {
                    "data": {
                        "text/html": "<table>\n    <thead>\n        <tr>\n            <th>Landing_Outcome</th>\n            <th>QTY</th>\n        </tr>\n    </thead>\n    <tbody>\n        <tr>\n            <td>Success</td>\n            <td>20</td>\n        </tr>\n        <tr>\n            <td>Success (drone ship)</td>\n            <td>8</td>\n        </tr>\n        <tr>\n            <td>Success (ground pad)</td>\n            <td>6</td>\n        </tr>\n    </tbody>\n</table>",
                        "text/plain": "[('Success', 20), ('Success (drone ship)', 8), ('Success (ground pad)', 6)]"
                    },
                    "execution_count": 106,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": "%sql SELECT LANDING_OUTCOME, COUNT(*) AS QTY FROM SPACEXTBL WHERE LANDING_OUTCOME LIKE '%Success%' AND Date BETWEEN '04-06-2010' AND '20-03-2017' GROUP BY LANDING_OUTCOME ORDER BY QTY 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-2022-01-01&origin=www.coursera.org\">Hands-on Lab : String Patterns, Sorting and Grouping</a>\n\n*   <a  href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Labs_Coursera_V5/labs/Lab%20-%20Built-in%20functions%20/Hands-on_Lab__Built-in_Functions.md.html?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDS0321ENSkillsNetwork26802033-2022-01-01&origin=www.coursera.org\">Hands-on Lab: Built-in functions</a>\n\n*   <a  href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Labs_Coursera_V5/labs/Lab%20-%20Sub-queries%20and%20Nested%20SELECTs%20/instructional-labs.md.html?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDS0321ENSkillsNetwork26802033-2022-01-01&origin=www.coursera.org\">Hands-on Lab : Sub-queries and Nested SELECT Statements</a>\n\n*   <a href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Module%205/DB0201EN-Week3-1-3-SQLmagic.ipynb?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDS0321ENSkillsNetwork26802033-2022-01-01\">Hands-on Tutorial: Accessing Databases with SQL magic</a>\n\n*   <a href= \"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Module%205/DB0201EN-Week3-1-4-Analyzing.ipynb?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDS0321ENSkillsNetwork26802033-2022-01-01\">Hands-on Lab: Analyzing a real World Data Set</a>\n"
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": "## Author(s)\n\n<h4> Lakshmi Holla </h4>\n"
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": "## Other Contributors\n\n<h4> Rav Ahuja </h4>\n"
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": "## Change log\n\n| Date       | Version | Changed by    | Change Description        |\n| ---------- | ------- | ------------- | ------------------------- |\n| 2021-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\"> \u00a9 IBM Corporation 2021. All rights reserved. <h3/>\n"
        }
    ],
    "metadata": {
        "kernelspec": {
            "display_name": "Python",
            "language": "python",
            "name": "conda-env-python-py"
        },
        "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.7.12"
        }
    },
    "nbformat": 4,
    "nbformat_minor": 4
}