Skip to content

Instantly share code, notes, and snippets.

@brt-h

brt-h/Hands-on Lab Complete the EDA with SQL.ipynb

Created September 23, 2021 19:58
Show Gist options
  • Save brt-h/013e775e28bf43411f53514195183324 to your computer and use it in GitHub Desktop.
Save brt-h/013e775e28bf43411f53514195183324 to your computer and use it in GitHub Desktop.
Download ZIP
Applied Data Science Capstone
Display the source blob
Display the rendered blob
Raw
Hands-on Lab Complete the EDA with SQL.ipynb
{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"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",
"\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": {},
"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": {},
"source": [
"### Download the datasets\n",
"\n",
"This assignment requires you to load the spacex dataset.\n",
"\n",
"In many cases the dataset to be analyzed is available as a .CSV (comma separated values) file, perhaps on the internet. Click on the link below to download and save the dataset (.CSV file):\n",
"\n",
"<a href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/module_2/data/Spacex.csv?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"
]
},
{
"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",
"\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",
"<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-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"
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {
"scrolled": true
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Requirement already satisfied: sqlalchemy==1.3.9 in /home/jupyterlab/conda/envs/python/lib/python3.6/site-packages (1.3.9)\n",
"Requirement already satisfied: ibm_db in /home/jupyterlab/conda/envs/python/lib/python3.6/site-packages (2.0.8a0)\n",
"Requirement already satisfied: ibm_db_sa in /home/jupyterlab/conda/envs/python/lib/python3.6/site-packages (0.3.3)\n",
"Requirement already satisfied: sqlalchemy>=0.7.3 in /home/jupyterlab/conda/envs/python/lib/python3.6/site-packages (from ibm_db_sa) (1.3.9)\n",
"Requirement already satisfied: ipython-sql in /home/jupyterlab/conda/envs/python/lib/python3.6/site-packages (0.3.9)\n",
"Requirement already satisfied: ipython>=1.0 in /home/jupyterlab/conda/envs/python/lib/python3.6/site-packages (from ipython-sql) (7.16.1)\n",
"Requirement already satisfied: sqlparse in /home/jupyterlab/conda/envs/python/lib/python3.6/site-packages (from ipython-sql) (0.4.1)\n",
"Requirement already satisfied: prettytable in /home/jupyterlab/conda/envs/python/lib/python3.6/site-packages (from ipython-sql) (2.1.0)\n",
"Requirement already satisfied: ipython-genutils>=0.1.0 in /home/jupyterlab/conda/envs/python/lib/python3.6/site-packages (from ipython-sql) (0.2.0)\n",
"Requirement already satisfied: sqlalchemy>=0.6.7 in /home/jupyterlab/conda/envs/python/lib/python3.6/site-packages (from ipython-sql) (1.3.9)\n",
"Requirement already satisfied: six in /home/jupyterlab/conda/envs/python/lib/python3.6/site-packages (from ipython-sql) (1.15.0)\n",
"Requirement already satisfied: decorator in /home/jupyterlab/conda/envs/python/lib/python3.6/site-packages (from ipython>=1.0->ipython-sql) (4.4.2)\n",
"Requirement already satisfied: backcall in /home/jupyterlab/conda/envs/python/lib/python3.6/site-packages (from ipython>=1.0->ipython-sql) (0.2.0)\n",
"Requirement already satisfied: pexpect; sys_platform != \"win32\" in /home/jupyterlab/conda/envs/python/lib/python3.6/site-packages (from ipython>=1.0->ipython-sql) (4.8.0)\n",
"Requirement already satisfied: pygments in /home/jupyterlab/conda/envs/python/lib/python3.6/site-packages (from ipython>=1.0->ipython-sql) (2.9.0)\n",
"Requirement already satisfied: traitlets>=4.2 in /home/jupyterlab/conda/envs/python/lib/python3.6/site-packages (from ipython>=1.0->ipython-sql) (4.3.3)\n",
"Requirement already satisfied: jedi>=0.10 in /home/jupyterlab/conda/envs/python/lib/python3.6/site-packages (from ipython>=1.0->ipython-sql) (0.17.2)\n",
"Requirement already satisfied: pickleshare in /home/jupyterlab/conda/envs/python/lib/python3.6/site-packages (from ipython>=1.0->ipython-sql) (0.7.5)\n",
"Requirement already satisfied: setuptools>=18.5 in /home/jupyterlab/conda/envs/python/lib/python3.6/site-packages (from ipython>=1.0->ipython-sql) (49.6.0.post20210108)\n",
"Requirement already satisfied: prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0 in /home/jupyterlab/conda/envs/python/lib/python3.6/site-packages (from ipython>=1.0->ipython-sql) (3.0.19)\n",
"Requirement already satisfied: wcwidth in /home/jupyterlab/conda/envs/python/lib/python3.6/site-packages (from prettytable->ipython-sql) (0.2.5)\n",
"Requirement already satisfied: importlib-metadata; python_version < \"3.8\" in /home/jupyterlab/conda/envs/python/lib/python3.6/site-packages (from prettytable->ipython-sql) (4.6.1)\n",
"Requirement already satisfied: ptyprocess>=0.5 in /home/jupyterlab/conda/envs/python/lib/python3.6/site-packages (from pexpect; sys_platform != \"win32\"->ipython>=1.0->ipython-sql) (0.7.0)\n",
"Requirement already satisfied: parso<0.8.0,>=0.7.0 in /home/jupyterlab/conda/envs/python/lib/python3.6/site-packages (from jedi>=0.10->ipython>=1.0->ipython-sql) (0.7.1)\n",
"Requirement already satisfied: zipp>=0.5 in /home/jupyterlab/conda/envs/python/lib/python3.6/site-packages (from importlib-metadata; python_version < \"3.8\"->prettytable->ipython-sql) (3.5.0)\n",
"Requirement already satisfied: typing-extensions>=3.6.4; python_version < \"3.8\" in /home/jupyterlab/conda/envs/python/lib/python3.6/site-packages (from importlib-metadata; python_version < \"3.8\"->prettytable->ipython-sql) (3.10.0.0)\n"
]
}
],
"source": [
"!pip install sqlalchemy==1.3.9\n",
"!pip install ibm_db\n",
"!pip install ibm_db_sa\n",
"!pip install ipython-sql"
]
},
{
"cell_type": "markdown",
"metadata": {},
"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": 2,
"metadata": {},
"outputs": [],
"source": [
"%load_ext sql"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"**DB2 magic in case of old UI service credentials.**\n",
"\n",
"In 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",
"\n",
"in 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"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"'Connected: xcg80731@bludb'"
]
},
"execution_count": 3,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%sql ibm_db_sa://xcg80731:Z0Aq5Vwa8fZKzZ60@ba99a9e6-d59e-4883-8fc0-d6a8c9f7a08f.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:31321/bludb?security=SSL"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Tasks\n",
"\n",
"Now 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"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * ibm_db_sa://xcg80731:***@ba99a9e6-d59e-4883-8fc0-d6a8c9f7a08f.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:31321/bludb\n",
"Done.\n"
]
},
{
"data": {
"text/html": [
"<table>\n",
" <thead>\n",
" <tr>\n",
" <th>tabschema</th>\n",
" <th>tabname</th>\n",
" <th>create_time</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <td>XCG80731</td>\n",
" <td>SPACEXTBL</td>\n",
" <td>2021-09-16 23:15:07.510732</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>"
],
"text/plain": [
"[('XCG80731', 'SPACEXTBL', datetime.datetime(2021, 9, 16, 23, 15, 7, 510732))]"
]
},
"execution_count": 4,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%sql SELECT TABSCHEMA, TABNAME, CREATE_TIME FROM SYSCAT.TABLES WHERE TABSCHEMA='XCG80731';"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * ibm_db_sa://xcg80731:***@ba99a9e6-d59e-4883-8fc0-d6a8c9f7a08f.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:31321/bludb\n",
"Done.\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": 5,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"SELECT DISTINCT LAUNCH_SITE\n",
"FROM SPACEXTBL;"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * ibm_db_sa://xcg80731:***@ba99a9e6-d59e-4883-8fc0-d6a8c9f7a08f.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:31321/bludb\n",
"Done.\n"
]
},
{
"data": {
"text/html": [
"<table>\n",
" <thead>\n",
" <tr>\n",
" <th>launch_site</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <td>26</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>"
],
"text/plain": [
"[(26,)]"
]
},
"execution_count": 6,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"SELECT COUNT(*) LAUNCH_SITE\n",
"FROM SPACEXTBL\n",
"WHERE LAUNCH_SITE = 'CCAFS LC-40';"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * ibm_db_sa://xcg80731:***@ba99a9e6-d59e-4883-8fc0-d6a8c9f7a08f.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:31321/bludb\n",
"Done.\n"
]
},
{
"data": {
"text/html": [
"<table>\n",
" <thead>\n",
" <tr>\n",
" <th>launch_site</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <td>34</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>"
],
"text/plain": [
"[(34,)]"
]
},
"execution_count": 7,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"SELECT COUNT(*) LAUNCH_SITE\n",
"FROM SPACEXTBL\n",
"WHERE LAUNCH_SITE = 'CCAFS SLC-40';"
]
},
{
"cell_type": "code",
"execution_count": 8,
"metadata": {},
"outputs": [],
"source": [
"# It seems the provided dataset is flawed,\n",
"# launch sites 'CCAFS SLC-40' and 'CCAFS LC-40' are the same place"
]
},
{
"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": 9,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * ibm_db_sa://xcg80731:***@ba99a9e6-d59e-4883-8fc0-d6a8c9f7a08f.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:31321/bludb\n",
"Done.\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 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",
" </tbody>\n",
"</table>"
],
"text/plain": [
"[('CCAFS LC-40',),\n",
" ('CCAFS LC-40',),\n",
" ('CCAFS LC-40',),\n",
" ('CCAFS LC-40',),\n",
" ('CCAFS LC-40',)]"
]
},
"execution_count": 9,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"SELECT LAUNCH_SITE\n",
"FROM SPACEXTBL\n",
"WHERE LAUNCH_SITE LIKE 'CCA%'\n",
"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": 10,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * ibm_db_sa://xcg80731:***@ba99a9e6-d59e-4883-8fc0-d6a8c9f7a08f.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:31321/bludb\n",
"Done.\n"
]
},
{
"data": {
"text/html": [
"<table>\n",
" <thead>\n",
" <tr>\n",
" <th>1</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <td>45596</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>"
],
"text/plain": [
"[(45596,)]"
]
},
"execution_count": 10,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"SELECT SUM(PAYLOAD_MASS__KG_)\n",
"FROM SPACEXTBL\n",
"WHERE Customer = 'NASA (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": 11,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * ibm_db_sa://xcg80731:***@ba99a9e6-d59e-4883-8fc0-d6a8c9f7a08f.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:31321/bludb\n",
"Done.\n"
]
},
{
"data": {
"text/html": [
"<table>\n",
" <thead>\n",
" <tr>\n",
" <th>1</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <td>340</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>"
],
"text/plain": [
"[(340,)]"
]
},
"execution_count": 11,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"SELECT AVG(PAYLOAD_MASS__KG_)\n",
"FROM SPACEXTBL\n",
"WHERE Booster_Version LIKE 'F9 v1.0%';"
]
},
{
"cell_type": "markdown",
"metadata": {},
"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"
]
},
{
"cell_type": "code",
"execution_count": 30,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * ibm_db_sa://xcg80731:***@ba99a9e6-d59e-4883-8fc0-d6a8c9f7a08f.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:31321/bludb\n",
"Done.\n"
]
},
{
"data": {
"text/html": [
"<table>\n",
" <thead>\n",
" <tr>\n",
" <th>1</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <td>2015-12-22</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>"
],
"text/plain": [
"[(datetime.date(2015, 12, 22),)]"
]
},
"execution_count": 30,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"SELECT MIN(Date)\n",
"FROM SPACEXTBL\n",
"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": 33,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * ibm_db_sa://xcg80731:***@ba99a9e6-d59e-4883-8fc0-d6a8c9f7a08f.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:31321/bludb\n",
"Done.\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 B1021.1</td>\n",
" </tr>\n",
" <tr>\n",
" <td>F9 FT B1023.1</td>\n",
" </tr>\n",
" <tr>\n",
" <td>F9 FT B1029.2</td>\n",
" </tr>\n",
" <tr>\n",
" <td>F9 FT B1038.1</td>\n",
" </tr>\n",
" <tr>\n",
" <td>F9 B4 B1042.1</td>\n",
" </tr>\n",
" <tr>\n",
" <td>F9 B4 B1045.1</td>\n",
" </tr>\n",
" <tr>\n",
" <td>F9 B5 B1046.1</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>"
],
"text/plain": [
"[('F9 FT B1021.1',),\n",
" ('F9 FT B1023.1',),\n",
" ('F9 FT B1029.2',),\n",
" ('F9 FT B1038.1',),\n",
" ('F9 B4 B1042.1',),\n",
" ('F9 B4 B1045.1',),\n",
" ('F9 B5 B1046.1',)]"
]
},
"execution_count": 33,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"SELECT BOOSTER_VERSION\n",
"FROM SPACEXTBL\n",
"WHERE LANDING__OUTCOME = 'Success (drone ship)'\n",
" AND 4000 < PAYLOAD_MASS__KG_ < 6000;"
]
},
{
"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": 44,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * ibm_db_sa://xcg80731:***@ba99a9e6-d59e-4883-8fc0-d6a8c9f7a08f.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:31321/bludb\n",
"Done.\n"
]
},
{
"data": {
"text/html": [
"<table>\n",
" <thead>\n",
" <tr>\n",
" <th>mission_outcome</th>\n",
" <th>total_number</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>99</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', 99),\n",
" ('Success (payload status unclear)', 1)]"
]
},
"execution_count": 44,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"SELECT MISSION_OUTCOME, COUNT(MISSION_OUTCOME) AS TOTAL_NUMBER\n",
"FROM SPACEXTBL\n",
"GROUP 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": 57,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * ibm_db_sa://xcg80731:***@ba99a9e6-d59e-4883-8fc0-d6a8c9f7a08f.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:31321/bludb\n",
"Done.\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": 57,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"SELECT DISTINCT BOOSTER_VERSION\n",
"FROM SPACEXTBL\n",
"WHERE PAYLOAD_MASS__KG_ = (\n",
" SELECT MAX(PAYLOAD_MASS__KG_)\n",
" FROM SPACEXTBL);"
]
},
{
"cell_type": "markdown",
"metadata": {},
"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"
]
},
{
"cell_type": "code",
"execution_count": 62,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * ibm_db_sa://xcg80731:***@ba99a9e6-d59e-4883-8fc0-d6a8c9f7a08f.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:31321/bludb\n",
"Done.\n"
]
},
{
"data": {
"text/html": [
"<table>\n",
" <thead>\n",
" <tr>\n",
" <th>landing__outcome</th>\n",
" <th>booster_version</th>\n",
" <th>launch_site</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <td>Failure (drone ship)</td>\n",
" <td>F9 v1.1 B1012</td>\n",
" <td>CCAFS LC-40</td>\n",
" </tr>\n",
" <tr>\n",
" <td>Failure (drone ship)</td>\n",
" <td>F9 v1.1 B1015</td>\n",
" <td>CCAFS LC-40</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>"
],
"text/plain": [
"[('Failure (drone ship)', 'F9 v1.1 B1012', 'CCAFS LC-40'),\n",
" ('Failure (drone ship)', 'F9 v1.1 B1015', 'CCAFS LC-40')]"
]
},
"execution_count": 62,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"SELECT LANDING__OUTCOME, BOOSTER_VERSION, LAUNCH_SITE\n",
"FROM SPACEXTBL\n",
"WHERE Landing__Outcome = 'Failure (drone ship)'\n",
" AND YEAR(DATE) = 2015;"
]
},
{
"cell_type": "markdown",
"metadata": {},
"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"
]
},
{
"cell_type": "code",
"execution_count": 70,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * ibm_db_sa://xcg80731:***@ba99a9e6-d59e-4883-8fc0-d6a8c9f7a08f.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:31321/bludb\n",
"Done.\n"
]
},
{
"data": {
"text/html": [
"<table>\n",
" <thead>\n",
" <tr>\n",
" <th>landing__outcome</th>\n",
" <th>total_number</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <td>No attempt</td>\n",
" <td>10</td>\n",
" </tr>\n",
" <tr>\n",
" <td>Failure (drone ship)</td>\n",
" <td>5</td>\n",
" </tr>\n",
" <tr>\n",
" <td>Success (drone ship)</td>\n",
" <td>5</td>\n",
" </tr>\n",
" <tr>\n",
" <td>Controlled (ocean)</td>\n",
" <td>3</td>\n",
" </tr>\n",
" <tr>\n",
" <td>Success (ground pad)</td>\n",
" <td>3</td>\n",
" </tr>\n",
" <tr>\n",
" <td>Failure (parachute)</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <td>Uncontrolled (ocean)</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <td>Precluded (drone ship)</td>\n",
" <td>1</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>"
],
"text/plain": [
"[('No attempt', 10),\n",
" ('Failure (drone ship)', 5),\n",
" ('Success (drone ship)', 5),\n",
" ('Controlled (ocean)', 3),\n",
" ('Success (ground pad)', 3),\n",
" ('Failure (parachute)', 2),\n",
" ('Uncontrolled (ocean)', 2),\n",
" ('Precluded (drone ship)', 1)]"
]
},
"execution_count": 70,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"SELECT LANDING__OUTCOME, COUNT(LANDING__OUTCOME) AS TOTAL_NUMBER\n",
"FROM SPACEXTBL\n",
"WHERE DATE BETWEEN '2010-06-04' AND '2017-03-20'\n",
"GROUP BY LANDING__OUTCOME\n",
"ORDER BY TOTAL_NUMBER 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-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"
]
},
{
"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-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"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## <h3 align=\"center\"> © IBM Corporation 2021. All rights reserved. <h3/>\n"
]
}
],
"metadata": {
"kernelspec": {
"display_name": "Python",
"language": "python",
"name": "conda-env-python-py"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.6.13"
}
},
"nbformat": 4,
"nbformat_minor": 4
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment