SQL_CRUD_with_code.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rC5dTmT4Ljf6"
      },
      "source": [
        "# SQL - (Structured Query Language) \n",
        "It is the language that we use when we try to interact with the database or we can say that SQL is basically a standard that all relational DBMS like Mysql implement.\n",
        "\n",
        "Mysql\n",
        "\n",
        "we will be using mysql throughout these tutorial but the concepts that we will learn can be applied to pretty much any relational databases."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Ga2axL6xLjf7"
      },
      "source": [
        "# Working with Databases\n",
        "in order to start working with databases, we first need to create them and then select them to use that databases, after that we can make table in those databases to store the various data in tabular format as per our requirement.\n",
        "- to create a database we use - \n",
        "**CREATE DATABASE mydb**(we have already created a database)\n",
        "<p>once the database is created , we can select that databases, by doing this we explicitly tell mysql that out of all the available databases which one of them we want to work upon.</p>\n",
        "- to select a database\n",
        "**\"USE mydb\"**\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "orGusXKvLjf7"
      },
      "source": [
        "# Why SQL is so important?\n",
        "[Why SQL is in demand](https://www.freecodecamp.org/news/why-learn-sql/)\n",
        "\n",
        "---\n",
        "[Why you Should Learn SQL](https://www.youtube.com/watch?v=wr7Qwp0cBzI)\n"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### **RUN NEXT 5 CELLS TO CREATE DATABASE, TABLE AND TO INSERT DATA**"
      ],
      "metadata": {
        "id": "Z6kE0veFzWfo"
      }
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "id": "NtA0LkJp3PfT"
      },
      "outputs": [],
      "source": [
        "## JUST RUN THIS CELL\n",
        "import sqlite3\n",
        "import pandas as pd\n",
        "\n",
        "df = pd.DataFrame({\"id\": [1, 2, 3, 4, 5],\n",
        "    \"name\": [\"David Beggam\", \"Rahul Josh\",\n",
        "                            \"Joe Kamal\", \"Micheal John\", \"Sarath Kumar\"],\n",
        "                  \"address\": [\"California\", \"Mumbai\",\n",
        "                              \"Banglore\", \"Delhi\", \"Chennai\"]})\n",
        "\n",
        "df.set_index(\"id\", inplace=True)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 2,
      "metadata": {
        "id": "wUfxjmFq3PfV",
        "outputId": "e6f8b588-ab8c-4d4d-c27b-daa36a9f5753",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 238
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "            name     address\n",
              "id                          \n",
              "1   David Beggam  California\n",
              "2     Rahul Josh      Mumbai\n",
              "3      Joe Kamal    Banglore\n",
              "4   Micheal John       Delhi\n",
              "5   Sarath Kumar     Chennai"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-ed8618cf-bfdb-4ce3-9f4d-17b5225f160b\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>name</th>\n",
              "      <th>address</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>id</th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>David Beggam</td>\n",
              "      <td>California</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Rahul Josh</td>\n",
              "      <td>Mumbai</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Joe Kamal</td>\n",
              "      <td>Banglore</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Micheal John</td>\n",
              "      <td>Delhi</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>Sarath Kumar</td>\n",
              "      <td>Chennai</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-ed8618cf-bfdb-4ce3-9f4d-17b5225f160b')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-ed8618cf-bfdb-4ce3-9f4d-17b5225f160b button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-ed8618cf-bfdb-4ce3-9f4d-17b5225f160b');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 2
        }
      ],
      "source": [
        "# Run this and look at your dataframe which you will pass into database\n",
        "df.head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 3,
      "metadata": {
        "id": "VOfSaB8_3PfW"
      },
      "outputs": [],
      "source": [
        "# run this to create and connect to database\n",
        "connection = sqlite3.connect('cloud.db')\n",
        "df.to_sql('customers', connection)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 4,
      "metadata": {
        "id": "Wf6YGN4Y3PfW"
      },
      "outputs": [],
      "source": [
        "# load sql extension\n",
        "%load_ext sql"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 5,
      "metadata": {
        "id": "XcEgvw8I3PfX",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 35
        },
        "outputId": "44afd700-d57f-42fa-f1d7-d43d65ed9d9c"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'Connected: @cloud.db'"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            }
          },
          "metadata": {},
          "execution_count": 5
        }
      ],
      "source": [
        "# connecting to cloud.db\n",
        "%sql sqlite:///cloud.db"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "y0rd5DAuLjf9"
      },
      "source": [
        "## Hoorayy!!Now it's time to check your skill on sql . Write a code for below questions\n",
        "\n",
        "##### **NOTE** : Do not remove %%sql magic command as it will throw error if you run sql query without it."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "yOxU1NiNLjf9"
      },
      "source": [
        "### 1.Show all the data inside the table\"customers\""
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 30,
      "metadata": {
        "id": "J9GwxDdFLjf-",
        "scrolled": false,
        "outputId": "ed322eaf-2000-4a09-86a1-159f662c2abe",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 221
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///cloud.db\n",
            "Done.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[(1, 'David Beggam', 'gujrat'),\n",
              " (2, 'Rahul Josh', 'Mumbai'),\n",
              " (3, 'Joe Kamal', 'Banglore'),\n",
              " (4, 'Micheal John', 'Delhi'),\n",
              " (5, 'Sarath Kumar', 'Chennai'),\n",
              " (None, 'Mary', 'Goa'),\n",
              " (6, 'Mary', 'Goa')]"
            ],
            "text/html": [
              "<table>\n",
              "    <thead>\n",
              "        <tr>\n",
              "            <th>id</th>\n",
              "            <th>name</th>\n",
              "            <th>address</th>\n",
              "        </tr>\n",
              "    </thead>\n",
              "    <tbody>\n",
              "        <tr>\n",
              "            <td>1</td>\n",
              "            <td>David Beggam</td>\n",
              "            <td>gujrat</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2</td>\n",
              "            <td>Rahul Josh</td>\n",
              "            <td>Mumbai</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>3</td>\n",
              "            <td>Joe Kamal</td>\n",
              "            <td>Banglore</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>4</td>\n",
              "            <td>Micheal John</td>\n",
              "            <td>Delhi</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>5</td>\n",
              "            <td>Sarath Kumar</td>\n",
              "            <td>Chennai</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>None</td>\n",
              "            <td>Mary</td>\n",
              "            <td>Goa</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>6</td>\n",
              "            <td>Mary</td>\n",
              "            <td>Goa</td>\n",
              "        </tr>\n",
              "    </tbody>\n",
              "</table>"
            ]
          },
          "metadata": {},
          "execution_count": 30
        }
      ],
      "source": [
        "%%sql\n",
        "select * from customers\n"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### **Check This link to See How to Limit Customer Data**\n",
        "\n",
        "https://www.w3schools.com/sql/sql_top.asp"
      ],
      "metadata": {
        "id": "G85ZNIKQQ8AG"
      }
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "sYDL_XYxLjf-"
      },
      "source": [
        "<b>##2. LIMIT CUSTOMERS DATA TO 3</b>"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 31,
      "metadata": {
        "id": "FnLpWeRPLjf-",
        "outputId": "5a3f0490-27d8-4c2a-ab31-78aa04001b55",
        "scrolled": true,
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 137
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///cloud.db\n",
            "Done.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[(1, 'David Beggam', 'gujrat'),\n",
              " (2, 'Rahul Josh', 'Mumbai'),\n",
              " (3, 'Joe Kamal', 'Banglore')]"
            ],
            "text/html": [
              "<table>\n",
              "    <thead>\n",
              "        <tr>\n",
              "            <th>id</th>\n",
              "            <th>name</th>\n",
              "            <th>address</th>\n",
              "        </tr>\n",
              "    </thead>\n",
              "    <tbody>\n",
              "        <tr>\n",
              "            <td>1</td>\n",
              "            <td>David Beggam</td>\n",
              "            <td>gujrat</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2</td>\n",
              "            <td>Rahul Josh</td>\n",
              "            <td>Mumbai</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>3</td>\n",
              "            <td>Joe Kamal</td>\n",
              "            <td>Banglore</td>\n",
              "        </tr>\n",
              "    </tbody>\n",
              "</table>"
            ]
          },
          "metadata": {},
          "execution_count": 31
        }
      ],
      "source": [
        "%%sql\n",
        "select * from customers\n",
        "limit 3\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Laz8Riy7Ljf_"
      },
      "source": [
        "<b>3.Select all the data of employees that work in Chennai and Mumbai </b>"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 32,
      "metadata": {
        "id": "JYtI6A7bLjf_",
        "outputId": "cc51ffed-131d-4662-d7d5-634838bb1417",
        "scrolled": false,
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 116
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///cloud.db\n",
            "Done.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[(2, 'Rahul Josh', 'Mumbai'), (5, 'Sarath Kumar', 'Chennai')]"
            ],
            "text/html": [
              "<table>\n",
              "    <thead>\n",
              "        <tr>\n",
              "            <th>id</th>\n",
              "            <th>name</th>\n",
              "            <th>address</th>\n",
              "        </tr>\n",
              "    </thead>\n",
              "    <tbody>\n",
              "        <tr>\n",
              "            <td>2</td>\n",
              "            <td>Rahul Josh</td>\n",
              "            <td>Mumbai</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>5</td>\n",
              "            <td>Sarath Kumar</td>\n",
              "            <td>Chennai</td>\n",
              "        </tr>\n",
              "    </tbody>\n",
              "</table>"
            ]
          },
          "metadata": {},
          "execution_count": 32
        }
      ],
      "source": [
        "%%sql\n",
        "select * from customers\n",
        "where address = 'Chennai' or address = 'Mumbai'\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "yNOnlJW0Ljf_"
      },
      "source": [
        "<b>4. select name starting with letter S</b>"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 33,
      "metadata": {
        "id": "956qOvmkLjf_",
        "outputId": "fb1fdb67-cd19-4f71-848b-b3c508942832",
        "scrolled": false,
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 95
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///cloud.db\n",
            "Done.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[(5, 'Sarath Kumar', 'Chennai')]"
            ],
            "text/html": [
              "<table>\n",
              "    <thead>\n",
              "        <tr>\n",
              "            <th>id</th>\n",
              "            <th>name</th>\n",
              "            <th>address</th>\n",
              "        </tr>\n",
              "    </thead>\n",
              "    <tbody>\n",
              "        <tr>\n",
              "            <td>5</td>\n",
              "            <td>Sarath Kumar</td>\n",
              "            <td>Chennai</td>\n",
              "        </tr>\n",
              "    </tbody>\n",
              "</table>"
            ]
          },
          "metadata": {},
          "execution_count": 33
        }
      ],
      "source": [
        "%%sql\n",
        "select * from customers\n",
        "where name like 's%'\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "a4F2MbGVLjf_"
      },
      "source": [
        "<b>##5. select the col\"address\" from the table\"customers\" </b>"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 34,
      "metadata": {
        "id": "DhyB5kUbLjgA",
        "outputId": "c009fdd2-b48c-4008-f391-4e3a414d9206",
        "scrolled": true,
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 221
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///cloud.db\n",
            "Done.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[('gujrat',),\n",
              " ('Mumbai',),\n",
              " ('Banglore',),\n",
              " ('Delhi',),\n",
              " ('Chennai',),\n",
              " ('Goa',),\n",
              " ('Goa',)]"
            ],
            "text/html": [
              "<table>\n",
              "    <thead>\n",
              "        <tr>\n",
              "            <th>address</th>\n",
              "        </tr>\n",
              "    </thead>\n",
              "    <tbody>\n",
              "        <tr>\n",
              "            <td>gujrat</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>Mumbai</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>Banglore</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>Delhi</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>Chennai</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>Goa</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>Goa</td>\n",
              "        </tr>\n",
              "    </tbody>\n",
              "</table>"
            ]
          },
          "metadata": {},
          "execution_count": 34
        }
      ],
      "source": [
        "%%sql\n",
        "select address from customers\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2is1BoOBLjgA"
      },
      "source": [
        "<b>##6.select column \"address\" where city not in delhi</b>"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 35,
      "metadata": {
        "id": "pQ91gfkwLjgA",
        "outputId": "1ae9231a-ece4-482d-c55b-1aa27102899c",
        "scrolled": true,
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 200
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///cloud.db\n",
            "Done.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[(1, 'David Beggam', 'gujrat'),\n",
              " (2, 'Rahul Josh', 'Mumbai'),\n",
              " (3, 'Joe Kamal', 'Banglore'),\n",
              " (5, 'Sarath Kumar', 'Chennai'),\n",
              " (None, 'Mary', 'Goa'),\n",
              " (6, 'Mary', 'Goa')]"
            ],
            "text/html": [
              "<table>\n",
              "    <thead>\n",
              "        <tr>\n",
              "            <th>id</th>\n",
              "            <th>name</th>\n",
              "            <th>address</th>\n",
              "        </tr>\n",
              "    </thead>\n",
              "    <tbody>\n",
              "        <tr>\n",
              "            <td>1</td>\n",
              "            <td>David Beggam</td>\n",
              "            <td>gujrat</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2</td>\n",
              "            <td>Rahul Josh</td>\n",
              "            <td>Mumbai</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>3</td>\n",
              "            <td>Joe Kamal</td>\n",
              "            <td>Banglore</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>5</td>\n",
              "            <td>Sarath Kumar</td>\n",
              "            <td>Chennai</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>None</td>\n",
              "            <td>Mary</td>\n",
              "            <td>Goa</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>6</td>\n",
              "            <td>Mary</td>\n",
              "            <td>Goa</td>\n",
              "        </tr>\n",
              "    </tbody>\n",
              "</table>"
            ]
          },
          "metadata": {},
          "execution_count": 35
        }
      ],
      "source": [
        "%%sql\n",
        "select id, name, address\n",
        "from customers\n",
        "where address !='Delhi'\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### **Check this to see how to use asc, desc in SQL**\n",
        "\n",
        "https://www.w3schools.com/sql/sql_ref_asc.asp\n",
        "\n",
        "https://www.w3schools.com/sql/sql_ref_desc.asp"
      ],
      "metadata": {
        "id": "y0fmnqe4Uyy3"
      }
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7OuFnJ9LLjgA"
      },
      "source": [
        "<b>7. sort the column: name in asc and addrress in desc</b>"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 36,
      "metadata": {
        "id": "9umkztFJLjgB",
        "outputId": "22ba5f27-c17d-4b07-bb84-08719957a0c4",
        "scrolled": true,
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 221
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///cloud.db\n",
            "Done.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[(1, 'David Beggam', 'gujrat'),\n",
              " (3, 'Joe Kamal', 'Banglore'),\n",
              " (None, 'Mary', 'Goa'),\n",
              " (6, 'Mary', 'Goa'),\n",
              " (4, 'Micheal John', 'Delhi'),\n",
              " (2, 'Rahul Josh', 'Mumbai'),\n",
              " (5, 'Sarath Kumar', 'Chennai')]"
            ],
            "text/html": [
              "<table>\n",
              "    <thead>\n",
              "        <tr>\n",
              "            <th>id</th>\n",
              "            <th>name</th>\n",
              "            <th>address</th>\n",
              "        </tr>\n",
              "    </thead>\n",
              "    <tbody>\n",
              "        <tr>\n",
              "            <td>1</td>\n",
              "            <td>David Beggam</td>\n",
              "            <td>gujrat</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>3</td>\n",
              "            <td>Joe Kamal</td>\n",
              "            <td>Banglore</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>None</td>\n",
              "            <td>Mary</td>\n",
              "            <td>Goa</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>6</td>\n",
              "            <td>Mary</td>\n",
              "            <td>Goa</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>4</td>\n",
              "            <td>Micheal John</td>\n",
              "            <td>Delhi</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2</td>\n",
              "            <td>Rahul Josh</td>\n",
              "            <td>Mumbai</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>5</td>\n",
              "            <td>Sarath Kumar</td>\n",
              "            <td>Chennai</td>\n",
              "        </tr>\n",
              "    </tbody>\n",
              "</table>"
            ]
          },
          "metadata": {},
          "execution_count": 36
        }
      ],
      "source": [
        "%%sql\n",
        "select * from customers\n",
        "order by name asc,address desc\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "qhybMo0ELjgB"
      },
      "source": [
        "<b>8.  Add an customer name  called \"Mary\" ,with address in 'Goa'</b>"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 37,
      "metadata": {
        "id": "gEOyLV0mLjgB",
        "outputId": "e6f28791-5139-40cd-f1a3-a183c9f161a0",
        "scrolled": true,
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///cloud.db\n",
            "1 rows affected.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {},
          "execution_count": 37
        }
      ],
      "source": [
        "%%sql\n",
        "insert into customers(id,name,address)\n",
        "values (6,\"Mary\",\"Goa\");\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "%%sql\n",
        "select * from customers"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 242
        },
        "id": "Nc28eoJNlZcw",
        "outputId": "4b500561-f204-496d-d3ed-76003bc39555"
      },
      "execution_count": 38,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///cloud.db\n",
            "Done.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[(1, 'David Beggam', 'gujrat'),\n",
              " (2, 'Rahul Josh', 'Mumbai'),\n",
              " (3, 'Joe Kamal', 'Banglore'),\n",
              " (4, 'Micheal John', 'Delhi'),\n",
              " (5, 'Sarath Kumar', 'Chennai'),\n",
              " (None, 'Mary', 'Goa'),\n",
              " (6, 'Mary', 'Goa'),\n",
              " (6, 'Mary', 'Goa')]"
            ],
            "text/html": [
              "<table>\n",
              "    <thead>\n",
              "        <tr>\n",
              "            <th>id</th>\n",
              "            <th>name</th>\n",
              "            <th>address</th>\n",
              "        </tr>\n",
              "    </thead>\n",
              "    <tbody>\n",
              "        <tr>\n",
              "            <td>1</td>\n",
              "            <td>David Beggam</td>\n",
              "            <td>gujrat</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2</td>\n",
              "            <td>Rahul Josh</td>\n",
              "            <td>Mumbai</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>3</td>\n",
              "            <td>Joe Kamal</td>\n",
              "            <td>Banglore</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>4</td>\n",
              "            <td>Micheal John</td>\n",
              "            <td>Delhi</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>5</td>\n",
              "            <td>Sarath Kumar</td>\n",
              "            <td>Chennai</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>None</td>\n",
              "            <td>Mary</td>\n",
              "            <td>Goa</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>6</td>\n",
              "            <td>Mary</td>\n",
              "            <td>Goa</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>6</td>\n",
              "            <td>Mary</td>\n",
              "            <td>Goa</td>\n",
              "        </tr>\n",
              "    </tbody>\n",
              "</table>"
            ]
          },
          "metadata": {},
          "execution_count": 38
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### **Check this how to use update keyword in sql**\n",
        "\n",
        "https://www.w3schools.com/sql/sql_update.asp"
      ],
      "metadata": {
        "id": "F20SEsJAV4g2"
      }
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Z7CA_MLKLjgB"
      },
      "source": [
        "<b>9. update address to gujarat where id is 1</b>"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 39,
      "metadata": {
        "id": "pLIum-Hw3Pfd",
        "outputId": "ae3c14ff-a6cf-46d8-e9a2-b0ff8c1f3827",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///cloud.db\n",
            "1 rows affected.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {},
          "execution_count": 39
        }
      ],
      "source": [
        "%%sql\n",
        "\n",
        "update customers \n",
        "set address = 'gujrat'\n",
        "where id = 1"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "%%sql\n",
        "select * from customers"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 242
        },
        "id": "TgbnQGR9mdPR",
        "outputId": "002b7c73-6bff-46cc-ab78-4765090a83fb"
      },
      "execution_count": 40,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///cloud.db\n",
            "Done.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[(1, 'David Beggam', 'gujrat'),\n",
              " (2, 'Rahul Josh', 'Mumbai'),\n",
              " (3, 'Joe Kamal', 'Banglore'),\n",
              " (4, 'Micheal John', 'Delhi'),\n",
              " (5, 'Sarath Kumar', 'Chennai'),\n",
              " (None, 'Mary', 'Goa'),\n",
              " (6, 'Mary', 'Goa'),\n",
              " (6, 'Mary', 'Goa')]"
            ],
            "text/html": [
              "<table>\n",
              "    <thead>\n",
              "        <tr>\n",
              "            <th>id</th>\n",
              "            <th>name</th>\n",
              "            <th>address</th>\n",
              "        </tr>\n",
              "    </thead>\n",
              "    <tbody>\n",
              "        <tr>\n",
              "            <td>1</td>\n",
              "            <td>David Beggam</td>\n",
              "            <td>gujrat</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2</td>\n",
              "            <td>Rahul Josh</td>\n",
              "            <td>Mumbai</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>3</td>\n",
              "            <td>Joe Kamal</td>\n",
              "            <td>Banglore</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>4</td>\n",
              "            <td>Micheal John</td>\n",
              "            <td>Delhi</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>5</td>\n",
              "            <td>Sarath Kumar</td>\n",
              "            <td>Chennai</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>None</td>\n",
              "            <td>Mary</td>\n",
              "            <td>Goa</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>6</td>\n",
              "            <td>Mary</td>\n",
              "            <td>Goa</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>6</td>\n",
              "            <td>Mary</td>\n",
              "            <td>Goa</td>\n",
              "        </tr>\n",
              "    </tbody>\n",
              "</table>"
            ]
          },
          "metadata": {},
          "execution_count": 40
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### **Check this how you to use delete keyword in sql**\n",
        "\n",
        "https://www.w3schools.com/sql/sql_delete.asp"
      ],
      "metadata": {
        "id": "GcctJlvWWC-5"
      }
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "WyKYppVDLjgC"
      },
      "source": [
        "<b>10.delete the record where address='mumbai'</b>"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 44,
      "metadata": {
        "id": "SE5b9b2kLjgC",
        "outputId": "8cb28f50-0df8-4a5d-cccc-4fbad2da9584",
        "scrolled": false,
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///cloud.db\n",
            "1 rows affected.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {},
          "execution_count": 44
        }
      ],
      "source": [
        "%%sql\n",
        "delete from customers\n",
        "where address='Mumbai'\n"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### **Check this to see how you create temporary name for the column**\n",
        "\n",
        "https://www.w3schools.com/sql/sql_alias.asp#:~:text=SQL%20aliases%20are%20used%20to,created%20with%20the%20AS%20keyword."
      ],
      "metadata": {
        "id": "KqBZGKRuVInS"
      }
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Bt6BCu11LjgC"
      },
      "source": [
        "<b>11.create a temporary name for the column \"name\"with 'N' and \"address\"as 'A'</b>"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 47,
      "metadata": {
        "id": "SBpN142pLjgC",
        "outputId": "fb1e6167-ba4b-45f9-dfe6-598e70b36e66",
        "scrolled": false,
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 200
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///cloud.db\n",
            "Done.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[(None, 'Mary', 'Goa'),\n",
              " (1, 'David Beggam', 'gujrat'),\n",
              " (3, 'Joe Kamal', 'Banglore'),\n",
              " (4, 'Micheal John', 'Delhi'),\n",
              " (5, 'Sarath Kumar', 'Chennai'),\n",
              " (6, 'Mary', 'Goa')]"
            ],
            "text/html": [
              "<table>\n",
              "    <thead>\n",
              "        <tr>\n",
              "            <th>id</th>\n",
              "            <th>N</th>\n",
              "            <th>A</th>\n",
              "        </tr>\n",
              "    </thead>\n",
              "    <tbody>\n",
              "        <tr>\n",
              "            <td>None</td>\n",
              "            <td>Mary</td>\n",
              "            <td>Goa</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>1</td>\n",
              "            <td>David Beggam</td>\n",
              "            <td>gujrat</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>3</td>\n",
              "            <td>Joe Kamal</td>\n",
              "            <td>Banglore</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>4</td>\n",
              "            <td>Micheal John</td>\n",
              "            <td>Delhi</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>5</td>\n",
              "            <td>Sarath Kumar</td>\n",
              "            <td>Chennai</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>6</td>\n",
              "            <td>Mary</td>\n",
              "            <td>Goa</td>\n",
              "        </tr>\n",
              "    </tbody>\n",
              "</table>"
            ]
          },
          "metadata": {},
          "execution_count": 47
        }
      ],
      "source": [
        "%%sql \n",
        "select distinct id,name As N, address as A\n",
        "from customers\n"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### **Check this to see how to concat in sqlite**\n",
        "\n",
        "https://www.sqlitetutorial.net/sqlite-string-functions/sqlite-concat/"
      ],
      "metadata": {
        "id": "Lw3FBmlJVY1z"
      }
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "xSYS6d1JLjgC"
      },
      "source": [
        "<b>12. concat column's \"name\" and \"address\" and name it as customerdb</b>"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 53,
      "metadata": {
        "id": "hInKNTIrLjgC",
        "outputId": "23028b60-2cd5-4705-c4c1-70e857556482",
        "scrolled": true,
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 221
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///cloud.db\n",
            "Done.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[(1, 'David Beggam', 'David Beggam  gujrat'),\n",
              " (3, 'Joe Kamal', 'Joe Kamal  Banglore'),\n",
              " (4, 'Micheal John', 'Micheal John  Delhi'),\n",
              " (5, 'Sarath Kumar', 'Sarath Kumar  Chennai'),\n",
              " (None, 'Mary', 'Mary  Goa'),\n",
              " (6, 'Mary', 'Mary  Goa'),\n",
              " (6, 'Mary', 'Mary  Goa')]"
            ],
            "text/html": [
              "<table>\n",
              "    <thead>\n",
              "        <tr>\n",
              "            <th>id</th>\n",
              "            <th>name</th>\n",
              "            <th>customerdb</th>\n",
              "        </tr>\n",
              "    </thead>\n",
              "    <tbody>\n",
              "        <tr>\n",
              "            <td>1</td>\n",
              "            <td>David Beggam</td>\n",
              "            <td>David Beggam  gujrat</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>3</td>\n",
              "            <td>Joe Kamal</td>\n",
              "            <td>Joe Kamal  Banglore</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>4</td>\n",
              "            <td>Micheal John</td>\n",
              "            <td>Micheal John  Delhi</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>5</td>\n",
              "            <td>Sarath Kumar</td>\n",
              "            <td>Sarath Kumar  Chennai</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>None</td>\n",
              "            <td>Mary</td>\n",
              "            <td>Mary  Goa</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>6</td>\n",
              "            <td>Mary</td>\n",
              "            <td>Mary  Goa</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>6</td>\n",
              "            <td>Mary</td>\n",
              "            <td>Mary  Goa</td>\n",
              "        </tr>\n",
              "    </tbody>\n",
              "</table>"
            ]
          },
          "metadata": {},
          "execution_count": 53
        }
      ],
      "source": [
        "%%sql\n",
        "select id,name, name || \"  \"  || address as customerdb\n",
        "from customers\n"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### **Check this how to use group by**\n",
        "\n",
        "https://www.w3schools.com/sql/sql_groupby.asp"
      ],
      "metadata": {
        "id": "QPOXAevaVtzD"
      }
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "mDnHPfHTLjgC"
      },
      "source": [
        "<b>##13 select address and also count the column \"name\" and group by address </b> "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 54,
      "metadata": {
        "id": "k8oUNVj6LjgD",
        "outputId": "4b4c3e8e-8724-4816-885e-acf336090d8b",
        "scrolled": true,
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 179
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * sqlite:///cloud.db\n",
            "Done.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[('Banglore', 1), ('Chennai', 1), ('Delhi', 1), ('Goa', 3), ('gujrat', 1)]"
            ],
            "text/html": [
              "<table>\n",
              "    <thead>\n",
              "        <tr>\n",
              "            <th>address</th>\n",
              "            <th>count(name)</th>\n",
              "        </tr>\n",
              "    </thead>\n",
              "    <tbody>\n",
              "        <tr>\n",
              "            <td>Banglore</td>\n",
              "            <td>1</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>Chennai</td>\n",
              "            <td>1</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>Delhi</td>\n",
              "            <td>1</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>Goa</td>\n",
              "            <td>3</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>gujrat</td>\n",
              "            <td>1</td>\n",
              "        </tr>\n",
              "    </tbody>\n",
              "</table>"
            ]
          },
          "metadata": {},
          "execution_count": 54
        }
      ],
      "source": [
        "%%sql\n",
        "select address,count(name)\n",
        "from customers\n",
        "group by address\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "irGeDjEaLjgD"
      },
      "source": [
        "<b>Congratulations !! You Have Just Completed your first step towards becoming an SQL Ninja!</b>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ynFRizm4Lsy2"
      },
      "source": [
        "# **FEEDBACK FORM** #\n",
        "https://docs.google.com/forms/d/e/1FAIpQLScnAaAKvph9R3vINyCtOOMiav-tSMsRGlJReMaHyPjsXDlTIA/viewform"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "name": "SQL_CRUD_with_code.ipynb",
      "provenance": [],
      "collapsed_sections": [
        "rC5dTmT4Ljf6",
        "Ga2axL6xLjf7",
        "ynFRizm4Lsy2"
      ]
    },
    "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.8.8"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}