jupyter-labs-eda-sql-coursera_sqllite.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style=\"text-align:center\">\n",
    "    <a href=\"https://skills.network\" 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",
    "\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",
   "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",
    "\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\" target=\"_blank\">Spacex DataSet</a>\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: sqlalchemy==1.3.9 in c:\\users\\ravi tej\\anaconda3\\lib\\site-packages (1.3.9)\n",
      "Requirement already satisfied: ipython-sql in c:\\users\\ravi tej\\anaconda3\\lib\\site-packages (0.5.0)\n",
      "Requirement already satisfied: prettytable in c:\\users\\ravi tej\\anaconda3\\lib\\site-packages (from ipython-sql) (3.8.0)\n",
      "Requirement already satisfied: ipython in c:\\users\\ravi tej\\anaconda3\\lib\\site-packages (from ipython-sql) (7.31.1)\n",
      "Requirement already satisfied: sqlparse in c:\\users\\ravi tej\\anaconda3\\lib\\site-packages (from ipython-sql) (0.4.4)\n",
      "Requirement already satisfied: ipython-genutils in c:\\users\\ravi tej\\anaconda3\\lib\\site-packages (from ipython-sql) (0.2.0)\n",
      "Collecting sqlalchemy>=2.0\n",
      "  Using cached SQLAlchemy-2.0.20-cp39-cp39-win_amd64.whl (2.0 MB)\n",
      "Requirement already satisfied: six in c:\\users\\ravi tej\\anaconda3\\lib\\site-packages (from ipython-sql) (1.16.0)\n",
      "Requirement already satisfied: greenlet!=0.4.17 in c:\\users\\ravi tej\\anaconda3\\lib\\site-packages (from sqlalchemy>=2.0->ipython-sql) (1.1.1)\n",
      "Requirement already satisfied: typing-extensions>=4.2.0 in c:\\users\\ravi tej\\anaconda3\\lib\\site-packages (from sqlalchemy>=2.0->ipython-sql) (4.3.0)\n",
      "Requirement already satisfied: matplotlib-inline in c:\\users\\ravi tej\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (0.1.6)\n",
      "Requirement already satisfied: decorator in c:\\users\\ravi tej\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (5.1.1)\n",
      "Requirement already satisfied: backcall in c:\\users\\ravi tej\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (0.2.0)\n",
      "Requirement already satisfied: setuptools>=18.5 in c:\\users\\ravi tej\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (63.4.1)\n",
      "Requirement already satisfied: colorama in c:\\users\\ravi tej\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (0.4.5)\n",
      "Requirement already satisfied: jedi>=0.16 in c:\\users\\ravi tej\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (0.18.1)\n",
      "Requirement already satisfied: prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0 in c:\\users\\ravi tej\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (3.0.20)\n",
      "Requirement already satisfied: traitlets>=4.2 in c:\\users\\ravi tej\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (5.1.1)\n",
      "Requirement already satisfied: pygments in c:\\users\\ravi tej\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (2.11.2)\n",
      "Requirement already satisfied: pickleshare in c:\\users\\ravi tej\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (0.7.5)\n",
      "Requirement already satisfied: wcwidth in c:\\users\\ravi tej\\anaconda3\\lib\\site-packages (from prettytable->ipython-sql) (0.2.5)\n",
      "Requirement already satisfied: parso<0.9.0,>=0.8.0 in c:\\users\\ravi tej\\anaconda3\\lib\\site-packages (from jedi>=0.16->ipython->ipython-sql) (0.8.3)\n",
      "Installing collected packages: sqlalchemy\n",
      "  Attempting uninstall: sqlalchemy\n",
      "    Found existing installation: SQLAlchemy 1.3.9\n",
      "    Uninstalling SQLAlchemy-1.3.9:\n",
      "      Successfully uninstalled SQLAlchemy-1.3.9\n",
      "Successfully installed sqlalchemy-2.0.20\n"
     ]
    }
   ],
   "source": [
    "!pip install sqlalchemy==1.3.9\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": 41,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The sql extension is already loaded. To reload it, use:\n",
      "  %reload_ext sql\n"
     ]
    }
   ],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import csv, sqlite3\n",
    "\n",
    "con = sqlite3.connect(\"my_data1.db\")\n",
    "cur = con.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "!pip install -q pandas==1.1.5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Traceback (most recent call last):\n",
      "  File \"C:\\Users\\RAVI TEJ\\anaconda3\\lib\\site-packages\\sql\\magic.py\", line 203, in execute\n",
      "    conn.internal_connection.rollback()\n",
      "AttributeError: 'Connection' object has no attribute 'rollback'\n",
      "\n",
      "Connection info needed in SQLAlchemy format, example:\n",
      "               postgresql://username:password@hostname/dbname\n",
      "               or an existing connection: dict_keys(['sqlite:///my_data1.db'])\n"
     ]
    }
   ],
   "source": [
    "%sql sqlite:///my_data1.db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\RAVI TEJ\\anaconda3\\lib\\site-packages\\pandas\\core\\generic.py:2605: UserWarning: The spaces in these column names will not be changed. In pandas versions < 0.14, spaces were converted to underscores.\n",
      "  sql.to_sql(\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\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",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2010-04-06</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",
       "      <th>1</th>\n",
       "      <td>2010-08-12</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...</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",
       "      <th>2</th>\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",
       "      <th>3</th>\n",
       "      <td>2012-08-10</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",
       "      <th>4</th>\n",
       "      <td>2013-01-03</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",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>2020-05-11</td>\n",
       "      <td>23:24:23</td>\n",
       "      <td>F9 B5B1062.1</td>\n",
       "      <td>CCAFS SLC-40</td>\n",
       "      <td>GPS III-04 , Crew-1</td>\n",
       "      <td>4311</td>\n",
       "      <td>MEO</td>\n",
       "      <td>USSF</td>\n",
       "      <td>Success</td>\n",
       "      <td>Success</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>2020-11-16</td>\n",
       "      <td>00:27:00</td>\n",
       "      <td>F9 B5B1061.1</td>\n",
       "      <td>KSC LC-39A</td>\n",
       "      <td>Crew-1, Sentinel-6 Michael Freilich</td>\n",
       "      <td>12500</td>\n",
       "      <td>LEO (ISS)</td>\n",
       "      <td>NASA (CCP)</td>\n",
       "      <td>Success</td>\n",
       "      <td>Success</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>2020-11-21</td>\n",
       "      <td>17:17:08</td>\n",
       "      <td>F9 B5B1063.1</td>\n",
       "      <td>VAFB SLC-4E</td>\n",
       "      <td>Sentinel-6 Michael Freilich, Starlink 15 v1.0</td>\n",
       "      <td>1192</td>\n",
       "      <td>LEO</td>\n",
       "      <td>NASA / NOAA / ESA / EUMETSAT</td>\n",
       "      <td>Success</td>\n",
       "      <td>Success</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>2020-11-25</td>\n",
       "      <td>02:13:00</td>\n",
       "      <td>F9 B5 B1049.7</td>\n",
       "      <td>CCAFS SLC-40</td>\n",
       "      <td>Starlink 15 v1.0, SpaceX CRS-21</td>\n",
       "      <td>15600</td>\n",
       "      <td>LEO</td>\n",
       "      <td>SpaceX</td>\n",
       "      <td>Success</td>\n",
       "      <td>Success</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>2020-06-12</td>\n",
       "      <td>16:17:08</td>\n",
       "      <td>F9 B5 B1058.4</td>\n",
       "      <td>KSC LC-39A</td>\n",
       "      <td>SpaceX CRS-21</td>\n",
       "      <td>2972</td>\n",
       "      <td>LEO (ISS)</td>\n",
       "      <td>NASA (CRS)</td>\n",
       "      <td>Success</td>\n",
       "      <td>Success</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>101 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           Date Time (UTC) Booster_Version   Launch_Site  \\\n",
       "0    2010-04-06   18:45:00  F9 v1.0  B0003   CCAFS LC-40   \n",
       "1    2010-08-12   15:43:00  F9 v1.0  B0004   CCAFS LC-40   \n",
       "2    2012-05-22   07:44:00  F9 v1.0  B0005   CCAFS LC-40   \n",
       "3    2012-08-10   00:35:00  F9 v1.0  B0006   CCAFS LC-40   \n",
       "4    2013-01-03   15:10:00  F9 v1.0  B0007   CCAFS LC-40   \n",
       "..          ...        ...             ...           ...   \n",
       "96   2020-05-11   23:24:23    F9 B5B1062.1  CCAFS SLC-40   \n",
       "97   2020-11-16   00:27:00   F9 B5B1061.1     KSC LC-39A   \n",
       "98   2020-11-21   17:17:08    F9 B5B1063.1   VAFB SLC-4E   \n",
       "99   2020-11-25   02:13:00  F9 B5 B1049.7   CCAFS SLC-40   \n",
       "100  2020-06-12   16:17:08  F9 B5 B1058.4     KSC LC-39A   \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",
       "96                                 GPS III-04 , Crew-1               4311   \n",
       "97                Crew-1, Sentinel-6 Michael Freilich               12500   \n",
       "98      Sentinel-6 Michael Freilich, Starlink 15 v1.0                1192   \n",
       "99                     Starlink 15 v1.0, SpaceX CRS-21              15600   \n",
       "100                                      SpaceX CRS-21               2972   \n",
       "\n",
       "         Orbit                      Customer Mission_Outcome  \\\n",
       "0          LEO                        SpaceX         Success   \n",
       "1    LEO (ISS)               NASA (COTS) NRO         Success   \n",
       "2    LEO (ISS)                   NASA (COTS)         Success   \n",
       "3    LEO (ISS)                    NASA (CRS)         Success   \n",
       "4    LEO (ISS)                    NASA (CRS)         Success   \n",
       "..         ...                           ...             ...   \n",
       "96         MEO                          USSF         Success   \n",
       "97   LEO (ISS)                    NASA (CCP)         Success   \n",
       "98         LEO  NASA / NOAA / ESA / EUMETSAT         Success   \n",
       "99         LEO                        SpaceX         Success   \n",
       "100  LEO (ISS)                    NASA (CRS)         Success   \n",
       "\n",
       "         Landing_Outcome  \n",
       "0    Failure (parachute)  \n",
       "1    Failure (parachute)  \n",
       "2             No attempt  \n",
       "3             No attempt  \n",
       "4             No attempt  \n",
       "..                   ...  \n",
       "96               Success  \n",
       "97               Success  \n",
       "98               Success  \n",
       "99               Success  \n",
       "100              Success  \n",
       "\n",
       "[101 rows x 10 columns]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "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",
    "df.to_sql(\"SPACEXTBL\", con, if_exists='replace', index=False,method=\"multi\")\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Note:This below code is added to remove blank rows from table**\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Traceback (most recent call last):\n",
      "  File \"C:\\Users\\RAVI TEJ\\anaconda3\\lib\\site-packages\\sql\\magic.py\", line 203, in execute\n",
      "    conn.internal_connection.rollback()\n",
      "AttributeError: 'Connection' object has no attribute 'rollback'\n",
      "\n",
      "Connection info needed in SQLAlchemy format, example:\n",
      "               postgresql://username:password@hostname/dbname\n",
      "               or an existing connection: dict_keys(['sqlite:///my_data1.db'])\n"
     ]
    }
   ],
   "source": [
    "%sql create table SPACEXTABLE as select * from SPACEXTBL where Date is not null"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "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",
    "\n",
    "\n",
    "\n",
    "##### Display the names of the unique launch sites  in the space mission\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "scrolled": true,
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Traceback (most recent call last):\n",
      "  File \"C:\\Users\\RAVI TEJ\\anaconda3\\lib\\site-packages\\sql\\magic.py\", line 203, in execute\n",
      "    conn.internal_connection.rollback()\n",
      "AttributeError: 'Connection' object has no attribute 'rollback'\n",
      "\n",
      "Connection info needed in SQLAlchemy format, example:\n",
      "               postgresql://username:password@hostname/dbname\n",
      "               or an existing connection: dict_keys(['sqlite:///my_data1.db'])\n"
     ]
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT * FROM SPACEXTABLE\n",
    "PRAGMA table_info(SPACEXTABLE);\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Traceback (most recent call last):\n",
      "  File \"C:\\Users\\RAVI TEJ\\anaconda3\\lib\\site-packages\\sql\\magic.py\", line 203, in execute\n",
      "    conn.internal_connection.rollback()\n",
      "AttributeError: 'Connection' object has no attribute 'rollback'\n",
      "\n",
      "Connection info needed in SQLAlchemy format, example:\n",
      "               postgresql://username:password@hostname/dbname\n",
      "               or an existing connection: dict_keys(['sqlite:///my_data1.db'])\n"
     ]
    }
   ],
   "source": [
    "%%sql\n",
    "ALTER TABLE SPACEXTABLE RENAME COLUMN \"Time (UTC)\" TO '\"Time (UTC)\"';\n",
    "ALTER TABLE SPACEXTABLE RENAME COLUMN \"Booster_Version\" TO '\"Booster_Version\"';\n",
    "ALTER TABLE SPACEXTABLE RENAME COLUMN \"Launch_Site\" TO '\"Launch_Site\"';\n",
    "ALTER TABLE SPACEXTABLE RENAME COLUMN \"Payload\" TO '\"Payload\"';\n",
    "ALTER TABLE SPACEXTABLE RENAME COLUMN \"PAYLOAD_MASS__KG_\" TO '\"PAYLOAD_MASS__KG_\"';\n",
    "ALTER TABLE SPACEXTABLE RENAME COLUMN \"Orbit\" TO '\"Orbit\"';\n",
    "ALTER TABLE SPACEXTABLE RENAME COLUMN \"Customer\" TO '\"Customer\"';\n",
    "ALTER TABLE SPACEXTABLE RENAME COLUMN \"Mission_Outcome\" TO '\"Mission_Outcome\"';\n",
    "ALTER TABLE SPACEXTABLE RENAME COLUMN \"Landing_Outcome\" TO '\"Landing_Outcome\"';\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Traceback (most recent call last):\n",
      "  File \"C:\\Users\\RAVI TEJ\\anaconda3\\lib\\site-packages\\sql\\magic.py\", line 203, in execute\n",
      "    conn.internal_connection.rollback()\n",
      "AttributeError: 'Connection' object has no attribute 'rollback'\n",
      "\n",
      "Connection info needed in SQLAlchemy format, example:\n",
      "               postgresql://username:password@hostname/dbname\n",
      "               or an existing connection: dict_keys(['sqlite:///my_data1.db'])\n"
     ]
    }
   ],
   "source": [
    "%%sql\n",
    "-- SELECT * FROM SPACEXTABLE\n",
    "PRAGMA table_info(SPACEXTABLE);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Traceback (most recent call last):\n",
      "  File \"C:\\Users\\RAVI TEJ\\anaconda3\\lib\\site-packages\\sql\\magic.py\", line 203, in execute\n",
      "    conn.internal_connection.rollback()\n",
      "AttributeError: 'Connection' object has no attribute 'rollback'\n",
      "\n",
      "Connection info needed in SQLAlchemy format, example:\n",
      "               postgresql://username:password@hostname/dbname\n",
      "               or an existing connection: dict_keys(['sqlite:///my_data1.db'])\n"
     ]
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM SPACEXTABLE LIMIT 5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Traceback (most recent call last):\n",
      "  File \"C:\\Users\\RAVI TEJ\\anaconda3\\lib\\site-packages\\sql\\magic.py\", line 203, in execute\n",
      "    conn.internal_connection.rollback()\n",
      "AttributeError: 'Connection' object has no attribute 'rollback'\n",
      "\n",
      "Connection info needed in SQLAlchemy format, example:\n",
      "               postgresql://username:password@hostname/dbname\n",
      "               or an existing connection: dict_keys(['sqlite:///my_data1.db'])\n"
     ]
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT DISTINCT \"Launch_Site\"\n",
    "FROM SPACEXTABLE\n",
    "\n",
    "-- SELECT * FROM SPACEXTABLE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Traceback (most recent call last):\n",
      "  File \"C:\\Users\\RAVI TEJ\\anaconda3\\lib\\site-packages\\sql\\magic.py\", line 203, in execute\n",
      "    conn.internal_connection.rollback()\n",
      "AttributeError: 'Connection' object has no attribute 'rollback'\n",
      "\n",
      "Connection info needed in SQLAlchemy format, example:\n",
      "               postgresql://username:password@hostname/dbname\n",
      "               or an existing connection: dict_keys(['sqlite:///my_data1.db'])\n"
     ]
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT DISTINCT \"Launch_Site\" FROM SPACEXTABLE "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "### Task 2\n",
    "\n",
    "\n",
    "#####  Display 5 records where launch sites begin with the string 'CCA' \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Traceback (most recent call last):\n",
      "  File \"C:\\Users\\RAVI TEJ\\anaconda3\\lib\\site-packages\\sql\\magic.py\", line 203, in execute\n",
      "    conn.internal_connection.rollback()\n",
      "AttributeError: 'Connection' object has no attribute 'rollback'\n",
      "\n",
      "Connection info needed in SQLAlchemy format, example:\n",
      "               postgresql://username:password@hostname/dbname\n",
      "               or an existing connection: dict_keys(['sqlite:///my_data1.db'])\n"
     ]
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "FROM SPACEXTABLE\n",
    "WHERE \"Launch_Site\" LIKE 'CCA%'\n",
    "LIMIT 5;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 3\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "##### Display the total payload mass carried by boosters launched by NASA (CRS)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Traceback (most recent call last):\n",
      "  File \"C:\\Users\\RAVI TEJ\\anaconda3\\lib\\site-packages\\sql\\magic.py\", line 203, in execute\n",
      "    conn.internal_connection.rollback()\n",
      "AttributeError: 'Connection' object has no attribute 'rollback'\n",
      "\n",
      "Connection info needed in SQLAlchemy format, example:\n",
      "               postgresql://username:password@hostname/dbname\n",
      "               or an existing connection: dict_keys(['sqlite:///my_data1.db'])\n"
     ]
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT SUM(\"PAYLOAD_MASS__KG_\")\n",
    "FROM SPACEXTABLE\n",
    "WHERE \"Customer\" = 'NASA (CRS)';\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 4\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "##### Display average payload mass carried by booster version F9 v1.1\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Traceback (most recent call last):\n",
      "  File \"C:\\Users\\RAVI TEJ\\anaconda3\\lib\\site-packages\\sql\\magic.py\", line 203, in execute\n",
      "    conn.internal_connection.rollback()\n",
      "AttributeError: 'Connection' object has no attribute 'rollback'\n",
      "\n",
      "Connection info needed in SQLAlchemy format, example:\n",
      "               postgresql://username:password@hostname/dbname\n",
      "               or an existing connection: dict_keys(['sqlite:///my_data1.db'])\n"
     ]
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT AVG(\"PAYLOAD_MASS__KG_\")\n",
    "FROM SPACEXTABLE\n",
    "WHERE \"Booster_Version\" = 'F9 v1.1'\n"
   ]
  },
  {
   "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",
    "\n",
    "_Hint:Use min function_ \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Traceback (most recent call last):\n",
      "  File \"C:\\Users\\RAVI TEJ\\anaconda3\\lib\\site-packages\\sql\\magic.py\", line 203, in execute\n",
      "    conn.internal_connection.rollback()\n",
      "AttributeError: 'Connection' object has no attribute 'rollback'\n",
      "\n",
      "Connection info needed in SQLAlchemy format, example:\n",
      "               postgresql://username:password@hostname/dbname\n",
      "               or an existing connection: dict_keys(['sqlite:///my_data1.db'])\n"
     ]
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "\n",
    "SELECT MIN(\"Date\")\n",
    "FROM SPACEXTABLE\n",
    "WHERE \"Landing_Outcome\" = 'Success (ground pad)'\n"
   ]
  },
  {
   "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": 18,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Traceback (most recent call last):\n",
      "  File \"C:\\Users\\RAVI TEJ\\anaconda3\\lib\\site-packages\\sql\\magic.py\", line 203, in execute\n",
      "    conn.internal_connection.rollback()\n",
      "AttributeError: 'Connection' object has no attribute 'rollback'\n",
      "\n",
      "Connection info needed in SQLAlchemy format, example:\n",
      "               postgresql://username:password@hostname/dbname\n",
      "               or an existing connection: dict_keys(['sqlite:///my_data1.db'])\n"
     ]
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT \"Booster_Version\"\n",
    "FROM SPACEXTABLE\n",
    "WHERE \"Landing_Outcome\" = 'Success (drone ship)'\n",
    "  AND \"PAYLOAD_MASS__KG_\" > 4000\n",
    "  AND \"PAYLOAD_MASS__KG_\" < 6000\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 7\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "##### List the total number of successful and failure mission outcomes\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Traceback (most recent call last):\n",
      "  File \"C:\\Users\\RAVI TEJ\\anaconda3\\lib\\site-packages\\sql\\magic.py\", line 203, in execute\n",
      "    conn.internal_connection.rollback()\n",
      "AttributeError: 'Connection' object has no attribute 'rollback'\n",
      "\n",
      "Connection info needed in SQLAlchemy format, example:\n",
      "               postgresql://username:password@hostname/dbname\n",
      "               or an existing connection: dict_keys(['sqlite:///my_data1.db'])\n"
     ]
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT \"Mission_Outcome\", COUNT(*) AS \"Total\"\n",
    "FROM SPACEXTABLE\n",
    "WHERE \"Mission_Outcome\" IN ('Success', 'Failure')\n",
    "GROUP BY \"Mission_Outcome\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "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",
   "execution_count": 20,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Traceback (most recent call last):\n",
      "  File \"C:\\Users\\RAVI TEJ\\anaconda3\\lib\\site-packages\\sql\\magic.py\", line 203, in execute\n",
      "    conn.internal_connection.rollback()\n",
      "AttributeError: 'Connection' object has no attribute 'rollback'\n",
      "\n",
      "Connection info needed in SQLAlchemy format, example:\n",
      "               postgresql://username:password@hostname/dbname\n",
      "               or an existing connection: dict_keys(['sqlite:///my_data1.db'])\n"
     ]
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT \"Booster_Version\"\n",
    "FROM SPACEXTABLE\n",
    "WHERE \"PAYLOAD_MASS__KG_\" = (SELECT MAX(\"PAYLOAD_MASS__KG_\")FROM SPACEXTABLE);\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 9\n",
    "\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": 21,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Traceback (most recent call last):\n",
      "  File \"C:\\Users\\RAVI TEJ\\anaconda3\\lib\\site-packages\\sql\\magic.py\", line 203, in execute\n",
      "    conn.internal_connection.rollback()\n",
      "AttributeError: 'Connection' object has no attribute 'rollback'\n",
      "\n",
      "Connection info needed in SQLAlchemy format, example:\n",
      "               postgresql://username:password@hostname/dbname\n",
      "               or an existing connection: dict_keys(['sqlite:///my_data1.db'])\n"
     ]
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT \n",
    "    CASE\n",
    "        WHEN substr(\"Date\", 4, 2) = '01' THEN 'January'\n",
    "        WHEN substr(\"Date\", 4, 2) = '02' THEN 'February'\n",
    "        WHEN substr(\"Date\", 4, 2) = '03' THEN 'March'\n",
    "        WHEN substr(\"Date\", 4, 2) = '04' THEN 'April'\n",
    "        WHEN substr(\"Date\", 4, 2) = '05' THEN 'May'\n",
    "        WHEN substr(\"Date\", 4, 2) = '06' THEN 'June'\n",
    "        WHEN substr(\"Date\", 4, 2) = '07' THEN 'July'\n",
    "        WHEN substr(\"Date\", 4, 2) = '08' THEN 'August'\n",
    "        WHEN substr(\"Date\", 4, 2) = '09' THEN 'September'\n",
    "        WHEN substr(\"Date\", 4, 2) = '10' THEN 'October'\n",
    "        WHEN substr(\"Date\", 4, 2) = '11' THEN 'November'\n",
    "        WHEN substr(\"Date\", 4, 2) = '12' THEN 'December'\n",
    "    END AS Month_Name,\n",
    "    \"Landing_Outcome\",\n",
    "    \"Booster_Version\",\n",
    "    \"Launch_Site\"\n",
    "FROM SPACEXTABLE\n",
    "WHERE substr(\"Date\", 7, 4) = '2015'\n",
    "    AND \"Landing_Outcome\" = 'Failure (drone ship)';\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Task 10\n",
    "\n",
    "\n",
    "\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": 22,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///my_data1.db\n",
      "Traceback (most recent call last):\n",
      "  File \"C:\\Users\\RAVI TEJ\\anaconda3\\lib\\site-packages\\sql\\magic.py\", line 203, in execute\n",
      "    conn.internal_connection.rollback()\n",
      "AttributeError: 'Connection' object has no attribute 'rollback'\n",
      "\n",
      "Connection info needed in SQLAlchemy format, example:\n",
      "               postgresql://username:password@hostname/dbname\n",
      "               or an existing connection: dict_keys(['sqlite:///my_data1.db'])\n"
     ]
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT \"Landing_Outcome\", COUNT(*) AS \"Outcome_Count\"\n",
    "FROM SPACEXTABLE\n",
    "WHERE \"Date\" BETWEEN '2010-06-04' AND '2017-03-20'\n",
    "  AND \"Landing_Outcome\" IN ('Failure (drone ship)', 'Success (ground pad)')\n",
    "GROUP BY \"Landing_Outcome\"\n",
    "ORDER BY \"Outcome_Count\" DESC;\n"
   ]
  },
  {
   "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?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?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?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\">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\">Hands-on Lab: Analyzing a real World Data Set</a>\n",
    "\n",
    "\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",
    "| 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": {},
   "source": [
    "## <h3 align=\"center\"> © IBM Corporation 2021. All rights reserved. <h3/>\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}