week 2 data eda.ipynb

{"cells": [{"metadata": {}, "cell_type": "markdown", "source": "<center>\n    <img src=\"https://gitlab.com/ibm/skills-network/courses/placeholder101/-/raw/master/labs/module%201/images/IDSNlogo.png\"  width=\"300\" alt=\"cognitiveclass.ai logo\"  />\n</center>\n\n<h1 align=center><font size = 5>Assignment: SQL Notebook for Peer Assignment</font></h1>\n\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"}, {"metadata": {}, "cell_type": "markdown", "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"}, {"metadata": {}, "cell_type": "markdown", "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-2021-01-01\" target=\"_blank\">Spacex DataSet</a>\n"}, {"metadata": {}, "cell_type": "markdown", "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\n    Here you should place the cursor at Date field and manually type as DD-MM-YYYY.\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"}, {"metadata": {}, "cell_type": "markdown", "source": "**Changes to be considered when having DB2 instance with the new UI having Go to UI screen**\n\n*   Refer to this insruction in this <a href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Labs_Coursera_V5/labs/Lab%20-%20Sign%20up%20for%20IBM%20Cloud%20-%20Create%20Db2%20service%20instance%20-%20Get%20started%20with%20the%20Db2%20console/instructional-labs.md.html?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDS0321ENSkillsNetwork26802033-2021-01-01\">link</a> for viewing  the new  Go to UI screen.\n\n*   Later click on **Data link(below SQL)**  in the Go to UI screen  and click on **Load Data** tab.\n\n*   Later browse for the downloaded spacex file.\n\n<img src=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/module_2/images/browsefile.png\" width=\"800\"/>\n\n*   Once done select the schema andload the file.\n\n <img src=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/module_2/images/spacexload3.png\" width=\"800\"/>\n"}, {"metadata": {}, "cell_type": "code", "source": "!pip install sqlalchemy==1.3.9\n!pip install ibm_db_sa\n!pip install ipython-sql", "execution_count": 1, "outputs": [{"output_type": "stream", "text": "Collecting sqlalchemy==1.3.9\n  Downloading SQLAlchemy-1.3.9.tar.gz (6.0 MB)\n\u001b[K     |\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588| 6.0 MB 10.6 MB/s eta 0:00:01\n\u001b[?25hBuilding wheels for collected packages: sqlalchemy\n  Building wheel for sqlalchemy (setup.py) ... \u001b[?25ldone\n\u001b[?25h  Created wheel for sqlalchemy: filename=SQLAlchemy-1.3.9-cp38-cp38-linux_x86_64.whl size=1209498 sha256=df290e7fa36a4db8f5a7fc1e35fff0247c77bcb46a1f04ff2cc455d3e919f024\n  Stored in directory: /tmp/wsuser/.cache/pip/wheels/cb/43/46/fa638f2422554332b7865d600275b24568bf60e76104a94bb4\nSuccessfully built sqlalchemy\nInstalling collected packages: sqlalchemy\n  Attempting uninstall: sqlalchemy\n    Found existing installation: SQLAlchemy 1.4.22\n    Uninstalling SQLAlchemy-1.4.22:\n      Successfully uninstalled SQLAlchemy-1.4.22\nSuccessfully installed sqlalchemy-1.3.9\nRequirement already satisfied: ibm_db_sa in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (0.3.7)\nRequirement already satisfied: sqlalchemy>=0.7.3 in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ibm_db_sa) (1.3.9)\nRequirement already satisfied: ibm-db>=2.0.0 in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ibm_db_sa) (3.0.4)\nCollecting ipython-sql\n  Downloading ipython_sql-0.4.0-py3-none-any.whl (19 kB)\nRequirement already satisfied: sqlalchemy>=0.6.7 in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ipython-sql) (1.3.9)\nRequirement already satisfied: ipython-genutils>=0.1.0 in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ipython-sql) (0.2.0)\nCollecting sqlparse\n  Downloading sqlparse-0.4.2-py3-none-any.whl (42 kB)\n\u001b[K     |\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588| 42 kB 3.1 MB/s  eta 0:00:01\n\u001b[?25hCollecting prettytable<1\n  Downloading prettytable-0.7.2.zip (28 kB)\nRequirement already satisfied: six in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ipython-sql) (1.15.0)\nRequirement already satisfied: ipython>=1.0 in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ipython-sql) (7.27.0)\nRequirement already satisfied: decorator in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (5.0.9)\nRequirement already satisfied: prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0 in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (3.0.20)\nRequirement already satisfied: traitlets>=4.2 in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (5.0.5)\nRequirement already satisfied: setuptools>=18.5 in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (52.0.0.post20211006)\nRequirement already satisfied: matplotlib-inline in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (0.1.2)\nRequirement already satisfied: backcall in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (0.2.0)\nRequirement already satisfied: pygments in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (2.9.0)\nRequirement already satisfied: pickleshare in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (0.7.5)\nRequirement already satisfied: pexpect>4.3 in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (4.8.0)\nRequirement already satisfied: jedi>=0.16 in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from ipython>=1.0->ipython-sql) (0.17.2)\nRequirement already satisfied: parso<0.8.0,>=0.7.0 in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from jedi>=0.16->ipython>=1.0->ipython-sql) (0.7.0)\nRequirement already satisfied: ptyprocess>=0.5 in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from pexpect>4.3->ipython>=1.0->ipython-sql) (0.7.0)\nRequirement already satisfied: wcwidth in /opt/conda/envs/Python-3.8-main/lib/python3.8/site-packages (from prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0->ipython>=1.0->ipython-sql) (0.2.5)\nBuilding wheels for collected packages: prettytable\n  Building wheel for prettytable (setup.py) ... \u001b[?25ldone\n\u001b[?25h  Created wheel for prettytable: filename=prettytable-0.7.2-py3-none-any.whl size=13700 sha256=42810e153a2ee98696aa38846c83e99681b91582c288c47e12c9315b55c16c34\n  Stored in directory: /tmp/wsuser/.cache/pip/wheels/48/6d/77/9517cb933af254f51a446f1a5ec9c2be3e45f17384940bce68\nSuccessfully built prettytable\nInstalling collected packages: sqlparse, prettytable, ipython-sql\nSuccessfully installed ipython-sql-0.4.0 prettytable-0.7.2 sqlparse-0.4.2\n", "name": "stdout"}]}, {"metadata": {}, "cell_type": "markdown", "source": "### Connect to the database\n\nLet us first load the SQL extension and establish a connection with the database\n"}, {"metadata": {}, "cell_type": "code", "source": "%load_ext sql", "execution_count": 2, "outputs": []}, {"metadata": {}, "cell_type": "markdown", "source": "**DB2 magic in case of old UI service credentials.**\n\nIn the next cell enter your db2 connection string. Recall you created Service Credentials for your Db2 instance before. From the **uri** field of your Db2 service credentials copy everything after db2:// (except the double quote at the end) and paste it in the cell below after ibm_db_sa://\n\n<img src =\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/FinalModule_edX/images/URI.jpg\">\n\nin the following format\n\n**%sql ibm_db_sa://my-username:my-password\\@my-hostname:my-port/my-db-name**\n\n**DB2 magic in case of new UI service credentials.**\n\n<img src =\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/module_2/images/servicecredentials.png\" width=600>  \n\n*   Use the following format.\n\n*   Add security=SSL at the end\n\n**%sql ibm_db_sa://my-username:my-password\\@my-hostname:my-port/my-db-name?security=SSL**\n"}, {"metadata": {}, "cell_type": "code", "source": "%sql ibm_db_sa://crc12421:TXeOfalFw0aAGm1n@b0aebb68-94fa-46ec-a1fc-1c999edb6187.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:31249/bludb?security=SSL", "execution_count": 5, "outputs": []}, {"metadata": {}, "cell_type": "code", "source": "%sql ibm_db_sa://", "execution_count": null, "outputs": []}, {"metadata": {}, "cell_type": "markdown", "source": "## Tasks\n\nNow write and execute SQL queries to solve the assignment tasks.\n\n### Task 1\n\n##### Display the names of the unique launch sites  in the space mission\n"}, {"metadata": {}, "cell_type": "code", "source": "tb=\"CAPESTONE-WEEK2-SPACE-X\"\n%sql select distinct (LAUNCH_SITE) from \"CAPESTONE-WEEK2-SPACE-X\" ;", "execution_count": 6, "outputs": [{"output_type": "stream", "text": " * ibm_db_sa://crc12421:***@b0aebb68-94fa-46ec-a1fc-1c999edb6187.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:31249/bludb\nDone.\n", "name": "stdout"}, {"output_type": "execute_result", "execution_count": 6, "data": {"text/plain": "[('CCAFS LC-40',), ('CCAFS SLC-40',), ('KSC LC-39A',), ('VAFB SLC-4E',)]", "text/html": "<table>\n    <tr>\n        <th>launch_site</th>\n    </tr>\n    <tr>\n        <td>CCAFS LC-40</td>\n    </tr>\n    <tr>\n        <td>CCAFS SLC-40</td>\n    </tr>\n    <tr>\n        <td>KSC LC-39A</td>\n    </tr>\n    <tr>\n        <td>VAFB SLC-4E</td>\n    </tr>\n</table>"}, "metadata": {}}]}, {"metadata": {}, "cell_type": "markdown", "source": "### Task 2\n\n##### Display 5 records where launch sites begin with the string 'CCA'\n"}, {"metadata": {}, "cell_type": "code", "source": "%sql select LAUNCH_SITE from \"CAPESTONE-WEEK2-SPACE-X\" where LAUNCH_SITE like 'CCA%' limit 5 ;", "execution_count": 7, "outputs": [{"output_type": "stream", "text": " * ibm_db_sa://crc12421:***@b0aebb68-94fa-46ec-a1fc-1c999edb6187.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:31249/bludb\nDone.\n", "name": "stdout"}, {"output_type": "execute_result", "execution_count": 7, "data": {"text/plain": "[('CCAFS LC-40',),\n ('CCAFS LC-40',),\n ('CCAFS LC-40',),\n ('CCAFS LC-40',),\n ('CCAFS LC-40',)]", "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 LC-40</td>\n    </tr>\n    <tr>\n        <td>CCAFS LC-40</td>\n    </tr>\n    <tr>\n        <td>CCAFS LC-40</td>\n    </tr>\n    <tr>\n        <td>CCAFS LC-40</td>\n    </tr>\n</table>"}, "metadata": {}}]}, {"metadata": {}, "cell_type": "markdown", "source": "### Task 3\n\n##### Display the total payload mass carried by boosters launched by NASA (CRS)\n"}, {"metadata": {}, "cell_type": "code", "source": "%sql select sum(PAYLOAD_MASS__KG_) from \"CAPESTONE-WEEK2-SPACE-X\"  where CUSTOMER like 'NASA (CRS)';", "execution_count": 8, "outputs": [{"output_type": "stream", "text": " * ibm_db_sa://crc12421:***@b0aebb68-94fa-46ec-a1fc-1c999edb6187.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:31249/bludb\nDone.\n", "name": "stdout"}, {"output_type": "execute_result", "execution_count": 8, "data": {"text/plain": "[(22007,)]", "text/html": "<table>\n    <tr>\n        <th>1</th>\n    </tr>\n    <tr>\n        <td>22007</td>\n    </tr>\n</table>"}, "metadata": {}}]}, {"metadata": {}, "cell_type": "markdown", "source": "### Task 4\n\n##### Display average payload mass carried by booster version F9 v1.1\n"}, {"metadata": {}, "cell_type": "code", "source": "%sql select avg(PAYLOAD_MASS__KG_) from \"CAPESTONE-WEEK2-SPACE-X\"  where BOOSTER_VERSION like 'F9 v1.1';", "execution_count": 9, "outputs": [{"output_type": "stream", "text": " * ibm_db_sa://crc12421:***@b0aebb68-94fa-46ec-a1fc-1c999edb6187.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:31249/bludb\nDone.\n", "name": "stdout"}, {"output_type": "execute_result", "execution_count": 9, "data": {"text/plain": "[(3676,)]", "text/html": "<table>\n    <tr>\n        <th>1</th>\n    </tr>\n    <tr>\n        <td>3676</td>\n    </tr>\n</table>"}, "metadata": {}}]}, {"metadata": {}, "cell_type": "markdown", "source": "### Task 5\n\n##### List the date when the first successful landing outcome in ground pad was acheived.\n\n*Hint:Use min function*\n"}, {"metadata": {}, "cell_type": "code", "source": "%sql select DATE from \"CAPESTONE-WEEK2-SPACE-X\"   where MISSION_OUTCOME like 'Success'  order by DATE ASC limit 1 ; ", "execution_count": 25, "outputs": [{"output_type": "stream", "text": " * ibm_db_sa://crc12421:***@b0aebb68-94fa-46ec-a1fc-1c999edb6187.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:31249/bludb\nDone.\n", "name": "stdout"}, {"output_type": "execute_result", "execution_count": 25, "data": {"text/plain": "[(datetime.date(2010, 4, 6),)]", "text/html": "<table>\n    <tr>\n        <th>DATE</th>\n    </tr>\n    <tr>\n        <td>2010-04-06</td>\n    </tr>\n</table>"}, "metadata": {}}]}, {"metadata": {}, "cell_type": "markdown", "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"}, {"metadata": {}, "cell_type": "code", "source": "%sql select BOOSTER_VERSION from \"CAPESTONE-WEEK2-SPACE-X\" where  MISSION_OUTCOME like 'Success' and landing__outcome like '%drone ship%' and PAYLOAD_MASS__KG_ between 4000 and 6000;", "execution_count": 29, "outputs": [{"output_type": "stream", "text": " * ibm_db_sa://crc12421:***@b0aebb68-94fa-46ec-a1fc-1c999edb6187.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:31249/bludb\nDone.\n", "name": "stdout"}, {"output_type": "execute_result", "execution_count": 29, "data": {"text/plain": "[('F9 FT B1020',), ('F9 FT B1022',), ('F9 FT  B1031.2',)]", "text/html": "<table>\n    <tr>\n        <th>booster_version</th>\n    </tr>\n    <tr>\n        <td>F9 FT B1020</td>\n    </tr>\n    <tr>\n        <td>F9 FT B1022</td>\n    </tr>\n    <tr>\n        <td>F9 FT  B1031.2</td>\n    </tr>\n</table>"}, "metadata": {}}]}, {"metadata": {}, "cell_type": "markdown", "source": "### Task 7\n\n##### List the total number of successful and failure mission outcomes\n"}, {"metadata": {}, "cell_type": "code", "source": "%sql select   count(*) from \"CAPESTONE-WEEK2-SPACE-X\" where  mission_outcome like '%Success%' ;", "execution_count": 38, "outputs": [{"output_type": "stream", "text": " * ibm_db_sa://crc12421:***@b0aebb68-94fa-46ec-a1fc-1c999edb6187.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:31249/bludb\nDone.\n", "name": "stdout"}, {"output_type": "execute_result", "execution_count": 38, "data": {"text/plain": "[(45,)]", "text/html": "<table>\n    <tr>\n        <th>1</th>\n    </tr>\n    <tr>\n        <td>45</td>\n    </tr>\n</table>"}, "metadata": {}}]}, {"metadata": {}, "cell_type": "code", "source": "%sql select  'Succesxxs', count(*) from \"CAPESTONE-WEEK2-SPACE-X\" where  mission_outcome like '%Success%' ;", "execution_count": 41, "outputs": [{"output_type": "stream", "text": " * ibm_db_sa://crc12421:***@b0aebb68-94fa-46ec-a1fc-1c999edb6187.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:31249/bludb\nDone.\n", "name": "stdout"}, {"output_type": "execute_result", "execution_count": 41, "data": {"text/plain": "[('Succesxxs', 45)]", "text/html": "<table>\n    <tr>\n        <th>1</th>\n        <th>2</th>\n    </tr>\n    <tr>\n        <td>Succesxxs</td>\n        <td>45</td>\n    </tr>\n</table>"}, "metadata": {}}]}, {"metadata": {}, "cell_type": "code", "source": "%sql select 'failure', count(*) from \"CAPESTONE-WEEK2-SPACE-X\" where  mission_outcome like '%Failure%';", "execution_count": 50, "outputs": [{"output_type": "stream", "text": " * ibm_db_sa://crc12421:***@b0aebb68-94fa-46ec-a1fc-1c999edb6187.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:31249/bludb\nDone.\n", "name": "stdout"}, {"output_type": "execute_result", "execution_count": 50, "data": {"text/plain": "[('failure', 0)]", "text/html": "<table>\n    <tr>\n        <th>1</th>\n        <th>2</th>\n    </tr>\n    <tr>\n        <td>failure</td>\n        <td>0</td>\n    </tr>\n</table>"}, "metadata": {}}]}, {"metadata": {}, "cell_type": "code", "source": "%sql select 'success', count(*) from \"CAPESTONE-WEEK2-SPACE-X\" where  mission_outcome like '%Sucess%';", "execution_count": 52, "outputs": [{"output_type": "stream", "text": " * ibm_db_sa://crc12421:***@b0aebb68-94fa-46ec-a1fc-1c999edb6187.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:31249/bludb\nDone.\n", "name": "stdout"}, {"output_type": "execute_result", "execution_count": 52, "data": {"text/plain": "[('success', 0)]", "text/html": "<table>\n    <tr>\n        <th>1</th>\n        <th>2</th>\n    </tr>\n    <tr>\n        <td>success</td>\n        <td>0</td>\n    </tr>\n</table>"}, "metadata": {}}]}, {"metadata": {}, "cell_type": "code", "source": "%sql select 'success', count(*) from \"CAPESTONE-WEEK2-SPACE-X\" where  mission_outcome like '%Success%' union  (select 'failure', count(*) from \"CAPESTONE-WEEK2-SPACE-X\" where  mission_outcome like '%Failure%');", "execution_count": 53, "outputs": [{"output_type": "stream", "text": " * ibm_db_sa://crc12421:***@b0aebb68-94fa-46ec-a1fc-1c999edb6187.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:31249/bludb\nDone.\n", "name": "stdout"}, {"output_type": "execute_result", "execution_count": 53, "data": {"text/plain": "[('failure', 0), ('success', 45)]", "text/html": "<table>\n    <tr>\n        <th>1</th>\n        <th>2</th>\n    </tr>\n    <tr>\n        <td>failure</td>\n        <td>0</td>\n    </tr>\n    <tr>\n        <td>success</td>\n        <td>45</td>\n    </tr>\n</table>"}, "metadata": {}}]}, {"metadata": {}, "cell_type": "markdown", "source": "### Task 8\n\n##### List the   names of the booster_versions which have carried the maximum payload mass. Use a subquery\n"}, {"metadata": {}, "cell_type": "code", "source": "%sql select booster_version, PAYLOAD_MASS__KG_ from \"CAPESTONE-WEEK2-SPACE-X\" where PAYLOAD_MASS__KG_ = (select max(PAYLOAD_MASS__KG_) from \"CAPESTONE-WEEK2-SPACE-X\");", "execution_count": 57, "outputs": [{"output_type": "stream", "text": " * ibm_db_sa://crc12421:***@b0aebb68-94fa-46ec-a1fc-1c999edb6187.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:31249/bludb\nDone.\n", "name": "stdout"}, {"output_type": "execute_result", "execution_count": 57, "data": {"text/plain": "[('F9 B5 B1048.4', 15600),\n ('F9 B5 B1049.4', 15600),\n ('F9 B5 B1049.5', 15600),\n ('F9 B5 B1060.2', 15600),\n ('F9 B5 B1058.3', 15600)]", "text/html": "<table>\n    <tr>\n        <th>booster_version</th>\n        <th>payload_mass__kg_</th>\n    </tr>\n    <tr>\n        <td>F9 B5 B1048.4</td>\n        <td>15600</td>\n    </tr>\n    <tr>\n        <td>F9 B5 B1049.4</td>\n        <td>15600</td>\n    </tr>\n    <tr>\n        <td>F9 B5 B1049.5</td>\n        <td>15600</td>\n    </tr>\n    <tr>\n        <td>F9 B5 B1060.2</td>\n        <td>15600</td>\n    </tr>\n    <tr>\n        <td>F9 B5 B1058.3</td>\n        <td>15600</td>\n    </tr>\n</table>"}, "metadata": {}}]}, {"metadata": {}, "cell_type": "markdown", "source": "### Task 9\n\n##### List the failed landing_outcomes in drone ship, their booster versions, and launch site names for in year 2015\n"}, {"metadata": {}, "cell_type": "code", "source": "%sql select BOOSTER_VERSION, launch_site from \"CAPESTONE-WEEK2-SPACE-X\" where  landing__OUTCOME like '%Failure%' and landing__outcome like '%drone ship%' and DATE like '%2015%';", "execution_count": 64, "outputs": [{"output_type": "stream", "text": " * ibm_db_sa://crc12421:***@b0aebb68-94fa-46ec-a1fc-1c999edb6187.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:31249/bludb\nDone.\n", "name": "stdout"}, {"output_type": "execute_result", "execution_count": 64, "data": {"text/plain": "[('F9 v1.1 B1012', 'CCAFS LC-40')]", "text/html": "<table>\n    <tr>\n        <th>booster_version</th>\n        <th>launch_site</th>\n    </tr>\n    <tr>\n        <td>F9 v1.1 B1012</td>\n        <td>CCAFS LC-40</td>\n    </tr>\n</table>"}, "metadata": {}}]}, {"metadata": {}, "cell_type": "markdown", "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"}, {"metadata": {}, "cell_type": "code", "source": "%sql select  landing__OUTCOME, count( landing__OUTCOME) as tnum from \"CAPESTONE-WEEK2-SPACE-X\" group by   landing__OUTCOME  order by tnum ;", "execution_count": 67, "outputs": [{"output_type": "stream", "text": " * ibm_db_sa://crc12421:***@b0aebb68-94fa-46ec-a1fc-1c999edb6187.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:31249/bludb\nDone.\n", "name": "stdout"}, {"output_type": "execute_result", "execution_count": 67, "data": {"text/plain": "[('Controlled (ocean)', 1),\n ('Failure', 1),\n ('Failure (drone ship)', 2),\n ('Failure (parachute)', 2),\n ('Success (ground pad)', 4),\n ('Success (drone ship)', 5),\n ('No attempt', 12),\n ('Success', 18)]", "text/html": "<table>\n    <tr>\n        <th>landing__outcome</th>\n        <th>tnum</th>\n    </tr>\n    <tr>\n        <td>Controlled (ocean)</td>\n        <td>1</td>\n    </tr>\n    <tr>\n        <td>Failure</td>\n        <td>1</td>\n    </tr>\n    <tr>\n        <td>Failure (drone ship)</td>\n        <td>2</td>\n    </tr>\n    <tr>\n        <td>Failure (parachute)</td>\n        <td>2</td>\n    </tr>\n    <tr>\n        <td>Success (ground pad)</td>\n        <td>4</td>\n    </tr>\n    <tr>\n        <td>Success (drone ship)</td>\n        <td>5</td>\n    </tr>\n    <tr>\n        <td>No attempt</td>\n        <td>12</td>\n    </tr>\n    <tr>\n        <td>Success</td>\n        <td>18</td>\n    </tr>\n</table>"}, "metadata": {}}]}, {"metadata": {}, "cell_type": "markdown", "source": "### Reference Links\n\n*   <a href =\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Labs_Coursera_V5/labs/Lab%20-%20String%20Patterns%20-%20Sorting%20-%20Grouping/instructional-labs.md.html?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDS0321ENSkillsNetwork26802033-2021-01-01&origin=www.coursera.org\">Hands-on Lab : String Patterns, Sorting and Grouping</a>\n\n*   <a  href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Labs_Coursera_V5/labs/Lab%20-%20Built-in%20functions%20/Hands-on_Lab__Built-in_Functions.md.html?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDS0321ENSkillsNetwork26802033-2021-01-01&origin=www.coursera.org\">Hands-on Lab: Built-in functions</a>\n\n*   <a  href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Labs_Coursera_V5/labs/Lab%20-%20Sub-queries%20and%20Nested%20SELECTs%20/instructional-labs.md.html?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDS0321ENSkillsNetwork26802033-2021-01-01&origin=www.coursera.org\">Hands-on Lab : Sub-queries and Nested SELECT Statements</a>\n\n*   <a href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Module%205/DB0201EN-Week3-1-3-SQLmagic.ipynb?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDS0321ENSkillsNetwork26802033-2021-01-01\">Hands-on Tutorial: Accessing Databases with SQL magic</a>\n\n*   <a href= \"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Module%205/DB0201EN-Week3-1-4-Analyzing.ipynb?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDS0321ENSkillsNetwork26802033-2021-01-01\">Hands-on Lab: Analyzing a real World Data Set</a>\n"}, {"metadata": {}, "cell_type": "markdown", "source": "## Author(s)\n\n<h4> Lakshmi Holla </h4>\n"}, {"metadata": {}, "cell_type": "markdown", "source": "## Other Contributors\n\n<h4> Rav Ahuja </h4>\n"}, {"metadata": {}, "cell_type": "markdown", "source": "## Change log\n\n| Date       | Version | Changed by    | Change Description        |\n| ---------- | ------- | ------------- | ------------------------- |\n| 2021-10-12 | 0.4     | Lakshmi Holla | Changed markdown          |\n| 2021-08-24 | 0.3     | Lakshmi Holla | Added library update      |\n| 2021-07-09 | 0.2     | Lakshmi Holla | Changes made in magic sql |\n| 2021-05-20 | 0.1     | Lakshmi Holla | Created Initial Version   |\n"}, {"metadata": {}, "cell_type": "markdown", "source": "## <h3 align=\"center\"> \u00a9 IBM Corporation 2021. All rights reserved. <h3/>\n"}], "metadata": {"kernelspec": {"name": "python3", "display_name": "Python 3.8", "language": "python"}, "language_info": {"name": "python", "version": "3.8.12", "mimetype": "text/x-python", "codemirror_mode": {"name": "ipython", "version": 3}, "pygments_lexer": "ipython3", "nbconvert_exporter": "python", "file_extension": ".py"}}, "nbformat": 4, "nbformat_minor": 4}