jupyter-labs-eda-sql-coursera (1).ipynb

{"cells": [{"metadata": {}, "cell_type": "markdown", "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"}, {"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-2022-01-01\" target=\"_blank\">Spacex DataSet</a>\n"}, {"metadata": {}, "cell_type": "markdown", "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?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   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[2K     \u001b[90m\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u001b[0m \u001b[32m6.0/6.0 MB\u001b[0m \u001b[31m25.5 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m00:01\u001b[0m00:01\u001b[0m\n\u001b[?25h  Preparing metadata (setup.py) ... \u001b[?25ldone\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-cp310-cp310-linux_x86_64.whl size=1159594 sha256=e30c02d0372492915979bcc39ecfe16f97e986bca2e6aca93adc1de8145ee2d7\n  Stored in directory: /tmp/wsuser/.cache/pip/wheels/8b/6d/9c/e1793e5a2d85b89b8c362a4f0e3def54b786bcd14923b9ff00\nSuccessfully built sqlalchemy\nInstalling collected packages: sqlalchemy\n  Attempting uninstall: sqlalchemy\n    Found existing installation: SQLAlchemy 1.4.39\n    Uninstalling SQLAlchemy-1.4.39:\n      Successfully uninstalled SQLAlchemy-1.4.39\nSuccessfully installed sqlalchemy-1.3.9\nRequirement already satisfied: ibm_db_sa in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (0.3.8)\nRequirement already satisfied: ibm-db>=2.0.0 in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ibm_db_sa) (3.1.3)\nRequirement already satisfied: sqlalchemy>=0.7.3 in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ibm_db_sa) (1.3.9)\nCollecting ipython-sql\n  Downloading ipython_sql-0.4.1-py3-none-any.whl (21 kB)\nCollecting sqlparse\n  Downloading sqlparse-0.4.3-py3-none-any.whl (42 kB)\n\u001b[2K     \u001b[90m\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u001b[0m \u001b[32m42.8/42.8 kB\u001b[0m \u001b[31m6.8 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n\u001b[?25hCollecting prettytable<1\n  Downloading prettytable-0.7.2.zip (28 kB)\n  Preparing metadata (setup.py) ... \u001b[?25ldone\n\u001b[?25hRequirement already satisfied: sqlalchemy>=0.6.7 in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython-sql) (1.3.9)\nRequirement already satisfied: ipython-genutils>=0.1.0 in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython-sql) (0.2.0)\nRequirement already satisfied: ipython>=1.0 in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython-sql) (8.4.0)\nRequirement already satisfied: six in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython-sql) (1.16.0)\nRequirement already satisfied: traitlets>=5 in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython>=1.0->ipython-sql) (5.1.1)\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) (2.11.2)\nRequirement already satisfied: setuptools>=18.5 in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython>=1.0->ipython-sql) (63.4.1)\nRequirement already satisfied: pexpect>4.3 in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython>=1.0->ipython-sql) (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) (3.0.20)\nRequirement already satisfied: pickleshare in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython>=1.0->ipython-sql) (0.7.5)\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.18.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.1.6)\nRequirement already satisfied: stack-data in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython>=1.0->ipython-sql) (0.2.0)\nRequirement already satisfied: backcall in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython>=1.0->ipython-sql) (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) (5.1.1)\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.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.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.2.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.8.3)\nRequirement already satisfied: asttokens in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from stack-data->ipython>=1.0->ipython-sql) (2.0.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.2.2)\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=13714 sha256=4e1d3b2e014015d3eea8ba00bd14ce4aa790476612dfb52d292c7c4271756e7d\n  Stored in directory: /tmp/wsuser/.cache/pip/wheels/25/4b/07/18c5d92824315576e478206ea69df34a9e31958f6143eb0e31\nSuccessfully built prettytable\nInstalling collected packages: prettytable, sqlparse, ipython-sql\nSuccessfully installed ipython-sql-0.4.1 prettytable-0.7.2 sqlparse-0.4.3\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": "", "execution_count": null, "outputs": []}, {"metadata": {}, "cell_type": "code", "source": "", "execution_count": null, "outputs": []}, {"metadata": {}, "cell_type": "code", "source": "%sql ibm_db_sa://bnd27390:WdiGwPEH7JqN3Q9L@55fbc997-9266-4331-afd3-888b05e734c0.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:31929/bludb?security=SSL", "execution_count": 5, "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": "%sql SELECT UNIQUE launch_site FROM SPACE_X", "execution_count": 6, "outputs": [{"output_type": "stream", "text": " * ibm_db_sa://bnd27390:***@55fbc997-9266-4331-afd3-888b05e734c0.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:31929/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 * FROM SPACE_X WHERE launch_site LIKE 'CCA%' LIMIT 5", "execution_count": 8, "outputs": [{"output_type": "stream", "text": " * ibm_db_sa://bnd27390:***@55fbc997-9266-4331-afd3-888b05e734c0.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:31929/bludb\nDone.\n", "name": "stdout"}, {"output_type": "execute_result", "execution_count": 8, "data": {"text/plain": "[(datetime.date(2010, 6, 4), datetime.time(18, 45), 'F9 v1.0  B0003', 'CCAFS LC-40', 'Dragon Spacecraft Qualification Unit', 0, 'LEO', 'SpaceX', 'Success', 'Failure (parachute)'),\n (datetime.date(2010, 12, 8), datetime.time(15, 43), 'F9 v1.0  B0004', 'CCAFS LC-40', 'Dragon demo flight C1, two CubeSats, barrel of Brouere cheese', 0, 'LEO (ISS)', 'NASA (COTS) NRO', 'Success', 'Failure (parachute)'),\n (datetime.date(2012, 5, 22), datetime.time(7, 44), 'F9 v1.0  B0005', 'CCAFS LC-40', 'Dragon demo flight C2', 525, 'LEO (ISS)', 'NASA (COTS)', 'Success', 'No attempt'),\n (datetime.date(2012, 10, 8), datetime.time(0, 35), 'F9 v1.0  B0006', 'CCAFS LC-40', 'SpaceX CRS-1', 500, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'No attempt'),\n (datetime.date(2013, 3, 1), datetime.time(15, 10), 'F9 v1.0  B0007', 'CCAFS LC-40', 'SpaceX CRS-2', 677, 'LEO (ISS)', 'NASA (CRS)', 'Success', 'No attempt')]", "text/html": "<table>\n    <tr>\n        <th>DATE</th>\n        <th>time__utc_</th>\n        <th>booster_version</th>\n        <th>launch_site</th>\n        <th>payload</th>\n        <th>payload_mass__kg_</th>\n        <th>orbit</th>\n        <th>customer</th>\n        <th>mission_outcome</th>\n        <th>landing__outcome</th>\n    </tr>\n    <tr>\n        <td>2010-06-04</td>\n        <td>18:45:00</td>\n        <td>F9 v1.0  B0003</td>\n        <td>CCAFS LC-40</td>\n        <td>Dragon Spacecraft Qualification Unit</td>\n        <td>0</td>\n        <td>LEO</td>\n        <td>SpaceX</td>\n        <td>Success</td>\n        <td>Failure (parachute)</td>\n    </tr>\n    <tr>\n        <td>2010-12-08</td>\n        <td>15:43:00</td>\n        <td>F9 v1.0  B0004</td>\n        <td>CCAFS LC-40</td>\n        <td>Dragon demo flight C1, two CubeSats, barrel of Brouere cheese</td>\n        <td>0</td>\n        <td>LEO (ISS)</td>\n        <td>NASA (COTS) NRO</td>\n        <td>Success</td>\n        <td>Failure (parachute)</td>\n    </tr>\n    <tr>\n        <td>2012-05-22</td>\n        <td>07:44:00</td>\n        <td>F9 v1.0  B0005</td>\n        <td>CCAFS LC-40</td>\n        <td>Dragon demo flight C2</td>\n        <td>525</td>\n        <td>LEO (ISS)</td>\n        <td>NASA (COTS)</td>\n        <td>Success</td>\n        <td>No attempt</td>\n    </tr>\n    <tr>\n        <td>2012-10-08</td>\n        <td>00:35:00</td>\n        <td>F9 v1.0  B0006</td>\n        <td>CCAFS LC-40</td>\n        <td>SpaceX CRS-1</td>\n        <td>500</td>\n        <td>LEO (ISS)</td>\n        <td>NASA (CRS)</td>\n        <td>Success</td>\n        <td>No attempt</td>\n    </tr>\n    <tr>\n        <td>2013-03-01</td>\n        <td>15:10:00</td>\n        <td>F9 v1.0  B0007</td>\n        <td>CCAFS LC-40</td>\n        <td>SpaceX CRS-2</td>\n        <td>677</td>\n        <td>LEO (ISS)</td>\n        <td>NASA (CRS)</td>\n        <td>Success</td>\n        <td>No attempt</td>\n    </tr>\n</table>"}, "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 SPACE_X WHERE customer = 'NASA (CRS)'", "execution_count": 9, "outputs": [{"output_type": "stream", "text": " * ibm_db_sa://bnd27390:***@55fbc997-9266-4331-afd3-888b05e734c0.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:31929/bludb\nDone.\n", "name": "stdout"}, {"output_type": "execute_result", "execution_count": 9, "data": {"text/plain": "[(45596,)]", "text/html": "<table>\n    <tr>\n        <th>1</th>\n    </tr>\n    <tr>\n        <td>45596</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 SPACE_X WHERE booster_version LIKE 'F9 v1.1 %'", "execution_count": 10, "outputs": [{"output_type": "stream", "text": " * ibm_db_sa://bnd27390:***@55fbc997-9266-4331-afd3-888b05e734c0.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:31929/bludb\nDone.\n", "name": "stdout"}, {"output_type": "execute_result", "execution_count": 10, "data": {"text/plain": "[(2337,)]", "text/html": "<table>\n    <tr>\n        <th>1</th>\n    </tr>\n    <tr>\n        <td>2337</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 MIN(DATE) FROM SPACE_X WHERE landing__outcome = 'Success (ground pad)'", "execution_count": 11, "outputs": [{"output_type": "stream", "text": " * ibm_db_sa://bnd27390:***@55fbc997-9266-4331-afd3-888b05e734c0.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:31929/bludb\nDone.\n", "name": "stdout"}, {"output_type": "execute_result", "execution_count": 11, "data": {"text/plain": "[(datetime.date(2015, 12, 22),)]", "text/html": "<table>\n    <tr>\n        <th>1</th>\n    </tr>\n    <tr>\n        <td>2015-12-22</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 SPACE_X WHERE landing__outcome = 'Success (drone ship)' AND payload_mass__kg_ BETWEEN 4000 AND 6000", "execution_count": 12, "outputs": [{"output_type": "stream", "text": " * ibm_db_sa://bnd27390:***@55fbc997-9266-4331-afd3-888b05e734c0.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:31929/bludb\nDone.\n", "name": "stdout"}, {"output_type": "execute_result", "execution_count": 12, "data": {"text/plain": "[('F9 FT B1022',), ('F9 FT B1026',), ('F9 FT  B1021.2',), ('F9 FT  B1031.2',)]", "text/html": "<table>\n    <tr>\n        <th>booster_version</th>\n    </tr>\n    <tr>\n        <td>F9 FT B1022</td>\n    </tr>\n    <tr>\n        <td>F9 FT B1026</td>\n    </tr>\n    <tr>\n        <td>F9 FT  B1021.2</td>\n    </tr>\n    <tr>\n        <td>F9 FT  B1031.2</td>\n    </tr>\n</table>"}, "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 landing__outcome, COUNT(landing__outcome) FROM SPACE_X GROUP BY landing__outcome", "execution_count": 13, "outputs": [{"output_type": "stream", "text": " * ibm_db_sa://bnd27390:***@55fbc997-9266-4331-afd3-888b05e734c0.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:31929/bludb\nDone.\n", "name": "stdout"}, {"output_type": "execute_result", "execution_count": 13, "data": {"text/plain": "[('Controlled (ocean)', 5),\n ('Failure', 3),\n ('Failure (drone ship)', 5),\n ('Failure (parachute)', 2),\n ('No attempt', 22),\n ('Precluded (drone ship)', 1),\n ('Success', 38),\n ('Success (drone ship)', 14),\n ('Success (ground pad)', 9),\n ('Uncontrolled (ocean)', 2)]", "text/html": "<table>\n    <tr>\n        <th>landing__outcome</th>\n        <th>2</th>\n    </tr>\n    <tr>\n        <td>Controlled (ocean)</td>\n        <td>5</td>\n    </tr>\n    <tr>\n        <td>Failure</td>\n        <td>3</td>\n    </tr>\n    <tr>\n        <td>Failure (drone ship)</td>\n        <td>5</td>\n    </tr>\n    <tr>\n        <td>Failure (parachute)</td>\n        <td>2</td>\n    </tr>\n    <tr>\n        <td>No attempt</td>\n        <td>22</td>\n    </tr>\n    <tr>\n        <td>Precluded (drone ship)</td>\n        <td>1</td>\n    </tr>\n    <tr>\n        <td>Success</td>\n        <td>38</td>\n    </tr>\n    <tr>\n        <td>Success (drone ship)</td>\n        <td>14</td>\n    </tr>\n    <tr>\n        <td>Success (ground pad)</td>\n        <td>9</td>\n    </tr>\n    <tr>\n        <td>Uncontrolled (ocean)</td>\n        <td>2</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 FROM SPACE_X WHERE payload_mass__kg_ = (SELECT MAX(payload_mass__kg_) FROM SPACE_X)", "execution_count": 14, "outputs": [{"output_type": "stream", "text": " * ibm_db_sa://bnd27390:***@55fbc997-9266-4331-afd3-888b05e734c0.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:31929/bludb\nDone.\n", "name": "stdout"}, {"output_type": "execute_result", "execution_count": 14, "data": {"text/plain": "[('F9 B5 B1048.4',),\n ('F9 B5 B1049.4',),\n ('F9 B5 B1051.3',),\n ('F9 B5 B1056.4',),\n ('F9 B5 B1048.5',),\n ('F9 B5 B1051.4',),\n ('F9 B5 B1049.5',),\n ('F9 B5 B1060.2',),\n ('F9 B5 B1058.3',),\n ('F9 B5 B1051.6',),\n ('F9 B5 B1060.3',),\n ('F9 B5 B1049.7',)]", "text/html": "<table>\n    <tr>\n        <th>booster_version</th>\n    </tr>\n    <tr>\n        <td>F9 B5 B1048.4</td>\n    </tr>\n    <tr>\n        <td>F9 B5 B1049.4</td>\n    </tr>\n    <tr>\n        <td>F9 B5 B1051.3</td>\n    </tr>\n    <tr>\n        <td>F9 B5 B1056.4</td>\n    </tr>\n    <tr>\n        <td>F9 B5 B1048.5</td>\n    </tr>\n    <tr>\n        <td>F9 B5 B1051.4</td>\n    </tr>\n    <tr>\n        <td>F9 B5 B1049.5</td>\n    </tr>\n    <tr>\n        <td>F9 B5 B1060.2</td>\n    </tr>\n    <tr>\n        <td>F9 B5 B1058.3</td>\n    </tr>\n    <tr>\n        <td>F9 B5 B1051.6</td>\n    </tr>\n    <tr>\n        <td>F9 B5 B1060.3</td>\n    </tr>\n    <tr>\n        <td>F9 B5 B1049.7</td>\n    </tr>\n</table>"}, "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 landing__outcome, booster_version, launch_site FROM SPACE_X WHERE landing__outcome = 'Success (drone ship)' AND YEAR(date) = 2015", "execution_count": 24, "outputs": [{"output_type": "stream", "text": " * ibm_db_sa://bnd27390:***@55fbc997-9266-4331-afd3-888b05e734c0.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:31929/bludb\nDone.\n", "name": "stdout"}, {"output_type": "execute_result", "execution_count": 24, "data": {"text/plain": "[]", "text/html": "<table>\n    <tr>\n        <th>landing__outcome</th>\n        <th>booster_version</th>\n        <th>launch_site</th>\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\nSELECT landing__outcome, COUNT(landing__outcome)\nFROM SPACE_X\nWHERE date >= '2010-06-04' \nAND date <= '2017-03-20'\nGROUP BY landing__outcome", "execution_count": 30, "outputs": [{"output_type": "stream", "text": " * ibm_db_sa://bnd27390:***@55fbc997-9266-4331-afd3-888b05e734c0.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:31929/bludb\nDone.\n", "name": "stdout"}, {"output_type": "execute_result", "execution_count": 30, "data": {"text/plain": "[('Controlled (ocean)', 3),\n ('Failure (drone ship)', 5),\n ('Failure (parachute)', 2),\n ('No attempt', 10),\n ('Precluded (drone ship)', 1),\n ('Success (drone ship)', 5),\n ('Success (ground pad)', 3),\n ('Uncontrolled (ocean)', 2)]", "text/html": "<table>\n    <tr>\n        <th>landing__outcome</th>\n        <th>2</th>\n    </tr>\n    <tr>\n        <td>Controlled (ocean)</td>\n        <td>3</td>\n    </tr>\n    <tr>\n        <td>Failure (drone ship)</td>\n        <td>5</td>\n    </tr>\n    <tr>\n        <td>Failure (parachute)</td>\n        <td>2</td>\n    </tr>\n    <tr>\n        <td>No attempt</td>\n        <td>10</td>\n    </tr>\n    <tr>\n        <td>Precluded (drone ship)</td>\n        <td>1</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>3</td>\n    </tr>\n    <tr>\n        <td>Uncontrolled (ocean)</td>\n        <td>2</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-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"}, {"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.10", "language": "python"}, "language_info": {"name": "python", "version": "3.10.6", "mimetype": "text/x-python", "codemirror_mode": {"name": "ipython", "version": 3}, "pygments_lexer": "ipython3", "nbconvert_exporter": "python", "file_extension": ".py"}}, "nbformat": 4, "nbformat_minor": 4}