{ "cells": [ { "cell_type": "markdown", "metadata": { "id": "w1YGiykrNtdS" }, "source": [ "

\n", " \n", " \"Skills\n", " \n", "

\n", "\n", "

Assignment: SQL Notebook for Peer Assignment

\n", "\n", "Estimated time needed: **60** minutes.\n", "\n", "## Introduction\n", "\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": "d1tyTG7gNtdW" }, "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", "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": "tX_l4RMINtdX" }, "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", "Spacex DataSet\n" ] }, { "cell_type": "markdown", "metadata": { "id": "vbNpE34zNtdX" }, "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", "\n", "\n", "Now 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", "\n", "1. Change the Date Format by manually typing DD-MM-YYYY and timestamp format as DD-MM-YYYY HH\\:MM:SS\n", "\n", "2. Change the PAYLOAD_MASS\\_\\_KG\\_ datatype to INTEGER.\n", "\n", "\n" ] }, { "cell_type": "markdown", "metadata": { "id": "tIW3H1JjNtdY" }, "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 link 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", "\n", "\n", "* Once done select the schema andload the file.\n", "\n", " \n" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "xv9m4QQKNtdZ" }, "outputs": [], "source": [ "!pip install sqlalchemy==1.3.9\n" ] }, { "cell_type": "markdown", "metadata": { "id": "0gXX3srdNtda" }, "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", "execution_count": 1, "metadata": { "id": "2yv8wJ1INtdb" }, "outputs": [], "source": [ "%load_ext sql" ] }, { "cell_type": "code", "execution_count": 2, "metadata": { "id": "RiARoe8bNtdb" }, "outputs": [], "source": [ "import csv, sqlite3\n", "\n", "con = sqlite3.connect(\"my_data1.db\")\n", "cur = con.cursor()" ] }, { "cell_type": "code", "execution_count": 3, "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "id": "aPCWc-v9Ntdc", "outputId": "211a41cf-538e-4697-898f-616b18b802b1" }, "outputs": [ { "output_type": "stream", "name": "stdout", "text": [ "\u001b[K |████████████████████████████████| 9.5 MB 8.2 MB/s \n", "\u001b[?25h" ] } ], "source": [ "!pip install -q pandas==1.1.5" ] }, { "cell_type": "code", "execution_count": 3, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 35 }, "id": "4mR0SCpTNtdc", "outputId": "9ee4c1c1-265c-40a1-dd52-7a3ee5b1e82e" }, "outputs": [ { "output_type": "execute_result", "data": { "text/plain": [ "'Connected: @my_data1.db'" ], "application/vnd.google.colaboratory.intrinsic+json": { "type": "string" } }, "metadata": {}, "execution_count": 3 } ], "source": [ "%sql sqlite:///my_data1.db" ] }, { "cell_type": "code", "execution_count": 4, "metadata": { "id": "WxR32qmiNtdd" }, "outputs": [], "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" ] }, { "cell_type": "code", "source": [ "df.to_sql(\"SPACEXTBL\", con, if_exists='replace', index=False,method=\"multi\")" ], "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "id": "YbuU8btUOS6N", "outputId": "c67c9bec-b8f3-4a16-b78a-d02ef1ec5396" }, "execution_count": 6, "outputs": [ { "output_type": "stream", "name": "stderr", "text": [ "/usr/local/lib/python3.7/dist-packages/pandas/core/generic.py:2615: UserWarning: The spaces in these column names will not be changed. In pandas versions < 0.14, spaces were converted to underscores.\n", " method=method,\n" ] } ] }, { "cell_type": "code", "execution_count": 5, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 354 }, "id": "SOYaq1JUNtdd", "outputId": "98690e7d-94c3-4a6f-bb0d-884beb135ef5" }, "outputs": [ { "output_type": "execute_result", "data": { "text/plain": [ " Date Time (UTC) Booster_Version Launch_Site \\\n", "0 04-06-2010 18:45:00 F9 v1.0 B0003 CCAFS LC-40 \n", "1 08-12-2010 15:43:00 F9 v1.0 B0004 CCAFS LC-40 \n", "2 22-05-2012 07:44:00 F9 v1.0 B0005 CCAFS LC-40 \n", "3 08-10-2012 00:35:00 F9 v1.0 B0006 CCAFS LC-40 \n", "4 01-03-2013 15:10:00 F9 v1.0 B0007 CCAFS LC-40 \n", "\n", " Payload PAYLOAD_MASS__KG_ \\\n", "0 Dragon Spacecraft Qualification Unit 0 \n", "1 Dragon demo flight C1, two CubeSats, barrel of... 0 \n", "2 Dragon demo flight C2 525 \n", "3 SpaceX CRS-1 500 \n", "4 SpaceX CRS-2 677 \n", "\n", " Orbit Customer Mission_Outcome Landing _Outcome \n", "0 LEO SpaceX Success Failure (parachute) \n", "1 LEO (ISS) NASA (COTS) NRO Success Failure (parachute) \n", "2 LEO (ISS) NASA (COTS) Success No attempt \n", "3 LEO (ISS) NASA (CRS) Success No attempt \n", "4 LEO (ISS) NASA (CRS) Success No attempt " ], "text/html": [ "\n", "
\n", "
\n", "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
DateTime (UTC)Booster_VersionLaunch_SitePayloadPAYLOAD_MASS__KG_OrbitCustomerMission_OutcomeLanding _Outcome
004-06-201018:45:00F9 v1.0 B0003CCAFS LC-40Dragon Spacecraft Qualification Unit0LEOSpaceXSuccessFailure (parachute)
108-12-201015:43:00F9 v1.0 B0004CCAFS LC-40Dragon demo flight C1, two CubeSats, barrel of...0LEO (ISS)NASA (COTS) NROSuccessFailure (parachute)
222-05-201207:44:00F9 v1.0 B0005CCAFS LC-40Dragon demo flight C2525LEO (ISS)NASA (COTS)SuccessNo attempt
308-10-201200:35:00F9 v1.0 B0006CCAFS LC-40SpaceX CRS-1500LEO (ISS)NASA (CRS)SuccessNo attempt
401-03-201315:10:00F9 v1.0 B0007CCAFS LC-40SpaceX CRS-2677LEO (ISS)NASA (CRS)SuccessNo attempt
\n", "
\n", " \n", " \n", " \n", "\n", " \n", "
\n", "
\n", " " ] }, "metadata": {}, "execution_count": 5 } ], "source": [ "df.head()" ] }, { "cell_type": "code", "execution_count": 7, "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "id": "AAYJr2YwNtde", "outputId": "17cc5a12-bcc1-403f-ae66-9d140b8ff8e0" }, "outputs": [ { "output_type": "execute_result", "data": { "text/plain": [ "Success 38\n", "No attempt 21\n", "Success (drone ship) 14\n", "Success (ground pad) 9\n", "Failure (drone ship) 5\n", "Controlled (ocean) 5\n", "Failure 3\n", "Failure (parachute) 2\n", "Uncontrolled (ocean) 2\n", "Precluded (drone ship) 1\n", "No attempt 1\n", "Name: Landing _Outcome, dtype: int64" ] }, "metadata": {}, "execution_count": 7 } ], "source": [ "df[\"Landing _Outcome\"].value_counts()" ] }, { "cell_type": "markdown", "metadata": { "id": "qwE2CY4LNtde" }, "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", "##### Display the names of the unique launch sites in the space mission\n" ] }, { "cell_type": "code", "execution_count": 8, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 162 }, "id": "Hz5Sj1MwNtde", "outputId": "a4afaa0a-927a-433f-dd44-4538a57d00d6" }, "outputs": [ { "output_type": "stream", "name": "stdout", "text": [ " * sqlite:///my_data1.db\n", "Done.\n" ] }, { "output_type": "execute_result", "data": { "text/plain": [ "[('CCAFS LC-40',), ('VAFB SLC-4E',), ('KSC LC-39A',), ('CCAFS SLC-40',)]" ], "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Launch_Site
CCAFS LC-40
VAFB SLC-4E
KSC LC-39A
CCAFS SLC-40
" ] }, "metadata": {}, "execution_count": 8 } ], "source": [ "%sql select distinct Launch_Site from SPACEXTBL " ] }, { "cell_type": "markdown", "metadata": { "id": "FYNP0vmNNtdf" }, "source": [ "### Task 2\n", "\n", "##### Display 5 records where launch sites begin with the string 'CCA'\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "9CsK7U5fNtdf", "outputId": "c65b7607-4863-4284-e105-469688079b39" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite:///my_data1.db\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
DateTime (UTC)Booster_VersionLaunch_SitePayloadPAYLOAD_MASS__KG_OrbitCustomerMission_OutcomeLanding _Outcome
04-06-201018:45:00F9 v1.0 B0003CCAFS LC-40Dragon Spacecraft Qualification Unit0LEOSpaceXSuccessFailure (parachute)
08-12-201015:43:00F9 v1.0 B0004CCAFS LC-40Dragon demo flight C1, two CubeSats, barrel of Brouere cheese0LEO (ISS)NASA (COTS) NROSuccessFailure (parachute)
22-05-201207:44:00F9 v1.0 B0005CCAFS LC-40Dragon demo flight C2525LEO (ISS)NASA (COTS)SuccessNo attempt
08-10-201200:35:00F9 v1.0 B0006CCAFS LC-40SpaceX CRS-1500LEO (ISS)NASA (CRS)SuccessNo attempt
01-03-201315:10:00F9 v1.0 B0007CCAFS LC-40SpaceX CRS-2677LEO (ISS)NASA (CRS)SuccessNo attempt
" ], "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": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql select * from SPACEXTBL where Launch_Site like 'CCA%' LIMIT 5" ] }, { "cell_type": "markdown", "metadata": { "id": "ergLF7mHNtdf" }, "source": [ "### Task 3\n", "\n", "##### Display the total payload mass carried by boosters launched by NASA (CRS)\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "DXgDsLEDNtdg", "outputId": "7f6c1b14-d138-4aa0-ee08-e163d520fb18" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite:///my_data1.db\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
sum(PAYLOAD_MASS__KG_)
45596
" ], "text/plain": [ "[(45596,)]" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql select sum(PAYLOAD_MASS__KG_) from SPACEXTBL where Customer='NASA (CRS)'" ] }, { "cell_type": "code", "source": [ "%sql select sum(PAYLOAD_MASS__KG_) from SPACEXTBL where Customer like 'NASA%'" ], "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 97 }, "id": "SMh0OQj8Ok59", "outputId": "3df2b0ea-acf0-4d83-df98-fae82fa4c50e" }, "execution_count": 9, "outputs": [ { "output_type": "stream", "name": "stdout", "text": [ " * sqlite:///my_data1.db\n", "Done.\n" ] }, { "output_type": "execute_result", "data": { "text/plain": [ "[(99980,)]" ], "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
sum(PAYLOAD_MASS__KG_)
99980
" ] }, "metadata": {}, "execution_count": 9 } ] }, { "cell_type": "markdown", "metadata": { "id": "z0goAeNzNtdg" }, "source": [ "### Task 4\n", "\n", "##### Display average payload mass carried by booster version F9 v1.1\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "DkzKQxGoNtdg", "outputId": "b50e024d-457a-4b85-f9ca-defbef5e42ad" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite:///my_data1.db\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
avg(PAYLOAD_MASS__KG_)
2534.6666666666665
" ], "text/plain": [ "[(2534.6666666666665,)]" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql select avg(PAYLOAD_MASS__KG_) from SPACEXTBL where Booster_Version like 'F9 v1.1%'" ] }, { "cell_type": "markdown", "metadata": { "id": "Y5V12mrQNtdg" }, "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": null, "metadata": { "id": "Fx3mkW3PNtdh", "outputId": "2ed69caa-f21f-4d20-ab56-42ce5c91c6d8" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite:///my_data1.db\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
min(Date)
01-05-2017
" ], "text/plain": [ "[('01-05-2017',)]" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql select min(Date) from SPACEXTBL where \"Landing _Outcome\" = \"Success (ground pad)\"" ] }, { "cell_type": "markdown", "metadata": { "id": "VCX2UwZJNtdh" }, "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": null, "metadata": { "id": "dbBEkc2QNtdh", "outputId": "f8e7a0fb-b60e-49b3-dd7a-ec2412bd6080" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite:///my_data1.db\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Booster_Version
F9 FT B1022
F9 FT B1026
F9 FT B1021.2
F9 FT B1031.2
" ], "text/plain": [ "[('F9 FT B1022',), ('F9 FT B1026',), ('F9 FT B1021.2',), ('F9 FT B1031.2',)]" ] }, "execution_count": 23, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "\n", "select Booster_Version from SPACEXTBL \n", "where \"Landing _Outcome\" = \"Success (drone ship)\" \n", " and PAYLOAD_MASS__KG_>4000 \n", " and PAYLOAD_MASS__KG_ < 6000" ] }, { "cell_type": "markdown", "metadata": { "id": "0240sD01Ntdi" }, "source": [ "### Task 7\n", "\n", "##### List the total number of successful and failure mission outcomes\n" ] }, { "cell_type": "code", "source": [ "%%sql \n", "select distinct \"Mission_Outcome\" from SPACEXTBL" ], "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 162 }, "id": "NxvKGNGnRWlU", "outputId": "5f1f7b8c-746c-4b2e-ed42-15389841967a" }, "execution_count": 13, "outputs": [ { "output_type": "stream", "name": "stdout", "text": [ " * sqlite:///my_data1.db\n", "Done.\n" ] }, { "output_type": "execute_result", "data": { "text/plain": [ "[('Success',),\n", " ('Failure (in flight)',),\n", " ('Success (payload status unclear)',),\n", " ('Success ',)]" ], "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Mission_Outcome
Success
Failure (in flight)
Success (payload status unclear)
Success
" ] }, "metadata": {}, "execution_count": 13 } ] }, { "cell_type": "code", "execution_count": 10, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 97 }, "id": "jW501zglNtdi", "outputId": "31fb2db2-28ae-4c32-caa8-d5fec66e73fd" }, "outputs": [ { "output_type": "stream", "name": "stdout", "text": [ " * sqlite:///my_data1.db\n", "Done.\n" ] }, { "output_type": "execute_result", "data": { "text/plain": [ "[(100,)]" ], "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
count(*)
100
" ] }, "metadata": {}, "execution_count": 10 } ], "source": [ "%%sql \n", "\n", "select count(*) from SPACEXTBL \n", "where \"Mission_Outcome\" like \"Success%\" " ] }, { "cell_type": "code", "execution_count": 11, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 97 }, "id": "eoXqBIGwNtdi", "outputId": "efa390c9-f2c1-4084-9541-7007db523e23" }, "outputs": [ { "output_type": "stream", "name": "stdout", "text": [ " * sqlite:///my_data1.db\n", "Done.\n" ] }, { "output_type": "execute_result", "data": { "text/plain": [ "[(1,)]" ], "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
count(*)
1
" ] }, "metadata": {}, "execution_count": 11 } ], "source": [ "%%sql \n", "\n", "select count(*) from SPACEXTBL \n", "where \"Mission_Outcome\" like \"Failure%\" " ] }, { "cell_type": "markdown", "metadata": { "id": "a09hIhraNtdi" }, "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": null, "metadata": { "id": "6x_BbDceNtdi", "outputId": "b24cce68-a31f-46eb-9c5c-673028fdc052" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite:///my_data1.db\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Booster_Version
F9 B5 B1048.4
F9 B5 B1049.4
F9 B5 B1051.3
F9 B5 B1056.4
F9 B5 B1048.5
F9 B5 B1051.4
F9 B5 B1049.5
F9 B5 B1060.2
F9 B5 B1058.3
F9 B5 B1051.6
F9 B5 B1060.3
F9 B5 B1049.7
" ], "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 ',)]" ] }, "execution_count": 26, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "\n", "select Booster_Version from SPACEXTBL \n", "where PAYLOAD_MASS__KG_ = (select max(PAYLOAD_MASS__KG_) from SPACEXTBL)" ] }, { "cell_type": "markdown", "metadata": { "id": "lTDxjxgcNtdj" }, "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": 14, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 119 }, "id": "2Z-hfzlGNtdj", "outputId": "778580cd-854c-40ce-9f48-643dc1e1a16b" }, "outputs": [ { "output_type": "stream", "name": "stdout", "text": [ " * sqlite:///my_data1.db\n", "Done.\n" ] }, { "output_type": "execute_result", "data": { "text/plain": [ "[('01', 'F9 v1.1 B1012', 'CCAFS LC-40'),\n", " ('04', 'F9 v1.1 B1015', 'CCAFS LC-40')]" ], "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
MonthBooster_VersionLaunch_Site
01F9 v1.1 B1012CCAFS LC-40
04F9 v1.1 B1015CCAFS LC-40
" ] }, "metadata": {}, "execution_count": 14 } ], "source": [ "%%sql \n", "\n", "select substr(Date, 4, 2) as Month, Booster_Version, Launch_Site from SPACEXTBL \n", "where substr(Date,7,4)='2015' and \"Landing _Outcome\" = \"Failure (drone ship)\"" ] }, { "cell_type": "markdown", "metadata": { "id": "tFZx_pVJNtdj" }, "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": 15, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 268 }, "id": "K_UUxyDrNtdj", "outputId": "045e63bd-e6c2-4ab8-ea1a-16ded5d58739" }, "outputs": [ { "output_type": "stream", "name": "stdout", "text": [ " * sqlite:///my_data1.db\n", "Done.\n" ] }, { "output_type": "execute_result", "data": { "text/plain": [ "[('Success', 20),\n", " ('No attempt', 10),\n", " ('Success (drone ship)', 8),\n", " ('Success (ground pad)', 6),\n", " ('Failure (drone ship)', 4),\n", " ('Controlled (ocean)', 3),\n", " ('Failure', 3),\n", " ('Failure (parachute)', 2),\n", " ('No attempt ', 1)]" ], "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Landing _Outcomelandings
Success20
No attempt10
Success (drone ship)8
Success (ground pad)6
Failure (drone ship)4
Controlled (ocean)3
Failure3
Failure (parachute)2
No attempt 1
" ] }, "metadata": {}, "execution_count": 15 } ], "source": [ "%%sql \n", "\n", "select \"Landing _Outcome\", \n", " count(\"Landing _Outcome\") as landings \n", "from SPACEXTBL \n", "where Date >= \"04-06-2010\" and Date <= \"20-03-2017\"\n", "group by \"Landing _Outcome\"\n", "order by landings desc" ] }, { "cell_type": "markdown", "metadata": { "id": "uJZmPQ80Ntdj" }, "source": [ "### Reference Links\n", "\n", "* Hands-on Lab : String Patterns, Sorting and Grouping\n", "\n", "* Hands-on Lab: Built-in functions\n", "\n", "* Hands-on Lab : Sub-queries and Nested SELECT Statements\n", "\n", "* Hands-on Tutorial: Accessing Databases with SQL magic\n", "\n", "* Hands-on Lab: Analyzing a real World Data Set\n" ] }, { "cell_type": "markdown", "metadata": { "id": "ZjDuvBAyNtdk" }, "source": [ "## Author(s)\n", "\n", "

Lakshmi Holla

\n" ] }, { "cell_type": "markdown", "metadata": { "id": "2OFJ8-AqNtdk" }, "source": [ "## Other Contributors\n", "\n", "

Rav Ahuja

\n" ] }, { "cell_type": "markdown", "metadata": { "id": "ttXW8FGINtdk" }, "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": { "id": "xNJzSW3uNtdk" }, "source": [ "##

© IBM Corporation 2021. All rights reserved.

\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" }, "colab": { "name": "4-jupyter-labs-eda-sql-coursera_sqllite.ipynb", "provenance": [], "collapsed_sections": [] } }, "nbformat": 4, "nbformat_minor": 0 }