Skip to content

Instantly share code, notes, and snippets.

@maryamalqasmi

maryamalqasmi/EDA SQL.ipynb

Created August 26, 2021 12:03
Show Gist options
  • Save maryamalqasmi/1300390809e3e56f678bc1f5ff6e2be7 to your computer and use it in GitHub Desktop.
Save maryamalqasmi/1300390809e3e56f678bc1f5ff6e2be7 to your computer and use it in GitHub Desktop.
Download ZIP
Display the source blob
Display the rendered blob
Raw
EDA 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\nEstimated time needed: **60** minutes.\n\n## Introduction\n\nUsing this Python notebook you will:\n\n1. Understand the Spacex DataSet\n2. Load the dataset into the corresponding table in a Db2 database\n3. Execute SQL queries to answer assignment questions\n"
},
{
"cell_type": "markdown",
"metadata": {},
"source": "## Overview of the DataSet\n\nSpaceX has gained worldwide attention for a series of historic milestones.\n\nIt is the only private company ever to return a spacecraft from low-earth orbit, which it first accomplished in December 2010.\nSpaceX advertises Falcon 9 rocket launches on its website with a cost of 62 million dollars wheras other providers cost upward of 165 million dollars each, much of the savings is because Space X can reuse the first stage.\n\nTherefore if we can determine if the first stage will land, we can determine the cost of a launch.\n\nThis information can be used if an alternate company wants to bid against SpaceX for a rocket launch.\n\nThis dataset includes a record for each payload carried during a SpaceX mission into outer space.\n"
},
{
"cell_type": "markdown",
"metadata": {},
"source": "### Download the datasets\n\nThis assignment requires you to load the spacex dataset.\n\nIn many cases the dataset to be analyzed is available as a .CSV (comma separated values) file, perhaps on the internet. Click on the link below to download and save the dataset (.CSV file):\n\n<a href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/module_2/data/Spacex.csv?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDS0321ENSkillsNetwork26802033-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\nNow open the Db2 console, open the LOAD tool, Select / Drag the .CSV file for the dataset, Next create a New Table, and then follow the steps on-screen instructions to load the data. Name the new table as follows:\n\n**SPACEXDATASET**\n\n**Follow these steps while using old DB2 UI which is having Open Console Screen**\n\n**Note:While loading Spacex dataset, ensure that detect datatypes is disabled. Later click on the pencil icon(edit option).**\n\n1. Change the Date Format by manually typing DD-MM-YYYY and timestamp format as DD-MM-YYYY HH\\:MM:SS\n\n2. Change the PAYLOAD_MASS\\_\\_KG\\_ datatype to INTEGER.\n\n<img src = \"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/module_2/images/spacexload2.png\">\n"
},
{
"cell_type": "markdown",
"metadata": {},
"source": "**Changes to be considered when having DB2 instance with the new UI having Go to UI screen**\n\n* Refer to this insruction in this <a href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Labs_Coursera_V5/labs/Lab%20-%20Sign%20up%20for%20IBM%20Cloud%20-%20Create%20Db2%20service%20instance%20-%20Get%20started%20with%20the%20Db2%20console/instructional-labs.md.html?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDS0321ENSkillsNetwork26802033-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": null,
"metadata": {},
"outputs": [],
"source": "!pip install sqlalchemy==1.3.9\n!pip install ibm_db_sa\n!pip install ipython-sql"
},
{
"cell_type": "markdown",
"metadata": {},
"source": "### Connect to the database\n\nLet us first load the SQL extension and establish a connection with the database\n"
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": "%load_ext sql"
},
{
"cell_type": "markdown",
"metadata": {},
"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"
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": ""
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": ""
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": "%sql ibm_db_sa://"
},
{
"cell_type": "markdown",
"metadata": {},
"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"
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": ""
},
{
"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": null,
"metadata": {},
"outputs": [],
"source": ""
},
{
"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": null,
"metadata": {},
"outputs": [],
"source": ""
},
{
"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": null,
"metadata": {},
"outputs": [],
"source": ""
},
{
"cell_type": "markdown",
"metadata": {},
"source": "### Task 5\n\n##### List the date when the first succesful landing outcome in ground pad was acheived.\n\n*Hint:Use min function*\n"
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": ""
},
{
"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": null,
"metadata": {},
"outputs": [],
"source": ""
},
{
"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": null,
"metadata": {},
"outputs": [],
"source": ""
},
{
"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": null,
"metadata": {},
"outputs": [],
"source": ""
},
{
"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 the in year 2015\n"
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": ""
},
{
"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": null,
"metadata": {},
"outputs": [],
"source": ""
},
{
"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\"> \u00a9 IBM Corporation 2021. All rights reserved. <h3/>\n"
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3",
"language": "python",
"name": "python3"
},
"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.8.8"
}
},
"nbformat": 4,
"nbformat_minor": 4
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment