Copy_of_SQL_Programming_Getting_Started_with_Databases.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Copy of SQL Programming - Getting Started with Databases",
      "version": "0.3.2",
      "provenance": [],
      "collapsed_sections": [],
      "toc_visible": true,
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "accelerator": "GPU"
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/theBuluku/my-data-science-work/blob/master/Copy_of_SQL_Programming_Getting_Started_with_Databases.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "tRHyN7N81xCS",
        "colab_type": "text"
      },
      "source": [
        "<font color=\"green\">*To start working on this notebook, or any other notebook that we will use in the Moringa Data Science Course, we will need to save our own copy of it. We can do this by clicking File > Save a Copy in Drive. We will then be able to make edits to our own copy of this notebook.*</font> \n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "xEE0pm61HkJ0",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_brgSPnC2ClB",
        "colab_type": "text"
      },
      "source": [
        "# SQL Programming - Getting Started with Databases"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_D8JbTh02GDA",
        "colab_type": "text"
      },
      "source": [
        "## 1.1 Overview "
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ueOY-njw3bzC",
        "colab_type": "text"
      },
      "source": [
        "Structured Query Language (SQL) is the language that is used to store, manipulate and retrieve data from many databases. It is the standard language for many relational database management systems which are a type of databases used by organisations across the world. These type of relational database management systems are used to store data in tables and examples of such include SQLite, MySQL, Postgres, Oracle etc. \n",
        "\n",
        "During the Moringa DataScience Prep, we will learn about SQL since as Data Scientists, we may be required to interact with this data through the use of SQL.\n",
        "\n",
        "In this notebook, we will use SQL to learn how tables in databases are created. More specifically, we will learn how the structure of tables is defined which is critical in determining the quality of data. The better the structure, the easier it becomes to clean data."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "D6BU_C0d3dMo",
        "colab_type": "text"
      },
      "source": [
        "## 1.2 Connecting to our Database"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "PDk968tq3v43",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 35
        },
        "outputId": "4c772581-9e13-4ff9-ce14-8a7d1fa77262"
      },
      "source": [
        "# We will first load an sql extension into our environment\n",
        "# This extension will allow us to work with sql on Colaboratory\n",
        "#\n",
        "%load_ext sql\n",
        "\n",
        "# We will then connect to our in memory sqlite database\n",
        "# NB: This database will cease to exist as soon as the database connection is closed.\n",
        "# We will learn more about how databases are created later in prep.\n",
        "#\n",
        "%sql sqlite://"
      ],
      "execution_count": 1,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'Connected: @None'"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 1
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "TjMtC5JN7ZoI",
        "colab_type": "text"
      },
      "source": [
        "## 1.3 Creating a Table"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ee0bJxIKPQvK",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 95
        },
        "outputId": "203bdce9-bd3c-4106-b0e3-9d764b85f619"
      },
      "source": [
        "# Example 1\n",
        "# We will now define and create a table Classmates in our database (if it doesn't exist).\n",
        "# This table will have fields: PersonID, LastName, FirstName, Phone and Residence as shown below.\n",
        "# We will then fetch all records from the table.\n",
        "#\n",
        "%%sql \n",
        "CREATE TABLE if not exists Classmates (\n",
        "    PersonID, \n",
        "    LastName, \n",
        "    FirstName, \n",
        "    Phone, \n",
        "    Residence\n",
        "); \n",
        "\n",
        "SELECT * From Classmates;"
      ],
      "execution_count": 2,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            " * sqlite://\n",
            "Done.\n",
            "Done.\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<table>\n",
              "    <tr>\n",
              "        <th>PersonID</th>\n",
              "        <th>LastName</th>\n",
              "        <th>FirstName</th>\n",
              "        <th>Phone</th>\n",
              "        <th>Residence</th>\n",
              "    </tr>\n",
              "</table>"
            ],
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 2
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "a5-4ih-ea37Z",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 95
        },
        "outputId": "27a28806-9cf6-403e-8ad8-d8a5b14db274"
      },
      "source": [
        "# Example 2\n",
        "# In this example, we will create a table named Customers \n",
        "# with the columns Id, Name, Age, Address, Salary. \n",
        "# This kind of a table structure can be used by Sacco Management system.\n",
        "# Then later fetch all records in the table.\n",
        "# \n",
        "%%sql\n",
        "CREATE TABLE if not exists Customers(\n",
        "   Id,   \n",
        "   Name,  \n",
        "   Age,\n",
        "   Address,  \n",
        "   Salary\n",
        ");\n",
        "\n",
        "SELECT * From Customers;"
      ],
      "execution_count": 3,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            " * sqlite://\n",
            "Done.\n",
            "Done.\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<table>\n",
              "    <tr>\n",
              "        <th>Id</th>\n",
              "        <th>Name</th>\n",
              "        <th>Age</th>\n",
              "        <th>Address</th>\n",
              "        <th>Salary</th>\n",
              "    </tr>\n",
              "</table>"
            ],
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 3
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "0FmFgfaTbZO6",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 95
        },
        "outputId": "b34c8729-94dc-438d-8776-406dc4110e0e"
      },
      "source": [
        "# Example 3\n",
        "# In this example, we will create a Students table for a student management system.\n",
        "# This will contain the following fields,\n",
        "# AdmissionsNo, FirstName, MiddleName, LastName, DateOfBirth and DateOfAdmission.\n",
        "# Then fetch all records from Students table.\n",
        "#\n",
        "%%sql\n",
        "CREATE TABLE if not exists Students(\n",
        "    AdmissionsNo,\n",
        "    FirstName,\n",
        "    MiddleName,\n",
        "    LastName,\n",
        "    DateOfBirth,\n",
        "    DateOfAdmission\n",
        ");\n",
        " \n",
        "SELECT * from Students;"
      ],
      "execution_count": 4,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            " * sqlite://\n",
            "Done.\n",
            "Done.\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<table>\n",
              "    <tr>\n",
              "        <th>AdmissionsNo</th>\n",
              "        <th>FirstName</th>\n",
              "        <th>MiddleName</th>\n",
              "        <th>LastName</th>\n",
              "        <th>DateOfBirth</th>\n",
              "        <th>DateOfAdmission</th>\n",
              "    </tr>\n",
              "</table>"
            ],
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 4
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "oxISLcTfPSqn",
        "colab_type": "text"
      },
      "source": [
        "### <font color=\"green\"> 1.3 Challenges</font>"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "SbRfVF-IPa7y",
        "colab_type": "code",
        "outputId": "1e827aac-3ac6-4873-f563-fd7afbc58c15",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 95
        }
      },
      "source": [
        "# Challenge 1\n",
        "# Let us create a table name PC with the following fields; \n",
        "# Code, model, speed, RAM, HD, CD and Price.\n",
        "# We also specify the appropriate data types to our table, then display it.\n",
        "#\n",
        "%%sql\n",
        "CREATE TABLE if not exists PC(\n",
        "Code,\n",
        "model,\n",
        "speed\n",
        "RAM,\n",
        "HD,\n",
        "CD,\n",
        "Price\n",
        ");\n",
        "SELECT * from PC;"
      ],
      "execution_count": 5,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            " * sqlite://\n",
            "Done.\n",
            "Done.\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<table>\n",
              "    <tr>\n",
              "        <th>Code</th>\n",
              "        <th>model</th>\n",
              "        <th>speed</th>\n",
              "        <th>HD</th>\n",
              "        <th>CD</th>\n",
              "        <th>Price</th>\n",
              "    </tr>\n",
              "</table>"
            ],
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 5
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "raslfjbEPa6h",
        "colab_type": "code",
        "outputId": "57d7a2e7-7d0d-443d-a0d7-041aaba63a1c",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 95
        }
      },
      "source": [
        "# Challenge 2\n",
        "# Let us create a table named Printer with \n",
        "# the following code, model, speed, type and Price.\n",
        "#\n",
        "%%sql\n",
        "CREATE TABLE if not exists Printer(\n",
        "code,\n",
        "model,\n",
        "speed,\n",
        "type,\n",
        "Price\n",
        ");\n",
        "SELECT * from Printer;"
      ],
      "execution_count": 6,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            " * sqlite://\n",
            "Done.\n",
            "Done.\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<table>\n",
              "    <tr>\n",
              "        <th>code</th>\n",
              "        <th>model</th>\n",
              "        <th>speed</th>\n",
              "        <th>type</th>\n",
              "        <th>Price</th>\n",
              "    </tr>\n",
              "</table>"
            ],
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 6
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "mJd7ytpJPav_",
        "colab_type": "code",
        "outputId": "5965fd5a-a087-4130-83e6-20d554e67e17",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 95
        }
      },
      "source": [
        "# Challenge 3\n",
        "# We can now write another table called Movies with the columns \n",
        "# id, title, director, year and length_minutes\n",
        "# \n",
        "%%sql\n",
        "CREATE TABLE if not exists Movies(\n",
        "id,\n",
        "title,\n",
        "director,\n",
        "year,\n",
        "length_minutes\n",
        ");\n",
        "SELECT * from Movies;"
      ],
      "execution_count": 10,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            " * sqlite://\n",
            "Done.\n",
            "Done.\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<table>\n",
              "    <tr>\n",
              "        <th>id</th>\n",
              "        <th>title</th>\n",
              "        <th>director</th>\n",
              "        <th>year</th>\n",
              "        <th>length_minutes</th>\n",
              "    </tr>\n",
              "</table>"
            ],
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 10
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "tsDIChSD7eZA",
        "colab_type": "text"
      },
      "source": [
        "## 1.4 Specifying Column Data Types"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "LR9UX1XnbPEJ",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 95
        },
        "outputId": "0cf41959-6612-4f94-afbe-735ccd7820fe"
      },
      "source": [
        "# Example 1\n",
        "# While defining our table, we should specify different data types. \n",
        "# These datatypes will ensure that the particular column stores only\n",
        "# records of that type i.e. The NationalID column in the defined Citizens table \n",
        "# below will only take integer values. These are values between  -2,147,483,648 to 2,147,483,647.\n",
        "# If one would require to store much smaller or bigger values than the range above then\n",
        "# they can use a different datatype i.e. tinyint or bigint.\n",
        "# The datatype varchar will hold letters and numbers upto the specified limit\n",
        "# in the brackets.\n",
        "#\n",
        "%%sql\n",
        "CREATE TABLE IF NOT EXISTS Citizens (\n",
        "    NationalID int,\n",
        "    FirstName varchar(255),\n",
        "    MiddleName varchar(255),\n",
        "    PostalAddress varchar(255),\n",
        "    Residence varchar(255));\n",
        "SELECT * from Citizens;"
      ],
      "execution_count": 11,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            " * sqlite://\n",
            "Done.\n",
            "Done.\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<table>\n",
              "    <tr>\n",
              "        <th>NationalID</th>\n",
              "        <th>FirstName</th>\n",
              "        <th>MiddleName</th>\n",
              "        <th>PostalAddress</th>\n",
              "        <th>Residence</th>\n",
              "    </tr>\n",
              "</table>"
            ],
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 11
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "xD0OhRTmdFW5",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 95
        },
        "outputId": "da6c9838-19c8-40ea-d954-8cfd6a232176"
      },
      "source": [
        "# Example 2\n",
        "# Specifying column data types will ensure that \n",
        "# the data that is stored within that table of the correct type.\n",
        "# The data type date would ensure that the data stored is in the format YYYY-MM-DD.\t\n",
        "# The data type boolean supports the storage of two values: TRUE or FALSE.\n",
        "# No other data of a different nature would be accepted to the table \n",
        "# than the one it was specified to have.\n",
        "#\n",
        "%%sql\n",
        "CREATE TABLE IF NOT EXISTS artists(\n",
        "    Artist_Id int,\n",
        "    Artist_Name varchar(60),\n",
        "    Artist_DOB date,\n",
        "    Posters_In_Stock boolean);\n",
        "\n",
        "SELECT * from artists;"
      ],
      "execution_count": 12,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            " * sqlite://\n",
            "Done.\n",
            "Done.\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<table>\n",
              "    <tr>\n",
              "        <th>Artist_Id</th>\n",
              "        <th>Artist_Name</th>\n",
              "        <th>Artist_DOB</th>\n",
              "        <th>Posters_In_Stock</th>\n",
              "    </tr>\n",
              "</table>"
            ],
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 12
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "FOWSIAvGZpE-",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 95
        },
        "outputId": "d6c18f4a-74e7-4ae8-d08e-4c6042433809"
      },
      "source": [
        "# Example 3\n",
        "# The data type text accepts upto 2,147,483,647 characters\n",
        "# The data type float accepts floating point numbers i.e 183.3 as shown\n",
        "# \n",
        "%%sql\n",
        "CREATE TABLE IF NOT EXISTS Players (\n",
        "    id int,\n",
        "    name text,\n",
        "    age integer,\n",
        "    height float);\n",
        "\n",
        "SELECT * from Players;"
      ],
      "execution_count": 13,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            " * sqlite://\n",
            "Done.\n",
            "Done.\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<table>\n",
              "    <tr>\n",
              "        <th>id</th>\n",
              "        <th>name</th>\n",
              "        <th>age</th>\n",
              "        <th>height</th>\n",
              "    </tr>\n",
              "</table>"
            ],
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 13
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "LMP7zZY2dGuV",
        "colab_type": "text"
      },
      "source": [
        "### <font color=\"green\"> 1.4 Challenges</font>"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "8g9IjuQDdKP5",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 95
        },
        "outputId": "2f97ec0c-3f8b-40d1-b040-dd53f1e2e1e1"
      },
      "source": [
        "# Challenge 1\n",
        "# Let's create a table customer with CustID with datatype Integer, LastName \n",
        "# with datatype character(25), FirstName with datatype Character(20)\n",
        "#\n",
        "%%sql\n",
        "CREATE TABLE if not exists customer(\n",
        "  CustID int,\n",
        "  LastName VarChar(25),\n",
        "  FirstName VarChar(20));\n",
        "SELECT * from customer;"
      ],
      "execution_count": 16,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            " * sqlite://\n",
            "Done.\n",
            "Done.\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<table>\n",
              "    <tr>\n",
              "        <th>CustID</th>\n",
              "        <th>LastName</th>\n",
              "        <th>FirstName</th>\n",
              "    </tr>\n",
              "</table>"
            ],
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 16
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "mhbYzTUqgcT6",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 95
        },
        "outputId": "2dc6f5c4-7258-4863-9cd7-01885b6bf485"
      },
      "source": [
        "# Challenge 3\n",
        "# Create a table called sales that stores sales ID, customer ID, name, and address information.\n",
        "# using also the appropriate data types\n",
        "# \n",
        "%%sql\n",
        "CREATE TABLE if not exists sales(\n",
        "sales ID int,\n",
        "customer ID int,\n",
        "name text,\n",
        "address information text);\n",
        "SELECT * from sales;"
      ],
      "execution_count": 17,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            " * sqlite://\n",
            "Done.\n",
            "Done.\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<table>\n",
              "    <tr>\n",
              "        <th>sales</th>\n",
              "        <th>customer</th>\n",
              "        <th>name</th>\n",
              "        <th>address</th>\n",
              "    </tr>\n",
              "</table>"
            ],
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 17
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "dusTkcdWuv3E",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 95
        },
        "outputId": "ca47fb3b-3437-4694-efcb-3d081f7274b2"
      },
      "source": [
        "# Challenge 4\n",
        "# Create a table called employees that stores employee number, employee name, \n",
        "# department, and salary information using appropriate data types\n",
        "#\n",
        "%%sql\n",
        "CREATE TABLE if not exists employees(\n",
        "   employee number int,\n",
        "   name text,\n",
        "   department text,\n",
        "   salary information float);\n",
        "SELECT * from employees;"
      ],
      "execution_count": 21,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            " * sqlite://\n",
            "Done.\n",
            "Done.\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<table>\n",
              "    <tr>\n",
              "        <th>employee</th>\n",
              "        <th>name</th>\n",
              "        <th>department</th>\n",
              "        <th>salary</th>\n",
              "    </tr>\n",
              "</table>"
            ],
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 21
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ASFyzyDc9hth",
        "colab_type": "text"
      },
      "source": [
        "## 1.5 Specifying Column Default Values"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "mkNyTl37gfeM",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 72
        },
        "outputId": "3ec93bf8-d119-42c6-da30-cbdb4547ca45"
      },
      "source": [
        "# Example 1\n",
        "# To specify column default values, let's define a table named artists \n",
        "# which contains three columns artist_id, artist_name and place_of_birth. \n",
        "# - artist_id will be of the type int\n",
        "# - artist_name of the type varchar(60) \n",
        "# - place_of_birth varchar(60) with default 'Unknown'.\n",
        "# NB: Changing the case of our data type definition will not have any effect.\n",
        "#\n",
        "%%sql \n",
        "CREATE TABLE if not exists Artists(\n",
        "    artist_id INT, \n",
        "    artist_name VARCHAR(60), \n",
        "    place_of_birth VARCHAR(60)\n",
        ");"
      ],
      "execution_count": 24,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            " * sqlite://\n",
            "Done.\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 24
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_ut68z0BgfW9",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 72
        },
        "outputId": "bf169d2a-c808-4912-f3f5-c917c5774411"
      },
      "source": [
        "# Example 2\n",
        "# To specify default values for the fields in a new table TeamMembers,\n",
        "# We do the following as shown below.\n",
        "# \n",
        "%%sql\n",
        "CREATE TABLE IF NOT EXISTS TeamMembers (\n",
        "    id INTEGER PRIMARY KEY,\n",
        "    name TEXT,\n",
        "    age INTEGER DEFAULT 'Unknown',\n",
        "    height REAL);"
      ],
      "execution_count": 25,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            " * sqlite://\n",
            "Done.\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 25
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Xr_iw9O1gynX",
        "colab_type": "text"
      },
      "source": [
        "### <font color=\"green\"> 1.5 Challenges</font>"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "H0X577pGgxuY",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 72
        },
        "outputId": "f84e35bd-e45e-4106-ed80-8104c337d42b"
      },
      "source": [
        "# Challenge 1\n",
        "# Let's create a new table called latest_players with similar fields to\n",
        "# the already created Players table but specify the default value to unknown\n",
        "#\n",
        "%%sql\n",
        "CREATE TABLE if not exists latest_players(\n",
        "    id INTEGER PRIMARY KEY,\n",
        "    name text,\n",
        "    age INTEGER DEFAULT'Unknown',\n",
        "    height float);"
      ],
      "execution_count": 31,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            " * sqlite://\n",
            "Done.\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 31
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "1LEzaO5ggpCt",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 199
        },
        "outputId": "eb80f12a-0e4d-4686-a806-eb4100256330"
      },
      "source": [
        "# Challenge 2\n",
        "# Let's create a new table called restaurants with the fields\n",
        "# - name: string\n",
        "# - description: text \n",
        "# - address: string, default value is unknown\n",
        "# - user_id: integer\n",
        "# - last_orders_at: date\n",
        "# We can perform data type external research if need be\n",
        "#\n",
        "%%sql\n",
        "CREATE TABLE if not exists restuarants(\n",
        "  name string,\n",
        "  description text,\n",
        "  address STRING DEFAULT 'Unknown',\n",
        "  user_id int,\n",
        "  last_orders_at date),\n",
        "SELECT * from restuarants;\n"
      ],
      "execution_count": 34,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            " * sqlite://\n",
            "(sqlite3.OperationalError) near \",\": syntax error\n",
            "[SQL: CREATE TABLE if not exists restuarants(\n",
            "  name string,\n",
            "description text,\n",
            "address STRING DEFAULT 'Unknown',\n",
            "user_id int,\n",
            "last_orders_at date),\n",
            "SELECT * from restuarants;]\n",
            "(Background on this error at: http://sqlalche.me/e/e3q8)\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "fbqK98Ve9qUw",
        "colab_type": "text"
      },
      "source": [
        "## 1.6 Altering SQL Tables"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "hX5YD-_dhEra",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 95
        },
        "outputId": "127e1ba7-57c5-48ef-b94c-20596c4d5ca2"
      },
      "source": [
        "# Example 1: Adding a Column\n",
        "# To add a column Gender to the Classmates table, we do the following, \n",
        "# then preview the table to see the changes\n",
        "#\n",
        "%%sql \n",
        "ALTER TABLE Classmates ADD Gender;\n",
        " \n",
        "SELECT * FROM Classmates;"
      ],
      "execution_count": 35,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            " * sqlite://\n",
            "Done.\n",
            "Done.\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<table>\n",
              "    <tr>\n",
              "        <th>PersonID</th>\n",
              "        <th>LastName</th>\n",
              "        <th>FirstName</th>\n",
              "        <th>Phone</th>\n",
              "        <th>Residence</th>\n",
              "        <th>Gender</th>\n",
              "    </tr>\n",
              "</table>"
            ],
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 35
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "IGZSdFj2hGbP",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 90
        },
        "outputId": "08c87dec-c5b9-4ede-8674-0b16c5903cb1"
      },
      "source": [
        "# Example 2: Deleting a Column\n",
        "# To delete a column Phone in a table above we do the following,\n",
        "# Then fetch records from the table to confirm the changes\n",
        "#\n",
        "%%sql \n",
        "ALTER TABLE Classmates drop Phone;\n",
        "SELECT * from Classmates;\n"
      ],
      "execution_count": 43,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            " * sqlite://\n",
            "(sqlite3.OperationalError) near \"drop\": syntax error\n",
            "[SQL: ALTER TABLE Classmates drop Phone;]\n",
            "(Background on this error at: http://sqlalche.me/e/e3q8)\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "sHLkllxhhH3v",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 95
        },
        "outputId": "dfff2abb-a289-47a3-d052-981c211e8f14"
      },
      "source": [
        "# Example 3\n",
        "# We can change the name of the Classmates table to Schoolmates by doing the following,\n",
        "# Then fetching the records from the table to confirm the changes\n",
        "#\n",
        "%%sql  \n",
        "ALTER TABLE Classmates RENAME TO Schoolmates;\n",
        "\n",
        "SELECT * FROM Schoolmates;"
      ],
      "execution_count": 44,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            " * sqlite://\n",
            "Done.\n",
            "Done.\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<table>\n",
              "    <tr>\n",
              "        <th>PersonID</th>\n",
              "        <th>LastName</th>\n",
              "        <th>FirstName</th>\n",
              "        <th>Phone</th>\n",
              "        <th>Residence</th>\n",
              "        <th>Gender</th>\n",
              "    </tr>\n",
              "</table>"
            ],
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 44
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "f20THWRqhJC-",
        "colab_type": "text"
      },
      "source": [
        "### <font color=\"green\"> 1.6 Challenges</font>"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "6Nrsden-hJ7u",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 95
        },
        "outputId": "24232b25-6a55-4cf6-e331-52fa9b5acd6e"
      },
      "source": [
        "# Challenge 1\n",
        "# We can add a column DOB with the data type DATE to the TeamMembers table by;\n",
        "# Hint: The data type comes after the co\n",
        "%%sql\n",
        "ALTER TABLE TeamMembers ADD DOB date;\n",
        "SELECT * from TeamMembers;"
      ],
      "execution_count": 45,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            " * sqlite://\n",
            "Done.\n",
            "Done.\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<table>\n",
              "    <tr>\n",
              "        <th>id</th>\n",
              "        <th>name</th>\n",
              "        <th>age</th>\n",
              "        <th>height</th>\n",
              "        <th>DOB</th>\n",
              "    </tr>\n",
              "</table>"
            ],
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 45
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "2hwkJkSNlIyd",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 181
        },
        "outputId": "7455b12a-7907-484d-de3b-ad12cb9cc502"
      },
      "source": [
        "# Example 4: Confirmation\n",
        "# Let's check our data type\n",
        "%%sql\n",
        "PRAGMA table_info(TeamMembers);"
      ],
      "execution_count": 46,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            " * sqlite://\n",
            "Done.\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<table>\n",
              "    <tr>\n",
              "        <th>cid</th>\n",
              "        <th>name</th>\n",
              "        <th>type</th>\n",
              "        <th>notnull</th>\n",
              "        <th>dflt_value</th>\n",
              "        <th>pk</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "        <td>0</td>\n",
              "        <td>id</td>\n",
              "        <td>INTEGER</td>\n",
              "        <td>0</td>\n",
              "        <td>None</td>\n",
              "        <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "        <td>1</td>\n",
              "        <td>name</td>\n",
              "        <td>TEXT</td>\n",
              "        <td>0</td>\n",
              "        <td>None</td>\n",
              "        <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "        <td>2</td>\n",
              "        <td>age</td>\n",
              "        <td>INTEGER</td>\n",
              "        <td>0</td>\n",
              "        <td>&#x27;Unknown&#x27;</td>\n",
              "        <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "        <td>3</td>\n",
              "        <td>height</td>\n",
              "        <td>REAL</td>\n",
              "        <td>0</td>\n",
              "        <td>None</td>\n",
              "        <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "        <td>4</td>\n",
              "        <td>DOB</td>\n",
              "        <td>date</td>\n",
              "        <td>0</td>\n",
              "        <td>None</td>\n",
              "        <td>0</td>\n",
              "    </tr>\n",
              "</table>"
            ],
            "text/plain": [
              "[(0, 'id', 'INTEGER', 0, None, 1),\n",
              " (1, 'name', 'TEXT', 0, None, 0),\n",
              " (2, 'age', 'INTEGER', 0, \"'Unknown'\", 0),\n",
              " (3, 'height', 'REAL', 0, None, 0),\n",
              " (4, 'DOB', 'date', 0, None, 0)]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 46
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "iLNLtWBFhLCS",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 95
        },
        "outputId": "c9c7ffef-e0c1-442d-a68c-f26e09687385"
      },
      "source": [
        "# Challenge 2\n",
        "# Let's now add a column STUDIO with the data type TEXT to the Artists table \n",
        "#\n",
        "%%sql\n",
        "ALTER TABLE Artists ADD STUDIO TEXT;\n",
        "SELECT * from Artists;"
      ],
      "execution_count": 47,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            " * sqlite://\n",
            "Done.\n",
            "Done.\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<table>\n",
              "    <tr>\n",
              "        <th>Artist_Id</th>\n",
              "        <th>Artist_Name</th>\n",
              "        <th>Artist_DOB</th>\n",
              "        <th>Posters_In_Stock</th>\n",
              "        <th>STUDIO</th>\n",
              "    </tr>\n",
              "</table>"
            ],
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 47
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "16L-Ex_ehKzt",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 76
        },
        "outputId": "b621a355-ea9f-4e44-f227-2422b22ac1ae"
      },
      "source": [
        "# Challenge 3\n",
        "# We then rename the table Artists to MusicArtists\n",
        "#\n",
        "%%sql\n",
        "\n"
      ],
      "execution_count": 50,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            " * sqlite://\n",
            "Done.\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<table>\n",
              "    <tr>\n",
              "        <th>cid</th>\n",
              "        <th>name</th>\n",
              "        <th>type</th>\n",
              "        <th>notnull</th>\n",
              "        <th>dflt_value</th>\n",
              "        <th>pk</th>\n",
              "    </tr>\n",
              "</table>"
            ],
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 50
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "N5323tiC9u5B",
        "colab_type": "text"
      },
      "source": [
        "## 1.7 Dropping SQL Tables"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Lyy1XQCy_rIm",
        "colab_type": "text"
      },
      "source": [
        "### 1.71 Truncating a Table\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "EF5WdMH1hj-8",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 72
        },
        "outputId": "dfb92fa2-cae3-42e5-8f31-a970de79e7cf"
      },
      "source": [
        "# Example 1\n",
        "# We may have two options while thinking about dropping (deleting) database tables. \n",
        "# These are truncating or dropping.\n",
        "# We can use the TRUNCATE TABLE statement to delete the data inside \n",
        "# our table as shown below. Do note that this command retains the table itself.\n",
        "# We will get to confirm the effect of using this command later when we get \n",
        "# to insert data to the table.\n",
        "#\n",
        "%%sql\n",
        "DROP TABLE Schoolmates;"
      ],
      "execution_count": 54,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            " * sqlite://\n",
            "Done.\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 54
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "018guVdKDwnu",
        "colab_type": "text"
      },
      "source": [
        "### 1.72 Dropping a Table"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "IBP2xlN4Dz8H",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 135
        },
        "outputId": "dcf30473-d951-4905-bb7c-877252100b29"
      },
      "source": [
        "# Example 1\n",
        "# We can drop our table by using the DROP TABLE statement as shown below\n",
        "#\n",
        "%sql \n",
        "DROP TABLE Schoolmates;"
      ],
      "execution_count": 65,
      "outputs": [
        {
          "output_type": "error",
          "ename": "SyntaxError",
          "evalue": "ignored",
          "traceback": [
            "\u001b[0;36m  File \u001b[0;32m\"<ipython-input-65-f6a18b6e0cc8>\"\u001b[0;36m, line \u001b[0;32m2\u001b[0m\n\u001b[0;31m    DROP TABLE Schoolmates;\u001b[0m\n\u001b[0m             ^\u001b[0m\n\u001b[0;31mSyntaxError\u001b[0m\u001b[0;31m:\u001b[0m invalid syntax\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "hK3Aff9bhkkE",
        "colab_type": "text"
      },
      "source": [
        "### <font color=\"green\"> 1.7 Challenges</font>"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "OhTHowBAhmF_",
        "colab_type": "code",
        "cellView": "both",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 72
        },
        "outputId": "5129882a-0dc7-48af-fcba-fae2e24d1df2"
      },
      "source": [
        "# Challenge 1\n",
        "# Lets drop the Players table from our database\n",
        "#\n",
        "%%sql\n",
        "DROP TABLE Players;"
      ],
      "execution_count": 57,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            " * sqlite://\n",
            "Done.\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 57
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "TupkeQYohmc9",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 72
        },
        "outputId": "20861acc-a111-49f0-9769-b28aa4eb5ad7"
      },
      "source": [
        "# Challenge 2\n",
        "# Lets drop the Customers table from our database \n",
        "#\n",
        "%%sql\n",
        "DROP TABLE Customers;"
      ],
      "execution_count": 58,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            " * sqlite://\n",
            "Done.\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 58
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "5JA8Kr56hmPr",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 90
        },
        "outputId": "d8d70b11-b04c-4f34-fde7-cab1e3265655"
      },
      "source": [
        "# Challenge 3\n",
        "# And finally truncate and drop our Artists table from our database\n",
        "#\n",
        "%%sql\n",
        "DROP  artists;"
      ],
      "execution_count": 63,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            " * sqlite://\n",
            "(sqlite3.OperationalError) near \"RECORD\": syntax error\n",
            "[SQL: DROP RECORD artists;]\n",
            "(Background on this error at: http://sqlalche.me/e/e3q8)\n"
          ],
          "name": "stdout"
        }
      ]
    }
  ]
}