jupyter-labs-eda-sql-coursera.ipynb

{"cells":[{"cell_type":"markdown","id":"1a4572a7-337d-4da8-933e-77d85ab02a73","metadata":{},"outputs":[],"source":["\u003cp style=\"text-align:center\"\u003e\n","    \u003ca href=\"https://skills.network/?utm_medium=Exinfluencer\u0026utm_source=Exinfluencer\u0026utm_content=000026UJ\u0026utm_term=10006555\u0026utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDS0321ENSkillsNetwork865-2023-01-01\"\u003e\n","    \u003cimg src=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/assets/logos/SN_web_lightmode.png\" width=\"200\" alt=\"Skills Network Logo\"  /\u003e\n","    \u003c/a\u003e\n","\u003c/p\u003e\n","\n","\u003ch1 align=center\u003e\u003cfont size = 5\u003eAssignment: SQL Notebook for Peer Assignment\u003c/font\u003e\u003c/h1\u003e\n","\n","Estimated time needed: **60** minutes.\n","\n","## Introduction\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","id":"f0f042ef-27c0-4a64-94f5-b5f6ff99fa29","metadata":{},"outputs":[],"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","\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","id":"9df33158-7234-4d15-83f0-acab745b6b06","metadata":{},"outputs":[],"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"," \u003ca href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/module_2/data/Spacex.csv\" target=\"_blank\"\u003eSpacex DataSet\u003c/a\u003e\n","\n"]},{"cell_type":"markdown","id":"7e594554-fe5c-4346-a1f3-38fb3e5c6126","metadata":{},"outputs":[],"source":["**Navigate to the Go to UI screen** \n","\n","* Refer to this insruction in this \u003ca 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\"\u003elink\u003c/a\u003e for viewing  the   Go to UI screen. \n","\n","\n","* Later click on **Data link(below SQL)**  in the Go to UI screen  and click on **Load Data** tab.  \n","\n","\n","\n","* Later browse for the downloaded spacex file.\n","\n","\n","\n","\u003cimg src=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/module_2/images/browsefile.png\" width=\"800\"\u003e\n","\n","\n","* Once done select the schema andload the file.  \n","\n","\n"," \u003cimg src=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/module_2/images/spacexload3.png\" width=\"800\"\u003e\n"," \n"]},{"cell_type":"markdown","id":"10e0e2ed-3414-4c28-ad6e-7e21c1863683","metadata":{},"outputs":[],"source":["\n","If you are facing a problem in uploading the dataset (which is a csv file), you can follow the steps below to upload the .sql file instead of the CSV file:\n","\n","* Download the file \u003ca href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/datasets/Spacex%20.sql\"\u003eSpacex.sql\u003c/a\u003e\n","\n","* Later click on **SQL** in the  **Go to UI Screen**.\n","\n","* Use the **From file** option to browse for the **SQL** file and upload it.\n","\n","\u003cimg src=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/module_2/images/sqlfile.png\"\u003e\n","\n","* Once you upload the script,you can use the **Run All** option to run all the queries to insert the data.\n","\n","\u003cimg src=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/module_2/images/runall.png\"\u003e\n","\n","    \n"]},{"cell_type":"code","id":"cfa51ac3-773f-464f-8613-0f40371d1237","metadata":{},"outputs":[],"source":["!pip install --force-reinstall ibm_db==3.1.0 ibm_db_sa==0.3.3\n!pip install sqlalchemy==1.3.24\n!pip uninstall ipython-sql -y\n!pip install ipython-sql==0.4.1"]},{"cell_type":"markdown","id":"c3dba6f4-b072-426f-a321-170cc50d9d54","metadata":{},"outputs":[],"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","id":"f6d1a362-0ff3-489a-89bf-89e07ae36de6","metadata":{},"outputs":[],"source":["%load_ext sql"]},{"cell_type":"markdown","id":"0d0f926b-bc7f-4fa8-bfb7-1df4c6eef9b1","metadata":{},"outputs":[],"source":["\n","\n","\n","**DB2 magic in case of  UI service credentials.**\n","\n","\n","\n","\u003cimg src=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/module_2/images/servicecredentials.png\" width=\"600\"\u003e  \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","id":"c440ae0e-a4d2-4775-8ce4-da4668ea088e","metadata":{},"outputs":[],"source":[""]},{"cell_type":"code","id":"5e21609d-8105-46f0-b6a7-9aa6569b323b","metadata":{},"outputs":[],"source":[""]},{"cell_type":"code","id":"88bdd54b-9c54-4045-a2cc-36150de56071","metadata":{},"outputs":[],"source":["%sql ibm_db_sa://"]},{"cell_type":"markdown","id":"b0eba71f-c487-4645-a9db-b38e3de859bd","metadata":{},"outputs":[],"source":["## Tasks\n","\n","Now write and execute SQL queries to solve the assignment tasks.\n","\n","### Task 1\n","\n","\n","\n","\n","##### Display the names of the unique launch sites  in the space mission\n"]},{"cell_type":"code","id":"f31edd45-b6bb-4b6b-b0e8-7f5abee8c30a","metadata":{},"outputs":[],"source":[""]},{"cell_type":"markdown","id":"3dcd6095-ce1e-47fe-b980-82ec5c94868b","metadata":{},"outputs":[],"source":["\n","### Task 2\n","\n","\n","#####  Display 5 records where launch sites begin with the string 'CCA' \n"]},{"cell_type":"code","id":"546fb108-e3e5-4cd0-85c4-59d7b1c3de4e","metadata":{},"outputs":[],"source":[""]},{"cell_type":"markdown","id":"c71c6965-cf7d-4d67-ac53-a02bfd9989d1","metadata":{},"outputs":[],"source":["### Task 3\n","\n","\n","\n","\n","##### Display the total payload mass carried by boosters launched by NASA (CRS)\n"]},{"cell_type":"code","id":"880f11e0-4aa3-46cd-a6c6-7b5f483544f0","metadata":{},"outputs":[],"source":[""]},{"cell_type":"markdown","id":"390dab14-7504-49dd-80d1-845142f118e9","metadata":{},"outputs":[],"source":["### Task 4\n","\n","\n","\n","\n","##### Display average payload mass carried by booster version F9 v1.1\n"]},{"cell_type":"code","id":"f5556b76-1f7b-4cdf-9802-0ae766b032ff","metadata":{},"outputs":[],"source":[""]},{"cell_type":"markdown","id":"2e1a7ec4-bbd5-403b-9e8d-2772313b32a7","metadata":{},"outputs":[],"source":["### Task 5\n","\n","##### List the date when the first successful landing outcome in ground pad was acheived.\n","\n","\n","_Hint:Use min function_ \n"]},{"cell_type":"code","id":"1cc1dbe7-cb50-4b40-8db3-b4d4cdee01a5","metadata":{},"outputs":[],"source":[""]},{"cell_type":"markdown","id":"61b1d91a-b208-4beb-80d4-00624dbe46a8","metadata":{},"outputs":[],"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","id":"521f4752-62e6-402b-ac53-42b8795dc1c4","metadata":{},"outputs":[],"source":[""]},{"cell_type":"markdown","id":"ceccf431-8329-4443-875f-1505a5c2c083","metadata":{},"outputs":[],"source":["### Task 7\n","\n","\n","\n","\n","##### List the total number of successful and failure mission outcomes\n"]},{"cell_type":"code","id":"f0099236-66c8-4369-ae2d-fc5aae522610","metadata":{},"outputs":[],"source":[""]},{"cell_type":"markdown","id":"cffcca0b-2fb7-45a9-afd6-a2af9edcddfa","metadata":{},"outputs":[],"source":["### Task 8\n","\n","\n","\n","##### List the   names of the booster_versions which have carried the maximum payload mass. Use a subquery\n"]},{"cell_type":"code","id":"2cb7b593-2192-47e4-99c5-2bc62ca6ebd5","metadata":{},"outputs":[],"source":[""]},{"cell_type":"markdown","id":"d81d8af0-6542-4c3b-8713-d2c266a36434","metadata":{},"outputs":[],"source":["### Task 9\n","\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","id":"dd00fec0-d4b5-4fb1-8cd7-f95ebf2f1856","metadata":{},"outputs":[],"source":[""]},{"cell_type":"markdown","id":"cbfc7853-46d8-4a89-9468-1170274ec555","metadata":{},"outputs":[],"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","id":"a1aecee7-7189-4189-9c74-c0808ac34cd6","metadata":{},"outputs":[],"source":[""]},{"cell_type":"markdown","id":"07c75a47-4b2b-4dba-a09d-2890d61169ca","metadata":{},"outputs":[],"source":["### Reference Links\n","\n","* \u003ca 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?origin=www.coursera.org\"\u003eHands-on Lab : String Patterns, Sorting and Grouping\u003c/a\u003e  \n","\n","*  \u003ca  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?origin=www.coursera.org\"\u003eHands-on Lab: Built-in functions\u003c/a\u003e\n","\n","*  \u003ca  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?origin=www.coursera.org\"\u003eHands-on Lab : Sub-queries and Nested SELECT Statements\u003c/a\u003e\n","\n","*   \u003ca href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Module%205/DB0201EN-Week3-1-3-SQLmagic.ipynb\"\u003eHands-on Tutorial: Accessing Databases with SQL magic\u003c/a\u003e\n","\n","*  \u003ca href= \"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Module%205/DB0201EN-Week3-1-4-Analyzing.ipynb\"\u003eHands-on Lab: Analyzing a real World Data Set\u003c/a\u003e\n","\n","\n"]},{"cell_type":"markdown","id":"76a8e6fb-7dfe-4e28-ab8e-c240920ae1d3","metadata":{},"outputs":[],"source":["## Author(s)\n","\n","\u003ch4\u003e Lakshmi Holla \u003c/h4\u003e\n"]},{"cell_type":"markdown","id":"5b99ecdf-6aa7-4cca-b6fc-bbc01e78f553","metadata":{},"outputs":[],"source":["## Other Contributors\n","\n","\u003ch4\u003e Rav Ahuja \u003c/h4\u003e\n"]},{"cell_type":"markdown","id":"b0f48c22-b195-4751-9638-48a6e6948a22","metadata":{},"outputs":[],"source":["## Change log\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"]},{"cell_type":"markdown","id":"04d29bb5-339b-4842-b38c-af6de62d5ad5","metadata":{},"outputs":[],"source":["## \u003ch3 align=\"center\"\u003e © IBM Corporation 2021. All rights reserved. \u003ch3/\u003e\n"]}],"metadata":{"kernelspec":{"display_name":"Python","language":"python","name":"conda-env-python-py"},"language_info":{"name":""}},"nbformat":4,"nbformat_minor":4}